Database Shrink [duplicate]

Question

We have deleted approx 200000 tables and after which we ran Database shrink from GUI. But still database size shows almost 1 TB. Any solution?

Answer 1

I think you need to get a better handle on what you actually have. Here's my recommendations:

First, find out the used/free space within each file:

SELECT DB_NAME() as dbname, type_desc, name as logical_name, 
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
 FROM sys.database_files WITH (NOLOCK)
 ORDER BY type, file_id;

That should tell you the actual size and used/free space within each data file and log file. You may find that deleting thousands of small tables didn't make as much difference as you thought.

Second, find out what tables are using up the most space:

create table #tbl(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50))

exec sp_msforeachtable 'insert into #tbl exec sp_spaceused [?]'

select name, rows,
    convert(int, substring(reserved, 1, len(reserved)-3)) as reserved_kb,
    convert(int, substring(data, 1, len(data)-3)) as data_kb,
    convert(int, substring(index_size, 1, len(index_size)-3)) as index_kb,
    convert(int, substring(unused, 1, len(unused)-3)) as free_kb
 from #tbl
    order by 3 desc

drop table #tbl

This might let you find huge log or archive tables that you hadn't realized were there.

Third, in some cases, you might need to REINDEX your tables to free space allocated to tables

Data in SQL is stored in 8k blocks called "pages". A page is either allocated to something (table or index usually), or is marked as free. Here's another answer that gets into more detail on this point.

If you delete a column of a table, or if you delete non-contiguous rows of a table (like every other row), then you might have only cleared part of each 8k data page, and they won't be freed back to the database.

Doing an index rebuild (including on the clustered index) can combine partially used pages and free up space to the database (depending on fill factor, but that's a more advanced topic).

If the table is a heap (no clustered index), then you'll have to either add a clustered index, or (if you know what you're doing), add a cluster to "reindex", then remove it to leave it as a heap.

After this, run the first query above to see if more space has been freed to the database (it won't yet be freed to the file system).

Fourth, make sure you understand the risks and tradeoffs of shrinking.

Lots of good articles out there about this with scary titles:

• Stop Shrinking Your Database Files. Seriously. Now..
• Why you should not shrink your data files
• Shrinking SQL Server Data Files – Best Practices, and Why It Sucks

In short:

1. Only shrink when you absolutely have to, and only when you're permanently reclaiming the space (huge data purges, change in application behavior, move between environments, etc). Otherwise it'll just grow back to the current size and you're back where you started. Ideally, understand and plan your database growth and size them appropriately ahead of time.
2. Shrinking data files can badly fragment indexes, leading to performance issues. The normal mitigation is to reindex after the shrink.
3. Shrinking is a very high IO operation, so plan accordingly with your application activity, and absolutely never ever ever enable "auto-shrink".

Lastly, never use SHRINKDATABASE, only SHRINKFILE:

Once you've understood everything above, always shrink a specific file, with a specific target size in mind.

Leave tran log files as big as they need to be for your activity. For data files, I usually try to leave 10-20% internal space free. Something like:

DBCC SHRINKFILE (NAME = 'mydatafile', SIZE = 400000MB)

Answer 2

I think you are confusing database file shrinking and compression. Shrink recovers unused space in the underlying database files. If you've deleted a large amount of data from your database and wish to scale down the file size, you using Shrink. If you want the data in your database to be stored in a denser fashion, your turn on page or row compression. You need to see how much data is stored in all your tables and indexes and see if it actually is 1 TB. We distributed our database to redshift and dynamodb so we had an opportunity to shrink the db from 1 TB to 200GB. But that's because we had less data in the db than before.