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

Improve this question
2
  • 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 - AWS
    Mar 8, 2016 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 - AWS
    Mar 9, 2016 at 1:19

1 Answer 1

Reset to default
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

Improve this answer
1
  • 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, 2016 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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