sp_spaceused and sp_HelpDB | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

sp_spaceused and sp_HelpDB

Hi, Am new to the system stored procedures in SQL Server 2000.
How do I make the stored procedure return only the db_size of a database using sp_spaceused or sp_helpdb.
I want to store the db size value in a variable and check the free unallocated space and then load the data into tables. Appreciate any help on this as this is very urgent. thanks in advance
http://www.sqlteam.com/item.asp?ItemID=282 for similar information to findout biggest table in a database. http://www.nigelrivett.net/SpaceUsedAllTables.html to get information for all tables space information. Other easy option is to run SP_HELPTEXT SP_HELPDB and understand the bit of getting db_size value from the text. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
This will give you your db size in MB.
I multiply by 8 because these are the 8-K pages. use yourdatabasenamehere
declare @dbsize int set @dbsize = (select (sum(size)) * 8 / 1024 from sysfiles) select @dbsize
Thanks so much for the quick replies.
I also tried writing a small piece of code but I get the error Server: Msg 8114, Level 16, State 5, Line 19
Error converting data type nvarchar to numeric. Here’s the code declare @db_size dec(15,0),
@freepages dec(15,0) create table #My_DBSize
(
dbname sysname,
dbsize nvarchar(13) null,
owner sysname,
dbid smallint,
created nvarchar(11),
dbdesc nvarchar(600) null,
cmptlevel tinyint
)
insert into #My_DBSize
exec sp_helpdb
select @db_size = cast(dbsize as dec(15,0)) from #My_DBSize Thanks in advance

Declare dbsize variable as int. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>