Shrink database issue

Question

Shrink database issue

don075

SSC-Addicted

Points: 421
More actions
October 3, 2022 at 6:19 pm

#4094993

In my database, I have few temp tables that total up to 50GB in size. I already removed the data from the table. When I set the shrinkdatabase in my maintenance plan, it runs successfully but doesn't shrink the database.
I tried dbcc shrinkfile(1,truncateonly) and got the same result.
However, from sql management studio when I right click the database and select shrink database, it takes a while and shrinks the database. I notice the file size reduces by 50GB.
my question is why it's not doing this with the maintenance plan shrink option or with the sql command?
Is there any other command that I could use to shrink it?
Appreciate any help on this.

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

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

ScottPletcher SSC Guru Points: 101095 More actions · Answer 1

Best would be not to shrink the main db at all. If possible, add a secondary filegroup, create the temp tables in there, then shrink only the file(s) in that secondary filegroup.

For your shrinkfile command, don't specify "truncateonly"; either specify the known size in mb you want to shrink to or leave it off for max shrink:

DBCC SHRINKFILE(1)

I can't help you on the maintenance plan shrink, since I've never used a mp.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

Scheduling a database shrink is a bad idea and should be avoided. There are times when performing a manual shrink may be necessary - but it should only be done when you know the database will never grow to utilize that additional space.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

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

Let's ask the question... how often do the "temp tables" appear? Let's also ask, why do you have "temp tables" of such a size to begin with?

--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)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

Jeff Moden wrote:

Let's ask the question... how often do the "temp tables" appear? Let's also ask, why do you have "temp tables" of such a size to begin with?

The reason I'm asking these questions is because it sounds like you may be concentrating on the wrong issue to begin with.

--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)

don075 SSC-Addicted Points: 421 More actions · Answer 5

All tables in the database should keep data only for 15 days. There is an automated task by the software vendor to delete data older than 15 days. But this process doesn't release the space back to OS. And it keeps growing and our network admin keeps adding additional space on the hard drive. With the deletion of old data, db size should not grow beyond 20GB. Since the space is not returned to OS, last time db size was 120GB and growing.

I want to shrink the database to release the space back to OS.

jasona.work SSC Guru Points: 50117 More actions · Answer 6

That makes it sound like the data isn't being "deleted" in the database, then. While the files wouldn't shrink, the space IN the DB would be marked as "free" space and the next time one of those temp tables gets spun up it SHOULD use said free space.

Think of your database file like a sheet of paper that gets data written to it, when you "delete" data, that erases data from the paper, which you can now write on again.

You probably should reach out to the software vendor to find out why their process isn't doing what it should and why the database just keeps on growing if they're "deleting" data...

don075 SSC-Addicted Points: 421 More actions · Answer 7

don075

SSC-Addicted

Points: 421

October 27, 2022 at 1:47 pm

#4105327

Thanks for the response. Will contact the vendor.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 8

It could also be that that data isn't being deleted in the same order as the Clustered Index. If that's true, you can recover the space left by the deletes simply by rebuilding the Clustered Index. That should leave only a small amount of extra space from the rebuild while returning space for use from the tables themselves.

For sure, most non-clustered indexes aren't going to be in the correct order for painless deletes and will also need to be rebuilt.

Don't look for logical fragmentation when trying to decide which tables/indexes need to be rebuilt due to the deletes. Especially for indexes in a different order than the deletes, there may be zero or very little logical fragmentation. Look for at the percent of page fullness, instead.

--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)

don075 SSC-Addicted Points: 421 More actions · Answer 9

don075

SSC-Addicted

Points: 421

October 27, 2022 at 6:54 pm

#4105500

Thanks for the suggestions. Much appreciated.