Need Script to create log file

Question

Need Script to create log file

anandha31

SSC Veteran

Points: 250
More actions
December 6, 2011 at 11:23 pm

#261074

Dear All,
I need to create a log file in SQL Server 2008 which records the activities of the tables such as Insert, Update and Delete. The log file should record the transaction with time and system name.Please help in this regarding. Thanks in advance.
With Regards,
K.Anandhakumar

Viewing 14 posts - 1 through 13 (of 13 total)

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

Divine Flame SSCoach Points: 15943 More actions · Answer 1

anandha31 (12/6/2011)
Dear All,
I need to create a log file in SQL Server 2008 which records the activities of the tables such as Insert, Update and Delete. The log file should record the transaction with time and system name.Please help in this regarding. Thanks in advance.
With Regards,
K.Anandhakumar

Please explain your requirement so that we can help you better. You want to do this for each & every table in the database or is it for some tables ?

You can add triggers on those tables, which you want to be audited. Using triggers you can store the required data in some audit table you want.

Sujeet Singh

Divine Flame SSCoach Points: 15943 More actions · Answer 2

OR BTW, I forgot to ask, do you really want to create a log file i.e. (.ldf file) because your database has got corrupt OR you have only the data file (.mdf file) so you want to recover this database ?

Sujeet Singh

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

Divine Flame (12/6/2011)
OR BTW, I forgot to ask, do you really want to create a log file i.e. (.ldf file) because your database has got corrupt OR you have only the data file (.mdf file) so you want to recover this database ?

@Sujit: I don't think so he is talking about database log file. I assume its application log file that tracks changes to database operations.

@Anandha: Please confirm if my assumption is correct. Also if it's the case then you need to provide more information on requirements because we can log it in database tables easily. It would be handy for further analysis as well e.g. number of Inserts in a day, Failed Attempt of Inserts in a day etc.

mhike2hale SSC Eights! Points: 858 More actions · Answer 4

try to look for Change Data Capture(cdc) feature of Sql Server. This would be efficient than creating triggers for each table especially when you have lots of table to be monitored/audited.

"Often speak with code not with word,
A simple solution for a simple question"

anandha31 SSC Veteran Points: 250 More actions · Answer 5

Dear Sujeet,

Thanks for your reply. Actually I want to know when data are inserting / updating / deleting into the table and by whom it is happening? This should be for every table in the database. If anyone insert the data or delete the data in the table then I want to know who did that and from which system it has been made?. I am not able to identify. So I thought to trace the activites. But I dont know how to do? Because I am new to this.

anandha31 SSC Veteran Points: 250 More actions · Answer 6

anandha31

SSC Veteran

Points: 250

December 7, 2011 at 12:00 am

#1417796

Yes Ofcourse you are correct Mr.Dev

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

anandha31 (12/6/2011)
Actually I want to know when data are inserting / updating / deleting into the table and by whom it is happening? This should be for every table in the database. If anyone insert the data or delete the data in the table then I want to know who did that and from which system it has been made?. I am not able to identify. So I thought to trace the activites. But I dont know how to do? Because I am new to this.

Considering your audit / logging requirements, I would suggest you to hire a professional. As you said you are new to this, it would be difficult for you.

There are many options available and all of them have merits & demerits. A professional can assess your requirements & can suggest you better. PM me if you need more information.

Here is something for you to start with...

What, When and who? Auditing 101

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

tommey152 Right there with Babe Points: 772 More actions · Answer 8

anandha31 (12/6/2011)
Dear Sujeet,
Thanks for your reply. Actually I want to know when data are inserting / updating / deleting into the table and by whom it is happening? This should be for every table in the database. If anyone insert the data or delete the data in the table then I want to know who did that and from which system it has been made?. I am not able to identify. So I thought to trace the activites. But I dont know how to do? Because I am new to this.

Hi Anandha.....As mhike2hale said go and for Change Data Capture(cdc) feature of Sql Server

2008 this is new feature,To know more follow the link

http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

Hope this helps....

_______________________________________________________________

Need help? Help us help you.

anandha31 SSC Veteran Points: 250 More actions · Answer 9

Thanks Mr.Dev. I got an idea. Let me try CDC. If I find any difficulties then I will come back.

Also Thanks to all those who gave suggesstion

anandha31 SSC Veteran Points: 250 More actions · Answer 10

Thanks Mr.Tommey. I got an idea. Thanks to all those who gave suggesstions.

Divine Flame SSCoach Points: 15943 More actions · Answer 11

anandha31 (12/6/2011)
Dear Sujeet,
Thanks for your reply. Actually I want to know when data are inserting / updating / deleting into the table and by whom it is happening? This should be for every table in the database. If anyone insert the data or delete the data in the table then I want to know who did that and from which system it has been made?. I am not able to identify. So I thought to trace the activites. But I dont know how to do? Because I am new to this.

As Mhike suggested above you can go for CDC if you are using SQL Server 2008. Tommy has provided a good link above for you to understand it better.

I am not sure if CDC provides the user name also who changed the data. In that case, you can use triggers on the tables.

However, applying the triggers on all the tables may reduce the performance, so test, test, & test before applying :cool:.

Sujeet Singh

Suresh B. SSC-Insane Points: 22986 More actions · Answer 12

anandha31 (12/6/2011)
This should be for every table in the database.

Note that, if you try to store all the insert/delete/update info for all the tables in the db, it can grow larger if it is a long term requirement.

anandha31 (12/6/2011)
Dear Sujeet,
Thanks for your reply. Actually I want to know when data are inserting / updating / deleting into the table and by whom it is happening? This should be for every table in the database. If anyone insert the data or delete the data in the table then I want to know who did that and from which system it has been made?. I am not able to identify. So I thought to trace the activites. But I dont know how to do? Because I am new to this.

I have few suggestion here.

1) Add columns called UserName and CreateDate to all the tables. Set SUSER_SNAME() and GETDATE() as DEFAULT vaulue. This will automatically give you information about who inserted the row and when.

2) Write stored procedure for insert/delete/update. Don't allow insert/delete/updates directly to the table. In the stored procedure you can capture additional information for auditing.

3) SQL Server 2008 has new Auditing features.

anandha31 SSC Veteran Points: 250 More actions · Answer 13

Dear Suresh,

Thanks for your suggestion.I cant add new column for that. Because tables are related with .net application. Let me try with CDCI.