SQL Server: Timeout When Viewing SQL Server Agent Job History

Too many times when I try to look at the history of a SQL Server Agent Job, I get this error: TITLE: .Net SqlClient Data Provider ------------------------------ Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.2210&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: The wait … Continue reading SQL Server: Timeout When Viewing SQL Server Agent Job History

SQL Server: Fix – Msg 1204, Level 19, State 4 – The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time…

This is an error that does not happen often. In fact, I came across this for the first time when one of my co-workers Inken HOEIJMANS asked me about this. With her permission, I am reproducing her code solution here. Thank you Inken! She was trying to run a MERGE statement using a large source … Continue reading SQL Server: Fix – Msg 1204, Level 19, State 4 – The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time…

SQL Server: Fix: Transaction (Process ID) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction

I have a nightly SQL Agent job that aggregates backup information from system views to power a Power BI dashboard that the DBA's review periodically for database backup failures. It is actually a PowerShell job that runs every night in parallel to collect the data from hundreds of instances (both SQL Server and Oracle). As … Continue reading SQL Server: Fix: Transaction (Process ID) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction

PowerShell & SQL Server: Search & Find Indexes By SchemaName, TableName, IndexName Or ColumnName Across Instances/DB’s

Necessity is the mother of new PowerShell code :-)! Today, I received an email request to get the list of all indexes in a given TableName but the requester did not specify the DatabaseName although the SQL InstanceName was specified. It would be too easy to ask the person for the DBName and script it … Continue reading PowerShell & SQL Server: Search & Find Indexes By SchemaName, TableName, IndexName Or ColumnName Across Instances/DB’s

PowerShell: Drop All SQL Server Tables, Views, SP’s, Functions, Types etc.

I hope this script does not become infamous for the wrong reasons! Please use caution. I had to help a team recreate everything in a database and test their scripts but leave the roles and role grants in place. Basically, this meant that I could have scripted out the permissions and recreated the database but … Continue reading PowerShell: Drop All SQL Server Tables, Views, SP’s, Functions, Types etc.