I am my company's Sys Admin, and although I know enough to get around in SQL, I don't know THAT much. Currently we are using SQL 2005 on a Win2K3 Enterprise Server for a major app, and I keep running into issues where I am constantly running out of space. I'm sure it's just a matter of me not having the maintenance plan setup correctly or something, but was wonder if anyone could give me specific steps to so I can compare them with my job? I have three databases, and would like to clean up the database and shrink it however the most optimum way would be. I'd like to also remove transaction logs at whatever the most optimum way would be. Meaning, keep them however often I need to, but only the amount I need to. I backup the DB every night to my Veritas program and tape drive. Thanks so much for all your help in advance.

1. If these are enterprise-level applications, the recovery model should be set to Full so that you could do a point-in-time recovery if needed.

2. Keep doing your nightly backups (I assume these are Full backups of the databases), but add a Transaction Log backup to your Maintenance Plan. A TL backup will free up space in the transaction log itself and prevent it from growing out of control. The frequency of your TL backups will depend on how heavily the database is used. Maybe start with twice a day and see if that keeps your log files from growing. Of course, be sure to back up your TL backups to tape as well.

3. Don't turn on autoshrink or use the Shrink Database Maintenance Plan task. It can get you into a grow-shrink cycle that can fragment your db files and cause performance issues. If the Transaction Logs are backed up regularly, you won't need to shrink them.

4. If you like, use the Maintenance Cleanup Task in your maintenance plan. This will automatically delete old backup files that you no longer need and free up the needed space on your server.

10 Replies

I'm a SQL hack and no expert, but have used that version. You can do maintenance on the databases and this is very common. What I can tell you is that the biggest issue I have run through is the log files and not the db itself. May be different for you. I run full maintenance daily via SQL and I chose the Simple Recovery method. Downside is that you are only able to recover from the SQL snapshots. Using the Full method, you can recover from a particular point in time. For my use, I chose Simple. The Full method can fill up with a lot of log files. Here is a quick link (that is for 2008) but I believe applies to 2005 in theory:

1. If these are enterprise-level applications, the recovery model should be set to Full so that you could do a point-in-time recovery if needed.

2. Keep doing your nightly backups (I assume these are Full backups of the databases), but add a Transaction Log backup to your Maintenance Plan. A TL backup will free up space in the transaction log itself and prevent it from growing out of control. The frequency of your TL backups will depend on how heavily the database is used. Maybe start with twice a day and see if that keeps your log files from growing. Of course, be sure to back up your TL backups to tape as well.

3. Don't turn on autoshrink or use the Shrink Database Maintenance Plan task. It can get you into a grow-shrink cycle that can fragment your db files and cause performance issues. If the Transaction Logs are backed up regularly, you won't need to shrink them.

4. If you like, use the Maintenance Cleanup Task in your maintenance plan. This will automatically delete old backup files that you no longer need and free up the needed space on your server.

Not sure about Veritas, but using Backup Exec 11 we run a full trans log backup every 6 hours. It keeps trans log sizes to a minimum, because without that they can grow out of control easily.

If your server isn't already set up to have transaction logs on separate volumes (separate physical disks is ideal), you'll want them set up that way. Having trans logs and data share volumes means the data growth is more than doubled per transaction, and causes real issues if you run out of space on the disk.

1. If these are enterprise-level applications, the recovery model should be set to Full so that you could do a point-in-time recovery if needed.

2. Keep doing your nightly backups (I assume these are Full backups of the databases), but add a Transaction Log backup to your Maintenance Plan. A TL backup will free up space in the transaction log itself and prevent it from growing out of control. The frequency of your TL backups will depend on how heavily the database is used. Maybe start with twice a day and see if that keeps your log files from growing. Of course, be sure to back up your TL backups to tape as well.

3. Don't turn on autoshrink or use the Shrink Database Maintenance Plan task. It can get you into a grow-shrink cycle that can fragment your db files and cause performance issues. If the Transaction Logs are backed up regularly, you won't need to shrink them.

4. If you like, use the Maintenance Cleanup Task in your maintenance plan. This will automatically delete old backup files that you no longer need and free up the needed space on your server.

Hope this helps.

+1. You do NOT want to shrink your database repeatedly, or you will get fragmented pages.

If you want to get really fancy, you should set the size of your tlog files ahead of time and don't let them auto-grow. Ditto on the temp DB's. Otherwise you risk disk fragmentation (any new bits of data beyond the existing file may not be put on the drive in the right place).

You could do the same for the actual data files, but you really have to be sharp with sizing when you do that.

Also somebody mentioned above setting your recovery mode to full. That's certainly one option, and it gives you the ability to restore to a specific transaction in the DB.

However you could look at the DB, the amount of data that is updated, etc and go from there. We run plenty of DB's in simple recovery mode (no transaction log) but we run backups every 15 minutes (full backups 2x a day, diff backups every 15 minutes).

Either way, if you're backing up the T-log make sure whatever backup software you are using correctly flags it for SQL server, we had a product many, many years ago that didn't, so despite full tlog backups it wouldn't flag it and they grew and grew and grew and crash (out of disk).

I'm not a SQL guy at all, but playing with this is turning me into one. I just wish someone could look at my setup with me and make sure I have it set correctly. I guess we'll just see over the next few days/weeks and make sure all is showing okay.

One other question I have is this...do the TL's automatically get removed when the DB is backed up?

One other question I have is this...do the TL's automatically get removed when the DB is backed up?

When you run the TL backup, space is automatically freed up in the transaction log itself (the .ldf file).

The Maintenance Cleanup Task in your Maintenance Plan will remove your old TL backups. You can set how long to keep the backups and what folder the task should search for your backup files.

Since you're removing the TL backups from your server's hard drive, make sure you keep a tape archive copy for as long as you think you'll need them. Keep in mind that a tape restore will be slower than a restore from disk.

EDIT: Oops - saw that you answered your question before I got this posted.

1

This discussion has been inactive for over a year.

You may get a better answer to your question by starting a new discussion.