| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2007-07-06 : 09:14:15
|
| the log file is getting huge ( 50GB in two hours... ) whenever there are transactions.. i am doing a transaction log backup every hour .. it used to keep the size of log file in the control....but suddenly it became out of control...I am not sure where the problem comes from.. how can i fix this problem? |
|
|
b.veenings
Yak Posting Veteran
96 Posts |
Posted - 2007-07-06 : 10:22:10
|
| what is the db used for, is there an open transaction at this moment, try to figure at what changed in the gui the moment before it happenedNeed an SQLDB consultant?check www.veeningsengineering.nl |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-06 : 11:19:27
|
| Double check with 'dbcc opentran(db)name'. |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2007-07-06 : 12:09:02
|
| it's for the solomon application..I donlt undersand why suddenly one updete/ insert statment can generate a couple of GB of log file in a couple of mins? |
|
|
|
b.veenings
Yak Posting Veteran
96 Posts |
Posted - 2007-07-06 : 13:58:36
|
| if one statements is in a loop it can insert or update a lot of records, what was the statement?i had this problem with Whats up gold from IP switch, this application logs everything that happens on your network, in one day mine log grows to 5 GB but i use scripts to prevent it from groing to big.Need an SQLDB consultant?check www.veeningsengineering.nl |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-07-06 : 16:48:49
|
| Do you have an option of restarting the machine or sql server? I once fixed a similar issue by a simple reboot. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-06 : 17:03:46
|
| Rebooting the server or restarting the service is not a solution for this.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-07-06 : 17:17:09
|
| Tara,It solved my problem. And I never had this problem again. Ideally, we all want to know the real reason, as the problem can arise again, but as a quick fix, I think this solution is worth a try. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-06 : 17:21:03
|
| Your solution could cause bad data to be in the system as SQL Server has to rollback uncompleted transactions. You should not restart or reboot in the middle of a process running.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-07-06 : 17:27:41
|
quote:
Originally posted by tkizer
Your solution could cause bad data to be in the system as SQL Server has to rollback uncompleted transactions. You should not restart or reboot in the middle of a process running.Tara Kizerhttp://weblogs.sqlteam.com/tarad/
Not to mention the recovery time after the reboot could be significantly longer due to rollbacks having to occur to put the database in a consistent state. The database is not brought online until it's in a consistent state, which can take up to 3x longer than the original transaction would take to complete (as a rule of thumb, a rollback can be 2.5 - 3x longer than the transaction takes to run to completion if uninterrupted).You defenitly should put forth the effort to find out what's going on in the db if it occurs again. You potentially put yourself at risk for some long downtime by restarting the service in the middle of the transaction. Also, consider that even though you have a storage issue at the moment, your database is up and running...which is better than not running and explaining to management that you don't know when it's going to be back online (if you restart)... |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-07-06 : 17:27:42
|
| You are absolutely correct!Thank you |
|
|
|
b.veenings
Yak Posting Veteran
96 Posts |
Posted - 2007-07-07 : 15:45:35
|
| is simply to find out why it stopped growing after the reboot, thats because the statement was broken while it was running, what can cause seriously consitenty problems.did you finally find out what the problem was?Need an SQLDB consultant?check www.veeningsengineering.nl |
|
|
|
|
Log file is getting huge