Many times I have to perform ad-hoc queries that insert, update or delete very large amounts of data.  For example, a nightly ETL load failed and I must reload the data  manually or deleting large amounts of data

in batches from a logging table.  In such cases I like to keep an eye out on how full the transaction log is becoming while these  ad-hoc queries are running to ensure that the T-Log doesn't grow out of control. If needed, I can then pause whatever query is running to allow log truncation to occur or take a manual log backup.

The DBCC SQLPERF(logspace) is a nice way to see how full the transaction log is. But, if you have many databases on the server, it can be diffcult to find the database you're interested in since the list isn't sorted (it's actually sorted by database_id, which isn't terribly helpful). Here is a stored proc that I use that will execute the DBCC command and return the database list sorted alphabetically. You can also pass in a search string for the database name and it will return only those databases that have the search string in their name (sorted by name). I put this proc in a utility database that I use for functions and procs that are needed server-wide.

Usage is very simple:

EXEC dbo.TLogSqlPerf

This will return all databases sorted  alphabetically.

EXEC dbo.TLogSqlPerf @p_DbName = 'Adventure'

This will return only those databases with "Adventure" in their name (i.e.  AdventureWorks2012 and AdventureWorksDW2012)

I hope some of you find this useful.

Peter Wehner