Transaction Log Problem!!!

Discussion in 'Database Support' started by Hawkeye, Dec 18, 2003.

  1. brawney

    brawney Perch

    Thanks for that clarification. Something that all users need to consider if they're using MS SQL - ask to have auto shrink enabled on their DB.
  2. Hawkeye

    Hawkeye Perch

    Ok, this looks fine thus far. Please do not make any other changes from this point forward, as I will be trying to rebuild data and test the integrity of what has been restored.
  3. Hawkeye

    Hawkeye Perch

    This is interesting - before the big crash, the transaction log was growing by about 12 megs a day, every day. So, basically you're saying that it will reach a point a level out BEFORE it actually hits the maximum (and actually crashes the site)????
  4. Yash

    Yash Bass

    Didn't get you. You can always ask us to shrink your database when you need it
  5. Hawkeye

    Hawkeye Perch

    I can't tell when I need it due to this thread. So, yes it needs to be applied. Is there any danger to the database when doing this?
  6. Hawkeye

    Hawkeye Perch


    Yash, is the autoshrink still an option? Is anyone having this applied to their database(s) currently?

    My transaction log is now at 83 megs...it is growing at the rate of 10-12 megs per day and still shows no sign of slowing down. Considering this, it doesn't seem that it is being purged with the nightly backup as you said and over-written with "white space". If it had to start writing to that space at the beginning of each day, it wouldn't be growing at this rate. I keep changing my over DB size to avoid have this thing hit the ceiling and take down my site (again).

    Can you offer any help or insight?

    Thanks,
  7. Yash

    Yash Bass

    Ok

    It appears that with all the manual work we did on your database, the scheduled backup we had set earlier is no longer there. We'll reschedule the backup and turn on auto-shrink tomorrow morning (12 hours from now) when our DB admin is in. I really don't want anyone else to play around with the DB who isn't qualified. Please submit a ticket so it remains on our to-do list

    In the mean time, just increase your quota. I'll make sure you aren't charged.
  8. Hawkeye

    Hawkeye Perch

    Ok, I ticket has now been submitted. Thanks.
  9. Hawkeye

    Hawkeye Perch

    Yash, please followup on this with support and expedite if possible. I haven't heard anything yet and have been increasing the size of the database a couple times a day. Transaction log is now 215 megs.

    Thanks.
  10. Yash

    Yash Bass

    A backup was scheduled on your database about 4 hours ago. The backup will take place at 12AM server time and your database would shrink as well (auto shrink enabled)

    You should have received a reply.
  11. Hawkeye

    Hawkeye Perch

    Ok Yash - I'm a bit perturbed here. I did in fact receive a reply that this was finished, but the backup and autoshrik actually have *NOT* happened yet. Today my transaction log hit the ceiling at 314 megs and took my site down.

    I am no longer able to simply increase the size via HSPHERE (says I am beyond quota), so the site is STILL down!!!

    Why is this so hard? Please assist...this is now outage number 3 in the two months I've been with Jodohost.
  12. Hawkeye

    Hawkeye Perch

  13. Yash

    Yash Bass

    We just checked that out and realised that the reason it was not able to perform a complete scheduled backup was that the log file was 100% full. It failed because of that

    I'm really sorry about this. I manually applied the backup and rescheduled your backup for 12AM
  14. Hawkeye

    Hawkeye Perch

    Ok, well I checked this morning and we're back up, but I don't think it's all working yet (maybe the first cycle will be tonight).

    The transaction log has been reduced to 292 megs now (from about 375), but I expected it to be reduced much further (starting at almost zero), and I'm sure it hasn't grown that much in only 10 hours. So, I'll check again tomorrow after tonight's cycle.

    Since my payment has now been applied, I can now make adjustments again within HSPHERE (at least until I hit the ceiling - not sure what that number is). I'm at a total DB size of 500 megs right now, even though the actual database is only 10 megs...all the rest is allocated for the transaction log, which is messy, but hopefully will change when the autoshrink is working.
  15. Hawkeye

    Hawkeye Perch

    Update: It appears that the backup is working because the transaction log is now standing at 293 megs (much of which I assume is "white space"). However, since it is setting at 293 megs, the autoshrink obviously isn't working. Does this work for anyone else? I don't want to be the test case...thanks.
  16. brawney

    brawney Perch

    I don't know much about this but I can't understand why your transaction log is so big. Here are some links that may help us al understand it a little better, although after reading the links I still don't know why your transaction log file on JodoHost will not shrink. Why don't other users here have this problem?

    http://dbforums.com/arch/179/2003/8/882748
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
    http://www.databasejournal.com/features/mssql/print.php/1439801
    http://www.sql-server-performance.com/ac_filegroup_performance.asp


    Sounds like they need to be doing a truncate as well. As I said, I'm not a MSSQL guy... can any of this be done through Enterprise Manager manually?

    Shrinking a log is dependent on first truncating the log. Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.
  17. Yash

    Yash Bass

    Auto shrink should have shrunk the database automatically during the backup operation according to my understanding

    Anyway, we have scheduled a shrink operation as well that will take place at 4:40am server time. Your database is 300MB out of which 200MB is white space
  18. Hawkeye

    Hawkeye Perch

    Yea, currently sitting at 293 megs (looks like I gained a meg somehow), of which most is probably white space. Still no autoshrink though. No big deal I guess...as long as the backup is working and the space is being reclaimed, the site will stay up.

    But, and FYI for you that the autoshrink is definitely non-functional. Probably not for anyone. Thanks.
  19. yeah - auto shrink didn't work on my DB either. mine is at 52 MB and should be more like 15 MB as only about 3-5 people have any kind of access to it and that access is minimal - my question is going to be, what happens when I get more traffic? Is this thing going to balloon to an unimaginable size? Shouldn't, i run a very successful e-comm site and our DB (with over 10, 000 products and 100,000 customers) is only about 250-300 MB MAX...
  20. Yash

    Yash Bass

    The situation isn't like that. All databases are backed up every 24 hours. With each backup, you transaction log is cleared and filled with whitespace. You don't have to worry about it filling up to a critical level

    The case for hawkeye was totally different

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services