February 1, 2010 at 9:36 am
I have a database of 950GB in production and also have same copy in development, everytime i have to load data into the datrabase i do it on development server. During this process i load data of nearly 50GB,rebuild all indexes,shrink database , backup the database and then restore it on production.
I am not sure how correct i am dogin this process but main thought is to keep my production safe and not slow doen the production server. I do every thing from a sql job which takes around 2 days every time i do this.
I have a question..do i really need to shirnk db before backing up the database, i assume backup file will not save any log space if available, right?
February 1, 2010 at 9:39 am
Right.
February 1, 2010 at 9:53 am
Tara-1044200 (2/1/2010)
I have a database of 950GB in production and also have same copy in development, everytime i have to load data into the datrabase i do it on development server. During this process i load data of nearly 50GB,rebuild all indexes,shrink database , backup the database and then restore it on production.I am not sure how correct i am dogin this process but main thought is to keep my production safe and not slow doen the production server. I do every thing from a sql job which takes around 2 days every time i do this.
I have a question..do i really need to shirnk db before backing up the database, i assume backup file will not save any log space if available, right?
Do you load fresh 50 GB data or delete the old one and then reload updated one?
February 1, 2010 at 10:04 am
i load fresh data every month about 50GB.
any thgouts on this process or is there a scopre fro improvement ?
February 1, 2010 at 10:12 am
Tara-1044200 (2/1/2010)
i load fresh data every month about 50GB.any thgouts on this process or is there a scopre fro improvement ?
Can you provide more information on your process? Like, what is source of the data? what form the source data exists? how frequently you load this data? how do you import it? etc.
February 1, 2010 at 10:22 am
i load data through ssis pkgs, source and dest both are sql server databases, this is done every month.
February 1, 2010 at 10:32 am
I really just have one thought on the process as a whole and it pertains to shrinking your db. Shrinking the log makes sense, but shrinking your data files can be bad. Paul Randall has a great post on it here
http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx
February 1, 2010 at 11:09 am
as it is a very big database i have to shrink them to get my space back and more over once moved to production server every one just read the database no writes.
every time i shrink the database i get atlreast 100gig space back and i feel its worth shrinking, any thoughts?
February 1, 2010 at 12:12 pm
As long as you do not mind the fragmentation that it can create (and likely is creating) it should not be a problem, it just could make queries against the table run a bit slower and will certainly cause more reads than if the table were not fragmented.
Randall tosses out an alternative if you need to shrink the db in his blog, here is that excerpt from the blog linked above.
So what if you *do* need to run a shrink? For instance, if you've deleted a large proportion of a very large database and the database isn't likely to grow, or you need to empty a file before removing it?
The method I like to recommend is as follows:
*
Create a new filegroup
*
Move all affected tables and indexes into the new filegroup using the CREATE INDEX ... WITH (DROP_EXISTING) ON <filegroup> syntax, to move the tables and remove fragmentation from them at the same time
*
Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)
Basically you need to provision some more space before you can shrink the old files, but it's a much cleaner mechanism.
Every system is different, so certainly do what you need to do, but I am definitely a fan of keeping my tables as contiguous as possible if I have the option.
Good luck.
February 1, 2010 at 12:23 pm
:w00t:
I think this are two different tasks and might be run at different schedules. First the shrink process, Many shrinks could create data corruption and this must done, at least I do with my warehouses (about 1.3 TB each and I have maybe seven)
I know when I have to load the data, so I do a bunch of maintenance tasks way before this happens. DBCC CHECKS, Reindex, if needed, etc. When I am safe I run a backup for the warehouse to be loaded from different sources (I have Oracle and SQL sources) With a safe backup, then I run the shrink DB, but I don't do it once. I kind of decided to do it in stages to allow the job to complete in a timely manner (so far I look like I am paranoic of my data? :hehe:)
With that in mind. All my data is safe and the load comes to a database with space to be loaded with no issues. Of course as everyone stated, each environment is different and each DBA has their own way to do things as each DBA knows better what he/she has y their plate. This is just a couple of ideas in how I do it and so far no issues. It has been about three years doing so.
OK just two cents to the discussion. Thank you and good luck :w00t: :hehe: 😎
February 1, 2010 at 12:25 pm
Tara-1044200 (2/1/2010)
as it is a very big database i have to shrink them to get my space back and more over once moved to production server every one just read the database no writes.every time i shrink the database i get atlreast 100gig space back and i feel its worth shrinking, any thoughts?
Yes... everytime you shrink the database, you've basically undone the reindexing especially where the clustered index is concerned.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply