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 mirroringrefer, 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