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
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 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
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
The seed is good if the current identity value is equal to or greater than the current value. Note: If you are using SQL Compare to update table, don’t run the RESEED statement. May have to run the reseed statement multiple times if there are already data rows with the existing seed. Sources: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver15
Database backup and restore time will depend on server and memory allocated. Example 1 Server: 2.10 GHz CPU, 4.0 GB Memory File Size Time to Backup Time to Restore 0.5 GB 10 seconds 2 GB 40 seconds 35 GB 7:50 minutes for Full 283 seconds (4.7 minutes)6:37 minutes for Full 81 GB 13:54 minutes for... » read more
Error: Msg 3203, Level 16, State 1, Line 7 Read on "H:\Backups\MyBackupFile.bak" failed: 1453(Insufficient quota to complete the requested service.) Msg 3013, Level 16, State 1, Line 7 RESTORE DATABASE is terminating abnormally. Fix 1: Remove “maxtransfersize” from restore statement. Fix 2: Restrict the max server memory setting of the Sql server to allow other... » read more
Query: Execution Plan suggest Missing Index… Missing Index (Impact 93.0): CREATE NONCLUSTERED INDEX [<name>] ON [dbo].[tbTable01] ([Col01], [Col02], [Col03], [Col04]) Index: Good Better Include Col01 and Col03 to eliminate key lookup step. Note: 84 records = 12 minutes
SET TRANSACTION ISOLATION LEVEL READ COMMITTED will cause deadlocks if the table is very large without indexes. Having the query perform a table/index scan with the read committed option practically “locks” the table. The impact can be minimized if the procedures access the data using the correct index so it does a table/index seek, therefore... » read more
Querying SQL Server Extended Events for Deadlock history. Extended events capture a lot of data from the system and you should explore that as well. There have a lot of useful detailed information which is already being captured. Please have a look at this event on MSDN here. We are only concerned about the deadlocks.... » read more