Shell Programming and Scripting

I want to write a script to ensure we have enough space in test to bcp in tables from production. Currently we bcp tables out of a sybase database in production and bcp in the table data to a test environment. We use isql and run the stored procedure sp_spaceused against each of the tables in the production environment; then we run sp_spaceused against the same tables in the test environment. We then want to compare the space in production with the space in test - and if the space of the tables in production is greater than the space available in test, send an email saying we need more space for that table.

We're a little confused on which columns returned by sp_spaceused would give us an accurate result of the space used in prod and required in test. I think it's the reserved space column in production minus the reservered space column in test that will give the amount of space needed for that table in our test environment. The person I'm working with on these scripts thinks it's the data size PLUS the index size that's in production MINUS the data size plus the index size in test that will give a more accurate result.

When we compare the space on production versus test, which colunm(s) from sp_spaceused will give the correct number? Any suggestions would be greatly appreciated!

Thanks in advance

You probably already know, but make sure you have up to date data from spaceused - The info is only as good as the last time update stats was run on the table.

sp_spaceused displays estimates of the number of data pages, space used by a specified table or by all tables in the current database, and the number of rows in the tables. sp_spaceused computes the rowtotal value using the rowcnt built-in function. This function uses a value for the average number of rows per data page based on a value in the allocation pages for the object. This method is very fast, but the results are estimates, and update and insert activity change actual values. The update statistics command, dbcc checktable, and dbcc checkdb update the rows-per-page estimate, so rowtotal is most accurate after one of these commands executes. Always use select count(*) if you need exact row counts.

from: http://manuals.sybase.com/onlinebook...pt=117666;uf=0

I usually add data and index together for estimates. Or better yet, tell management dev needs to match prod and get the databases the same size.