Inserting database size into a database

[aftertaf]

Hi,

I've got a specific need: to use either sp_spaceused, or a similar manner, to obtain the size of a specific database on our server (maybe index size, remaining size too....)
I have tried sp_spaceused and what it returns is good.... but.


What we need it to recover this information and then be able to insert it into a table, to log database size over a certain amount of time.

And i can't find a way of doing this.
Can anyone help?

David

Aftertaf
We shall prevail, and they shall not

 

[2ni]

you can find informations in the sysobects, sysindexes, sysfiles and spt_values tables.

If you need informations send by sp_spaceused look in it and take what u need exactly.

Here is the source of sp_spaceused :




create procedure sp_spaceused --- 1996/08/20 17:01
@objname nvarchar(776) = null, -- The object we want size on.
@updateusage varchar(5) = false -- Param. for specifying that
-- usage info. should be updated.
as

declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)

/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

/*
** Check to see if user wants usages updated.
*/

if @updateusage is not null
begin
select @updateusage=lower(@updateusage)

if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin

select @dbname = parsename(@objname, 3)

if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end

if @dbname is null
select @dbname = db_name()

/*
** Try to find the object.
*/
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@objname)

/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end


if not exists (select * from sysindexes
where @id = id and indid < 2)

if @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
begin
raiserror(15234,-1,-1)
return (1)
end
else if @type = 'V ' -- View => no physical data storage.
begin
raiserror(15235,-1,-1)
return (1)
end
else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
begin
raiserror(15064,-1,-1)
return (1)
end
else if @type = 'F ' -- FK => no physical data storage.
begin
raiserror(15275,-1,-1)
return (1)
end
end

/*
** Update usages if user specified to do so.
*/

if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end


set nocount on

/*
** If @id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @dbsize = Pages used
** @bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = 'E' and
** d.number = 1.
** Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
if @id is null
begin
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)

select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)

select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage

select database_name = db_name(),
database_size =
ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2)+ ' MB')

print ' '
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)

/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages


/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))

select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end

/*
** We want a particular object.
*/
else
begin
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id

/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages


/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id

select name = object_name(@id),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end

return (0) -- sp_spaceused

 

[mutley1]

You could try the following - I can't remember whose site I got it from, but i found it a very handy script. Create a DB called DBA (or amend the 2nd script to the database of your choice) and run the first script to create the table:

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DBSTATS]') AND 
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[DBSTATS]
GO

CREATE TABLE [dbo].[DBSTATS] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[RECORD_TYPE] [int] NOT NULL ,
	[DBNAME] [char] (50) NOT NULL ,
	[DATA_SIZE] [decimal](9, 2) NULL ,
	[DATA_USED] [decimal](9, 2) NULL ,
	[LOG_SIZE] [decimal](9, 2) NULL ,
	[LOG_USED] [decimal](9, 2) NULL ,
	[STAT_DATE] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DBSTATS] WITH NOCHECK ADD
	CONSTRAINT [DF_DBSTATS_STAT_DATE] DEFAULT (getdate()) FOR [STAT_DATE]
GO

Then run this SP to create the SP that will do all the data retrieval for you - when you run it, the output will be a script to gather everything you need for all current databases on the server and insert all the details into dbstats table.

 IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[usp_get_dbstats]') AND 
OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_get_dbstats]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

CREATE PROCEDURE usp_get_dbstats AS

DECLARE @DBSTATS_DB char(3)
SET @DBSTATS_DB = 'DBA'

-- Begin callout A
PRINT 'DECLARE @cmd nvarchar(1024) '

PRINT 'IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N' + char(39) + 
'[tempdb]..[#tmplg]' + char(39) + '))'
   PRINT 'DROP TABLE #tmplg'

PRINT 'CREATE TABLE #tmplg'
PRINT '('
PRINT 'DBName varchar(32),'
PRINT 'LogSize real,'
PRINT 'LogSpaceUsed real,'
PRINT 'Status int'
PRINT ')'

PRINT 'SELECT @cmd = ' + char(39) + 'dbcc sqlperf (logspace)' + char(39)

PRINT  'INSERT INTO #tmplg EXECUTE (@cmd)'
-- End callout A
-- Begin callout B
PRINT 'IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N' + char (39) + 
'[tempdb]..[#tmp_stats]' + char(39 ) + '))'
   PRINT 'DROP TABLE #tmp_stats'

PRINT 'CREATE TABLE #tmp_stats ('
PRINT 'totalextents int, '
PRINT 'usedextents int,'
PRINT 'dbname varchar(40),'
PRINT 'logsize real,'
PRINT 'logspaceused real'
PRINT ')'
PRINT 'go'--End callout B
--Begin callout C
DECLARE AllDatabases CURSOR FOR

SELECT name FROM master..sysdatabases
 
OPEN AllDatabases

DECLARE @DB nvarchar(128)

FETCH NEXT FROM AllDatabases INTO @DB

WHILE (@@FETCH_STATUS = 0)

BEGIN
   PRINT 'USE [' + @DB + ']'
   PRINT 'GO'
   PRINT 'IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N' + char(39) 
+ '[tempdb]..[#tmp_sfs]' + char(39) + '))'
   PRINT 'DROP TABLE #tmp_sfs'
   PRINT 'CREATE TABLE #tmp_sfs ('
   PRINT 'fileid int,'
   PRINT 'filegroup int, '
   PRINT 'totalextents int, '
   PRINT 'usedextents int,'
   PRINT 'name varchar(1024),'
   PRINT 'filename varchar(1024)'
   PRINT ')'
   PRINT 'go'

   PRINT 'DECLARE @cmd nvarchar(1024)'

   PRINT 'SET @cmd=' + char(39) + 'DBCC SHOWFILESTATS' + char(39)

   PRINT 'INSERT INTO #tmp_sfs EXECUTE(@cmd)'

   PRINT 'DECLARE @logsize real '
   PRINT 'DECLARE @logspaceused real '

   PRINT 'SELECT @logsize= logsize FROM #tmplg WHERE dbname = ' + char(39) + @DB + 
char(39)
   PRINT 'SELECT @logspaceused = (logsize*logspaceused)/100.0'
   PRINT '      FROM #tmplg WHERE dbname = ' + char(39) + @DB + char(39)
   PRINT 'SET @cmd = ' + char(39) + ' INSERT INTO #tmp_stats' + char(39) + ' +'
   PRINT '     ' + char(39) + '(totalextents,usedextents,dbname,logsize,logspaceused)' + char(39) + 
' +'
   PRINT '     ' + char(39) + ' SELECT SUM(totalextents), SUM (usedextents),' + char(39) + ' + 
char(39) + ' + char(39) + @DB + char(39) + '+ char(39) + ' + char(39) + ',' + char(39) + ' + '
   PRINT ' CAST(@logsize AS varchar) + ' + char(39) + ',' + char(39) + ' + CAST (@logspaceused 
AS varchar) +'
   PRINT ' ' + char(39) + ' FROM #tmp_sfs' + char(39)
PRINT 'EXEC sp_executesql @cmd'

FETCH NEXT FROM AllDatabases INTO @DB
END --(@@FETCH_STATUS = 0)
--End callout C
--Begin callout D
PRINT 'INSERT INTO ' + @DBSTATS_DB + '.dbo.DBSTATS '
PRINT '  (RECORD_TYPE, DBNAME, DATA_SIZE, DATA_USED, LOG_SIZE, LOG_USED)'
PRINT '    SELECT 1,dbname,totalextents*64/1024 , usedextents*64/1024 ,'
PRINT '           logsize ,logspaceused FROM #tmp_stats'
--End callout D

CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

I've amended the table it writes to with computed columns for % free etc. As i say, I found it somewhere when trawling the net - probably one of the experts on here who wrote it!

Cheers,

M.

 

[aftertaf]

hehe.....
we ended up ripping a bit out of sp_spaceused:

select @dbsize = sum(convert(dec(15),size))
        from dbo.sysfiles
        where (status & 64 = 0)

    select @logsize = sum(convert(dec(15),size))
        from dbo.sysfiles
        where (status & 64 <> 0)

    select @bytesperpage = low
        from master.dbo.spt_values
        where number = 1
            and type = 'E'
    select @pagesperMB = 1048576 / @bytesperpage

    select  database_name = db_name(),
        database_size =
            ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB')

and sticking it in an sp that has an OUTPUT parameter...

thanks guys


Aftertaf
We shall prevail, and they shall not