SQL Server Optimization Job | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Server Optimization Job

This is the canned SQL Server job in the Management section when you create a new database maintenance plan. I run this job every night and have a couple of third party applications that lose their database connectivity during this job and it causes problems. I know this job does alot to optimize the database, and I assume that it can cause connections to be broken or the ability to not attach to the database because of the work it is doing. Is there a wwy around for this? Anyone else ever run into this? What do you do? Thanks, Warren
When you run database maintenance plan, this also include Reindex. Reindex has to run alone, because it lock tables.
I suggest to run the job in non working times. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
Yes, the job is run in the middle of the night but cuts database connections to the application which causes an applicaiton re-start in the mornings. Any way around this? Thanks, Warren
I’m afraid no. To keep performance, you have to run those jobs. If you have not hours free on working days, then run it on weekends.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
You might want to consider creating your own script for optimization. This way, for example, you can use DBCC INDEXDEFRAG instead of DBCC DBREINDEX. See thishttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx for a discussion of both concepts. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Usually its better to schedule the optimisation jobs for weekends as it includes Index rebuid.On daily basis you can run backup DB,BackupLOg .
Thanks for all your help …will do. Warren
What all does the optimization job do? Does it only re-build indexes? Thanks. Chris – Chris
]]>