Transaction log file 1

[mnmdesigns]

Hi,

I am using SQL server 2000 and have some DTS jobs. These jobs are scheduled to run every night automatically. The jobs failed this weekend and now when I try to run the job manually I get an error message stating that the Transaction log file is too big. When I go to view the size of the file is is currnetly 100 gigs. I am running out of space on the disk drive.

This is a ".ldf" file. Is there a way I can shrink this file or even delete it?

Any help would be appreciated.

MD

 

[JayKusch]

backup log MyDatabase with NO_Truncate

Thanks

J. Kusch

 

[JayKusch]

You would run that command in query analyzer. You cannot get rid of the Transaction Log (LDF). You could bypass most transactions being written to it, thus making it grow, by putting the DB in Simple recovery mode. Just right click on the DB in Enterprise Manager and choose Properties..Options and set the model to Simple. In doing this, you are only able to recover from the last full backup if you were to have a DB disaster.

Better to set up some type of backup plan and truncate the tlog after the backup to keep it within bounds.

Thanks

J. Kusch

 

[mnmdesigns]

Thanks for your reply. I tried your first suggestion. Here is the command I used in query analyzer.

backup log CreationReport with NO_Truncate.

When I run this query I get the following message:

Server: Msg 3207, Level 16, State 1, Line 1
Backup or restore requires at least one backup device. Rerun your statement specifying a backup device.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

I dont know what to do, can you please suggest.

Thanks,

MD

 

[JayKusch]

What version of SQL Server are you running?

Thanks

J. Kusch

 

[JayKusch]

If you are running SS7 ... then try ...

backup log CreationReport with Truncate_only

Thanks

J. Kusch

 

[mnmdesigns]

SQL Server 2000

Thanks,

MD

 

[mnmdesigns]

I tried your other suggestion and the query analyzer seem to run it fine. I still have the problem of oversize log file. I dont have much space left of the hard drive and file is over a 100 Gbs. Is there anyway I can make this file smaller or compress it?

Thanks, I really appreciate the help.

MD

 

[JayKusch]

lets give the ole DBCC a run. This command will release the unused log space back to the OS. You must be a bit patient though. I have seen it take a little bit of time to release all of the log file that is not being used. Run this in Query Analyzer. May need to run it a couple of times just for good measure ...

DBCC SHRINKFILE(CreationReport_LDF)

Thanks

J. Kusch

 

[JayKusch]

ALMOST HAD IT ... LOL

DBCC SHRINKFILE(CreationReport_LDF,TRUNCATEONLY)

Thanks

J. Kusch

 

[mnmdesigns]

AWESOME!! Worked like a charm.

Thanks ALOT!!

 

[MDXer]

you should schedule some type of log backup or database backup on a regular schedule to keep the size of your log file down. you can schedule this using the Database Maintence utility lokacted under the management node of enterprise manager.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck