Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
nau.7thNext
Member

Откуда:
Сообщений: 7
Здравствуйте. Прошу пояснить, ЧЯДНТ и как надо (я не sql-щик, просто больше некому).

Настраиваю выполнение дефрагментации индексов через планировщик винды.

+ Пример Б отсюда: http://msdn.microsoft.com/ru-ru/library/ms177571.aspx

-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 10.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO


Вышеприведённое находится в файле "Бухгалтерия. Дефрагментация индексов.sql", который выполняю таким .bat-файлом:

chcp 1251
sqlcmd -S SERVER\SQLEXPRESS -d MB-Account2014 -i "Бухгалтерия. Дефрагментация индексов.sql" -o "Бухгалтерия. Дефрагментация индексов.log"

В логе для каждой таблицы получаю следующее (с точностью до имен и чисел):

Executing DBCC INDEXDEFRAG (0, _InfoRg7003, 1) - fragmentation currently 63%
Pages Scanned Pages Moved Pages Removed
-------------------- -------------------- --------------------
8 0 0
Выполнение DBCC завершено. Если DBCC выдает сообщения об ошибках, обратитесь к системному администратору.

.bat запускается от имени отдельно взятого пользователя винды, который имеет право на вход как пакетное задание (чтобы запускался через планировщик), для которого на SERVER\SQLEXPRESS сделано имя входа, у которого выставлен флажок db_owner для MB-Account2014. Но чего-то, видимо, не хватает (пожалуйста, ЧЕГО??? именно), потому что Pages Moved и Pages Removed все время 0, сколько ни запускай, и проценты фрагментации не меняются. При запуске .sql зашедши как sa в Management Studio оно работает. Отдельным вопросом, пожалуйста, будет, какие права надо выставить конкретно для этого случая, чтобы убрать db_owner.

+ Что делал, но не получилось
sp_msforeachtable не работает из-за каких-то заморочек, модифицированную ее версию отсюда всунуть в базу не смог (точнее, она создалась, но я не смог заставить ее выполняться).

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
20 окт 14, 20:59    [16733350]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
Glory
Member

Откуда:
Сообщений: 104751
nau.7thNext
Pages Scanned - 8   

Что вы собрались дефрагментировать на таблице размером в 1 экстент ?
20 окт 14, 21:07    [16733380]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
nau.7thNext
Member

Откуда:
Сообщений: 7
Glory,

ну так я ж говорю -- фрагмент лога с точностью до чисел. Там несколько сотен таблиц, есть и побольше. Просто первое сообщение взял из лога.
20 окт 14, 21:11    [16733386]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
Значит во всех ваших таблицах по 8 страниц данных, которые не поддались дефрагментации.
20 окт 14, 21:12    [16733388]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
nau.7thNext
Glory,

ну так я ж говорю -- фрагмент лога с точностью до чисел. Там несколько сотен таблиц, есть и побольше. Просто первое сообщение взял из лога.
И это подтверждается выводом в лог поля CountPages?

Сообщение было отредактировано: 20 окт 14, 21:14
20 окт 14, 21:14    [16733398]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
Glory
Member

Откуда:
Сообщений: 104751
nau.7thNext
ну так я ж говорю -- фрагмент лога с точностью до чисел.

Ну так и я вас спрашиваю, что вы собрались дефрагментировать в такой маленькой таблице ?
20 окт 14, 21:27    [16733457]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Третий параметр уберите.
20 окт 14, 21:28    [16733464]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
nau.7thNext
Member

Откуда:
Сообщений: 7
Гавриленко Сергей Алексеевич
nau.7thNext
Glory,

ну так я ж говорю -- фрагмент лога с точностью до чисел. Там несколько сотен таблиц, есть и побольше. Просто первое сообщение взял из лога.
И это подтверждается выводом в лог поля CountPages?


Ну вот, нашел таблицу побольше. (Ладно, разыграли чайника, pages count = pages scanned, мог бы и не возиться)

Executing DBCC INDEXDEFRAG (0, _AccumRgTn7408, 1) - fragmentation currently 10%, pages count: 9198
Pages Scanned Pages Moved Pages Removed
-------------------- -------------------- --------------------
9158 8840 370
Выполнение DBCC завершено. Если DBCC выдает сообщения об ошибках, обратитесь к системному администратору.

Гавриленко Сергей Алексеевич
Значит во всех ваших таблицах по 8 страниц данных, которые не поддались дефрагментации.


ОК, понятно, читаю как "таблицы еще не успели фрагментироваться с прошлого выполнения этой процедуры". Действительно, мог бы сам догадаться...

То есть все на самом деле работает. Тогда прошу подсказать, какие минимально права нужно оставить для этой учетки, чтобы работало -- не хочется давать избыточные полномочия.
20 окт 14, 21:43    [16733501]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
nau.7thNext
Member

Откуда:
Сообщений: 7
pkarklin
Третий параметр уберите.


Мильпардон, откуда именно? Их там полно кругом, третьих параметров...
20 окт 14, 21:46    [16733516]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
nau.7thNext
pkarklin
Третий параметр уберите.


Мильпардон, откуда именно? Их там полно кругом, третьих параметров...


Из вызова DBCC INDEXDEFRAG при "ручном запуске", чтоб все индексы попали под дефрагментацию вне зависимости от их текущей фрагментации и увидеть кол-во обработанных страниц.
20 окт 14, 21:53    [16733538]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
nau.7thNext
Member

Откуда:
Сообщений: 7
ОК, спасибо.

Основной вопрос, видимо, решен. Не могли бы вы "на сладкое" подсказать набор минимально необходимых прав?
20 окт 14, 22:04    [16733561]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
В справке по DBCC командам есть раздел Permissions, например:

автор
Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.


Для получения информации из представлений схемы Information_Schema необходимы права VIEW DEFINITION.
20 окт 14, 22:15    [16733599]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
nau.7thNext
Не могли бы вы "на сладкое" подсказать набор минимально необходимых прав?
http://msdn.microsoft.com/en-us/library/ms177571(v=sql.110).aspx
Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.
20 окт 14, 22:18    [16733611]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
nau.7thNext
Member

Откуда:
Сообщений: 7
invm,

"Читайте книги, они рулез". :) Спасибо.
20 окт 14, 22:25    [16733647]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
nau.7thNext
Member

Откуда:
Сообщений: 7
pkarklin
необходимы права VIEW DEFINITION.


Спасибо.
20 окт 14, 22:27    [16733652]     Ответить | Цитировать Сообщить модератору
 Re: Права для DBCC или что-то другое? (не выполняется дефрагментация индексов)  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
nau.7thNext
Здравствуйте. Прошу пояснить, ЧЯДНТ и как надо (я не sql-щик, просто больше некому).

Настраиваю выполнение дефрагментации индексов через планировщик винды.

Пример Б отсюда: http://msdn.microsoft.com/ru-ru/library/ms177571.aspx

А вас правда не смутило?
http://msdn.microsoft.com/ru-ru/library/ms177571.aspx
Важно!
В будущей версии Microsoft SQL Server этот компонент будет удален. Не используйте его при работе над новыми приложениями и как можно быстрее измените приложения, в которых он в настоящее время используется. Используйте вместо этого инструкцию ALTER INDEX.
21 окт 14, 00:06    [16733944]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить