Delete massive records and still can't shrink db

Question

Post reply

Delete massive records and still can't shrink db

WhiteLotus

SSChampion

Points: 11963
More actions
October 24, 2016 at 8:30 pm

#318366

Hi All,
I just deleted massive unnecessary records in a database ( around 1 million records ) but unfortunately I am still not able to shrink data file ( the size is around 200 GB which consumes most of the drive)
Log back up has been performed every hour on that db but it is not helpful .
I am wondering how do I shrink this naughty database ?
Any feedback are highly appreciated
Many thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63198 More actions · Answer 1

pietlinden

SSC Guru

Points: 63198

October 24, 2016 at 8:55 pm

#1908503

What message was returned when you tried it?

WhiteLotus SSChampion Points: 11963 More actions · Answer 2

Thanks for the reply . Much appreciated

No message because when I right click on that db and choose Shrink -> data file . It shows up a shrink file dialog box telling me that available free space is 0 % .

So that’s why I can’t shrink at all ?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

WhiteLotus (10/24/2016)
Thanks for the reply . Much appreciated
No message because when I right click on that db and choose Shrink -> data file . It shows up a shrink file dialog box telling me that available free space is 0 % .
So that’s why I can’t shrink at all ?

After you did your massive delete, which does NOT compress partially full pages, did you rebuild the indexes (all of them) on the tables you did the deletes on? If the table has blobs in it, you'll need to do a reorganize the indexes that include those blobs (which will also include the clustered index).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

HanShi SSC-Dedicated Points: 33506 More actions · Answer 4

Does the database consists of multiple files and/or multiple filegroups? When you want to shrink databasefiles using the GUI it will default show only the first datafile of the PRIMARY filegroup. Please execute the query below to show the size and usage of all files within the current database. Post the results if you need additional assistance.

SELECT

[name]

, DB_ID() as [DatabaseID]

, [type_desc] as [Type]

, /128.0 as [SizeMB]

, fileproperty([name],'SpaceUsed')/128.0 as [SizeUsedMB]

, (/128.0) - (fileproperty([name],'SpaceUsed')/128.0) as [FreeMB]

, [physical_name] as [FilesystemPath]

FROM sys.database_files

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Houston, the OP has left the building! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Perry Whittle SSC Guru Points: 233792 More actions · Answer 6

Jeff Moden (10/25/2016)
Houston, the OP has left the building! 😀

Probably had a Eureka moment after reading the replies 😀

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

WhiteLotus SSChampion Points: 11963 More actions · Answer 7

Thanks for the reply . Much appreciated .

Only 1 filegroup which is primary and 1 data file

The result of your query is as below ( I cut some information but I feel below are the info that you need)

TypeSizeMBSizeUsedMBFreeMB FilesystemPath

10ROWS213198.250000213197.6250000.625000

10LOG29620.000000951.68750028668.312500

HanShi SSC-Dedicated Points: 33506 More actions · Answer 8

WhiteLotus (10/27/2016)
Thanks for the reply . Much appreciated .
Only 1 filegroup which is primary and 1 data file
The result of your query is as below ( I cut some information but I feel below are the info that you need)
[font="Courier New"]Type.....SizeMB...........SizeUsedMB.......FreeMB
ROWS.....213198.250000....213197.625000....0.625000
LOG......29620.000000.....951.687500.......28668.312500[/font]

The figures above show you have indeed no free space in the DATA file.

Did you do the INDEX maintenance as Jeff Moden suggested?

Can you (double) check if the rows are indeed deleted? Perhaps the delete statements failed and were rolled back. Please check the number of rows in the tables? Are they what you expect it to be after the removal?

Thinking out-of-the-box: has there been a restore of the database after you have deleted the rows?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

WhiteLotus SSChampion Points: 11963 More actions · Answer 9

Thanks for the reply . Much appreciated

I feel It has nothing to do with rebuilt index as I have just rebuilt all indexes in that database and still can’t shrink them

No one restore the database after deleting ..

Any other possible things that I can do ?

Thank you