How to read SQL 2000 DB Transaction Logs

Question

Post reply

How to read SQL 2000 DB Transaction Logs

Ronald San Juan

SSCrazy

Points: 2937
More actions
January 15, 2007 at 3:58 pm

#62161

Can anyone recommend a good tool on reading SQL 2000 DB Transaction Logs.
Some of the DBs I maintain generate a lot of logs from hundreds of jobs/replications performed on them. I need to look at the transaction logs to pinpoint which job or procedure is causing my miseries. I cannot use profiler as I'm not certain as to what time this occurs.
TIA
Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"

Viewing 8 posts - 1 through 7 (of 7 total)

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

There is no good help from MS in this reagard but you can use fn_dblog() function...

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-27-fn_dblog.htm

It is better to use third party tools like Log Explorer, ApexSQL's Log

Navigator..

http://www.lumigent.com/products/le_sql.html

MohammedU
Microsoft SQL Server MVP

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 2

yes third party tools are the best option for this.

apex and red-gaet also having same feature tool. Also, you need to have Full recovery option for the database

My Blog: http://dineshasanka.spaces.live.com/

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

examining tran logs is not for the faint hearted, however I'm slightly interested on why you consider this a misery, there seem to have been a number of posts about tran logs, what do you expect to do when you have identified which update/insert/delete is causing the growth - not allow it? Each change has to be logged, that's the point, so knowing this what do you hope to gain from this? You should be aware that index rebuilds and such also cause log growth.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

Actually if you use a third party tool I don't think it's too bad reading the log. Lumigent, Red Gate, ApexSQL, Golden Gate all have tools that work well.

The biggest problems are if you don't know the time or the person making the change. Then you just have a lot of data to weed through.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 5

Steve - yeah agreed it's not too bad until you have several gb of logs to go through!! also assumes you know what you're looking for.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 6

Thanks for all your replies. I'll look into these tools.

BTW, the reason why I need to know which is causing the log outbursts (probably a job) is to find out if the statements used can be optimized. Also, I'll be looking into bulk-logged recovery options.

Thanks again.

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 7

That's an interesting view but you can't usually optimise an update or insert to use less tran log, I agree you can optimise the queries but an insert is an insert. One pointer, however, on data loads ,is to make sure that if tables are being cleared each day ( for example ) that you truncate rather than drop. e.g. dropping a 1gb table will require about 1.5gb tlog space, truncating and then dropping the table will not really use any log space at all. ( It's also much quicker on big tables )

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/