January 20, 2012 at 4:10 am
Hi Guys,
We are using sqlserver2005. My Log file size is increasing heavily (~15gb).
We are this DB for Monitoring purpose and this happening every day. for work around our team is sinking Log file to deceasing its size.
For to resolved permanently i have Created backup plan for it.
1. Full backup with INIT Option(Overwriting the backup set due to disk space issue)
2. Taking the Log backup with Trancate_only Option for every 6 hours.
But its was not resolved, Today log file size went upto 12GB.
--Backup LOG <DB> to disk='C:\dfdfg\.trn' with truncate_only executing this job every 6hours will it correct.
Any Suggestions Pls....
January 20, 2012 at 4:18 am
Change the recovery mode to Simple. Read the following article first to understand the implications:
http://msdn.microsoft.com/en-us/library/ms175987.aspx
January 20, 2012 at 4:35 am
Earlier it was Simple.... i was just changed to Full and implemented Log backup
January 20, 2012 at 5:26 am
Why?
In simple recovery the log is automatically reused, in full recovery log reuse requires a log backup. Seems counter-intuitive to switch to full recovery model unless you need point-in-time recovery (and if you did, backing up the log with truncate_only is not the way)
Please read through this - Managing Transaction Logs[/url]
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
January 20, 2012 at 5:55 am
Sounds like you need to run your log backups much, much more frequently. None of the mid-sized to large systems I've run would have worked well with a 6 hour log backup cycle. Instead we were running them as often as every 10 minutes. I'd suggest at least every 30 minutes to start.
Also, check to see if you have open transactions from your code. We had an issue once where the app kept transactions open for hours at a time. That would fill the log horribly (among other issues).
"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
January 20, 2012 at 7:00 am
we are using this DB for monitoring Application and we are having less disk space.
So that we are planing to run every 6 hours.
When i issue DBCC Opentran() -- it was not showing any active transactions if atoll my Log file having the size of 10GB.
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I have checked in sys.database catalog status of log_reuse_wait_desc showing :Log backup.
--How can check which are currently active transactions and y Log getting filled up.
-- it was happening in simple recovery model also.
January 20, 2012 at 7:07 am
Please read through this - Managing Transaction Logs[/url] and http://www.sqlservercentral.com/articles/Transaction+Log/72488/
If you don't need point-in-time recovery (the ability to restore to a point-in-time or point-of-failure), switch the database to simple recovery model
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
January 20, 2012 at 7:20 am
It's possible you have an issue with long running queries or queries that are not being closed. This causes the VL to not be able to be reused, and therefore continuing growing.
January 20, 2012 at 7:22 am
Simha24 (1/20/2012)
we are using this DB for monitoring Application and we are having less disk space.So that we are planing to run every 6 hours.
When i issue DBCC Opentran() -- it was not showing any active transactions if atoll my Log file having the size of 10GB.
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I have checked in sys.database catalog status of log_reuse_wait_desc showing :Log backup.
--How can check which are currently active transactions and y Log getting filled up.
-- it was happening in simple recovery model also.
Well, you've got an issue. If you want point in time recovery of your data, you must have transaction logs. You can either let the log get really, really big and backup it up every six hours, using however much disk space that requires. Or, you can run frequent log backups in order to keep the log size down, using however much disk space that requires. There is no way to do both and use little to no disk space.
"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
January 20, 2012 at 8:27 am
I Hope it will helping me....
thanks for posting..
one more question: what exactly happen when we use With Truncate only option when we are backing up log
BACKUP LOG ... WITH TRUNCATE ONLY.
Will It Truncate Active Log by writing Active log to disk or what else it is doing..
How it will make more space in log
January 20, 2012 at 8:32 am
It means the inactive part of the log (transactions commited and flushed to disk) are removed logically. This does not release the space back to the operating system but will allow SQL Server to reuse the space that you just freed within the file.
January 20, 2012 at 8:34 am
Simha24 (1/20/2012)
one more question: what exactly happen when we use With Truncate only option when we are backing up logBACKUP LOG ... WITH TRUNCATE ONLY.
Will It Truncate Active Log by writing Active log to disk or what else it is doing..
How it will make more space in log
Did you read the articles that I referenced?
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
January 20, 2012 at 8:43 am
Database Mirroring, 12 GB log files, Full Recovery Model, 6 hrs Log Backup & low disk space cannot go all together.
As everyone already pointed out, you must set up the priority. Point-In-Time recovery & High Availability can’t be setup with ease in Simple Mode & low disk space. My first recommendation is to add more disk space & increase the frequency of log backups.
January 20, 2012 at 8:49 am
Where did you get database mirroring from?
p.s. In case anyone missed it, the OP's on SQL 2005 and the log is in auto-truncate mode (because it's been backed up WITH TRUNCATE_ONLY), so the frequency of log backups is immaterial at the moment because the database is behaving as if it were still in simple recovery.
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
January 20, 2012 at 9:02 am
Apologies, I misread ‘DB for Monitoring’ as 'DB Mirroring'.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply