CODEWITHSUNDEEP
sql-server
Sreedhar
Sreedhar

Reputation: 30015

Removing allocated space for Table - SQL Server

I got a table where in got allocated space of 3gig but have no rows.
How to remove this allocated space ?

Upvotes: 0

Views: 10383

Answers (3)

Hakan Winther
Hakan Winther

Reputation: 526

There are some restriction with TRUNCATE TABLE, and if can't do a truncate then you can rebuild your clustered index to free up the allocated space.

You cannot use TRUNCATE TABLE on tables that:

  • Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
  • Participate in an indexed view.
  • Are published by using transactional replication or merge replication.

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead. If this is the case then you need to reseed the table to continue the identity increment from where it was.

If the table does not have a clustered index you can use TABLOCK hint in your DELETE statement to free up the allocated space.

DELETE FROM table WITH (TABLOCK)

If table is using a clustered index you can rebuild the index to free the space.

Upvotes: 3

Coentje
Coentje

Reputation: 520

These should do it, they will shrink your database files. If the table is empty the reserved space for the table will be released with the following commands

DBCC SHRINKDATABASE ('DBName')
DBCC SHRINKFILE ('LogicalFileName')

Upvotes: 1

Beep beep
Beep beep

Reputation: 19141

You can do:

truncate table [table_name]

Then in Sql Server Express Manager, right click on the database and go to tasks->shrink->database/files (do both). Should clear you up.

Upvotes: 1

Related Questions