Free up log space
-
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 usedI 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.
Thank you.
-Jeelani
- Edited by Jeelani Kamal Wednesday, May 02, 2012 5:20 PM
All Replies
-
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.
Thank you.
-Jeelani
- Edited by Jeelani Kamal Wednesday, May 02, 2012 5:59 PM
- Edited by Jeelani Kamal Wednesday, May 02, 2012 6:00 PM
- Edited by Jeelani Kamal Wednesday, May 02, 2012 6:00 PM
-
Wednesday, May 02, 2012 6:42 PMModerator
Jeelani,
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.
Sankar Reddy
Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/- Proposed As Answer by Ahmed Ibrahim - MSFTMicrosoft Employee Wednesday, May 02, 2012 6:44 PM
- Marked As Answer by Jeelani Kamal Wednesday, May 02, 2012 9:12 PM
-
Wednesday, May 02, 2012 6:59 PM
Sankar,
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.
-Jeelani
- Edited by Jeelani Kamal Wednesday, May 02, 2012 6:59 PM
.png)
