365DBA Blog

Database Stuff

Transactions With Log Records

Here is another fairly simple SQL Server script to show information on open transactions which have any log records. You can use this to get detailed information about processes that are currently running or schedule it to catch any processes which might be bloating your transaction log (which is hard to catch after the fact if you don't see it right afterward rolling back).

Select GetDate() As recCreatedDt,
DateDiff(ss, DBT.database_transaction_begin_time, GetDate()) As AgeInSeconds,
DB_Name(DBT.database_id) As 'DatabaseName',
DBT.transaction_id,
DBT.database_transaction_begin_time,
Case DBT.database_transaction_type
When 1 Then 'Read/write transaction'
When 2 Then 'Read-only transaction'
When 3 Then 'System transaction'
End As 'TypeOfTransaction',
Case DBT.database_transaction_state
When 1 Then 'The transaction has not been initialized.'
When 3 Then 'The transaction has been initialized but has not generated any log records.'
When 4 Then 'The transaction has generated log records'
When 5 Then 'The transaction has been prepared.'
When 10 Then 'The transaction has been committed.'
When 11 Then 'The transaction has been rolled back.'
When 12 Then 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.'
End As 'StateOfTransaction',
DBT.database_transaction_log_record_count As record_count,
DBT.database_transaction_replicate_record_count As replicated_records,
DBT.database_transaction_log_bytes_used As bytes_used,
DBT.database_transaction_log_bytes_reserved As bytes_reserved,
DBT.database_transaction_log_bytes_used_system As system_bytes,
DBT.database_transaction_log_bytes_reserved_system As system_reserved,
--DBT.database_transaction_begin_lsn,
--DBT.database_transaction_last_lsn,
--DBT.database_transaction_most_recent_savepoint_lsn,
--DBT.database_transaction_commit_lsn,
--DBT.database_transaction_last_rollback_lsn,
--DBT.database_transaction_next_undo_lsn, 
ES.[host_name], 
ES.[program_name], 
Case ER.transaction_isolation_level
When 0 Then 'Unspecified'
When 1 Then 'ReadUncomitted'
When 2 Then 'ReadCommitted'
When 3 Then 'Repeatable'
When 4 Then 'Serializable'
When 5 Then 'Snapshot'
End As IsolationLevel, 
ER.command,
S.text, 
SubString(S.text, (ER.statement_start_offset/2)+1, 
((Case ER.statement_end_offset
When -1 Then DataLength(S.text)
Else ER.statement_end_offset
End - ER.statement_start_offset)/2) + 1) As statement_text
From sys.dm_tran_database_transactions DBT
Left Join sys.dm_tran_session_transactions ST On DBT.transaction_id = ST.transaction_id
Left Join sys.dm_exec_sessions ES On ST.session_id = ES.session_id
Left Join sys.dm_exec_requests ER On DBT.transaction_id = ER.transaction_id
Outer Apply sys.dm_exec_sql_text(ER.sql_handle) S
Where DBT.database_transaction_log_record_count > 0

Database Free Space

Here is a very simple SQL Server script that has come in handy throughout the years; it displays the total space, used space, and free space (bloat) for each data file within every database on the instance. Remove the commented line to limit the results to a specific drive (in case you are running out of room on a specific drive and are only interested in files which reside on that drive). 

*Disclaimer: Keep in mind however, that shrinking files is against best practices and causes everything to get fragmented, etc., but in real life sometimes you need to do it regardless of how ugly it is.

If Object_ID('tempdb.dbo.#UsedSpace') > 0
    Drop Table #UsedSpace
Create Table #UsedSpace (DBName VarChar(150), FileID SmallInt, FileGroup SmallInt, TotalExtents Int, UsedExtents Int, 
Name NVarChar(256), FileName NVarChar(512))
exec sp_msforeachdb 'use [?]
Declare @SQL VarChar(8000)
Set @SQL = ''DBCC SHOWFILESTATS WITH TABLERESULTS''
Insert Into #UsedSpace (FileID, FileGroup, TotalExtents, UsedExtents, Name, FileName)
Exec(@SQL);
Update #UsedSpace Set DBName = ''?'' Where DBName Is Null;
'
Select DBName, Name, (TotalExtents * 64) / 1024 As TotalSpace,
(UsedExtents * 64) / 1024 As UsedSpace, 
((TotalExtents * 64) / 1024) - ((UsedExtents * 64) / 1024) As FreeSpace
From #UsedSpace
--Where Left(FileName, 1) = 'F'
Order By ((TotalExtents * 64) / 1024) - ((UsedExtents * 64) / 1024) Desc
Drop Table #UsedSpace