Wednesday, May 02, 2012 5:19 PM
OS: Windows Server 2003 SP2 R2 Enterprise
SQL Server: 2008 R2 Enterprise
Database: Simple Recovery Mode
I am trying to free up log space from a database. But, I am unable to. select log_reuse_wait, log_reuse_wait_desc from sys.databases returns 6 and 'REPLICATION'. The Database ID for this database is 6 but there is no REPLICATION set up. I was wondering how I could bring log_reuse_wait_desc from REPLICATION to NOTHING, and then run, dbcc shrinkfile(log, 1000)
What I have tried:
1. DBCC SHRINKFILE (Database_log, NOTRUNCATE)
2. DBCC SHRINKFILE (Database_log, TRUNCATEONLY)
3. DBCC SHRINKFILE (Database_log, 1000)
4. sqlperf(logspace) returns 94% log space used
I could try attach and detach of database as the last step. But, wanted to find out if there are other ways to release log space.
- Edited by Jeelani Kamal Wednesday, May 02, 2012 5:20 PM
Wednesday, May 02, 2012 5:47 PMModerator
DBCC SHRINKFILE is in general la bad practice because the file will grow right back and take even more resources to grow halting other transactions up.
Is the database under or involved into replication(s)?
If the db backup does not exist then this is the primary cause, back it periodically up and the log will shrink.
Arthur My Blog
Wednesday, May 02, 2012 5:58 PM
I ran the proc, exec sp_removedbreplication, and was able to move log_reuse_wait_desc to NOTHING. I was then able to shrink the logs.
I was wondering if anyone would have the insight on why a database with simple recovery model with no replication could end up with log_reuse_wait_desc=REPLICATION.
Wednesday, May 02, 2012 6:42 PMModerator
AFAIK its a bug and its documented over at [http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx] and see the alternatives to fix the issue.
Wednesday, May 02, 2012 6:59 PM
Thank you. The article refers to a database in a snapshot replication. However, the database I am referring to is not involved in a snapshot replication. So, I am assuming that either way, it is a bug.
- Edited by Jeelani Kamal Wednesday, May 02, 2012 6:59 PM