MS SQL (2000) Admins

[Izi]

I run a couple of webservers. Up until recently they have coped with what I have thrown at them. However, a couple of sites are now hitting 100k users/month and the dbs are getting bigger and the servers are slowing.

The DB files are around 1.5gb. The log files are around 10gb +

Can someone explain to me what "SHRINKDATABASE" command actually does? What can be done on the servers to help performance? How can I reduce the log file size ? This area is new to me so any help is much appreciated.

 

[Xinn]

While I'm not sure exactly what it does one of you SQL DBA's recently removed the shrink database step from all of our maintenance plans on all of our SQL instances after attending a course.

 

[iaind]

You need to backup the databases to truncate the log files!

 

[akakjs]

(adapted from SQL 2005 knowledge)

It sounds like, as iaind said, you need to backup the database. This clears a flag in the transaction log file, and allows SQL to start to re-use some of the space it's been allocated from the file system. Then if you find yourself short of space, you can shrink the transaction log. See this MS knowledge base article for a guide (and links to the relevant commands).

You should schedule regular backups, as MSSQL's design assumes it will be in an environment where it will be backed up regularly.

akakjs

 

[Izi]

Thanks for the replies.

I back up the DB files every 4 hours, but not the transaction log files. Should i include them in my back up schedual then?

 

[Izi]

Also what does "Reorganise data and index pages" actually do.

Any ideas on "Shrink database if it grows over x"..

I dont mind shinking the database, but i dont want it to effect the data with in it.

 

[Izi]

You need to backup the databases to truncate the log files!

I backed up the dbs + log files and the size remains the same.

If i use this code: dbcc shrinkfile (mydatabasename_log, 10); it shrinks it however.

Should a backup automatically shrink the log file?

 

[hudson]

Sound slike your maintenance plan needs tweeking to backup your transaction log AND database and then shrink it. This should remove a big chuff of data.

Aslong as your backup task completes and your transaction logs aswell then you will free up what "slack" there is available.

 

[akakjs]

As I understand it backing up allows the transaction log to "wrap" around to the start again, so regular transaction log backups will allow the log to re-use the space it already has. If you don't backup the transaction log will keep appending transactions to the end of the log (causing the file to grow).

Shrinking the transaction log, releases the unused space (that is the space marked as already backed-up) back to the file system. Note however that expanding the file again, is slower than just re-using space in the transaction log, so if you need speed in your system don't shrink too often.

On a side note, you don't need to backup your main database that often, you can combine full backups with transaction log backups to restore to any point since the last backup.

For example if you backup every night, and backup the transaction log hourly (keeping the last 24 hours of transaction log backups), you can restore up to the nearest hour, by restoring your nightly backup, then "replaying" your transaction log backups against the database again*. This keeps the amount of space you'll need to store your backup files down (and you gain a 24 hour history).

akakjs

* it's actually little fiddly to do the restore; so don't expect to have it running again in 5 minutes, but your data will be safe up until last hourly transaction log backup.

 

[Izi]

As I understand it backing up allows the transaction log to "wrap" around to the start again, so regular transaction log backups will allow the log to re-use the space it already has. If you don't backup the transaction log will keep appending transactions to the end of the log (causing the file to grow).

Shrinking the transaction log, releases the unused space (that is the space marked as already backed-up) back to the file system. Note however that expanding the file again, is slower than just re-using space in the transaction log, so if you need speed in your system don't shrink too often.

On a side note, you don't need to backup your main database that often, you can combine full backups with transaction log backups to restore to any point since the last backup.

For example if you backup every night, and backup the transaction log hourly (keeping the last 24 hours of transaction log backups), you can restore up to the nearest hour, by restoring your nightly backup, then "replaying" your transaction log backups against the database again*. This keeps the amount of space you'll need to store your backup files down (and you gain a 24 hour history).

akakjs

* it's actually little fiddly to do the restore; so don't expect to have it running again in 5 minutes, but your data will be safe up until last hourly transaction log backup.

OK thanks for the reply...

So for me to 'Start again' I should back up my transaction logs and dbs.

Truncate and shrink my log files back to say 100 MBs.

Create an hourly (or so) maint plan to back up tran logs.
Create a daily maint plan to back up dbs?

Is that about right?

 

[akakjs]

OK thanks for the reply...

So for me to 'Start again' I should back up my transaction logs and dbs.

Truncate and shrink my log files back to say 100 MBs.

Create an hourly (or so) maint plan to back up tran logs.
Create a daily maint plan to back up dbs?

Is that about right?

That sounds about right. Your transaction log size will increase a bit, but then stabilize after a while (depends on the average # of rows your transactions change, and how many transactions you process a day).

An important thing to note when your hourly transaction log backup, make sure you keep at least 24 hours of old log file backups (backup your tlogs to a seperate directory from the main backups, and check the clean-up files checkbox in the backup wizard.)

akakjs