Всем доброго времени суток. Пришла беда, откуда не ждали. Увалилась база. MSSQL висит Recovering Pending.
Создал новую, с таким же именем, стопнул сервер, заменил MDF, запустил сервер, сделал следующее:
USE master
GO
sp_configure 'allow updates', 1
reconfigure WITH override
GO
ALTER DATABASE db_name SET EMERGENCY, SINGLE_USER
GO
Получил
Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 601, Level 12, State 3, Line 6
Could not continue scan with NOLOCK due to data movement.
Could not continue scan with NOLOCK due to data movement. - это приговор, как это победить???
Прошу у Вас помощи.

felix_ff,сильно тапками не закидывайте... 17-я версия.
База в Recovering Pending. Подскажите, как ее реанимировать...? Буду признателен.

Гавриленко Сергей Алексеевич,
восстановил бы и этой темы здесь не было, но что случилось, то случилось... Если знаете, как восстановить, подмогните.

Гавриленко Сергей Алексеевич,
Еще раз повторюсь, про бекап в курсе, и темы бы не было. Терять все равно уже нечего, поэтому буду признателен, если подскажите про DBCC CHECKDB. Загвоздка в том ,что затыкается, не дойдя до DBCC CHECKDB

Артем Falcon,
Сейчас попробую. Спасибо. Отпишу по результату.

Артем Falcon,
Запрос
alter database имя_твоей_базы set emergency
В ответ
Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
Запрос
dbcc checkdb('имя_твоей_базы')
Ответ
Msg 945, Level 14, State 2, Line 1
Database 'имя_твоей_базы' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Запрос
alter database имя_твоей_базы set single_user
Ответ
Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
Остальные запросы не делал, нет смысла, и так понятно, воткнулась на первом запросе, НО в EMERGENCY перешла.

Relic Hunter,
Msg 945, Level 14, State 2, Line 1
Database 'MY_DATABASE' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

SQL2008,
Проверял, есть. Пользователь, от которого работает служба sql, имеет полный доступ на папку и файл. В качестве теста, раскатал бекап другой базы, в проблемную, работает.

komrad,

07/07/2020 03:10:18,Logon,Unknown,Login failed for user 'SERVER1C\Администратор'. Reason: Failed to open the explicitly specified database 'my_base'. [CLIENT: <local machine>]
07/07/2020 03:10:18,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
07/07/2020 03:10:08,Logon,Unknown,Login failed for user 'SERVER1C\Администратор'. Reason: Failed to open the explicitly specified database 'my_base'. [CLIENT: <local machine>]
07/07/2020 03:10:08,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
07/07/2020 03:08:16,spid51,Unknown,An error occurred while processing the log for database 'my_base'. If possible<c/> restore from backup. If a backup is not available<c/> it might be necessary to rebuild the log.
07/07/2020 03:08:16,spid51,Unknown,Error: 9004<c/> Severity: 21<c/> State: 1.
07/07/2020 03:08:16,spid51,Unknown,Starting up database 'my_base'.
07/07/2020 03:08:16,spid51,Unknown,Setting database option ONLINE to ON for database 'my_base'.
07/07/2020 03:08:16,spid51,Unknown,An error occurred while processing the log for database 'my_base'. If possible<c/> restore from backup. If a backup is not available<c/> it might be necessary to rebuild the log.
07/07/2020 03:08:16,spid51,Unknown,Error: 9004<c/> Severity: 21<c/> State: 1.
07/07/2020 03:08:16,spid51,Unknown,Starting up database 'my_base'.
07/07/2020 03:08:16,spid51,Unknown,Setting database option ONLINE to ON for database 'my_base'.
07/07/2020 03:06:13,spid51,Unknown,Setting database option OFFLINE to ON for database 'my_base'.

Гавриленко Сергей Алексеевич,
Правильно понимаю, вот эти запросы мне нужны?
USE master

GO

ALTER DATABASE [dbname] SET EMERGENCY

GO

ALTER DATABASE [dbname] SET SINGLE_USER

GO

ALTER DATABASE [dbname] REBUILD LOG ON

