Why I can not search all records in my fn_dblog

Question

Why I can not search all records in my fn_dblog

calvin_wk_wong

SSC Enthusiast

Points: 156
More actions
December 3, 2015 at 1:43 am

#306852

I am currently work with SQL 2008, I want search my transaction log which never truncate before and around sized 1XXGB,
When I doing search, it only show current date records, if I specify yesterday (for example) it return nothing
My Code:
===============
SELECT Operation,[Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] = ‘0000:00000752’
AND [Operation] = ‘LOP_BEGIN_XACT’ And [Begin Time] = cast(2015-12-02 as date)
===========================Return Nothing, If change today, it return records,
I don't know why, and hope all you can help me
Best Regards and Thanks
Calvin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why do you say it's never truncated? What recovery model, what backups are scheduled?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

The predicate

[Begin Time] = cast('2015-12-02' as date)

requires transactions to start exactly at 2015-12-02 00:00:00.000 .

Is it what you mean really?

calvin_wk_wong SSC Enthusiast Points: 156 More actions · Answer 3

Even I try use select * from fn_dblog(null,null)

it only return recent (today's records)

Any solution ?

Thanks

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 4

calvin_wk_wong (12/3/2015)
Even I try use select * from fn_dblog(null,null)
it only return recent (today's records)
Any solution ?
Thanks

If you're backing up the transaction log like you should be, why would you expect to see yesterday's rows in it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

GilaMonster (12/3/2015)
Why do you say it's never truncated? What recovery model, what backups are scheduled?

???

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass