August 28, 2009 at 2:10 am
I a, trying to implement some audit on sql server databases. I dont think there is a way in sql server to identify modified date of a particular database, i tried checking this value but what i observed is everytime i backup a database the last modified date changes.
So let me know if there a way to identify that, if not possible how would i implement that manually by creating some audit tables in each database which gives this information below
1. table name
2. login name
3. modified date
4. Action (Read/write/update)
5. textdata ( i think this field may occupy more space, may be i can skip )
and my second question is with regards to win 2003. I understand that windows will give a modified date on a folder or file when ever if something is changed on that file, how feasible is to depend on that date to identify last modified date of a file.
August 28, 2009 at 2:50 am
Tara (8/28/2009)
I a, trying to implement some audit on sql server databases. I dont think there is a way in sql server to identify modified date of a particular database, i tried checking this value but what i observed is everytime i backup a database the last modified date changes.So let me know if there a way to identify that, if not possible how would i implement that manually by creating some audit tables in each database which gives this information below
1. table name
2. login name
3. modified date
4. Action (Read/write/update)
5. textdata ( i think this field may occupy more space, may be i can skip )
and my second question is with regards to win 2003. I understand that windows will give a modified date on a folder or file when ever if something is changed on that file, how feasible is to depend on that date to identify last modified date of a file.
Be specific what exactly you want?
DO you want the last modified date of DB or any particular Object in DB or All objects in DB.
if not possible how would i implement that manually by creating some audit tables in each database which gives this information below
1. table name
2. login name
3. modified date
4. Action (Read/write/update)
5. textdata ( i think this field may occupy more space, may be i can skip )
And if you want to audit such actions..... you can do that with the help of server side default trace in SQL Server.
Be clear with your requirement so that you can get the desired help.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 28, 2009 at 5:36 am
Which version of SQL Server are you using?
If you are using SQL Server 2008, then you have the ability to trace the change easily.
Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
August 28, 2009 at 6:05 am
I am looking for all obejcts(tables) in the database, I already have default trace job setup in sql 2005 which runs every 15min and records it to the table but it records only if object deletes,adds or any permissions changes. how would record if any of the table in the db is either read,writeor update the table.
My goal is to find recent activity in a database, what ever it may be , i am trying to find if any of the databases in my server are un used.
September 15, 2009 at 6:59 am
Has anyone already implemented this kind of audit in sql 2005 to find out when a db or object is read/write/updated ?
September 15, 2009 at 8:36 am
Tara (9/15/2009)
Has anyone already implemented this kind of audit in sql 2005 to find out when a db or object is read/write/updated ?
One way to do this is to use a trace, but have a column filter on textdata for
%select%
%update%
%delete%
%insert%
you will need to do this on the event classes SQL and SP. better if you play around with it, then you can do it your way
this does work as I have just tried it.
--------------------------------------------------------------------------------------
[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]
September 15, 2009 at 9:30 am
but unfortunately text data is not captured in all events, i say NULLs in most places for textdata.
September 15, 2009 at 9:39 am
Tara (9/15/2009)
but unfortunately text data is not captured in all events, i say NULLs in most places for textdata.
Not sure what you are after then, what i have suggested and tested captures DML statements as direct sql and stored procedure based.
What else are you looking for, you mentioned read/write/updated operations
--------------------------------------------------------------------------------------
[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]
September 15, 2009 at 9:50 am
what i mean to say is in my table where i save all trace data more than half of the total records has NULL for textdata column.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply