Shrinking the size of a device / database 
Author Message
 Shrinking the size of a device / database

I have created a data device of 60 MB and a log device of 30 MB on a
SQL Server. I have created a database using these devices making a
database of 90 MB. It is now several months later and I want to use
this space for something else, since it turn out the database only
requires 15 MB. How can I shrink the database to go on a data device of
30 MB and log of 15 MB ?

ALTER can only increase the size of the database and when I use
dump/load database, it insists the old database size of 90 MB be
maintained. I have tried bcp, but am unclear on how to use it. The manual
does not give a clear example and the FAQ does not either.

Thanks for your help in advance.

--

Systems Engineer
SHL Systemhouse Inc., Ottawa Branch, 50 O'Connor Street, Suite 501,
Ottawa, Ontario, Canada, K1P 6L2, 613-236-9734



Mon, 02 Mar 1998 03:00:00 GMT
 Shrinking the size of a device / database

The quickest way to do it is to do a
Select database.dbo.Table (table by table )
into newdatabase.dbo.table.
When you copy all the tables into the new database , drop the old
database from the original device , thus freeing up the space on
the old db and moving the original database into the smaller one.
Good Luck,
Mike in CT

PS - do a backup of the original database first , just in case
you run into any unfoeseen problems....



Wed, 04 Mar 1998 03:00:00 GMT
 Shrinking the size of a device / database

Quote:

>I have created a data device of 60 MB and a log device of 30 MB on a
>SQL Server. I have created a database using these devices making a
>database of 90 MB. It is now several months later and I want to use
>this space for something else, since it turn out the database only
>requires 15 MB. How can I shrink the database to go on a data device of
>30 MB and log of 15 MB ?

You cannot shrink a USER database.

BCP out the data.  Drop the database. Create another database of desired
size . Create table schemas and BCP in the data.

Tarlock
Sybase Australia

Quote:
>ALTER can only increase the size of the database and when I use
>dump/load database, it insists the old database size of 90 MB be
>maintained. I have tried bcp, but am unclear on how to use it. The manual
>does not give a clear example and the FAQ does not either.

>Thanks for your help in advance.

>--

>Systems Engineer
>SHL Systemhouse Inc., Ottawa Branch, 50 O'Connor Street, Suite 501,
>Ottawa, Ontario, Canada, K1P 6L2, 613-236-9734



Fri, 06 Mar 1998 03:00:00 GMT
 Shrinking the size of a device / database

Quote:
> The quickest way to do it is to do a
> Select database.dbo.Table (table by table )
> into newdatabase.dbo.table.
> When you copy all the tables into the new database , drop the old
> database from the original device , thus freeing up the space on
> the old db and moving the original database into the smaller one.
> Good Luck,
> Mike in CT

> PS - do a backup of the original database first , just in case
> you run into any unfoeseen problems....

However, you wouldn't end up with any rules, defaults, views, triggers,
user defined datatypes, stored procedures, indexes, groups, users or
permissions.
You will therefore require more than luck.  

Create your new database schema via the scripts you have for it, or use a
schema generator if you don't have scripts.  Then
        INSERT newdb..table1 SELECT * from olddb..table1
for each table.  Or use BCP.  

simon walker
The SQL Workshop
Milton Keynes, UK



Sat, 07 Mar 1998 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. How to shrink the Database size and Database Device size

2. How Do I Shrink The Size Of A Data Device (Newbie)

3. Device size and database size

4. Device size and database size

5. Database size bigger than device size???

6. Help Shrinking Database Device!

7. Shrinking Database devices

8. Can you shrink a Database Device in SS 6.5

9. How to shrink Master database device

10. Shrinking database devices in SQL Server 6.5

11. Help Shrinking a DataBase Device.

12. Database Device shrinking


 
Powered by phpBB® Forum Software