Сказка о поломке или что делать, когда проблема не понятна

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

Предыстория такова: понадобилось перенести tempdb на новый раздел, подключенный к новому хранилищу.

Задача простая

1. пишем запрос, показывающий где у нас лежит tempdb:

   use tempdb

   go

   exec sp_helpfile

   go

2. пишем запрос для изменения настроек хранения tempdb:

   use master

   go

   alter database tempdb

   modify file (name = tempdev, filename = 'Новый_Диск:\Новый_Каталог\tempdb.mdf')

   go

   alter database tempdb

   modify file (name = templog, filename = 'Новый_Диск:\Новый_Каталог\templog.ldf')

   go

3. выполняем его и рестартуем службу SQL Server

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

Приступаем. Запрос выполнили, службу перезапустили, служба перезапущена, проверяем коннект и SSMS зависает на попытке подключится к серверу. Идем смотреть  логи сервера Windows и... ничего. Служба запущена без ошибок, проблем нет. 

Очень сложно исправлять поломку, когда не понимаешь в чём её причина.

В текстовых логах MS SQL были лишь сообщения:

Logon       Login failed for user 'AD\User'. Reason: Failed to open the explicitly specified database 'database'. [CLIENT: 10.10.1.10]

Logon       Error: 18456, Severity: 14, State: 38.

По коду ошибки было понятно, что есть проблемы с правами на базы данных, но подключится не получалось даже используя sa.

 Т.к. идеи у нас быстро кончились, в интернете пытались найти причины проблемы с правами простым перебором:

1. Подключили диск для tempdb на раздел, где места с большим запасом. Маловероятно, что tempdb не создался из-за нехватки места и в логе это не указал, но проверить это требовалось. Не помогло.

2. Запустили SQL Server в минимальной конфигурации (с ключом –f). Файлы tempdb создались в варианте по умолчанию, но на доступ к серверу это не повлияло.

3. В минимальной конфигурации подключились используя DAC и попытались изменить ещё раз пути для tempdb. Обнаружили, что настройки из базы данных считываются, но не меняются, выдавая ошибку, что данного файла не существует.

Сложив полученное определили, что причиной проблем является повреждение базы master - из базы не могли получить данные о правах пользователя и tempdb, но в логе это никак не отражалось и понять в чем причина не удавалось. После восстановления базы master:

DBCC CHECKALLOC ('master',REPAIR_REBUILD);

CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'master'.

получили возможность подключится к остальными базам. Решение было неочевидным, т.к. на поломку базы master указывали только косвенные признаки, такие как недоступность информации о правах.

Два простых вывода - лог может подвести и понять в чем неисправность можно только по совокупности косвенных признаков и backup служебных баз может сильно сэкономить время на восстановление работы сервиса.