August 20, 2010 at 4:50 am
Hi Experts,
When I try DBCC SHRINKFILE with EMPTYFILE i am getting error
Msg 2556, Level 16, State 1, Line 1
There is insufficient space in the filegroup to complete the emptyfile operation.
Please find DB structure below
FILEID FILE_SIZE_MB SPACE_USED_MB FREE_SPACE_MB NAME
------ --------------------------------------- --------------------------------------- --------------------------------------- ---------------
1 10.00 1.44 8.56 Test_dat
2 8.13 7.58 0.55 Test_log
3 3.00 0.06 2.94 TEST_QUAD
4 24.50 24.50 0.00 TEST_SEC
5 13.00 6.63 6.38 TEST_TRI
6 5.00 0.06 4.94 Test1data
August 23, 2010 at 10:57 pm
You should make sure that other files in the same filegroup has sufficient space to be able to move the data from the file being emptied.
August 25, 2010 at 4:03 am
DBCC shrinkfile also require space to do the shrinkage
take log backup ( if it is in FULL recovery) then try to do shrinking
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 25, 2010 at 5:28 am
take the full backup & restore to the server which has good amount of disk space & move all files to the same file group & then you can run shrink file with empty that should help.
Thanks
August 26, 2010 at 3:39 am
As "Bhuvnesh" suggested, try Log backup and then do shrink operation.
December 28, 2016 at 7:59 am
so let me get this straight. I'm running into this same issue. We have about 90 db's in sql server 2014 enterprise avail group. The drive our transaction logs are on is at 9% free space which isn’t that bad as it takes a week or so to drop another percent. Nonetheless, I’m trying to free up some space. It turns out the guy who set this up is no longer here. He set the maintenance plan to back up only 17 of the 90+ databases. Have no idea why. I suspect he had a few selected in the maintenance plan then as databases were added over time they were not added to the maintenance plan. Regardless, I’m trying to free up space. Simply taking backups of the transaction logs who aren’t in the maintenance plan will free up space in the log file itself however it won’t release the space on the drive correct?
Also, can anyone think of a valid reason why he wouldn’t have selected “all user databases” when he created the maintenance for the log files? I have to assume he had a reason.
December 28, 2016 at 8:00 am
sorry, moving this to sql server 2014 admin area..
December 28, 2016 at 11:53 am
Snargables (12/28/2016)
so let me get this straight. I'm running into this same issue. We have about 90 db's in sql server 2014 enterprise avail group. The drive our transaction logs are on is at 9% free space which isn’t that bad as it takes a week or so to drop another percent. Nonetheless, I’m trying to free up some space. It turns out the guy who set this up is no longer here. He set the maintenance plan to back up only 17 of the 90+ databases. Have no idea why. I suspect he had a few selected in the maintenance plan then as databases were added over time they were not added to the maintenance plan. Regardless, I’m trying to free up space. Simply taking backups of the transaction logs who aren’t in the maintenance plan will free up space in the log file itself however it won’t release the space on the drive correct?Also, can anyone think of a valid reason why he wouldn’t have selected “all user databases” when he created the maintenance for the log files? I have to assume he had a reason.
This is a completely separate issue and probably should be a question on its own.
If you have databases that were not being backed up - at all, either a full or transaction log backup - and those databases are in full recovery model then you can start backing them up now and backup the transaction logs. Once the transaction logs have been backed up you can perform a shrinkfile on the transaction log files for those databases. You will need to schedule frequent transaction log backups to insure they are backed up - and do not grow too large. You will also need to insure you have enough space on your backup drive to support these new backups.
If these databases are all set to simple recovery model - then shrinking the transaction log files will not gain you anything because they will just grow again to the size needed for that database.
As for why someone wouldn't select all databases - I am sure he did but that only works (in this version) for those databases that existed at the time the maintenance plan was created. That setting has been fixed in later version so it picks up new databases when they are added...
You will get a lot of recommendations to use Ola's tools or Minion...but that doesn't help if you don't already know what needs to be done. And it really doesn't help if you have different requirements for different databases where some need nightly index rebuilds and others need weekly - or how often you need to update statistics and whether or not that can be done across all databases at the same time.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply