Solved

sp_spaceused - database_size - sql 2000

Posted on 2010-09-15
5
475 Views
Last Modified: 2012-05-10
There is store procedure sp_spaceused this give information about database size and some other database related information. One of the output column is database_size

Does anyone know in which table "database_size" field is stored? thanks
0
Comment
Question by:tech2010
  • 2
  • 2
5 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33683333
It's not directly stored in that way.  That value is likely a summation of other values in system tables.

In SQL 2000, I think you have to use dbo.sysindexes to get the relevant sizes.  Note that even a table w/o any index is in sysindexes, with an indid = 0 (0 = heap, 1 = clus index).
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33683368
Its from dbo.sysfiles and the query involved here:

select sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) dbsize,
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) logsize
            from dbo.sysfiles
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33683480
You can always go to master and look at the code for
sp_spaceused

Yeah, sysindexes wouldn't have log size, only data size.
0
 

Author Comment

by:tech2010
ID: 33683600
how can sum data + log size and then use xp_sendmail to fire an email if total sum is > 100
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 33684197
>> how can sum data + log size

It can be summed like this

select sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) total_size
from dbo.sysfiles

Send the result of the above query using xp_sendmail like:

EXEC xp_sendmail @recipients = 'emailid',
   @query = 'select sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) total_size from dbo.sysfiles',
   @subject = 'Result',
   @message = 'Result of the query:',
   @attach_results = 'TRUE', @width = 250
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now