Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Move extents between files Expand / Collapse
Author
Message
Posted Saturday, September 8, 2012 10:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 14, 2014 6:15 PM
Points: 23, Visits: 428
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 :)
Post #1356384
Posted Saturday, September 8, 2012 10:21 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:32 AM
Points: 68, Visits: 430
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.)



Post #1356385
Posted Saturday, September 8, 2012 10:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 14, 2014 6:15 PM
Points: 23, Visits: 428
DBCC CHECKDB gave no errors :)
Post #1356386
Posted Saturday, September 8, 2012 10:35 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:32 AM
Points: 68, Visits: 430
OK, great, that's one start. Now can you follow up with the rest?


Post #1356387
Posted Saturday, September 8, 2012 10:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 14, 2014 6:15 PM
Points: 23, Visits: 428
Brent you mean, execute the DBCC SHRINKFILE command to remove 1MB to the datafile each time, since all extents are used ?
Post #1356388
Posted Saturday, September 8, 2012 10:38 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:32 AM
Points: 68, Visits: 430
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.)



Post #1356389
Posted Saturday, September 8, 2012 10:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 14, 2014 6:15 PM
Points: 23, Visits: 428
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 ?
Post #1356391
Posted Saturday, September 8, 2012 10:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 8:43 AM
Points: 37, 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
Post #1356392
Posted Saturday, September 8, 2012 10:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 14, 2014 6:15 PM
Points: 23, Visits: 428
I have enough space on tlog and DB is in simple mode

Log Size (MB) Log Space Used (%) Status
9993,992 0,4976818 0
Post #1356393
Posted Saturday, September 8, 2012 10:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
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 2008, MVP
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

Post #1356394
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse