May 3, 2007 at 1:23 pm
Let me say I am not a SQL Server administrative guru, actually I am a .NET developer, but I have inherited the DBA tasks for our database. The problem is, is that there are no current "maintenance" activities taking place other than a nightly database backup. We have a 30 GB (used space) SQL Server 2000 database and we are already receiving some timeout errors in some of our applications, as well as, some full transaction log errors when we try to manually rebuild indexes (DBCC REIndex).
I guess my question is what would be a good start as far as determining some regular maintenance activities/scripts that need to take place? I know this is very high level but at the least what should be done on a periodic basis and in what order? For instance, I know we should rebuid indexes and statistics on a regular basis but should we turn auto shrink off and periodically run DBCC commands to shrink the database and/or log files? Any help or reference to some type of high level article detailing a generic plan would be greatly appreciated. Thanks.
May 3, 2007 at 2:36 pm
I would personally turn off auto shrink as its only real use is on development machines where space is at a premium. You can find numerous articles on why its bad but on production system it should never be on.
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
Also although auto grow is useful in case you forget to keep an eye on your databases and they run out of space you should again never let it actually happen as again it will really hurt performance as it can take quite a while for the files to grow.
http://www.sql-server-performance.com/sql_server_performance_audit6.asp
http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyAndHow.aspx
To be honest the above are likely candidates for your time-out issues and if you do need to shrink databases do it manually
So basically you should size the database and the transactions logs so that they do not need to auto-grow.
Indexes are more problematic to advise on because it depends on the acivity in your database when they will need to be rebuilt but there are lots of scripts about which will rebuild them automtically depending on how fragmented they are which are quite useful.
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1475
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1822
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=797
hth
David
May 3, 2007 at 3:00 pm
Running DBCC CHECKDB is very important. You do not want your db corrupted little by little without knowing it.
May 3, 2007 at 3:10 pm
David...
Thanks for the reply. One more quick question. Let's say I
How should I incorporate shrinking of log files into this since they will become quite large I'm sure ?
Should I have a script (or collection of) that does the following:
I am with a smaller start up company that really has no DBA nor any regularly scheduled "maintenance" scripts so I am trying to get a "skeleton" of a plan started as far as what to run and in what order to run them.
May 3, 2007 at 3:25 pm
If you need to reduce the size of the log after rebuilding the indexes then I would use dbcc shrinkfile and put this as the final step in your job which re-indexes the database.
If you dont need point in time recovery you could just set the database to simple and this would keep your transaction logs small if you do need point in time recovery then you will need to back them so that they get truncated just doing a db backup won't do it.
David
May 4, 2007 at 10:26 am
I would rearrange your list and put DBCC DBREINDEX at the top. I generates a huge amount of log information, sometimes as large as the database itself. If you think you'll ever need to shrink the log, it would be right after DBREINDEX (and after a log backup, of course).
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply