Disk usage for the databases and tempdb.

,

This procedure will return size of the data files & log files of the database and tempdb. This is returned in one resultset so you can use this procedure to store fluctuation in database sizes.

/*****************************************************************************************************
   Procedure Name	SP_PH_DATABASE_USAGE
	Created By: 	Prakash Heda
   Functionality  	This procedure will return size of the data files & log files of 
							the database and tempdb. This is returned in one resultset so you 
							can use this procedure to store fluctuation in database sizes.
   Parameters Used	parameter name    	datatype
		NA						NA							NA
   Tables Used		Table Name		Operation Type
						SYSOBJECTS			Select
						SYSFILEGROUPS		Select 
   Language             T SQL
   Date of Creation     1st May 2002.
   Modifications done   By		Date		Purpose
==========================================================================================*/   
USE MASTER
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'SP_PH_DATABASE_USAGE' AND XTYPE = 'P')
DROP PROC SP_PH_DATABASE_USAGE 
GO

CREATE PROCEDURE SP_PH_DATABASE_USAGE AS
SET NOCOUNT ON
IF EXISTS(	SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##DATASPACEFORDB' )
DROP TABLE ##DATASPACEFORDB
IF EXISTS(	SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TEMPFORDB' )
DROP TABLE ##TEMPFORDB


CREATE TABLE ##DATASPACEFORDB
 (	FILEID 	NUMERIC,
	FILEGRP 	NUMERIC,
	TOTEXT	NUMERIC,
	USDEXT 	NUMERIC,
   LFILENM	VARCHAR( 100),
   PFILENM	VARCHAR( 100)
   )
INSERT ##DATASPACEFORDB
 EXEC( 'DBCC SHOWFILESTATS WITH NO_INFOMSGS')

SELECT FILEID,GROUPNAME,(TOTEXT*64)/1024 AS 'TOTALSPACE_MB',(USDEXT*64)/1024 AS 'SPACEUSED_MB',LFILENM,PFILENM 
INTO ##TEMPFORDB FROM ##DATASPACEFORDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID
ORDER BY GROUPNAME

INSERT INTO ##TEMPFORDB 
SELECT '50','TOTAL DATA FILE SIZE IN GB',SUM((TOTEXT*64)/1024)/1024 ,SUM((USDEXT*64)/1024)/1024  ,'',''
FROM ##DATASPACEFORDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID


IF EXISTS(	SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##DATASPACEFORTEMPDB' )
DROP TABLE ##DATASPACEFORTEMPDB

IF EXISTS(	SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TEMPFORTEMPDB' )
DROP TABLE ##TEMPFORTEMPDB

CREATE TABLE ##DATASPACEFORTEMPDB
 (	FILEID 	NUMERIC,
	FILEGRP 	NUMERIC,
	TOTEXT	NUMERIC,
	USDEXT 	NUMERIC,
   LFILENM	VARCHAR( 100),
   PFILENM	VARCHAR( 100)
   )
INSERT ##DATASPACEFORTEMPDB
 EXEC( 'USE TEMPDB DBCC SHOWFILESTATS WITH NO_INFOMSGS')

SELECT FILEID,GROUPNAME,(TOTEXT*64)/1024 AS 'TOTALSPACE_MB',(USDEXT*64)/1024 AS 'SPACEUSED_MB',LFILENM,PFILENM 
INTO ##TEMPFORTEMPDB FROM ##DATASPACEFORTEMPDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID
ORDER BY GROUPNAME

INSERT INTO ##TEMPFORTEMPDB 
SELECT '100','TOTAL DATA FILE SIZE IN GB',SUM((TOTEXT*64)/1024)/1024 ,SUM((USDEXT*64)/1024)/1024  ,'',''
FROM ##DATASPACEFORTEMPDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID

IF EXISTS(	SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TMPLOGSPACE' )
DROP TABLE ##TMPLOGSPACE

CREATE TABLE ##TMPLOGSPACE 
(
DATABASENAME VARCHAR(100),
LOGSIZE VARCHAR(100),
LOGSPACEUSED VARCHAR(100),
STATUS VARCHAR(100)
)

INSERT INTO ##TMPLOGSPACE 
    EXEC ('DBCC SQLPERF ( LOGSPACE ) WITH NO_INFOMSGS')


SELECT '                        ' AS 'DATABASENAME',* INTO ##FINALTABLEFORDB  FROM ##TEMPFORDB WHERE 1=2

INSERT INTO ##FINALTABLEFORDB  
SELECT DB_NAME(),* FROM ##TEMPFORDB 

INSERT INTO ##FINALTABLEFORDB  
 SELECT 'TEMPDB',* FROM ##TEMPFORTEMPDB

INSERT INTO ##FINALTABLEFORDB  
SELECT DATABASENAME,101,'TOTAL LOG SIZE IN GB',CONVERT(NUMERIC(20),LOGSIZE)/1024 , (CONVERT(NUMERIC(20),((CONVERT(NUMERIC(20),LOGSPACEUSED)*LOGSIZE)/100)))/1024,'',''  
FROM ##TMPLOGSPACE WHERE DATABASENAME IN (DB_NAME(),'TEMPDB' )

SELECT UPPER(DATABASENAME),FILEID,GROUPNAME,CONVERT(NUMERIC(20),TOTALSPACE_MB) AS TOTALSPACE_MB,CONVERT(NUMERIC(20),SPACEUSED_MB) AS SPACEUSED_MB ,CONVERT(NUMERIC(20),(TOTALSPACE_MB-SPACEUSED_MB)) AS 'FREESPACE_MB' , LFILENM,PFILENM 
FROM ##FINALTABLEFORDB  ORDER BY 1,2

DROP TABLE ##DATASPACEFORDB, ##TEMPFORDB
DROP TABLE ##DATASPACEFORTEMPDB, ##TEMPFORTEMPDB
DROP TABLE ##TMPLOGSPACE, ##FINALTABLEFORDB

GO

Rate

Share

Share

Rate