July 9, 2012 at 9:38 am
We currently have about 1500 GB in data files on one SAN and need to move to a new (and hopefully better suited) SAN. I have one large DB (~1000GB) that has about 50% listed as Unallocated. If I run DBCC SRINKFILE (DBFILENAME, TRUNCATEONLY), will this reduce the file size (and reduce the time to transfer from one SAN to the other).
And if I do that, will it cause me a lot of troubles with fragmentation, etc. later one? Is any of that going to be a permanent condition?
My plan is to get backups, shut down the SQL instance, copy the data between the two SAN's, unmap the old LUN's, remap the new LUN's with t he same drive letters and restart the instance. Does that seem complete?
Thanks!
Norman
July 9, 2012 at 9:43 am
n.heyen (7/9/2012)
We currently have about 1500 GB in data files on one SAN and need to move to a new (and hopefully better suited) SAN. I have one large DB (~1000GB) that has about 50% listed as Unallocated. If I run DBCC SRINKFILE (DBFILENAME, TRUNCATEONLY), will this reduce the file size (and reduce the time to transfer from one SAN to the other).
Maybe, maybe not. If the free space is at the end of the file, yes. If not, you'll have to shrink with a target size and not the truncate only option
And if I do that, will it cause me a lot of troubles with fragmentation, etc. later one? Is any of that going to be a permanent condition?
TruncateOnly, no fragmentation. If you have to shrink with a target size, yes, you'll fragment indexes, rebuild them afterwards
My plan is to get backups, shut down the SQL instance, copy the data between the two SAN's, unmap the old LUN's, remap the new LUN's with t he same drive letters and restart the instance. Does that seem complete?
As long as you have backups from before you start that, yes.
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
July 9, 2012 at 10:33 am
Thank you Gail,
BOL isn't telling me that but maybe and I don't understand what it is saying exactly. But what you say makes sense. I'll do a shrinkfile with a target and see if that gets me down somewhat. I'll defrag the indexes over the next maintenance window, I guess I sort of expected that.
This just seems too simple and that always worries me a lot... 🙂
And congratulations on your latest 2012 Certifications!
Norman
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply