Исправление конфигурации SQL Server в монопольном режиме

В этой статье мы рассмотрим запуск MS SQL Server в режиме минимальной конфигурации (аналог Safe Mode), использование режима выделенного административного подключения DAC (Dedicated Admin Connection) , а также сценарий запуска SQL Server без tempdb (исходная база повреждена или недоступна) и ручное восстановление этой базы данных.

Обычно эти методы применяются в аварийных ситуациях, когда SQL Server не запускается, либо с ним невозможно соединиться из-за сильной нагрузки или ошибок в конфигурации, из-за которых SQL Server не может запуститься в нормальном режиме.

Запуск SQL Server с минимальной конфигурацией, параметр -f

Если SQL Server не запускается из-за ошибок конфигурации:

• Отсутствует или повреждена база tempdb;
• Ошибка в триггере или процедуре, из-за которой невозможно попасть на SQL Server;
• Любые другие ошибки, связанные с конфигурацией SQL Server.

В этом случае необходимо запустить SQL Server в режиме минимальной конфигурации или Safe Mode. Для этого нужно добавить параметр -f в строку запуска экземпляра.

В этом режиме MSSQL запускается с определенной конфигурацией:

• Сервер запускается в однопользовательском (монопольном) режиме single user
• Хранимые процедуры, настроенные на запуск при старте сервера не запускаются
• База tempdb будет сконфигурирована на минимально допустимый размер

В монопольном режиме вы можете восстановить повреждённую базу данных master и другиен системные базы данных.

Восстановление служебной базы данных tempdb через safe mode

Для примера мы возьмем сценарий, когда файлы базы tempdb на дисковом хранилище полностью утеряны и SQL Server не запускается.

В логах MSSQL при отсутствующей tempdb будут ошибки вида:

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘F:DBTempdb.mdf’_x000D__x000D_Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

1. По RDP соединитесь с сервером, где находится нужный вам экземпляр MSSQL;
2. Откройте SQL Server Configuration Manager, в менеджере зайдите в свойства экземпляра -> Startup Parameters и добавьте параметр –f ;
   Также можно использовать команду NET START MSSQLSERVER /f для запуска SQL Server в режиме минимальной конфигурации.

3. Перезагрузите экземпляр SQL Server;
   Совет . После перезагрузки экземпляра, сервер будет работать только с одним соединением. Чтобы это соединение ничего не заняло, выключите SQL Server Agent, ограничьте доступ к SQL Server через фаервол, а также убедитесь, что его не занимают другие администраторы или другие локальные приложения. Вы также можете использовать параметр -m с указанием приложения, с которого вы будете соединяться, например, -mSQLCMD или -m"SQL Server Management Studio" . Поскольку в SQL Server Configuration Manager невозможно задать параметры с кавычками, параметр -m"SQL Server Management Studio" , вы сможете использовать его только запуская SQL Server из командной строки.
4. Запустите SQL Server Management Studio, слздайте новый запрос через кнопку New Query;

Если вы получите подобную ошибку, значит кто-то уже занял соединение.

Cannot connect to testnodenode2_x000D_Login failed for user username. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

Если всё сделано правильно, вы получите доступ к серверу.

Теперь нам нужно изменить пути к испорченной базе tempdb. Выполните следующие T-SQL команды:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:templog.ldf');
GO

Это задаст новую конфигурацию для tempdb. Файлы tempdb будут созданы на диске E:.

The file tempdev/templog has been modified in the system catalog. The new path will be used the next time the database is started.

Удалите параметр -f из диспетчера конфигураций SQL Server и перезагрузите экземпляр.

С помощью этого режима вы также можете поправить другие проблемы с конфигурацией, например, отключить триггер или процедуру.

Также для запуска SQL Server в безопасном режиме можно использовать командную строку:

"C:Program FilesMicrosoft SQL ServerMSSQL15.NODE2MSSQLBinnsqlservr.exe" –c -f -sNODE2"

Это запустит экземпляр NODE2 в safe mode. Параметр -с ускоряет загрузку SQL Server при запуске из командой строки. Вы также можете указать параметр -m"SQL Server Management Studio" для ограничения подключений только из SQL студии или -mSQLCMD для подключения только из sqlcmd .

Режим административного подключения (Dedicated Admin Connection) в SQL Server

Dedicated Admin Connection – это соединение в SQL Server, которое резервируется для подключения администратора. DAC используется только в том случае, если сервер находится под массивной нагрузкой, соединиться обычными способами не получается. для выхода их такой ситуации приходилось перезапускать сервер с потерей всех текущих сеансов пользователей.

По умолчанию, соединиться с помощью DAC можно только с компьютера, на котором запушен SQL Server. Разрешить удаленные DAC соединения можно через T-SQL (можно сделать это сразу после установки SQL Server ).

Для начала проверим настройку remote admin connections:

SELECT value
FROM sys.configurations
where name like '%remote admin connections%'

Если в результате вы получили value = 0, то значит, удаленный DAC выключен. Чтобы включить его, выполните:

EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE
GO

Перезагружать экземпляр не надо, конфигурация применяется динамически.

netstat –aon|findstr 1434