Link to home
Create AccountLog in
Avatar of swedishmotors
swedishmotorsFlag for United States of America

asked on

Windows Server 2008 R2 SQL Database Clients Loose Connection After 12 hrs

I'm running Windows Server 2008 R2
My Clients PC's are running Xp Pro
I'm running a SQL Database server.

After about 12 hrs the clients loose the connection to the SQL Database.

If I reboot the server the clients find the connection again.

Where do I start to diagnose why this is happening?
Avatar of Rich Weissler
Rich Weissler

I'll start by saying, this is a daunting question.   We might not be able to resolve this, but lets roll up our sleeves and see what we can figure out.  

You've asking how to diagnose the problem.  If it were me, I'd start by collecting information, and asking questions:

Which version of MS SQL is running on the server?
  (You can 'SELECT @@VERSION', and post the response line here.)
Are only SQL connections impacted?  (I.e. can you still ping the server, can you still RDP to the server? etc.)
Do any relevant messages appear in the SQL Errorlog, Windows Application, or Windows System Logs?  (The Windows Security Log might not be a bad thing to look at, but I wouldn't necessary start there.)
 When clients are no longer able to access SQL, can you open the Management Studio on the server console connection?  Can you see any other connections (either query sp_who, or by "select * from dm_exec_connections" (if SQL >=2005)

On the server, I'd be tempted to run "netstat" from a command prompt, and and see if there are an unexpected number of connections to the server.


When the problem occurs, does it occur to all the clients at approximately the same time?  How many clients do you have?  What application is being used?  Using an ODBC driver on the client?  If yes, which driver?
Do the clients receive a disconnect message, or any other specific messages when the error occurs?  
Do any relevant messages in the Windows Application log on the client machines?
Avatar of swedishmotors

ASKER

Express Edition on Windows NT 6.0 (Build 6002: Service Pack 2)

The issue did not occur this morning.
The mapped drives disconnect as well, I am looking into a solution to resolve this as well.

I will check RDP to the the server when the issue occurs again.
Hmm.  I think that line is only giving the end -- it doesn't contain the SQL version.. just Windows version.  (And it looks like Windows 2008, Not R2.)

The good news is that if it's mapped drives too, that leads us away from SQL, and maybe towards the network interface or the physical network hardware, etc.
Even before the problem reoccurs, can you see anything in the System or Application event logs on the server from previous nights which might illuminate a source of the problem?
The issue occurred again this morning.
Upon reboot I get this error:

Description:
  Stopped working

Files that help describe the problem:
  C:\Users\Administrator.GFS-MASTER\AppData\Local\Temp\1\WERC956.tmp.version.txt
  C:\Users\Administrator.GFS-MASTER\AppData\Local\Temp\1\WERDA96.tmp.mdmp

Read our privacy statement:
  http://go.microsoft.com/fwlink/?linkid=50163&clcid=0x0409
I interpret that to mean that SQL crashed... something crashed anyway.  The mdmp is a minidump file, and the text file _might_ have something useful, but I doubt it.  Open it up in notepad?

If it's SQL that stopped responding, check the LOGS directory, ERRORLOG dated just before the occurrance, and look at the end of the file, and see if it has any additional information there.

Because you were losing fileshares before though, it's not impossible it's something else (other than SQL) though.  Is there any additional information in the logs around the same time which indicates which service 'Stopped working'?
C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ErrorDumps\
folder is empty.

In the logs directory I do not have a ERRORLOG file

I found the mdmp file but it is unreadble
Fullscreen-capture-11212012-9214.bmp
Fullscreen-capture-11212012-9013.bmp
Fullscreen-capture-11212012-8555.bmp
Data in the 90\. directories relate to the SQL client, rather than the SQL server.
The errorlog I was asking about earlier would be in [SQL Install Directory]\Microsoft SQL Server\[InstanceDirectory]\MSSQL\Logs if the defaults were selected.  This would be on the SQL server.  If it's 64 bit SQL, it won't be in a directory with (x86), so if the server only has a C: drive, check the other Program Files directory.  (I usually have SQL installed on drives other than C:, so it might be worth checking there.)

The '90' in the previous directory path would indicate that we're working with SQL 2005.  

Given that there are problems with Mapped Drives which seem to happen at the same time however, I'm thinking the problem has a wider scope than SQL.  It'll still be good to find the SQL ERRORLOG file, because it _might_ give us a clue, especially since you are experiencing a program crash -- but open Event Viewer (often in the Administrative Tools from All Programs from the Start Button), and look through the SYSTEM event log.  Specifically look through the times around when your problems occur.
This morning I found that I could connect to the server shared/mapped drives.
But not the SQL database.
I ran osql -L from the command prompt and can see the server in the list that is returned.
Yet, the program I use to cannot connect to the database.
Also, when I launch the software I am having an issue with it also return a list of available databases and the one on my server I am having an issue with is displayed, but I cannot connect.

I am wondering if the issue is a that the permissions for the connection are timed out.
Here is the error log for today, the issue did occur today:

2012-11-26 08:02:39.20 Server      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
      Nov 24 2008 13:01:59
      Copyright (c) 1988-2005 Microsoft Corporation
      Express Edition on Windows NT 6.0 (Build 6002: Service Pack 2)

2012-11-26 08:02:39.23 Server      (c) 2005 Microsoft Corporation.
2012-11-26 08:02:39.23 Server      All rights reserved.
2012-11-26 08:02:39.23 Server      Server process ID is 2200.
2012-11-26 08:02:39.23 Server      Authentication mode is MIXED.
2012-11-26 08:02:39.23 Server      Logging SQL Server messages in file 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2012-11-26 08:02:39.23 Server      This instance of SQL Server last reported using a process ID of 2192 at 11/25/2012 3:00:19 AM (local) 11/25/2012 8:00:19 AM (UTC). This is an informational message only; no user action is required.
2012-11-26 08:02:39.23 Server      Registry startup parameters:
2012-11-26 08:02:39.25 Server             -d C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2012-11-26 08:02:39.25 Server             -e C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2012-11-26 08:02:39.25 Server             -l C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2012-11-26 08:02:39.26 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2012-11-26 08:02:39.26 Server      Detected 8 CPUs. This is an informational message; no user action is required.
2012-11-26 08:02:41.10 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.
2012-11-26 08:02:42.28 Server      Database mirroring has been enabled on this instance of SQL Server.
2012-11-26 08:02:42.54 spid5s      Starting up database 'master'.
2012-11-26 08:02:43.05 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2012-11-26 08:02:43.59 spid5s      SQL Trace ID 1 was started by login "sa".
2012-11-26 08:02:43.79 spid5s      Starting up database 'mssqlsystemresource'.
2012-11-26 08:02:43.86 spid5s      The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
2012-11-26 08:02:45.18 spid8s      Starting up database 'model'.
2012-11-26 08:02:45.19 spid5s      Server name is 'GFS-MASTER\SHOPSTREAM'. This is an informational message only. No user action is required.
2012-11-26 08:02:45.22 spid5s      Starting up database 'msdb'.
2012-11-26 08:02:45.79 spid8s      Clearing tempdb database.
2012-11-26 08:02:46.62 Server      A self-generated certificate was successfully loaded for encryption.
2012-11-26 08:02:46.67 Server      Server is listening on [ 'any' <ipv6> 65120].
2012-11-26 08:02:46.68 Server      Server is listening on [ 'any' <ipv4> 65120].
2012-11-26 08:02:46.71 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SHOPSTREAM ].
2012-11-26 08:02:46.71 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SHOPSTREAM\sql\query ].
2012-11-26 08:02:46.73 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.
2012-11-26 08:02:46.76 spid8s      Starting up database 'tempdb'.
2012-11-26 08:02:46.82 Server      The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2012-11-26 08:02:46.82 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2012-11-26 08:02:46.98 spid5s      Recovery is complete. This is an informational message only. No user action is required.
2012-11-26 08:02:47.16 spid11s     The Service Broker protocol transport is disabled or not configured.
2012-11-26 08:02:47.18 spid11s     The Database Mirroring protocol transport is disabled or not configured.
2012-11-26 08:02:47.39 spid11s     Service Broker manager has started.
2012-11-26 08:02:51.41 spid51      Starting up database 'ShopMgt'.
2012-11-26 08:03:08.28 Backup      Database backed up. Database: ShopMgt, creation date(time): 2012/11/02(10:43:18), pages dumped: 41315, first LSN: 20084:44:37, last LSN: 20084:62:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\ProgramData\Mitchell1\DataProtection\Package\MGR SE-Mon-Full-0a688130-4a83-48ca-9a54-89caa1883bbb\ShopMgt.bak'}). This is an informational message only. No user action is required.
2012-11-26 08:03:29.20 Server      Server resumed execution after being idle 11 seconds: user activity awakened the server. This is an informational message only. No user action is required.
I suspect this is the ERRORLOG after you rebooted or restarted SQL to fix your problem.  (This log covers about fifty seconds, including boot and a single database backup.)  In the same directory, is there an ERRORLOG.1, which would be the previous log?  

