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 →

INSERT BULK in Profiler trace

In a Profiler trace you can see INSERT BULK statements , which are a mechanism for interface between SQL Server and a client bulk copy API.For example : .Net SqlBulkCopy & ODBC Bulk Copy methods. The Profiler trace whill display the INSERT BULK statement but the FROM part , meaning you cannot view the values. The INSERT BULK statement specifies the target tables \ columns including other meta data information - NULL management , triggers etc The tricky bit from a troubleshooting per, spective is you cannot view the values in the INSERT BULK statement. The values are transfered in... Read more →

SQL Server 100 cpu diagnose

Question: Users are reporting long delays in query responses. I checked on the server and noticed CPU counter at 100 % on Performance Monitor. There are 4 cores associated to this server and all the cores were hovering around 100%. How can I diagnose the underlying cause ? Answer: It’s a common problem faced by DBAs , where the server is sitting at 100% for prolonged periods. Check there are no other processes outside of SQL Server which are causing the server to be at 100%. Typically security scans, virus scans and monitoring can be root causes. I have also... Read more →

ERROR 8623 The query processor ran out of internal resources and could not produce a query plan

Question: In the SQL Server Error Logs I saw the following message: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query Is there a way to find the query ? I know I can use something like Extended Events – which would allow me to trap the sql text , but what about finding a query that has executed and triggered this error Answer: If you... Read more →

How to Audit user DML with SQL Server Extended Events

Question: What methods are available to audit the DML activity of a user. I need to track a third party developer and record the DML statements they execute on a SQL Server data? The data needs to be recorded and made available for a regular review. Answer: There are many ways to skin the audit cat. Since SQL Server 2008 the DBA life has become easier with the introduction of Extended events , as applied to auditing. Before I explain of how to set up Extended Events let’s review some other methods Manual auditing – refers to a home grown... Read more →