Problem solve Get help with specific problems with your technologies, process and projects.

Database Snapshots: Creating read-only copies of a database

Database Snapshots feature in SQL Server 2005 Enterprise Edition creates read-only files. For these separate databases, our site expert Adam Machanic explains the functioning of locks.

I'm interested in using SQL Server 2005's Database Snapshot feature offered by the Enterprise Edition. I want to create a read-only copy of a database at a point-in-time for reporting purposes. I've read that for unchanged data, reads are transparently redirected to the primary database and for changed data the reads hit a sparse file.
My question is related to locking. One of the primary reasons we want to have a separate database for read-only reporting is that we do not want reads to be going directly against our production database. When reading unchanged data from a snapshot, are read locks placed on the primary database? If so, are these locks the same as the locks placed if I had directly read from the primary database? If the locking is the same for unchanged data, using snapshots in my eyes may result in similar locking issues. If this is the case, the feature has lost some of its luster.
When querying a database snapshot, there are no locks taken in the source database. It is quite easy to test this in order to verify. First, create a snapshot of a large database on your system. Next, run a complex and time-consuming query against the snapshot -- the kind of query that you definitely do not want holding locks! While the query is running, you can monitor locking using the sp_lock system stored procedure. You should notice that locks are taken in the snapshot database, but not in the source database.

Dig Deeper on SQL Server Replication

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.