SQL Server Performance Forum – Threads Archive

Snapshots

I believe in SQL Server 2005, we could create snapshots of database. I was wondering, if my DB size is 20GB and when i create a Snapshot, will i require to have 20GB of free space? Thanks,

BOL refers

quote:
The underlying backup technology creates an instantaneous copy of the data that is being backed up. The instantaneous copying is typically accomplished by splitting a mirrored set of disks or by creating a copy of a disk block when it is written. This preserves the original. At restore time, the original is made available immediately and synchronization of the underlying disks occurs in the background. This results in almost instantaneous restore operations.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

No, you don’t need to have the 20 GB free space…
http://msdn2.microsoft.com/en-us/library/ms190677.aspx A database snapshot is created like a typical database with a CREATE DATABASE statement; there is the additional specification of a source database as a snapshot in the statement. When the snapshot is created, a sparse file is created. This file (which is used only on NTFS volumes) initially has no disk space allocated to itâ€”even though if you view the size of the file in Windows Explorer, it will look like it is the same size as the original source database file. The size on disk for that file is close to zero.
The database snapshot initially reads the data files from the source database. As data changes in the source database, the database engine copies the original data pages from the source database to the snapshot database. This technique ensures that the snapshot database will only reflect the state of the data that existed at the time the snapshot was taken. When a SELECT statement is issued against a database snapshot, no locks are ever issuedâ€”regardless of whether the data page being read is located in the source database data file or the snapshot database data file. Because no locks are issued on the read-only database snapshot, snapshots are a great candidate for reporting solutions. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I think there will be a change on the disk space, I will test it and come back here. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Database Snapshots in SQL Server 2005
http://www.sqlservercentral.com/columnists/akhosla/2733.asp
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The snapshot is initially created as a "sparse" file. As data changes in your source database and you take snapshots your snapshots will only grow the amount of changes in between snapshots. Take a snapshot at 9am, it will start out with a small near 0-byte file. If your source database grows 5MB between 9am & 9:30am, you’re snapshot will grow 5MB in the same timeframe. Database snapshots only reflect the data that has changed since the snapshot was taken. This has to be taken into consideration – any queries looking for data that has not been inserted or modified since the snapshot was taken will go to the source database for its I/O requests (this can be a burden on the source db if you’re trying to move reporting off of an OLTP (type) of server). This is why MS reccomends setting up a "Source -> Mirror -> Snapshot" if you want to offload reporting properly and minimize the risk of affecting the source database.

can this snapshot be used for system databases as well??

No.
Snapshots of the model, master, and tempdb databases are prohibited.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Haywood
Thats a good one to understand, but somehow I can still see the change in disk space if I perform this fashion of snapshot backups. Including above system databases they are not allowed on Reporting services system databases too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Snapshots will take up the same amount of disk space as the primary copy of the database. If your primary db is 20gb, then you will need 20gb free space to create a snapshot.

If you are talking about database snapshot replication then yes it needs 20 GB….
But you are talking about Database snapshots then you don’t need…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I have had more confirmation from Paul Randal, MSFT during the Tech-ed (observe the bold captions): As you are aware database Snapshots operate at a data-page level and the original page is copied from the source database using the copy-on-write operation. Obviously the updates to the database subsequently will not have any affect or changes to the snapshot. As these pages (snapshot) are stored in sparse files (an NTFS feature) which are empty files that contain no user data and do not have disk space for user data allocated to it as yet. However, sparse files can grow in size and occupy disk space as the snapshots get stored into it. So on the basis of the growth rate of the snapshot and the disk space available in the sparse file. If the disk drive fills up the write operations to all snapshots will fail. Hence you should study the typical update patterns for the database and plan the amount of space required and also plan for the lifespan of the snapshot.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

quote:Originally posted by boyd0029 Snapshots will take up the same amount of disk space as the primary copy of the database. If your primary db is 20gb, then you will need 20gb free space to create a snapshot.

