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