I am a bit lost on what it the right way to do this. I am migrating data from one db to another (corrupt errors) so i did the following:
- Create a new blank db
- Generate a object script from my source db (not including indexes because I want my data copy to be fast), run on the new db
- Generate a script for all my indexes from source
- Copy all my data from source to dest using tsql, all is well
- Run my create index on the dest, all is well, my new db size is approx 1.76GB with approx 760MB being the log file
- Shrink the database (doing this for testing only at this point)
Now if I run the following command (to rebuild all the indexes)
EXECUTE sp_msForEachTable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD;
The new sizes are 3600mb for the db and 1608MB for log
My question is doesn't creating indexes on a fresh db same thing as rebuilding or is there more to it than that. Also should I be using rebuild all indexes?
I guess I am trying to find out what I should be doing in the future, drop/recreate indexes every once in a while, or just run the rebuild command.
My database size is getting too large for the space I have left at my hosting.
I am trying to keep my db sizes down as much as I can, because I have 10 dbs and limited space on my VPS and recently I was getting errors that my db is corrupted (I do not have the backups) so this is why I am creating new dbs, moving data and starting "fresh" so I want to make sure everything is as optimized as possible going forward.