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