February 5, 2010 at 12:26 am
Hi to all,
I've been challanged to find out what should be the fastest way to empty datafiles and if possible how to merge them restoring the database from old to new 'version'.
The spects on this;
We do have a large 4 TB database containing 40 datafiles. To optimize performance its recommended to have one datafile for each CPU-core.
Shrinking the files using 'emtyfile' is not possible due to the max volume sizes set for this organisation.
So, is there a way to use a backup (or other mechanism) to merge datafiles to a new created database (on another location) containing less datafiles?
For another project we will start with a copy of a production database (3,6 TB -36 datafiles) which will be copied to a seperate 'staging' zone to be cleaned-up.
Once cleaned-up a copy of this (cleand-up) database will be used for traning purposses.
If the database is in the staging zone and has been 'ripped' I'd like to reduce datafilesizes (from approx 70 Gb to 8 Gb) as fast as possible.
Sofar dbcc shrinkdatafile (x,truncateonly) seems to be the fastest way (dbcc shrinkdatafile (x,minimum value) will last almost 10 time as long - up to 8 hours per datafile).
Is the a way to speed this up (see also my first question - restoring within new small datafiles - autogrowth enabled)?
Trying to recreate a new database with less datafile, having a DDL extraction from the original database and export all data is not working cause SQL server is not be able to script all 120.000 objects from the original database and stalls the server. (although we use pretty 'heavy' servers -see spects below)
Who can help me out on this?
Regards Guus
OS Name: Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition
OS Version: 5.2.3790 Service Pack 2 Build 3790
OS Manufacturer: Microsoft Corporation
OS Configuration: Member Server
OS Build Type: Multiprocessor Free
System Manufacturer: HP
System Model: ProLiant DL585 G2
System Type: x64-based PC
Processor(s): 8 Processor(s) Installed.
February 5, 2010 at 12:53 am
Guus,
Not sure this will work but it might be worth giving a try (just hoping you want run into the same limitations).
In SSIS there are several tasks that you can use to transfer SQL Server objects, databases, jobs, logins etc.
One that I use very often is the Tansfer SQL Server Objects Task.
Once your target database is created, you can use SISS of course to also transfer the data.
Sounds great no? In theory there is no difference between theory and practice. In practice however...
Let me know how you fare!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply