Unanswered: SOS! my SQL65 transaction log getting smaller
I use SQL 6.5 and after I did some delete statement i found my available Log space getting smaller. I has already set the DB option to "truncate log on checkpoint", why the log is still getting smaller, even I set this option on? I tried to disable the option and backup the transation log but i was told that must use backup database since it never be backup. but after backup db i still get same error. In my db i used to add one device which is used for both data and log, is this cause my problem? if so, is there any solution? I have tried to backup the DB to a file and re-create a new DB which use seperate data and log device, and restore the DB backup file into new created DB, but it seems don't work. Is there any solution?
No, the space is still has some space for current use. But I still have a question that, i have a testing SQL6.5 server, and re-created new DB with seperate data and log device and restore the DB into this new DB, and enable the "truncate log on checkpoint" but after I do some delete statement, the available log size still getting smaller even I restart the SQL server. Why? I will test if I disable the DB option and use dump the transaction log to check if this can work. But I still can not understand why i enable the DB option but the log available size still get smaller? Is this a SQL65 a bug or as known problem in SQL65?
Originally posted by nigelrivett
Is it out of space completely i.e. do you get a space error?
If so there's not much you can do without increasing the size of the device a bit as it can't checkpoint to activate any of the changes.
Check there are no open transactions with dbcc opentran. If there is an open transaction (e.g. an active update) then it can't truncate the log as an entry will be held by the transaction.
You shouldn't share data and log in the same device as it becomes difficult to control - fortunately the option was removed with v7.
Try creating a new device for the log and using alter database (I think) to use it.
If that is the case then it will evetually run out of space so your question is "Does v6.5 always crash due to running out of space".
And the answer is of course no.
You can use dbcc loginfo to see if you have any entries that cannot be cleared but look for active queries or open transactiopns which will stop log space from being freed.
Make sure the database is being checkpointed - do a manual one or truncate the log manually if necessary.