I am concerned about the last message that appears there, however.  If you have the Management Tools available, right click the database, select Properties... select options in the dialog, and double check that the option for 'Auto Close' is False.  (If you don't have the management tools, I can track down the command line option...)
I set Autoclose to false.


Here is the previous log:

11/22/2012 3:00:16 AM (local) 11/22/2012 8:00:16 AM (UTC). This is an informational message only; no user action is required.
2012-11-23 08:36:03.45 Server      Registry startup parameters:
2012-11-23 08:36:03.47 Server             -d C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2012-11-23 08:36:03.47 Server             -e C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2012-11-23 08:36:03.47 Server             -l C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2012-11-23 08:36:03.50 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2012-11-23 08:36:03.50 Server      Detected 8 CPUs. This is an informational message; no user action is required.
2012-11-23 08:36:05.20 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.
2012-11-23 08:36:06.45 Server      Database mirroring has been enabled on this instance of SQL Server.
2012-11-23 08:36:06.72 spid5s      Starting up database 'master'.
2012-11-23 08:36:07.47 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2012-11-23 08:36:07.92 spid5s      SQL Trace ID 1 was started by login "sa".
2012-11-23 08:36:08.05 spid5s      Starting up database 'mssqlsystemresource'.
2012-11-23 08:36:08.10 spid5s      The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
2012-11-23 08:36:08.83 spid8s      Starting up database 'model'.
2012-11-23 08:36:08.87 spid5s      Server name is 'GFS-MASTER\SHOPSTREAM'. This is an informational message only. No user action is required.
2012-11-23 08:36:08.88 spid5s      Starting up database 'msdb'.
2012-11-23 08:36:09.37 spid8s      Clearing tempdb database.
2012-11-23 08:36:09.88 spid8s      Starting up database 'tempdb'.
2012-11-23 08:36:10.02 spid5s      Recovery is complete. This is an informational message only. No user action is required.
2012-11-23 08:36:10.20 spid11s     The Service Broker protocol transport is disabled or not configured.
2012-11-23 08:36:10.22 spid11s     The Database Mirroring protocol transport is disabled or not configured.
2012-11-23 08:36:10.66 spid11s     Service Broker manager has started.
2012-11-23 08:36:10.65 Server      A self-generated certificate was successfully loaded for encryption.
2012-11-23 08:36:10.70 Server      Server is listening on [ 'any' <ipv6> 65120].
2012-11-23 08:36:10.70 Server      Server is listening on [ 'any' <ipv4> 65120].
2012-11-23 08:36:10.73 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SHOPSTREAM ].
2012-11-23 08:36:10.73 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SHOPSTREAM\sql\query ].
2012-11-23 08:36:10.74 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.
2012-11-23 08:36:10.98 Server      The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2012-11-23 08:36:10.98 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2012-11-23 08:36:13.37 spid51      Starting up database 'ShopMgt'.
2012-11-23 08:36:29.19 Backup      Database backed up. Database: ShopMgt, creation date(time): 2012/11/02(10:43:18), pages dumped: 41243, first LSN: 19968:44:37, last LSN: 19968:62:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\ProgramData\Mitchell1\DataProtection\Package\MGR SE-Fri-Full-6785a3da-b5e3-41f3-ba39-83a8ad0572e1\ShopMgt.bak'}). This is an informational message only. No user action is required.
2012-11-23 08:36:44.17 Server      Server resumed execution after being idle 2 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2012-11-23 18:06:49.42 Backup      Database differential changes were backed up. Database: ShopMgt, creation date(time): 2012/11/02(10:43:18), pages dumped: 4227, first LSN: 20043:149:1, last LSN: 20043:151:1, full backup LSN: 19968:44:37, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\ProgramData\Mitchell1\DataProtection\Package\MGR SE-Fri-Diff-d7d1c831-efd6-45a3-8d4e-067f5f990d27\ShopMgt.dif'}). This is an informational message. No user action is required.
2012-11-24 03:00:17.49 spid11s     Service Broker manager has shut down.
2012-11-24 03:00:17.54 spid5s      SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
2012-11-24 03:00:17.55 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
SOLUTION
Avatar of Rich Weissler
Rich Weissler

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
See attached for a screen shot.
I do not see anything that occurred at 3:00AM
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I found this:
Installation Failure: Windows failed to install the following update with error 0x80070643: Microsoft SQL Server 2005 Express Edition Service Pack 4 (KB2463332).
3:01:53 AM

