March 29, 2011 at 3:37 pm
If I take a snapshot out of a 500gig DB, what would be the size of the snapshot ?
How do i accomodate for the size ?
If the snapshot size is 300Gig, And reporting is run out of that snapshot , i dont want to delete that snapshot and recreate another one coz people are not gonna liek it. So i have to create a new snapshot first and then delete the old one so that the users can query out of the new snapshot.
Is dis a proper approach ?
If it is, What shoudl be the Disk space on this server? Shoudl it be
Disk Space = (Size of the database + Twice the size of the snapshot )
My another important question is , When we create a DB, SQL Server assigns a Quota on the disk and grows it exponentially , Will the snapshot occupy place with in that region ? or it grows outside ?
Please look at the attachment for more detail.
If you cant open the word document , the screenshots are available as snapshots
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
March 29, 2011 at 8:28 pm
The snapshot is written to completely separate files to the original database. SQL Server uses Windows sparse files to manage the data in the files used for the snapshot. If you look at the file properties using Windows Explorer, you should see that the file size is the same as the equivalent original database file but the space used on disk is, typically, much less. This is because SQL Server only need to write to the snapshot database when a page in the original database changes. So, initially, the size of the snapshot is close to zero bytes (from memory, the minimum size if 64k of meta data only). As INSERT/UPDATE/DELETEs are done, the "Before Image" pages affected are written to the snapshot. It is unlikely that every page in a database is changed, hence, the actual size of a snapshot database is less than the original.
In reality, you might actually be able to get away with disk space less than double the original database but this may get you into trouble.
As for how a database grows - you allocate space to each database file (MDF, NDF's and LDF's). SQL Server will use the space in the files as required. You can tell SQL Server that when a file fills up, whether it can grow and by how much. The "how much" can be specified as a fix number of bytes or a percentage. If your files are growing exponentially, you have probably specified a precentage grow. Additionally, if the files are needing to grow, you really should be checking what the size of the database files really should be. As a general rule, I recommend that a database file should NEVER grow during the period of main activity. You should be managing the file size and when it is neccesary to increase it's size, schedule this to be done at a quiet time.
March 29, 2011 at 9:49 pm
happycat59 (3/29/2011)
As a general rule, I recommend that a database file should NEVER grow during the period of main activity. You should be managing the file size and when it is neccesary to increase it's size, schedule this to be done at a quiet time.
Thanks for taking the time to answer my questions. Your answers make sense. I was wondering, what kind of consequences we would face if we WAIT until SQL Server decides to grow the data file size ?
Thank You
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
March 30, 2011 at 5:27 am
When the files grow, your users will experience delays because SQL Server will not be able to complete the transaction(s) that were in progress until the file growth has completed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply