Database is suspect mode but CheckDB reports no errors

[Wiseguy2001]

This is a weird one, a client of mine restarted they're server to install some updates. When it booted up their main database was in suspect mode. The strange bit is that when I run CHECKDB (with ALL_ERRORMSGS) I get this:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'OrderApp'.

I don't see what is going on here, it's checked out fine but still won't mount.

Any ideas?

 

[Wiseguy2001]

Don't worry, I can access the data in emergency mode and then merge today's transactions with a backup.

Again, windows update rears it's ugly head

 

[mikeblas]

What's Windows Update got to do with it?

 

[Wiseguy2001]

It forced a server restart while it was in use (there's a number of people complaining about database not coming back up after updates). Luckily they got the batch of invoices printed before or they would have missed their delivery run.

I'm switching the updates back to manual, going without updates for a month isn't going to hurt as it is pretty locked down.

Anyway, It's all sorted now. Just something I can do without.

 

[mikeblas]

What does the SQL Server error log say about shutdown?

 

[Kiznilian]

This is a script we run on our suspect DB's at my company

USE master

GO

--set database to single user mode

EXEC sp_dboption 'DB NAME', 'single user', 'true'

GO

--set database to emergency mode

ALTER DATABASE DBNAME SET EMERGENCY;

GO

--recover database

DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

GO

--set database to multi user mode

EXEC sp_dboption 'DB NAME', 'single user', 'false'

GO

Obviously change out DBNAME to the database name. When I first see a suspect DB I will usually detach re-attach first. If still suspect I then try this.

 

[calebb]

This is a script we run on our suspect DB's at my company

USE master
GO
--set database to single user mode
EXEC sp_dboption 'DB NAME', 'single user', 'true'
GO
--set database to emergency mode
ALTER DATABASE DBNAME SET EMERGENCY;
GO
--recover database
DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
--set database to multi user mode
EXEC sp_dboption 'DB NAME', 'single user', 'false'
GO

Obviously change out DBNAME to the database name. When I first see a suspect DB I will usually detach re-attach first. If still suspect I then try this.

Seriously? Do you investigate which tables are affected before/after you run those statements? For example, if data pages from tables containing financial data are affected, you could lose data. If your accounting department has made payments based on that data, you will no longer have sufficient data to support the payment in an audit. If you don't investigate before you run those statements, you won't even know the scope of your problem... and when you start rotating your backups and transaction logs, you'll be discarding your only copies of this data.

When I encounter a consistency/allocation error, my first step is to check which tables the affected pages are in. If the pages are important, it's easy enough to recover them from transaction logs or recent backups.

 

[HardUp4HardWare]

QFT,
Sure sign that someone has no recovery plan when they run this and look the other way.

Had a DBA here run the same on our DB and I asked our people about this, nobody cared, they all just wanted to go back to bed.

Mind you, our DB is not financial or heavily transactional, but man, you just can't play russian roulette like that.

Oh well.

Seriously? Do you investigate which tables are affected before/after you run those statements? For example, if data pages from tables containing financial data are affected, you could lose data. If your accounting department has made payments based on that data, you will no longer have sufficient data to support the payment in an audit. If you don't investigate before you run those statements, you won't even know the scope of your problem... and when you start rotating your backups and transaction logs, you'll be discarding your only copies of this data.

When I encounter a consistency/allocation error, my first step is to check which tables the affected pages are in. If the pages are important, it's easy enough to recover them from transaction logs or recent backups.

 

[Kiznilian]

Never had a problem with lost data. We have a thousand or so SQL databases running. Sometimes a database goes suspect. and I am curious as to how you guys would fix a torn page/index issue without repairing it? I know the ALLOW_Data_Loss is the scary thing to you but what other way could you do it?

 

[calebb]

Here's an overview:

http://blogs.msdn.com/b/sqlserverst...ges-using-page-restore-or-manual-inserts.aspx

This assumes you're using an enterprise edition of SQL Server with support for page restores... there are other articles to show you the (less granular) options to handle these operations with standard edition.

 

[Wiseguy2001]

What does the SQL Server error log say about shutdown?

I went through the logs and although there was some updates applied @ 8:15am, there was some more update entries @ 8am with an unclean shut-down in the middle of this. I know the director of the company is not a patient man, and they're playing dumb to the 8am updates/ restart

Seriously?

Yeah, REPAIR_ALLOW_DATA_LOSS is like using a sledge hammer when you lost your car keys. I'd be very careful about using this and only as a last resort. Plus use ALL_ERRORMSGS so you know what it 'fixed'.