Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Snapshot of MSDB Expand / Collapse
Author
Message
Posted Wednesday, August 26, 2009 11:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 14, 2011 2:16 AM
Points: 1, Visits: 43
Comments posted to this topic are about the item Snapshot of MSDB
Post #778045
Posted Wednesday, August 26, 2009 11:38 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:25 AM
Points: 1,519, Visits: 2,550

Good question.
I am able to understand why snapshot cannot be created on TEMPDB. But don't understand why it has been prohibited for Master and Model (Though it's not necessary to create snapshots on system DBs).






Post #778048
Posted Thursday, August 27, 2009 5:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 6:01 AM
Points: 191, Visits: 317
The Microsoft 70-431 self-training book says "You cannot create Database Snapshots against system databases." Was this changed in a service pack, or was the author being lazy?
Post #778180
Posted Thursday, August 27, 2009 8:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 6:50 AM
Points: 540, Visits: 254
Yes, the MCTS 70-431 on p605 does say "You cannot create Database Snapshots against system databases." The MSKB at http://support.microsoft.com/kb/920926 does not have any errata for this page to indicate otherwise. Has anybody tried this on SQL 2005 Enterprise? What was the result?
Post #778327
Posted Thursday, August 27, 2009 9:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:34 PM
Points: 23,343, Visits: 32,079
dun (8/27/2009)
Yes, the MCTS 70-431 on p605 does say "You cannot create Database Snapshots against system databases." The MSKB at http://support.microsoft.com/kb/920926 does not have any errata for this page to indicate otherwise. Has anybody tried this on SQL 2005 Enterprise? What was the result?


Okay, I tried the following and it was successful.

create database msdb_dbss20090827 on
(name = MSDBData, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\msdbdata.ss')
as snapshot of msdb;




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #778414
Posted Thursday, August 27, 2009 10:34 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 21,657, Visits: 15,326
Also, this msdn article explicitly states model,master and tempdb as being prohibited - thus leaving the door open for msdb snapshots.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #778465
Posted Thursday, August 27, 2009 10:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:34 PM
Points: 23,343, Visits: 32,079
As does BOL (Books Online).



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #778482
Posted Thursday, August 27, 2009 3:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:57 PM
Points: 33,206, Visits: 15,361
Thanks for the test.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #778769
Posted Saturday, March 20, 2010 12:10 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:59 PM
Points: 8,748, Visits: 9,296
Joy Smith San (8/26/2009)

Good question.
I am able to understand why snapshot cannot be created on TEMPDB. But don't understand why it has been prohibited for Master and Model (Though it's not necessary to create snapshots on system DBs).

It may not be necessary, but it can be useful - especially for MSDB. MSDB will grow like topsy unless job and jobstep history is pruned, but you want to avoid having oldest data disappear while you are generating reports about the history of jobs and steps (time taken, failures, scheduled runs missed,...) so that everything's viewed from the same base. So take a snapshot of MSDB and do the reporting against that - the real MSDB can happily be pruned while the report task is running. Throw the snapshot away when the report is done.
I'd always want to have a well documented history of model, what changed when and why it changed, and it should change so rarely that there is no point in taking a snapshot (snapshots are NOT backups, they are temporary static images you can play with). Probably sensible to ban taking snapshots as otherwise people might be tempted to misuse them. Probably the same applies to Master, although it changes more often.


Tom
Post #886940
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse