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
Example Stats: 85 million records = 4 to 11 minutes
When updating a large table using transaction, make sure there is enough space for the log file. This includes … BEGIN TRANSACTION Alter column in a table Create index Tips … Shrink database data and log files beforehand Break up the query into multiple queries Adding more space to the drive where the log file... » read more
Error: The transaction log for database ‘xxxxx’ is full due to ‘ACTIVE_TRANSACTION’. Fix: Backup the transaction log. Set database recovery model from “Full” to “Simple”. If database setup for Always On, make sure Always On is working and synchronizing on all secondary database servers. Backup Transaction Log If you don’t care about the transaction log... » read more
The SQL Server agent checks whether the job is already running before starting a new iteration. If you have long running job and its schedule comes up, it would be skipped until the next interval.
Issue: The transaction log for database ‘tempdb’ is full due to ‘ACTIVE_TRANSACTION’ Fix: Restart server to clear up tempdb. Tempdb database is part of SQL Server System database and gets created every time SQL Server Service starts. Tempdb stores temporary operations (like sorting and grouping data output etc) and tables, it stores lots of information in... » read more