?
Solved

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

Posted on 2004-08-11
16
Medium Priority
?
74,676 Views
Last Modified: 2012-06-22
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
Comment
Question by:jcks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11779493
in query analyzer run:

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

0
 
LVL 34

Expert Comment

by:arbert
ID: 11779503
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
 

Author Comment

by:jcks
ID: 11779601
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:arbert
ID: 11779615
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
 

Author Comment

by:jcks
ID: 11779686
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
 

Author Comment

by:jcks
ID: 11779696
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
 
LVL 34

Accepted Solution

by:
arbert earned 2000 total points
ID: 11779758
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
 

Author Comment

by:jcks
ID: 11779859
Awesome. Thanks Brett, full credit to you!
0
 

Author Comment

by:jcks
ID: 11779869
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
 
LVL 34

Expert Comment

by:arbert
ID: 11780023
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
 

Author Comment

by:jcks
ID: 11780105
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
 
LVL 34

Expert Comment

by:arbert
ID: 11780132
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
 

Expert Comment

by:rdneynrman
ID: 12033614
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
 
LVL 34

Expert Comment

by:arbert
ID: 12035386
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
 
LVL 34

Expert Comment

by:arbert
ID: 12035390
Hey, rdneynrman, this isn't even your question....
0
 

Expert Comment

by:harshism
ID: 14326770
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question