Transaction Log Problem!!!

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

  1. Yash

    Yash Bass

    nope, nothing. We're still looking into this
  2. Hawkeye

    Hawkeye Perch

    Yash, where are we at with this?! I've had the site closed since yesterday to keep anything new from being added in case you are able to do a more recent restore.
  3. Yash

    Yash Bass

    We have exhausted every backup we had of your database on the MS SQL server and on our backup server. They all appear to be the same.

    I'm really sorry, there is nothing we can do to retrieve the data you lost. But I do promise that we are looking into this thoroughly to ensure such an incident doesn't ever occur again
  4. Ron

    Ron Perch

    ouch! I feel your pain Hawkeye. I've never heard of such an incident before at JodoHost.
    I always maintain my own backups regardless of whatever my hosting company says. I just feel safer.
  5. Hawkeye

    Hawkeye Perch

    Well, Yash...I'm going to delay expressing my anger over this - the event as well as the process involved, to tell you that, even based on what you have said, things still are not right.

    My site is connecting to a database called SVArt_db, which is a week old, as we have discussed.

    However, within HSPHERE this database DOES NOT APPEAR THERE. You have some temporary, COMPLETELY empty, database called SVArt_db2.

    So, this needs to be corrected...db2 needs to be deleted (which I can do), and SVArt_db needs to be reconnected to HSPHERE.

    When I mentioned this before you said that a restore would have to be done in order for it to show up on HSPHERE again. Well, since we've been through several restores by now I would have thought this should have been fixed.
  6. Yash

    Yash Bass

    The SVArt_db2 was created to replace SVArt_db. We were not able to restore SVArt_db into HSphere and PSOFT said the only remedy was to create a new DB and copy data from the old to the new. Let me know if you want SVArt_db2 to be your new database. If so, I'll have data imported from SVArt_db
  7. Hawkeye

    Hawkeye Perch

    Yes, this needs to be done - I can't move forward without any HSPHERE capability. Once complete, delete the original so there is no future confusion...
  8. Yash

    Yash Bass

    This has been completed. Your database login names has to be recreated. You'll have to change their passwords

    Let me know when we can delete SVArt_db
  9. Hawkeye

    Hawkeye Perch

    Couple things...all IDs will need dbo permissions setup if they are not already.

    Also, that new database is only 5 megs - the old one was 9. I'm already getting errors on the stored procedures. Was this a full restore?
  10. Yash

    Yash Bass

    you have DBO permissions on users and admin.
    Wha errors are you getting or what data is missing? This was a full restore
  11. Yash

    Yash Bass

    we restored again. I think all your data has been imported this time
  12. Hawkeye

    Hawkeye Perch

    Ok, the non-existant user "SVArt_users" was somehow still attached to the database. I had to remove that and add "SVArt_users2", which is the ID you created. That ID does not appear to have dbo permissions though.


    Microsoft OLE DB Provider for SQL Server error '80040e09'

    SELECT permission denied on object 'tblConfiguration', database 'SVArt_db2', owner 'dbo'.
  13. Hawkeye

    Hawkeye Perch

    ...and then some. I just checked and it looks like all the data is now duplicated within the database (2 of everything).

    So, it needs to be scratched entirely and done again. I'm sitting here wondering how on this earth all of this could have happened from my making a simple request to purge my transaction logs.

    Now I have a week's worth of data which is gone forever and we can't even seem to get anything restored correctly before that point. ?(
  14. Yash

    Yash Bass

    OK, we fixed this. SVArt_admin and SvArt_users2 have DBO permissions. a new SVArt_DB2 database was created and was restored from your original SVArt_db database. Let me know if there are any problems. If something is missing, please send us your backup file and we'd restore it from that.

    Your transaction log file was cleared on the very first go. Every night at 12AM a backup is made of all databases. This backup clears the log file. Your log file usage remain static because the log file is filled with white space.
  15. Yash

    Yash Bass

    Your old database, SVArt_DB is also online. You may use that for the moment if the problem still exists, till we solve it. We recreated it's users, SVArt_admin and SVArt_users2 (the same as for SVArt_DB2). They both have DBO permissions
  16. Hawkeye

    Hawkeye Perch

  17. Atul

    Atul Administrator Staff Member

  18. Hawkeye

    Hawkeye Perch

    It is done - I have it there in ther root. I have also tested this copy on a local SQL server and know that it is functioning properly. You can over-write the current database, but let me know when restore is complete. All IDs will need DBO, as before.
  19. brawney

    brawney Perch

    Yash, and all. I am wondering what can be done about a log file that grows so large. Apparently you're saying that there is no way to make it smaller again. Although the backup truncates the log file it does not shrink the file size. There must be some way around this.

    I am not too good with MS SQL Server but I did fine the following informaion in the online documentation for MS SQL Server. Apparently there is a way to use authshrink to make the log file get smaller after the backup when it is truncated. If we can't do this then it will really cause problems for many users. We all have a limited amount of disk space and log files growing to enormous sizes and never getting smaller will cause a lot of problems for all users.

    Truncating the Transaction LogWhen SQL Server finishes backing up the transaction log, it automatically truncates the inactive portion of the transaction log. This inactive portion contains completed transactions and so is no longer used during the recovery process. Conversely, the active portion of the transaction log contains transactions that are still running and have not yet completed. SQL Server reuses this truncated, inactive space in the transaction log instead of allowing the transaction log to continue to grow and use more space.

    Although the transaction log may be truncated manually, it is strongly recommended that you do not do this, as it breaks the log backup chain. Until a full database backup is created, the database is not protected from media failure. Use manual log truncation only in very special circumstances, and create a full database backup as soon as practical.

    The ending point of the inactive portion of the transaction log, and hence the truncation point, is the earliest of the following events:

    The most recent checkpoint.

    The start of the oldest active transaction, which is a transaction that has not yet been committed or rolled back.
    This represents the earliest point to which SQL Server would have to roll back transactions during recovery.

    The start of the oldest transaction that involves objects published for replication whose changes have not been replicated yet.
    This represents the earliest point that SQL Server still has to replicate.

    Shrinking the Transaction Log
    The size of the log files are physically reduced when:

    A DBCC SHRINKDATABASE statement is executed.

    A DBCC SHRINKFILE statement referencing a log file is executed.

    An autoshrink operation occurs.
  20. Yash

    Yash Bass

    I never said that we can't shrink the database, I said that the transaction log is cleared with the nightly backup

    Yes, we could turn on autoshrink but HSphere does not set that up automatically and it would be a pain to do that for every database. We however can shrink the database on customer request

Share This Page

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