Reduce Log Size

[nidgep]

Hi

Can anyone suggest how to reduce the size of the log file (*.ldf) in a SQL server 2000 database.

The *.mdf file is 48Mb and the log file is 2.5Gb!!

Thanks

 

[dbomrrsm]

shrink it in EM - select the database - - select View Taskpad - hover over Space allocated - select shrink database - click on Files - in the Database file drop down list select the log file - click ok - when you get a message to say the file has been shrunk - ok the message - right click on the database then select refresh.

If this doesnt work you may need to back up the TL before trying the above again.

[blue]DBomrrsm[/blue]

 

[nidgep]

Hi

I have followed your advice and tried to shrink the log file without much success.

did a log backup and removed inactive entries, then tried a shrink log again.

The results taken from the DB properties are as follows:
Size: 2677.13Mb
Free Space: 2475.92Mb

Is there now any way the the physical size can be reduced as I will shortly need to temporarily detach/copy and FTP the db and log to a remote server for re-attachment?

Thanks for your help so far.

 

[nigelrivett]

see

http://www.nigelrivett.net/TransactionLogFileGrows_1.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[timscronin]

Simple fix, detach the db, delete the log file, then attach db and it will create a new log file

 

[mrdenny]

If you are going to use the detach method (which I don't ever recommend) don't delete the log file, rename it until the database is attached. That way if there is a problem you can still reattach the old log file.

If the log file is full of data do a backup (or just dump the data with the truncate_only flag). Then use dbcc shrinkfile to shrink the transaction log file.

use database_name
go
backup log database_name with TRUNCATE_ONLY
go
dbcc shrinkfile (database_log_file_name)
go

Denny

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

 

[timscronin]

mrdenny is correct, move that log file before, also make sure you have full backup of db itself. However strongly disagree, withe detach and attach, have done i several times with no issues unless using log shipping

 

[nidgep]

Thanks guys
sorry for the delay in responding....
the truncate log TSQL did the job a treat

thanks again

 

[mrdenny]

no problem.

Denny

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