Database Size - Data and Log :: Used/Free 2

[eb24]

I'm currently writing a report on the databases I currently have on our internal SQL Server 2000 box b/c they will soon be deployed to 2 other sites in 2 different cities. In the report, I am suppose to include the size of each database we have.

My question is, when I right-click on the database via EM, towards the top of the screen under the Database section, I see a set Size amount. Also, under the Space Allocated section, I see 2 amounts, one for Data and one for the Log, and these 2 having Free and Used amounts. So in my report, should I include the overall Size amount, or only use the Used Data amount, or include the Data and Log Used amounts or report both the Used and Free amounts for both Data and Log which is the Size amount given towards the top?

Thanks in advance for any advice.

 

[SQLBill]

To get accurate data, run two stored procedures.

sp_updateusage and sp_spaceused

Refer to the Books OnLine for the syntax. The first command updates the information and the second one will provide you with the information you need.

-SQLBill

BOL=Books OnLine=MS SQL Server's Help
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

 

[JayKusch]

This code may give you all you desire ... Enjoy!

-- Create Temp Tables

CREATE TABLE #Monitor_DB_and_TLog_Statistics (
	[DT_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Server_Name] [varchar] (100) NULL ,
	[DB_Name] [varchar] (100) NULL ,
	[DB_Total_Size_MB] [money] NULL ,
	[DB_Used_Size_MB] [money] NULL ,
	[DB_Free_Size_MB] [money] NULL ,
	[DB_Physical_Path] [varchar] (400) NULL ,
	[TLog_Total_Size_MB] [money] NULL ,
	[TLog_Used_Size_MB] [money] NULL ,
	[TLog_Free_Size_MB] [money] NULL ,
	[TLog_Physical_Path] [varchar] (400) NULL ,
	[DBs_Created_Date] [datetime] NULL ,
	[Date_Stats_Pulled] [datetime] NULL 
) ON [PRIMARY]

CREATE TABLE #DataSpace (
	[FileID]	[int] NULL ,
	[FileGroup]	[int] NULL ,
	[TotalExtents]	[int] NULL ,
	[UsedExtents]	[int] NULL ,
	[Name]		[varchar] (100) NULL,
	[FileName]	[varchar] (400) NULL 
) ON [PRIMARY]

CREATE TABLE #LogSpace (
	[LS_ID]		[int] IDENTITY (1, 1) NOT NULL,
	[DatabaseName]	[varchar] (100) NULL,
	[LogSize]	[money] NULL,
	[LogSpaceUsed]	[money] NULL,
	[Status]	[money] NULL 
) ON [PRIMARY]

CREATE TABLE #TLogName (
	[DBName]	[varchar] (100) NULL,
	[TLogFileName]	[varchar] (400) NULL
) ON [PRIMARY]

-- INSERT TLog infor into #LogSpace Temp Table

INSERT INTO #LogSpace EXEC ('DBCC SQLPERF (LogSpace)')

-- Gather Data & TLogs stats

DECLARE	@SQL_Command	VarChar(400)
DECLARE	@DBProcessing	VarChar(100)
DECLARE	@NextDBID	Int
DECLARE	@MaxDBID	Int

SET	@NextDBID = 1
SET	@MaxDBID  = (SELECT MAX(LS_ID) FROM #LogSpace)

WHILE	@NextDBID <= @MaxDBID

  BEGIN
	SET	@DBProcessing = (SELECT DatabaseName FROM #LogSpace WHERE LS_ID = @NextDBID)

	SET @SQL_Command = 'USE ' + @DBProcessing + '  EXEC (' + '''' + 'dbcc showfilestats ' + '''' + ')'

	-- INSERT TLog infor into #DataSpace Temp Table

	INSERT INTO #DataSpace 

		EXEC (@SQL_Command)

	-- Get TLog FileName 

	SET @SQL_Command =	'SELECT ' + '''' + @DBProcessing + '''' + ', FileName FROM ' + 
				@DBProcessing + '..SysFiles WHERE GroupId = 0'

	-- INSERT TLog Path infor into #TLogName Temp Table

	INSERT INTO #TLogName 

		EXEC (@SQL_Command)

	SET	@NextDBID = @NextDBID + 1
  END

--	Make DB name uniform between tables for JOINS later

UPDATE	#DataSpace

	SET	Name = REPLACE(REPLACE(REPLACE(Name,'TempDev','TempDB'),'ModelDev','Model'),'MSDBData','MSDB')

-- INSERT New stats to static Production Table

INSERT INTO #Monitor_DB_and_TLog_Statistics

	SELECT	@@ServerName,
		ls.DatabaseName,
		(ds.totalextents * cast(64 as decimal(8,2))/1024),
		(ds.usedextents  * cast(64 as decimal(8,2))/1024),
		(ds.totalextents * cast(64 as decimal(8,2))/1024) - (ds.usedextents * cast(64 as decimal(8,2))/1024),
		ds.FileName,
		ls.LogSize,
		(ls.LogSize * ls.LogSpaceUsed) /100,
		(ls.LogSize - (ls.LogSize * ls.LogSpaceUsed) /100),
		(SELECT TLogFileName FROM #TLogName
		 WHERE	DBName	= ls.DatabaseName),
		(SELECT CRDate FROM Master..SysDatabases
		 WHERE	Name	= 'CDMSAccounting'),
		GetDate()
	FROM #LogSpace  as ls
	JOIN #DataSpace as ds
	ON (ls.DatabaseName = REPLACE(ds.Name,'_Data','') )
	ORDER BY ls.DatabaseName

-- SELECT Information

SELECT * FROM #Monitor_DB_and_TLog_Statistics

-- Clean Up

DROP TABLE #Monitor_DB_and_TLog_Statistics
DROP TABLE #DataSpace
DROP TABLE #LogSpace
DROP TABLE #TLogName

Thanks

J. Kusch