That is not correct, they will not take up 20GB. But I agree with Satya, they are not near 0-bytes, they do take up some space when first created.

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Thanks Tom, I had performed testing on my environment and can see the changes within the disk free space where we are storing the database backups. Paul Randal explained the whole process clearly as my reply above states. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

Actually for a 4Gb database my snapshot files are 320Kb when created, so it depends really on your definition of "near zero", I guess.

Read BOL topic "Understanding Sparse File Sizes in Database Snapshots " to know more about the snapshot file size..
A database snapshot uses one or more sparse files to store data. During database snapshot creation, sparse files are created by using the file names in the CREATE DATABASE statement. These file names are stored in sys.master_files in the physical_name column. Note:
In sys.database_files (whether in the source database or in a snapshot), the physical_name column always contains the names of the source database files. Sparse files are a feature of the NTFS file system. Initially, a sparse file contains no user data, and disk space for user data has not been allocated to it. For general information about the use of sparse files in database snapshots and how database snapshots grow, see How Database Snapshots Work. When first created, a sparse file takes up little disk space. As data is written to the sparse file, NTFS allocates disk space gradually. Potentially, a sparse file can grow very large. If a database snapshot runs out of space, it is marked as suspect, and it must be dropped. The source database, however, is not affected; actions on it continue normally. Sparse files grow in 64-kilobyte (KB) increments; thus, the size of a sparse file on disk is always a multiple of 64 KB. The latest 64-KB increment holds from one to eight 8-KB pages, depending on how many pages have been copied from the source database. This means that, on the average, the size of a sparse file slightly exceeds the space actually filled by pages MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks for the answers. But i still have some more simple questions: I have a source database and i created a snapshot, let’s say on friday 6:00 PM. If my source database grow’s in size, will my snapshot follows? If I dont delete my snapshot from the server, will it consume any resources? what’s the snapshot database life on the server? I.E, will it expries after X time? My source ZZ database is running on server A, But i would like to create a snapshot of ZZ on target server called B. Is this possible? If yes, how? Appreciated if someone please advice. Many thanks,
Cali

I think you haven’t completely read the references above including the BOL topic on snapshots.
– Snapshots will not grow as you cannot update them, you have to perform another snapshot schedule.
– Nothing as such but due the nTFS technology over a period of time it might have problem with disk space.
– No expiration option I think, you have to drop it explicitly or having a scheduled job for this will be ideal. Any database snapshots on a database must be dropped before the database can be dropped.
– SNAPSHOT syntax as per BOL CREATE DATABASE database_snapshot_name
ON
(
NAME = logical_file_name,
FILENAME = ‘os_file_name’
) [ ,…n ]
AS SNAPSHOT OF source_database_name
[;]
.. you can opt any name, provided you can recognize is properly.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

1.) Yes. The snapshot has a catalogue image file, when a datapage changes in the source db (from the point in time the snapshot was created) SQL writes an image of the original datapage (without changes) to the snapshot. *Subsequent changes to that datapage on source db are ignored by the snapshot*. The snapshot maintains a catalogue of changed pages for datapages that have changed from the source db from the time the snapshot was created. So you are maintaining a point in time version of the source database from the time the snapshot was created. 2.) Yes, for every change in an original datapage from the source db from the time the snapshot is created a change occurs in the snapshot – in theory once you have changed/modified every origional datapage in the source database then you wouldn’t have any subsequent changes.
Snapshot life is as long as you want to keep it (or until it is no longer needed or there is something you are going to do that requires you to drop it) – but you can only have one snapshot per source db, and it reflects only one point in time. 3.) The snapshot must reside on the same SQL instance (Server) as the source, but you can mirror of the source db on another SQL instance (server) and create a snapshot against that instance. FWIW – you should understand the impact of a snapshot, and know what happens when you revert a snapshot before you use it. You cannot bu, restore, or detach a snapshot. You cannot drop, detach or restore the source db when a snapshot exists. The snapshot does not contain a transaction log. If you do revert a snapshot the transaction log is rebuilt, which breaks the log chain, in other words you cannot restore subsequent backups for db after reverting a snapshot. Snapshots are useful for certain limited situations – but they aren’t a bu.

