3

Possible Duplicate:
Why is My SQL Server Transaction Log Growing or Running out of Space?

The transaction log in one of my client's databases is 16GB, when the database itself is only 2MB. I have begun backups of the transaction log and they occur every half an hour.

I have attempted to run DBCC SHRINKFILE (Log,size) but it says all virtual pages are in use.

Does this mean there is an uncommited transaction or something? The file has been this big for a while now. Am I able to somehow truncate the log?

Improve this question
8

Was the database in FULL recovery but no backups being taken previously? Paul Randall's article Running Out of Transaction Log Space covers the other likely scenarios.

The DMV query below will tell you why the log cannot be truncated. If you're unsure how to interpret the output, add the response to your question.

SELECT
    db.[name] AS [Database Name]
  , db.recovery_model_desc AS [Recovery Model]
  , db.log_reuse_wait_desc AS [Log Reuse Wait Description]
  , ls.cntr_value AS [Log Size (KB)]
  , lu.cntr_value AS [Log Used (KB)]
  , CAST(CAST(lu.cntr_value AS FLOAT) 
    / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %]
FROM
    sys.databases AS db
INNER JOIN
    sys.dm_os_performance_counters AS lu
ON  db.name = lu.instance_name
INNER JOIN 
    sys.dm_os_performance_counters AS ls
ON  db.name = ls.instance_name
WHERE
    lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0
OPTION (RECOMPILE);
Improve this answer
-2

backup your database

change to simple recovery mode,

change back to full recovery mode

magic

Improve this answer
  • 1
    Changing to simple recovery and then back is not really necessary... if you care about the backups (i.e. in production) then you can shrink the transaction log immediately after backup, and if you don't care (i.e. a dev/test server) then you can just change the mode to simple and shrink it right away. – Aaronaught Nov 9 '11 at 3:29