Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
CREATE PROCEDURE [dbo].[proc_Database_Size]
@DBName VARCHAR(200),
@Error VARCHAR(250) OUTPUT
AS
-- ========================================================
-- Author: Your Name
-- Create date: mm/dd/yy
-- Description: Retrieves the size of the given database:
-- * overall size for all LOG records
-- * overall size for all ROWs
-- * overall size (LOG plus ROWs)
-- ========================================================
BEGIN
-- Prevents DONE_IN_PROC messages from being sent
-- back to the client
SET NOCOUNT ON;
-- Check if the given database already exists
IF (NOT EXISTS (
SELECT name
FROM master.dbo.sysdatabases
WHERE (''['' + name + '']'' = @DBName OR name = @DBName))
)
BEGIN
SET @Error = ''Database '' + @DBName + '' doesn''''t exist'';
RETURN -1;
END
-- Get different size values for the given database
SELECT
LogDataSize = CAST(SUM(CASE WHEN type_desc = ''LOG'' THEN size END) * 8. / 1024 AS DECIMAL(8,1)),
RowDataSize = CAST(SUM(CASE WHEN type_desc = ''ROWS'' THEN size END) * 8. / 1024 AS DECIMAL(8,1)),
OverallSize = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID(@DBName)
GROUP BY database_id;
RETURN 0;
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment