June 22, 2017 at 3:21 pm
Hello all.
I just started working for a SaaS provider that has over 200 SQL Servers with over 5000 SQL databases. The old methodology of running Database Integrity, Rebuild Index, Update Stats and backups is taking up to 8 to 10 hours to complete.
I was wondering if any of you gurus out there can help provide some "strategies" to get this done quicker.
Currently, here is what we are doing:
1. Midnight - all servers run DBCC CheckDB as a seperate job every day.
2. Reboot SQL server (most of them) every Sunday around 4:00 AM.
3. Run Re-Index and Update Stats only on Sunday morning, after reboot job (job taking about 8 hours per server).
4. Every night run Full SQL Backup (using LiteSpeed). Some days job can take up to 10 hours.
5. Run hourly T-Log backups.
All servers are virtual.
Obviously there is a lot that can be done (like staggering times, run on alternate days, etc). Just want to hear what you are all doing so I can get some ideas.
Thank you!!!
June 22, 2017 at 3:47 pm
Why are you rebooting the servers every week?
You can run checkDB on a secondary server. Restore the backups, run checkDB on those. Double win there, the backups are known to be restorable, and checkDB is done.
For reindex, don't use maintenance plans, use Ola Hallengren's scripts. The maint plan is major overkill
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
June 22, 2017 at 4:54 pm
To add a couple things to Gail's good points:
Unless there's a very good reason you're not doing it (say, nearly all the data is changed every day), I'd also suggest taking full backups less frequently, and making your nightly backups differentials instead.
Also, if you have the CPU headroom when you run your larger backups, look into using backup compression if you're not already.
Cheers!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply