Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
Shrinking log files for user databases might be OK if internal fragmentation is becoming an issue (search on Virtual Log Files for detailed info). Best to avoid it by setting up appropriate autogrowth sizes, avoiding growth in percentages as well. The defaults are inappropriate for any realistic data load.
Shrinking tempdb log files may not be a good idea at all, I have even seen data corruption that I believe resulted from a job that was regularly shrinking the tempdb log file. Best to pre-size it according to the workload.
You might find the following book helpful: SQL Server Transaction Log Management, from Red Gate (it's available as a free PDF). It doesn't just discuss managing the T-logs, but also what they are, and what the differences are in the various database recovery models.
tempdb is used for temporary objects such as temp tables, table variables etc. it is also used for transfering large amounts of data between tables when the amount of memory is not enough to contain the transfer. tempdb will shrink and grow as the objects use it.
The Log file (ldf) is used to store transactions. Usually in the case a transaction needs to be reverted the .ldf is used to revert to the previous state.The .ldf will usually stay enlarged until a full backup of the .mdf is made. You will see the .ldf file grow as you do inserts, deletes, updates etc. all of those commands use logging and are therefore stored in the .ldf file.
Some commands, like TRUNCATE, use minimal logging so you may not see an increase in the .ldf file size.
The only safe way to shrink the log file is to create a full backup first (therefore stating that all the committed transactions are final) and that the transactions can be discarded.
As for the individual file growth you should note that some T-SQL commands affect .mdf and .ldf files. For more information you can lookup MSDN for T-SQL commands that use logging (as a typical example the DELETE statement won't shrink the .mdf file and may increase the .ldf file, while TRUNCATE may shrink the .mdf file but will not increase the .ldf file due to the fact that the statement doesn't use logging as much)
The rule of thumb is never to shrink the .mdf file as this will mess up the index pages and will cripple the query performance. if you must shrink the .mdf file rebuilding all the indexes is recommended, and depending on the database size this may take anywhere from several minutes to a couple of hours.
