July 19, 2011 at 12:41 pm
Hi all, not sure if this is the correct sub-forum to be posting this in, but anyway...
I've recently been given the responsibility of managing the maintenance of a database server.
Currently the database server is using a maintenance plan generated through the maintenance plans section of SSMS.
I'm not a huge fan of this option, since I can see just from quickly looking at it, that there are problems which are existing.
What I need to know is, from what already exists in the plan, which pieces I can remove or rewrite.
One thing to consider - I'm not going to be the main DBA for this database server. I'm currently basically a consultant. This means that any changes I make, should not be the kind where after a few months of me being gone, they are no longer valid.
Currently existing:
Step 1 - Check Database Integrity
-- This step I figure I'll leave in place. The advantage of the maintenance plan task is that if new databases are added to the database server, the maintenance plan will automatically run the CHECKDB for that database. This way if whoever takes over after me forgets to implement a job to manually do that, they won't be at risk of a corruption error.
Step 2 - Shrink Database
-- From everything I've read, this is one of those "evil, don't do!" things. But I'm not really sure if that is because people recommend a more elegant solution, or what. I've looked at the code that is running, and it is executing, for each database, this script:
DBCC SHRINKDATABASE(N'model', 5, TRUNCATEONLY)
Step 3 - Rebuild Index
-- This one I'm going to remove. I think it's not really a good idea to have, because it ends up taking a very long time to finish, and most tables don't really need to have their indexes be rebuilt anyway. For the big tables that do, I'm going to create jobs that rebuild those indexes alone, and schedule them at seperate times to avoid conflicts.
Step 4 - Update Statistics
-- No idea on whether this is necessary. The databases are all set to do auto computing of statistics, and from everything I've read, it seems like this means there's no real necessity to have a rebuilding of the statistics job as well. Can I just remove this completely? The code behind the job is running an UPDATE STATISTICS on each table.
Step 5 - Clean Up History
-- Again no real idea what this is doing. The code being executed is:
declare @dt datetime select @dt = cast(N'06/21/2011 14:39:25' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt
GO
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='06/21/2011 14:39:25'
GO
EXECUTE msdb..sp_maintplan_delete_log null,null,'06/21/2011 14:39:25'
-- It seems like this isn't really a good thing to be doing. I can't imagine the logs for job history and maintenance plan history take up much space. As for the backups, I've no idea what that is doing. There is a seperate plan handling creation of backups.
July 19, 2011 at 12:45 pm
Shrink Database fragments the database and hurts performance. Generally a bad idea, unless you are so low on disk space that you are about to crash the server.
There are many threads and articles with more detail about this.
July 19, 2011 at 12:54 pm
Problem is that disk space *is* an issue. We're currently looking at 24gb of free space, on a drive that has 150gb of space. There are plans to get more disk space, but this is something that will probably be taking several months to implement. Without the shrinking, I'd bet the disk space would be reduced to under 15gb before long.
July 19, 2011 at 1:46 pm
Well shrink database will only shrink the file if there's actually free space in the physical file to shrink. If there is free space SQL Server will use it before growing the file anyways and if your application needs that space it'll just grow again anyways after you shrink it.
Now if you're not planning on that specific database growing and you need that space on disk for another database or for another purpose then shrinking might be an option, otherwise it won't really help you.
July 19, 2011 at 2:35 pm
Okay, first - remove the shrink. It should never be a scheduled operation.
Second, since you are probably rebuilding all indexes - change the update statistics step to only update column statistics. The rebuild will update index statistics with a full scan when the index is rebuilt.
Third, remove the history cleanup step and move it to it's own sub-plan or plan (I use a separate maintenance plan myself). That step removes backup history, agent job history, etc...
Now, you need to add 2 additional steps. You need to include a step to remove old backup files and a step to remove old log backup files. This will be done using the maintenance cleanup task.
And finally, you need to check the recovery model of each database. If the recovery model is full - make sure you have another maintenance plan (or sub-plan) that is setup to backup the transaction logs for those databases on a frequent basis (e.g. every 15 minutes).
I would also recommend creating a separate plan (or sub-plan) specifically for the system databases and not include them in this plan. That way, you can manage them separately as needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply