2

I have a problem when it comes to delete a large table from my database (170GB). When i "elete this large table by right click > delete I do not get the storage space free again. Of course the table is off the database but the database needed space does not shrink

Can anyone tell me what is wrong?

user3868224
  • 363
  • 1
  • 6
  • 19
  • You have to actually shrink the database itself. Read about it [here](http://technet.microsoft.com/en-us/library/ms189493(v=sql.105).aspx). And [here](http://dba.stackexchange.com/questions/11310/to-dbcc-shrinkdatabase-or-not-to-dbcc-shrinkdatabase-thats-the-question) is a great link about shrinking your db. – Andrew Aug 11 '14 at 19:31
  • 1
    Dont shrink the db if you plan to add data to the same db. – Mihai Aug 11 '14 at 19:33
  • There is **nothing wrong** - that's the way SQL Server works! It will **not** give back the space to the operating system, just to have to allocate it again later on for something else; SQL Server holds on to that space and uses it for other purposes. – marc_s Aug 11 '14 at 19:47
  • Well thank you, all i need to know. – user3868224 Aug 12 '14 at 06:09

2 Answers2

2

Tables are stored in table spaces. These are allocated to the database, regardless of whether the space is actually used to store tables (or indexes or anything else).

When you delete the table, you have freed space in the table space. The space is available to the database for your next table (or whatever). You need to either drop or shrink the table space to release the space back to the operating system.

A place to start is with dbcc shrinkfile, documented here.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    "Table space" is an Oracle db concept. Isn't it? – Dave Mason Aug 11 '14 at 19:40
  • In SQL Server Rows are stored on Pages --> 8 Pages are stored in an extent and Extents and stored in files (.mdf, .ndf). Never heard of table space :S – M.Ali Aug 11 '14 at 19:44
  • @DMason . . . I guess the proper SQL Server terminology is "file groups". Oracle, Postgres, MySQL, and DB2 use the term "table space", which I just use generically as the pre-allocated space for database tables and perhaps other database objects. – Gordon Linoff Aug 11 '14 at 22:01
1

Short answer:

Run sp_clean_db_free_space, before shrinking. My assumption is that you've tried shrinking the files, but if not that question has been answered.

Parenthetical statement:

You shouldn't shrink databases if you can avoid it.

Long answer: The behavior you see is the result of Ghost Records. To understand more about this at a system level read this article: Inside the Storage Engine: Ghost cleanup in depth.

Community
  • 1
  • 1
Jim V.
  • 2,137
  • 16
  • 14