Solved

sp_spaceused and Delphi

Posted on 2010-09-08
5
477 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

810 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