MSSQL DB Space usage

[datosivakumar]

How to find out the space usage for all the databases in MS SQL thru SQL Query Analyzer?

Normaly i used to view thru SQL Server Enterprise Manager Screen (view --> Taskpad) by selecting 1 db per view.

[PaulThomasSilvey]

I think you are looking for this EXEC sp_helpdb

[ck9663]

Also, check this out.

-- CK

[madan agarwal]

declare @TargetDatabase sysname
declare @Level varchar(10)
declare @UpdateUsage bit
declare @Unit char(2)
select @Unit='MB'
SET NOCOUNT ON;
IF OBJECT_ID('temp db.dbo.##Tbl_Co mbinedInfo', 'U') IS NOT NULL
DROP TABLE dbo.##Tbl_Combi nedInfo;

IF OBJECT_ID('temp db.dbo.##Tbl_Db FileStats', 'U') IS NOT NULL
DROP TABLE dbo.##Tbl_DbFil eStats;

IF OBJECT_ID('temp db.dbo.##Tbl_Va lidDbs', 'U') IS NOT NULL
DROP TABLE dbo.##Tbl_Valid Dbs;

IF OBJECT_ID('temp db.dbo.##Tbl_Lo gs', 'U') IS NOT NULL
DROP TABLE dbo.##Tbl_Logs;

CREATE TABLE dbo.##Tbl_Combi nedInfo (
DatabaseName sysname NULL,
[type] VARCHAR(10) NULL,
LogicalName sysname NULL,
T dec(10, 2) NULL,
U dec(10, 2) NULL,
[U(%)] dec(5, 2) NULL,
F dec(10, 2) NULL,
[F(%)] dec(5, 2) NULL,
PhysicalName sysname NULL );
CREATE TABLE dbo.##Tbl_DbFil eStats (
Id int identity,
DatabaseName sysname NULL,
FileId int NULL,
FileGroup int NULL,
TotalExtents bigint NULL,
UsedExtents bigint NULL,
Name sysname NULL,
FileName varchar(255) NULL );

CREATE TABLE dbo.##Tbl_Valid Dbs (
Id int identity,
Dbname sysname NULL );

CREATE TABLE dbo.##Tbl_Logs (
DatabaseName sysname NULL,
LogSize dec (10, 2) NULL,
LogSpaceUsedPer cent dec (5, 2) NULL,
Status int NULL );
DECLARE @Ver varchar(10),
@DatabaseName sysname,
@Ident_last int,
@String varchar(2000),
@BaseString varchar(2000);

SELECT @DatabaseName = '',
@Ident_last = 0,
@String = '',
@Ver = CASE WHEN @@VERSION LIKE '%9.0%' THEN 'SQL 2005'
WHEN @@VERSION LIKE '%8.0%' THEN 'SQL 2000'
WHEN @@VERSION LIKE '%10.0%' THEN 'SQL 2008'
END;

SELECT @BaseString =
' SELECT DB_NAME(), ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'CASE WHEN status & 0x40 = 0x40 THEN ''Log'' ELSE ''Data'' END'
ELSE ' CASE type WHEN 0 THEN ''Data'' WHEN 1 THEN ''Log'' WHEN 4 THEN ''Full-text'' ELSE ''reserved'' END' END +
', name, ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'filename' ELSE 'physical_name' END +
', size*8.0/1024.0 FROM ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'sysfiles' ELSE 'sys.database_f iles' END +
' WHERE '
+ CASE WHEN @Ver = 'SQL 2000' THEN ' HAS_DBACCESS(DB _NAME()) = 1' ELSE 'state_desc = ''ONLINE''' END + '';
SELECT @String = 'INSERT INTO dbo.##Tbl_Valid Dbs SELECT name FROM ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'master.dbo.sys databases'
WHEN @Ver IN ('SQL 2005', 'SQL 2008') THEN 'master.sys.dat abases'
END + ' WHERE HAS_DBACCESS(na me) = 1 ORDER BY name ASC';
EXEC (@String);
INSERT INTO dbo.##Tbl_Logs EXEC ('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS');
-- For data part
IF @TargetDatabase IS NOT NULL
BEGIN
SELECT @DatabaseName = @TargetDatabase ;
SELECT @String = 'INSERT INTO dbo.##Tbl_Combi nedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString;
INSERT INTO dbo.##Tbl_DbFil eStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName)
EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');
EXEC ('USE [' + @DatabaseName + '] ' + @String);

UPDATE dbo.##Tbl_DbFil eStats SET DatabaseName = @DatabaseName;
END
ELSE
BEGIN
WHILE 1 = 1
BEGIN
SELECT TOP 1 @DatabaseName = Dbname FROM dbo.##Tbl_Valid Dbs WHERE Dbname > @DatabaseName ORDER BY Dbname ASC;
IF @@ROWCOUNT = 0
BREAK;
/*
IF @UpdateUsage <> 0 AND DATABASEPROPERT YEX (@DatabaseName, 'Status') = 'ONLINE'
AND DATABASEPROPERT YEX (@DatabaseName, 'Updateability' ) <> 'READ_ONLY'
BEGIN
SELECT @String = 'DBCC UPDATEUSAGE (''' + @DatabaseName + ''') ';
PRINT '*** ' + @String + '*** ';
EXEC (@String);
PRINT '';
END
*/

SELECT @Ident_last = ISNULL(MAX(Id), 0) FROM dbo.##Tbl_DbFil eStats;
SELECT @String = 'INSERT INTO dbo.##Tbl_Combi nedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString;
EXEC ('USE [' + @DatabaseName + '] ' + @String);

INSERT INTO dbo.##Tbl_DbFil eStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName)
EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');
UPDATE dbo.##Tbl_DbFil eStats SET DatabaseName = @DatabaseName WHERE Id BETWEEN @Ident_last + 1 AND @@IDENTITY;
END
END
-- set used size for data files, do not change total obtained from sys.database_fi les as it has for log files
UPDATE dbo.##Tbl_Combi nedInfo
SET U = s.UsedExtents*8 *8/1024.0
FROM dbo.##Tbl_Combi nedInfo t JOIN dbo.##Tbl_DbFil eStats s
ON t.LogicalName = s.Name AND s.DatabaseName = t.DatabaseName;
-- set used size and % values for log files:
UPDATE dbo.##Tbl_Combi nedInfo
SET [U(%)] = LogSpaceUsedPer cent,
U = T * LogSpaceUsedPer cent/100.0
FROM dbo.##Tbl_Combi nedInfo t JOIN dbo.##Tbl_Logs l
ON l.DatabaseName = t.DatabaseName
WHERE t.type = 'Log';
UPDATE dbo.##Tbl_Combi nedInfo SET F = T - U, [U(%)] = U*100.0/T;
UPDATE dbo.##Tbl_Combi nedInfo SET [F(%)] = F*100.0/T;
IF UPPER(ISNULL(@L evel, 'DATABASE')) = 'FILE'
BEGIN
IF @Unit = 'KB'
UPDATE dbo.##Tbl_Combi nedInfo
SET T = T * 1024, U = U * 1024, F = F * 1024;

IF @Unit = 'GB'
UPDATE dbo.##Tbl_Combi nedInfo
SET T = T / 1024, U = U / 1024, F = F / 1024;

