Failure on trying to shrink files

• When trying to shrink files for a database in SQL server 2005 (2.5MB database with 57GB log file!) may get a failure such as:
  • Shrink Failed for DataFile 'DBName' (Microsoft.SqlServer.Smo)
    • The transaction log for database 'DBName' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.database (Microsoft SQL Server, Error: 9002)

Reading SQL Log File

select * from ::fn_dblog(null,null)

That will get you a formatted output of the log and the log binary data in a field - even that is not trivial to decode. As well as the log file format, you will have to have a very indepth knowledge of SQL itself to understand the entries properly.

Eliminate SQL Server 2005 .LDF Log File

If you do not need the log file, you can get rid of it as follows:

• Detach the database
• Rename the log file
• Attach the database without the log file
• Delete the log file

Let’s say, the database name is testDev. In the SQL Server Management Studio,

• Highlight the database-> Tasks->Detach..-> Click OK
• Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf
• Highlight Databases->Attach…-> Click Add -> add the database testDev,
• highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
• After this is done, you can verify the contents of the attached database and then delete the log file

This way we can safely delete the log file and free up the space.

NOTE: The above steps can fail.

Can not detach because the log is full

Detach database may fail if the drive is full because a log .LDF file has grown too large.

Solution:

• Stop SQL service (Win 2005 - Start Menu -> Administrative Tools -> Services. Select SQL Server - Stop)

Shrinking log file - SQL Server 2005

Following code always shrinks the Truncated Log File to minimum size possible.

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Notes:

• breaks the chain of the logs
• restore point in time can not be done
• Should be followed by full backup

Other

Other related commands:

• DBCC LOGINFO
• You can attach the MDF file without the LDF File by using the sp_attach_single_file_db system stored procedure. This procedure will attach the database and create a new log file.