Tables ordered by Size

Use below query to find the tables in a database based on Size

SELECT O.Name AS [Table], 
       SUM(A.Total_Pages) AS [Reserved Pages],
       SUM(A.Used_Pages) AS [Used Pages],
       (SUM(A.Total_Pages) * 8 / 1024) AS [Reserved (MB)],
       (SUM(A.Used_Pages) * 8 / 1024) AS [Used (MB)],
       SUM(CASE WHEN A.Type <> 1 THEN A.Used_Pages
                WHEN P.Index_ID < 2 THEN A.Data_Pages
                ELSE 0
                END) AS Pages,
       SUM(CASE WHEN ( P.Index_ID < 2 )
                AND ( A.Type = 1 ) THEN P.Rows
                ELSE 0
                END) AS [Rows]
FROM Sys.Objects AS O
       JOIN Sys.Partitions AS P ON P.Object_ID = O.Object_ID
       JOIN Sys.Allocation_Units A ON P.Partition_ID = A.Container_ID
WHERE O.Type = 'U'
GROUP BY O.Name
ORDER BY [Used Pages] DESC

Convert Seconds to HH:MM:SS

Use below function to convert Seconds to HH:MM:SS

Create Function dbo.GetHHMMSS(@InputSecs BIGINT) RETURNS nVarChar(Max)
Begin

Declare @HHMMSS nVarChar(Max)

If @InputSecs < 60 and @InputSecs<>0
Begin
SET @HHMMSS = '0:01:00'
End
Else
Begin

SET @HHMMSS = ISNULL(CAST(@InputSecs/3600 AS nVarChar(MAX)) + ':' + RIGHT('0' + CAST(ROUND(CAST((@InputSecs % 3600.0) / 60 AS float),0) AS nVarChar(MAX)),2) + ':' + RIGHT('0' + CAST(ROUND(CAST((@InputSecs % 60.0) AS float),0) AS nVarChar(MAX)),2) ,'0:00:00')

End

Return @HHMMSS
End

Example:

SELECT dbo.GetHHMMSS(500) 

You can also use below query to get above result:

DECLARE @SECONDS INT = 5000

SELECT CONVERT(CHAR(8),DATEADD(second,@SECONDS,0),108) 'TOS HHMMSS'

RESTORE DATABASE is terminating abnormally

Sometime when you try to restore a backup over an existing database, the following error may appear:

RESTORE DATABASE is terminating abnormally. The tail of the log for the database "DB_NAME" has not been backed up. 
Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose.

First check if the database is in full recovery model. If it is, than this message warns you that if you restore the database than there are some transactions that will be lost.
If this is not a problem than you can change the recovery model from full to simple or you can do the restore with REPLACE option. After this modification, the restore will complete successfully.

Query to get Last restored database in SQL Server

You can use below query to get the list of all database restored in SQL Server:

SELECT [RS].[Destination_Database_Name] AS [Destination DB Name],
[RS].[Restore_Date] AS [Restore Date],
[BS].[Backup_Start_Date] AS [Backup Start Date],
[BS].[Backup_Finish_Date] AS [Backup End Date],
[BS].[Database_Name] as [Source Database Name],
[BMF].[Physical_Device_Name] AS [Backup File Used For Restore]
FROM msdb..RestoreHistory RS
INNER JOIN msdb..BackupSet BS
ON [RS].[Backup_Set_ID] = [BS].[Backup_Set_ID]
INNER JOIN msdb..BackupMediaFamily BMF
ON [BS].[Media_Set_ID] = [BMF].[Media_Set_ID]
ORDER BY [RS].[Restore_Date] DESC

compare current row with previous / next row in a table

There must be a numeric identity / Unique column for comparing current with next / previous Rows in a table. 

Lets say you want to compare both Previous and next rows with current row in ItemMaster Table with unique numeric columns ItemID:

Select Cur.ItemName [Curent Value],
IsNull(Pre.ItemName,0) [Previous Value],
IsNull(Nex.ItemName,0) [Next Value]
From ItemMaster AS Cur
LEFT JOIN ItemMaster AS Pre On Pre.ItemID = Cur.ItemID - 1
LEFT JOIN ItemMaster AS Nex On Nex.ItemID = Cur.ItemID + 1

Change of safety level not allowed.

Today I was trying to change the transaction safety level in a database mirroring session from high-safety mode (safety full) to high-performance mode using T-SQL and I received below error message:

Msg 1473, Level 16, State 6, Line 1

This SQL Server edition does not allow changing the safety level. ALTER DATABASE command failed.

The reason for above error is: 
High-Performance Mode (Asynchronous Database Mirroring) is only supported on Enterprise Edition or Datacenter Edition.

There is no default instance of SQL Server on the computer

Sometime when trying to browse the SSIS packages from SQL Server Management Studio the following error can come:

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 Books Online.

This error is coming when the default instance runs on a different port than the default one (1433) or when you have only named SQL instances on the machine. 

To solve the error you had to make some changes in the MsDtsSrvr.ini.xml file:

SQL 2005 location:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

SQL 2008 location:
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

SQL 2012 location:
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml

Modify the MsDtsSrvr.ini.xml file to point to the your named instance or to the default instance with the right port, and then restart the SSIS service:

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB</Name>

      <ServerName>SERVER_NAME\INSTANCE_NAME</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

OR:

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB</Name>

      <ServerName>SERVER_NAME,PORT_NUMBER</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

Find outdated statistics in SQL Server

SQL server uses the STATISTICS to find the appropriate Query plan.

if you are experiencing performance issues for your queries than it may be due to outdated statistics. An outdated statistic can make the sql server choose a wrong plan, use the following query to find outdated stats:

SELECT OBJECT_NAME(ID) AS [Table], SI.Name, STATS_DATE(ID, IndID) AS [Date Last Update], RowModCtr [Rows Modified Since Last Update]

FROM SYS.Tables AS ST INNER JOIN SYS.SYSIndexes AS SI ON SI.[ID] = ST.[object_ID]

INNER JOIN SYS.Schemas AS SS ON ST.[schema_ID] = SS.[Schema_ID]

WHERE STATS_DATE(ID, IndID) <= DATEADD(DAY,-1,GETDATE()) AND RowModCtr > 10

ORDER BY [RowModCtr] DESC

Once you identified the outdated stats, you can use the "Update Statistics" to do the update.

Query to find out parameters for Stored Procedure

Use below query to find out parameters for Stored Procedure

SELECT Schema_Name(Schema_ID) AS Schema_Name,
 O.Type_Desc,
 O.Name AS Object_Name, 
 P.Parameter_ID,
 P.Name AS Parameter_Name,
 Type_Name(P.User_Type_ID) AS Parameter_Type,
 P.MAX_Length,
 P.Precision,
 P.Scale,
 P.IS_Output
FROM SYS.Parameters AS P INNER JOIN SYS.Objects AS O 
ON O.Object_ID = P.Object_ID 
WHERE O.Object_ID = Object_ID('<Stored Procedure>') 
ORDER BY Schema_Name, P.Parameter_ID;

Replace <Stored Procedure> with Original Stored Procedure name.

Selecting a random number for each row

Sometime you may want to return a random number for each row. You can not do this directly selecting a RAND() function in query:

SELECT Rand() AS Random_Number, Name, Code from MyTable.

The above query will return the same random number for each row.

But you want to select different random number for each row. Follow below steps to do so:

create view rand_view
as
select rand( ) as random_value
go

create function New_rand()
returns float
as
begin
declare @f float
set @f = (select random_value from rand_view)
return @f
end

SELECT dbo.New_rand() wrapped_rand, Name, Code FROM MyTable

Number of words in a string

In SQL Server there is not direct function to calculate the number of words in a string. 
But we can calculate the number of words in following way:

Option 1 :
CREATE FUNCTION dbo.UDFWordCount(@Text VARCHAR(8000))
RETURNS int
as
/*
SELECT dbo.UDFWordCount ('hello   world')
*/
BEGIN
     
    DECLARE @iCtr int, @iCtx INT, @Words int
    SELECT @iCtr = 1, @Words = 0 

    WHILE @iCtr <= DATALENGTH(@Text)
    BEGIN
        SELECT @iCtx = CHARINDEX(' ', @Text, @iCtr)
        if @iCtx = 0
        BEGIN
            SELECT @iCtx = DATALENGTH(@Text) + 1
        END
        IF SUBSTRING(@Text, @iCtr, @iCtx - @iCtr) <> ' '
              SELECT @Words = @Words + 1 
        SELECT @iCtr = @iCtx + 1
    END
    RETURN(@Words)
END
GO

SELECT dbo.UDFWordCount ('Microsoft SQL Server2008')
SELECT dbo.UDFWordCount ('Microsoft  SQL  Server  2008')

