Sql 2000, Reducing a ldf and mdf file size problems

[shibuyauk]

I am having a slight problem with our webserver. I have inherited a database whose initial configuration was for unrestricted growth.

This has resulted in a ldf file of over 9GB and a mdf of around 5GB.
This filled our HD space and caused our site to go down for a few minutes
I have been able to truncate the ldf to 8.5GB and then set the growth limmit to 8.6GB to stop it from growing exponentionally.
I would dearly love to get the Log File below 5GB, however the space used is almost as big as the file, so the shrink database option will not shrink it. Is there another process to shrink the file?

Also the mdf file is showing over 1GB of free space, however any attempt to compress and truncate the data just freezes Enterprise Manager which sits there until it is manually closed down.

Is there another way to compress and truncate, or should i leave Enterprise Manager when it freezes as it will be running in the background?

Thanks for any help

 

[shibuyauk]

Additional info

I know the BACKUP DATABASE statement can be used to assist in the truncation of log files, however i do not have any space on out Hard Drives in which to backup the database to.

Which presents a problem

 

[mrdenny]

No problem, you can use the truncate_only flag to clear the log without actually backing it up.

backup log {Database} with truncate_only

Then do a dbcc shrinkfile to shrink the log file.

dbcc shrinkfile ({LogFileName}, 100)

Then do a full database backup to cover your self. You'll want to schedule transaction log backups to happen on a regular basis so this doesn't happen again.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[shibuyauk]

Thanks, that worked well

!

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)