June 10, 2015 at 5:01 pm
Hi,
We have sql server 200r2 express edition.
One of the database is in simple recovery mode. It grown around 6 GB log in 45 days period. Why the log is growing large?
Do we need to schedule full backups frequently?
June 10, 2015 at 6:51 pm
If the log has grown that's because there has been either some very big transactions, or there's a transaction that's open and not closed preventing the other transactions from clearing. Check sys.dm_tran_active_transactions to see if you have old transactions that are still open.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 11, 2015 at 12:32 am
http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2015 at 7:15 am
Currently we don't have any scheduled full backups. We don't have sql agent.
So assuming with full back we can control the log size.
June 11, 2015 at 7:20 am
No. Full backups don't control the logs, at all.
Simple recovery, the logs clear all committed transactions at checkpoint. Backups, full or differential (which are all you can do) don't affect the log. Full recovery, you have to run log backups.
You either had one or more very large transactions that caused the log to grow, or you have an open transaction that's preventing the log from clearing out committed transactions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 11, 2015 at 7:26 am
But when I run
dbcc opentran
The results are nothing
June 11, 2015 at 7:29 am
OK. Then, the other option may be true. You had a one or more very large transactions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 11, 2015 at 8:47 am
Need help an example of
How to schedule weekly backup through windows scheduler tasks. No Sql server agent is available
June 11, 2015 at 9:10 am
I haven't set this up, but it shouldn't be at all hard. Documentation on the Task Scheduler is here. You'll want to use SQLCMD.EXE. You can just pass in a T-SQL BACKUP DATABASE command and you're done.
That's a different topic than the original question. To get more people to respond to that, you'd be better off posting it as a separate question.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 12, 2015 at 2:47 pm
Thank you
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply