Sql-server – Database T-Log is full due to ‘Active_transaction’. SQL server is using new T-log file first rather than old

•On my observation, I found that this is happening only with insert statement not with select. So, I presume that log entries will only be for INSERT/UPDATE & not for SELECT statement in any case. Am I correct?

Select statement as such is not logged as compared to DML statements like insert , update and delete. If you see output of fn_dblog for select statement it wont have any entry. But there would be numerous entries for DML statements. Please note. The transaction log is maintained and information related to transaction is written there so that during crash recovery or recovery SQL Server can know what transactions were committed and what were uncommitted and reading this it can bring database to consistent state THIS IS WHY log is so much necessary. Without transaction log recovery is not possible

For all practical purposes every transaction is logged in SQL Server. So YOU CANNOT have a scenario where you run a transaction and nothing is logged in transaction log. You have no option to disable logging in SQL Server. I suggest you to read Logging and recovery in SQL Server.

•How can I minimize logging while inserting updating records? I already have SIMPLE RECOVERY MODE

You are mistaken that logging does not happens in simple recovery model it does happen but moment transaction commits the transaction log is truncated and space used by logs generated for transaction is reutalized. As a fact logging in simple recovery is almost same as full recovery and difference lies when log truncation happens. Yes you can have minimal logging in Bulk Logged recovery model for only certain commands. For rest commands even in bulk logged recovery model logging would be full.

•I have found that average throughput during this period went from 25 MB approx. to 1.2 MB. What does it mean?

I cannot understand this can you please explain it.

•Is there any other way to troubleshoot this issue, other than increasing disk space?

As already suggested you are most likely to face issue if you keep @SET_SIZE INT = 500000000(even though its dummy) why such a high limit. Can you decrease it make it to 500000 and see how log file behaves. Make sure auotgrowth of log file is in MB and set to some sensible value. Do transaction in batches. Finally if you read Why transaction log keeps growing or runs out of space you can get some good idea.

•If only option is to shrink a file, then when should I do it? Can I do it when any active transaction is ON? [Production environment], for guiding me but I need to release space. I have a large Data warehouse which deal with millions of data everyday

Please don't shrink any, data or log file. I would not recommend it. If you really want to shrink ONCE to reclaim space and you have no option other than shrinking you might as well try it. But again remember you have to rebuild all fragmented indexes after you are done with shrinking. You can try method given By Paul in This Article which says not to shrink and gives alternative of shrinking.

You can shrink log file only once but please remember log file grew because you ran transaction which forced it to grow so its basically your mistake not transaction log file. Instead of this whole process of growing and shrinking why not preallocate some amount of space to log file so that it can avoid autogrowth events

Another thing

The above configuration is very bad. 1 MB autogrowth for data file is bound to cause issues. To set correct value please read the article Autogrowth settings. This will help you calculate correct autogrowth setting.

Think of the interval between log backups as the maximum amount of data you can afford to lose.

Realize you need to restore a "FULL" backup, plus all the log backups taken since the full backup in order to get back to the most recent point in time.

Also, practice your restore strategy. This is perhaps the single most important thing you can do vis-a-vis backups. You can have all the backups in the world, but if you don't practice how to restore them, they won't do you any good in the event of disaster.

Regarding your questions:

My issue is my log files seem to be growing very large quickly. I have read that log backups as often as every 5 minutes. I know that shrinking the log files often is not a good solution. I want to make sure that we have full recovery options.

Taking a full backup and a log backup will leave your log in a state where it is pretty close to empty (unless you have something like log shipping, mirroring, always on, etc, that relies on log records). The log will subsequently continue to grow until the next log backup is taken, at which point SQL Server will begin re-using space in the log file. For some interesting reading about log growth, please see this question.

If I take a nightly backup, then take a log backup right after, will that leave a break in consistency if there happens to be transactions going on at that time?

Any transactions that have not been committed at the precise moment a log backup begins will be automatically rolled back during the recovery process. However, if you have a later log backup where those transactions were committed, they will be committed during recovery once that later log backup is restored.

Typically, a sound production level backup strategy consists of the following minimal steps:

1. Take a full backup once per day, at some low-activity point. For instance say 2:00am.
2. Take log backups every 15 minutes, retaining every log backup taken since the last nightly full backup.
3. Restore the full backup, and every intervening log-backup once per day onto a non-production box to ensure the backup files and recovery procedures actually work.

If once-per-day full backups are unworkable, you can instead do:

1. Once per week, take a full backup.
2. Once per day, take a differential backup.
3. Once every 15 minutes take a log backup. Retain every log backup taken since either the last full backup, or the last differential backup, whichever is most recent.
4. Restore the full backup, and every intervening log-backup once per day onto a non-production box to ensure the backup files and recovery procedures actually work.

You may be interested in looking at Ola Hallengren's backup solution which is very highly regarded in the SQL Server community.