6

SQL Server 2005

I ran "dbcc SQLPerf(logspace)", then took two log backups, and ran "dbcc SQLPerf(logspace)" again. No change from 48.55% used, even though "dbcc opentran (foo)" says that there are no active open transactions.

Does that mean that there are inactive open transactions that are preventing the log free space from changing after backups?

Thanks.

Improve this question
1
  • What is the initial size of log file and what is the auto increment value for same?
    – KASQLDBA
    Aug 18, 2015 at 13:35

2 Answers 2

Reset to default
7

It could be that a transaction running in another database has a hold on some objects in foo database. Using dbcc opentran(foo) only reports on transactions in foo, not in some other database.

Also, it might help you to read this description (from 2001(!)) on How DBCC SQLPERF (Logspace) Reports Percentage Used at:

https://support.microsoft.com/en-us/kb/281879

Improve this answer
2
  • 2
    @user209436 - Thanks for awarding me the "answer", but it looks like it was Kenneth Fisher who answered your question. Likely you should uncheck giving me the answer and check on his answer as resolving your problem.
    – RLF
    Aug 18, 2015 at 18:08
  • 1
    Microsoft link is dead 2018-05-28
    – MickeyfAgain_BeforeExitOfSO
    May 28, 2018 at 18:37
10

In addition to the additional space marked "in-use" by SQLPERF you might have space in your transaction log that is not being freed for other reasons. You should probably take a look at the log_reuse_wait_desc column of sys.databases. If it says NOTHING or LOG_BACKUP then what you are seeing is probably the space taken up by the current VLF. If however if you are seeing other values, such as DATABASE_MIRRORING or REPLICATION then a portion of your log is waiting to be replicated to another machine. VLFs are not marked "free" unless they are completely ready to be re-used. This can mean an open transaction or waiting on a log backup (if in bulk-logged or full recovery model), but it can also be other things too.

Improve this answer
1
  • This is what happened. The network between production and DR was down, so transactions weren't replicated, and alert emails weren't being sent, so we didn't know that mirroring was suspended.
    – RonJohn
    Aug 18, 2015 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.