Capturing errors thru EXTENDED EVENTS

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

Comments

Post a Comment

Popular posts from this blog

Using PowerShell adding list of servers in CMS in SQL Server

  $path = get-location $servers = @(get-content "$path\servers.txt") { $q = use [msdb] insert into [dbo].[sysmanagement_shared_registered_servers_internal] (server_group_id,name,server_name,description,server_type) values ('45','$server','$server','','0'))" }  Invoke-Sqlcmd -serverInstance SQL2019 | -database "master" -query $q Note: Value 45 is not a default value it may vary according to server group we created in CMS. once we create a group in CMS this will save with on group id, we can see group id in "sysmanagement_shared_server_groups_internal"
Read more

Migrating SQL Server with minimal downtime from On premise to Azure using DAG (Distributed Availability Group)

Image
We have to migrate the SQL server from 2016 to 2019 with minimal downtime by configuring DAG. See the below process how to migrate with minimal downtime I have a single standalone server having version SQL 2016 and need to migrate the server to 2019 version. The user is not giving downtime to migrate the server having multiple database more than 200GB each. We have tried on lower version to migrate using DAG with minimal downtime. 1. We have to create different Cluster on both servers. 2. If we are migrating the server on Azure cloud then distributed availability groups are not allowed for DNN, SQL should not be DNN (DNN Listener). creating windows cluster on both server, select the server those need to configure always on and do validations and give cluster name and IP     if  we are creating the WFCS on Azure cloud there will be no option to provide cluster IP, it will take ip of one of the server those are in always on. Hence we have to use script to create cluster usi...
Read more

Database Growth in % daily and Incremental

 USE [DailyDatabaseGrowthReport] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[DailyDatabaseGrowthReport]  AS BEGIN     SET NOCOUNT ON;     -- Check and create the table only if it does not exist     IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'DBSizeDailyReport')     BEGIN         CREATE TABLE [dbo].[DBSizeDailyReport](             [ServerName] nvarchar(100) NOT NULL,             [DbName] nvarchar(100) NOT NULL,             [SizeInMB] int NOT NULL,             [WeekID] int NOT NULL,             [Date] datetime NOT NULL,             [DayWisePercentageGrowth] decimal(18, 2),             [IncrementalPercentage] decimal(18, 2)         );      ...
Read more