Solved

sp_spaceused and Delphi

Posted on 2010-09-08
5
472 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Note that the result is returned in Bytes so divide by 1024 to get MB
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 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

12 Experts available now in Live!

Get 1:1 Help Now