Empty contents of database

Hi All,
We have an SQL database that is filled by a Cisco call accounting program.
This program can only put data in a database which is less then 1.5GB in
size. Currently the database is 1.5GB and so the program can no longer keep
data.
What we wish to do is get rid of all records from this database, but leave
the table structure, etc.. in tact. We did try using just a blank database,
but the program does not work in that situation..
To that end, how can we empty the SQL database of records without upsetting
any of the other database properties.
ThanksHi
This sounds like you may be using MSDE which has a 2GB limit. You could
upgrade to a version of SQL Server that does not have this restriction!
If you do need to delete the data, make sure that you don't delete
static/configuration data that is required by the application to function
correctly, which could be why the empty database did not work (assuming you
did have tables in there!).
If you are deleting data from a table you will need to delete data from any
table that references it with a foreign key unless the FK has cascading
deletes, or alternatively you can disable foreign keys before deleting the
data and then re-instating them.
This may help you find which tables may be used most and therefore not
static data!
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
How to find foreign keys:
http://www.aspfaq.com/show.asp?id=2520
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
If you are deleting a large amount of data you may want to set the recovery
mode to simple during the excercise. You may want to TRUNCATE the table
rather than deleting rows. Truncation will reset identity values, you may
have to reset the identity values separately if you use delete.
Once you have a working template database, you can back it up and then you
will only need to restore the template database instead of deleting data the
next time it gets full.
John
"Luke Cassar" wrote:
> Hi All,
> We have an SQL database that is filled by a Cisco call accounting program.
> This program can only put data in a database which is less then 1.5GB in
> size. Currently the database is 1.5GB and so the program can no longer keep
> data.
> What we wish to do is get rid of all records from this database, but leave
> the table structure, etc.. in tact. We did try using just a blank database,
> but the program does not work in that situation..
> To that end, how can we empty the SQL database of records without upsetting
> any of the other database properties.
> Thanks