Transaction Log Problem!!!
- Thread starter Hawkeye
- Start date
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
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
Yash said:
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.
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
Yash said:
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.
Error:
Microsoft OLE DB Provider for SQL Server error '80040e09'
SELECT permission denied on object 'tblConfiguration', database 'SVArt_db2', owner 'dbo'.
Yash said:
...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. ?(
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.
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.
Yash said:
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.
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
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