Transaction Log Problem!!!
- Thread starter Hawkeye
- Start date
Yash said:
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?
Yash said:
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,
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.
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.
Yash said:
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.
Hawkeye said:I am no longer able to simply increase the size via HSPHERE (says I am beyond quota), so the site is STILL down!!!::sigh:: Looks like my bill is also due, but I didn't get the standard warning email. This is most likely why I can't increase quota again. I have now taken care of that.
Site is still down though - can't make any changes until the payment is processed (how's that for bad timing?). Still need the DB backup up and the autoshrink applied, which would have prevented this.
Yash said:
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.
Yash said:
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.
Hawkeye said:
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.
Yash said:
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.
coldfusionman
Perch
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...