MSSQL — уменьшаем Initial Size в tempdb

Срочно понадобилось уменьшить размер tempdb. Можно выполнить сжатие, перезапуск сервера, танцы с бубнами. Всё это уменьшит размер tempdb, но не сделает его меньше Initial Size. И это большая проблема, особенно для тех экземпляров, где база tempdb вынесена в оперативную память:

MSSQL — переносим tempdb на RAM диск

Есть полезная статья от Microsoft, в которой есть решение по уменьшению Initial Size:

Как сжать базу данных tempdb в SQL Server

Но о подводных камнях там ни слова. Печаль меня настигла, когда я дошёл до пункта: "Подключитесь к серверу SQL Server с помощью анализатора запросов". Сложно найти на сервере анализатор запросов, особенно если он там не установлен. Но можно обойтись без него, читаем.

Сжимаем tempdb

Для уменьшения Initial Size базы tempdb нужно:

1. Остановить службы SQL Server.
2. Запустить SQL Server в режиме минимальной конфигурации.
3. Подключиться к SQL Server от имени администратора (при этом нужно не дать подключиться к серверу другим администраторам раньше вас).
4. Выполнить SQL запросы для уменьшения базы tempdb:

   ALTER DATABASE tempdb MODIFY FILE
      (NAME = 'tempdev', SIZE = target_size_in_MB) 
      --Desired target size for the data file
   
      ALTER DATABASE tempdb MODIFY FILE
      (NAME = 'templog', SIZE = target_size_in_MB)
      --Desired target size for the log file

5. Остановить SQL Server.
6. Запустить SQL Server в обычном режиме.

Перед тем как остановить SQL Server подумайте, как сделать так, чтобы никто другой потом не смог установить соединение раньше вас. Особенно 1С.

• Вы можете зайти на сервер через консоль KVM и отключить сеть.
• Вы можете запретить доступ на сервер извне с помощью Firewall.
• Вы можете остановить все приложения, которые работают с данным SQL сервером.
• Вы можете сменить порт SQL сервера.
• Вы можете сменить пароль администратора SQL сервера.
• Вы можете ничего не делать, но при этом действовать быстро и подключиться к серверу первым.

Перед началом работ решите, какой установите Initial Size для tempdev и templog.

У меня начальный размер tempdb 12 ГБ, уменьшу до 1 ГБ.

Останавливаем службы SQL Server.

Открываем командную строку под администратором. Переходим в рабочую директорию:

cd "C:\Program Files\Microsoft SQL Server\MSSQL12.DL1CSQL00\MSSQL\Binn"

Запускаем SQL сервер в режиме минимальной конфигурации:

sqlservr -c -f

Сервер запускается.

Теперь нужно подключиться к SQL серверу. Если используем SQL Server Management Studio, то получим ошибку:

Login failed for user User. Reason: Server is in single user mode. Only one administrator can connect at this time.

Вероятно, студия выполняет несколько коннектов, что недопустимо в режиме single user mode. Похожая ошибка возникнет и в том случае, если кто-то успеет выполнить соединение раньше вас.

Запускаем вторую командную строку под администратором. Выполняем:

sqlcmd -S localhost -E

Если видим "1>", то подключение успешно. Вводим:

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 1024) 
ALTER DATABASE tempdb MODIFY FILE   (NAME = 'templog', SIZE = 100)
GO

Нажимаем в этом окне Ctrl+C, соединение завершается.

Нажимаем в окне с запущенным SQL сервером Ctrl+C, на вопрос об остановке SQL сервера пишем "Y", SQL сервер останавливается.

Запускаем SQL сервер в обычном режиме.

Проверяем размер tempdb. Initial Size теперь 1024 МБ.

Простой у меня составил четыре минуты.