Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
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 80

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 80

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

610 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