September 10, 2017 at 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
September 10, 2017 at 8:04 pm
sqlguy80 - Sunday, September 10, 2017 6:44 PMHi ,
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
Change is inevitable... Change for the better is not.
September 10, 2017 at 8:49 pm
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
September 10, 2017 at 9:37 pm
What command(s) are you using to shrink the files? If you're using the GUI, what options are you setting?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2017 at 10:56 pm
dbcc shrinkfile (1,1024)
goal is to bring the data file back to 1 GB from the init size 6 GB.
September 11, 2017 at 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.
September 11, 2017 at 8:22 am
sqlguy80 - Monday, September 11, 2017 7:12 AMImran,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
Change is inevitable... Change for the better is not.
September 11, 2017 at 9:57 am
Hi Jeff,
Yes I rebuilt all indexes and updated all stats hoping that would help with this shrink file operation.
Thanks
September 11, 2017 at 9:25 pm
sqlguy80 - Monday, September 11, 2017 9:57 AMHi 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
Change is inevitable... Change for the better is not.
September 11, 2017 at 10:24 pm
yes sir. I did it again to be safe.
September 11, 2017 at 11:55 pm
Imran-fast - Monday, September 11, 2017 1:53 AM
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply