4

I am running SQL Server 2005.

My db backup scheme is:
Recovery model: FULL
Backup Type: Full
Backup component: Database
Backup set will expire: after 0 days
Overwrite media: Back up to the existing media set, Append to the existing backup set

The db is writing to 250GB drive (232GB actual).

My _Data.mdf file is over 55GB and my _Log.ldf is over 148GB.

We ran into a situation where our drive was filled today. I moved our ab_Full.bak and ab_Log.bak files to another drive to make space - about 45GB. Five hours later, free space is at 37GB.

I'm new to managing SQL server; so, I have some basic questions about my backups.

I know I need to update the db to start managing the transaction log size to help prevent this problem in the future. So, assuming I have enough free space, I:
1. right click the db and choose Backup
2. set 'Backup Type' to 'Transaction Log'
3. change 'Backup set will expire' after to 30 days
4. click 'ok'

My understanding is this will move 'closed' transactions from the transaction log to a backup and truncate the transaction log.

Is this plan sound? Will I need to manually resize the log file afterwards?

Thanks for your time.

RevoJoe
  • 41
  • 4
  • try googling "kimberly tripp"+transaction+log – Mitch Wheat Jan 08 '10 at 01:19
  • Are you backing up your transaction log every hour or so? – Mitch Wheat Jan 08 '10 at 01:19
  • I'm down to 5GB now. It dawned on me to turn off my hourly data loads. I went ahead and started the normal scheduled transaction log backup and shrink. I'm really concerned this will fill my disk. I'm looking for steps to recover if this happens. I was told I could change my backup temporarily from full to simple backup, shrink the tlog and change back to full recovery. Don't know if I will have space for that. Thank you for your help. – RevoJoe Jan 08 '10 at 06:19

2 Answers2

4

Are you backing up the transaction log at any time at all? If you are using the FULL recovery model, then you need to back up the transaction log in addition to backing up the main database, or if you don't want to back up the log (why would you then use the FULL recovery model?) then at least truncate the log at some regular interval.

You should back up the transaction log before every full backup (and keep it as long as you keep the previous full backup) so you can restore to any point in time since the first full backup you've kept. Also, it might be worth backing up the transaction log more often (the total size is the same) in case something bad happens between two full backups.

Pent Ploompuu
  • 5,364
  • 1
  • 27
  • 47
  • Thanks everyone. I recently started with my company and am new to managing a db. Until 11/26/2009, the db was backing up the db once a day and the transaction log once an hour. Neither is running now. My predecessor left before I joined the company; so, there is no way to follow up with him on why the backups were stopped. The jobs are enabled, 'last run outcome' is "Succeeded", 'Next Run' is "Not Scheduled" and 'Scheduled' is "yes". Do I just need to right click on the db backup job, click 'Start job at step...', highlight step 1 and click start? – RevoJoe Jan 08 '10 at 02:05
  • If the answer to my previous question is 'yes'; then, I imagine I will do the same steps for the Transaction log backup once we have a successful db backup. Thanks again! – RevoJoe Jan 08 '10 at 02:06
  • If "Next Run" is "Not Scheduled" then you should check the job schedule first, maybe it has some simple mistake like a "End date" defined. – Pent Ploompuu Jan 08 '10 at 02:57
0

The best procedure is to regularly backup your log file. In the mean-time, for 'catastrofic' scenarios like the one you described, you may use this snippet to reduce the size of your log: http://www.snip2code.com/Snippet/12913/How-to-correctly-Shrink-Log-File-for-SQL

Cristiano Ghersi
  • 1,944
  • 1
  • 20
  • 46