Create Alert from Script
Script created by SSMS. Severity Alert Error Alert
Create Operator
Create Operator From Script No Pager schedule. Multiple Email Addresses: Add multiple email addresses separate by semi colon “;” E-mail name: address01@test.com;address02@test.com You could create a new operator with the semi colon delimited list of email addresses. Looking at the definition of sysoperators this is good for strings that can fit in nvarchar(100) If you need to exceed... » read more
SQL Server Autogrowth
Auto-growth What exactly are auto-growth events? An auto-growth event is the process by which the SQL Server engine expands the size of a database file when it runs out of space. The amount by which a database file grows is based on the settings that you have for the file growth options for your database.... » read more
Unable to Join Replicas to Available Group – Download configuration timeout. Error 47106
Error: Availability group xxxx is not joined on the secondary replica xxxx. (Microsoft.SqlServer.Management.HadrModel) Program Location: at Microsoft.SqlServer.Management.HadrModel.HadrTask.Perform(IExecutionPolicy policy, CancellationToken token, ScenarioTaskHandler taskDelegate) at Microsoft.SqlServer.Management.Hadr.JoinDatabaseToAvailabilityGroupWorkItem.DoWork() at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run() Failed to join local availability replica to availability group <ag1>. The operation encountered SQL Server error 47106 and has been rolled back. Check the SQL Server error log for... » read more
SQL Server Remote Access Denied
Error: Fix: Make sure Remote Access checkbox is checked in server properties setting. Make sure “TCP/IP” is set to “Enabled”
Availability Group Cluster Type
Cluster type The possible cluster types depend on the SQL Server version and operating system. Choose one from the following list: Windows Server Failover Clustering Use when the availability group is hosted on instances of SQL Server that belong to a Windows Server failover cluster for high availability and disaster recovery. Applies to all supported... » read more
Check Open Transaction
Using DBCC OPENTRAN Here, I show how DBCC OPENTRAN can also be used to quickly narrow down the specific problem below. When working with SSMS, have you run into a situation where you try to expand the list of tables and SQL Server came back with this nice message? Lock request time out period exceeded.... » read more
Lock request time out period exceeded.
Error: Msg 1222, Level 16, State 24, Procedure xxxx, Line 79 [Batch Start Line 778] Lock request time out period exceeded. Resolution: The query is referencing a database in AlwaysOn and the database is corrupted. Fix the AlwaysOn database. Error 2: “lock request time out period exceeded” Error When Trying to See DB Hierarchies. Resolution... » read more
Repair Database with CHECKDB
Repair the database with allow data loss. We will need to set the database into single user mode, run the repair and then set the database back into multi user mode. Check Database Repair Database Check Repair Database Progress DBCC CHECKDB WITH PHYSICAL_ONLY One alternative to minimize contention, is to use the WITH PHYSICAL_ONLY DBCC... » read more
A system assertion check has failed
Error: Msg 3624, Level 20, State 1, Line 4 A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup,... » read more