Extended Events to Capture Errors;

Hi Extended Events were introduced in SQL Server 2008, I just started learning about it, Jonathan Kehayias has an excellent white paper on Extended Events White Paper.  He has also blogged about it extensively.
I have frequently encountered problem where user doesn't have permission to execute something or they have errors running some script. I used to start a trace to monitor for Errors and Warnings. I thought about doing it differently, Extended Events can be used to capture permission denied errors, syntax errors and login failures etc. I have a small script to demonstrate that.

-- Create an Event session named CaptureErrors we are looking for errors between 10 and 20 in severity. I am using a file target. Also capturing sql_text, along with the database id, host computer etc.

Jonathan had mentioned about using max dispatch latency of 1 second ,  since it could impact the dispatcher pool and  it should be used  for demonstration purposes only.

CREATE EVENT SESSION CaptureErrors

ON SERVER
   ADD EVENT sqlserver.error_reported
   (ACTION (sqlserver.sql_text,sqlserver.database_id,sqlserver.client_hostname
   ,sqlserver.username,sqlserver.nt_username,sqlserver.tsql_stack)
     WHERE severity>=10 and severity<20/*anything over20 is captured by System Health Event*/ )
   ADD TARGET package0.asynchronous_file_target
  (SET FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.V1\MSSQL\Log\CaptureErrors.xel'
  , METADATAFILE = N'C:\Program Files\Microsoft SQL Server\MSSQL11.V1\MSSQL\Log\CaptureErrors.xem')
       WITH (max_dispatch_latency = 1 seconds);
GO
ALTER EVENT SESSION CaptureErrors ON SERVER

STATE = START
GO

--Once its started we could execute some scripts to generate errors

SELECT * from  databases
go
SELECT  from test1
GO

--We could then Query the file target and get the error messages.


IF OBJECT_ID('tempdb..#ErrorData') IS NOT NULL
       DROP TABLE #ErrorData
CREATE TABLE #ErrorData
(event_data xml)

INSERT INTO #ErrorData (event_data)
SELECT  CAST(event_data as XML) FROM sys.fn_xe_file_target_read_file
     ('C:\Program Files\Microsoft SQL Server\MSSQL11.V1\MSSQL\Log\CaptureErrors*.xel'
, 'C:\Program Files\Microsoft SQL Server\MSSQL11.V1\MSSQL\Log\CaptureErrors*.xem', NULL, NULL);

SELECT
       event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
   event_data.value('(event/@package)[1]', 'varchar(50)') AS package_name,
   event_data.value('(event/@id)[1]', 'varchar(50)') AS ID,
   event_data.value('(event/@version)[1]', 'varchar(50)') AS Version,
   DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
           event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
   event_data.value('(event/data[@name="error"]/value)[1]', 'int') as ErrorNumber,
   event_data.value('(event/data[@name="severity"]/value)[1]', 'smallint')  AS Severity,
   event_data.value('(event/data[@name="state"]/value)[1]', 'tinyint')  AS [State],
   event_data.value('(event/data[@name="user_defined"]/value)[1]', 'nvarchar(100)') as User_Defined,
   event_data.value('(event/data[@name="message"]/value)[1]', 'nvarchar(250)') as [Message],
   event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(4000)') as sql_text,
   event_data.value('(event/action[@name="database_id"]/value)[1]', 'int') as DatabaseID,
   event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(4000)') as client_hostname,
   event_data.value('(event/action[@name="nt_username"]/value)[1]', 'nvarchar(4000)') as nt_username,
   event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(4000)') as username,
   event_data.value('(event/action[@name="tsql_stack"]/value)[1]', 'nvarchar(4000)') as tsql_stack
FROM #ErrorData
ORDER BY [timestamp]

Below is the results from the above query, I ran aT-SQL commands that generated Syntax error  and another was a missing object, Then I had two login failure error messages. Extended Events Captured those information as we can see.

References:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/15/an-xevent-a-day-15-of-31-tracking-ghost-cleanup.aspx

http://www.sqlskills.com/BLOGS/PAUL/category/Extended-Events.aspx

Query to Get database Size;

I was trying to list the size of all databases in a particular server. This query gave me that information.


SELECT SUM(size*8/1024) SizeINMB,db_name(database_id) as DbName
FROM (SELECT distinct size,database_id FROM sys.master_files) F1
GROUP BY database_id

I could also use a powershell script to go against bunch of servers for inventory purposes.
Thanks

Scripting Partitioned Tables From object Explorer;

