July 28, 2021 at 8:42 pm
While trying to shrink a database using the command "dbcc shrinkdatabase ([db_name])", I get the following message
"DBCC SHRINKDATABASE: File ID 1 of database ID 13 was skipped because the file does not have enough free space to reclaim." That seems to be case with few other DBs too. I was web searching and came across a link which suggested to execute "DBCC SHRINKDATABASE ([db_name], TABULAR)" and I get the following table:
Dbid Field CurrentSize MinimumSize UsedPages EstimatedPages
13 1 12800 12800 448 448
13 2 6400 6400 6400 6400
There is space for the db files to expand. I was looking into options of shrinkdatabase command to see if it could skip a shrink if there is no space to be shrunk. Do you know of any such options? I am a bit new to these shrinkdatabase options.
PS. We have to do the shrinks on these databases because a bunch of tables get created and then dropped as part of scheduled jobs.
Thanks a lot, Ram
July 28, 2021 at 8:43 pm
You should still never shrink a database (at the db level), only specific db files (at the file/file_id level).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 28, 2021 at 9:15 pm
I agree with Scott and I have a question for you Ram too - how often do these jobs run?
This is relevant because if you run the jobs nightly (for example) to create new tables, populate them, then move the data out of those tables, drop the tables, and finally shrink the database, it is just going to grow again the next day. I would MUCH rather keep the disk space consumed by the instance so I know I have enough free space for the next run.
Now, if these jobs are run once per year, and the database is a read-only database, then I may shrink things, but like Scott, I only shrink the db files and not the whole database. I very rarely shrink a live system because it grew to that size at one point for a good reason, so it is likely to just grow all over again.
On my dev/test instances, I shrink files once I do the restore so I am not wasting disk. Dev/Test has a LOT fewer inserts so the autogrow can be set lower and I waste a lot less disk with that. But, like Scott said, I am shrinking the file not the database.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 28, 2021 at 10:06 pm
Thanks a lot for your time Scott and Brian. Your point that of not shrinking the db/files unnecessarily is noted.
But I am unable to understand how this error has occurred in our system when the same commands were working fine till last week. The processes have been running there for over a year.
July 28, 2021 at 10:10 pm
My understanding of the error - the particular database file has no room to shrink. If there is 0 bytes free in that database file (or near 0), then it will not be able to shrink and that is what the error is telling you.
If you look at the results in the table you provided it says there is no free space. Here is what you posted:
Dbid Field CurrentSize MinimumSize UsedPages EstimatedPages
13 1 12800 12800 448 448
13 2 6400 6400 6400 6400
Current Size is EQUAL to the Minimum Size, therefore there is nothing you can shrink.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 28, 2021 at 10:57 pm
If you have very volatile tables like that, you should put them in a separate filegroup and then shrink only the files associated with that filegroup.
You especially do not want to shrink the log file unnecessarily!
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 29, 2021 at 3:31 pm
Using SHRINKDATABASE attempts to shrink all of the files associated with the database - to their initial size. This is very important to consider for transaction log files. If the initial size of the file is 1MB - the shrink will cause the log file to shrink to that size if it can. Upon completion that file will immediately hit an auto growth as soon as there is any activity in that database.
Based on the results you posted - you either have a database in full recovery that has not run a log backup, or there is an open transaction preventing the transaction log from being truncated.
It sounds like you have a process in place to automatically shrink the database on a schedule. That is an incredibly bad practice - as it fragments the indexes and forces the transaction log to regrow to support new activity. You really should stop that practice, grow the files out to their expected size and leave it alone.
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
August 5, 2021 at 4:49 am
The error message is certainly misleading, but when you look at the TABULAR result, the reason for the error is clear. There simply isn't any free space to reclaim. You would expect SQL to simply let you know it's not going to shrink the database BUT....
The reality is the SHRINKDATABASE actually needs the free space otherwise it can't do a shrink. If there is no free space SQL would first need to EXPAND the database to provide an area to start moving pages/extents, just to release that space again.
What the error message is essentially saying is "There's no free space for me to use for the shrink process"
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 9, 2021 at 6:18 am
You can check this forum reference: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fb8b28ab-ea2e-4191-b92b-23e835e5bc5f/dbcc-shrinkdatabase-fails?forum=sqlkjmanageability
August 9, 2021 at 7:35 am
This was removed by the editor as SPAM
August 12, 2021 at 2:37 am
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply