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
74,620 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
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
 
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 500 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now