A maintenance job runs that REORGANIZES all the indexes and rebuilds all the statistics. Prior to creating and scheduling this job, I dropped and REBUILT all the indexes on all the tables. This job is created to prevent the need to drop and rebuild indexes while maintaining low fragmentation and updated stats.
Why does the maintenance job that REORGANIZES indexes only 24 hours after all the indexes are REBUILT cause the transaction log to grow to the size of the DB itself? The tables are a few billion bytes in size and the DB is about 12 billion bytes in size. I thought a reorganize would cause minimal activity if the indexes it is reorganizing had recently been dropped and rebuilt?
The transaction log is backed up twice a day. I can see the size growth of the log by looking at the backup of the transaction log after the maintenance plan runs and the next backup goes back to the usual size.
You aren't backing up your transaction log often enough. You should consider doing hourly log backups at a minimum. It is most common for people to do them every 15 minutes. Some people increase it to every 5 minutes during index maintenance.
All of our systems are set for 15 minutes.
There is no downside really to more frequent backups. The total size of the files will be the same. The two biggest benefits of more frequent log backups are more recovery points (less data loss in case of an issue) and less resources needed when it runs.