Option 2 :
DECLARE @strTemp VARCHAR(4000)
SELECT @strTemp = 'SQL Server 2005'
SELECT LEN(@strTemp) - LEN(REPLACE(@strTemp, ' ', '')) + 1

But in this option if word are separated by a single space than it will give you the correct result, If there is more than single space between words than it will give you  wrong result.

Query to find the Index with size

Use below query to find the Index with size in a table:

SELECT
OBJECT_Name(I.OBJECT_ID) AS TableName,
I.index_ID AS IndexID,
I.Name AS IndexName,
8 * SUM(A.Used_Pages)/1024 AS 'IndexSize in KB'
FROM sys.Indexes AS I
JOIN sys.Partitions AS p ON p.OBJECT_ID = I.OBJECT_ID AND p.index_ID = I.index_ID
JOIN sys.Allocation_Units AS A ON A.Container_ID = P.Partition_ID
WHERE I.Object_ID = Object_ID ('<Table_Name>')
GROUP BY I.OBJECT_ID,I.index_ID,I.Name

Replace <Table_Name> with Table Name

SQL Server could not spawn FRunCM thread error

Today I had to change the domain account on some Server. After I had changed the account the SQL server was not starting and showing below error:

Server Error: 17190, Severity: 16, State: 1.

2014-07-17 11:42:06.11 Server Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893802.

2014-07-17 11:42:06.11 Server Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.

2014-07-17 11:42:06.11 Server Error: 17182, Severity: 16, State: 1.

2014-07-17 11:42:06.11 Server TDSSNIClient initialization failed with error 0×80092004, status code 0×80. Reason: Unable to initialize SSL support. Cannot find object or property.

2014-07-17 11:42:06.11 Server Error: 17182, Severity: 16, State: 1.

2014-07-17 11:42:06.11 Server TDSSNIClient initialization failed with error 0×80092004, status code 0×1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

2014-07-17 11:42:06.11 Server Error: 17826, Severity: 18, State: 3.

2014-07-17 11:42:06.11 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2014-07-17 11:42:06.11 Server Error: 17120, Severity: 16, State: 1.

2014-07-17 11:42:06.11 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

After trying all trial and run I notice that when logging on with existing profile on the box there was an error. Something about that the profile couldn't be created and a temporary profile would be used instead.

In the end I had to delete the user profile and than start SQL Server and now SQL server is started properly.

I had followed the below steps:

Right click on computer, select Properties
Click on Advanced System Settings
Select the Advanced Tab
On the user profiles section click on Settings

Select the profile and hit Delete

If you try to log in as that user, the profile will be recreated.

Now when I tried to start SQL Server again, the errors were not there and SQL Server started up without a problem.

Script to find the No of Reads / Writes on Database files

Use below script to find the No of Reads / Writes on Database files

SELECT DB_Name(MF.Database_ID) AS [DB Name],
       MF.Physical_Name AS [Physical File Name],
       Num_Of_Bytes_Read,
       Num_Of_Reads,
       IO_Stall_Read_MS,
       Num_Of_Bytes_Written,
       Num_Of_Writes,
       IO_Stall_Write_MS,
       IO_Stall,
       Size_On_Disk_Bytes
FROM sys.dm_IO_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS MF ON MF.Database_ID = divfs.Database_ID
AND MF.File_ID = divfs.File_ID
                                  

Script to track Database Growth

Use below script to track database growth over a period of time:

WITH C AS (
SELECT 
RN = ROW_NUMBER() OVER( ORDER BY Backup_Start_Date DESC),
BackupDate = CONVERT(VARCHAR(10), Backup_Start_Date, 111), 
SizeInMB = FLOOR(Backup_Size/1024000) 
FROM msdb.dbo.BackupSet 
WHERE Database_Name = DB_NAME() -- put your databaseName here
AND Type = 'd'
) SELECT C.* , Diff = SizeInMB - (SELECT SizeInMB FROM C C2 WHERE RN = C.RN + 1) FROM C ORDER BY 1 ASC

Query to return database files and free space

Use below query to get database files and free space:

WITH C AS (
SELECT G.Name AS [FileGroupName],
       S.Type_Desc,
       S.Physical_Name AS [FileName],
       S.Size * CONVERT(FLOAT, 8) AS [Size],
       CAST(CASE S.Type
               WHEN 2 THEN 0
               ELSE CAST(FILEPROPERTY(S.Name, 'SpaceUsed') AS FLOAT) * CONVERT(FLOAT, 8)
             END AS FLOAT) AS [UsedSpace]
FROM Sys.filegroups AS G
INNER JOIN Sys.Master_Files AS S ON (( S.Type = 2 OR S.Type = 0)
                                      AND S.database_ID = DB_ID()
                                      AND (S.drop_lsn IS NULL))
                                    AND (S.Data_Space_ID = G.Data_Space_ID)
)
SELECT *, [Size] - [UsedSpace] AS RemainingSpace FROM C

SQL Server Cannot resolve collation conflict for equal to operation

Few days ago I had copied some tables, Stored Procedures, Views etc  from one database to a new database. 

Now whenever I execute a Stored procedure, I got below error:

"Cannot resolve collation conflict for equal to operation."

Thre error clearly indicates that the collation types for the columns being joined in the sql statement is different.

I had overcome this error by telling the columns to use the databases default collation on either side of the join fixed the problem - e.g.

SELECT table1.*, table2.* FROM table1
INNER JOIN table2 ON table1.column1 COLLATE DATABASE_DEFAULT = table2.column1 COLLATE DATABASE_DEFAULT

Query to find Computed columns

Use below query to find all computed columns in a database:

SELECT O.NAME AS [Table], C.NAME AS [Column], T.TEXT AS [formula]
FROM SysColumns AS C, SysObjects O, SysComments T
WHERE C.ID = O.ID AND O.ID = T.ID AND C.ColID = T.Number AND Iscomputed = 1 AND O.type = 'U'

SQL Server blocked access to statement OPENROWSET / OPENDATASOURCE

Sometime you may get below error when you execute query using OPENROWSET statement:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

This error is coming because Ad Hoc Distributed Queries configuration parameter is disabled. To enable Ad Hoc Distributed Queries you had to execute below commands:

sp_configure 'show advanced options',1  
reconfigure 
sp_configure 'Ad Hoc Distributed Queries',1  
reconfigure 

After executing above commands your problem will be solved.

You can do above changes in following ways also:

Right Click in SSMS on the SQL Server instance from the object explorer pane and choose Facets from the drop down menu.

In the facets window change the Facet to Surface Area Confuguration and set the AdHocRemoteQueriesEnabled to True

Find last execution time of Stored Procedure

SQL Server stores the execution plan of statements before executing any statement.In order to find the last execution we just need to query the SYS.dm_Exec_query_stats DMV.

SELECT ST.Text AS SQL, QS.creation_time,QS.lASt_Execution_time,QP.dbID,QP.ObjectID
FROM SYS.dm_Exec_query_stats AS QS
CROSS APPLY SYS.dm_Exec_sql_Text(QS.sql_handle) AS ST
CROSS APPLY SYS.dm_Exec_Text_query_plan(QS.plan_handle,DEFAULT,DEFAULT) AS QP WHERE ST.Text like '%<SP_Name>%'

Find all tables without Triggers in SQL Server

Sometime you need to know all tables without trigger before adding, changing  or dropping column in a table.  Here are two ways to know that:

SELECT S1.Name FROM SysObjects S1 LEFT JOIN SysObjects S2 ON
S1.ID =S2.Parent_Obj
AND S2.XType = 'TR'
WHERE S2.Name IS NULL
AND S1.XType = 'U'
ORDER BY S1.Name

SELECT T.TABLE_Name FROM INFORMATION_SCHEMA.TABLES T
LEFT JOIN (SELECT OBJECT_Name(o.Parent_Obj) AS TableName
FROM SysObjects O
WHERE OBJECTPROPERTY(O.[ID], 'IsTrigger') = 1
) TR ON T.TABLE_Name= TR.TableName
WHERE TR.TableName IS NULL
AND TABLE_TYPE ='BASE TABLE'
ORDER BY T.TABLE_Name

Backup History

Here is a useful script to get the backup History for all databases in SQL Server:

SELECT S.Server_Name, S.Recovery_Model, S.Database_Name, M.Physical_Device_Name,
    CASE S.[Type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType,
    S.Backup_Start_Date, S.Backup_finish_Date,
    CAST(DATEDIFF(second, S.Backup_Start_Date,S.Backup_Finish_Date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeElapsed,
    CAST(CAST(S.Backup_Size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Backup_Size,
    CAST(S.Dirst_LSN AS VARCHAR(50)) AS First_LSN,
    CAST(S.Last_LSN AS VARCHAR(50)) AS Last_LSN
FROM msdb.dbo.BackupMediaFamily AS M 
INNER JOIN msdb.dbo.BackupSet AS S ON M.Media_Set_ID = S.Media_Set_ID
ORDER BY S.Backup_Start_Date DESC

Unable to remove SQL Server 2008 after removing from cluster

I was unable to uninstall SQL Server after it was removed from Failover Cluster Manager. It was logging below error in application log:

Product: Microsoft SQL Server 2008 Database Engine Services — Error 25012. There was an error attempting to remove the configuration of the product which prevents any other action from occuring. The current configuration of the product is being cancelled as a result.

I had followed below steps to uninstall SQL Server:

1. Open Registry Editor (Start -> run -> type regedit)

2. Navigate to the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\ClusterState\SQL_Engine_Core_Inst

3. Change the value of this “SQL_Engine_Core_Inst” key from 1 to 0.

4. Uninstall as normal through Programs and Features

Finding a Table in all databases on Server

dbo.sp_MSforeachdb procedure will execute the attached string as a command against every database on the server. Any question mark(?) , within the string will be replaced by every database name.
We can use the Information_Schema view Tables to see the tables list in the current DB:

Select * From DB1.Information_Schema.Tables

So by combining both SP and View we can search for a Table in all databases in Server

EXEC dbo.sp_MSforeachdb 'SELECT ''?'', * FROM [?].INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE ''Items'' '

Find Table with Max. rows & Size

You can use the below query to get all tables with No of rows and size. User required View Database State permission on the Server to use below DMV

SELECT SN.Name AS [Schema Name], ST.Name AS [Table Name], SI.Name AS [Index Name], PS.Reserved_Page_Count * 8 AS [Total Space Consumed (in KB)], PS.Used_Page_Count * 8 AS [Used Space (in KB)], (PS.Reserved_Page_Count - PS.Used_Page_Count) * 8 AS [Free Space (in KC)], CASE WHEN PS.Index_ID IN (0, 1) THEN PS.Row_Count ELSE NULL END AS [Row Count]
FROM SYS.DM_DB_PARTITION_STATS AS PS INNER JOIN SYS.OBJECTS AS ST ON ST.OBJECT_ID = PS.OBJECT_ID INNER JOIN SYS.SCHEMAS AS SN ON SN.SCHEMA_ID = ST.SCHEMA_ID LEFT JOIN SYS.INDEXES AS SI ON SI.OBJECT_ID = PS.OBJECT_ID AND SI.INDEX_ID = PS.INDEX_ID
WHERE ST.IS_MS_SHIPPED = 0
ORDER BY [Total Space Consumed (in KB)] DESC 

Session Details

Use below queries to get the session details

SELECT S.Session_ID AS [Session ID], S.Status, S.Host_Name AS [Host], C.Client_Net_Address AS [Client Net Address], S.Login_Time AS [Login Time], CASE WHEN S.Login_Name = S.Original_Login_Name THEN S.Login_Name ELSE S.Login_Name + ' (' +S.Original_Login_Name + ')' end AS [Login Name], S.Program_Name AS [Program], ST.Text AS [Query Text], 
CASE S.Transaction_Isolation_Level WHEN 0 THEN 'UNSPECIFIED'  WHEN 1 THEN 'READUNCOMITTED' WHEN 2 THEN 'READCOMMITTED' WHEN 3 THEN 'REPEATABLE' WHEN 4 THEN 'SERIALIZABLE' WHEN 5 THEN 'SNAPSHOT' ELSE CAST(S.Transaction_Isolation_Level AS VARCHAR(32)) end AS [Transaction Isolation Level],
S.Memory_Usage AS [Memory Usage Pages], S.CPU_Time AS [CPU Time MS], S.Row_count AS [Row Count], S.Last_Request_Start_Time, S.Last_Request_end_Time, S.Prev_Error, C.Net_Transport, C.Protocol_Type, S.Language, S.Date_Format
FROM SYS.DM_Exec_Sessions AS S FULL OUTER JOIN SYS.DM_Exec_Connections C ON C.Session_ID = S.Session_ID CROSS APPLY SYS.DM_Exec_SQL_Text(C.MOST_RECENT_SQL_HANDLE) ST
WHERE S.Session_ID IS NULL OR S.Session_ID > 50 
ORDER BY S.Session_ID

Move TempDB files to different location

Use below query to move TempDB data and log files to some other location/drive:

ALTER DATABASE Database_Name MODIFY FILE ( NAME = Logical_Name, FILENAME = 'New_Location\File_Name' )

In above query Logical_Name you can get from below query:

SELECT db_name(dbid) AS DataBaseName, * FROM sys.sysaltfiles WHERE dbid = db_id('TempDB')

AND

New_Location\File_Name is new path and file file for TempDB database.

After moving data and log files you had to restart SQL Server .

Stop logging successful backup of databases in SQL Server error log

If you want to stop all database successful backups message in error log than use below DBCC command. This will not record successful backup message in error log but store all other message 

DBCC TRACEON(3226,-1)

Now if you want to record both success and failure backup databases entry in SQL Server error log, than run below DBCC command. 
By default SQL Server log you can see both success and failure backup entries.

DBCC TRACEOFF(3226,-1)

Find most expensive cached queries

Below queries returns aggregate performance statistics for cached query plans.  The result contains one row per query within the cached plan. If the query plan is removed from the cache than that query is excluded from the output. sys.dm_exec_query_stats is a very useful DMV to get cached query details for performance & server load analysis.

Execution count of each query

SELECT QT.DBID, DB_NAME(QT.DBID) AS [Database Name], QT.ObjectID, QT.Text AS [Query Text], QS.Execution_Count AS [Execution Count], QS.Total_Elapsed_Time AS [Total Elapsed Time], QS.Max_Elapsed_Time AS [Max Elapsed Time], QS.Min_Elapsed_Time AS [Min Elapsed Time], QS.Last_Elapsed_Time AS [Last Elapsed Time] FROM SYS.DM_EXEC_QUERY_STATS AS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT ORDER BY QS.Execution_Count DESC

Top 5 I/O intensive Queries

SELECT TOP 5 Total_Logical_Reads [Total Logical Read], Total_Logical_Writes AS [Total Logical Write], Total_Logical_Reads + Total_Logical_Writes AS [IO Total], Execution_Count AS [Execution Count], QT.Text AS [Query Text], DB_NAME(QT.DBID) AS [Databaes Name], QT.ObjectID AS [Object IO] FROM SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT WHERE Total_Logical_Reads + Total_Logical_Writes > 0 ORDER BY [IO Total] DESC

Top 5 CPU consuming Queries 

SELECT TOP 5 QT.TEXT AS [Statement Text], QS.TOTAL_WORKER_TIME AS [CPU Time], QP.QUERY_PLAN AS [Query Plan] FROM SYS.DM_EXEC_QUERY_STATS AS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP ORDER BY TOTAL_WORKER_TIME DESC

Top 5 Average CPU consuming Queries 

SELECT TOP 5 TOTAL_WORKER_TIME AS [Total Worker Time], EXECUTION_COUNT AS [Execution Count], TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU Time], QT.TEXT AS [Query Text] FROM SYS.DM_EXEC_QUERY_STATS AS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT ORDER BY QS.TOTAL_WORKER_TIME DESC

Installation Error Codes

• 2 – The system cannot find the file specified.
• 5 – Access denied.
• 52 – You were not connected because a duplicate name exists on the network. Make sure there is not a duplicate name in DNS and that 2 machines don’t have the same IP in DNS.
• 53 – Unable to locate – http://support.microsoft.com/kb/920852 – cannot connect to admin$ – Computer Browser not started – add File/print sharing to Exceptions in Firewall – turn file and print on.
• 58 – The specified server cannot perform The requested operation
• 64 – The specified network name is no longer available. Source: Windows
• 67 – network name cannot be found. 
• 86 – network password is not correct? Machine Name <> resolved name.
• 112 – Not enough disk space
• 1003 – Cannot complete this function.
• 1053 – The service did not respond to the start or control request in a timely fashion.
• 1068 – The dependency service or group failed to start
• 1130 – Not enough server storage is available to process this command. Source: Windows
• 1203 – The network path was either typed incorrectly, does not exist, or the network provider is not currently available. Please try retyping the path or contact your network administrator.
• 1208 – An extended error has occurred. Source: Windows
• 1396 – Logon Failure: The target account name is incorrect. (NBTSTAT -a reverse lookup, duplicate IP address)
• 1450 – Insufficient system resources exist to complete the requested service. Source: Windows
• 2147749889 – Generic WMI failure (Broken WMI)
• 2147749890 – not found – Source: Windows Management (WMI) – try repair WMI
• 2147749904 – Invalid class – Source: Windows Management (WMI)
• 2147749908 – Initialization failure – Source: Windows Management (WMI)
• 2147942405 – Access is Denied (Firewall rule? / MacAfee-HIPS?)
• 2147944122 – The RPC server is unavailable. (Dcom is miss-configured for security . http://support.microsoft.com/kb/899965 )
• 2148007941 – Server Execution Failed

Details of current running query

Use below query to check the details of current running Query on SQL Server.

SELECT R.Session_ID, R.Request_ID AS [Session Request ID], CASE WHEN S.Login_Name = S.Original_Login_Name THEN S.Login_Name ELSE S.Login_Name + ' (' + S.Original_Login_Name + ')' END AS [Login Name], S.Program_Name AS [Program Name], DB_Name(R.Database_ID) AS [Database Name], R.Status, S.Host_Name AS [Host Name], C.Client_Net_Address AS [Client Net Address], R.Command, ST.Text AS [Query Text], QP.Query_Plan AS [XML Query Plan], R.Last_Wait_Type AS [Last Wait Type], R.Wait_Type AS [Current Wait Type], R.Blocking_Session_ID AS [Blocking Session ID], R.Row_Count AS [Row Count], 
R.Granted_Query_Memory AS [Granted Query Memory], R.Open_Transaction_Count AS [Open Transaction Count], R.User_ID AS [User ID], R.Percent_Complete AS [% Complete], R.Start_Time AS [Start Time], R.CPU_Time AS [CPU Time], R.Estimated_Completion_Time AS [Estimated Completion Time], CASE R.Transaction_Isolation_Level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' ELSE CAST(R.Transaction_Isolation_Level AS VARCHAR(32)) END AS [Transaction Isolation Level]
FROM SYS.DM_EXEC_REQUESTS AS R LEFT OUTER JOIN SYS.DM_EXEC_SessionS AS S ON S.Session_ID = R.Session_ID LEFT OUTER JOIN SYS.DM_EXEC_ConnectionS AS C ON C.Connection_ID = R.Connection_ID CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_Handle) AS ST CROSS APPLY SYS.DM_EXEC_Query_Plan(R.Plan_Handle) AS QP 
WHERE R.STATUS NOT IN ('Background','Sleeping')

Identity column information

Use below query to get information for all identity columns in  a database:

SELECT QUOTENAME(SCHEMA_NAME(T.SCHEMA_ID)) + '.' + QUOTENAME(T.NAME) AS TABLENAME, C.NAME AS COLUMNNAME, CASE C.SYSTEM_TYPE_ID WHEN 56 THEN 'INT' WHEN 52 THEN 'SMALLINT' WHEN 48 THEN 'TINYINT' END AS 'DATATYPE', IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS CURRENTIDENTITYVALUE, IDENT_INCR(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYINCREMENT, IDENT_SEED(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYSEED, CASE C.SYSTEM_TYPE_ID WHEN 56 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 2147483647 WHEN 52 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 32767 WHEN 48 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 255 END AS 'PERCENTAGEUSED'
FROM SYS.COLUMNS AS C INNER JOIN SYS.TABLES AS T ON T.[OBJECT_ID] = C.[OBJECT_ID] AND C.IS_IDENTITY = 1 ORDER BY PERCENTAGEUSED DESC

Error 0xC0014062: Unable to complete login process due to delay in opening server connection

Sometimes you may get an error log when your backup maintenance plan job gets fails with the following error on a server with many databases or connections:

Message:
Executed as user: xyz\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  01:00:05 AM  Could not load package "Maintenance Plans\xyz" because of error 0xC0014062.  
Description: The LoadFromSQLServer method has encountered OLE DB error code 0-80004005 (Unable to complete login process due to delay in opening server connection).  The SQL statement that was issued has failed.  
Source:   Started:  01:00:05 AM  Finished: 01:01:02 AM  Elapsed:  57.175 seconds.  
The package could not be loaded.  The step failed.

This indicates that the maintenance plan which uses SSIS (integrated services) and msdb databases is delay in connection. To resolve this issue general recommendation could be

Change the host file
Which is generally located at “C:\WINDOWS\system32\drivers\etc\host” add server name and an IP address.

Change the number of retry for the specific job to greater number (eg. 5 or more) and also can change the retry interval to (0). This way if this job fails with delay in connection, will retry again till specified number of times and mostly it gets succeeded.

T-Log space used by which transaction

Sometime T-Log space is increasing very fast and we are not aware that which transaction is culprit. You can use below DMV to check which transaction is culprit.

SELECT ST.Session_ID AS [Session ID], DT.Transaction_ID AS [Transaction ID], DB_NAME(DT.Database_ID) AS [DataBase Name], 
CASE Database_Transaction_Type WHEN 1 THEN 'Read / Write' WHEN 2 THEN 'Read Only' WHEN 3 THEN 'System' END AS [Transaction Type], 
CASE Database_Transaction_State WHEN 1 THEN 'Not Initialized' WHEN 3 THEN 'Transaction No Log' WHEN 4 THEN 'Transaction With Log' WHEN 5 THEN 'Transaction Prepared' WHEN 10 THEN 'Commited' WHEN 11 THEN 'Rolled Back' WHEN 12 THEN 'Commited And Log Generated' END AS [Transaction State], 
Database_Transaction_Begin_Time AS [Transaction Begin Time], SP.HostName AS [Host Name], SQLT.Text, SP.LogiName AS [Login Name], SP.LastWaitType AS [Last Wait Type], SP.Status, 
Database_Transaction_Log_Bytes_Reserved AS [Log Bytes Reserved], Database_Transaction_Log_Record_Count AS [Log Record Count], Database_Transaction_Log_Bytes_Used AS [Log Bytes Used], 
(Database_Transaction_Log_Bytes_Used + Database_Transaction_Log_Bytes_Reserved )/1024 AS [Total Log Space Used KB]
FROM SYS.DM_TRAN_Database_TransactionS AS DT JOIN SYS.DM_TRAN_SESSION_TransactionS AS ST ON DT.Transaction_ID=ST.Transaction_ID JOIN SYS.SYSPROCESSES AS SP ON SP.SPID = ST.SESSION_ID CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT 
WHERE DT.Transaction_ID > 1000 AND ST.Session_ID > 50

Change Batch Separator

By default GO is the batch separator in SQL Server. GO is not a Transact SQL statement. It is a command recognized by SQL server as batch separator. You can change the batch separator (GO) in SQL server.

Follow below Steps to change the batch separator in SSMS:

• Start SSMS
• Go to Tools –> Options
• Click on the “Query Execution” node
• Notice that we have an option to change the Batch Separator
• Change the batch separator
• Click “OK”

List all Disabled Trigger

Use below query to get a list of all disabled trigger in a database:

SELECT db_name() AS [Database Name],
T.[Name] AS [TableName],
TR.[Name] AS [TriggerName],
[Status] = CASE WHEN OBJECTPROPERTY(TR.[ID], 'ExecIsTriggerDisabled') = 1
THEN 'Disabled' ELSE 'Enabled' END
FROM sysobjects T JOIN sysobjects TR ON T.[ID] = TR.[parent_obj]
WHERE T.[xtype] = 'U' AND TR.[xtype] = 'TR' AND OBJECTPROPERTY(TR.[ID], 'ExecIsTriggerDisabled') = 1 

Here you can use 0 to list Enabled triggers and 1 for Disabled triggers.

Last time SQL instance was restarted

You can use below methods to check the last time a SQL Server instance was started. 

Option 1 : Search the SQL Server error log for process start time. 

--search the current sql server error log for the the startup process id
DECLARE @ProcessType VARCHAR(64) = 'Server'
DECLARE @TextSearch VARCHAR(20) = 'Server process ID is'
DECLARE @LogType TINYINT = 1 -- 1 = SQL 2 = Agent
DECLARE @LogNum TINYINT = 0 -- 0 = current 1 =.1 etc
DECLARE @ErrLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
INSERT INTO @ErrLog EXEC sys.xp_ReadErrorLog @LogNum, @LogType, @ProcessType, @TextSearch

--grab the first occurrence and report back the timestamp as the last startup
DECLARE @LastRestart DATETIME
SELECT @LastRestart = MIN(LogDate) FROM @ErrLog
SELECT @LastRestart AS [Last Restart]
GO

Option 1 : You can use below DMV also to check last start date of SQL Server instance

SELECT SQLServer_Start_Time AS [Last Start Date] FROM sys.dm_os_sys_info