fuzzyocelot
Programmer
Hi everyone,
I was recently asked to maintain a new server that is running SQL Server 2005 (no service packs) which was setup by a vendor. So I started looking through it and found a few interesting things that I thought I'd ask about. Note: I already told them that it needs SP2.
They had set up some maintenance plans which was good. However, I thought they were a bit strange. Maybe it's just me. Does anyone know of a good reason why someone would want to reorganize the indexes and then rebuild them in the same plan for the same databases? The rebuild is set to occur right after the reorganization. This just seems strange to me.
They set up a separate plan for integrity checks per database, a separate plan for the optimizations per database, and a separate plan for backing up each database. There are two user databases. So there are 6 total maintenance plans. Plus, the jobs for each plan are scheduled 5 minutes apart. These 6 jobs run every single night. Maybe it's just not clear to me, but wouldn't it have been easier to just put them in one or two plans instead of six? I will probably end up asking them why they set it up this way because I don't understand.
By the way, this server hasn't gone into production yet so I don't know how high the transactions will be or what the fragmentation level will be. Maybe they're assuming it will be high which is why they scheduled the maintenance plans to run every night. Actually, they run around 2:00 in the morning with the backups running last.
Which brings me to another question. Would it be a good idea to do a backup before the maintenance plans run (i.e. integrity checks and optimization) in case something breaks while the maintenance plan is running and then do a separate backup afterwards in case it fixed something? Just asking.
Thanks!
I was recently asked to maintain a new server that is running SQL Server 2005 (no service packs) which was setup by a vendor. So I started looking through it and found a few interesting things that I thought I'd ask about. Note: I already told them that it needs SP2.
They had set up some maintenance plans which was good. However, I thought they were a bit strange. Maybe it's just me. Does anyone know of a good reason why someone would want to reorganize the indexes and then rebuild them in the same plan for the same databases? The rebuild is set to occur right after the reorganization. This just seems strange to me.
They set up a separate plan for integrity checks per database, a separate plan for the optimizations per database, and a separate plan for backing up each database. There are two user databases. So there are 6 total maintenance plans. Plus, the jobs for each plan are scheduled 5 minutes apart. These 6 jobs run every single night. Maybe it's just not clear to me, but wouldn't it have been easier to just put them in one or two plans instead of six? I will probably end up asking them why they set it up this way because I don't understand.
By the way, this server hasn't gone into production yet so I don't know how high the transactions will be or what the fragmentation level will be. Maybe they're assuming it will be high which is why they scheduled the maintenance plans to run every night. Actually, they run around 2:00 in the morning with the backups running last.
Which brings me to another question. Would it be a good idea to do a backup before the maintenance plans run (i.e. integrity checks and optimization) in case something breaks while the maintenance plan is running and then do a separate backup afterwards in case it fixed something? Just asking.
Thanks!