Search

Monday, December 29, 2014

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

Monday, December 15, 2014

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'

Monday, December 8, 2014

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.

Monday, December 1, 2014

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