Solved

sp_spaceused - database_size - sql 2000

Posted on 2010-09-15
5
481 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:Scott Pletcher
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:Scott Pletcher
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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 …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 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