msdb data file shrinkfile - Large Database

Question

msdb data file shrinkfile - Large Database

sqlguy80

SSCrazy

Points: 2765
More actions
September 10, 2017 at 6:44 pm

#339061

Hi ,
The msdb data file on a prod db server is 6 GB, Though I truncated the large tables when I try to shrink the data file I get the below error:
Any idea?
File ID of database 4 cannot be shrunk as it is either being shrunk by another process or is empty!
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

sqlguy80 - Sunday, September 10, 2017 6:44 PM
Hi ,
The msdb data file on a prod db server is 6 GB, Though I truncated the large tables when I try to shrink the data file I get the below error:
Any idea?
File ID of database 4 cannot be shrunk as it is either being shrunk by another process or is empty!
Thanks

It sounds to me like you started a shrink somewhere along the line or someone set it to "auto-shrink" (which should NEVER be done).

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

sqlguy80 SSCrazy Points: 2765 More actions · Answer 2

Hi Jeff,
The initial size of msdb data file is 6 GB and the tables are very small.

No matter what I do , to shrink the initial size doesnt become less in size..1 GB or so?

Any idea.

Thanks Again

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

What command(s) are you using to shrink the files? If you're using the GUI, what options are you setting?

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

sqlguy80 SSCrazy Points: 2765 More actions · Answer 4

dbcc shrinkfile (1,1024)
goal is to bring the data file back to 1 GB from the init size 6 GB.

Imran-fast SSC Enthusiast Points: 130 More actions · Answer 5

Dear sqlguy80 ,
Try to increase the size of the datafile by few MB.
then run the shrink command.

sqlguy80 SSCrazy Points: 2765 More actions · Answer 6

Imran,

Thank you so much, That worked! Your great.

Any logic behind that? I couldnt find this anywhere in google, most folks said recreate msdb, restore etc! but this worked like a charm.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

sqlguy80 - Monday, September 11, 2017 7:12 AM
Imran,
Thank you so much, That worked! Your great.
Any logic behind that? I couldnt find this anywhere in google, most folks said recreate msdb, restore etc! but this worked like a charm.

The next thing is, you have to at least REORGANIZE your indexes because SHRINKFILE doesn't just horribly fragment your database (which usually doesn't matter) but it does some other weird stuff behind the scenes that may cause some rather nasty performance issues.

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

sqlguy80 SSCrazy Points: 2765 More actions · Answer 8

Hi Jeff,

Yes I rebuilt all indexes and updated all stats hoping that would help with this shrink file operation.

Thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

sqlguy80 - Monday, September 11, 2017 9:57 AM
Hi Jeff,
Yes I rebuilt all indexes and updated all stats hoping that would help with this shrink file operation.
Thanks

It's not supposed to help with the shrink operation. It's supposed to clean up the mess afterwards. So just to be sure, you did the defrags AFTER the shrink, correct?

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

sqlguy80 SSCrazy Points: 2765 More actions · Answer 10

sqlguy80

SSCrazy

Points: 2765

September 11, 2017 at 10:24 pm

#1959043

yes sir. I did it again to be safe.

GroverVivek SSCommitted Points: 1674 More actions · Answer 11

Imran-fast - Monday, September 11, 2017 1:53 AM
Dear sqlguy80 ,
Try to increase the size of the datafile by few MB.
then run the shrink command.

Good Solution. How increasing the size of data file fixed the issue? Means shink operation would need some available free space for its operation?

Regards
VG