Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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