#4 SQL Server 2008 Truncating log file

[Webio]

SQL Server 2008 provider is not truncating log file because DB is using "Full" recovery mode.

Query before:

ExecuteNonQuery(String.Format(@"USE [{0}];DBCC SHRINKFILE ('{1}', 1);",databaseName, database.LogName));

Query after:

ExecuteNonQuery(String.Format(@"USE [{0}];ALTER DATABASE [{0}] SET RECOVERY SIMPLE;DBCC SHRINKFILE({1});ALTER DATABASE [{0}] SET RECOVERY FULL;",databaseName, database.LogName));

Affected file:

MsSqlServer2008.cs

[Webio]

[Webio]

Small update to "query after":

ExecuteNonQuery(String.Format(@"USE [{0}];ALTER DATABASE [{0}] SET RECOVERY SIMPLE;DBCC SHRINKFILE('{1}');ALTER DATABASE [{0}] SET RECOVERY FULL;", databaseName, database.LogName));

It will work correctly now with database name containing . (dot) sign

[ACW]

I didn't test it bu just switching the database to simple recovery should be all you need to clear the log so you may be able to remove DBCC SHRINKFILE('{1}'); from your query.

[Webio]

I've based my code on this blog entry:

http://www.jaxidian.org/update/2009/04/22/48/

"...Simply setting the database mode into simple recovery mode performs the actual truncation but the file is not shrunk by that. DBCC SHRINKFILE will take care of that second step..."

[ACW]

Correct, you don't need it if you want to just truncate it but I do see the original code was meant to not only truncate the database but also shrink it. So ultimately the WSP Truncate command was designed to do both and you are right in keeping it that way.

Thanks for the patch.

[Webio]

There is one more think which should be done here. Today one of my customers had problem with shrinking his log file. Whatever ha wanted to do he was getting this error:

The transaction log for database 'DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

To fix this problem I had to remove restrictions for log file size, shrink log file and add restrictions on log again. I think this should be done in WP Truncate function too. Anyone interested in writing SQL script which will:

1. Retrieve from DB current LOG restriction.
2. Execute shrinking
3. Set restriction on log file again.

?