I recently discoverd a nightmare on one of the servers I have taken over from another DBA in our group. The find was really by accident. Even though, I did so some preliminary documentation on the server.
You see, currently we use maintenance plans when setting up our servers. I actually have this on my list to fix. That will be another post and I am trying to put together a presentation for my first talk at our local user group, but I am getting off the subject if this post.
As I mentioned we use maintenance plans here. We also have an installation document for installs. I took over a project from the DBA that created the documentation, so I figured this person followed their own document. Silly rabbit tricks are for kids.
The maintenance plans are for system and user database. Each plan has we have 4 differnet subplans. One for the full backup, one transaction logs, one to check database integrity, and one to rebuild the indexes. What I discovered was this DBA included the last two steps into one.
Rebuild indexes, update statistics, and (wait for it, wait for it) shrink the database. Oh, no not that. It never occurred to me when seeing all the angels walking the halls around here without wings. If you don’t know what I mean, then you need to get on Twitter. But again, I am getting off the subject.
I found this little tidbit as a result of an error in the logs. I looked back through the history of the jobs and *fail*, *fail*, success, *fail*… So I removed the old failing subplan and created two seperate maintenance plans. Once for Datatbase Integrity Checks and one for Rebuilding Indexes. This was several weeks ago now and guess what? No more errors in the logs.
Let this be a lesson to you. When taking over servers from others go through it with a fine toothed comb and make sure things are setup the way you want them setup. Keeping in mind project requirements and SLA agreements.
Hope this helps. Let me know if you have any questions and check back. I’ll have some addition posts about replacing the maintenance plans.