I tried running the update manually in windows update and get and error, see attached
Here is the log file from the windows update error:

- System

  - Provider

   [ Name]  Microsoft-Windows-WindowsUpdateClient
   [ Guid]  {945a8954-c147-4acd-923f-40c45405a658}
 
   EventID 20
 
   Version 0
 
   Level 2
 
   Task 1
 
   Opcode 13
 
   Keywords 0x8000000000000028
 
  - TimeCreated

   [ SystemTime]  2012-11-26T22:57:54.923Z
 
   EventRecordID 534203
 
   Correlation
 
  - Execution

   [ ProcessID]  560
   [ ThreadID]  7236
 
   Channel System
 
   Computer GFS-MASTER.GFS1.com
 
  - Security

   [ UserID]  S-1-5-18
 

- EventData

  errorCode 0x80070643
  updateTitle Microsoft SQL Server 2005 Express Edition Service Pack 4 (KB2463332)
  updateGuid {48F7E3B7-2C8F-4900-AE32-F3D8F29C988D}
  updateRevisionNumber 102


This is the most relevant help I have found, but there is not enough data to take any steps:

Ok, Finally I've got the Solution. After trying 1000 of probably workarounds and not succeed with them, i figured out a solution that worked for me. We've got this problem on different Sql 2005 Servers in our company. I read all the Error Logs and recognized, that the SP4 Update will acces a temporary folder that was created at the SP3 Update. Its logical that this folder is no longer available. So i Created this Folder (A Name with a lot numbers and letters ive got from the error log). Then I manually downloaded the SP3 Update and opened it. It automatically extracts itself to a new temp folder, i copied all these new temp files into my Folder that i've created earlier. After that i'd started the SP 4 Update (No care if Windows Update oder Manual) and it run through without failures! Hope this will also help some people!
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I plan to close this question and open a new one to resolve the issue with installing the update.
The final conclusion is that finding the data in the log files is the place to start.