Questions[dbcc](server)

We have a mix of SQL 2000, 2005, and 2008 servers, and we've always run a DBCC CHECKDB nightly just before the full backups, under the theory that you want to make sure the DB is in good shape before you back it up. (Obviously, full verification of backups can only be done via test restores, but thats a slightly different topic.)

Assuming that I can't offload the DBCCs to a backup server or something (which would be ideal), is DBCC CHECKDB followed by FULL BACKUP the best sequence?

The only "best practices" document I found that discuss this was a Best Practices for SQL Server Maintenance for SAP from 2006 I found on TechNet:

Ideally, a consistency check using DBCC CHECKDB sould be run before performing an online database backup.

Is this advice correct? Is it correct for all versions of SQL?

(In case this helps, part of the motivation for asking this is that the DBCC runtime seems to vary a fair amount from night to night, so we can't rely on exactly when the backups will be complete, which makes scheduling our tape archive jobs difficult. Also, if maintenance runs long and has to be cancelled for any reason, I'd much rather that the backup be reliably complete than the DBCC.)

Do I need to be sysadmin to run DBCC commands on SQL Server? Or is db_owner rights enough?

I received a notice that one of our servers (SQL Server 2008 Cluster on Win Enterprise 2008 Cluster) DBCC CHECKDB for all databases has been failing the past four times it has been run. We don't have any data prior to that, but it doesn't look like it has been succeeding for awhile. There are no errors in the log file only:

DBCC results for 'sys.sysxmlfacet'. [SQLSTATE 01000]
Msg 0, Sev 0, State 1: Unspecified error occurred on SQL Server. Connection may have been     terminated by the server. [SQLSTATE HY000]
There are 112 rows in 1 pages for object "sys.sysxmlfacet". [SQLSTATE 01000]

I ran a DBCC CHECKDB using sp_MSForEachDB to get more accurate results and had the same error on the same DB but at a separate point:

DBCC results for 'NameValuePair_Greek_CI_AS'. [SQLSTATE 01000]
Msg 0, Sev 0, State 1: Unspecified error occurred on SQL Server. Connection may have     been terminated by the server. [SQLSTATE HY000]
There are 0 rows in 0 pages for object "NameValuePair_Greek_CI_AS". [SQLSTATE 01000]

Also, the error-log states that the DBCC completed without errors for this database.

I can't figure out how to track down this ambiguous issue that only happens on this database out of the dozens on this server. Any help is appreciated!

Edit to answer comment:
It is being run locally via SQL Server Agent. The only thing we thought it might be is since it is on a Win2008 cluster that it might be failing over since the db is too large right now (880GB, but not as large as many other db running same job on same hard/software), but we have nothing to show a failover is occurring which takes out connectivity issues. A failover usually sends off so many flags and warnings they're hard to miss. Breaking up the db might be our next troubleshooting measure, but until then we are just trying to understand why this job is running so oddly.

We have a SQL 2000 DB. The server crashed due to Raid array failure. Now when we run DBCC CHECKDB, we get an error that there are 27 consistency errors in 9 pages.

When we run DBCC PAGE on these pages, we get this:

Msg 8939, Level 16, State 106, Line 1
Table error: Object ID 1397580017, index ID 2, page (1:8404521). Test (m_freeCnt == freeCnt) failed. Values are 2 and 19.
Msg 8939, Level 16, State 108, Line 1
Table error: Object ID 1397580017, index ID 2, page (1:8404521). Test (emptySlotCnt == 0) failed. Values are 1 and 0.

Since the indicated index is non-clustered and is created by a unique constarint that includes 2 columns, we tried dropping and recreating the index. This resulted in the following error:

CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 2. Most significant primary key is '3280'. 
The statement has been terminated. 

However running

Select var_id,result_on
from tests
group by var_id,result_on
having count(*)>1

returns 0 rows.

Here is what we are planning to do:

• Restore a pre-server crash copy of the DB and run DBCC CHECKDB
• If that returns clean, then restore again with no recovery
• Apply all subequent TLOG backups
• Stop production app, take a tail log backup and apply that too
• Drop prod DB and rename the freshly restored DB to make it prod
• Start prod app

Could someone please punch holes in this approach? Maybe, suggest a different approach? What we need is minimum downtime.

SQL 2000 DB Size 94 GB The table that has corrupt pages has 460 Million+ rows of data

Thanks for the help.

Raj

I am running SHRINK file on a 600G data file.

Currently, status is reported as "suspended" and sys.dm_exec_requests.percent_complete for DbccFilesCompact command reports that it is running (but very slowly)

Is there a way to check why it's being suspended and how to make it run smoother?

FYI - SQL Query for Checking Status

select T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
       , R.cpu_time, R.total_elapsed_time, R.percent_complete
from   sys.dm_exec_requests R
       cross apply sys.dm_exec_sql_text(R.sql_handle) T
order by Command