October 25, 2010 at 11:32 am
Hi,
I ran a maintenance plan in the weekend on a prod server.
It had steps of checking database integrity, rebuilding indexes, updating stats and again checking database integrity. I ran this job on few databases.
When on monday I checked , the log file of a particular database had increased from 10gb to 85 gb.
Other databases also the % of log file has grew to a higher value.
Almost all databases log file has increased a lot due to this job.
How to decrease the log file again.
Should I add a shrink db maintenance task in that plan?
Does it do any ill effect on databases on a production server.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 25, 2010 at 12:15 pm
SKYBVI (10/25/2010)
Hi,I ran a maintenance plan in the weekend on a prod server.
It had steps of checking database integrity, rebuilding indexes, updating stats and again checking database integrity. I ran this job on few databases.
When on monday I checked , the log file of a particular database had increased from 10gb to 85 gb.
Other databases also the % of log file has grew to a higher value.
Almost all databases log file has increased a lot due to this job.
Perfectly normal. Index rebuilds are fully logged and hence can require a log of space in the log.
How to decrease the log file again.
Don't. It needs to be this size due to the maintenance you're doing. Leave it this size
Should I add a shrink db maintenance task in that plan?
Absolutely not! Worst thing you could consider doing.
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
October 25, 2010 at 1:34 pm
@ gila...
thanks.
Also, i have log shipping effective for those databases.
This morning, the log shipping was not in sync and I had to reconfigure the log shipping for that database (whose log file was 85 gb)..
After i configured log shipping for that database, the log file was 85 gb on the secondary server too and hence there was a less disk space issue (because last week it was only 10gb, before i ran tht job), so now that extra 75 gb has put the disk space very less..
As you advised that its perfectly normal and I dont need to shrink it( on the primary/prod server),
but on the secondary server , can I shrink the log shipped database so that I can resolve the disk space issue.
what are the alternatives, if I cant increase the disk space on secondary server ( in log shipping)
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 25, 2010 at 2:36 pm
Either change your maintenance so that you're not rebuilding every single index no matter what the fragmentation, or get more space to handle the large logs. Shrinking anything is a temporary solution at best, the databases will grow again, probably next time the index rebuild runs and you'll be right back where you are now.
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
October 25, 2010 at 3:04 pm
@ gila
So, that means you want to say that i can do shrinking but again whenever I perform the index rebuilds, the log file will again increase and then again I have to do shrinking, so its a viscious circle??
The best possible solution in this case is that I just need to reorganize /rebuild indexes on only selected ones ?
Am i getting it correct?
Thanks,
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 25, 2010 at 3:06 pm
Check the fragmentation level and rebuild indexes based on fragmentation.
setup job like that.
so it logs only that much .
how is log backup do you have for every 15 mins or no.
If none of the above is not possible for you .
Set recovery model to bulk logged
have backup every 15 mins.
Thanks,
SSDBA.
October 26, 2010 at 2:41 am
SKYBVI (10/25/2010)
@ gilaSo, that means you want to say that i can do shrinking but again whenever I perform the index rebuilds, the log file will again increase and then again I have to do shrinking, so its a viscious circle??
The best possible solution in this case is that I just need to reorganize /rebuild indexes on only selected ones ?
Am i getting it correct?
Yes and yes.
Rebuild what needs rebuilding, not everything. There are lots of good scripts available. Make sure that you have enough log and data space for what that rebuild will do
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
October 26, 2010 at 7:17 am
@ gila....
Thanks...
On the log shipping server, to free the disk space, if i shrink the log files on the prod server,
then automatically log files on log shipping server will get small OR
do i have to deconfigure log shipping, then shrink the database on prod server, then again reconfigure log shipping?
Regards
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 26, 2010 at 2:55 pm
@ gila
Is there a way of shrinking the database just on secondary(log shipping server) ??
Before doing any shrinking on prod server, if I backup the database, then is this fool proof?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 27, 2010 at 2:15 am
SKYBVI (10/26/2010)
Before doing any shrinking on prod server, if I backup the database, then is this fool proof?
This will not help you in space reclaim and Shrinking will not let the data loss.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2010 at 6:42 am
@ bhuvnesh
i cant get you what you mean to say?
What preventive measures I can take, before shrinking the log files on prod server?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 27, 2010 at 9:20 am
My solution for maintenance that is to occur during "scheduled downtime" (such as index rebuilding that causes the log to grow substantially) is to have steps in the job that first backup the log and e database, switch the DB over to simple recovery mode, perform the maintenance operation, then switch the database back to full recovery mode, back up the database and then the (empty) log. Your log is therefore kept small due to only being as large as the biggest LUW occurring in your maintenance cycle and substantially improves performance of the maintenance.
October 27, 2010 at 9:36 am
mike.hartman (10/27/2010)
My solution for maintenance that is to occur during "scheduled downtime" (such as index rebuilding that causes the log to grow substantially) is to have steps in the job that first backup the log and e database, switch the DB over to simple recovery mode, perform the maintenance operation, then switch the database back to full recovery mode, back up the database and then the (empty) log.
If there's log shipping configured that would require a compete recreation of the log shipping secondary after each maintenance window, as the switch to simple breaks the log chain. Same if there was database mirroring (and switching from full recovery is not permitted while mirroring is enabled)
Consider rather switching to bulk logged, the log won't grow as much (index rebuilds are minimally logged) and the log chain isn't broken.
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
October 27, 2010 at 9:49 am
I am not performing any log shipping in the night.
Just i have a full backup in night.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 27, 2010 at 9:52 am
Excellent advice, GilaMonster; Thanks! We haven't moved to log shipping (yet) but when we do your insight will come in handy!
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply