Queries that are generating the most IOs
Sources: https://techcommunity.microsoft.com/t5/sql-server/tempdb-monitoring-and-troubleshooting-io-bottleneck/ba-p/383515
Time-out occurred while waiting for buffer latch type 3
Error: Error in procedure dbo.uspXXXX Error in procedure dbo.uspXXXX Time-out occurred while waiting for buffer latch type 3 for page (6:784), database ID 2. [SQLSTATE 42000] (Error 50000) Process Failed, ERROR CODE:0 [SQLSTATE 42000] (Error 50000). The step failed. Cause: IO requests of SQL Server cannot be met by the hardware. This becomes pronounced when... » read more
Why you should not shrink your data files
Now, don’t confuse shrinking the transaction log with shrinking data files. Shrinking the log may be necessary if your log has grown out of control, or as part of a process to remove excessive VLF fragmentation (see Kimberly’s excellent posts on this here and here). However, shrinking the log should be a rare operation and should not be... » read more
How to delete large data of table in SQL without running out of log space
Change your database’s Recovery Mode to SIMPLE and then delete rows in smaller batches using a while loop something like this: Keep in mind that logs could grow a lot if you don’t commit the transaction after each chunk and perform a checkpoint. This is how I would do it and take this article http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes as reference,... » read more
Long running open transactions in a SQL Server database
Query to return long running transactions and cumulative CPU time in MS for open transactions in a SQL Server Database A long running transaction keeps the transaction log active from the virtual log file containing the first log record of the transaction. Truncation cannot occur from that virtual log file onward. It may leads to... » read more
Deleting Large Number of Records from a Database Table
Things to remember when deleting a large number of records from a database table. Consider using TRUNCATE instead of DELETE if truncating the table (removing all records from the table) is ok. Remove records in batches. Make sure there is enough space for the log files. If database is simple, truncate log after every run.... » read more
Add Default Constraint for Table Column
For Existing Table For New Table Definition
SQL GoTo
Using GOTO instead sql query statement.
Restore Database From Backup File
Simple restore database to same server on same disk configuration.
SQL Server Management Studio (SSMS) Keyboard Shortcut
Key Action F5 Execute Ctrl + F Find Ctrl + H Find and Replace Ctrl + F4 Close Query Crtl + K + C Comment Crtl + K + U Uncomment Crtl + Shift + V Cycle Through Clipboard