Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sp_spaceused and Delphi

Posted on 2010-09-08
5
Medium Priority
?
488 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:Ephraim Wangoya
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:Ephraim Wangoya
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:
Ephraim Wangoya earned 2000 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:Ephraim Wangoya
ID: 33630655
Note that the result is returned in Bytes so divide by 1024 to get MB
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

876 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