How to compress transaction log in MS SQL 2000 databases?

Question

I have several databases where the transaction log (.LDF) is many times larger than the database file (.MDF).

What can I do to automatically compress them or prevent them from becoming so large?

Answer 1

This should do the job.

use master
go
dump transaction <YourDBName> with no_log
go
use <YourDBName>
go
DBCC SHRINKFILE (<YourDBNameLogFileName>, 100) -- where 100 is the size you may want to shrink it to in MB, change it to your needs
go
-- then you can call to check that all went fine
dbcc checkdb(<YourDBName>)

Warning word

You would really use it in a test / development database where you do not need the right backup strategy, since flushing the log will result in a loss of transaction history. On living systems, you should use the sugested Cade Roux solution

Answer 2

.

, - , , () , .

Answer 3

Enterprise Manager > All Tasks > Shrink Database.

Answer 4

DBCC SHRINKFILE.

2005. 2000.

Answer 5

, : . SQL Server.

, db ( ) . , db.

Answer 6

, , - ( ) , . , , .

, , , .

Answer 7

Query Analyzer:

USE yourdabatase
SELECT * FROM sysfiles

:

FileID    …  
1             1             24264    -1            1280      1048578               0             yourdabatase_Data    D:\MSSQL_Services\Data\yourdabatase_Data.MDF
2             0             128         -1            1280      66           0                             yourdabatase_Log      D:\MSSQL_Services\Data\yourdabatase_Log.LDF

( 2 ). 2 3 .

Checkpoint
GO
Checkpoint
GO

, 1

DUMP TRAN yourdabatase WITH no_log 
DBCC SHRINKFILE(2,1)  /*(FileID , the new size = 1 Mb)*/

Answer 8

Here is what I used

BACKUP LOG <CatalogName> with TRUNCATE_ONLY
DBCC SHRINKDATABASE (<CatalogName>, 1)
use <CatalogName>
go
DBCC SHRINKFILE(<CatalogName_logName>,1)

Answer 9

try sp_force_shrink_log which you can find here http://www.rectanglered.com/sqlserver.php