Solved

sp_spaceused and Delphi

Posted on 2010-09-08
5
474 Views
Last Modified: 2012-05-10
Hi I would like to use sp_spaceused procedure to check the size of my database.
I want to save the information about database size in some parameter and if database is larger than 3GB i will erase some data.
Can you show me how can i do it in Delphi.
Thx.
Vaalar
0
Comment
Question by:Vaalar
  • 4
5 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33627280
Use a stored procedure to execute the sp_spaceused procedure and then return the database size or flag indicating whether or not to erase/delete some data.
However, you should be aware that you will not regain the space until after you backup and shrink the database.  In other words, if you execute the procedure and then delete some data, you will still get the same results if you execute the procedure again before you shrink the database . . . even if you execute the procedure the next day.  That could result in your progressively deleting a whole lot more data than you think you are going to.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 33630339
if you are using DBExpress try
var
  DatabaseSize: string;
  MyProc : TSQLStoredProc;

MyProc := TSQLStoredProc.Create(nil);
try
  MyProc.SQLConnection := Your Connection;
  MyProc.ExecProc;
 
  DatabseSize := MyProc.FieldByName('database_size').AsString;
finally
  FreeAndNil(MyProc);
end;
 
Other components should just work in a similar manner
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 33630381
you have to specify the stored procedure name after setting the connection i.e

MyProc.SQLConnection := Your Connection
MyProc..StoredProcName := 'sp_spaceused';
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 33630635
Looks like the sp_spaceused procedure does not return any fields or parameters

The query they use to get the size is basically
declare @dbsize bigint
declare @logsize bigint

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

Using ADOQuery, you can get the desired results as follows

ADOQuery1.SQL.Add('declare @dbsize bigint');
  ADOQuery1.SQL.Add('declare @logsize bigint');

  ADOQuery1.SQL.Add('select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))');
      ADOQuery1.SQL.Add(', @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))');
      ADOQuery1.SQL.Add('from dbo.sysfiles');

  ADOQuery1.SQL.Add('select db_name() as DBName, @dbsize as Size, @logsize as LogSize');
  ADOQuery1.Active := True;
  ShowMessage(ADOQuery1.FieldByName('Size').AsString);
  ADOQuery1.Active := False;
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 33630655
Note that the result is returned in Bytes so divide by 1024 to get MB
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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…

910 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