SQL 2005 Database size - files and logs

[LastWords]

Hi,

We are running two SQL 2005 servers mirrored. One of our databases for a Navision 5 application has a ndf file that is 80GB and a log file that is over 500MB. The servers hardware is Xeon 3.0Ghz, 4GB Ram, and there isn't any high I/O on the disks, and also we are not running out of disk space.

However, as I'm not an SQL expert I was wondering if any of you could help me. Should I be regularly shrinking the database files and log files? Should I re-index index files and perform other maintenance tasks daily, weekly, etc? What would you do?

Any help would be appreciated.

Thank you

by Lastwords,

Maentwrog (n.Welsh): Celtic word for a computer spelling mistake.

 

[ramam1]

Hey there,
Run db verification

http://msdn.microsoft.com/en-us/library/aa258278(SQL.80).aspx

Run Index rebuild or reoorganize

http://msdn.microsoft.com/en-us/library/ms188388.aspx

Update statistics regularly (I do this daily)

http://msdn.microsoft.com/en-us/library/ms187348.aspx

Backup your server (Full Backup)

http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx

Additionally f you are running in Full or Bulk recovery mode you should schedule regular backups of your transaction logs (same link different parameters to command).

Depending on your needs you may want to run differential backups (same link different parameters).

All of these things can be scheduled via the GUI with some clicking. The frequency of each of them depends on the rate of change of your DB, maintenance window sizes and timing, tolerance fo data loss, recovery speed demands in case of failures (ie disk crashed... how long before you are back up and running). You might consider mirroring, transaction log shipping, or HA clustering depending on your requirements.

You might read this FAQ:

http://www.tek-tips.com/faqs.cfm?fid=5742

Here are a few good resources in addition to this site:

http://www.sql-server-performance.com/

http://lessthandot.com/

and of course the bible:

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Have fun!
ram

 

[ramam1]

I forgot to mention but you should remember to ...

Test Your Backups Frequently. If you don't test them when you don't need them you will end up testing them when you do need them and it will not be as fun if they don't work for some reason

http://msdn.microsoft.com/en-us/library/ms186858.aspx

Be sure to look at the docs for the version of MSSS you are using. Most of the above are for 2005 or 2008... the feature set varies a bit.

 

[LastWords]

Brilliant advice Ram.

A few more questions: Could running db verification, index rebuild, or any of the above affect performance on a live database?

Also, so there is no need to shrink a database? Whilst researching this some people seemed to think shrinking a live database was a bad idea, whilst others, inc. Microsoft techies, seemed to think it was fairly good to do.

What are your thoughts on shrinking the database?

by Lastwords,

Maentwrog (n.Welsh): Celtic word for a computer spelling mistake.

 

[mrdenny]

You should pretty much NEVER need to shrink a database.

You can run DBCC, index rebuilds, etc on a live database however there will be an impact to your users.

DBCC which cause slowness when it runs.

Index rebuilds are an offline operation unless you have SQL Server 2005 Enterprise or newer. Index degrags are an online operation, but this will cause slowness on the SQL Server.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[ramam1]

Why would you need to shrink your DB? Normally files grow as needed - if the files are big it is because it is or was necessary to be that size. Shrinking and growing repeatedly is not good for performance as you are likely to end up with significant fragmentation.

Maintenance ops will put a load on the DB and thus impact performance on other work loads. Some maintenance can't be done with DB online. Best to do maintenance off hours if possible.

Have fun!