SQL Server 7 Transaction Logs Issues

Posted on 2013-11-05
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 78

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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 78

Expert Comment

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

Accepted Solution

Zberteoc earned 500 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 26

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to record audio from input sources to your PC – connected devices, connected preamp to record vinyl discs, streaming media, that play through your audio card: Vista, Windows 7, Windows 8, Windows 8.1 and Windows 10 – both 32 bit & 64.
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

762 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