• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

sp_spaceused and Delphi

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
Vaalar
Asked:
Vaalar
  • 4
1 Solution
 
8080_DiverCommented:
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
 
Ephraim WangoyaCommented:
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
 
Ephraim WangoyaCommented:
you have to specify the stored procedure name after setting the connection i.e

MyProc.SQLConnection := Your Connection
MyProc..StoredProcName := 'sp_spaceused';
0
 
Ephraim WangoyaCommented:
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
 
Ephraim WangoyaCommented:
Note that the result is returned in Bytes so divide by 1024 to get MB
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now