April 13, 2006 at 1:34 pm
Hello,
I posted this once...but it never showed up...so I will simplify my question.
I need to "auto-purge" old data from an SQL Express database when the 4GB limit is fastly approaching. We use it for collecting welding data from production welding machines. I want to use sp_spaceused to give me the database_size as a variable so I can trigger the deletion of the oldest records in order to free up about 250-500MB of space. First in, first out concept.
My hangup is I don't understand how to get the 2 recordsets supplied by sp_spaceused into a temp table. Can anyone help me get the 2 recordsets from sp_spaceused into a temp table? This is for the entire database...not just a single table.
Thank you,
Leland
April 13, 2006 at 1:50 pm
Take a look at the sp_spaceused_report procedure posted in message
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=265290#bm265442
You can change the final SELECT statement to sum up the #Res table for your result.
You can also remove all the stuff at the top for updating statistics. DBCC UPDATEUSAGE is no longer necessary in SQL Server 2005.
Or you can copy the code for master.sys.sp_spaceused and modify it to remove the resultset you don't need.
-Eddie
Eddie Wuerch
MCM: SQL
April 13, 2006 at 2:27 pm
THANK YOU...I will check it out. Really appreciate your timely response and willingness to do so.
Sincerely,
Leland
April 13, 2006 at 2:39 pm
Thanks so much...but what you provided was for individual tables. This may not be possible...but I need this information in a temp table.
execute sp_spaceused /* no table defined */
Results:
Recordset1
database_name database_size unallocated space
IMPACT 700.00 MB 351.90 MB
Recordset2
reserved data index_size unused
254056 KB 252016 KB 1584 KB 456 KB
Thanks,
April 18, 2006 at 12:05 pm
After further review...the sample procedure that was supplied by Eddie will work just fine. Thanks, Leland
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply