sp_WhoIsActive 

--Tempdb contention and blocking
EXEC sp_WhoisActive
@sort_order= '[tempdb_current] DESC',
@get_plans= 1,
@Output_Column_List= '[start_time][session_id][sql_text][query_plan][wait_info][temp%]';
GO
EXEC sp_Whoisactive
@find_block_leaders=1,
@sort_order = '[blocked_session_count] DESC',
@get_plans= 1,
@Get_additional_info=1,
@Output_Column_list = '[start_Time][session_id][sql_text][query_plan][wait_info][block%][additional_info]';
go
EXEC sp_Whoisactive
@get_avg_time = 1,
@Get_outer_Command = 1,
@Get_plans = 1

Comments

Post a Comment

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more

Always On FailOver Events

Image
We can run below PowerShell and SQL Server commands when we want to find ou when the failover has happened. cls   Get-winEvent -ComputerName   ListerNameHere   -filterHashTable @{logname = 'Microsoft-Windows-FailoverClustering/Operational' ; id = 1641 } |   Where-Object { $_ . Timecreated -like '*05/09/2020*' } | Format-Table -AutoSize -Wrap <# 35201-->timeout alert run this by changing -LogName to System 41-->41 is power shutdown run this in system #> clear Get-EventLog -LogName Application -After ( Get-Date ) . AddHours( -48 ) | Where-Object { $_ . EventID -in ( 1100 , 1074 , 35201,41,1561,7024,1135,7032,1146 )} | Format-Table -AutoSize -Wrap cls  Get-EventLog ` -LogName System |Where-Object{$_.EventID -in (1054,1053,1055,1138,1141,1142,5140,5142,1559,1560,1563,1588,1068,5144,1562,5143,5168,1564,1177)}|Format-Table -AutoSize -Wrap EXEC sp_readerrorlog 0,1,'lease worker' EXEC sp_readerrorlog 0,1,'
Read more

Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25.

Image
I got the below error in one of my production environments one day after patching. Below are the error message. You are unable to bring the database engine and agent online, and you will face the below error messages in the error log of SQL Server and eventvwr of Windows server. Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25. This is a serious error condition that might interfere with regular operation and the database will be taken offline. If the error happened during the upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion. This error is misleading us don't go for rebuild master database You can also find the same information in the error log SQL server. Worka
Read more