Thursday, March 22, 2012

Database mirroring and log file growth

Is there a way to shrink the log file while using database mirroring?
We have a 20 GB database with a log file about 80 GB.
Transaction log backup is done every 30 minutes, full backup each night, but the log file is not truncated.

Since it seems that the log file grows about 1 GB each day (we have migrated from sql2000 about 50 days ago) we are going to run out of disk space in a few weeks!

Ivan

refer this link,

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=377087

they have suggested to ,issue checkpoint and then truncate log......

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

|||

Is the Database also part of replication ?

Is the Database Mirroring Active , STATE = SYNCRONIZED ?

|||just try the above method and get back if its not possible you need to remove mirroring

refer, http://technet.microsoft.com/en-us/library/ms189112.aspx then try to truncate the tran log and try to shrink using dbcc shrinkfile........i am not sure which of the above 2 will work out.........|||

Removing and Reestablishing mirroring for a large database might be some work , again it depends how quickly you want to fix it , however what i will suggest ; check if the scheduled TLOG backups are successfull ? if the database is acting as a publisher in replication topology ; also if you could post the mode of mirroring Sync or Async ?

No comments:

Post a Comment