Solved

SQL Server 7 Transaction Logs Issues

Posted on 2013-11-05
7
258 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 76

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 76

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now