Home Forums Discussions Problems SQL shutting down on it's own...

Hey all, I've had an issue over the last week or so that's driving me nuts. Every night around midnight, the amazing charts SQL server just stops. I have to stay up nightly and manually start the service again before we open or AC will obviously not run. I'm running SQL Server 2005 on SBS2003, and I've gone over the error logs ad nauseum trying to figure out what could be causing it. You could set your watch to SQL stopping.

I initially thought that the AC Backup could be causing the issue (like stopping the sql server while it backs up the database), but I scheduled it differently and the problem remains. Anyone else dealing with something similar?

Oh yes, oh yes.
Every evening between 5:45 and 6:15. Has been happening since we went to version 4.

We also run SB2003.
We only use the SQL that comes packaged with AC.

How is SQL Server Amazing Charts logging on? As local? Also is it set to Automatic? My guess is yes to both as that is the default.

Vicki,

Send me your log files from SBS 2003. Control Panel, Administrative Tools, Event Viewer. I just need Application and System. Right click on each and go to Properties and then Filter.

For Event types, uncheck everything except Warning and Error. On the bottom, choose from October 1, 2009 to last event.

Now, right click on each and choose Export List and save it as a Text (Tab Delimited) (*.txt) file. Send them both to my email address in my profile.

It is interesting what both of you have in common. Royce has the problem every midnight and Vicki at 5:45PM. Which would lead one to believe that something else is occurring at those exact times which affects the SQL Server Amazing Charts service. While one would think that it would be the AC Backup, there are two things that argue against that. First, as you stated Royce, you changed the time, and second, quite a few people use the backup program and haven't had that issue. The backup program does not work by stopping and starting the service.

If you look in Control Panel -> Scheduled Tasks, you may notice something starting at that exact time. Check Windows Updates and see if it is on automatic. If so, shut that down.

Finally, I am running SP2 for a service pack on SQL Server Express 2005. They now have SP3 out. I don't know if that would help. My guess is it wouldn't.

Looking at the log files generated by SQL would help in knowing of sql is being shut down or killed. Also look at what is happening right before the time it shuts down

The logs are in this location.
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

There will be 6 log files, but the one without a number extension is the one you need. You can open that in notepad.

Start mode - Automatic
Log on As- LocalSystem

Here's that log, I assumed it would go back further:

2009-11-02 00:09:39.03 Server (c) 2005 Microsoft Corporation.
2009-11-02 00:09:39.03 Server All rights reserved.
2009-11-02 00:09:39.03 Server Server process ID is 4428.
2009-11-02 00:09:39.03 Server Authentication mode is MIXED.
2009-11-02 00:09:39.03 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2009-11-02 00:09:39.03 Server This instance of SQL Server last reported using a process ID of 980 at 11/2/2009 12:01:09 AM (local) 11/2/2009 6:01:09 AM (UTC). This is an informational message only; no user action is required.
2009-11-02 00:09:39.03 Server Registry startup parameters:
2009-11-02 00:09:39.05 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2009-11-02 00:09:39.05 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2009-11-02 00:09:39.05 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2009-11-02 00:09:39.06 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2009-11-02 00:09:39.06 Server Detected 2 CPUs. This is an informational message; no user action is required.
2009-11-02 00:09:39.43 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2009-11-02 00:09:39.70 Server Database mirroring has been enabled on this instance of SQL Server.
2009-11-02 00:09:39.76 spid5s Starting up database 'master'.
2009-11-02 00:09:39.92 spid5s 3 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2009-11-02 00:09:39.96 spid5s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2009-11-02 00:09:39.96 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2009-11-02 00:09:40.10 spid5s SQL Trace ID 1 was started by login "sa".
2009-11-02 00:09:40.14 spid5s Starting up database 'mssqlsystemresource'.
2009-11-02 00:09:40.15 spid5s The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
2009-11-02 00:09:40.30 spid8s Starting up database 'model'.
2009-11-02 00:09:40.31 spid5s Server name is 'SERVER\AMAZINGCHARTS'. This is an informational message only. No user action is required.
2009-11-02 00:09:40.31 spid5s Starting up database 'msdb'.
2009-11-02 00:09:40.45 Server The certificate was successfully loaded for encryption.
2009-11-02 00:09:40.65 spid8s Clearing tempdb database.
2009-11-02 00:09:40.73 Server Server is listening on [ 'any' <ipv6> 61067].
2009-11-02 00:09:40.80 Server Server is listening on [ 'any' <ipv4> 61067].
2009-11-02 00:09:40.80 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\AMAZINGCHARTS ].
2009-11-02 00:09:40.80 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$AMAZINGCHARTS\sql\query ].
2009-11-02 00:09:40.80 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2009-11-02 00:09:41.03 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2009-11-02 00:09:41.50 spid8s Starting up database 'tempdb'.
2009-11-02 00:09:41.67 spid5s Recovery is complete. This is an informational message only. No user action is required.
2009-11-02 00:09:41.69 spid11s The Service Broker protocol transport is disabled or not configured.
2009-11-02 00:09:41.69 spid11s The Database Mirroring protocol transport is disabled or not configured.
2009-11-02 00:09:41.74 spid11s Service Broker manager has started.
2009-11-02 01:00:46.62 spid1s Server resumed execution after being idle 3031 seconds. Reason: timer event.
2009-11-02 07:46:22.51 spid53 Starting up database 'AmazingCharts'.
2009-11-02 07:46:30.82 spid55 Starting up database 'Codes'.
2009-11-02 07:46:31.09 spid56 Starting up database 'Meds'.

This will send error messages at the system and application level. Please send these logs as well.

Had this problem too. The cause in my case was a SQL Server 2005 update that was stopping the SQL service to install the update. The update kept failing, which caused the issue to repeat daily.

I think this may be my issue as well. I kept seeing a failed SQL update, and it makes sense that it would try and reinstall a pending update at regular intervals. I turned off automatic updates and I'll see what happens now.

that is a good possibility that sql update is causing the issue

For those still having trouble, please send the logs as specified above. And, what service packs are you running?

Solved:

The SQL Update was the issue. I turned off automatic updates on SBS and the problem went with it. I guess when it tries to unpack and install itself it runs into issues and kills the SQL service and stalls the installation for some reason. I hope the update was an just a normal exploit patch and not an upgrade/update.

Great! Can you download it manually and possibly stop the service temporarily and see if it will download?

Do you know what the number of the update is? I would definitely email this to Jon so he knows about it. It is a serious bug. It may be a Microsoft issue, but who knows.

Glad it is solved.

How about Vicki?

It's the Security Update for SQL Server 2005 Service Pack 3 (KB970892), here's the MS link to the specifics:

http://support.microsoft.com/kb/970892

I applaud those who have discovered the likely problem to this. And, I should say given the fact the WSUS (Windows Server Update Services 3.0) takes a PhD in calculus to use, I do my updates manually. It is hard to get my staff to remember to update their PCs. I do like to know what they are updating, but I am not too worried about clients. So, auto updates aren't that bad of an idea. But, it is never a good idea on your main computer. For one, you may not want or need all of the updates. That new printer driver for your Dell printer may not be necessary given your current one is working just fine. But, as the article states below, some updates such as SQL Server 2005 SP3 may require user intervention, it may not install without your being there, hence the issues above. Plus, do you really want to install a full SP without researching it first or doing a full backup or even asking on here if people have tried it first? We should be able to trust Microsoft, but you have to decide, as we all do, between the recommendation of keeping your computer up to date vs the age old adage, why fix it if it ain't broke?

The other issue with autoupdates, is when you come to the office fully expecting the antivirus to have run and your backups to be done, only to find the server was rebooted, because of the autoupdate and its required reboot.


* When you use Microsoft Update to install SQL Server 2005 SP3, the installation is unattended unless components are clustered. If components are clustered, you must follow the prompts to complete the installation.
* Microsoft Update upgrades all instances of SQL Server 2005 to SQL Server 2005 SP3. For example, if you have two instances of SQL Server 2005 that are running on the computer, both instances are upgraded to SQL Server 2005 SP3.
* The SQL Server 2005 Express Edition updates are shown when you do an express scan and are available as an automatic update. The other editions of SQL Server are available as a custom update or as an optional update.
* For more information about Microsoft Update, visit the following Microsoft Web site:
http://update.microsoft.com/microsoftupdate/v6/about.aspx (http://update.microsoft.com/microsoftupdate/v6/about.aspx)

Hey Bert, just had some time to look back at this post. We are stil having this trouble. Will send you the logs next week.
Thanks

Good, 'cause the SQL guru who is helping me is getting antsy and yelling at me. Apparently, my Experts-Exchange board isn't quite as nice as ours. Plus, it's hard to go from being pretty knowledgeable on this board to being really dumb on that one.

Hi Friend,
We'll try to do it today. Really appreciate your help.

Hi y'all,

I'm also having the same problem; xp freezes on its own qhs. errors in the event log seem to indicate SQL server. I have auto updates turned off. going to install SQL server 2005 sp3 and hope it will work. I was unable to discern how SQL Server Amazing Charts is logging on: Local and if it is set to automatic. Any help is appreciated.

SQL Server (Amazing Charts) starts Automatically and, of course, runs only on the PC that is designated for your databases.

XP Pro or Home
P2P or Server/Client
Domain or no domain
Switch/Hub/Router ?
Network topagraphy?
When do you run backups?
Are they automatic?
Antivirus scanners?
Are backup programs compatible with SQL Server?
Have you uninstalled/reinstalled?

Something that worked for me recently was going to Start>Control Panel>Add or Remove Programs and selecting Microsoft .Net Framework 1.1, 2.0, 3.0 and 3.5 and clicking Change/Remove then selecting Repair, one at a time. To discern how the SQL service is running click Start>Run and type in services.msc and go through the list. View properties of any item to see what logon it is using. If you need more detail or more tools (be careful though, it's a jungle in SQL) download Microsoft's SQL Management Studio Express for free here: http://www.microsoft.com/downloads/...E3D-94B8-5A0F62BF7796&displaylang=en

Hi Bert and Paul,

thanks for the advice. I'm an IMP, so just running 1 computer at this time that I work on. see below for answers to your questions (best I could).

XP Pro or Home: Pro
P2P or Server/Client: P2P
Domain or no domain: none
Switch/Hub/Router ? none
Network topagraphy?
When do you run backups? manually qhs. seems to freeze sometimes part way through.
Are they automatic? no. used to check that box, but it never seemed to occur.
Antivirus scanners? AVG antivirus. doesn't seem to effect SQL freezes.
Are backup programs compatible with SQL Server? not using one. just dropbox.
Have you uninstalled/reinstalled? SQL server or AC? I have reinstalled AC.

I will try the .net repairs and see how that goes. many thanks.

btw, installing sp3 for sql server did not work and didn't show up in add/remove components list.

If those .net repairs don't fix it, I am unsure of what to advise you to do. Experts-exchange.com is amazing and wonderful but nobody ever takes Bert's advice to try it. If you are willing to sign up for the free trial you may be able to find an answer from some very knowledgeable people.

Hi Paul,

.net repairs didn't work. I will take a look at experts-exchange.com. thanks!

391 questions and counting....