Log LDF file

Question

Post reply

Log LDF file

OnlyOneRJ

Hall of Fame

Points: 3894
More actions
August 14, 2013 at 2:25 am

#277027

I came to know that some records are deleted from Table in SQL SERVER 2008...
We want to track it & see what happened & who deleted :w00t:
No log backup is taken.. only LDF file is there which is almost 5GB now..
please suggest how will i be able to read the LDF file to track the operation done yesterday when records were deleted :w00t:
Please help its urgent.. Client need answer :crazy:
************************************
Every Dog has a Tail !!!!! :-D

Viewing 15 posts - 1 through 15 (of 40 total)

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

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 1

Somebody please suggest n help 🙁

************************************
Every Dog has a Tail !!!!! :-D

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

It is almost impossible to get information about actions being taken in the past, if you don't have some kind of auditing/logging/tracing active. If it is important to know who is changing what data in the database you'll need to setup some kind of auditing. This can be done with third party tools, but audit is also available in SQL 2008. Please read this link http://technet.microsoft.com/en-us/library/dd392015(v=sql.100).aspx for more information about it.

There are some tools (like: http://www.apexsql.com/sql_tools_log.aspx) that can read the content of a log file (.LDF). I don't know if this tool will give you the information you seek.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Unless the database is in full recovery and there was a full backup taken at some point prior to the delete, there won't be anything in the log to analyse.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 4

Well... thanks for this..

What can be done in future to avoid such things??

Taking log backup every 3 hours will help??

Please provide expert comments. Gail please help us making a plan so that if incase in future something gets deleted.. we will be answerable easily to customers as who did that..

We cant make Auditing set ON all the time.. it may hamper performance..

please suggest

************************************
Every Dog has a Tail !!!!! :-D

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Well, I guess the first step is to identify requirements, before grabbing a solution at random.

What's the DB's RPO? RTO? What are the audit requirements for DDL? DML? What are the security requirements around who can do what?

etc, etc, etc

Oh, and none of those are technical questions...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 6

Hi Gail,

Requirenment here is to track if any update or delete happens by mistake, for which i am asked to take Log backups every 3 hours..

Execution of the backups finishes in 10 minutes .. so not problem..

Just a query that..

In-case if any delete happens then how will i provide information using those Log backups??? do i need any tool for it???

************************************
Every Dog has a Tail !!!!! :-D

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

Is 3 hour data loss acceptable for the business ? I prefer log backups every 15 or 20 minutes.

I run a server side trace 24/7 on my production server so that I can go back in time to troubleshoot problems or see what was running at a particular time. There are 3rd party tools such as Idera's Compliance Manager that can be set up to audit SQL activity.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

You didn't answer any of my questions.

GilaMonster (8/14/2013)
What's the DB's RPO? RTO? What are the audit requirements for DDL? DML? What are the security requirements around who can do what?
etc, etc, etc

"We're required to track updates and deletes and hence I'm taking log backups" is like saying "I need to track money going out of my bank account therefore I'm going to take out an insurance policy". The requirement and the proposed solution have nothing to do with each other.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 9

Hi Gail, i am not aware of your questions.. as we are not involved in it.. just they ask us for suggestions & set the plan.. or ask us the information when anything is wrong....

************************************
Every Dog has a Tail !!!!! :-D

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 10

homebrew01 (8/14/2013)
Is 3 hour data loss acceptable for the business ? I prefer log backups every 15 or 20 minutes.
I run a server side trace 24/7 on my production server so that I can go back in time to troubleshoot problems or see what was running at a particular time. There are 3rd party tools such as Idera's Compliance Manager that can be set up to audit SQL activity.

I am not asked for Server side trace.. just asked me to take backup. but will it help.. will that provide me data about who did what & what ahppened :w00t:

************************************
Every Dog has a Tail !!!!! :-D

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

OnlyOneRJ (8/14/2013)
will that provide me data about who did what & what ahppened :w00t:

No.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 12

GilaMonster (8/14/2013)
OnlyOneRJ (8/14/2013)
will that provide me data about who did what & what ahppened :w00t:
No.

Gail, Please suggest a easy plan to do where in trace n audit is not involved... only Log backup's or anything else... please

************************************
Every Dog has a Tail !!!!! :-D

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Can't, not with your set of restrictions. You're insisting that the easiest solution not be used and that a feature that has nothing to do with audits be used instead.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 14

OnlyOneRJ (8/14/2013)
homebrew01 (8/14/2013)
Is 3 hour data loss acceptable for the business ? I prefer log backups every 15 or 20 minutes.
I run a server side trace 24/7 on my production server so that I can go back in time to troubleshoot problems or see what was running at a particular time. There are 3rd party tools such as Idera's Compliance Manager that can be set up to audit SQL activity.
I am not asked for Server side trace.. just asked me to take backup. but will it help.. will that provide me data about who did what & what ahppened :w00t:

A trace or monitoring tool may help you troubleshoot the NEXT problem, but not the current one. Taking regular transaction log backups may help you recover lost data NEXT time.