Db size is not decreasing

Question

Dear All,

In our environment Due to one table Db is growing high.Due to this disk space is is high..We have truncated the table.But still disk is showing same size.

So we have shrieked the DB.But still it is showing same size.Kindly support me on this issue.

Answer 1

try shrinking the individual files using

USE [testDB]
GO
DBCC SHRINKFILE (N'testDB' , 0)
GO

---

Geert Vanhove

Answer 2

have a read this before shrinking the DB

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/13/629059.aspx

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 3

have a read this before shrinking the DB

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/13/629059.aspx

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Of course, don't activate auto shrink for it. But an occasionally shrink is sometimes necessary.

---

Geert Vanhove

Answer 4

if you're using Bulk or Full Recover Mode than database may be shrinked after you've taken a transaction log backup.

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 5

easy way to shrink

1- alter database <databasename> set recovery simple

2- use testdb

go

dbcc shrinkfile (filename,disered size in mb)

3- alter database <databasename> set recovery full

NOTE: If DB is configured with high availability (like logshipping & mirroring). should not be change recovery model 

---

padala

Answer 6

have a read this before shrinking the DB

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/13/629059.aspx

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Of course, don't activate auto shrink for it. But an occasionally shrink is sometimes necessary.

---

Geert Vanhove

I don’t agree with that.. unless it is absolutely sure that the db is NOT going grow ever again then it make sense.. else leave the free space there.. Think about a situation, you shrunk the db and there is only 5 MB free space left and one fine morning  one your developer transfer a 10MB size wroth data into it. The data file has to grow to accommodate the extra 5MB ..right..??. This auto growth typically hurt the system performance ..

My policy.. shirk db only if it’s in readonly mode..

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 7

if you're using Bulk or Full Recover Mode than database may be shrinked after you've taken a transaction log backup.

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Are you sure..???

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 8

have a read this before shrinking the DB

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/13/629059.aspx

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Of course, don't activate auto shrink for it. But an occasionally shrink is sometimes necessary.

---

Geert Vanhove

I don’t agree with that.. unless it is absolutely sure that the db is NOT going grow ever again then it make sense.. else leave the free space there.. Think about a situation, you shrunk the db and there is only 5 MB free space left and one fine morning  one your developer transfer a 10MB size wroth data into it. The data file has to grow to accommodate the extra 5MB ..right..??. This auto growth typically hurt the system performance ..

My policy.. shirk db only if it’s in readonly mode..

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Correct, it depends. Other situation, you just performed a 500GB initial load and after tranfer to staging, you truncate the load tables. Will you keep 500GB unused space in you DB?

And if fragmentation becomes an issue, you need to rebuild your indexes. But there is no magic in regaining diskspace.

BTW if you set autogrowth values correctly, a sporadic growthy will not hurt system performance.

---

Geert Vanhove

Answer 9

if you're using Bulk or Full Recover Mode than database may be shrinked after you've taken a transaction log backup.

Are you sure..???

maybe it was a bit unclear because of the missing context of the original question.

It is not shrinked by log backup but as long as you haven't backuped your transaction, shrinking the database file does not always works immediately - especially this applies to the transaction log file itself too if you want to shrink the log file and the pages must have already been written to disk (eg. by a CHECKPOINT)

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 10

Can you suggest after shrink if you can see the same size of the table or db?

---

Cheers!! Kalps

Answer 11

Can you please locate the file that is causing you issues and tell me if it is the data file (*.mdf) or log file (*.ldf)?

---

Regards, Matt Bowler MCITP, My blog

Answer 12

Hi Kalpana,

We are shrinking Db which is having initial size(450Gb). In that 450 GB, 300 gb is unallocated space.150gb space is using by Data and indexes.

We are trying to shrink the Db but its not shrinking we are seeing same size.At disk level also its taking 450 gb space due to this we are reaching mount point.

When we shrink db  is the initial size come done if there is no data(i.e will it come from 450 to 250 gb)

Kindly suggest me on the same.

Answer 13

Hi Matt,

We are shrinking  .ndf file.

Answer 14

Hi greet,

And if fragmentation becomes an issue, you need to rebuild your indexes. But there is no magic in regaining disk space

we deleted one table data.After that we tried to do shrink but its showing same size.first our db size is 450 in that unallocated is 300.

From the above bold point we can't decrease db size .is it right??

Answer 15

did you perform a shink database or shrink file.

Try something like this

DBCC SHRINKFILE (N'fileName' , 1000)

---

Geert Vanhove

Answer 16

yes we perform same command

Answer 17

please run DBCC UPDATEUSAGE and run sp_spaceused again

---

Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 18

Hi,

Please post the exact command you are using to shrink your data file.  If you do dbcc shrinkfile(logicalfilename) it will only shrink as far as the file was initially sized.  To get it any smaller, provided there's free space, you have to specify a target size to shrink to, i.e. dbcc shrinkfile(logicalfilename, 1)

Also, try growing the file first by 1MB, then have another go at your shrink. I had a similar problem a long time ago and I think that helped, as backwards as it might sound.

---

Thanks, Andrew

Answer 19

Hi Mito,

You might need to check where the free space is - if there is no free space in the ndf then you won't be able to shrink it.

Right click on the database in object explorer and choose reports > disk usage. Look for the Disk Space Used by Data Files section and expand that. Confirm that there is free space in the file you are trying to shrink.

If so please post your exact shrink command, and any output from the messages pane of SSMS.

---

Regards, Matt Bowler MCITP, My blog

Answer 20

Hi matt,

We are using enterprise edition and sql server 2008R2.I tried to check the disk usage as you mentioned in your post.But i am not getting report option when i do Right Click  on DataBase.

Answer 21

Hello Minto,

Table which you deleted is a heap or table with clustered index? If it is heap create a temporary clustered index and drop it then space will be reclaimed . If the table you truncated has lob data you have to move the data to new table-->drop the old table-->rename the new table to old table.

You can follow steps similar to one mentioned in below blog

http://mssqlwiki.com/2012/01/17/how-to-move-the-lob-data-from-one-file-group-to-other/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog Space |Twitter

www.Mssqlwiki.com

Please click theMark as answer button if this reply solves your problem.

Answer 22

Hi,

Check the database size using the following query1 and shrink the database by Querry 2 or Query 3.

Query1

select name,size/128.0 as TotalSize,size/128.0 - CAST(FILEPROPERTY (name,'spaceused')as int)/128.0
as  availablespaceinMB, CAST(FILEPROPERTY (name,'spaceused')as int)/128.0
as  spaceUsedinMB
from sys.database_files

Query2

DBCC SHRINKDATABASE(DATABASENAME);

Query3

DBCC SHRINKFILE(File id,Target Size in MB);

Note :- Shrinking Database is not good. This will create index fragmentation and affect the performance.

Please check the fragmentation before and after database shrink. Apply necessary methods to reduce the fragmentation.

Regards,

SKB