June 24, 2010 at 10:04 am
Hello Everyone
I have a mirrored database and the drive the log is on is close to full capacity. SQL 2008 does not have a Truncate_Only attribute for the backup log command. We cannot put this database into Simple mode, and run DBCC, and then put it back into full recovery.
How then can I truncate the log?
Thanks in advance
Andrew SQLDBA
June 24, 2010 at 11:00 am
backup the log to clear space within the log (i.e. truncate it). You can then shrink the log if you really need to, but only to the size required to support normal activity and only if you really need the drive space.
---------------------------------------------------------------------
June 24, 2010 at 11:24 am
The command Backup Log with Truncate_Only is no longer a valid command in SQL 2008.
I tries that first, only to find out that MS has done away with it. This is what is in the SQL BOL.
alter database <mydb> set recovery simple
go
checkpoint
go
alter database <mydb> set recovery full
go
backup database pubs to disk = 'c:\mydb.bak' with init
go
dbcc shrinkfile (N'mydb_log' , 1)
go
But my concern was what will happen to the mirror? I have found out that the above command is the only way to perform a truncate of the log.
The mirror will re-sync itself once you place the database back into full recovery mode and create the mirror again.
Andrew SQLDBA
June 24, 2010 at 12:06 pm
Have you tried this? I wonder if it will work. Once you go to simple mode, you should be breaking the recovery chain. I am unsure if that will break mirroring. My gut says it does since it should change the LSN.
June 24, 2010 at 2:26 pm
I did not mean backup log with truncate_only, just a normal backup log dbname to disk = ' wherever'
you cannot put a mirrored database into any recovery mode than full.
to truncate a log means to clear out inactive transactions, a normal log backup does that.
---------------------------------------------------------------------
June 24, 2010 at 2:58 pm
A normal full database backup will not truncate log while the database is in Full recover mode.
I found that once the database was put into Simple Recovery Mode, the trans log was truncated. I did not have to issue any command.
But I still used the code that I have listed in the post above.
I still have to reconstruct the mirror. I did not find a way to do this without breaking the mirror.
If anyone knows of a way, please let me know.
Thanks
Andrew SQLDBA
June 24, 2010 at 3:13 pm
Everything I know about mirroring says that if you go to simple, you have to rebuild the mirror. You can switch to bulk mode, but once you truncate the log, you move LSNs, and that means a new full backup to rebuild the mirror.
June 24, 2010 at 3:16 pm
Why did MS ever remove the Backup Log with Truncate_only command? This is just insane now.
Andrew SQLDBA
June 24, 2010 at 3:18 pm
It's not insane. The switch to simple mode does the same thing. If you were able to run the backup with truncate (or no_log), you are still breaking the recovery chain, and you would need a full backup. Or you would break mirroring.
As an FYI, you might want to read this: http://www.sqlskills.com/BLOGS/PAUL/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx
June 25, 2010 at 2:29 am
Hello? Can anyone hear me out there?
Run a backup LOG, not a full backup.
---------------------------------------------------------------------
June 29, 2010 at 2:50 am
Hi,Andrew
This is Mahalingam,
i am also the same stage. if your are enable the mirroring on the particular database , u cannot truncate the log.Because which is mapped with full recovery model.
Not use the command: Backup log 'dbname' with truncate_only or
Backup log dbname with no log.
do that:
dbcc shrinkdatabase('databasename',10).
dbcc shrinkfile('logfile name,1).
Regards,
M.Mahalingam
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply