Monday, January 5, 2009

Truncate Transaction log SQL Server 2000

Get database name:
SELECT * FROM master.dbo.sysdatabases

Get File group ID
Use [db_name]
select * from dbo.sysfiles

Truncate and shrink Transaction file:
Use [db_name]
BACKUP LOG [db_name] WITH TRUNCATE_ONLY
DBCC SHRINKFILE (fileID)
at

1 comment:

  1. AnonymousJanuary 5, 2009 at 9:42 AM

    Gary,

    This is a script I use to shrink the log. Found in the MSDN
    documentation.

    Regards,
    -DP

    USE pubs;
    GO
    -- Truncate the log by changing the database recovery model to SIMPLE.
    ALTER DATABASE pubs
    SET RECOVERY SIMPLE;
    GO
    -- Shrink the truncated log file to 1 MB.
    DBCC SHRINKFILE (pubs_Log, 1);
    GO
    -- Reset the database recovery model.
    ALTER DATABASE pubs
    SET RECOVERY FULL;
    GO

    ReplyDelete