April 17, 2013 at 9:49 am
I have seen some error messages related to I/O in sql logs. Can i take a snapshot of existing database on same server different drive, would that be equivalent to DBCC on the actual db?
April 17, 2013 at 10:08 am
This http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/10/dbcc-hidden-snapshot.aspx answered my question.
However i am still not clear what 'Hidden Snapshot' means?
April 17, 2013 at 10:35 am
Yes, you can take a snapshot of a database on a different drive and CheckDB that. It won't have any effect at all on the IO load on the database drive, but you can do it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2013 at 10:36 am
curious_sqldba (4/17/2013)
However i am still not clear what 'Hidden Snapshot' means?
Exactly that. It's a database snapshot that's hidden from view (not in the system tables)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2013 at 12:09 pm
GilaMonster (4/17/2013)
curious_sqldba (4/17/2013)
However i am still not clear what 'Hidden Snapshot' means?Exactly that. It's a database snapshot that's hidden from view (not in the system tables)
So i am able to see a entry in sys.databases when i take a snapshot. Are there any other "hidden snapshots" enabled apart from manual process? I am asking this because i would like to do similar on standard edition instances.
April 17, 2013 at 1:58 pm
CheckDB always creates a snapshot, on all editions, it's in the same directory as the database files are and that cannot be changed. Only on Enterprise edition can you manually create a snapshot.
Why are you considering running CheckDB against a manually created snapshot anyway?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2013 at 2:18 pm
GilaMonster (4/17/2013)
CheckDB always creates a snapshot, on all editions, it's in the same directory as the database files are and that cannot be changed. Only on Enterprise edition can you manually create a snapshot.Why are you considering running CheckDB against a manually created snapshot anyway?
Because DBCC checkdb will place locks on the tables. If is creating a snapshot anyways, then why would it lock the tables?
April 17, 2013 at 2:29 pm
curious_sqldba (4/17/2013)
GilaMonster (4/17/2013)
CheckDB always creates a snapshot, on all editions, it's in the same directory as the database files are and that cannot be changed. Only on Enterprise edition can you manually create a snapshot.Why are you considering running CheckDB against a manually created snapshot anyway?
Because DBCC checkdb will place locks on the tables. If is creating a snapshot anyways, then why would it lock the tables?
CheckDB doesn't lock the tables.
The only time CheckDB will take locks on the tables is when it's run with the TABLOCK option or if it can't create a database snapshot. In the first case, remove the tablock option, in the second figure out what's preventing the snapshot from being created
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2013 at 10:13 am
It used to take table locks in 7.0 - from 2000 onwards it's been online by default, with no table locks.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 18, 2013 at 10:49 am
Thanks. Post corrected accordingly (I never worked with SQL 7)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2013 at 10:52 am
Paul Randal (4/18/2013)
It used to take table locks in 7.0 - from 2000 onwards it's been online by default, with no table locks.
Thanks Paul. If DBCC doesn't place any table locks, why do we need to take snapshot and then run DBCC over a snapshot?
April 18, 2013 at 10:57 am
curious_sqldba (4/18/2013)
Paul Randal (4/18/2013)
It used to take table locks in 7.0 - from 2000 onwards it's been online by default, with no table locks.Thanks Paul. If DBCC doesn't place any table locks, why do we need to take snapshot and then run DBCC over a snapshot?
You don't.
CheckDB will take its own snapshot (the hidden database snapshot) and run against that. It does so specifically to remove the need to take locks (or to run a kind of in-memory recovery as it used to on SQL 2000)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2013 at 11:04 am
You don't. Only if you want to control where the snapshot goes do you need to do that.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 18, 2013 at 11:07 am
Paul Randal (4/18/2013)
You don't. Only if you want to control where the snapshot goes do you need to do that.
So i can run DBCC CHeckdb ('MyDB') on standard edition and it will still not place any table locks? Thank you for your explanation.
April 18, 2013 at 11:10 am
Correct. Although manual use of database snapshots is only in Enterprise Edition, DBCC's use of them is in every edition down to Express.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply