January 26, 2016 at 12:37 pm
mw112009 (1/26/2016)
Reply to John:See attached, I ran the sql you sent.
Also in my original post I have question#2.
That is:
IS there a way for me to do an update without SQL sever inflating the tr-log. I mean is there a command that I can issue which tells SQL server not to bother writing to the TR log. After all I am not in a PROD environment
The attachment shows that in your case the issue is not the combination of full recovery model and no transaction log backups (which is the most usual cause for this, hence what most readers assumed). In your case, at least one transaction has not committed or rolled back for a long time, and that is the cause of your problem.
You may not be a DBA, but a developer using SQL Server should also have at least some basic knowledge of the tool they are using. There are a lot of excellent articles out there on the transaction log and if you have time available I highly recommend this series[/url]. But here is a very short crash course.
The transaction log can do a lot of things. Some are optional, two are not. If you set the recovery model of a database to SIMPLE, you switch off all the optional stuff and have less management to do, but you lose the optional features. For simplicity, I will focus on SIMPLE here. Read the articles I referenced to learn more.
The two basic functions that require the transaction log are: rolling back transaction (because a ROLLBACK is issued or because an error occurs), and repairing database consistency after a SQL Server restart (especially after an unexpected termination), a process known as revcovery.
The transaction log is a sequential list of all modifications that happened. A ROLLBACK is done by reversing all the modifications done for that transaction. Recovery is done by repeating all modifications that were done for committed transactions that were not yet hardened to disk, and reversing all modifications done for transactions that were not yet committed when the server crashed. Based on this description, you'll understand that the transaction log can never wipe or overwrite data that belongs to a transaction that is not yet committed, because it might still be needed for rollback or recovery.
The architecture of the log file is such that it can overwrite data that is not needed anymore. But because it's always sequential, it can never overwrite past the oldest data that might have to be changed. So if I open a transaction, do a modification, and then never commit or rollback that transaction, everything that is written to the log after that will not be overwritten and that space not reused, even when those transactions have been ended. For the simple reason that nothing past the start of the oldest transaction can ever be overwritten.
If a transaction log file is full and has no reusable space left, SQL Server will attempt to grow it - unless someone created the log file without autogrow settings. And growing the log will obviously fail if your disk is full. You can add an extra log file on a second disk, but I would use this only as a temporary stopgap measure; it is better to have just a single file for the transaction log, that is large enough for everyday use, and with enough spare room on the disk that autogrow can kick in when unexpectedly extra space is needed.
In your case, you do have an open transaction that is preventing reuse of log space. Run Bill's code to try to identify the oldest open transaction, and see if you can get it to commit or rollback. If you need it to be open for this long, then you need a large enough transaction log to hold that amount of data - make sure you get the disk space required for this. You will probably want to get your DBA to help you with this, or to do this for you. If you have no DBA available, then your only option is to become what we like to call an "involuntary DBA". Fortunately for you, there are tons of articles on the internet that can teach you the basics.
Good luck!
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply