Sql-server – difference between tempdb log and database log? In which circumstances these log files uses

sql serversql-server-2008

As we all know there are two types of log file (*.ldf) comes into the picture whenever any large query getting executed 1. database log (transaction log) file 2. tempdb log.

Please describe me how SQL server uses thes files while any query executed?

2nd thing i want to know, when it is safe to shrink log files (database log, tempdb log)? In which case we shouldn't log files?

Best Answer

MS SQL Server is an ACID compliant RDBMS. One of the critical tools involved in providing ACID compliance is a concept of a transaction log. The transaction log is where the pages modified is saved, so it can revert them and put them back in the previous state if you need to rollback a transaction. There is much to consider here but you want to ensure you size your VLFs and manage it in advance.

TempDB is a temporary area used by MS SQL Server to store objects like temp tables, join results, some caching, etc. Unless you have performance issues, you generally don't want to worry about this, don't even back it up. It get's rebuilt on startup. It is good practice to create at least 2 or maybe up to 4 physical files for TempDB so you don't get PAGELATCH contention.

Paul Randall (An authority on MS SQL Server) recommends letting your transaction log be at whatever size it grows to after a full weeks activity after reindexing. Really, unless it grows a lot, don't worry too much about it. Just make sure to avoid growths.