December 31, 2008 at 11:11 pm
I need to know if DBCC log command can have filters?
If yes, then what can be the filters? And if no then kindly explain.
Thanks
December 31, 2008 at 11:45 pm
I don’t think that you can filter dbcc log command (unless there is a third parameter that can be used and I’m not aware about). If you do want to filter it, you can create a table and then insert the data into the table and use SQL query on this table. For example:
create table LogInfo (currentLSN varchar(50),
Operation varchar(50),
Context varchar(20),
TransactionID varchar(20))
go
insert into LogInfo
exec ('dbcc log (DBName, 0)')
Another alternative is to work with the function fn_dblog. Fn_dblog is a table values function and as such you can use it in SQL queries. Here is a small example of how to work with it:
SELECT TOP 10 * FROM ::fn_dblog(null, null)
Of course you can add a where clause. I have to admit that the data that I get from both DBCC log and fn_dblog tells me almost nothing. At the time when I looked for some information about it, I didn’t find much, so I left it and didn’t use anyone of them. Do you have any information about the results that you get from those commands? If so can you share this info?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 31, 2008 at 11:55 pm
So Cohen finally you've removed my confusion.
About understanding the output of these commands, you are true they are of quite no use unless we understand them. I am trying to analyze specifically the "opertaion" field values but haven't succeeded yet!
January 1, 2009 at 3:42 am
As far as i've come to know transaction log contains data in encrypted form, is there any way (beside third party tools) to interpret the information we get using DBCC commands? Or if we are able to decrypt it? I am concerned to the "operation" & "context" fields!
Thanks
Hira Masood
January 1, 2009 at 5:35 am
The tran log's not encrypted, but it's not designed to be human-readable. As far as I know, there are no official documents that are publicly released describing the data in the tran log.
All the log reading commands are undocumented, and may change between versions or even within versions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2009 at 4:21 pm
There is no DBCC Log command...in ss2k5
January 4, 2009 at 9:42 pm
nvkumar12 (1/4/2009)
There is no DBCC Log command...in ss2k5
Yes there is, but it is undocumented. You don't have to take my word for it, you can just try run it on SQL Server 2005.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 3, 2010 at 1:08 am
I think part of it you can decode like your data, page no which was modified , record size . I will just show a small thing here
-- create a database
USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'Page')
DROP DATABASE Page;
GO
CREATE DATABASE Page
ON PRIMARY
( NAME='Page_Data',
FILENAME = 'D:\Work\POC\DatabaseEngine\Data\Page_Data.mdf',
SIZE=3MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
LOG ON
( NAME='Page_Log',
FILENAME = 'D:\Work\POC\DatabaseEngine\Data\Page_Log.ldf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB);
GO
-- create table test
Use Page
GO
CREATE TABLE Test (c1 int,c2 varchar(900))
CREATE CLUSTERED INDEX idx_Test ON Test (c1);
-- insert data into test
INSERT INTO Test VALUES (1, REPLICATE('a',900));
-- check the page allocation using DBCC IND. I can see page 80 as data page and 89 as IAM
DBCC IND (Page,Test,1);
-- DBCC PAGE on data page
DBCC TRACEON (3604)
DBCC PAGE (Page, 1, 80, 0)
PAGE HEADER:
Page @0x0000000083BAE000
m_pageId = (1:80) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 30 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039894016
Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 1 m_freeCnt = 7177
m_freeData = 1013 m_reservedCnt = 0 m_lsn = (30:83:19)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
-- Look at m_lsn 30:83:19. Converting to hex 30:83:19 = 1e:53:13
-- fire a DBCC LOG command and look for this record
DBCC LOG (Page, 4)
-- Output --
0000001e:00000053:0013 LOP_INSERT_ROWS LCX_CLUSTERED 0000:000002c7 0 0x0000 62 1020 0000001e:00000053:0001 0x0002 74
0x00003E001E0000005300000001000200C70200000000020250000000010000001E0000001E000000530000001000000100000E000000000100000000000003009
50300001A0003133000080001000000030000020011009503
61616161616161616161616161616161616161616161616161616161616161616161
0001000101000C0000E7A4787D0000010200040204000A0200C411BA730000
-- In this text I have highlighted in bold 3 fields
first is 50 (hex) = 80 (decimal) is the page no
second is 9503 (hex - reversed bytes) = 5930 = 0395 = 917 (decimal) is the record size
third is a strig of 61 = which is nothing but the column data 'a'
I am still digging into it. If anyone has already please publish..
Thanks
j
August 16, 2011 at 10:53 pm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply