SQL Server Performance Forum – Threads Archive

Check DB size and unallocated space

Hi, I am a newbie as DBA. I would like to know how to check the size of the database and also check the unallocated space.
Basically this is being done in a stored procedure in SQL 6.5. I need to convert this code to SQL 2000. Here’s the code snippet in 6.5.
Any help is highly appreciated. Thanks in advance — ***************************************************************************
— Check available space
— *************************************************************************** — 1. Get database size and database unallocated space (in pages) [email protected](15,0),
@freepagesdec(15,0) SELECT @dbsize = SUM(convert(dec(15),size))
FROM master..sysusages
WHERE dbid = db_id() SELECT @freepages = (
@dbsize –
(SELECT SUM(CONVERT(dec(15),reserved))
FROM sysindexes
WHERE indid IN (0, 1, 255) ) ) — 2. Get max row per page for both tables [email protected],
@iRowPage2int SELECT @iRowPage1 = rowpage FROM sysindexes WHERE name = ‘DB_TDADMLoad..tb_DMLPricing’
SELECT @iRowPage2 = rowpage FROM sysindexes WHERE name = ‘DB_TDADMLoad..tb_DMTPricing’ — 3. Check if required number of rows can be allocated DECLARE @needpagesdec(15,0) SELECT @needpages = CONVERT( dec(15,0),
CEILING( CONVERT(real,@iNumberRecords) / CONVERT(real,@iRowPage1) ) +– pages required for DMLPricing
CEILING( CONVERT(real,@iNumberRecords) / CONVERT(real,@iRowPage2) ) — pages required for DMTPricing
) IF @freepages < @needpages
BEGIN
[email protected] = 401004,
@iError = 1
GOTO proc_exit
END

Why not use SP_HELPDB & SP_SPACEUSED. 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.

]]>