July 20, 2017 at 12:58 am
Good Morning Experts,
SOmeone tried to insert duplicate value in a table yesterday and we see this error
Violation of PRIMARY KEY constraint 'idx_volMon_p_cl_01'. Cannot insert duplicate key in object 'dbo.tbl_util_volMon'. The duplicate key value is (DCD16, D:\SQLDATA\SQL02, Jul 19 2017 9:01AM).".
I would like to know who inserted the duplicated key value. I am aware of fn_dblog(null,null) but not sure how to use it to find who inserted the duplicate values yesterday. Please advise
July 20, 2017 at 4:08 am
The somewhat obtuse answer is nobody inserted it, because your primary key constraint did what it's there for and blocked it. If you really need information like this, I would advise you to set up extended events or similar to capture it. Otherwise it's not easy to do after the fact. If you want to use fn_dblog, although it's an undocumented function there is nevertheless plenty of information out there about how it works. (I'm not familiar with it myself, but I'd guess that if your log has been truncated since the error occurred, which it certainly should have been, then you may be out of luck.) Normally the error message would be presented to the caller, so if you know what the message was, how do you not know who caused it?
John
July 20, 2017 at 4:18 am
Unless you have some custom monitoring in place recording user errors and what login caused them, you can't tell.
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
July 20, 2017 at 7:03 am
I would think the log would be useless, as it has what was successfully committed, and is not a log of errors and what did NOT get inserted.
you can add an extended event to capture errors, and that might help a little bit:
--#################################################################################################
--developer utility function added by Lowell
--Purpose: Creates an Extended Event that captures all errors raised on the server,
-- that might be supressed by an application.
--#################################################################################################
IF NOT EXISTS(SELECT * FROM [sys].[server_event_sessions] AS [dxs] WHERE [dxs].[name] = 'ApplicationErrors')
BEGIN
CREATE EVENT SESSION [ApplicationErrors] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(package0.event_sequence,
package0.last_error,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_id,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.plan_handle,
sqlserver.query_hash,
sqlserver.query_plan_hash,
sqlserver.session_nt_username,
sqlserver.sql_text,
sqlserver.username)
WHERE ([package0].[greater_than_equal_int64]([severity],(14))
--AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'SolarWinds')
)
)
ADD TARGET package0.event_file(SET filename=N'ApplicationErrors.xel')
WITH (MAX_MEMORY=1024 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
END
--ALTER EVENT SESSION [ApplicationErrors] ON SERVER STATE = START
--ALTER EVENT SESSION [ApplicationErrors] ON SERVER STATE = STOP
Lowell
July 20, 2017 at 7:26 am
Lowell - Thursday, July 20, 2017 7:03 AMI would think the log would be useless, as it has what was successfully committed...
Or rolled back. I believe. I've never investigated, though, but I'm sure the OP will do his own thorough research and come to a conclusion that works for him.
John
July 20, 2017 at 7:45 am
coolchaitu - Thursday, July 20, 2017 12:58 AMGood Morning Experts,
SOmeone tried to insert duplicate value in a table yesterday and we see this error
Violation of PRIMARY KEY constraint 'idx_volMon_p_cl_01'. Cannot insert duplicate key in object 'dbo.tbl_util_volMon'. The duplicate key value is (DCD16, D:\SQLDATA\SQL02, Jul 19 2017 9:01AM).".
I would like to know who inserted the duplicated key value. I am aware of fn_dblog(null,null) but not sure how to use it to find who inserted the duplicate values yesterday. Please advise
So, it looks like you did log the error at the time it occurred, assuming what you posted above is the actual error and timestamp. Did your error log not also include the login name or SPID ? If you at least know the SPID of the process that attempted the insert and the time the error occurred, then there are some options to work from that to get the user.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 20, 2017 at 10:01 am
Eric M Russell - Thursday, July 20, 2017 7:45 AMcoolchaitu - Thursday, July 20, 2017 12:58 AMGood Morning Experts,
SOmeone tried to insert duplicate value in a table yesterday and we see this error
Violation of PRIMARY KEY constraint 'idx_volMon_p_cl_01'. Cannot insert duplicate key in object 'dbo.tbl_util_volMon'. The duplicate key value is (DCD16, D:\SQLDATA\SQL02, Jul 19 2017 9:01AM).".
I would like to know who inserted the duplicated key value. I am aware of fn_dblog(null,null) but not sure how to use it to find who inserted the duplicate values yesterday. Please advise
So, it looks like you did log the error at the time it occurred, assuming what you posted above is the actual error and timestamp. Did your error log not also include the login name or SPID ? If you at least know the SPID of the process that attempted the insert and the time the error occurred, then there are some options to work from that to get the user.
Hi Eric,
The error log does not include the login name or spid. Is there any way by using fn_dblog()?
July 20, 2017 at 10:22 am
coolchaitu - Thursday, July 20, 2017 10:00 AMEric M Russell - Thursday, July 20, 2017 7:45 AMcoolchaitu - Thursday, July 20, 2017 12:58 AMGood Morning Experts,
SOmeone tried to insert duplicate value in a table yesterday and we see this error
Violation of PRIMARY KEY constraint 'idx_volMon_p_cl_01'. Cannot insert duplicate key in object 'dbo.tbl_util_volMon'. The duplicate key value is (DCD16, D:\SQLDATA\SQL02, Jul 19 2017 9:01AM).".
I would like to know who inserted the duplicated key value. I am aware of fn_dblog(null,null) but not sure how to use it to find who inserted the duplicate values yesterday. Please advise
So, it looks like you did log the error at the time it occurred, assuming what you posted above is the actual error and timestamp. Did your error log not also include the login name or SPID ? If you at least know the SPID of the process that attempted the insert and the time the error occurred, then there are some options to work from that to get the user.
Hi Eric,
The error log does not include the login name or spid. Is there any way by using fn_dblog()?
No, the transaction log basically functions like a block chain for modified pages. In this case no pages were modified, so the attempted insert operation itself would not be logged, ... unless it was part of a larger transaction. You can try looking for other related DML operations that actually did complete around that time frame. Even do, does fn_dblog() include the SPID; I don't think so. Why not just send out a department wide email asking your user base who among them was inserting data yesterday but got an error.
Does it really matter? At least you had the proper unique constraint in place to prevent the duplicate insert.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 21, 2017 at 7:52 am
Eric M Russell - Thursday, July 20, 2017 10:22 AMcoolchaitu - Thursday, July 20, 2017 10:00 AMEric M Russell - Thursday, July 20, 2017 7:45 AMcoolchaitu - Thursday, July 20, 2017 12:58 AMGood Morning Experts,
SOmeone tried to insert duplicate value in a table yesterday and we see this error
Violation of PRIMARY KEY constraint 'idx_volMon_p_cl_01'. Cannot insert duplicate key in object 'dbo.tbl_util_volMon'. The duplicate key value is (DCD16, D:\SQLDATA\SQL02, Jul 19 2017 9:01AM).".
I would like to know who inserted the duplicated key value. I am aware of fn_dblog(null,null) but not sure how to use it to find who inserted the duplicate values yesterday. Please advise
So, it looks like you did log the error at the time it occurred, assuming what you posted above is the actual error and timestamp. Did your error log not also include the login name or SPID ? If you at least know the SPID of the process that attempted the insert and the time the error occurred, then there are some options to work from that to get the user.
Hi Eric,
The error log does not include the login name or spid. Is there any way by using fn_dblog()?No, the transaction log basically functions like a block chain for modified pages. In this case no pages were modified, so the attempted insert operation itself would not be logged, ... unless it was part of a larger transaction. You can try looking for other related DML operations that actually did complete around that time frame. Even do, does fn_dblog() include the SPID; I don't think so. Why not just send out a department wide email asking your user base who among them was inserting data yesterday but got an error.
Does it really matter? At least you had the proper unique constraint in place to prevent the duplicate insert.
I may be misreading the OPs intent, but wouldn't modifying the error logging script to add ORGINAL_LOGIN() solve his problem?
July 21, 2017 at 9:36 am
Yes, going forward, SPID and LoginName would be useful in the error log.
For now, if no one fesses up to the infraction, then perhaps hold all the users collectively responsible. Who knows what momentary lapse in judgement or whatever else could have compelled them to do such a thing as this?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply