Я решил рассказать о ситуации, в которую мы попали по стечению обстоятельств при выполнении обычных работ по перенастройке 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 служебных баз может сильно сэкономить время на восстановление работы сервиса.