October 18, 2010 at 7:51 am
Hi,
I have a rebuilding index job that starts at night 12 am and a shrink database logs job starting at 8am. Genrally they both run well but from the past few days the rebuild index job doesnt get over until the shruink database logs start and then they both run at same time and the server gets stuck or runs very slow.
So, I changed the schedule for them but everyday the job activity monitor shows that they are both running at earlier(prior one) schedule. I checked in the jobs schedules and they are already set at differnet time to run but still job activity monitor runs at the same time as mentioend earlier.
Why is that.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 18, 2010 at 9:00 am
I don't know why, but my 2 thoughts are:
1) Job 1 can be configured to start job 2 as the last step, to eliminate timing issues.
2) Why do you keep shrinking ? Do you have frequent transaction log backups running ?
October 18, 2010 at 9:19 am
@ homebrew
The job 1 is SSIS package
The log size increases considerably.
but the databases (on which iam performing shrink operation ) are all in simple recovery mode.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 18, 2010 at 9:23 am
are you rebulding all your indexes , or do you have logic put in place to rebuild based on fragmentation?
October 18, 2010 at 9:31 am
@ steve
Yes, iam rebuilding all my indexes
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 18, 2010 at 9:39 am
You should really only rebuild indexes based upon fragmentation usually >30% is a rebuild and below 30 is a regorganise, its a waste of resources to rebuild every index. Have a read of
http://msdn.microsoft.com/en-us/library/ms189858(v=SQL.90).aspx"> http://msdn.microsoft.com/en-us/library/ms189858(v=SQL.90).aspx
for some advice
October 18, 2010 at 10:30 am
You can have more than one step in an SQL Agent job. Move the second job into the first one, as Step 2.
Does the disk space taken up by the logs actually matter? Is there a reason to force them to shrink? If you let them grow to the size SQL Server needs for the heaviest use, and can afford the disk space, then the index rebuild will be faster and more efficient.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply