Resizing an existing DB

I created a DB with a preallocated size of 20GB and I need to shink
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.
Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting.google.c om...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.

Resizing an existing DB

I created a DB with a preallocated size of 20GB and I need to shink
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in
Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting
.google.com...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.

Reset Increment Seed

I'm still in the development stage and am frequently deleting all data from all tables and then filling those tables anew. However, the increment seed for identifying fields doesn't reset to 1 (or 0--not sure which). While not important for operation of the database, I would prefer that the field identifiers start with 1 when I am ready to release the database for operation. Is there a way to do this?

I can generate scripts to rebuild the database structure to do this, but scripts aren't generated for database diagrams and the graphical representation of the table structure is very useful.

Hi Joe,

Truncate table reset the indentity. Truncate table is faster and need no transaction logs.

regards

Laurent

|||

You should take a look at DBCC CHECKINDENT in Books Online:

eg DBCC CHECKIDENT(YourTable, RESEED, 0)

HTH!

|||

Exception is that TRUNCATE TABLE will not work for table with reference to foreign keys. Almost all the table have references to foreign keys. Even when none of the tables have data in them, you still can not truncate the table.

Will try

DBCC CHECKIDENT(myTableName,RESEED,0) WITH NO_INFOMSGS

Reset Increment Seed

I'm still in the development stage and am frequently deleting all data from all tables and then filling those tables anew. However, the increment seed for identifying fields doesn't reset to 1 (or 0--not sure which). While not important for operation of the database, I would prefer that the field identifiers start with 1 when I am ready to release the database for operation. Is there a way to do this?

I can generate scripts to rebuild the database structure to do this, but scripts aren't generated for database diagrams and the graphical representation of the table structure is very useful.

Hi Joe,

Truncate table reset the indentity. Truncate table is faster and need no transaction logs.

regards

Laurent

|||

You should take a look at DBCC CHECKINDENT in Books Online:

eg DBCC CHECKIDENT(YourTable, RESEED, 0)

HTH!

|||

Exception is that TRUNCATE TABLE will not work for table with reference to foreign keys. Almost all the table have references to foreign keys. Even when none of the tables have data in them, you still can not truncate the table.

Will try

DBCC CHECKIDENT(myTableName,RESEED,0) WITH NO_INFOMSGS

sql