September 13, 2009 at 10:25 am
Hi,
I have a database with an abnormally large transaction log file - 60GB. It was in Simple Recovery Model. I changed it to Full Recovery Model and tried to backup the transaction log. This resulted in a 48GB backup file. When I then tried to shrink the database, it showed that there is 0% free space, and the transaction log file is still at 60GB.
I tried backing up with no_log and that also did not work.
At some point, when I tried to shrink database, it showed a negative value for the transaction log file's free space!
Any suggestions would be greatly appreciated.
Thanks.
September 13, 2009 at 4:48 pm
Timothy Patihk (9/13/2009)
I have a database with an abnormally large transaction log file - 60GB. It was in Simple Recovery Model. I changed it to Full Recovery Model and tried to backup the transaction log. This resulted in a 48GB backup file.
That's odd - you cannot take a log backup after a switch from FULL to SIMPLE recovery mode without taking a full database backup first. The full backup establishes the start of the recovery chain. Until that is done, the database is in 'auto-truncate' mode, just as if it was still set to SIMPLE recovery.
The following will show you whether your database is in auto-truncate mode or not:
SELECT db_name(database_id) as 'database',
CASE WHEN last_log_backup_lsn IS NULL THEN 'In auto-truncate mode!' ELSE 'Ok' END
FROM sys.database_recovery_status;
If it is in auto-truncate mode, take a full backup, and then backup the log.
The log file itself never shrinks on its own - you need to do that manually.
If you post the output from the following commands, we can provide further specific advice:
DBCC LOGINFO;
SELECT name, log_reuse_wait_desc FROM sys.databases ORDER BY name;
Timothy Patihk (9/13/2009)
When I then tried to shrink the database, it showed that there is 0% free space, and the transaction log file is still at 60GB.
Shrinking a database is only a good idea if you really need the disk space back urgently, and don't mind massive fragmentation.
You probably just want to shrink the transaction log, not the whole database. This can be done with DBCC SHRINKFILE, or by right-clicking on a database in SSMS and choosing Tasks -> Shrink -> Files, and then choosing the Log file type in the dialog that appears.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 5:24 pm
Paul White (9/13/2009)
That's odd - you cannot take a log backup after a switch from FULL to SIMPLE recovery mode without taking a full database backup first. The full backup establishes the start of the recovery chain. Until that is done, the database is in 'auto-truncate' mode, just as if it was still set to SIMPLE recovery.
I am sorry I left out that detail! I did a full data backup, and then ran a Transaction Log backup. It TRN file was large, and yet the space free on the LDF was 0%.
I just completed a full data backup again, and I'm about to attempt the transaction log backup again. So right now, according to the code you sent, the database is 'OK'.
Paul White (9/13/2009)
Shrinking a database is only a good idea if you really need the disk space back urgently, and don't mind massive fragmentation.You probably just want to shrink the transaction log, not the whole database. This can be done with DBCC SHRINKFILE, or by right-clicking on a database in SSMS and choosing Tasks -> Shrink -> Files, and then choosing the Log file type in the dialog that appears.
Again, sorry about that. I meant to say I shrank the LDF file, not the MDF, just as you described here.
DBCC LOGINFO;
SELECT name, log_reuse_wait_desc FROM sys.databases ORDER BY name;
I ran the above code, and the result set was large. Well, at least for the DBCC LOGINFO. The SELECT statement showed the database had a 'REPLICATION' value for 'log_reuse_wait_desc'. I am not sure why it is in that state, but I'm currently investigating. I know in this state that would be the reason I cannot backup or truncate the LDF file, correct?
Thanks for the continued support.
September 13, 2009 at 5:36 pm
REPLICATION is indeed the cause.
The log records have to be kept 'live' until any *transactional* replication that uses them has completed. If replication is 'stuck' (most likely it did not like the switch to Simple mode and back - the log chain was broken at that point).
Check that replication is running, including the Snapshot and Log Reader Agents. Use Replication Monitor for this.
You will probably need to re-initialise all the subscriptions affected.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 5:47 pm
Hi Paul,
I dont have replication configured. I chceked the Replication Monitor non-the-less, and there is nothing in there to monitor.
How can it be set in this mode without my input? Can I simply turn off a flag and try the transaction log backup again?
thanks for the continued support!
September 13, 2009 at 6:10 pm
Was the database restored from a server where it was replicated?
If you are *absolutely certain* about the replication, you can mark all transactions as having been processed by running the following command *in the affected database*
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
See Books Online for a full description of what sp_repldone does.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 8:17 pm
Hi Paul,
when I try that command, it says:
"Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication."
September 13, 2009 at 9:07 pm
The workaround to this problem is:
1. setup replication (i dont hv answers as to why you need to setup replication).
2. delete replication
3. backup tran log
4. shrink tran log(if its urgent)
September 13, 2009 at 9:12 pm
Hi Paul,
I found a fix that worked.
I created replication, deleted the configuration, and then I was able to shrink the LDF file!
Thanks again for pointing me in the right direction!
September 13, 2009 at 9:12 pm
Hi Pradeep!
I just saw your post when I sent mine.
That is EXACTLY what worked.
Thanks!
September 13, 2009 at 9:16 pm
Am Glad it worked!!:-)
September 13, 2009 at 9:37 pm
Me too. I was rather hoping to avoid temporarily setting up replication.
Another approach is to detach the database, then attach it using CREATE DATABASE with the REBUILD_LOG option.
I'm a little confused as to how this came about though - is the database concerned a backup of a replicated database, or...?
It would just be nice to know.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 15, 2009 at 2:20 am
i wonder about which version of sql your talking about ? 2000 ? and if it's 2005/2008 how you got the db on the server.
cause for restores in sql 2005 (t-sql) you need to explicitly put the keep_replication in the restore command to keep replication settings else it gets removed. this has changed in sql 2005. i'm not sure if the compatibility mode has influence on this.
a solution could also be for sql 2005/2008 (probably depending on the size of your db):
- backup, restore and dont use "keep_replication".
in ssms you can see this in the restore database screen with the options subscreen. the tag "preserve replication settings" is this part.
bol:
Database Settings and Restoring
During a restore, most of the database options that are settable using ALTER DATABASE are reset to the values in force at the time of the end of backup. If a replicated database is restored to a server other than the one on which the backup was taken, the KEEP_REPLICATION option must be used to retain the replication-related values in force at the time of the end of backup. For more information, see Backing Up and Restoring Replicated Databases.
Note:
This behavior differs from versions of SQL Server before SQL Server 2000.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply