How to capture query errors using SQL Server Extended Events

If you're troubleshooting SQL Server query errors , you'll already know Extended Events are highly useful and very effective method to capture SQL Server errors. To use the script you'll need appropriate privileges to create the Extended Event. You will also need to have some space available on the disk to store output files. I usually place them in the C:\tmp - although be careful just in case you fill the C:\ drive! The alternative is to use another drive. CREATE EVENT SESSION [CaptureErrors] ON SERVER ADD EVENT sqlserver.error_reported( ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.nt_username, sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username) WHERE ([severity]>=(10))) ADD TARGET package0.event_file(SET filename=N'C:\tmp\CaptureErrors.xel',metadatafile=N'C:\tmp\CaptureErrors.xem') WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1... Read more →

Troubleshoot SQL Security Error Ring Buffer Recorded - System Health

When monitoring sql security error events recorded through the System Health Extended Events , there’s a regular event recorded : security_error_ring_buffer_recorded. How can I troubleshoot? These are some general guidelines and tools for troubleshooting these security errors. Some are easy to track down but there are also some esoteric security errors / 1) Identify the meaning of Error Code with net helpmsg .example net helpmsg 5023 ERROR_INVALID_STATE 5023 (0x139F) The group or resource is not in the correct state to perform the requested operation. The error message description can be sometimes very general. If the explanation is not obvious ,... Read more →

SQL Server – Find who made a database security change

A nightly SQL Server Agent batch job started failing suddenly , with a message about a database user missing. The SQL Server Agent job history indicated the job ran successfully for 6 months. Either someone had restored the database to a much older version or a database user was deleted. The SQL Server default trace has an event - Audit Add DB User . The event is recorded in the trace when a login is added\removed as a database user. This query returns all events of the Audit Add DB User . Luckily , the trace file hadn’t yet rolled... Read more →

Audit Backup\Restore Event with SQL Default trace

What is the default audit trace in SQL Server? How to check backup restore status in SQL? Read more →

SQL Server - Who made DDL table changes on the database.

Question: Some DDL changes have occurred on the SQL Server database. Can I find out who made the changes? Answer: Yes. The SQL Server default trace has the Object Altered event. Read this FAQ for details on SQL default trace This script will list all Object Altered event. Add WHERE predicates on date and databasename to refine the search and isolate the DDL change select e.name as eventclass, t.loginname, t.spid, t.starttime, t.textdata, t.objectid, t.objectname, t.databasename, t.hostname, t.ntusername, t.ntdomainname, t.clientprocessid, t.applicationname, t.error FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1f.[value] FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2)), DEFAULT) T inner join sys.trace_events e... Read more →