March 11, 2015 at 4:21 am
Hi,
Someone droped a stored procedure from one of my databases sometime ago.
Can I find who deleted this procedure through a log?
The procedure name is sp_importactbs
March 11, 2015 at 4:40 am
Hi,
I tryed this funcion:
USE ReadingDBLog
GO
SELECT
Operation,
[Transaction Id],
[Transaction SID],
[Transaction Name],
[Begin Time],
[SPID],
Description
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GO
but this gave nothing....
I have the simple recovery model. it's because of this?
March 11, 2015 at 4:59 am
You can try the default trace. It's usually stored in the same folder as your errorlog and SQL Server Agent log files. You can open the file with Profiler and search for the procedure name, or you can export to a table so that you can query it. There may be a function or stored procedure (documented or not) that reads from a trace file as well - I've never used it if there is.
John
March 11, 2015 at 5:00 am
Even in full recovery, you wouldn't have been able to use that to find something that happened 'some time ago'. The log contains records back to the last log backup/checkpoint.
You can check the default trace, but since it only keeps 5 files of 20MB, there's a good chance that the drop would no longer show up in the default trace. If it doesn't, and you have no custom auditing, then you can't tell who dropped the procedure.
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
March 11, 2015 at 5:11 am
The default trace can be read from inside the sql server? do I have any function to read it?
March 11, 2015 at 5:24 am
Question is:
How can I know what were the droped objects from the default trace.
Can I use a function from SQL Server?
March 11, 2015 at 5:31 am
river1 (3/11/2015)
The default trace can be read from inside the sql server? do I have any function to read it?
sys.fn_trace_getinfo & fn_trace_gettable
Something like this:
declare @file nvarchar(1000);
SELECT @file = cast(value as nvarchar(1000))
FROM sys.fn_trace_getinfo(0)
WHERE traceid = 1 and property = 2;
PRINT @file
SELECT tgt.SPID, tgt.Duration, tgt.ClientProcessID, tgt.TextData, tgt.DatabaseName, tgt.HostName, tgt.LoginName, tgt.ApplicationName, tgt.StartTime, tgt.Reads, tgt.Writes,
tgt.CPU, tgt.ObjectID, tgt.FileName, tgt.IntegerData2, tgt.BigintData1, tgt.BigintData2, te.name AS TraceEvent, tc.name AS TraceCategory
FROM sys.fn_trace_gettable(@file, default) tgt
INNER JOIN sys.trace_events te ON te.trace_event_id = tgt.EventClass
INNER JOIN sys.trace_categories tc ON tc.category_id = te.category_id
WHERE tc.category_id = 5 /*Objects*/
AND te.trace_event_id = 47 /*Deleted*/
ORDER BY StartTime DESC;
This will return the contents of the most recent file; substitute the @file parameter in fn_trace_gettable with the output of the PRINT statement and decrement the file number for earlier files.
March 11, 2015 at 5:56 am
It gave me a lot of records
But non with the object name , just the object id.
But if the object was deleted (droped) how can I know it's name from the object id ?
Is it still in the sys.objects table after been droped?
March 11, 2015 at 6:02 am
river1 (3/11/2015)
Is it still in the sys.objects table after been droped?
No.
If the procedure was dropped some time ago, it's unlikely that the entry is still in the default trace. It only stores 5 files of no more than 20 MB each.
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
March 11, 2015 at 6:09 am
Some default traces last longer than others. If your applications are well designed and don't constantly perform DDL operations, then you've a fighting chance of having a few days' information or more available to you.
But if the object was deleted (droped) how can I know it's name from the object id ?
Try adding one of the other columns from fn_trace_gettable to your query.
John
March 11, 2015 at 6:16 am
river1 (3/11/2015)
It gave me a lot of recordsBut non with the object name , just the object id.
But if the object was deleted (droped) how can I know it's name from the object id ?
Is it still in the sys.objects table after been droped?
Oh yeah, sorry about that! Try adding tgt.ObjectName to the where clause and/or select list.
March 11, 2015 at 8:00 am
But how can I view if I still can find that object id correpondent name inside the trace?
March 11, 2015 at 11:55 am
If it's not there then as Gail & JM said it probably happened before the oldest record in the trace, and you won't be able to find it.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply