Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Log LDF file Expand / Collapse
Author
Message
Posted Wednesday, August 14, 2013 2:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, 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

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

Please help its urgent.. Client need answer








************************************
Every Dog has a Tail !!!!!
Post #1484124
Posted Wednesday, August 14, 2013 4:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
Somebody please suggest n help







************************************
Every Dog has a Tail !!!!!
Post #1484175
Posted Wednesday, August 14, 2013 4:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 2,242, Visits: 2,700
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’! **
Post #1484178
Posted Wednesday, August 14, 2013 4:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 42,481, Visits: 35,551
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 2008, MVP
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

Post #1484180
Posted Wednesday, August 14, 2013 5:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, 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 !!!!!
Post #1484190
Posted Wednesday, August 14, 2013 5:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 42,481, Visits: 35,551
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 2008, MVP
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

Post #1484197
Posted Wednesday, August 14, 2013 8:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, 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 !!!!!
Post #1484308
Posted Wednesday, August 14, 2013 8:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:10 PM
Points: 2,826, Visits: 8,463
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.



Post #1484318
Posted Wednesday, August 14, 2013 8:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 42,481, Visits: 35,551
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 2008, MVP
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

Post #1484321
Posted Wednesday, August 14, 2013 8:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, 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 !!!!!
Post #1484326
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse