Please help analyzing MS SQL Database Logfile(s)

Question

Please help analyzing MS SQL Database Logfile(s)

erkan.erbil

SSC Journeyman

Points: 89
More actions
September 2, 2011 at 1:35 am

#244111

On 08/31/2011 between 13:00 - 14:00 o clock on PROD Servicemanager DB data in Tables XXXXXXM1 / XXXXXXM2 were deleted. We don't know how and by whom.
Appserver Logs showed no hint. Our last idea is to find some Information on SQL Logs. Therefore we need your support.

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

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

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

Transaction logs aren't audit logs. They won't show login names, host names or application names

If you want to read the logs, you need either the log to not have been truncated since then (so full recovery and no log backups) or you need the log backups covering that period, and the log reader tools start at around $1000 (Apex SQLLog)

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

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

Unless there have been frequent SQL restarts since then, the information you're after should be in the default trace (you can use the schema modification report to look through that). If the info's not in there and you don't have custom auditing, then there won't be any way of telling what happened.

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

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 3

Gail tips should get you there. In case you want to make sure the default trace is on, execute sp_configure and check if the "run_value" of the "default trace enabled" is "1".

My blog

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 4

GilaMonster (9/2/2011)
(you can use the schema modification report to look through that).

Gail, do you mean the "Schema changes history" report?

My blog

forsqlserver SSCoach Points: 18902 More actions · Answer 5

Hi Rangel,

What is the work of "run value"?

How to get the complete list of sp_configure ?

Thanks

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

Ignacio A. Salom Rangel (9/2/2011)
Gail tips should get you there. In case you want to make sure the default trace is on, execute sp_configure and check if the "run_value" of the "default trace enabled" is "1".

Changes, modification, same difference, close enough

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

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 7

GilaMonster (9/2/2011)
Ignacio A. Salom Rangel (9/2/2011)
Gail tips should get you there. In case you want to make sure the default trace is on, execute sp_configure and check if the "run_value" of the "default trace enabled" is "1".
Changes, modification, same difference, close enough

😉

My blog

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 8

forsqlserver (9/2/2011)
Hi Rangel,
What is the work of "run value"?
How to get the complete list of sp_configure ?

EXEC sp_configure 'show advanced option', '1' (Don't take my word for it and check this link before you will execute it. Run_value will tell you what the current value is on your Server.

My blog

forsqlserver SSCoach Points: 18902 More actions · Answer 9

Oh yes I have seen I was earlier thinking as a keyword.

Thanks

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 10

forsqlserver (9/2/2011)
Oh yes I have seen I was earlier thinking as a keyword.

You are welcome 😉

My blog

erkan.erbil SSC Journeyman Points: 89 More actions · Answer 11

Thanks for the information you have provided, but how can I find out who deleted that.

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 12

erkan.erbil (9/2/2011)
Thanks for the information you have provided, but how can I find out who deleted that.

Did you check the report Gail told you?

My blog

erkan.erbil SSC Journeyman Points: 89 More actions · Answer 13

I made it

show advanced optionsMin:0Max:1Config_value:1run_value:1

and than What will I do it?

I could not run Apexsql

I want to see who was deleted.

Lowell SSC Guru Points: 323518 More actions · Answer 14

erkan.erbil (9/2/2011)
On 08/31/2011 between 13:00 - 14:00 o clock on PROD Servicemanager DB data in Tables XXXXXXM1 / XXXXXXM2 were deleted. We don't know how and by whom.
Appserver Logs showed no hint. Our last idea is to find some Information on SQL Logs. Therefore we need your support.

the previous posters alluded to the schema changes history report...hope this helps....

your default trace provides a limited window of review before it rolls over... if it has not rolled over, you can most easily access it from SSMS:

Good Luck!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!