Solved

SQL Server 7 Transaction Logs Issues

Posted on 2013-11-05
7
269 Views
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.
0
Comment
Question by:nate0187
7 Comments
 
LVL 16

Expert Comment

by:DcpKing
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.

hth

Mike
0
 
LVL 77

Expert Comment

by:arnold
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.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39626413
Hi,

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.

Regards
  David
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 77

Expert Comment

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

Accepted Solution

by:
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.
0
 
LVL 2

Author Closing Comment

by:nate0187
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
0
 
LVL 26

Expert Comment

by:Zberteoc
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

While rebooting windows server 2003 server , it's showing "active directory rebuilding indices please wait" at startup. It took a little while for this process to complete and once we logged on not all the services were started so another reboot is …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

856 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