Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log LDF file


Log LDF file

Author
Message
OnlyOneRJ
OnlyOneRJ
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 690
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
OnlyOneRJ
OnlyOneRJ
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 690
Somebody please suggest n help Sad

************************************
Every Dog has a Tail !!!!! :-D
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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’! **
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
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
OnlyOneRJ
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 690
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
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
OnlyOneRJ
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 690
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
homebrew01
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2988 Visits: 9071
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.



GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
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
OnlyOneRJ
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 690
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search