I've blogged about problems with database snapshots a few times previously (see my Database Snapshots category) but here's a new one that I've been meaning to blog for a while that you need to know about. One of the cool uses of database snapshots is to be able to create one, make a bunch of [...]
Paul S. Randal
In Recovery...
Bug: reverting from a database snapshot shrinks the transaction log to 0.5MB
Posted on: April 17, 2012 9:43 am
Using database snapshots
Posted on: July 26, 2011 10:59 am
Three weeks ago I kicked off a survey about using database snapshots – see here - because I don't really know how much they're used or not out in the wild. Here are the results: The "Other" values are: 13 x "No – not at the moment." 10 x "In our test environment for reverting [...]
Survey: do you use database snapshots?
Posted on: July 4, 2011 11:31 am
As I was writing a quick SQL Server Magazine blog post on database snapshots while waiting for my coffee to brew, I figured it would make an interesting survey to see whether people are making use of database snapshots at all. I don't expect to see much activity on this until tomorrow as it's July [...]
TechNet Magazine: May 2010 SQL Q&A column
Posted on: May 9, 2010 12:46 pm
The May edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: How to change the file locations for a user database The difference between page latches and page I/O latches Why database snapshots are not a good substitute for [...]
A SQL Server DBA myth a day: (30/30) backup myths
Posted on: April 30, 2010 9:19 am
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) The month is finally over so time for the grand [...]
A SQL Server DBA myth a day: (2/30) DBCC CHECKDB causes blocking
Posted on: April 2, 2010 4:55 am
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) It's the last day of our class here in Boston [...]
Misconceptions around database snapshots and transaction rollbacks
Posted on: January 7, 2010 11:03 am
This is a quick post to clarify an article I saw on SQLServerCentral this morning that seemed to state that transaction rollbacks push data into database snapshots. This is absolutely not true. A database page is copied into a database snapshot before it is changed in the source database. Although the mechanism is commonly called copy-on-write, [...]
SQL Server 2008 High Availability whitepaper published on MSDN
Posted on: September 28, 2009 12:42 pm
The 35-page whitepaper on high availability I wrote for the SQL team over the summer has been published on MSDN. It's a 2-300 level whitepaper that describes the various high-availability technologies in SQL Server 2008 and how they can be used to mitigate disasters. It's chock-full of links to other whitepapers, technical articles and Books [...]
Issues around DBCC CHECKDB and the use of hidden database snapshots
Posted on: June 15, 2009 12:50 pm
There are a couple of issues that I've heard of in the last few weeks (one while onsite at a customer) and I think they might bite some people so I'd like to share them with you. DBCC CHECKDB in 2005 onwards uses a hidden database snapshot to create the transactionally-consistent point-in-time view of the database that [...]
Do transactions rollback when DBCC CHECKDB runs?
Posted on: May 4, 2009 4:04 pm
Recently there’s been a spate of people noticing strange behavior from active transactions when DBCC CHECKDB (or any of the other DBCC consistency checking commands run). For example, I’ve create a database call DbccTest with a single table. In one connection I do: BEGIN TRAN INSERT INTO t1 VALUES (1, 1); GO And in another [...]
CHECKDB From Every Angle: SQL and Windows bugs around database snapshots
Posted on: March 10, 2009 1:10 pm
As you may know, DBCC CHECKDB (and some of the other DBCC CHECK*) commands use an internal database snapshot to get a transactionally consistent view of the database (if you didn't, see my blog post CHECKDB From Every Angle: Complete description of all CHECKDB stages for an explanation). It's entirely possible that the database snapshot may [...]
New whitepaper on database snapshots
Posted on: February 15, 2009 1:57 pm
Well, it was released a year ago but I only just found out about it last week. The SQL CAT team published a whitepaper last year called Database Snapshot Performance Considerations under I/O-Intensive Workloads. For a variety of workloads and with different numbers of concurrent database snapshots, it covers: How much time does it take to create a [...]
CHECKDB bug that people are hitting – Msg 8967, Level 16, State 216
Posted on: May 22, 2008 10:20 pm
Before I start, I want to make it clear that you can only hit this bug if you ALREADY have corruption, that it's quite rare, and that there is a workaround. I've noticed a few more people in the forums having CHECKDB fail with this particular error in the last month Msg 8967, Level 16, [...]
Available for download: Our High-Availability hands-on labs and SQL Server 2008 JumpStart materials
Posted on: March 12, 2008 5:23 pm
A couple of weeks ago I blogged about the three tracks of the SQL Server 2008 JumpStart course that SQLskills.com taught internally for Microsoft and some MVPs – see here for details. Well, the content is now available to download! Note that this was based on CTP-5 (November 2007 CTP) and there have been *lots* [...]
Search Engine Q&A #14: Beware 3rd party file-system drivers with DBCC CHECKDB
Posted on: February 4, 2008 6:36 am
Here’s an issue that I thought was a one-off but it just popped up again over the weekend so I want to publicize it. DBA runs a DBCC CHECKDB and gets output like the following: Msg 5172, Level 16, State 15, Line 1The header for file ‘E:\Data\namechanged.mdf:MSSQL_DBCC14′ is not a valid database file header. The [...]
Database snapshots – when things go wrong
Posted on: January 30, 2008 4:26 am
This is a post I’ve been meaning to do for a while – detailing some of the problems you can run into when using database snapshots. Reverting to a snapshot Many people use snapshots as a way of protecting against mistakes during a complicated set of changes to a database – you can just revert [...]
Conference Questions Pot-Pourri #5: Database snapshots, and creating a snapshot of a single filegroup
Posted on: November 14, 2007 7:32 am
One of the sessions we did yesterday at IT Forum was an Instructor-Led Lab on database snapshots, an Enterprise Edition feature of SQL Server 2005. Database snapshots are not particularly well known in the DBA community and there are many misconceptions about them. The session generated a bunch of questions, some of which I’ll answer [...]
