SQL Server - DB size not reduced even after Truncating Table

Asked By VY VY on 13-Jun-12 06:38 AM

Dear Sir

1. My Initial DB

size is 6.622070312 GB,


2. Aftern

Truncation to most of table size reduced to 2.16638671875 GB.


Note:- Whereas i am seein report

of Disk Usage by Top table On truncated Table DB. I am Summing Data (KB) + Indexes (KB) = 0.026252747 GB


My Question

is why the size is 2.16638671875 GB after Truncation which is still very very high. Whereas .LDF file is considerable reduced to 1 MB

Regards

kalpana aparnathi replied to VY VY on 13-Jun-12 06:44 AM
hi,

Do you want to release the space to os?

if yes then try DBCC SHRINKDATABASE ( <DB name>, TRUNCATEONLY )

Regards,
Venkat K replied to VY VY on 13-Jun-12 07:11 AM
1. Do you have only one table in your DB?
2. I don't know how you are calculating the size of the table.

You can't calculate the space of table data and compare with Database size, since you have many other objects in the database.

If you are not bother about the table data and you want to truncate the DB Size
user

DBCC SHRINKDATABASE command explained how to use here: http://msdn.microsoft.com/en-us/library/ms190488.aspx

You can also use DBCC SHRINKFILE  to shrink the mdf or log files : http://msdn.microsoft.com/en-us/library/ms189493.aspx

Regards,
Venkat
VY VY replied to kalpana aparnathi on 13-Jun-12 07:20 AM
Yes i tried that also still the size same. its not reduced.
James Murray replied to VY VY on 13-Jun-12 01:45 PM
"My Question is why the size is 2.16638671875 GB after Truncation which is still very very high. Whereas .LDF file is considerable reduced to 1 MB?"

The LDF is a collection of uncommited actions to your database if your database recovery model is full. When you perform a transaction log backup (which you may have done a full backup prior to running this command) it will reduce the LDF to 0 byte.