Shrinking a TLog while Mirroring

Question

Shrinking a TLog while Mirroring

Reg_Mayfield

SSCrazy

Points: 2661
More actions
June 27, 2007 at 10:48 am

#120440

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

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

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

Reg_Mayfield SSCrazy Points: 2661 More actions · Answer 2

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

Reg_Mayfield SSCrazy Points: 2661 More actions · Answer 3

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

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

noeld SSC Guru Points: 96590 More actions · Answer 5

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

Reg_Mayfield SSCrazy Points: 2661 More actions · Answer 6

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....

SimonLiew SSCrazy Eights Points: 8212 More actions · Answer 7

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

vmssanthosh SSC Eights! Points: 856 More actions · Answer 8

vmssanthosh

SSC Eights!

Points: 856

December 16, 2008 at 11:11 pm

#913609

Really helped.. thank u very much>>>>

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Allen.Feinberg Grasshopper Points: 11 More actions · Answer 10

So does this work for a SQL Server 2008 R2 database that is being mirrored?

vmssanthosh SSC Eights! Points: 856 More actions · Answer 11

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vmssanthosh SSC Eights! Points: 856 More actions · Answer 13

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 🙁

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass