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.

RonJohn
  • 694
  • 2
  • 12
  • 31

2 Answers2

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.

Kenneth Fisher
  • 24,307
  • 13
  • 63
  • 116
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

RLF
  • 14,035
  • 2
  • 34
  • 47