An unusual situation has come up at a client. While reviewing some integration logs, I saw that a custom database table had been deleted from the production Dynamics GP company database. The integration log recorded an error from a stored procedure--the proc was trying to insert a record into the table, but the table no longer existed.
An unexpected error occurred in SaveUpdateCCHistory: Invalid object name 'cstb_Update_CC_History'.
Since the procedure was still present, but the table wasn't, we were pretty confident the table had been deleted, since you can't create a proc that refers to a non-existent table.
Very strange. We recreated the table, and moved on.
Then a few days later, while reviewing the integration logs in the Test company database, we saw an error indicating that a call to a custom stored procedure was failing.
An unexpected error occurred in InsertCustomerEmailOptions: Could not find stored procedure 'cssp_New_Customer_Email'
Sure enough, that procedure was missing from the Test database.
These objects have been in both databases for weeks, if not several months, so we hadn't touched them, and certainly didn't have any processes or scripts that would delete them.
The client said they couldn't think of any way the objects would be deleted.
These mysteries are always difficult to research "after the fact". The ideal solution is to have third party SQL Server auditing software that records such activity and lets you review it later when a problem occurs. But since such SQL administration is relatively rare with GP clients, we usually have limited tools to research such issues.
After some Googling, I found a few articles on how you can query a database transaction log to determine when a database object was dropped and who dropped it.
But there are two big caveats:
1. The fn_dblog and fn_dump_dblog functions are not documented or supported, have been shown to have bugs, and can result in some unexpected consequences. So you should use them very cautiously.
2. The fn_dblog and fn_dump_dblog functions read database transaction log activity. So if the database has had one or more full backups since the drop, you are likely out of luck and will not find any information about the dropped objects.
Technically it is possible to read from a database backup file, but such files typically do not have much log data to work with, so the odds of finding the drop data in a backup file are slim.
Also, technically it is possible to use the functions to read directly from transaction logs, but I don't think I've ever seen a GP client that intentionally or systematically backs up their SQL transaction logs, so that is typically a long shot as well. Usually, once a full DB backup is performed, the transaction logs get cleared.
But, aside from those rather significant limitations, I was still pretty impressed that it is possible to determine when an object was dropped, and who dropped it. I'm guessing it will not be very helpful in a real environment where you may not know an object was dropped for a few days, but if you discover the problem quickly, you can try it and see if it works.
Below is the script that I used to test with the TWO database. I create a test table and insert some rows. I then backup the database and restore to a different database name. I then drop the table.
After the table is dropped, I can query the transaction log to see that an object was dropped, who dropped it, but I can't tell which object was dropped (since it no longer exists).
To get the name of the dropped object, you have to restore a backup of the database, then use the dropped object ID to query the database (where the object still exists), to see the object name.
USE [TWO]
GO
--Create table
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type IN (N'U'))
BEGIN
CREATE TABLE [dbo].[TestTable](
[FirstName] [varchar](30) NOT NULL,
[LastName] [varchar](30) NOT NULL,
[RowID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
END
GO
--Populate table
INSERT INTO TestTable (FirstName, LastName) VALUES ('Chuck', 'Norris')
INSERT INTO TestTable (FirstName, LastName) VALUES ('George', 'Washington')
INSERT INTO TestTable (FirstName, LastName) VALUES ('Britney', 'Spears')
--Verify table
SELECT * FROM TestTable
--****************************
--BACKUP DATABASE NOW AND RESTORE TO NEW DB
--****************************
--Drop the table in TWO
DROP TABLE TestTable
--Get info on drop from transaction log
SELECT
[Begin Time],
[Transaction Name],
SUSER_SNAME([Transaction SID]) AS UserName,
[Transaction Id],
[Transaction SID],
[SPID],
(SELECT TOP (1) [Lock Information] FROM fn_dblog (NULL, NULL)
WHERE [Transaction Id] = fndb.[Transaction Id]
AND [Lock Information] LIKE '%SCH_M OBJECT%') AS ObjectID
FROM fn_dblog (NULL, NULL) AS fndb
WHERE [Transaction Name] = 'DROPOBJ'
GO
--In the prior query, review the ObjectID field values. The object ID is the numeric value at the end, in between colons
--HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 6:1554182124:0
--In this example, the object ID is: 1554182124
--Insert numeric object ID and run on restored copy of DB
USE TWO01
SELECT OBJECT_NAME(1554182124) AS ObjectName
Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics
GP Certified IT Professional in Los Angeles. He is the owner of Precipio
Services, which provides Dynamics GP integrations, customizations, and
automation solutions.
No comments:
Post a Comment