• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74691
  • Last Modified:

"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.
0
jcks
Asked:
jcks
1 Solution
 
arbertCommented:
in query analyzer run:

backup log YOURDATABASENAME with truncate_only
go
dbcc shrinkfile (yourdatabasename_log,0)
go

0
 
arbertCommented:
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:
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?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
arbertCommented:
Right, you would just schedule a shrink of the file....

You can setup a new maintenance plan (not a bad idea for a newbie).  Go into enterprise manager, management, Right click on Maintenance plan,  New Maintenance plan.

Brett
0
 
jcksAuthor Commented:
would the shrink of the file AND the backup all be handled by the maintenance plan? Sorry if these are dumb questions and I appreciate the help!
0
 
jcksAuthor Commented:
oh and is it okay just to have the maintenance plan backup these files to the C: partition of my server or should I send it somewhere else (tape, another server, another partition)
0
 
arbertCommented:
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:
Awesome. Thanks Brett, full credit to you!
0
 
jcksAuthor Commented:
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?
0
 
arbertCommented:
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:
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.
0
 
arbertCommented:
ahhhh, a vendor app--it's amazing the vendors that release their apps for SQL but don't supply any maintenance!!!!

Well, you know how to reach me if you need assistance :)

Brett
0
 
rdneynrmanCommented:
hi
i used

dbcc shrinkfile (yourdatabasename_log,0)
go

but got this error .. can any one help me ...

Could not locate file 'yourdatabasename_log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

thanking you
0
 
arbertCommented:
I thought it was clear you need to replace yourdatabasename_log with the actual name of your database log.  Is that what you actually ran?
0
 
arbertCommented:
Hey, rdneynrman, this isn't even your question....
0
 
harshismCommented:
I tried to truncate the way of shrinkfile option.But still have the same data log file.
what i can do to change/just tell me the way of shrink the file
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now