Huge Logfile No Disk space

[govmule]

Hello,

I'm new to SQL but have the following problem.

SQL 2000 on Windows 2000

My 2 Gig database has a 21 Gig logfile. My server has ran out of disk space. I wanted start the database with a new logfile but keep running into errors.

This seems like it should be fairly easy, but I'm stumped. How would one accomplish this?

Thanks in advance!

 

[SQLDenis]

Can't you shrink the log file?

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[govmule]

Do you mean 'Shrink Database' or is there a seperate way Shrinking the Logfile. Also I checked and 'Auto Update Statistics' is checked. Is this what you mean by useless updates?

Thanks for you help Denis

 

[SQLDenis]

What is in quotes is my standard 'signature' nothing to do with you
There is a command DBCC SHRINKFILE look it up in BOL
You could also take a look at BACKUP LOG WITH TRUNCATE_ONLY

NO_LOG | TRUNCATE_ONLY

Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.


“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[mrdenny]

After you backup the log with the truncate_only flag and shrink the file do a full backup. Then you will want to do one of two things.

1. If keeping the transaction log isn't important to you change the recovery model from full to simple. This will automatically remove entries from the transaction log after they are committed to the database.

2. If you are interested in keeping the log around then you will want to setup regular transacation log backups to the disk or to a network share. When the log is backed up SQL will automatically remove the backed up transactions from the log file. This will keep the log file growth under control.

When shrinking your log file be sure to leave enough room for the database to continue working. If you generate 50 MB of log files per day, I'd recommend keeping the file at ~150 MB. This way there is plenty of room for the system to work with before it will need to expand the database. This will also allow for heavy work load days without expanding the log file.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com