February 27, 2008 at 9:45 am
Hi once again. I am relatively new to SQL and was hoping for some insight into 'General SQL Administration' (Maintenance Plans).
The database in question will be around (60GB). I was looking through the maintenance plan wizard at the various options.
When would it be advised to set a plan to:
A: Check Database Integrity
B: Reorganize Index
C: Rebuild Index
D: Update Statistics
What schedule might be appropriate (Weekly / Monthly). I appreciate that the correct answer will vary depending on individual circumstances, I am looking for a general rule. An earlier post advised NOT to shrink database (so I will leave that one alone!!).
Finally can you advise regards the appropriate use of the 'Database Engine Tuning Advisor'. How should this tool be used effectively? Will this make recommendations regards indexes (rebuilding).
Advice from you experts from real world experiences would be very much appreciated.
Regards,
Phil
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 27, 2008 at 10:23 am
Do a bi-weekly schedule and that too on non-production hours mostly prefer week ends nights to do these kind of tasts in my servers.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 1:07 pm
Thanks for the response.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 29, 2008 at 2:23 pm
Here is a basic outline of how we do ours. One note, a Rebuild Index is a little misleading, it not only rebuilds but also performs a Reorg.
User and System DBs
Integrity Check 0100 Sundays
Rebuild Index 0200 Sundays
Full backup 2200 Daily (.BAKs >2 days old deleted from server, but Tivoli sweeps them to archive nightly)
Most of our DBs are in Simple mode, so not a lot of attention given to the logs, we guarantee disaster recovery to the previous night's full backup. We do hourly log backups and delete any log backups older than a day. 😎
March 2, 2008 at 6:25 am
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 2, 2008 at 10:11 am
A: Check Database Integrity
B: Reorganize Index
C: Rebuild Index
D: Update Statistics
There is no need to reorganize and rebuild indexes. Rebuilding indexes removes all fragmentation and also updates the statistics. Keep in mind, rebuilding indexes can be a performance hog and should be done in a maintenance window. Additionally, you should be careful with disk space because rebuilding indexes inflates your transaction log size.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply