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

obiwankesoze
  • 33
  • 2
  • 5

1 Answers1

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.

adaptr
  • 16,576
  • 23
  • 34