|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Snapshot sizeI created a snapshot of AdventureWorks.
However, when I inspect the snapshot database file, it says size: 179MB size on disk: 192 KB What is that mean? Do you ask why the size on disk is small? This is the way snapshots work.
When you create a database snapshot it is almost an empty file and the snapshot really points to the pages of the source database. When a page in the source database is updated the snapshot saves the original page. So, as the original database is updated the snapshot file will also grow in size. Hope this helps, Ben Nevarez Senior Database Administrator AIG SunAmerica Show quote "Alan T" wrote: > I created a snapshot of AdventureWorks. > However, when I inspect the snapshot database file, it says > size: 179MB > size on disk: 192 KB > > What is that mean? > > > Hi,
Still not fully understand. Please comment if I am wrong: 1) If I add/modify records in a table, the snapshot will save the modified page only 2) Any further modifications to the same/other pages, snapshot saves the modified pages also So the snapshot seems to work like the differential database backup? Show quote "Ben Nevarez" <BenNeva***@discussions.microsoft.com> wrote in message news:C79415A4-010A-4F9A-BFC4-726B1F09F2CF@microsoft.com... > > Do you ask why the size on disk is small? This is the way snapshots work. > When you create a database snapshot it is almost an empty file and the > snapshot really points to the pages of the source database. When a page in > the source database is updated the snapshot saves the original page. So, as > the original database is updated the snapshot file will also grow in size. > > Hope this helps, > > Ben Nevarez > Senior Database Administrator > AIG SunAmerica > > > > "Alan T" wrote: > > > I created a snapshot of AdventureWorks. > > However, when I inspect the snapshot database file, it says > > size: 179MB > > size on disk: 192 KB > > > > What is that mean? > > > > > > If you change records and SQL Server updates a page on the online database,
the snapshot will keep a copy of the original page. If SQL Server updates the same page again on the online database that has no effect on the snapshot, the snapshot already has a copy of the original page. Hope this helps, Ben Nevarez Senior Database Administrator AIG SunAmerica Show quote "Alan T" wrote: > Hi, > > Still not fully understand. > Please comment if I am wrong: > 1) If I add/modify records in a table, the snapshot will save the modified > page only > 2) Any further modifications to the same/other pages, snapshot saves the > modified pages also > > So the snapshot seems to work like the differential database backup? > > "Ben Nevarez" <BenNeva***@discussions.microsoft.com> wrote in message > news:C79415A4-010A-4F9A-BFC4-726B1F09F2CF@microsoft.com... > > > > Do you ask why the size on disk is small? This is the way snapshots work. > > When you create a database snapshot it is almost an empty file and the > > snapshot really points to the pages of the source database. When a page in > > the source database is updated the snapshot saves the original page. So, > as > > the original database is updated the snapshot file will also grow in size. > > > > Hope this helps, > > > > Ben Nevarez > > Senior Database Administrator > > AIG SunAmerica > > > > > > > > "Alan T" wrote: > > > > > I created a snapshot of AdventureWorks. > > > However, when I inspect the snapshot database file, it says > > > size: 179MB > > > size on disk: 192 KB > > > > > > What is that mean? > > > > > > > > > > > > I think I am a bit clearer.
The snapshot keeps the page(s) that was/were before modified. So in order to keep safe of the original database, we should create or refresh the snapshot whenever we do a major modification to the original database? Show quote "Ben Nevarez" <BenNeva***@discussions.microsoft.com> wrote in message news:F3F5484A-46AA-40DF-9789-57DA51762BC6@microsoft.com... > > If you change records and SQL Server updates a page on the online database, > the snapshot will keep a copy of the original page. > > If SQL Server updates the same page again on the online database that has no > effect on the snapshot, the snapshot already has a copy of the original page. > > Hope this helps, > > Ben Nevarez > Senior Database Administrator > AIG SunAmerica > > > > "Alan T" wrote: > > > Hi, > > > > Still not fully understand. > > Please comment if I am wrong: > > 1) If I add/modify records in a table, the snapshot will save the modified > > page only > > 2) Any further modifications to the same/other pages, snapshot saves the > > modified pages also > > > > So the snapshot seems to work like the differential database backup? > > > > "Ben Nevarez" <BenNeva***@discussions.microsoft.com> wrote in message > > news:C79415A4-010A-4F9A-BFC4-726B1F09F2CF@microsoft.com... > > > > > > Do you ask why the size on disk is small? This is the way snapshots work. > > > When you create a database snapshot it is almost an empty file and the > > > snapshot really points to the pages of the source database. When a page in > > > the source database is updated the snapshot saves the original page. So, > > as > > > the original database is updated the snapshot file will also grow in size. > > > > > > Hope this helps, > > > > > > Ben Nevarez > > > Senior Database Administrator > > > AIG SunAmerica > > > > > > > > > > > > "Alan T" wrote: > > > > > > > I created a snapshot of AdventureWorks. > > > > However, when I inspect the snapshot database file, it says > > > > size: 179MB > > > > size on disk: 192 KB > > > > > > > > What is that mean? > > > > > > > > > > > > > > > > > > Yes, the snapshot keeps the original pages that have been changed on the
source database. Note that I used the term 'online' database before, perhaps 'source' database is a better description as we can also have snapshots of a mirror database. You create snapshots depending on your specific needs. Just keep in mind that as your source database changes your snapshot will also grow on size. Hope this helps, Ben Nevarez Senior Database Administrator AIG SunAmerica Show quote "Alan T" wrote: > I think I am a bit clearer. > The snapshot keeps the page(s) that was/were before modified. > > So in order to keep safe of the original database, we should create or > refresh the snapshot whenever we do a major modification to the original > database? > > > > "Ben Nevarez" <BenNeva***@discussions.microsoft.com> wrote in message > news:F3F5484A-46AA-40DF-9789-57DA51762BC6@microsoft.com... > > > > If you change records and SQL Server updates a page on the online > database, > > the snapshot will keep a copy of the original page. > > > > If SQL Server updates the same page again on the online database that has > no > > effect on the snapshot, the snapshot already has a copy of the original > page. > > > > Hope this helps, > > > > Ben Nevarez > > Senior Database Administrator > > AIG SunAmerica > > > > > > > > "Alan T" wrote: > > > > > Hi, > > > > > > Still not fully understand. > > > Please comment if I am wrong: > > > 1) If I add/modify records in a table, the snapshot will save the > modified > > > page only > > > 2) Any further modifications to the same/other pages, snapshot saves the > > > modified pages also > > > > > > So the snapshot seems to work like the differential database backup? > > > > > > "Ben Nevarez" <BenNeva***@discussions.microsoft.com> wrote in message > > > news:C79415A4-010A-4F9A-BFC4-726B1F09F2CF@microsoft.com... > > > > > > > > Do you ask why the size on disk is small? This is the way snapshots > work. > > > > When you create a database snapshot it is almost an empty file and the > > > > snapshot really points to the pages of the source database. When a > page in > > > > the source database is updated the snapshot saves the original page. > So, > > > as > > > > the original database is updated the snapshot file will also grow in > size. > > > > > > > > Hope this helps, > > > > > > > > Ben Nevarez > > > > Senior Database Administrator > > > > AIG SunAmerica > > > > > > > > > > > > > > > > "Alan T" wrote: > > > > > > > > > I created a snapshot of AdventureWorks. > > > > > However, when I inspect the snapshot database file, it says > > > > > size: 179MB > > > > > size on disk: 192 KB > > > > > > > > > > What is that mean? > > > > > > > > > > > > > > > > > > > > > > > > > > > How do I refresh the snapshot database whenever I modify the source database
so that the snapshot database has the 'before' state of the modified page? eg. Monday - modify the employee record of John Smith Tuesday - modify the employee record of John Smith Wednesday - modify the employee record of John Smith Thursday - modify the employee record of John Smith Friday - modify the employee record of John Smith If I don't 'refresh' the snapshot database, the snapshot database will still keep the 'Monday' state because the page is already there after I modified on Monday. Show quote "Ben Nevarez" <BenNeva***@discussions.microsoft.com> wrote in message news:E8286D1B-F5DB-4697-BE38-5D60C4734E18@microsoft.com... > > Yes, the snapshot keeps the original pages that have been changed on the > source database. Note that I used the term 'online' database before, perhaps > 'source' database is a better description as we can also have snapshots of a > mirror database. > > You create snapshots depending on your specific needs. Just keep in mind > that as your source database changes your snapshot will also grow on size. > > Hope this helps, > > Ben Nevarez > Senior Database Administrator > AIG SunAmerica > > > > "Alan T" wrote: > > > I think I am a bit clearer. > > The snapshot keeps the page(s) that was/were before modified. > > > > So in order to keep safe of the original database, we should create or > > refresh the snapshot whenever we do a major modification to the original > > database? > > > > > > > > "Ben Nevarez" <BenNeva***@discussions.microsoft.com> wrote in message > > news:F3F5484A-46AA-40DF-9789-57DA51762BC6@microsoft.com... > > > > > > If you change records and SQL Server updates a page on the online > > database, > > > the snapshot will keep a copy of the original page. > > > > > > If SQL Server updates the same page again on the online database that has > > no > > > effect on the snapshot, the snapshot already has a copy of the original > > page. > > > > > > Hope this helps, > > > > > > Ben Nevarez > > > Senior Database Administrator > > > AIG SunAmerica > > > > > > > > > > > > "Alan T" wrote: > > > > > > > Hi, > > > > > > > > Still not fully understand. > > > > Please comment if I am wrong: > > > > 1) If I add/modify records in a table, the snapshot will save the > > modified > > > > page only > > > > 2) Any further modifications to the same/other pages, snapshot saves the > > > > modified pages also > > > > > > > > So the snapshot seems to work like the differential database backup? > > > > > > > > "Ben Nevarez" <BenNeva***@discussions.microsoft.com> wrote in message > > > > news:C79415A4-010A-4F9A-BFC4-726B1F09F2CF@microsoft.com... > > > > > > > > > > Do you ask why the size on disk is small? This is the way snapshots > > work. > > > > > When you create a database snapshot it is almost an empty file and the > > > > > snapshot really points to the pages of the source database. When a > > page in > > > > > the source database is updated the snapshot saves the original page. > > So, > > > > as > > > > > the original database is updated the snapshot file will also grow in > > size. > > > > > > > > > > Hope this helps, > > > > > > > > > > Ben Nevarez > > > > > Senior Database Administrator > > > > > AIG SunAmerica > > > > > > > > > > > > > > > > > > > > "Alan T" wrote: > > > > > > > > > > > I created a snapshot of AdventureWorks. > > > > > > However, when I inspect the snapshot database file, it says > > > > > > size: 179MB > > > > > > size on disk: 192 KB > > > > > > > > > > > > What is that mean? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > How do I refresh the snapshot database whenever I modify the source database You don't. A snapshot database is a ... snapshot of the data. If you want continuous reflection of > so that the snapshot database has the 'before' state of the modified page? modifications in the source database then you should go for some other technology (like transactional replication, for instance). Of course, you can drop and re-create the snapshot database, at an interval that meets your requirements. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Alan T" <alanNOSPAMpltse@yahoo.com.au> wrote in message news:u1vovi8KIHA.5224@TK2MSFTNGP02.phx.gbl... > How do I refresh the snapshot database whenever I modify the source database > so that the snapshot database has the 'before' state of the modified page? > > eg. > Monday - modify the employee record of John Smith > Tuesday - modify the employee record of John Smith > Wednesday - modify the employee record of John Smith > Thursday - modify the employee record of John Smith > Friday - modify the employee record of John Smith > > If I don't 'refresh' the snapshot database, the snapshot database will still > keep the 'Monday' state because the page is already there after I modified > on Monday. > > "Ben Nevarez" <BenNeva***@discussions.microsoft.com> wrote in message > news:E8286D1B-F5DB-4697-BE38-5D60C4734E18@microsoft.com... >> >> Yes, the snapshot keeps the original pages that have been changed on the >> source database. Note that I used the term 'online' database before, > perhaps >> 'source' database is a better description as we can also have snapshots of > a >> mirror database. >> >> You create snapshots depending on your specific needs. Just keep in mind >> that as your source database changes your snapshot will also grow on size. >> >> Hope this helps, >> >> Ben Nevarez >> Senior Database Administrator >> AIG SunAmerica >> >> >> >> "Alan T" wrote: >> >> > I think I am a bit clearer. >> > The snapshot keeps the page(s) that was/were before modified. >> > >> > So in order to keep safe of the original database, we should create or >> > refresh the snapshot whenever we do a major modification to the original >> > database? >> > >> > >> > >> > "Ben Nevarez" <BenNeva***@discussions.microsoft.com> wrote in message >> > news:F3F5484A-46AA-40DF-9789-57DA51762BC6@microsoft.com... >> > > >> > > If you change records and SQL Server updates a page on the online >> > database, >> > > the snapshot will keep a copy of the original page. >> > > >> > > If SQL Server updates the same page again on the online database that > has >> > no >> > > effect on the snapshot, the snapshot already has a copy of the > original >> > page. >> > > >> > > Hope this helps, >> > > >> > > Ben Nevarez >> > > Senior Database Administrator >> > > AIG SunAmerica >> > > >> > > >> > > >> > > "Alan T" wrote: >> > > >> > > > Hi, >> > > > >> > > > Still not fully understand. >> > > > Please comment if I am wrong: >> > > > 1) If I add/modify records in a table, the snapshot will save the >> > modified >> > > > page only >> > > > 2) Any further modifications to the same/other pages, snapshot saves > the >> > > > modified pages also >> > > > >> > > > So the snapshot seems to work like the differential database backup? >> > > > >> > > > "Ben Nevarez" <BenNeva***@discussions.microsoft.com> wrote in > message >> > > > news:C79415A4-010A-4F9A-BFC4-726B1F09F2CF@microsoft.com... >> > > > > >> > > > > Do you ask why the size on disk is small? This is the way > snapshots >> > work. >> > > > > When you create a database snapshot it is almost an empty file and > the >> > > > > snapshot really points to the pages of the source database. When a >> > page in >> > > > > the source database is updated the snapshot saves the original > page. >> > So, >> > > > as >> > > > > the original database is updated the snapshot file will also grow > in >> > size. >> > > > > >> > > > > Hope this helps, >> > > > > >> > > > > Ben Nevarez >> > > > > Senior Database Administrator >> > > > > AIG SunAmerica >> > > > > >> > > > > >> > > > > >> > > > > "Alan T" wrote: >> > > > > >> > > > > > I created a snapshot of AdventureWorks. >> > > > > > However, when I inspect the snapshot database file, it says >> > > > > > size: 179MB >> > > > > > size on disk: 192 KB >> > > > > > >> > > > > > What is that mean? >> > > > > > >> > > > > > >> > > > > > >> > > > >> > > > >> > > > >> > >> > >> > > > Sorry, go back to my question is if I keep modifying the same page (I think
the same record will be in the same page in the short interval say 10 minutes time if no page split), the snapshot database will only keep the original page right before the first modification? eg. Monday 12:00pm - modify the employee record of John Smith Monday 12:02pmTuesday - modify the employee record of John Smith Monday 12:04pm - modify the employee record of John Smith Monday 12:06pm - modify the employee record of John Smith Monday 12:08pm - modify the employee record of John Smith So the snapshot database only keeps the page before 12:00pm's modification? What I think is at 12:02pm even the page is modified but the snapshot database will not refresh the page before this page is already in the snapshot database. > Sorry, go back to my question is if I keep modifying the same page (I think Correct. The page will be copied to the snapshot file for the first modification of that page > the same record will be in the same page in the short interval say 10 > minutes time if no page split), the snapshot database will only keep the > original page right before the first modification? performed in the source database. > So the snapshot database only keeps the page before 12:00pm's modification? Correct.> What I think is at 12:02pm even the page is modified but the snapshot Correct.> database will not refresh the page before this page is already in the > snapshot database. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Alan T" <alanNOSPAMpltse@yahoo.com.au> wrote in message news:%232W4YrJLIHA.5360@TK2MSFTNGP03.phx.gbl... > Sorry, go back to my question is if I keep modifying the same page (I think > the same record will be in the same page in the short interval say 10 > minutes time if no page split), the snapshot database will only keep the > original page right before the first modification? > > eg. > Monday 12:00pm - modify the employee record of John Smith > Monday 12:02pmTuesday - modify the employee record of John Smith > Monday 12:04pm - modify the employee record of John Smith > Monday 12:06pm - modify the employee record of John Smith > Monday 12:08pm - modify the employee record of John Smith > > So the snapshot database only keeps the page before 12:00pm's modification? > What I think is at 12:02pm even the page is modified but the snapshot > database will not refresh the page before this page is already in the > snapshot database. > > >  |
|||||||||||||||||||||||
Other interesting topics