Guys, a quick post.

Sometimes when you run SHRINKFILE on the log file we get this error:

[sourcecode language=”sql”]
Cannot shrink log file 2 (DBADB_log) because the logical log file located at the end of the file is in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[/sourcecode]

There are a couple of options to fix this:

• Try quering sys.databases catalog table.
  • [sourcecode language=”sql”]SELECT name, log_reuse_wait_desc FROM sys.databases[/sourcecode]
  • The column ‘log_reuse_wait_desc‘ shows the reason why. In my case if was waiting for LOG backup to occur before the file could be shrunk. Once I performed the TRN backup, the SHRINKFILE ran successfully.
• Change recovery model
  • If the above step does not work, try changing the recovery model of the  database from FULL to SIMPLE. Once in SIMPLE mode, the LOG file would be automatically reduced to smaller size.Then revert to FULL recovery model again.
  • Make sure you perform a FULL backup and TRN backup after the recovery model change. This is why
• Use these queries to measure the usage in each file.

[sourcecode language=”sql”]
— all files
SELECT name
, size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

— Log file usage
DBCC SQLPERF (LOGSPACE)
[/sourcecode]

Hope this helps,

_SqlTimes