Single User Mode taking over 36 hours

Question

Hello:

After receiving an error message sayin theie may be corruption in the database I decided to do a repair. I ran the script with:

ALTER DATABASE PRODUCTION
SET SINGLE_USER;
GO

Started on Saturday morning and here is it on Sunday afternoon and here it is on Sunday afternoon and it is still "Executing query". I'm assumiong if I stop the query now it will be a disaster. The .mdf file is around 11gb in size.

The error that caused me to start thei process was error 824. I planned on running DBB CHECKDB after I put into single user mode.

TIA

Answer 1

If that is all you say, the command will wait until there are no processes running in the database.

You will either have to chase around manually to kill them, and this is what you want to do, if you are anxious that you may will something important.

Or you can be a little more brutal:

ALTER DATABASE Production SET SINGLE_USER WITH ROLLBACK_IMMEDIATE

This will kill all processes that has this DB as its current database, and usually returns within a couple of seconds. However, if a process is running code outside the SQL Server instance, for instance on a linked server, it will not notice that has been asked to exit.

You can cancel the command you have running. There will be no damage done.

When it comes to the corruption, keep in mind that corruption does not come out of the blue, but it often due to hardware issues or issues in the I/O subsystem. Unless you know there has been some special event that may have caused this, you should consider the current setup as tainted.

Answer 2

Thanks for your reply. I'm pretty sure the corruption came about from a file level restore after a ransomware attack.

This is what cam back after I ran CHECKDB

Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:700540) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:700541) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 156579646, index ID 0, partition ID 10261603680256, alloc unit ID 10261603680256 (type In-row data), page (1:700539). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -1.
Msg 8928, Level 16, State 1, Line 1
Object ID 156579646, index ID 0, partition ID 10261603680256, alloc unit ID 10261603680256 (type In-row data): Page (1:700539) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 156579646, index ID 0, partition ID 10261603680256, alloc unit ID 10261603680256 (type In-row data): Page (1:700540) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 156579646, index ID 0, partition ID 10261603680256, alloc unit ID 10261603680256 (type In-row data): Page (1:700541) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'Change_History' (object ID 156579646).
CHECKDB found 0 allocation errors and 6 consistency errors in database 'PRODUCTION'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PRODUCTION).

Answer 3

You should find a clean backup to restore from; one taken before the ransomware attack, and apply transaction logs from there.

If you for reason do find that feasible, by all means take a backup of the current database before you do anything else! I would recommend that you restore the database elsewhere, and run CHECKDB with REPAIR_ALLOW_DATA_LOSS on that copy. This option does what it says: it basically throws everything away it can't piece together, even if that is your most important data.

Answer 4

Thanks very much!! Full backup started.