In my introduction to Extended Events blog, I mentioned that the xEvent is my favorite toy for performance troubleshooting. This blog may make you to start playing with it. In this tip, I will be discussing how you can find what caused SQL Server to write error in the Errorlog file. 

Problem:

One of the clients reported that SQL Server was kept writing below error into the SQL Server Errorlog file, but they were not able to find the reason what causing SQL Server to write the error.

“Error [36, 17, 145] occurred while attempting to drop allocation unit ID XXXXXXXXXXX belonging to worktable with partition ID XXXXXXXXXXX.”

Solution:

To find the reason for the error, I performed a thorough health check-up of the instance but didn’t see anything abnormal. So, I did quick google and scanned through the following blogs to find out the solution but no lock 🙁

• msdn – Error[36, 17, 145] occurred while attempting to drop allocation unit
• sqlservercentral – Error [36, 17, 145] occurred while attempting to drop allocation unit
• sqlservercentral – Error [36, 17, 145] occurred while attempting to drop allocation unit ID xxx belonging to worktable…
• dba.stackexchange.com – Error [36, 17, 145]occurred while attempting to drop allocation unit ID belonging to worktable with partition ID

Since I could not find any clue from above blogs, I used xEvent to create one event session on the server to collect the sqlserver.error_reported and sqlserver.errorlog_written.

• sqlserver.error_reported: This event gets fired every time when an error happens on the SQL Server.
• sqlserver.errorlog_written: This event gets triggered when SQL Server error log is written to.

CREATE EVENT SESSION [Capture_SQLReport_Error]ON SERVER ---- You can change name as you want.
ADD EVENT sqlserver.error_reported ------------------------- Any error reported by SQL Server
    (ACTION (package0.last_error,
			 sqlserver.database_name,
			 sqlserver.nt_username,
			 sqlserver.query_hash,
			 sqlserver.query_plan_hash,
			 sqlserver.session_id,
			 sqlserver.sql_text,
			 sqlserver.username)
			),
ADD EVENT sqlserver.errorlog_written------------------------- Any error written by SQL Server in the error log
	(ACTION (package0.last_error,
			 sqlserver.database_id,
			 sqlserver.nt_username,
			 sqlserver.plan_handle,
			 sqlserver.query_hash,
			 sqlserver.query_plan_hash,
			 sqlserver.session_id,
			 sqlserver.sql_text,
			 sqlserver.username
			)	
	) 
ADD TARGET package0.event_file
	(SET filename=N'Capture_SQLReport_Error.xel', --------Mention the file name which you want to create r
	 max_file_size=(600)						  --------at default error log folder to save the output
	)
WITH (	MAX_MEMORY=4096 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)
GO

The xEvent helped to capture what was causing to generate the error in the SQL Server Errorlog. 

Happy Learning!