"Log file for database is full, back up transaction log for the database to free up log space" error message.

Hello, I'm a relative newbie to this since the SQL database person at my work is out for an extended period of time. Recently, on of the applications we use stopped working and returned the error I had in the title. After doing some research on-line I kind of know what's going on here, but I really have no idea what to actually do once I get into the Enterprise manager. I don't want to mess this up. Basically I want to the transaction logs to be backed up automatically so they get truncated (this is what happens after each log backup correct?) and this error never appears again. Right now the transaction log is set to Automatically grow, with Unrestricted file growth and the Recovery model is set to Full. There's no maintenance plan set for this database either. The only backup I'm aware that occurs on this is a tape backup that occurs nightly that backups all the databases on this server.

So how do I get rid of this error message and how do I make it where it doesn't occur again? This is for SQL 7.0. Thank you for any help given.

Then, make sure that you have a backup schedule in place to prevent the log file growth from getting out of hand. Backup the database, and then shrink the transaction log to reclaim the space.

0

jcksAuthor Commented: 2004-08-11

Okay so once I run the BACKUP LOG databasename TRUNCATE_ONLY and then shrink the log, I won't ever have to do those steps again if I have a backup in place?

Where do I setup this backup? It's more then just copying the file to another location and then redoing the truncate and shrink steps correct? I want something pretty much automated. Is there an option or function in Enterprise manager where I do the backup of the transaction log?

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Nope, not dumb questions--you're just getting your feet wet :) Yes, you can do the backup and the shink in the maintenance plan--just select both options....

As far as where you back the data up to it depends. you'll get better performance if you backup to disks other than the disks your SQL server data is on. However, you can backup to the same disks and then move it later. I always like to try and keep one backup on disk (different disks than the data) and multiple copies on tape. Keeping a copy on disk allows faster restores and I just get more of a "fuzzy" feeling with disk than tape. Tape, you just don't know when you're going to have a problem :)

Brett

0

jcksAuthor Commented: 2004-08-11

Awesome. Thanks Brett, full credit to you!

0

jcksAuthor Commented: 2004-08-11

I already accepted your answer Brett, but one more question. I noticed when running the maintenance plan wizard, there was a lot of options to select. Do I just go with defaults or is there some "must do/don'ts" that I should be aware of?

Ya, I'll be honest, the maintenance plan is an easy way to get started--you gotta have your backups....

Personally? I don't use them. Couple reasons I don't like maintenance plans. First, one day you will find your maintenance plans start failing for no reason (they can be hard to debug), and second, you can't make maintenance on your databases as granular as if you script them with TSQL. Don't worry and think it's not going to work--set something up and then start looking at how you can script things on your own....

So, that said, I would put just a backup database and backup transaction log in place to start with. At some point (I don't know your exact setup or data, so I can give specifics) you will want to include some index reorganizations and data reorganizations.

Brett
(my email address is in my profile if you need more assistance or further resources)

0

jcksAuthor Commented: 2004-08-11

Cool, thanks for all the help. The vendor should be helping me out, but they don't seem to have a clue when I call tech support, LOL.