Any good tools to automate SQL Server management tasks?

Question

I know I could write scripts and create jobs to run them, but at least some of what I'm wanting it to do is beyond my programming abilities for that to be an option.

What I'm imagining is something that can run on a regular schedule that will examine all the databases on a server and automatically shrink data and log files (after a backup, of course) when they've reached a file size that contains too much free space. It would be nice if it could defrag index files when they've become too fragmented as well.

I guess what I'm probably looking for is a DBA in a box!

Or it could just be that I need better performance monitoring tools instead. I know how to take care of both of those issues, but it's more that I forget to check for those issues until I start seeing performance issues with my apps.

Answer 1

That stuff is all built in, it is called a maintenance plan

Answer 2

If you are using SQL Server 2005. Fire up the Management Studio and look at the Maintenance Plan section.

See http://msdn.microsoft.com/en-us/library/ms187658.aspx for an overview and http://msdn.microsoft.com/en-us/library/ms189036.aspx for details on the Maintenance plan wizard.

Finally, http://msdn.microsoft.com/en-us/library/ms140255.aspx is a list of all the maintenance tasks available.

I am pretty sure this is all available even in the Express Edition. I can't speak to if anything has changed in 2008, I haven't used it yet.

Answer 3

yeah everything you described (except maybe perf monitoring) can be done with database maintenance plans, back ups, shrinking log files etc.

Answer 4

I guess the tool I was looking for was under my nose the whole time! I've used Maintenance Plans for backups but I think I set those up at least 4 years ago or more, long before I knew anything about shrinking files and defragging indexes. Thanks!