Query a Sql Server LOG (LDF) File

Question

Post reply

Query a Sql Server LOG (LDF) File

salimgbelim

Old Hand

Points: 336
More actions
October 31, 2003 at 4:26 am

#358501

I was just wondering is there any way to query the transaction log life in sql server?
actually i want to get what table where updated during particular time range.one way is if i take transaction log back up and query those transaction log i can get it.is there any other ways to achieve this.
salim

salim

Viewing 10 posts - 1 through 9 (of 9 total)

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

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

see dbcc log (dbname,4)

or try the log explorer from lumigent.

Cursors never.

DTS - only when needed and never to control.

Cursors never.
DTS - only when needed and never to control.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Please avoid cross post in different categories

Piggy-backing on Nigel this might be on some help

http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=12279

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

salimgbelim Old Hand Points: 336 More actions · Answer 3

Thats great but the dbcc log does not give me the table names etc..

i know about log explorer they are great but i am not looking to buy any software

any more great ideas please!!!

salim

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

The most detailed informations you'll get with the -1 parameter, as mentioned in the link above, but again no table names.

Figuring out yourself what the columns mean, will be more cost intensive and more erroneous than buying a third party product, I bet.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

salimgbelim Old Hand Points: 336 More actions · Answer 5

Thanks Frank,but isn't there any other way to fullfill my task of getting the table names and column updated during a particular time range? am using .net(c#)

salim

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

No other way that I know of.

What task are you trying to fulfill?

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

salimgbelim Old Hand Points: 336 More actions · Answer 7

What i want to do is :

At a particular time I want to Know How many tables where update between the timeframe e.g 10/10/2003 12PM - 12/12/2003 12PM which tables (including columns) where updated by the users during this particular time so from that i will get the data in table and save those snapshot of data in another db.

any ideas!!!

salim

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Things are getting clearer now.

But when you know the table has been updated, why do you want to know which column(s) have been updated. Figuring that out, including all implication of possible permutations of those columns involved will get messy.

Why not simply add another column to each table with a DEFAULT of GETDATE()? You then only have to run a query and search for data that meets your criteria and mirror them to another db.

Any reason only some parts of the data should be considered?

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

iadams SSCommitted Points: 1970 More actions · Answer 9

Sounds like you need columns for create_date, create_user, update_date and update_user. But of course you wont get the entire change history, which is what youre really looking for (ie. Transaction Log). You could possibly create a "history" or "Audit Trail" for the changed or added data by using triggers.

You could also create a trace but the overhead would be too much.

-Isaiah