How to shrink a log file with MSSQL

  • Log files can grow quite large. During an Accounting upgrade, the log file grew to 250Gb! And it did not shrink. The 'people' that wrote the accounting software decided to do the upgrade as a single transaction!
  • After the upgrade completed there is no need to keep the 250Gb transaction log.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Deleting the log

Get the filenames & location with SQL Server 2005 & SQL Server 2008

  • The log file usually has the _log at the end, unless you have given another name to the log file.
  1. USE sql911;
  2. go
  3. SELECT CAST(name AS VARCHAR(20)) AS 'Name',
  4.        CAST(physical_name AS VARCHAR(75)) AS 'Filename'
  5. FROM sys.database_files;
  6. go
Name                 Filename
-------------------- ---------------------------------------------------------------------------
sql911               C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf
sql911_log           C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911_log.ldf

(2 row(s) affected)

Get the filenames & location with SQL Server 2000

  • The log file usually has the _log at the end, unless you have given another name to the log file.
  1. USE sql911;
  2. go
  3. SELECT CAST(name AS VARCHAR(20)) AS 'Name',
  4.        CAST(filename AS VARCHAR(75)) AS 'Filename'
  5. FROM sysfiles
  6. go
Name                 Filename
-------------------- ---------------------------------------------------------------------------
sql911               C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf
sql911_log           C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911_log.ldf

(2 row(s) affected)

Detach the log

  1. USE master;
  2. go
  3. SP_DETACH_DB 'sql911';
  4. go
  • Nobody must be in the database to detach it.
  • or set the database to single user mode with:
  1. ALTER DATABASE sql911
  2. SET single_user WITH ROLLBACK IMMEDIATE;
  3. go
  • There was no message from SQL Server for sp_detach_db.

Delete the log

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\froggy>del "C:\Program Files\Microsoft SQL Server\MSSQ
L.1\MSSQL\DATA\sql911_log.ldf"

C:\Documents and Settings\froggy>

Attach the database again

  1. USE master;
  2. go
  3. SP_ATTACH_DB 'sql911','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf';
  4. go
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911_log.ldf" may be incorrect.
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911_log.LDF' was created.
  • Line 3: sp_attach_db requires 2 filenames:
    1. The database filename
    2. The log filename
  • I have provided only the database filename and MSSQL has created the log file.