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

Export Import entire database (or best method to accomplish this) Expand / Collapse
Author
Message
Posted Saturday, October 19, 2013 4:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 5:49 AM
Points: 7, Visits: 29
Hi
I need to clean up a database. Main reason: datafiles have grossly disparate sizes. I'd like to export entire database (or some other meaningful method), resize and add datafiles of same size, re-import the data. What is the best way to accomplish this?

Further explanation of problem: When this database was originally created, there was one datafile. Over time, it grew quite large. Upon a subsequent upgrade,other datafiles were added, much smaller than the original datafile. As a result, considering the way MSSQL uses an algorithm to write data to datafiles, the data is not being distributed to the various datafiles in an equitable way. Hence, one datafile is very big and VERY hot. I'd like to redistribute the data to the various datafiles, and resize them equally so that the MSSQL algorithm would then evenly distribute data to ALL the datafiles.

Thanks for any help you can offer.
kd
Post #1506382
Posted Saturday, October 19, 2013 5:32 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 5,881, Visits: 13,016
SQL will use a proportional fill algorithm across the data files (in the same filegroup) to try and keep the amount of free space in each file the same. If your original file is still the hotspot this suggests this file is a lot emptier.

Do you have the space to increase the size of the newer files? Perhaps do that and shrink the original to remove some of the free space, then rebuild your indexes?

You will need an outage anyway so no need to move all the data out first (just take a backup)


---------------------------------------------------------------------

Post #1506384
Posted Saturday, October 19, 2013 10:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,403, Visits: 31,964
ken def (10/19/2013)
Hi
I need to clean up a database. Main reason: datafiles have grossly disparate sizes. I'd like to export entire database (or some other meaningful method), resize and add datafiles of same size, re-import the data. What is the best way to accomplish this?

Further explanation of problem: When this database was originally created, there was one datafile. Over time, it grew quite large. Upon a subsequent upgrade,other datafiles were added, much smaller than the original datafile. As a result, considering the way MSSQL uses an algorithm to write data to datafiles, the data is not being distributed to the various datafiles in an equitable way. Hence, one datafile is very big and VERY hot. I'd like to redistribute the data to the various datafiles, and resize them equally so that the MSSQL algorithm would then evenly distribute data to ALL the datafiles.

Thanks for any help you can offer.
kd


Are the datafiles in the same filegroup or not?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1506440
Posted Monday, October 21, 2013 7:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 5:49 AM
Points: 7, Visits: 29
Yes, they are all in the same file group.
And When I say "hot", because the very large datafile holds so much of the data, many more reads are targeted against that datafile than any of the others.
Post #1506944
Posted Tuesday, October 22, 2013 3:49 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 5,881, Visits: 13,016
aah, thats a different problem to the one I thought you were originally describing.

You need to bring the smaller datafiles up to the size of the original as much as possible. If space prevents this and these files are very small, get a drive that allows you to create new files of a decent size, shrink the small files with the emptyfile option to empty them, then drop them.

at the end rebuild the clustered indexes on the tables in the filegroup and SQL will attempt to spread the data between the datafiles.

I see no need at all to remove all your data from the database and reload it but you will need an outage and back the database up first.

If you can restore the database to a test server first to practice the task, script it up and get timings.


---------------------------------------------------------------------

Post #1507035
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse