Creating indexes vs Rebuilding indexes (larger db/log file)

Question

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:

1. Create a new blank db
2. 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
3. Generate a script for all my indexes from source
4. Copy all my data from source to dest using tsql, all is well
5. 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
6. 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.

Answer 1

I'd be a bad human being if I didn't start by saying this:

YOU NEED TO START TAKING BACKUPS

After looking at your other questions, I'd be an even worse human being if I didn't say this next:

MOVE TO A NEW SERVER

It sounds like there's a more serious problem here if you're running into data corruption over and over again that repairing isn't fixing. Simply moving to a new database on the same server may lead you right back into corruption.

With that out of the way:

Do I need to rebuild nonclustered indexes after I create them?

Rebuilding indexes immediately after creating them doesn't really do anything for you. A newly created index is about as "optimized" as an index can get. Rebuilding indexes should be a rare event, typically reserved to correct an issue that isn't fragmentation. Rebuilding indexes is a size-of-data operation. However big your largest index is is how much space your log file needs when it's rebuilt.

How do I keep my database as small as possible?

1. Check your recovery model. If you're in Full, you need to take log backups to control the size of the log file. Typically, more frequent will keep them smaller. If you're not in the mood to take log backups, put your database in Simple recovery. If you're unsure about which recovery model is right for you, start here.

2. If you're dead set on rebuilding indexes in the future, try using the SORT_IN_TEMPDB option.

3. If you're on Enterprise Edition, or SQL Server 2016 SP1 Standard Edition, you can use Page or Row compression to keep index sizes down.

4. Look at archiving data. The best way to keep database sizes small is to keep data small.

5. If they suit your workload and query patterns, filtered indexes can help, because you're not putting all the table data into an index.

6. Make sure your tables have clustered indexes. Heaps can do weird things to space used.

7. Check your data types. Are you using CHAR or NCHAR? Are you using NVARCHAR where VARCHAR would do? DATETIME where you just need a DATE? If you're worried about database sizes at the 4GB mark, bytes saved here can matter.

Why did my log file grow so much?

Memory seems to serve that when you issue the REBUILD ALL command, this rebuilds all your indexes in a single transaction, whereas issuing individual CREATE statements will give your log backups or Simple recovery model CHECKPOINT magic a chance to kick in.

If you're looking to bring some sanity to index maintenance, check out Ola Hallengren's free scripts, and some examples of how to not run them foolishly here.