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
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

NewHampshire
Starting Member

14 Posts

Posted - 01/21/2009 :  10:37:26  Show Profile  Reply with Quote
Here's my current situation. I have a database that gets a nightly feed of about 1 gig. It is set to simple recovery, but for various reasons the transactions are mostly fully logging. I'm working on that (I'm new here) but in the meantime, I am perplexed why the log, 1. Gets so big (15GB) and 2. Doesn't empty.

Usually in my past experience, this means I have a transaction open. But not here. Any ideas?

Thanks.

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/21/2009 :  10:40:54  Show Profile  Reply with Quote
Do your nightly feed in batches.
Go to Top of Page

NewHampshire
Starting Member

14 Posts

Posted - 01/21/2009 :  10:54:11  Show Profile  Reply with Quote
Bringing it over in batches would reduce the number of transactions, certainly.

But the db isn't actually doing much with ths data now, so the file growth is out of whack. And the fact that the feed is in one piece might explain a large log file, but doesn't explain why it isn't clearing.

I'd rather unerstand the issue rather than reduce the pain by changing the feed. I'm new here, and these explorations help me learn the environment.
Go to Top of Page

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 01/21/2009 :  10:59:14  Show Profile  Reply with Quote
>>Bringing it over in batches would reduce the number of transactions
also, issue CHECKPOINT between those batches...
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/21/2009 :  11:09:16  Show Profile  Reply with Quote
Open transactions( Long running tran) will not truncate inactive portion of log even in simple recovery model. I wouldn't issue checkpoints in between as Simple recovery model already does automatic checkpoint.
Go to Top of Page

NewHampshire
Starting Member

14 Posts

Posted - 01/21/2009 :  11:25:55  Show Profile  Reply with Quote
Right, I know that. That's why I said that there areno open transactions.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/21/2009 :  11:31:57  Show Profile  Reply with Quote
I mean while you are doing Nightly Feed? How Long does it run? Shrink it with DBCC shrinkfile to manage growth.
Go to Top of Page

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 01/22/2009 :  09:40:28  Show Profile  Reply with Quote
sodeep, even though checkpoint is performed automatically in Simple Recovery model what will happen when you do?

DELETE TOP (20000000) FROM MyTable
even with simple recovery mode your log file will explode atleast based on my understanding. You will have to delete in batches and issue a checkpoint between those batches.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/22/2009 :  09:45:35  Show Profile  Reply with Quote
Here is from Books online:

The Database Engine cannot recover from an interrupted checkpoint. If a checkpoint is interrupted and a recovery required, then the Database Engine must start recovery from a previous, successful checkpoint.
Go to Top of Page

edmays
Starting Member

6 Posts

Posted - 02/09/2009 :  15:57:52  Show Profile  Reply with Quote
I'm having a similar issue. The database is a reporting database that gets loaded once daily. The daily transaction load is about 300MB. The database is using the simple recovery model. The log file continues to grow after each day's import. I thought that the simple recovery model allowed the existing transaction log to be re-used. I would prefer to keep the transaction log at a fixed size so that I don't have excessive file maintenance and fragmentation.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/09/2009 :  16:06:20  Show Profile  Reply with Quote
You should do in batches for Huge DML operations.
Go to Top of Page

edmays
Starting Member

6 Posts

Posted - 02/09/2009 :  16:19:46  Show Profile  Reply with Quote
Whether I do batches or not, there will be transactions that fill the log. Why does the log continue to grow, even in simple recovery?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 02/09/2009 :  16:24:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by edmays

Whether I do batches or not, there will be transactions that fill the log. Why does the log continue to grow, even in simple recovery?



Because that's how SQL Server works. It always uses the log for data modifications, regardless of recovery model used. The recovery model just affects what happens after the transaction completes.

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

Subscribe to my blog
Go to Top of Page

edmays
Starting Member

6 Posts

Posted - 02/09/2009 :  16:37:28  Show Profile  Reply with Quote
I understand that SQL Server puts transactions in the log. How does it reclaim log space in Simple recovery mode? MS says that it does it automatically. From the overview of recovery models on TechNet:
Simple
No log backups.
Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.

However, my log file keeps growing. It's not automatically reclaiming log space.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/09/2009 :  17:03:05  Show Profile  Reply with Quote
This is from Booksonline:


If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:
The log becomes 70 percent full.


The number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.

Long-Running Transactions
The active portion of the log must include every part of all uncommitted transactions. An application that starts a transaction and does not commit it or roll it back prevents SQL Server from advancing the MinLSN. This can cause two types of problems:

If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take considerably longer than the amount of time specified in the recovery interval option.


The log may grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model, in which the transaction log is normally truncated on each automatic checkpoint. Replication Transactions
The active portion of the log must also contain all transactions marked for replication, but that have not yet been replicated to a subscriber. If these transactions are not replicated in a timely manner, they can also prevent truncation of the log.

Edited by - sodeep on 02/09/2009 17:05:48
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/09/2009 :  17:08:27  Show Profile  Reply with Quote
If you are looking more deeper explanation, dig in here.

http://msdn.microsoft.com/en-us/library/ms179355.aspx
Go to Top of Page

edmays
Starting Member

6 Posts

Posted - 02/09/2009 :  17:14:42  Show Profile  Reply with Quote
Thanks. The database has no replication, and there aren't any uncommitted transactions. In fact, checking the log_reuse_wait_desc, it returns nothing. I would have thought that at least it would have been waiting for a checkpoint.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/09/2009 :  17:29:19  Show Profile  Reply with Quote
quote:
Originally posted by edmays

Thanks. The database has no replication, and there aren't any uncommitted transactions. In fact, checking the log_reuse_wait_desc, it returns nothing. I would have thought that at least it would have been waiting for a checkpoint.



Then why don't you shrink the log file?
Go to Top of Page

edmays
Starting Member

6 Posts

Posted - 02/09/2009 :  17:35:30  Show Profile  Reply with Quote
That's an option. Not one that I want to pursue right now, because I would be doing it without knowing why the behavior is occurring. Additionally, shrinking and expanding the log file is going to fragment the files. There has to be a reason why it's not allowing reuse.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/09/2009 :  17:36:56  Show Profile  Reply with Quote
DID YOU READ THE LINK I POSTED TO MAKE CLEAR TO YOU?
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/09/2009 :  17:42:56  Show Profile  Reply with Quote
Here is what it says:

Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. The log may grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model, in which the transaction log is normally truncated on each automatic checkpoint.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next 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.09 seconds. Powered By: Snitz Forums 2000