Getting database size: a stored procedure to do it......

[Robin Tucker]

As I had real problems working my head around sp_spaceused, I've written an
SP to do it (I also noted a lot of questions about this when "searching").
Pass in a database name and it will return the size of the database as a
float (ie. 0.75 for 0.75mb). Update usage set to 1 indicates the DB should
update its size information before giving you the result.

Anyway, comments appreciate.
ALTER PROCEDURE dbo.proc_BL_Get_Space_Used

@DatabaseName NVARCHAR(54),
@updateusage BIT,
@Size REAL OUTPUT
AS
BEGIN
DECLARE @dbsize DECIMAL(15,0)
DECLARE @bytesperpage DECIMAL(15,0)
DECLARE @pagesperMB DECIMAL(15,0)
DECLARE @Error INTEGER
DECLARE @ExecString NVARCHAR(256)
DECLARE @ParmString NVARCHAR(128)

SET @Error = 0

/*
Update usage for this database.
*/

IF @updateusage = 1
BEGIN
DBCC UPDATEUSAGE (@DatabaseName) WITH NO_INFOMSGS
SET @Error = @@ERROR
END

SET NOCOUNT ON

/*
Work out the database size.
*/

IF @Error = 0
BEGIN
SET @ExecString = 'SELECT @dbsize = SUM ( CONVERT ( DECIMAL (
15 ), SIZE ) ) FROM ' + @DatabaseName + '.dbo.sysfiles WHERE (status & 64 =
0)'
SET @ParmString = '@dbsize DECIMAL(15,0) OUTPUT'
EXECUTE sp_executesql @ExecString, @ParmString, @dbsize OUTPUT
SET @Error = COALESCE ( NULLIF ( @Error, 0 ), @@ERROR )
END

/*
and bytes per page.
*/

IF @Error = 0
BEGIN
SELECT @bytesperpage = LOW
FROM master.dbo.spt_values
WHERE number = 1
AND type = 'E'
SET @Error = @@ERROR
END

/*
pages per mb
*/

IF @Error = 0
BEGIN
SELECT @pagesperMB = 1048576 / @bytesperpage
SET @Error = @@ERROR
END

/*
and finally, the result.
*/

IF @Error = 0
BEGIN
SET @Size = CONVERT(REAL, @dbsize) / CONVERT(REAL, @pagesperMB )
SET @Error = @@ERROR
END

RETURN @Error
END

[Robin Tucker]

Oh, this just works out the db size, it doesn't throw in the log file size
or anything extra like that. I wrote this to check on db sizes for MSDE
(desktop engine), which sets a limit of 2gb. Something we had to work
around in our software.

"Robin Tucker" <id*************************@reallyidont.com> wrote in
message news:bk*******************@news.demon.co.uk...

As I had real problems working my head around sp_spaceused, I've written an SP to do it (I also noted a lot of questions about this when "searching").
Pass in a database name and it will return the size of the database as a
float (ie. 0.75 for 0.75mb). Update usage set to 1 indicates the DB should update its size information before giving you the result.

Anyway, comments appreciate.
ALTER PROCEDURE dbo.proc_BL_Get_Space_Used

@DatabaseName NVARCHAR(54),
@updateusage BIT,
@Size REAL OUTPUT
AS
BEGIN
DECLARE @dbsize DECIMAL(15,0)
DECLARE @bytesperpage DECIMAL(15,0)
DECLARE @pagesperMB DECIMAL(15,0)
DECLARE @Error INTEGER
DECLARE @ExecString NVARCHAR(256)
DECLARE @ParmString NVARCHAR(128)

SET @Error = 0

/*
Update usage for this database.
*/

IF @updateusage = 1
BEGIN
DBCC UPDATEUSAGE (@DatabaseName) WITH NO_INFOMSGS
SET @Error = @@ERROR
END

SET NOCOUNT ON

/*
Work out the database size.
*/

IF @Error = 0
BEGIN
SET @ExecString = 'SELECT @dbsize = SUM ( CONVERT ( DECIMAL (
15 ), SIZE ) ) FROM ' + @DatabaseName + '.dbo.sysfiles WHERE (status & 64 = 0)'
SET @ParmString = '@dbsize DECIMAL(15,0) OUTPUT'
EXECUTE sp_executesql @ExecString, @ParmString, @dbsize OUTPUT
SET @Error = COALESCE ( NULLIF ( @Error, 0 ), @@ERROR )
END

/*
and bytes per page.
*/

IF @Error = 0
BEGIN
SELECT @bytesperpage = LOW
FROM master.dbo.spt_values
WHERE number = 1
AND type = 'E'
SET @Error = @@ERROR
END

/*
pages per mb
*/

IF @Error = 0
BEGIN
SELECT @pagesperMB = 1048576 / @bytesperpage
SET @Error = @@ERROR
END

/*
and finally, the result.
*/

IF @Error = 0
BEGIN
SET @Size = CONVERT(REAL, @dbsize) / CONVERT(REAL, @pagesperMB ) SET @Error = @@ERROR
END

RETURN @Error
END