September 9, 2012 at 5:32 am
About the EMPTYFILE operation on same filegroup:
i have file A1 with 100GB occupied
file A2 with 20GB (autogrowth disabled)
file A3 with 30GB (autogrowth disabled)
if i make EMPTYFILE on A1 it will :
- A1 becomes 50GB and marked as EMPTY, A2 with 20GB and A3 with 30GB ?
- Operation doesnt start because it dont have enough space on filegroup ?
- Lets imagine autogrowth is enabled on A2 and A3, they will become 50GB each ( A2 and A3 ) and A1 completely EMPTY ?
When EMPTYFILE is run, it will SHRINK the file to 0MB (physically) ?
How i know what datafiles and tlog's are marked as empty ? theres any table or dmv with that kind of information ?
September 9, 2012 at 5:41 am
Probably A2 and A3 become full and A is NOT marked as empty (it's not empty, it can't be marked that way)
Try it and see.
Btw, why are you trying to 'balance' the data across the existing and new files? If you're just running out of space, add a new file and let SQL allocate to the new file (which is what it will do)
- Lets imagine autogrowth is enabled on A2 and A3, they will become 50GB each ( A2 and A3 ) and A1 completely EMPTY ?
A1 will be completely empty,yes (that's what EmptyFile does). Not big the other two get depends on their autogrow settings. Since A2 starts smaller it'll probably stay smaller.
Again, try it and see.
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 9, 2012 at 6:13 am
Thanks GILA 🙂
About a query to check if a file is marked as empty or not you have any ?
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply