Issue: Unable to connect to SQL Server remotely. Able to connect on the server. This is for a newly installed instance of SQL Server. Fix: Make sure “Named Pipes” and “TCP/IP” protocol is enabled. Go to SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for MSSQLSERVER Make sure Named Pipes and TCP/IP... » read more
I encountered an odd situation where appending OPTION (RECOMPILE) to my query causes it to run in half a second, while omitting it causes the query to take well over five minutes. There are times that using OPTION(RECOMPILE) makes sense. In my experience the only time this is a viable option is when you are using dynamic SQL.... » read more
There are number of general signs in the SQL Execution Plan that indicate potentially bad performance spots on the query. For example, the most expensive operator that has the highest cost, related to the overall query cost, is a good starting point for the query performance troubleshooting. In addition, the fat arrows, that followed with thin ones, indicates... » read more
Note: This query is the most detailed. It breaks out the Used space to Data space and Index space. ReservedSpace = DataSpace + IndexSize + UnusedSpace Note: This run needs to be ran on specific database on the server. Sources:
Issue: SQL Server Agent jobs stuck in running state. No errors and no history. When trying to view history displayed error “Cannot display activity data”. SQL Server Agent Error Logs displays error message… Message: [298] SQLServer Error: 53, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not... » read more
Look for deleted records in transaction log file. There is an undocumented function called fn_dblog which enables you to read data from your transaction log which contains very informative data about things that are happening in your database. The function fn_dblog requires a beginning LSN and ending LSN for a transaction. NULL is the default for this... » read more
NT SERVICE\MSSQLSERVER NT SERVICE\SQLSERVERAGENT)