1

I am having a problem with disk space in SQL Server 2008. This is the situation: - I had a DB set with Simple Recovery and this DB used to use 2-3 GB for the transaction log file - I moved this DB to a new server and the situation was the same - I changed the recovery mode to FULL and, rightly, the log got 20 GB of disk space - I changed the recovery mode back to SIMPLE and I am not able to shrink the log file back to the 2-3 GB of disk space

The log file is very big and is growing fast.

I've verified and in the log file there is no room, in fact "available free space" is near 0%.

Can anyone help me please?

Thank you very much, Enrico

=============================================================

Till I can't add comments, I will add here the SOLUTION:

Thanks to adaptr answer a foung the solution.

visit this forum post: http://www.sqlservercentral.com/Forums/Topic695034-357-1.aspx#bm1377968

the steps I followed are:

1)SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'yourdb' --> the result was REPLICATION

2)EXEC sp_removedbreplication [yourdb] --> then execute SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'yourdb' ----> the result was NOTHING

2a)(ALTER DATABASE msdb SET RECOVERY SIMPLE) --> I did not run this, but may be someone could need it

3)SHRINK the DB log file --> now the log file is correctly back to less than 1GB

Thank you for your help

1

Run the following to see why the log can't be re-used:

SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'yourdb'

See this article for possible reasons.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.