SQL Server 7 Transaction Logs Issues

Posted on 2013-11-05
Medium Priority
Last Modified: 2013-11-08
Over the week my database server transaction log was full. With our app people could select from the database but could not update or insert into the database. In the past we have just truncated the transaction logs. After that, everything was back to normal.

This week I truncated the transaction logs, and shrink that database. Now we can select, update, and insert into the database. The only issue is when we do a big job, and to a lot on inserting or updating, we get the following error:

Database error: S1008:[Microsft][ODBC]Operation canceled

Open in new window

We never had this issue before, I am assuming the that is the same as a timeout error.
Question by:nate0187
LVL 16

Expert Comment

ID: 39626282
Well the reason you couldn't do anything but select was that SQL Server writes every action that changes data into the log, then does it, then marks it done. The way to stop from filling up your disk with huge transaction logs is to do a transaction log backup after you do a data backup. Then truncate the log to a reasonable size (not too big, but not tiny either). Then watch and when it starts to grow do another backup. every time you do a backup the backed up parts of the log are marked as no longer needed and are overwritten, as SQL Server then "knows" that you are safely keeping the backup of the log. If disaster strikes you restore from the last backup and then each log in (chronological) turn, thus repeating all the changes that have taken place up until the most recent log backup.

 Your S1008 is, as it says, Asynchronous Operation Cancelled. You're right in that it may be a time-out: what front-end to the ODBC connection are you using?  
You should be able to increase the timeout value in the front-end, or maybe in the registry (it should be HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\SQL Server\CPTimeout)

The next question is, of course, to ask why it's happening. Doing a big job, with lots of inserts and deletes, means that you're putting a lot of entries into the log file. This is probably set to auto-grow, so every few megabytes or so will be asking the operating system for a little more disk space. Because it's only asking for little bits at a time it'll be getting whatever the OS has available, and if you've been doing this for a long time (as you probably have with a SQL Server 7 installation, I'd guess thatnot only is allocation of those megabytes quite slow, but also when it does get them they're in tiny bits scattered all over the place! This all slows down the database server, very possibly to the point where the ODBC timeouts are finally being tripped!

If I'm right about that then I would shrink your log file to the absolute minimum, defrag the disk drive its on, and then expand it to about half the free size of the disk.


LVL 81

Expert Comment

ID: 39626300
To the above, youse enterprise manager to setup a scheduled regular transaction log backup.
This will free up the space within the log of transactions that have completed, the issue you may have is a stuck active transaction preventing the log from freeing up space.
Dbcc opentran(databasename) to check on active transactions.

Make sure the filesystem/drive where the transaction log is is not running/ran out of space.
LVL 35

Expert Comment

by:David Todd
ID: 39626413

If you don't need the transaction log backups and recovery, think about changing the recovery model from full to simple - way less hassle.

And of course, about time you thought about upgrading to a current version of sql. Its so long ago I've forgotten if there is restore to point in time with SQL 7, or if this is a more recent feature.

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

LVL 81

Expert Comment

ID: 39626440
If memory serves me right, it does have a restore in time option.
LVL 27

Accepted Solution

Zberteoc earned 1500 total points
ID: 39633549
One advice: migrate to SQL 2012 express. It is free and probably much more powerful that SQL 7. :).

It might sound as a joke but think of it like it isn't.

Author Closing Comment

ID: 39633662
I detached the database from SQL 7 and installed SQL Server 2008 Enterprise on another server. I attached the database to the SQL Server 2008, and problem solved. This was needing to be done from some time now anyway, so bedsides some down time, it was a blessing.  Thank you all for your help
LVL 27

Expert Comment

ID: 39633781
One thing though. The transaction log grows for a reason and just shrinking it manually is not the right way. There are couple of scenarios to address this issue:

1. If your database is set with Simple recovery the transaction log will shrink by itself after the checkpoint is executed and the transactions are committed. This will prevent it from growing unless you have a huge transaction that needs a lot of space. Not likely though. This is the simplest solution, hence the Simple recovery mode. The disadvantage is that if something happens to the database and you need to restore you will need the last FULL backup available and if that was done a week ago you will lose everything done since. With this solution you will have to make sure you do full backups more often, like at least daily if not twice a day. This works if the database is relatively small and full backup is fast, under, let's say, 5 minutes.

2. If your database is large and writes are done daily then you will have to use FULL recovery mode in which case you will have to setup a weekly FULL backup, a daily DIFF backup and transaction log backup every, let's say, 15 min. This way the most you will lose in a disaster scenario will be the work done since the last transaction log backup and the failure moment, which is less than 15 min.

In any of these 2 scenarios your transaction log will be kept under reasonable size, which should not be shrunk. When needed the SQL server will increase the size which is a resource consuming process. Otherwise it will just reuse the space it already has.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Dropbox has a relatively new feature called Smart Sync.  This feature allows Dropbox Professional (not plus) and Dropbox Business (if enabled) users to store information in Dropbox WITHOUT storing any files on their computer.
Virtualization software lets you run different versions of Windows, Ubuntu Linux and other versions of Linux all at the same time, rather than running each one directly from your computer's hard drive.
Viewers will learn how the fundamental information of how to create a table.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question