SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Move extents between files


Move extents between files

Author
Message
klunky
klunky
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 759
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 Smile
Brent Ozar
Brent Ozar
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 504
No, you can't ignore the error. :-D 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.)



klunky
klunky
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 759
DBCC CHECKDB gave no errors Smile
Brent Ozar
Brent Ozar
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 504
OK, great, that's one start. Now can you follow up with the rest?



klunky
klunky
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 759
Brent you mean, execute the DBCC SHRINKFILE command to remove 1MB to the datafile each time, since all extents are used ?
Brent Ozar
Brent Ozar
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 504
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.)



klunky
klunky
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 759
Brent Smile 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 Smile

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 ?
JESDBA
JESDBA
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 82
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

klunky
klunky
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 759
I have enough space on tlog and DB is in simple mode

Log Size (MB) Log Space Used (%) Status
9993,992 0,4976818 0
GilaMonster
GilaMonster
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214716 Visits: 46269
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search