Here is important point in regards to DBCC SHRINKFILE not to be misinterpreted: DBCC SHRINKFILE (<Transaction_LogFile_LogicalName>, TRUNCATEONLY) does truncate a transaction log file, i.e. breaks the LSN - Log Sequence Number.
No , it does not. Shrinking never breaks the log chain. That 'truncateonly' means to only release free space at the end of the file not to shuffle data pages around (emphasis data pages, it's ignored when shrinking a log).
The only things that break the log chain are:
- Switch to simple recovery
- Backup log with truncate only (SQL 2005)
- Reverting from a database snapshot
- Rebuilding the log
What you have stated there is a prevalent and very irritating myth. If you'd tested it, you'd have seen that the shrink does not break the log chain.
Instead, backup the transaction log file: (SQL Server 2008 Standard Edition, Enterprise Edition / SQL Server 2008R2 any addition)
BACKUP LOG <DatabaseName> TO DISK = N'<drive>:\<backup_folder>\<database_name>LogBackup.trn' WITH NOFORMAT, INIT, NAME = N'<DatabaseName> - Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, COPY_ONLY, STATS = 1;
A copy only log backup won't free any space in the log, so I don't known what the point of running this is.
Also might like to mention that statement is Enterprise only, because of the compression.
then execute
DBCC SHRINKFILE(<Transaction Log File Name (logical)>, 1)which will shrink the T-Log to to its last used extent.
Log files don't have extents. It'll shrink by removing any inactive VLFs that are at the end of the file. If the last VLF in the file is part of the active portion of the log, it won't shrink at all.
Shrinking as small as possible is a horrid thing to do, unless you intend to immediately grow the log file again. If the idea is just to shrink the log after unusual growth, then it should just be shrunk to normal size. See Managing Transaction Logs[/url]
Warning: Backing up the transaction log file prior to its shrinkage is required because in the total majority of situations, the T-Log file won't shrink because of active segments either at its tail or towards it. Those ones are "freed-up" exactly by running transaction log file backup
Except that you said to run the log backup with copy_only, which won't free anything. Even a normal log backup won't free VLFs that are active and at the end of the file if they're needed for something else (transaction, replication, mirroring, etc). Shrink cannot move log records around the log.
In this kind of situation, when you're getting 9002 errors and database is in "Suspect" state, there is really no time to wait for transaction log file backup completion before you can proceed with its shrinking,
A full log will never send the database suspect (if it ever does, you've hit a critical bug). A full log makes the DB read only, that's all.
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