database maintenance issue

[masds]

We have a database that increase the size continuously, the database is updated every early morning by batch updating SQL, then keeps still in the day time. The only scheduled maintenance job is shrink the database to 50M free space, and the SQL Server is always running and never supposed to be restarted. Is this the correct setting? Do I need to drop and recreate indexes regularly and restart the SQL server regularly? Is there any risks if I just leave it run as is forever? Thanks

 

[Catadmin]

I'm reminded of that rotissiere commericial "Set it and forget it!". The funny thing about the commerical is, when you get your product in the mail, the first thing the how to book says is "Don't take 'Set it and forget it' literally!"

The same is pretty much true for SQL Server. Theoretically, nothing is wrong with those settings. IRL, though, if you don't do manual checking or the occasional manual shrinkage along with creating a backup plan, a database(s) integrity plan and the occasional performance tuning, you could end up with a bloated and potentially corrupted database on your SQL Server.

Ever hear of the phrase 'OS Rot'? I've seen similar things happen with SQL. You have to do preventive maintanence on SQL which sometimes requires stopping and restarting the service. Heck, I recommend doing preventive maintanence on the OS too, which might require the occasional machine reboot. If you don't, you're just asking for trouble.

You can leave the job scheduled without any changes if you so desire. Nothing wrong with that. Make sure you run Profiler to get a baseline on all your machines, though, during busy times preferable. Then, occasionally run Profiler to check current performance against that base line. Then use the traces to run the Index Tuning Wizard (unless you have predefined indices that you don't want SQL to overwrite). Then run DBCC (all commands) on a regular basis.

Double check your backups, do some manual shrinks. And yes, drop and recreate your indices as the database grows, so it doesn't become a big mess that's hard to search through.

Remember, just like in dental care, regular checkups are essential to a health SQL Server and the prevention of gum disease... Or OS Rot..



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[masds]

Thanks a lot, catadmin

Make sure you run Profiler to get a baseline on all your machines, though, during busy times preferable. Then, occasionally run Profiler to check current performance against that base line.

Do you really mean performance monitor, I heard that before but I don't know how to create a pmon baseline and how to compare it with the current performance numbers, can you tell me how to do that in a little detail?

 

[SQLBill]

SQL Server has it's own version of Performance Monitor. It's called Profiler. (Two ways to get to it: Start>Programs>Microsoft SQL Server>Profiler or Enterprise Manager>Tools>SQL Server Profiler). When you run this, the default values are for SQL Server.

-SQLBill

Posting advice: FAQ481-4875

 

[masds]

I mean the baseline features should be for performance monitor and not available in SQL Profiler which is used to trace problems, thanks