30 August,2011 by Tom Collins
Executing the code below in SQL Server 2008 will throw an error message:
BACKUP LOG ‘MyDatabase’ WITH TRUNCATE_ONLY Msg 155, Level 15, State 1, Line 44 'TRUNCATE_ONLY' is not a recognized BACKUP option.
It was deprecated in SQL Server 2008.To view SQL deprecated features use the Perfmon SQL Server Deprecated Features Object
Some options for SQL Server 2008 are :
1) BACKUPLOG [DBNAME] TODISK='NUL'
This will break the transaction log chain. Therefore a Full backup is required after the command.
2) Switch to Simple Recovery and then switch back to Full Recovery. This will required a Full backup to restart a new transaction log chain
3) DBCC SHRINKFILE(Test_Log) . This option causes fragmentation , but does the job when it comes to reclaiming space. You might find it doesn’t shrink – in that case check for open transactions.
4) Take regular Transaction Log backups while maintaining a small log file size
The choice will depend on the circumstances.
The transaction log for database is full due to ACTIVE_TRANSACTION
Modify sql transaction log file size - increase or decrease - SQL ...
SQL Server - Preallocate SQL Transaction Logs for large queries
...
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |