This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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