September 7, 2010 at 3:51 am
Hi,
One of our production server(sql 2005 Enterprise edition) db log size is increasing
MDF size : 10240 KB
LDF size : 69277760 KB
How to Restrict & Reduces The LDF file sizes
critical issue replay immediatly
September 7, 2010 at 3:53 am
you really need to do a log backup
have a read of this
September 7, 2010 at 4:01 am
Change the growth option of the log file to in MBS instead of in percentage.
Check if there is any BULK operation is going you can change the reciovery model unless it is not production
Check if large transactuion is there make it small and close the transaction once it is commited etc
"More Green More Oxygen !! Plant a tree today"
September 7, 2010 at 4:32 am
jamessdb (9/7/2010)
How to Restrict & Reduces The LDF file sizes
What are your backup plan strategies ( in case of log files) ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 7, 2010 at 5:02 am
As mentioned already, log backups are critical. I do them every 15-30 minutes on production databases. That helps control the log size, and give you good point-in-time recovery options.
September 8, 2010 at 5:49 am
Thanks to all
Daily we are taking full backup only, no Trn backup
the original backup size is 30MB only suddenly it increased to >60GB
Recovery model is FULL
how to minimize the unused space in the db
is it recommended to run below script in prod server
backup log [dbname] with truncate_only
go
DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY)
go
September 8, 2010 at 6:26 am
No it is no recommended to run that.
Take a look through this article - http://www.sqlservercentral.com/articles/64582/
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
September 8, 2010 at 8:19 am
jamessdba (9/8/2010)
Thanks to allDaily we are taking full backup only, no Trn backup
the original backup size is 30MB only suddenly it increased to >60GB
Recovery model is FULL
There's your problem: " ..no Trn backup.."
Basic guidelines:
1) For databases (prod or dev) where point-in-time recovery is critical: FULL model with frequent transaction log backups.
2) For static databases: SIMPLE model with daily full backups (or differential also).
Much more to the topic, but you should do #1 to get started.
September 8, 2010 at 9:14 am
So you have the basic advice. Here's what I'd recommend you do right now:
1. First, make sure that you have a good full backup.
2. Switch to simple mode, then back to full
3. Take a full backup
4. setup transaction log backups, I'd go for every hour to start, and monitor the sizes.
5. Use dbcc shrinkfile to shrink the log file down to a reasonable size. Look at your hourly log backups, which will give you an idea of how large the log should be. Leave a pad.
Don't shrink your data files unless you really need to. You ought to have enough space in there to handle the normal maintenance (index rebuilds) as well as a few months of data growth.
September 8, 2010 at 11:20 am
Steve is right there. Follow his guidelines.
Also you need to know that you should shrink the log file only for the first time since it has grown to a huge size.
After that, regular and frequent TL backups will ensure that Log doesn't grow at a rapid pace.
If you think that the Log is still growing you need to reduce the TL backups frequency to 30 mins, 15 mins or even 10 mins based on your environment and the Log growth.
But for now, you should be pretty safe if you follow Steve's Guidelines.
Thank You,
Best Regards,
SQLBuddy
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply