January 13, 2012 at 8:24 am
I have an instance with a number of databases in read only mode and a few read/write. My tlog directory is about 75GB with about 45GB taken up by transaction logs for read only databases. I believe the vendor has come up with a solution that alters a database to read only mode when appropriate. Currently, they simply alter the database to read only.
Going forward I'm going to request the vendor update their process to: change database to simple recovery mode-> shrink transaction log file -> alter to read only.
However, I currently need to cleanup the large leftover transaction log files. Is there anyway to reduce the file size without taking the database out of read only mode?
January 13, 2012 at 8:40 am
a shrink needs to make updates to the database so is not an option when the database is in read-only mode.
---------------------------------------------------------------------
January 13, 2012 at 8:42 am
Transaction Log Backups should do the trick (assuming database is in FULL recovery model).
January 13, 2012 at 8:45 am
Dev (1/13/2012)
Transaction Log Backups should do the trick (assuming database is in FULL recovery model).
sorry Dev, that won't make the files any smaller.
---------------------------------------------------------------------
January 13, 2012 at 8:54 am
Will it not truncate the log files?
January 13, 2012 at 9:04 am
truncate the log file means remove the inactive portion of the log, it does not physically shrink the size of the log file on the drive, only a shrink command does that.
some people misuse the term 'truncate' in this context.
---------------------------------------------------------------------
January 13, 2012 at 9:10 am
I understand it. My point is shrinking the log will be more effective after log truncation.
Per BOL:
Shrinking can occur only while the database is online and, also, while at least one virtual log file is free. In some cases, shrinking the log may not be possible until after the next log truncation.
January 13, 2012 at 9:18 am
Dev (1/13/2012)
I understand it. My point is shrinking the log will be more effective after log truncation.
Maybe, assuming that truncation was necessary and that said truncation did actually mark some space as reusable.
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
January 13, 2012 at 9:18 am
the OP may need to back up the log as well, but I would not expect active log in a read only database,
but either way the log cannot be shrunk while it is read_only mode
---------------------------------------------------------------------
January 13, 2012 at 9:20 am
GilaMonster (1/13/2012)
Dev (1/13/2012)
I understand it. My point is shrinking the log will be more effective after log truncation.Maybe, assuming that truncation was necessary and that said truncation did actually mark some space as reusable.
🙂
---------------------------------------------------------------------
January 13, 2012 at 9:20 am
jshurak (1/13/2012)
Is there anyway to reduce the file size without taking the database out of read only mode?
No.
Alter the databases to read-write, set recovery model to simple, take a backup, shrink the log to a reasonable size and set it back to read only.
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
January 13, 2012 at 9:21 am
Dev (1/13/2012)
I understand it. My point is shrinking the log will be more effective after log truncation.Per BOL:
Shrinking can occur only while the database is online and, also, while at least one virtual log file is free. In some cases, shrinking the log may not be possible until after the next log truncation.
you do not need to truncate the log. Moving to simple will mark the used portion of the log as inactive on a checkpoint.
Move to simple
checkpoint (probably runs, but no harm in manually running)
shrink t-log files
mark as read only
January 13, 2012 at 9:28 am
However I shouldn’t expect any DML activates on Read Only database. But putting the database in simple recovery mode is not my preference. I hesitate to put database in any recovery mode except FULL.
January 13, 2012 at 9:35 am
Dev (1/13/2012)
However I shouldn’t expect any DML activates on Read Only database.
Other than selects, there will be none, there can't be.
But putting the database in simple recovery mode is not my preference. I hesitate to put database in any recovery mode except FULL.
Why? What would be the point in keeping a read only database in full recovery. Granted, it won't need log backups, but it doesn't need the ability for point in time recovery.
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
January 13, 2012 at 9:39 am
Thus it’s fine in either way. It’s a matter of choice now.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply