How to View Transaction Log in SQL Server

As a DBA, you may need to view the Transaction Log in SQL Server. This information is not typically available to common db users but can be obtained using a simple undocumented command

DBCC log ( dbname, 0|1|2|3|4 )


0: minimum information (Default)
1: Returns info available using 0 + flags, tags and the log record length.
2: Returns info available using 1 + object, index, page ID and slot ID.
3: Maximum information about each operation.
4: Maximum information about each operation + hexadecimal dump of the current transaction log row

In order to run this command against a database called 'PictureAlbum' :-

DBCC LOG(PictureAlbum, 3)


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

No comments: