December 28, 2016 at 8:01 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
December 28, 2016 at 8:01 am
this was from sql server 2008. I'm having the same issue...sorry i copied the above from the 2008 admin forum.
December 28, 2016 at 8:01 am
ISSUE:
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 trans logs. 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:13 am
December 28, 2016 at 8:13 am
Snargables (12/28/2016)
When I try DBCC SHRINKFILE with EMPTYFILE i am getting error
Please post the command that you're attempting to run.
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?
That's right.
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?
Possibly for the same reason that I don't. He wants to be able to run backup jobs individually. He's also conscious that different databases will have different backup frequency requirements, and that it's easier to stagger IO load when backups can be controlled with finer granularity.
John
December 28, 2016 at 8:24 am
they are all in full recovery as it is a requirement for them being in avail groups
the cammands are below
BACKUP LOG dbname TO DISK='F:\LogBackup\dbname_logbeforeShrink20161228.trn'
dbcc loginfo
DBCC SHRINKFILE (dbname, EMPTYFILE);
December 28, 2016 at 8:26 am
i can remove them from the avail group then change to simple then shrink then add back to avail group however i'd rather not have to do that unless i really have to.
December 28, 2016 at 8:35 am
Well, if the database structure really is as you posted it earlier, you wouldn't get the error message you described - you'd get this one:
[font="Courier New"]Could not locate file 'dbname' for database '<MyDatabase>' in sys.database_files. The file either does not exist, or was dropped.[/font]
John
December 28, 2016 at 8:59 am
Snargables (12/28/2016)
i can remove them from the avail group then change to simple then shrink then add back to avail group however i'd rather not have to do that unless i really have to.
Missed the HA before, are you saying that you have DBs in full recovery without log backups?
Don't do scheduled shrinking and in fact don't shrink the files unless absolutely necessary. From what I've seen so far, this looks like a shotgun arrangement maintenance, can you outline the schedules/ops for us?
😎
Further, what are the file growth settings?
December 28, 2016 at 11:23 am
98 db's in full recovery. all 98 have full backup every sunday night. 17 of these databases have their trans logs being backud up every 3 hours. All 98 db's have a daily
differencial backup.
Full and differencian is doing "all user databases". Trans backup only have 17 databases selected.
THis is a concern however not my immediate issue. The immediate issue is the insuffiencient space in the filegroup error. I'm about to sidestep this error by removing a few of the db'a from their avail group and shrinking the log after setting them to simple then putting them back to the avail group.
In the meantime I took backup of the trans logs who arent being backed up so there is free scpace for the near future. I just figured there had to be a way to get around the error w/o removing them from avail group etc
December 28, 2016 at 12:16 pm
Snargables (12/28/2016)
98 db's in full recovery. all 98 have full backup every sunday night. 17 of these databases have their trans logs being backud up every 3 hours. All 98 db's have a dailydifferencial backup.
Full and differencian is doing "all user databases". Trans backup only have 17 databases selected.
THis is a concern however not my immediate issue. The immediate issue is the insuffiencient space in the filegroup error. I'm about to sidestep this error by removing a few of the db'a from their avail group and shrinking the log after setting them to simple then putting them back to the avail group.
In the meantime I took backup of the trans logs who arent being backed up so there is free scpace for the near future. I just figured there had to be a way to get around the error w/o removing them from avail group etc
You cannot shrink the transaction log files using the emptyfile option. That is only for the database files - and you probably do not have multiple files in the filegroup which is why you are getting that error.
Setup your transaction log backups for *all* databases first...that must be done prior to anything else. Once you have backed up the transaction logs for every database you can then look at shrinking the transaction log files using:
DBCC SHRINKFILE(2, {new size}) -- where 2 is the default transaction log file. If you have more than one transaction log file (you should not) then you would pass into the command the file number for those additional files.
When shrinking the transaction log files - make sure you shrink them to a reasonable size...and that you have set a reasonable growth size. The default of 1MB is horribly bad...and anything over 8GB is way too large. The size should be large enough to handle all of the transactions that will be performed between transaction log backups.
Note: backing up your transaction logs every 3 hours means the business is okay with losing up to 3 hours of data in the event of a disaster. I would not trust that value and would back up the transaction logs at least every 15 minutes if not more often...
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
January 4, 2017 at 5:47 am
when emptying a file in a filegroup, DBCC will attempt to move the pages to other files in the same group, if there's not enough space in the other files you'll get this issue.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply