What to do if your transaction log feels up

In MSSQL your Tranlog will only fill out if your hard drive is full

Before I show you how to shrink the Tran log I will explain a little about it and what the tranlog is for.

The Transaction Log is Probably the most underutilized and underpperciated part of SQL. this is probably due to its simplicity.

The transaction log can actually help to lower the server’s resources, increase productivity, all the while providing a method of an additional safety layer for you to fall back on!

Why does the tran log exist?

This history of the existence of the transaction log is difficult to trace; and is to a small degree controversial due to certain aspects are based on other applications. An example is the method to use transaction logs to restore databases is primarily based on proven methods used in Microsoft Access, which in turn is based on other application(s) and standards. This means there is no single document that states the history of Transaction Logs and most current documentation is partially based on personal experience, knowledge, and/or interpretations.
I’ll make no attempt to determine the exact history of transaction logs. I just want to point out that transaction logs are largely based on Microsoft Access methods to store information for transactions. This is important to know because it has become a common misunderstanding that using the TRUNCATE command should not only remove old transaction information, but also should SHRINK the file down. This is absolutely, without a doubt…WRONG! I’ll get into this more as this article progresses. First, to be able to understand why TRUNCATE will not make the log smaller in physical size you will need to understand how the transactions are inserted into the log; both physically and logically.
The primary purpose the transaction log is to provide a method to be able to restore a database to a point-in-time when necessary. This can include rolling back transactions to a certain time, or to roll forward transactions from a full backup restoration.
In recent years the transaction log is starting to be used for additional purposes. They are being used in mirroring databases, quickly importing data from old database versions to a new database version, and security audits. This is just the tip of the iceberg for the way these logs are being utilized to gain “useful” information. The last point, security audits, is a more relatively new and undocumented (officially, that is) use of transaction logs. Information stored within transaction logs can be very powerful, but also tricky to learn at first while the structure and information being stored is new to you. If you take your time to learn what you can get out of the transaction log, you can produce very useful information that can help to increase your productivity (especially in the Security Audits area)!
An example of how to use these logs for security audits is that you can view a transaction log and be able to detect transactions that have occurred and whom (what user or system account) had produced the transaction (this is no trivial task to accomplish); and if you know your database very well you can quickly deduce if someone is accessing parts of the database they shouldn't’t be; or even worse, if an unauthorized user has accessed your database! The techniques to use the transaction logs for security audits go beyond the scope of this article; the preceding is intended to provide a broader understanding of methods being used to obtain useful information. You can search sites such as MSDN, SQLServerCentral.com, and Google for discussions and articles detailing many different ways to audit the logs and obtain additional useful information. It is also worthwhile to note that there are 3rd party applications that utilize the transaction logs for security auditing, data movement, and many other purposes. As always, evaluate any 3rd party application when possible; and test on TEST servers before ever using new techniques or applications in a production environment.

