"Stuck" transactions - HELP!

[Avilin]

Ok hopefully someone out here might be able to help me troubleshoot a SQL 7.0 installation. It has been having some real weird - hair yanking issues the last few weeks.

The database is an established SQL database (about 6 years old) and an established production server (about 2 years old).

A few weeks ago an end of day stored procedure that is run nightly from a job starting "sticking". Not failing - but just not returning. The process, which starts at 2am would still be running at 8am (it normally takes less than a minute to run). Some other times it WOULD fail - the error code it returned was "SQL Server returned an invalid token." The process would not fail every night - just sometimes.

I ran the stored procedure on the backup server and about 9 out of 10 times it would stick. (There was not other activity on the backup server at the time so locks were not really an issue.)

I tried a lot of things to try and make the SP never stick (although this is a stored procedure that has existed for 6+ years!). I ran profiler on the stored procedure and the most confusing thing occurs - the profiler reports that the stored procedure DOES indeed complete normally - however the stored procedure never returns from Query Analyzer!

Oh and that is not all!

Every hour, for the last 6 years, the server runs a job to back up the transaction log. Well now 2 out of 4 days last week that job also "stuck"! I examined the database when the process was "stuck" and there were NO locks nor were there any open transactions. Everything seems to be fine except that the command never returns. Of course this does cause other locks which causes other processes later on to also stick because of the locks.

When I run the profiler - it also states that one of my tables has out of date statistics on a column that is not part of an index..? Whey would it give me that warning on a column that does not have an index?

Things I have tried:
- Running DBCC Checkdb (fine)
- Shrinking DB, backing up, dropping DB, and restoring backup
- Scripting several of the tables that I think might be corrupt somehow (the ones with the warnings I mentioned above) and exporting the data, and recreating the tables
- Running all the SQL maintenance routines that I know of on the database
- Checking for locks and open transactions

Any ideas why this stored procedure might have starting ¡§sticking¡¨, and more importantly, the log backup? Any ideas on how to troubleshoot or better yet ¡V how to fix it!

The client is willing to upgrade to 2000, however if we go through this and it does NOT fix the problem ¡V well that would not be good! ƒº
I have also thought of scripting the entire database, exporting all data, and re-importing. (Yikes!)

[MAK]

what is the SQL Server SP and Windows SP on that box?

Did anybody install anything new on that box?

Try to delete and re-create the job.

If you are not on SP4 + 818-hotfix, install it.

[Avilin]

I do know it has SP 4 on it, I am not sure about the hot fix. The OS has the latest updates.
However, keep in mind that it also acts the same way on the backup server... which makes me believe it is something in the database and not something to do with the installation.

Also, the stored procedure that "sticks" has been created and recreated 1000 times in the last week trying different things. The transaction log backup job has not been recreated, but since it is a simple call to a SQL SP, I don't think that would make any difference.

Thanks for the ideas.

[rmiao]

Then run 'select @@version' to check sql7 sp and fix level.

[MAK]

http://www.sqlsecurity.com/DesktopDe...ndex=3&tabid=4