SQL Server – How know AlwaysON Failover Time using TSQL

Do you want to know when AlwaysOn failover happened? This is a script which I got from internet and gives good information about the last failovers.


SELECT @FileName = target_data.value('(EventFileTarget/File/@name)[1]', 'nvarchar(4000)')
SELECT CAST(target_data AS XML) target_data
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t
ON s.address = t.event_session_address
WHERE s.name = N'AlwaysOn_health'
) ft;
WITH base
AS (
SELECT XEData.value('(event/@timestamp)[1]', 'datetime2(3)') AS event_timestamp
,XEData.value('(event/data/text)[1]', 'VARCHAR(255)') AS previous_state
,XEData.value('(event/data/text)[2]', 'VARCHAR(255)') AS current_state
SELECT CAST(event_data AS XML) XEData
FROM sys.fn_xe_file_target_read_file(@FileName, NULL, NULL, NULL)
WHERE object_name = 'availability_replica_state_change'
) event_data
JOIN sys.availability_replicas ar
ON ar.replica_id = XEData.value('(event/data/value)[5]', 'VARCHAR(255)')
SELECT DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), event_timestamp) AS event_timestamp
FROM base
ORDER BY event_timestamp DESC;

SQL Server – Capacity Planning at Intial stage..!

What is capacity planning?

Capacity planning is used to determine the ever changing needs of an application pro actively. In words of SQL Server, capacity planning of SQL Server is used to determine the capacity of SQL Server , which is used to determine the forthcoming needs of  SQL Server  corresponding to the  growth of business , data, users etc.

Capacity Planning while intial deployment?

Recently when one of my friend Santhosh Balakrishnan  who is my colleague in Allianz Managed Operations and Services , opens a question to me regarding the intial capacity planning of SQL Server. The question was “How we will Configure  or do the Capacity planning  during the intial setup?”.  I think question was wonderful, because it makes sense.

The answer to Santhosh question   from my point of view is we can collect the requirements from the business for example no of users  connect to the system in next 5 or n  years , dump data etc and do a load testing for SQL Server. Form the results of Load testing we can will be able to estimate a round figure of what we need for the SQL Server for running in the tested Work load.

Capacity Planning for SQL Server suggestions for Microsoft

Here is the link of the article by Microsoft regarding the SQL Server Capacity Planning. So I am not going write in detail about the SQL Server Capacity Planning. I will try include some scripts for finding the capacity planning in the coming posts.


I like to dedicate this post  to my dear friend Santhosh Balakrishnan.