November 8, 2002 at 3:45 am
Hi,
I am monitoring my transaction log file and it seems to be growing too big. 2 days ago, I create a brand new transaction log file and today I have a size of 50 Gig. I would say that there are not heavy traffic to the database.
How do I know what is being written onto the transaction log? Is there a way of monitoring what is wrong?
Edited by - wsze on 11/08/2002 04:29:44 AM
November 8, 2002 at 4:29 am
Is there a tool that can read the logfile?
November 8, 2002 at 4:34 am
Depends on several things, one of course is traffic. Another is the database recovery model if you have SQL 2000, if you do bulk inserts and have the database set to Bulk Logged then these transaction are stored to the transation log.
The way to fix it is to determine if you need to be able to recover the transactions, if not set the DB to Simple in 2000 or turn on Truncate on checkpoint in 7's DB properties. If you do then make a periodic backup of the Transaction Log, this will truncate the inactive portions of the TL but not free the space. I would monitor the size over the period of time based on how often oyu will do the TL backups for a few days and figure what the biggest growth was then set the size of the TL to that value and do a backup on the same time amount you used to set the size. This is so you can support all the transactions for that time frame without too much chance of a need for file to grow.
Other than that there are many things that can potentially cause logged transactions. Have you done anything specific in the last 2 days.
November 8, 2002 at 4:39 am
There is no special activity over the past few days. I have read about past topics that says reindexing will cause increase of Transaction Log. I have already stopped the reindexing schedule but still it is increasing.
The past history of this dataabse was it use to be clustered and replication was running. Now we have reinstall this instance to be a stand-alone and non-replication using the same MDF file. Would this post any problems?
Are there toold to read the Transaction log file to see what has been written into it?
November 8, 2002 at 4:42 am
In the long term i would like Transaction Log to run as this is a production DB. So recovery would be crucial.
November 8, 2002 at 5:38 am
To look at what is in the Transaction Log you can use a tool like LogExplorer which is very easy to understand, just a bit pricey. Or as posted in another thread
quote:
DBCC LOG is an undocumented command for reading from a log file. You can find some limited documentation on it in Ken Henderson's Guru's Guide to Transact SQL. The command has the following format:DBCC LOG ('database'[, format])
"[D]atabase" is the name of the database whose log you wish to view. "[F]ormat" is an integral value between 0 (the default) and 4 that specifies different formats for the output; different formats provide different levels of detail, with higher numbered formats generally providing higher level of detail, and usually greater levels of readability. The results tend to be cryptic, but a saavy DBA can interpret them, and can even use them to do what Log Explorer does, including undo-ing transactions. In fact, I've traditionally viewed Log Explorer as little more than a wrapper for this command, but newer versions of the product have persuaded me that it may be a worthwhile buy, thanks to additional features it provides; and of course, while one can certainly use DBCC LOG to figure out how to undo a command, Log Explorer makes it much simpler. In any case, DBCC LOG is a great educational tool; it provides a lot of insight into how SQL Server performs various operations, how databases are physically structured, and how transactions work.
Matthew Burr
Now for the TL to still be growing there has to be something causing transactions. I would suggest using Profiler and filter for that database to catch all TSQL Stmts. This should then give you can idea as to what is happening and with account names listed and app names listed, some idea of what is causing it.
November 8, 2002 at 7:13 am
Is it possible in someway that since this database use to run replication but now is not, it is still trying to replace the transaction?
From what I understand, there is no special events that causes extra transaction.
November 8, 2002 at 7:43 am
I don't believ so, could be triggers on the table you don't remember. But again your best bet to find the answers is to watch the DB with SQL Profiler and see what all is going on.
November 8, 2002 at 11:18 am
So if I have my theory right, things that will be written into a transaction log file would be write, delete and rollback transactions?
November 15, 2002 at 9:35 am
If you don't mind being evil (like me), just set a limit on the size you want the logfile to grow to. It sounds like it wouldn't be too long before something fails, that "something" might be your cause.
I find it hard to beleive a transaction log can grow to that size, that quickly without the amount of database activity being obvious.
Before you spend a few boring days staring at SQL Profiler, check your scheduled jobs (on this server, and any server that may feed into or out of this database on this server.) Your problem may just be an end of day, or an early morning job that causes this growth. This is activity that you won't see during the day.
-Ken
November 17, 2002 at 11:32 pm
would locks on database or tables causes transaction logs to grow? I realize that some of the clients have a time out connection to the database and at the same time, the transaction log file growns??
November 18, 2002 at 4:08 am
Generally no, locks prevent other activity from occurring against a referenced table until the process issuing the lock is complete. If a timeout occurrs of a process is deadlock and someone is victimized none of the requested changes for the timedout or vicitimizes connection are done and an error is sent back to the issuing client. No transactions in the database take place in regards to the connection that is timedout/vicitimized. There are some situations in which a deadlock may occurr during update with two or more connections in which partial transactions can have taken place and when victimized and the changes have to be rolledback.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply