Home » 2016 » December

Monthly Archives: December 2016


When you turn on this option, it will display the number of milliseconds required to parse, compile, and execute each statement. By default, this option is disabled. SET STATISTICS TIME ON; GO SELECT * FROM [HUMANRESOURCES].[EMPLOYEE] Output: – SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms. (290 … Continue reading

How to use DTA (Database Tuning Advisor) with Plan Cache option?

SQL Server 2012 comes up with new workload option in DTA (Database Tuning Advisor). In addition, on existing options File or Table, Microsoft gives option to fetch workload from plan cache of SQL Server. In this case, the DTA will select the top 1,000 events from the plan cache based on total elapsed time of … Continue reading

SQL Server 20082008 R22012 || Remove Node from failover cluster

Steps to remove node from failover cluster for SQL Server 2008 onwards :- Note :- a) Same process will be used to remove node from failover cluster b) Same process will be used when you left with only one node, this will clear cluster group as well c) Old process of uninstallation from Add and … Continue reading

MSDB log file grown large – SLEEP_MSDBSTARTUP waittype

Issue: MSDB database log file is getting full very rapidly and became very huge. Currently, T-Log file is of 5GB whereas Data file is only 1GB for MSDB on server. Findings: We found system owned open transaction in MSDB which is causing issue. Transaction information for database ‘msdb’. Oldest active transaction: SPID (server process ID): … Continue reading

%d bloggers like this: