Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Cannot shrink log file 2...

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 Database

BACKUP 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 Database

ALTER 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 Database

USE [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 .
Go to Top of Page

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-10-31 : 12:51:18
Any other thoughts?
Go to Top of Page

homebrew
Posting Yak Master

114 Posts

Posted - 2008-10-31 : 13:57:14
What's the point of shrinking in this case ??
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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)?
Go to Top of Page

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 backup
2) 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 situation

a)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
Go to Top of Page

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-11-04 : 17:04:57
saurab, thanks for the leads.
Go to Top of Page

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2008-11-05 : 12:51:06
YOu're Welcome sir
Go to Top of Page
   

- Advertisement -