September 8, 2012 at 10:18 am
Hello, i would like someone to help me in this
I have a filegroup A with file A1 and A2
file A1 have
TotalExtents 3387040
UsedExtents 3387040
so is completely full
size of file is 211690MB
my new file A2, is completely free and have auto growth enabled
my objective is, whats the best practice to move data ( extents ) from file A1 to A2 ?
i tried "DBCC SHRINKFILE (N'A1', 211689)" but took so long and gave a strange error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Database is currently operating normally, so i think we can ignore that ?
i just wanted to move data from one file to another
thanks for all your time and help 🙂
September 8, 2012 at 10:21 am
No, you can't ignore the error. 😀 Check all of the event logs described in our troubleshooting checklist:
http://www.brentozar.com/archive/2011/12/sql-server-troubleshooting-checklist/
Go through all of those, and the error's going to show up in one of them. Post the full text of the error messages here (and of course, Google for 'em, because that's going to be enlightening.)
September 8, 2012 at 10:29 am
DBCC CHECKDB gave no errors 🙂
September 8, 2012 at 10:35 am
OK, great, that's one start. Now can you follow up with the rest?
September 8, 2012 at 10:37 am
Brent you mean, execute the DBCC SHRINKFILE command to remove 1MB to the datafile each time, since all extents are used ?
September 8, 2012 at 10:38 am
No, I mean follow up on the error. Check all of the event logs described in our troubleshooting checklist:
http://www.brentozar.com/archive/2011/12/sql-server-troubleshooting-checklist/
Go through all of those, and the error's going to show up in one of them. Post the full text of the error messages here (and of course, Google for 'em, because that's going to be enlightening.)
September 8, 2012 at 10:43 am
Brent 🙂 thanks for all the help,
i checked all that pdf tips and everything is ok with the instance and databases, theres no errors on SQL Error log 🙂
that error showed when i tried to DBCC SHRINKFILE and gave on the T-SQL Output window :/
The best way to move extents from one datafile to another in the same filegroup is do 1MB each time ?
September 8, 2012 at 10:46 am
Butting in for just a second, it sounds like you don't have enough space to shrink the whole file at once. I would check DBCC SQLPERF and see what you get from that. However, Brent might be right, and you might only be able to move 1MB at a time.
_____________________________________________________________________
-Jamie Scharbrough
MCTS: SQL 2008R2
September 8, 2012 at 10:54 am
I have enough space on tlog and DB is in simple mode
Log Size (MB) Log Space Used (%) Status
9993,992 0,4976818 0
September 8, 2012 at 10:58 am
biohug (9/8/2012)
i tried "DBCC SHRINKFILE (N'A1', 211689)" but took so long and gave a strange error:Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Did someone perhaps run KILL on the session doing the shrink?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2012 at 11:01 am
GilaMonster (9/8/2012)
biohug (9/8/2012)
i tried "DBCC SHRINKFILE (N'A1', 211689)" but took so long and gave a strange error:Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Did someone perhaps run KILL on the session doing the shrink?
Nop Gila 🙂
September 8, 2012 at 11:14 am
about the move of extents ( data ) between the datafiles in same filegroup, theres any best practice or tutorial or tips i should follow ?
since all extents are used on A1, i need to do a DBCC SHRINKFILE of 1MB ?
September 8, 2012 at 11:21 am
No, you don't want to shrink in 1 MB chunks...
Create 2 new files in the filegroup, then shrink the full one with EMPTYFILE and remove it. Result - data balanced across the other 2 files.
Shrink without that option does not move data between files, it'll just shrink down to the used space in that file.
p.s. Sure about the kill? If not, then you have something wrong somewhere that caused that 'severe error'. Check the error log, any entry mentioning kill?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2012 at 11:33 am
GilaMonster (9/8/2012)
No, you don't want to shrink in 1 MB chunks...Create 2 new files in the filegroup, then shrink the full one with EMPTYFILE and remove it. Result - data balanced across the other 2 files.
Shrink without that option does not move data between files, it'll just shrink down to the used space in that file.
p.s. Sure about the kill? If not, then you have something wrong somewhere that caused that 'severe error'. Check the error log, any entry mentioning kill?
GILA awsome !!! 😉 thanks for the tip, i knew about EMPTYFILE but since this file is too big and i dont have that much storage on the server, i thought there was a way to move chunks of extents between datafiles.
The ONLY way is really EMPTYFILE A1 and spread across A2 and A3 datafile ?
Yes, completely sure about the KILL, no one killed and nothing on error log
September 8, 2012 at 11:39 am
The only way that will definitely work, yes.
You can try stuff like rebuilding indexes and hoping SQL balances out the allocations, but it's pot luck.
If you're out of space, get more disks. Databases grow and shrinking and playing with files is just a stop-gap effort.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply