Shrinking a TLog while Mirroring
-
I have looked on the Web as well as in this forum and I can't quite find a definitive answer on how or when to shrink a transaction log when a database is being mirrored. I was using a script found on this web site that allowed me to monitor my databases and when the transaction log went past a user specified threshhold an alert was generated. At that point I would run the following commands to shrink the Log file in question...
DBCC SHRINKFILE(DatabaseName_Log, 1)
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DatabaseName_Log, 1)
This would keep the log file under the threshhold for several days to a week. I have migrated to Sql Server 2005 and I am mirroring several of my databases rather than using LogShipping. I can no longer use the above commands while mirroring a database but have seen my transaction log approach 1Gb on a 2.5 Gb database. I also run full database backups followed by Transaction log backups at the close of our workday (I read somewhere performing a transaction log backup would truncate the log file after a backup) but the TLog size has not grown nor has it shrunk in the past several days. I am scheduled to go on vacation in the next week or two and I am worried that if the TLog grows too big I might get interrupted during my vacation. Is there a way to one, shrink the transaction log while mirroring and two, how to tell if the log has issues and may grow out of control???
Thanks
-
1. try: dbcc sqlperf(logspace) to figure out how much free space you have on the log.
2. TRUNCATE_ONLY is deprecated in 2005 so you have to backup the log. If you don't care about transaction log backup you can back it up to the NUL device and you are done!
Cheers,
* Noel -
Noel,
I ran the command dbcc sqlperf(logspace) and found that the database I am concerned with is only using 5.49% of the log space.
I went to create a NULL device as you suggested but in Sql Server 2005 it would not let me create a device without a valid path. I then used TSQL and ran the following command:
USE master;
GO
EXEC
sp_addumpdevice 'disk', 'Diskdump', '';
But when I try and use the device in a backup command I get the following error:
Invalid zero-length device name...
Hopefully you can direct me as to how to set up a Null device so that I can backup the Tlog to that device and see if the transaction log will shrink down to normal level...
Thanks
-
I wanted to post a final solution to the question I posed several weeks ago in case someelse has this problem...
To shrink a Transaction log for a database that is being mirrored I do the following:
USE XXX
GO
CHECKPOINT
GO
BACKUP LOG [XXX] TO [XXX_TLOG] WITH NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
DBCC SHRINKFILE(XXX_Log, 1)
GO
Can either be run manualy or as a job...
Hopefuly this may be helpful for others with large transaction logs for mirrored databases
-
But i have one question is it safer to shrink a logfile that is being used in log shipping or DB Mirroring it is not that wayso be careful on that.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
What I wanted you to do is simply:
BACKUP LOG [dbname] TO DISK='NULL'
Then do the shrink
dbcc shrinkfile ('TLOGFILENAME', yoursize) -- where yoursize is a number in MB
Cheers!
* Noel -
Noel,
Thanks for your reply...I have tried shrinking the TLog with your method and it also works just fine so now I have two ways to shrink a log file while mirroring a database...
Sugesh,
I understand your word of caution and I only shrink the TLog after hours and when I have done a full backup of the database just in case anything goes wrong. So thank you also for your reply....
-
Reg,
Basically the method Noel suggested will flush the TLogs to disk and then shrink the file. Its no difference from issuing a command
backup log <db name> to disk = 'C:\abc.trn'
I'd advise to db a full db backup and then append the TLogs to the backup file that you've created and then shrink the DB Log size. Its much safer this way.
BACKUP LOG <db name> TO DISK = 'C:\MSSQL\BACKUP\<db full backup>.bak' WITH NOINIT , NOUNLOAD , NAME = N'xxx backup', NOSKIP , STATS = 10, NOFORMAT
If you flush the logs away, and had added new records into your DB, there NO WAY you can use any info flushed out from your TLog to recovery anything.
Cheers,
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
Really helped.. thank u very much>>>>
-
Please note: 4 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
So does this work for a SQL Server 2008 R2 database that is being mirrored?
-
Yes it works for 2008 r2.. Our production server has 2008 r2 only. and we are using the same technique for truncating log files.
Regards
VMSSanthosh
-
vmssanthosh (4/4/2011)
Yes it works for 2008 r2.. Our production server has 2008 r2 only. and we are using the same technique for truncating log files.Why? Just take normal log backups. Why on earth would you want no point-in-time recovery on a DB that's important enough to be mirrored?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
hi
sorry i didn't understand your question. can you please explain in detail. I am not an experienced dba. i am forced to become dba 🙁
-
I'm trying to understand why you would think that you want to use such a command. There's no practical use for backing up a log to the NUL device (equivalent to backing it up then deleting the backup file)
p.s. Can you post in a new thread and ask whatever's troubling you in detail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply