Help with SQL 6.5

I know this is an old version but the customer cannot upgrade at this time due to a Mac software issue. I am a newbie so forgive me if I ramble. Here is my question.

Windows Server 2003
SQL running version 6.5
Log size =1998 MB
Log Avail=600 MB
No maint. plan set up on this, and when I try to create one it warns me about running a maint. plan on a DB that is larger than 400 MB. When I try and trucate logs in EM seems like it runs but the size stays the same. The customer restarts the SQL service and users are then able to log in.

THe database used to run on a NT 4.0 box up till 6 months ago when it was moved to the 2003 box. It ran fine up till last week. The customer tells me that people have been getting errors logging in. In the event viewer the following error reports.

Event Type: Error
Event Source: MSSQLServer
Event Category: (2)
Event ID: 17060
Date: 8/4/2006
Time: 7:51:51 AM
User: N/A
Computer: AUX-SERVER
Description:
The description for Event ID ( 17060 ) in Source ( MSSQLServer ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Error : 701, Severity: 17, State: 2, There is insufficient system memory to run this query..
Data:
0000: bd 02 00 00 11 00 00 00 ......
0008: 00 00 00 00 07 00 00 00 ......
0010: 6d 61 73 74 65 72 00 master.
Any ideas? Thanks!!What are the errors that the users are getting?

And the all important question, what changed, and who changed it?|||There is insufficient system memory to run this query..I am not sure what changed...|||Is this happening every day, or does it take a few days to "build up"? Also, when it does happen, does everyone get the error message, or do a few people manage to get in, while others are locked out?|||Ok, there are a few different ways to solve this problem.

The underlying problem has to do with how SQL 6.5 allocates memory. There are issues with the way SQL 6.5 works in Windows 2000 and later releases.

The easy solution is to buy a copy of Microsoft Virtual Server, install that onto the box you're using to run SQL 6.5, then create a virtual machine and install Windows NT 4.0 in that virtual machine. At this point, you've got the problem contained and can manage it easily and effectively.

A much more difficult solution (but requiring no additional software or licenses) is to simply work to configure the SQL 6.5 instance so that it uses a fixed amount of memory, then adjust the XP settings in the registry so that they don't strangle themselves when they hit those limits. This isn't usually hard, but it is rather complex and it requires someone that really knows SQL 6.5 and its memory usage... It is not a job for someone without a lot of experience.

There are a number of other possible solutions, but they all have associated risks. You'll have to decide which one suits your needs best if you decide to head down any of these paths.

-PatP|||To answer Mcrowley...it happens every cpl days...all are not able to log in...|||Oh yeah, one relatively simple way to solve this problem if you can afford daily reboots is to reboot the machine every day. This works around the memory allocation problem by not allowing the machine to reach the threshold where it can't effectively allocate memory anymore.

If you can afford the daily reboots, then the simple answer is to just schedule a script to restart (http://www.microsoft.com/technet/scriptcenter/scripts/desktop/state/dmstvb07.mspx) the server.

-PatP|||the machine has been running for a few months, configured the same way, with no problems...why now did it start acting up? Took that long to build up? Total server memory is 1 gig. SQL Server is set up with 32768 (2K blocks) of memory. Like I said I am now well versed in SQL then alone version 6.5!|||The underlying problem depends on the number of occurances of certain behaviors. In other words the problem occurs after the ill-behaved code executes a certain number of times... That number depends on the hardware configuration, device drivers, services, etc.

You've probably just reached the point where the threshold is now low enough to become a "pain point" while it hadn't been one before. This could be because of hardware changes, patches, or even network changes that forced loading additional software/drivers that were configured but not used in the past.

-PatP|||Thanks Pat...would setting up a maintenance plan help for this database? When I try to set one up it warns against setting one up on database's larger than 400mb.|||Setting up a maintenance plan might or might not help with database performance, but it won't do diddly for helping with memory problems. If my analysis of what's causing the machine (SQL Server anyway) to become non-responsive is correct, then a maintenance plan won't make any difference.

SQL 6.5 is a much simpler creature than its successors. The maintenance plans were not too effective, and it was EASY to code a script that did a much better job, especially for databases over about 300 Mb or so. You could ensure basic database health with just two commands DBCC CHECKDB, and DBCC CHECKALLOC, but you still needed to keep an eye on the database on a regular basis to "keep the wheels on the bus"

-PatP