SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Simple Recovery - Transaction Log Not Emptying
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

edmays
Starting Member

6 Posts

Posted - 02/09/2009 :  18:11:06  Show Profile  Reply with Quote
sodeep: Yes, I have read the link you provided. No, it did not make it clear. Is there a way to determine the MinLSN? Why does the log_resuse_wait_desc return "Nothing"? What is the relationship between log_reuse_wait_desc and MinLSN?

Edited by - edmays on 02/09/2009 18:12:16
Go to Top of Page

ryan13in
Starting Member

1 Posts

Posted - 06/17/2009 :  18:37:58  Show Profile  Reply with Quote
Hi ,

I have a similar problem . my log file continues to grow even though my recovery model is simple .. there are no active transactions as well ...

The log file grow to such an extent till there is no space on the disk. At this point the dataabse tries to do a self recovery and hence locks out users trying to access the application.

I have set up a job every 15 mins to truncate logs and shrink the database, and this is helping me to a good extent .. However since the Backup .. tuncate_only is deprecated in 2005, my errors logs have a entry saying the command is deprecated ..

How do I fix this problem??

Ryan D
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36582 Posts

Posted - 06/17/2009 :  18:57:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
You do have some kind of large transaction or perhaps lots of uncommitted transactions causing this. You'll need to watch SQL Profiler to see what's going on.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

danst01
Starting Member

USA
1 Posts

Posted - 08/06/2010 :  11:23:44  Show Profile  Reply with Quote
Did you ever get a definitive answer or solution to this problem? I am seeing the same behaviour.

Simple Recovery - Transaction Log Not Emptying

Thanks.

SteveD
Go to Top of Page

Laura Beranek
Starting Member

USA
9 Posts

Posted - 08/30/2010 :  14:34:04  Show Profile  Reply with Quote
I am having a similar problem. I have a database in simple recovery and DBCC OPENTRAN returns no open transactions. In production, the log_reuse_wait_desc value is ACTIVE_TRANSACTION and in test, the value is 'NOTHING'. I tried issuing the CHECKPOINT command which did nothing. I have not tried to truncate the log and I am running SQL Server 2008. I am not even sure I can truncate the log in 2008...I need to research that. There was mention to run SQL Profiler...if I do that, what am I looking for? This database is used frequently in production and hardly at all in test. In prod, there would be a lot of activity so I'm not sure what specifically I would be looking for. In prod, the log file is 88% full and in test, 48% full. The prod one continues to grow.

Please let me know if you have any ideas of what I could do to fix this.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36582 Posts

Posted - 08/30/2010 :  14:37:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
Laura, please start a new thread on this. You likely didn't run DBCC OPENTRAN in the right database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Laura Beranek
Starting Member

USA
9 Posts

Posted - 08/30/2010 :  14:54:48  Show Profile  Reply with Quote
My problem sounds exactly like the problems described in this thread. Also, I definitely ran DBCC OPENTRAN against the correct database. Do I still need to start a new thread?
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000