SP that shows Actual space used & assigned to all files in a DB

Question

Post reply

SP that shows Actual space used & assigned to all files in a DB

YSLGuru

SSC-Insane

Points: 21661
More actions
February 15, 2009 at 12:43 am

#126474

I just had to unexpectatntly reformat my system and I lost some of my shortcut code like an SP I used (assigned to a Ctrl key combo) that would show the spaceallotted to each file in a DB (Data & logs) as well as how much of that spaec was actually being used verses set aside and unused.
I swer I picked it up on this website but I can't locate anything like it now. Does anyone recognize this?
Thanks
Kindest Regards,
Just say No to Facebook!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

Was it the code from this article?

http://www.mssqltips.com/tip.asp?tip=1349

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 2

I'm sure there's a better way than the code I modified to go from 2k to 2k5... I haven't nailed down all of the new system views that came with 2k5, yet. But, the following seems to do the trick... first time may take a while because it does a DBCC UPDATEUSAGE on each database.

--====================================================================================================================

-- Presets

--====================================================================================================================

--===== Setup the environment to prevent blocking, etc

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --Allows "dirty" reads

SET NOCOUNT ON --Suppresses auto-display of rowcounts for appearance/speed

--===== Declare local variables

DECLARE @SQLExec VARCHAR(MAX) --Holds executable dynamic SQL

DECLARE @SQLTmpl VARCHAR(MAX) --Holds dynamic SQL template

--===== Preset Values

SET @SQLExec = ''

SET @SQLTmpl ='

--===== Identify the database to use

USE

--===== Make sure usage info is up to date for each DB

DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS

--===== Get the database information similar to sp_SpaceUsed but in a set based fashion.

-- (Returns only 1 row for current database for each call)

INSERT INTO #Results

(DBName,DBID,DBTotalMB,LogFileMB,DataFileMB,UnallocatedMB,ReservedMB,TotalUsedMB,UnusedMB,DataMB,IndexMB)

SELECT DBName = DB_NAME(),

DBID = DB_ID(),

DBTotalMB = fs.DataFileMB + fs.LogFileMB,

LogFileMB = fs.LogFileMB,

DataFileMB = fs.DataFileMB,

UnallocatedMB = fs.DataFileMB - r.ReservedMB,

ReservedMB = r.ReservedMB,

TotalUsedMB = r.TotalUsedMB,

UnusedMB = r.ReservedMB - r.TotalUsedMB,

DataMB = r.DataMB,

IndexMB = r.TotalUsedMB - r.DataMB

FROM (--==== Derived table "fs" finds total file sizes (Status 64 = Log Device, 128 = Pages per MB)

SELECT DataFileMB = SUM(CASE WHEN Status & 64 = 0 THEN Size ELSE 0 END)/128.0,

LogFileMB = SUM(CASE WHEN Status & 64 <> 0 THEN Size ELSE 0 END)/128.0

FROM dbo.SysFiles

)fs

,

(--==== Derived table "r" finds types of space

SELECT ReservedMB = SUM(Reserved)/128.0,

TotalUsedMB = SUM(Used)/128.0,

DataMB = SUM(CASE WHEN IndID < 2 THEN DPages

WHEN IndID = 255 THEN Used

ELSE 0

END)/128.0

FROM dbo.SysIndexes

WHERE IndID IN (0,1,255)

)r'

--====================================================================================================================

-- Create Temp Tables

--====================================================================================================================

--===== Temp table to hold results to be displayed

IF OBJECT_ID('TempDB..#Results','U') IS NOT NULL

DROP TABLE #Results

CREATE TABLE #Results

(

DBName SYSNAME PRIMARY KEY CLUSTERED,

DBID INT,

DBTotalMB DECIMAL(19,1),

LogFileMB DECIMAL(19,1),

DataFileMB DECIMAL(19,1),

UnallocatedMB DECIMAL(19,1),

ReservedMB DECIMAL(19,1),

TotalUsedMB DECIMAL(19,1),

UnusedMB DECIMAL(19,1),

DataMB DECIMAL(19,1),

IndexMB DECIMAL(19,1)

)

--====================================================================================================================

-- Build the command for all databases

--====================================================================================================================

--===== Get the next database name to work on an insert the dynamic SQL using the template as a master

SELECT @SQLExec = @SQLExec + REPLACE(@SQLTmpl,' ',Name) + CHAR(13)

FROM Master.sys.SysDatabases

--===== Execute the dynamic SQL to get the size information for the current database name

EXEC (@SQLExec)

--====================================================================================================================

-- Return the results

--====================================================================================================================

SELECT * FROM #Results

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)