The common T-SQL script to shrink/truncate log file is using the script below
|
1 2 3 |
USE [foo] GO DBCC SHRINKFILE(foo_log, 1) |
But, if the script runs on SQL Server 2008 instance, it will fail. You still have the same log file size, nothing change (shrink) . In other word you cannot shrink the log file.
To solve the issue here’s a work around:
Microsoft SQL Server 2008 has a default setting ‘FULL‘ for Recovery Model, so that means that we cannot just shrink Log File to minimum size.
Change the recovery model to ‘SIMPLE‘ and Shrink the Log File using:
|
1 2 |
USE [foo] DBCC SHRINKFILE(foo_log, 1) |
If ‘FULL‘ Recovery Mode still needed, cause is crucial for transaction DB, you can use ALTER sql command to change and recover Recovery Mode to its original state.
|
1 2 3 4 5 6 |
USE [foo] GO ALTER DATABASE [foo] SET RECOVERY SIMPLE WITH NO_WAIT DBCC SHRINKFILE(foo_log, 1) ALTER DATABASE [foo] SET RECOVERY FULL WITH NO_WAIT GO |
I do not even know how I stopped up right here, however I believed this publish was once
great. I do not know who you’re however definitely you’re going to a famous
blogger for those who are not already. Cheers!