SELECT DatabaseName AS 'Database',
type AS 'Type',
LogicalName,
T AS 'Total',
U AS 'Used',
[U(%)] AS 'Used (%)',
F AS 'Free',
[F(%)] AS 'Free (%)',
PhysicalName
FROM dbo.##Tbl_Combi nedInfo
WHERE DatabaseName LIKE ISNULL(@TargetD atabase, '%')
ORDER BY DatabaseName ASC, type ASC;
SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM',
SUM (T) AS 'TOTAL', SUM (U) AS 'USED', SUM (F) AS 'FREE' FROM dbo.##Tbl_Combi nedInfo;
END
IF UPPER(ISNULL(@L evel, 'DATABASE')) = 'DATABASE'
BEGIN
DECLARE @Tbl_Final TABLE (
DatabaseName sysname NULL,
TOTAL dec (10, 2),
[=] char(1),
used dec (10, 2),
[used (%)] dec (5, 2),
[+] char(1),
free dec (10, 2),
[free (%)] dec (5, 2),
[==] char(2),
Data dec (10, 2),
Data_Used dec (10, 2),
[Data_Used (%)] dec (5, 2),
Data_Free dec (10, 2),
[Data_Free (%)] dec (5, 2),
[++] char(2),
Log dec (10, 2),
Log_Used dec (10, 2),
[Log_Used (%)] dec (5, 2),
Log_Free dec (10, 2),
[Log_Free (%)] dec (5, 2) );
INSERT INTO @Tbl_Final
SELECT x.DatabaseName,
x.Data + y.Log AS 'TOTAL',
'=' AS '=',
x.Data_Used + y.Log_Used AS 'U',
(x.Data_Used + y.Log_Used)*100 .0 / (x.Data + y.Log) AS 'U(%)',
'+' AS '+',
x.Data_Free + y.Log_Free AS 'F',
(x.Data_Free + y.Log_Free)*100 .0 / (x.Data + y.Log) AS 'F(%)',
'==' AS '==',
x.Data,
x.Data_Used,
x.Data_Used*100/x.Data AS 'D_U(%)',
x.Data_Free,
x.Data_Free*100/x.Data AS 'D_F(%)',
'++' AS '++',
y.Log,
y.Log_Used,
y.Log_Used*100/y.Log AS 'L_U(%)',
y.Log_Free,
y.Log_Free*100/y.Log AS 'L_F(%)'
FROM
( SELECT d.DatabaseName,
SUM(d.T) AS 'Data',
SUM(d.U) AS 'Data_Used',
SUM(d.F) AS 'Data_Free'
FROM dbo.##Tbl_Combi nedInfo d WHERE d.type = 'Data' GROUP BY d.DatabaseName ) AS x
JOIN
( SELECT l.DatabaseName,
SUM(l.T) AS 'Log',
SUM(l.U) AS 'Log_Used',
SUM(l.F) AS 'Log_Free'
FROM dbo.##Tbl_Combi nedInfo l WHERE l.type = 'Log' GROUP BY l.DatabaseName ) AS y
ON x.DatabaseName = y.DatabaseName;

IF @Unit = 'KB'
UPDATE @Tbl_Final SET TOTAL = TOTAL * 1024,
used = used * 1024,
free = free * 1024,
Data = Data * 1024,
Data_Used = Data_Used * 1024,
Data_Free = Data_Free * 1024,
Log = Log * 1024,
Log_Used = Log_Used * 1024,
Log_Free = Log_Free * 1024;

IF @Unit = 'GB'
UPDATE @Tbl_Final SET TOTAL = TOTAL / 1024,
used = used / 1024,
free = free / 1024,
Data = Data / 1024,
Data_Used = Data_Used / 1024,
Data_Free = Data_Free / 1024,
Log = Log / 1024,
Log_Used = Log_Used / 1024,
Log_Free = Log_Free / 1024;

DECLARE @GrantTotal dec(11, 2);
SELECT @GrantTotal = SUM(TOTAL) FROM @Tbl_Final;
/*
SELECT
CONVERT(dec(10, 2), TOTAL*100.0/@GrantTotal) AS 'WEIGHT (%)',
substring(Datab aseName,1,30) AS 'DATABASE',
CONVERT(VARCHAR (12), used) + ' (' + CONVERT(VARCHAR (12), [used (%)]) + ' %)' AS 'USED (%)',
[+],
CONVERT(VARCHAR (12), free) + ' (' + CONVERT(VARCHAR (12), [free (%)]) + ' %)' AS 'FREE (%)',
[=],
TOTAL,
[=],
CONVERT(VARCHAR (12), Data) + ' (' + CONVERT(VARCHAR (12), Data_Used) + ', ' +
CONVERT(VARCHAR (12), [Data_Used (%)]) + '%)' AS 'DATA (used, %)',
[+],
CONVERT(VARCHAR (12), Log) + ' (' + CONVERT(VARCHAR (12), Log_Used) + ', ' +
CONVERT(VARCHAR (12), [Log_Used (%)]) + '%)' AS 'LOG (used, %)'
FROM @Tbl_Final
WHERE DatabaseName LIKE ISNULL(@TargetD atabase, '%')
ORDER BY DatabaseName ASC;

select substring(Datab aseName , 1, 30 ) 'Database' , Data , '=' '=' , Data_Used , '+' '+' , Data_Free , '|' '|' , Log, '=' '=' , Log_Used, '+' '+' , Log_Free from @Tbl_Final
select substring(Datab aseName , 1, 20 ) 'Database' ,
substring(cast( Data as varchar(20)), 1 , 20) 'Data' , '=' '=' ,
substring(cast( Data_Used as varchar(20)), 1 , 20) 'Data Used' , '+' '+' ,
substring(cast( Data_Free as varchar(20)), 1 , 20) 'Data Free' , '|' '|' ,
substring(cast( Log as varchar(20)), 1 , 20) 'Log', '=' '=' ,
substring(cast( Log_Used as varchar(20)), 1 , 20) 'Log Used', '+' '+' ,
substring(cast( Log_Free as varchar(20)), 1 , 20) 'Log Free'
from @Tbl_Final
*/
print '============== =============='
print ' ' + substring(@@ser vername , 1, 30)
print '============== =============='
print ''
print '-------------------- ------------------------------------- ----------- ---- ------------------------------------- -----------'
select substring(Datab aseName , 1, 20 ) 'Database' ,
substring(
substring(cast( Data as varchar(20)), 1 , 20) + ' = ' +
substring(cast( Data_Used as varchar(20)), 1 , 20) + ' + ' +
substring(cast( Data_Free as varchar(20)), 1 , 20)
,1,37)'DATA = USED + FREE (MB)' ,
substring(cast([Data_Free (%)] as varchar(10)), 1 , 10) + '%' 'FREE(%)' , '|' '|' ,
substring(
substring(cast( Log as varchar(20)), 1 , 20) + ' = ' +
substring(cast( Log_Used as varchar(20)), 1 , 20) + ' + ' +
substring(cast( Log_Free as varchar(20)), 1 , 20)
,1,37)'LOG = USED + FREE (MB)' ,
substring(cast([Log_Free (%)] as varchar(10)), 1 , 10) + '%' 'FREE(%)'
from @Tbl_Final
print '-------------------- ------------------------------------- ----------- ---- ------------------------------------- -----------'
END
GO

------------------------------------------------------------------------------------------------------------------------