SQL Server: list transaction log query


Today, I came across a nice little SQL statement that list the content of the transaction log for a particular database. I haven’t investigated further, but there probably exists standard reports and/or 3rd party tools that list you the same thing, but as a geeky developer, I like SQL statements better 🙂

SELECT 
   year(a.[Begin Time]) as dbYear, 
   month(a.[Begin Time]) as db, 
   count(*) 
FROM sys.fn_dblog(NULL, NULL) a 
WHERE Operation IN (
	'LOP_INSERT_ROWS',
	'LOP_MODIFY_ROW', 
	'LOP_DELETE_ROWS',
	'LOP_BEGIN_XACT',
	'LOP_COMMIT_XACT') 
group by year(a.[Begin Time]), month(a.[Begin Time])
order by year(a.[Begin Time]), month(a.[Begin Time])
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s