How much space is a SQL server using? 2

[daglugub37]

Is there a way to find out how much space all databases of a particular server are using? Not allocated space but an actual sumation of space used across all databases

 

[ptheriault]

what version of SQL is this?

- Paul
- Database performance looks fine, it must be the Network!

 

[daglugub37]

2000

 

[ptheriault]

you could use sp_MSforeachdb to select the size from the sysfiles table in each db.

Or something like this.

SELECT @@ServerName AS Server, 
	LEFT(@@version, 26) AS Version
PRINT 'Database Files - locations - sizes...'
--File Sizes and locations etc...
DECLARE @dbid INT
DECLARE @MaxId INT
DECLARE @dbName SYSNAME
SET @MaxId = (SELECT MAX(dbid) FROM MASTER.dbo.sysdatabases)
SET @dbid = 1 
WHILE @dbid <= @MaxId 
BEGIN
SET @dbName = (SELECT name FROM MASTER.dbo.sysdatabases WHERE dbid = @dbid)
	IF (@dbname IS NOT NULL) 
	BEGIN
	EXEC ('SET QUOTED_IDENTIFIER OFF
	SELECT "[' + @dbname +']" AS DBName, 
	RTRIM(name) AS DevName, 
	RTRIM(filename) AS Filename, 
	RTRIM(size/128) AS Size FROM [' + @dbname + '].dbo.sysfiles')
	SET @dbid = @dbid + 1
	END
	ELSE
	SET @dbid = @dbid + 1
END

But both of these examples are the size of the file on disk. If you want the size of the actual data you would have to query sysindexes. you would have to take the number of pages and multiply it by 8K. I would also run

DBCC UPDATEUSAGE

To update the usage the SQL server is reporting

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

Here is a script I just got from SSWUG. You will have to run it on each of your databases then sum up the the total size of each.

SELECT 	SUBSTRING(/*u.name + '.' +*/ o.name,1,50) AS TableName,
	Rows,
	Reserved * 8 AS SizeKB,
	Reserved * 8 / 1024 AS SizeMB
FROM sysobjects o (NOLOCK)
	INNER JOIN sysindexes i (NOLOCK) ON o.id = i.id AND o.type = 'U' AND i.indid < 2
	INNER JOIN sysusers u (NOLOCK) ON u.uid = o.uid
ORDER BY o.name, u.name
COMPUTE SUM(Rows), SUM(Reserved * 8), SUM(Reserved * 8 / 1024)

- Paul
- Database performance looks fine, it must be the Network!

 

[daglugub37]

thanks, these solutions worked out fine

 

[mutley1]

Hey guys,

Here's another one that will give you a lot of info (i.e. allocated space, space used, % free of the data / log files, sutogrow options, filegroup or data or log, location of the physical file and the amount of free disk space left on drive that file is sitting on. This will do all the DB's on the server so as Paul said you just need to do a sum at the bottom.

2 stored procs, 1 table in the code below. usp_dbspace_stats populates table dbspace_stats and also calls usp_dbspace_dsk. the _dsk script drops a table and recreates it with an insert (of xp_fixeddrives). The final part of the 1st stored proc updates the free disk space column in dbspace_stats.

When you load it you might get a sysdepends error - not an issue.

Just load the script and then EXEC usp_dbspace_stats and then a select * from dbspace_stats.

CREATE TABLE [dbo].[dbspace_stats] (
	[datestamp] [datetime] NULL ,
	[Servername] [varchar] (50) NULL ,
	[DataBaseName] [varchar] (50) NULL ,
	[Fileid] [int] NULL ,
	[GroupId] [int] NULL ,
	[Grp] [varchar] (50) NULL ,
	[logical_name] [varchar] (500) NULL ,
	[size_mb] [varchar] (50) NULL ,
	[used_mb] [varchar] (50) NULL ,
	[space_free] [decimal](5, 2) NULL ,
	[file_growth] [varchar] (50) NULL ,
	[max_mb] [varchar] (50) NULL ,
	[physical_name] [varchar] (50) NULL ,
	[filetype] [varchar] (10) NULL ,
	[freediskspace] [varchar] (10) NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




CREATE procedure usp_dbspace_stats
AS
truncate table dbspace_stats
--truncate table dbspace_dsk
EXEC usp_dbspace_dsk

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

insert into dbspace_stats
exec sp_MSforeachdb @command1='use [?] 
select	GetDate(),
	@@servername,
	''?'' AS DataBaseName,
	fileid,
	sf.groupid,
	grp = left([groupname],20),
	phname = upper (rtrim(filename)),
	size_mb=[size]/128,
	used_mb = FILEPROPERTY([name], ''SpaceUsed'')/128,
	space_free = case when (100 - ((convert (float,(FILEPROPERTY([name], ''SpaceUsed'')/128)) / (convert (float,[size]/128)))*100)) is NULL then 0
		else 100 - ((convert (float,(FILEPROPERTY([name], ''SpaceUsed'')/128)) / (convert (float,[size]/128)))*100) end,
	file_growth = case when (sf.status&0x100000) > 0 then str(growth)+'' %''
		else str(growth/128)+'' mb'' end,
	max_mb=case when [maxsize]<0 then ''Unrestricted''
		else str([maxsize]/128) end,
	lname = rtrim([name]),
	filetype = case
		when filename like ''%.mdf%'' THEN ''DATAFILE''
		when filename like ''%.MDF%'' THEN ''DATAFILE''
		when filename like ''%.ldf%'' THEN ''LOGFILE''
		when filename like ''%.LDF%'' THEN ''LOGFILE''
		when filename like ''%.ndf%'' THEN ''FILEGROUP''
		else ''UNKNOWN FILE TYPE''
			END,
	''''
from sysfiles sf 
	left outer join 
sysfilegroups sfg 
	on sf.groupid=sfg.groupid
order by 1, 2, 3'

update dbspace_stats
set freediskspace = DRVSPACE
from dbspace_stats, dbspace_dsk
where [SQLSERVER_NAME] = [Servername]
and (left (logical_name, 1)) = DRVLETTER

--select * from dbspace_stats
--select * from dbspace_dsk
--drop table dbspace_stats

SET ARITHABORT ON
SET ANSI_WARNINGS ON



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE      procedure usp_dbspace_dsk


AS

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DBspace_dsk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DBspace_dsk]

DECLARE @server_name Varchar (50)
SET @server_name = (SELECT @@servername)
        
CREATE TABLE #T1(
	DRVLETTER CHAR(1),
	DRVSPACE INT
	)
INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives
SELECT
	SQLSERVER_NAME = @server_name,
     	DRVLETTER,
	DRVSPACE 
INTO DBspace_dsk
FROM    #T1

--SELECT * FROM DBspace_dsk

--DROP TABLE DBspace_dsk


DROP TABLE #T1



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

HTH

M.

 

[ptheriault]

Nice script M, I'll be adding it to my library!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mutley1]

Cheers Paul,

I cant take the credit as I got it somewhere else, but did tailor it a bit for certain items - esp the disk space as we use calcs to work out everything on a drive and take into account the growth rate etc. to see if there will be any possible space issues.

I should have added the disk space update / proc into the original usp but was just being lazy....

Cheers,

M.