One of our developer mentioned while scripting INDIVIDUAL partitioned tables from the database the Partition Scheme wasn't included in his script. For example, I created a database and a table on partiton scheme named TestPS. The Partiton Function is TestPF. But When I script that table from object explorer it doesn't include the partition scheme unless we change the scripting option.

But Once I change that option, under Tools-Options->SQL Server ObjectExplorer->Scripting . On the Right under Table and View options-> We need to set Partition Schemes to True So the script includes the partiton Scheme the table was created on.


 CREATE TABLE [dbo].[TestPart]([CurrentTime] [datetime] NOT NULL,[Id] [int] NULL,PRIMARY KEY CLUSTERED ([CurrentTime] ASC)
)ON [TestPS]([CurrentTime]) ON [TestPS]([CurrentTime])
GO


This is the Partition Function and Partition Scheme I used to create the table.
Thanks

USE [TEST]
GO/
****** Object: PartitionFunction [TestPF] Script Date: 02/02/2011 09:17:08 ******/
CREATE PARTITION FUNCTION [TestPF](datetime) AS RANGE RIGHT FOR VALUES (N'2011-01-31T23:59:59.997', N'2011-02-28T23:59:59.997')
GO
USE [TEST]
GO
/****** Object: PartitionScheme [TestPS] Script Date: 02/02/2011 09:16:59 ******/
CREATE PARTITION SCHEME [TestPS] AS PARTITION [TestPF] TO ([PRIMARY], [PRIMARY], [PRIMARY])
GO

/****** Object: Table [dbo].[TestPart] Script Date: 02/02/2011 09:28:26 ******/
CREATE TABLE [dbo].[TestPart]([CurrentTime] [datetime] NOT NULL,[Id] [int] NULL,PRIMARY KEY CLUSTERED ([CurrentTime] ASC))

/****** Object: Table [dbo].[TestPart] Script Date: 02/02/2011 09:16:08 ******/
 
CREATE TABLE [dbo].[TestPart]([CurrentTime] [datetime] NOT NULL,[Id] [int] NULL,PRIMARY KEY CLUSTERED ([CurrentTime] ASC))

Query to find partitioned tables;

--One of our development teams were asking about all tables that were partitioned . The two queries at the bottom gave us that information. I am creating a database with two tables one partitioned and one NON partitioned for testing.


(CREATE DATABASE TEST ON PRIMARY(name='test',filename='d:\test.mdf'),name='test_Data1',filename='d:\test_Data.ndf')LOG ON (name='test_Log',filename='c:\test_Log.ldf')


go


USE TEST


GO

CREATE Partition Function [TestPF](datetime) AS RANGE RIGHT FOR VALUES('1/31/2011 23:59:59.997','2/28/2011 23:59:59.997')


GO

CREATE Partition SCHEME TestPS AS PARTITION TestPF TO ([PRIMARY],[PRIMARY],[PRIMARY])


GO

--Create a Partitioned Table on Partition Scheme TestPS


CREATE TABLE TestPart(CurrentTime datetime,Id Int Primary Key Clustered (CurrentTime))on TestPS(CurrentTime)


GO

--Creating an Aligned Index to the same partitioned scheme

CREATE Index NCIX_ID on TestPart(ID) on TestPS(CurrentTime)


GO

--Create a NON Partitioned Table


CREATE TABLE TESTNonPart (UniqueID Int,FirstName Varchar(100) Primary Key Clustered(UniqueID))


GO


--IF you want clustered index Please use this


SELECT  T.NAME,S.NAME,I.NAME
FROM SYS.DATA_SPACES DS  
INNER JOIN SYS.INDEXES i 
ON DS.DATA_SPACE_ID=I.DATA_SPACE_ID 
INNER JOIN SYS.TABLES T
 ON I.OBJECT_ID=T.OBJECT_ID
 INNER JOIN SYS.SCHEMAS S 
 ON S.SCHEMA_ID=T.SCHEMA_ID
WHERE  ds.name='TestPS'  AND I.Index_id=1

--IF you want clustered index and all other indexes Please remove Index_id=1)


SELECT T.NAME,S.NAME,I.NAME 
FROM SYS.DATA_SPACES DS
 INNER JOIN  SYS.INDEXES i
ON DS.DATA_SPACE_ID=I.DATA_SPACE_ID 
INNER JOIN SYS.TABLES T
 ON I.OBJECT_ID=T.OBJECT_ID 
INNER JOIN SYS.SCHEMAS S 
 ON S.SCHEMA_ID=T.SCHEMA_ID 
WHERE ds.name='TestPS'