2

I have a 25TB db (SQL2008 Enterprise SP4) that appears to not have had CHECKDB run on it in production. No idea if it was run against a restored backup somewhere at sometime. Right now I don't have space to restore a copy on another server connected to the same storage.

I need to be able to run checkcatalog, checkalloc and checktable but even for small tables it doesn't appear to be able to create the internal sparse file dbcc snapshot.

So I was wondering if I can create a SQL database snapshot on another drive and then run those commands against that and then drop the snapshot db once complete.

Thanks!

--Todd

  • DBCC CHECKDB creates a internal snapshot already, and if you create a snapshot in prod it will just use the same underlying resources and SQL Server instance. Have you checked out Paul Randal's blog on CheckDB? He wrote it and has several suggestions: sqlskills.com/blogs/paul/… Here's one on how to make it faster suggesting features such as using 'physical only' sqlperformance.com/2012/11/io-subsystem/… Do you not have anywhere you can restore this even across a network? – Ali Razeghi Mar 8 '16 at 23:20
  • Backup filegroups an option? I'm concerned 25TB of checkDB work against a snapshot in Prod might cause other resource issues. – Ali Razeghi Mar 9 '16 at 1:19
7

I think Robert Davis answered your question in two posts:

How to manually create a database snapshot and get SQL Server to use it for CHECKDB:

http://www.sqlsoldier.com/wp/sqlserver/day1of31daysofdisasterrecoverydoesdbccautomaticallyuseexistingsnapshot

Performance comparison of CHECKTABLE commands run against database vs manually created snapshot w/ different options:

http://www.sqlsoldier.com/wp/sqlserver/day15of31daysofdisasterrecoveryrunningdbccchecktableinparalleljobs

  • 1
    Yes, absolutely, you can put the snapshot on a different drive and run your DBCC CHECKxxx processes against it. – Robert L Davis Mar 9 '16 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.