Hello Satya, Thanks for your email. I did read the thread and since they were back and fourth answer’s, I thought to clear myself again.. But the other question i had was: My source ZZ database is running on server A, But i would like to create a snapshot of ZZ on target server called B. Is this possible? Appreciated if you could please answer this. Thanks again Satya.
Cali

Ok, i got it bit wrong [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Read CG’s reply on 3rd point in this regard. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya Ok, i got it bit wrong [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Read CG’s reply on 3rd point in this regard. <hr height="1" noshade id="quote"></blockquote id="quote"> Which was also described by me in the 6th post… And yes, you can call the snapshot database whatever you want. It does not have to be the same name as the source database.

quote:Originally posted by CG2000 2.) – but you can only have one snapshot per source db, and it reflects only one point in time.

Actually, you can have multiple snapshots of the same database. I’ve done it in order to provide a ‘rolling snapshot history’ for the reporting apps. I kept the last four snapshots (one taken each hour) available. The calling application deals with the disconnect and uses a snapshot_name table to retrieve the list of currently available snapshots and the user decides which one they want to use.

Haywood; "Actually, you can have multiple snapshots of the same database." Interesting, I have only maintained one at a time, as I understood that is all you could have. I don’t use them very often, mostly just for testing or if there is a change that could impact the db. Being able to have multiple snapshots on a db makes them more useful as a tool. Thanks!

What I do is name my snapshots as follows: <SourceDBName>_Snapshot_<TimeStamp> and leave the last four snapshots available. The process that manages the snapshots is dynamic and can be updated for any # of snapshots to keep via a parameter for each database participating in it’s own snapshots.

Looks like I was spreading more lies about reverting snapshots and recovering information <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Not really restoring a transaction log, but a way of salvaging more tranaction log data. BOL 2005 states: – Optionally, with the full recovery model, if you backed up the transaction log before reverting (see step 3 earlier in this procedure), you might be able to reconstruct some of the lost data that was inserted into the database since the snapshot to which you reverted was created. For example, to reconstruct lost data after reverting: Begin by re-creating an offline copy of the pre-error database on a different server. If you are using Microsoft SQL Server 2005 Enterprise Edition, you can create this offline pre-error copy by restoring the damaged database to a point in time just before the error (for more information, see How to: Restore to a Point In Time (SQL Server Management Studio) or How to: Restore to a Point in Time (Transact-SQL)). At your discretion, you can then salvage data added to the database after the database snapshot’s creation by manually bulk exporting the data from the offline copy of the prerevert source and bulk importing the data into the current source database (the reverted database). For more information, see Importing and Exporting Bulk Data. After you have salvaged data from before the table was dropped, you can drop the offline copy of the pre-error database and create an offline copy of the database to the point at which you backed up the tail of the log. Then you can salvage data added to the database after the error occurred. –

"What I do is name my snapshots as follows: <SourceDBName>_Snapshot_<TimeStamp> and leave the last four snapshots available. The process that manages the snapshots is dynamic and can be updated for any # of snapshots to keep via a parameter for each database participating in it’s own snapshots." The process you describe is an application/SPROC you have written to create a 4 snapshots per hour? Or are you refering to the reporting/calling application?

quote:Originally posted by CG2000 The process you describe is an application/SPROC you have written to create a 4 snapshots per hour? Or are you refering to the reporting/calling application?

It’s a procedure written by me to be executed as a job to manage the snapshots. However, the reporting application had to be recoded to take into account the snapshot management ‘subsystem’ I created.
It’s one snapshot per database per hour and is configurable for a # of snapshots, the default being four.

]]>