(NAME= logicalname, FILENAME=’Drive path to new file location\Log file name.ldf’) — Like ‘E:\MSSQL\LOGS\DB_Log.ldf’

GO

DBCC CHECKDB ([dbname])

GO

— DBCC CHECKDB ([dbname], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

— GO

ALTER DATABASE [dbname] SET MULTI_USER

GO

komrad,
Запрос
USE master;
GO
EXEC sp_attach_single_file_db @dbname = 'my_base',
@physname =
N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\my_base.mdf';
Ответ
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\my_base_log.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 3
Could not open new database 'my_base'. CREATE DATABASE is aborted.
LOG
07/07/2020 23:25:11,spid52,Unknown,FileMgr::StartLogFiles: Operating system error 2(Не удается найти указанный файл.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\my_base_log.ldf'. Diagnose and correct the operating system error<c/> and retry the operation.
07/07/2020 23:25:11,spid52,Unknown,Error: 17207<c/> Severity: 16<c/> State: 1.
07/07/2020 23:25:11,spid52,Unknown,Starting up database 'my_base'.

Выполнил запрос
USE master;

CREATE DATABASE [my_base]
ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\my_base.mdf' )
FOR ATTACH_FORCE_REBUILD_LOG;
GO
Ответ
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\my_base_log.ldf" may be incorrect.
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\my_base_log.ldf' was created.
Msg 1813, Level 16, State 2, Line 6
Could not open new database 'my_base'. CREATE DATABASE is aborted.
Msg 605, Level 21, State 3, Line 6
Attempt to fetch logical page (1:37115) in database 5 failed. It belongs to allocation unit 72057598646288384 not to 281474980642816.

Relic Hunter,
Запрос
USE master
GO

DBCC TRACEON(3604)
GO

ALTER DATABASE my_base SET EMERGENCY
GO

ALTER DATABASE my_base SET SINGLE_USER
GO

DBCC CHECKDB('my_base', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS
GO

ALTER DATABASE my_base SET MULTI_USER
GO
Ответ
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 601, Level 12, State 3, Line 7
Could not continue scan with NOLOCK due to data movement.
Msg 601, Level 12, State 3, Line 10
Could not continue scan with NOLOCK due to data movement.
Msg 945, Level 14, State 2, Line 13
Database 'my_base' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 601, Level 12, State 3, Line 16
Could not continue scan with NOLOCK due to data movement.
LOG
Date,Source,Severity,Message
07/08/2020 23:26:38,spid53,Unknown,The database 'my_base' is marked EMERGENCY_MODE and is in a state that does not allow recovery to be run.
07/08/2020 23:26:38,spid53,Unknown,Starting up database 'my_base'.
07/08/2020 23:26:38,spid53,Unknown,Setting database option MULTI_USER to ON for database 'my_base'.
07/08/2020 23:26:38,spid53,Unknown,The database 'my_base' is marked EMERGENCY_MODE and is in a state that does not allow recovery to be run.
07/08/2020 23:26:38,spid53,Unknown,Starting up database 'my_base'.
07/08/2020 23:26:38,spid53,Unknown,Setting database option SINGLE_USER to ON for database 'my_base'.
07/08/2020 23:26:38,spid53,Unknown,The database 'my_base' is marked EMERGENCY_MODE and is in a state that does not allow recovery to be run.
07/08/2020 23:26:38,spid53,Unknown,Starting up database 'my_base'.
07/08/2020 23:26:38,spid53,Unknown,Setting database option EMERGENCY to ON for database 'my_base'.
07/08/2020 23:26:38,spid53,Unknown,DBCC TRACEON 3604<c/> server process ID (SPID) 53. This is an informational message only; no user action is required.

Есть мысли? Уже руки опускаются!!!

invm,
вопрос в том, как эти данные перелить? Подскажите?
И в целом, может кто то посодействовать в этом вопросе, за разумное вознаграждение?

Гавриленко Сергей Алексеевич,
База труп, как думаете?

komrad, можно как то с Вами связаться лично, по почте например?