Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
luk
Starting Member
2 Posts |
 Posted - 2007-04-05 : 06:53:13
|
| Hi guys, I am developing this site http://www.onlineacademicadvisor.com and having DB problems for the 4th time in a row. Whenever the traffic on the site is getting bigger, the transaction log becomes full and no user can login. This problem is described at http://support.microsoft.com/kb/317375 From there, I got the feeling that the problem occurrs if transactions are not committed and last for too long. However, I do not have any long lasting transactions, just the usual select, insert, update statements in rather short stored procedures. I do not call COMMIT (or RETURN) explicitly at the end of my stored procedures though. When the transaction log is full, select statements are still executed alright, but insert and update fails.Have you got any ideas about what can cause the problem? I really have not idea what that could be. My hoster, re-invent, says it is my app that causes the trouble... Your help is much appreciated. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-04-05 : 09:40:15
|
| I do not call COMMIT (or RETURN) explicitly at the end of my stored procedures though.Do you ever issue BEGIN TRAN statements? If not then you don't need to call COMMIT. Do you regularly back up the transaction log (and the database for that matter)? When you backup the transaction log the old completed transactions are removed, if you aren't backing up then the old transactions are never removed and your log will become full. |
 |
|
|
luk
Starting Member
2 Posts |
Posted - 2007-04-05 : 11:18:32
|
| Yes, the log is truncated every 24 hours. But in this time, my log is growing too fast and I run out of space. I have got 175 MB of disk space now, which is satisfactory for most sites and applications, says my hoster. What in my app can cause this growth? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-04-05 : 13:47:38
|
| So back it up more frequently - every data modification in your app puts entries in the log. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-05 : 13:51:54
|
| If you don't require point in time recovery, then you should you SIMPLE recovery model which should eliminate this problem (that is of course if you aren't currently using SIMPLE).Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
No user can login