Author |
Topic |
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-10-31 : 10:31:22
|
Scenario:I have a nightly process in place that backups up our production database and restores it to another server (for reporting purposes). This backup can be assumed to be a read-only database for all intents and purposes. The steps I perform to do this are as:- Backup production database
- Copy backups from production server to report server
- Restore reporting database
- Shrink reporting database
Now the exact syntax I use for the SQL portions are as follows:Backup DatabaseBACKUP DATABASE [MyDatabase] TO DISK = '\\SERVERNAME\BACKUPSHARE1\MyDatabase_YYYYMMDDHHMMSSMMM_FULL_001.BAK', DISK = '\\SERVERNAME\BACKUPSHARE2\MyDatabase_YYYYMMDDHHMMSSMMM_FULL_002.BAK', DISK = '\\SERVERNAME\BACKUPSHARE3\MyDatabase_YYYYMMDDHHMMSSMMM_FULL_003.BAK' WITH INIT, NOUNLOAD, NAME = '\\SERVERNAME\SQLBACKUP\MyDatabase_YYYYMMDDHHMMSSMMM_FULL', STATS = 10, FORMAT; Restore DatabaseALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;RESTORE DATABASE [MyDatabase] FROM DISK = '\\SERVERNAME\BACKUPSHARE1\MyDatabase_YYYYMMDDHHMMSSMMM_FULL_001.BAK', DISK = '\\SERVERNAME\BACKUPSHARE1\MyDatabase_YYYYMMDDHHMMSSMMM_FULL_002.BAK', DISK = '\\SERVERNAME\BACKUPSHARE1\MyDatabase_YYYYMMDDHHMMSSMMM_FULL_003.BAK' WITH MOVE 'MyDatabaseData' TO '<drive>\Data\MyDatabaseData.mdf', MOVE 'MyDatabaseLog' TO '<drive>\Logs\MyDatabaseLog.ldf', REPLACE;ALTER DATABASE [MyDatabase] SET MULTI_USER; Shrink DatabaseUSE [master];ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;DBCC SHRINKDATABASE('MyDatabase', 0);ALTER DATABASE [MyDatabase] SET MULTI_USER; Now the problem is I will intermittently receive this error on one or more of the databases (I do this process to 4 databases nightly) - Cannot shrink log file 2 (MyDatabaseLog) because all logical log files are in use. [SQLSTATE 01000]When this happens and I query msdb.sys.databases, the value for log_reuse_wait_desc is LOG_BACKUP. Is it safe to assume in this situation, since I did a full backup, I can safely execute a BACKUP LOG MyDatabase WITH TRUNCATE_ONLY and be safe? Or better yet, simply switch the recovery model to SIMPLE (prior to the SHRINKDATABASE statement)? |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-31 : 10:53:39
|
You can change database in Reporting server to simple recovery model and shrink it.Also this database is used for reporting needs (read-only) and there is nightly process for data refresh . |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-10-31 : 12:51:18
|
Any other thoughts? |
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2008-10-31 : 13:57:14
|
What's the point of shrinking in this case ?? |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-10-31 : 14:04:05
|
quote: Originally posted by homebrew What's the point of shrinking in this case ??
To reallocate the unused space in the data/log files back to the operating system. |
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2008-10-31 : 15:57:49
|
But every night when you restore, you take up that space again. Since you always need the space during the restore, you might as well just leave it allocated to SQL and save the time & trouble of shrinking. |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-11-01 : 10:55:31
|
quote: Originally posted by homebrew But every night when you restore, you take up that space again. Since you always need the space during the restore, you might as well just leave it allocated to SQL and save the time & trouble of shrinking.
Any opinions on my original question? You are making assumptions that are invalid. I did not communicate our entire workflow and situation because it was not necessary. I have already determined I simply want to reclaim the space and be done with it. Any suggestions? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-01 : 12:26:35
|
Tfountain,Provided the little information, thats all advice you will get. If you backing up and restoring in other server,then you will get exact size as Primary server while restoring to other server . Also what is point of Shrinking it everytime when you restore when you exactly know that SHRINKING IS BAD FOR PERFORMANCE AND CAN RESHUFFLES YOUR INDEX AND DATA PAGES RESULTING TO EXTERNAL AND INTERNAL FRAGMENTATION. Or Please explain in Detail. |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-11-03 : 09:33:57
|
Please do not respond unless you have a valid answer or suggestion to my post. I have decided to do the SHRINKDATABASE command. Regardless of the noted side effects, you can assume those are all addressed or accounted for in some way. I stated my situation clearly. No, I did not tell you everything this process does, nor am I allowed to. Simply put - I do enjoy my job and I would like to keep it.Now, aside from that, any thoughts on my original question:quote: Is it safe to assume in this situation, since I did a full backup, I can safely execute a BACKUP LOG MyDatabase WITH TRUNCATE_ONLY and be safe? Or better yet, simply switch the recovery model to SIMPLE (prior to the SHRINKDATABASE statement)?
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2008-11-03 : 09:52:51
|
1) The error message means that transactions are still open in *logical* log files. Each physical log file is made up of multiple virtual log files and the physical log cannot be shrunk when there all of these virtual logs are occupied with open transactions or active. Run DBCC opentran just for verification before running log backup2) Yes it is safe to run log back up to reclaim virtual logs. Because restored database is READ_ONLY. 3) YOu run SHRINKDATABASE: Some differences between shrinkdatabase and shrinkfile that would help you decide which one is better in your situationa)The shrinkdatabase command is based on a percentage where as b)shrinkfile is based on a target. c)Shrinkdatabase uses the percentage to calculate a target for each file but can't override the minimum file size. 8)ShrinkFile allows shrinking below the minimum file size. shrinkdatabase command results in a loop over each of the files in the database. Using the current free space of the file and the percentage the shrink target is calculated for each file. If the calculated target is less than the minimum size of the file the error is produced and the shrink attempt aborted for that file.output of shrinkdatabase can be checked using undocumented 'Tabular' option without invoking the shrink actions. (report only)dbcc shrinkdatabase(your_database, tabular)Using the information you can determine the expected behavior of dbcc shrinkdatabase and your targets. MimimumSize -The current minimum size established for the file. UsedPages - The number of pages used in the file (allocated) EstimatedPages - The estimated size that might be obtained during a shrink In some cases the minimum and maximum values where established the same when the file is completed. Shrinkdatabase won't shrink the file but shrinkfile could be used to reduce the minimum value and complete the shrink.Also check this out: http://blogs.msdn.com/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000.aspx |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-11-04 : 17:04:57
|
saurab, thanks for the leads. |
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2008-11-05 : 12:51:06
|
YOu're Welcome sir |
|
|
|