While working on some issue, it struck to me on “why we need log File for TempDB”. Generally, we need log file during crash recovery to rollback or roll-forward the transactions based on whether they are committed or not at that time. This helps the database to be transactionally consistent. Of course, we need log file so we can take log backups and this helps us to make point in time restoration. PS: Log file keeps track of many other events that happen within the user database.
But neither of these points apply for TempDB as it always gets recreated every time the instance restarts. I did some research and found out that we need TempDB log file for ‘ROLLBACKS’.Apparently, I was not thinking clearly.(It was always makes sense, when you know the answer). Also, I learnt that Logging in TempDB is different than User databases and logging is lighter and faster in TempDB.
So, if we have to create a temp table, it lot better to create on TempDB than USer DB as it is much faster because of less logging