How the Transaction Log Works
As mentioned in the “Logical Architecture (Concept)” portion, transactions are recorded into the log in a sequential manner. The LSN for each transaction will have a value that is higher than the previously written transaction. It’s actually just that simple; no complicated mathematical methods are used. There is no clever programming to add new transactions. It’s literally the same as taking a piece of paper and writing in each event that has happened in your day and using an ID number starting with 1 and increasing by 1 for each event occurring to give the order of the sequence. And just like your day, the next event that occurs to you must happen after the previous event; thus you’d give the next event a higher ID number than the previous. It’s safe to say that most of the following events have happened in this order for you to read this article:
1) You told your computer to go to the link, or to open a document, containing this article.
2) Your device displayed the article on your display, or printed the article to another format to be viewed.
3) You started reading this article.
As with these events, you can’t have the 2nd and 3rd events occur without first event obtaining the article, and you can’t read the article unless you view/print it (thus #3 can’t occur without #2 occurring first). As you can see, you can’t have these steps logged out of order to recreate this process; so each step has a higher ID number than the previous step.
A transaction log is just exactly that in concept, a journal of events that occur within your database. The main points to remember here is that each event is given a LSN that is higher than the previous LSN (to keep track of what event happened in what sequence), and that there is enough information being stored so that SQL Server may recreate the event should a restore of the database be required.

Microsoft Recommendations
The Transaction Log is recommended, by Microsoft, to be located on a mirrored drive in a fault-tolerant storage system. It is also recommended, by many experts, to ensure that the log is located on its own physical hard drive. These two recommendations are based on the idea that should the database (or the hard drive holding the database) become corrupt that you can ensure that you have access to the Transaction Log during your recovery operation. Also, this setup will help to ensure you have minimal impact on your database performance due to the consistent writing to the Transaction Log; obviously to have a hard drive write to the database and then to write to the Transaction Log would require it to physically write twice. If the database and hard drive are on separate drives then each drive can only write once, thus improving your performance

My recommendations

I would recommend that you create a database and run allot of tests with large and small amounts of data to mimic a real life situation.
I’d recommend starting off with a modest amount of space for your transaction log (ranging from 5% to 10% of the database size). The objective is to get a picture of how big the transaction log will need to be from regular usage. Assuming there are not any drastic unusual amounts of data being passed through the system, after about 3 full backups (using your regularly scheduled intervals) with NOT shrinking the log file, you can feel fairly confident in knowing the size the transaction log needs to be.

It is common to use the log size being produced after the database has had enough regular usage data passed through plus another 10 – 20% of the transaction log size for small abnormal transactions and maintenance; such as a small number of records being archived (if archiving is done on a more regular basis, such as monthly or quarterly), additional tables are added later to hold new data requirements, or reports are being created and stored within the database. These are all concerns that are best addressed earlier in the development cycle; however, if the transaction log needs to be increased later that can always be accomplished by the administrator (or by SQL Server if left at defaults).
Keep track of the log file size throughout the life span of the database; if you notice it grow suddenly then look into the cause. The highest concern would be a massive amount of data being modified (either from archiving, or even worse deleting); if either of these are the case then make sure that the transaction log is not truncated. You may find that you will need this to rollback that large transaction, if this occurred during a non-planned time (such as a misuse of the DELETE or DROP commands by a database user). This is where the transaction log can literally save your job!
The last and probably most helpful recommendation I can give is to take a proactive approach to sizing your database’s transaction log file. Be generous in the amount of space you reserve for the transaction log. And most importantly if you expect (or even suspect) the log will need to be increased do this as soon as possible during off-peak hours; this will have the least amount of impact on your system and will result in the optimal performance of the system (while avoiding any unexpected performance degrading that might occur during peak hours!); remember that the less resources you utilize means the more resources that can be given to help your queries and reports!

Ok so now you are thinking how do I shrink the log down here is how you do it.

firstly you will need to kill the processes that are attached to the db.

The process for clearing this is as follows

1) dbcc opentran(database) - this will show the oldest open transaction

2) Kill this process

3) backup log database with no_log

4) use database

5) dbcc shrinkfile(database,100) - shrinks the log back to 100mb

additional resources if needed
TechNet: Working with Transaction Log Backups - http://technet.microsoft.com/en-us/library/ms190440.aspxTechNet: Restoring a Database to a Point Within a Backup - http://technet.microsoft.com/en-us/library/ms190244.aspxTechNet: Recovering to a Log Sequence Number (LSN) - http://technet.microsoft.com/en-us/library/ms191459.aspxTechNet: Best Practices for Recovering a Database to a Specific Recovery Point - http://technet.microsoft.com/en-us/library/ms191468.aspxTechNet: Security Considerations for Backup and Restore - http://technet.microsoft.com/en-us/library/ms190964.aspxMSDN: DBCC SHRINKFILE (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms189493.aspxMSDN: DBCC SQLPERF (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms189768.aspx