Monitoring a Table/ Log for a table

Question

Monitoring a Table/ Log for a table

raju.tanneeru

Right there with Babe

Points: 763
More actions
September 25, 2009 at 8:52 am

#226550

HI ALL,
i am using SQL Server 2005 Data base as back end, having two agent jobs for taking full and diff backups
recently there is an update made on a particular table, in a table Table1 one column COL1 is updated to '*' in production.
there are 3/4 people are having access to production data base. on product support task we may need to execute some sql statement to fix the issue. now we would like to know when this particular update has occurred.
how can i check the updates for Table1 ?
If in future i want monitor this kind of issues then what will be the precautions ??
Thank you

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

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

Slick84 SSCertifiable Points: 5602 More actions · Answer 1

You can setup triggers to monitor for updates to the table by logging them into another table when an update / insert takes place into any of your production tables. I'm sure there are a million ways to do this, and honestly I have not done this yet but thats what I read so far.

--
:hehe:

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

You could also query the log file written by the default trace, a background trace that runs in SQL 2005. See "Default Trace" in BOL or search this site for how-to articles. It writes to 5 log files and recycles them, so you'd want to query them soon after you discover a problem.

Greg

GSquared SSC Guru Points: 260824 More actions · Answer 3

If your database is in Full recovery mode, you can get who/what/when on updates and such by using a log parser. ApexSQL has a good one, and I think you can get a free trial for it.

Audit logging is how I keep track of that kind of thing. I wrote a couple of articles on it, which you can find here:

http://www.sqlservercentral.com/articles/Auditing/63247/

http://www.sqlservercentral.com/articles/Auditing/63248/

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323507 More actions · Answer 4

Greg Charles (9/28/2009)
You could also query the log file written by the default trace, a background trace that runs in SQL 2005. See "Default Trace" in BOL or search this site for how-to articles. It writes to 5 log files and recycles them, so you'd want to query them soon after you discover a problem.

oops...the Default Trace tracks DDL changes(CREATE OBJECT,ALTER,DROP) , not insert/updates. you'll want to add a seperate server side trace that captures SQL:BatchCompleted events and , and query that, but querying it is exactly the same as Greg suggested.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 5

I'll chime in that GSquared's series is worth a read.

Once question I'll ask. Are you trying to keep track of this for lots of data, or are you concerned about this one update? A log reader tool will help you if you want to backtrack things periodically. An auditing solution will track things all the time, which is a lot of data to monitor and manage. Think about which one you are talking about.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

Lowell (9/28/2009)[hroops...the Default Trace tracks DDL changes(CREATE OBJECT,ALTER,DROP) , not insert/updates. you'll want to add a seperate server side trace that captures SQL:BatchCompleted events and , and query that, but querying it is exactly the same as Greg suggested.

Doh! Thanks for the correction. I should know better than to post answers pre-coffee.

Greg

raju.tanneeru Right there with Babe Points: 763 More actions · Answer 7

Hi ALL,

Thanks for your replies.

I dont have any particular requirement to check the log on periodically. I only want to check the log when some thing goes wrong.

if this is difficult in SQL 2005, then is it possible in SQL 2008 ?

Please let me know the better way to handle these kind of issues.

Thank You

Silverfox SSC-Insane Points: 23150 More actions · Answer 8

The solutions mentioned will help in most scenarios, it is totally different and a lot easier in sql 2008, as changes can be tracked.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

raju.tanneeru Right there with Babe Points: 763 More actions · Answer 9

Hi Ten Centuries

Could please tell me how this process can be configure or track in SQL 2008

Silverfox SSC-Insane Points: 23150 More actions · Answer 10

http://www.databasejournal.com/features/mssql/article.php/3720361/Microsoft-SQL-Server-2008----Change-Data-Capture--Part-I.htm

This is one of the new features in sql 2008, you also have more advanced auditing functionality as well.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]