Search This Blog

Wednesday, September 29, 2010

Error: 26049, Severity: 16, State: 1 :Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\XXXXX ]. Error: 0x5

There might be many reasons and many solutions for this kind of error.But let me explain my situation :) ...For testing I installed a new default instance on one of the test servers.The setup was successful .However , later one of the other named instance did not come up after the restart.

The errors were :

2010-09-30 03:17:31.65 Server Error: 26049, Severity: 16, State: 1.
2010-09-30 03:17:31.65 Server Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\XXXXX ]. Error: 0x5
2010-09-30 03:17:31.65 Server Error: 17182, Severity: 16, State: 1.
2010-09-30 03:17:31.65 Server TDSSNIClient initialization failed with error 0x5, status code 0x40.
2010-09-30 03:17:31.65 Server Error: 17182, Severity: 16, State: 1.
2010-09-30 03:17:31.65 Server TDSSNIClient initialization failed with error 0x5, status code 0x1.
2010-09-30 03:17:31.65 Server Error: 17826, Severity: 18, State: 3.
2010-09-30 03:17:31.65 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2010-09-30 03:17:31.65 Server Error: 17120, Severity: 16, State: 1.
2010-09-30 03:17:31.65 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

The issue is quite simple unlike it looks like .I tried everything like changing the named pipe , etc. etc.
Assuming, that 0x5 is the OS error which means Access Denied ,I gave the permission to the domain ID on the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer.
This resolved the issue .

status code 0x40 means that there is an issue with Shared memory listener
status code 0x50 means that there is an issue with Named pipe listener
status code 0x0A means that there is an issue with TCP/IP listener

Please go through this MSDN blog (which has one more link in it).
http://blogs.msdn.com/b/sql_protocols/archive/2006/03/09/546655.aspx?wa=wsignin1.0

Happy Learning !!!

Tuesday, September 28, 2010

A simple VB script to retain Errorlogs worth 90 days (or as you like)

So far ,I heard retaining X number of errlogs which is widely used (So I am not writing that script here)...But one of our clients asked us to retain errlogs worth only 90 days .The client was not ready to recycle the errorlogs and wanted us to keep them to the default ...

Finally , we could come out with a simple VB script that can do it .The script code is mentioned below .

Code :


sFolder = "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG"
iMaxAge = 90
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FolderExists(sFolder) Then
for each oFile in oFSO.GetFolder(sFolder).Files
If DateDiff("d", oFile.DateLastModified, Now) > iMaxAge and (oFile.name= "ERRORLOG.1" or oFile.name= "ERRORLOG.2" or oFile.name= "ERRORLOG.3" or oFile.name= "ERRORLOG.4" or oFile.name= "ERRORLOG.5" or oFile.name= "ERRORLOG.6") Then
wscript.echo "Deleting" &oFile.Name
oFile.Delete
End If
next
End If

You will need to create a scheduled task/Or SQL Server job using xp_cmdshell, to run at a specific time .Once its kicked off , if any of the files mentioned in the code (Note : errorlog will not be tried upon) have a timestamp greater than 90 days from the day you are executing the file , It will delete those files ...for example if I have 7 files below :

File Timestamp
Errorlog 12/9/2009
Errorlog.1 12/8/2009
Errorlog.2 12/7/2009
Errorlog.3 12/6/2009
Errorlog.4 12/5/2009
Errorlog.5 12/4/2009
Errorlog.6 12/3/2009

The files deleted will be : Errorlog.3,Errorlog.4,Errorlog.5 and Errorlog.6

You need to change the Path of sFolder variable ..
Happy learning ...

Abhay

checkODBCConnectError: sqlstate = 28000; native error = 4818; message = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'XXXXX\clusterservice

a little Background :
As per our security guidelines Builtin\Administrator login should be removed from all the SQL Server instances.It was implemented on all the SQL Server instances including those which are on MCSC (Windows Cluster).
After that, the nodes were rebooted due to patching requirements .The nodes came up , but SQL Server did not :D ...

Error in cluster logs (you will not find it in SQL Server logs) :

ERR SQL Server : [sqsrvres] checkODBCConnectError: sqlstate = 28000; native error = 4818; message = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'XXXXX\clusterservice'.
ERR SQL Server : [sqsrvres] ODBC sqldriverconnect failed

The error was clear .The cluster service was not able to login to SQL Server through user XXXXX\clusterservice but via a LOGIN ...That login is BUILTIN\Administrators.
But why it needs to login to SQL Server ?? Because it needs to run the isAlive check to make sure that the SQL Server is up and running .It also runs the looksalive (its a function)check but that does not need to query SQL Server .Is Alive check runs select @@servername and waits for the return message through ODBC client (in our case its SQL Server Native client).Thus the Isalive check was not able to create a trusted connection and we lost the access to Virtual server.

So, in a SQL Server 2005\2008 failover cluster installation, the cluster service account relies on membership in the BUILTIN\Administrators group to log on to SQL Server 2005\2008 to run the IsAlive check.If you remove the BUILTIN\Administrators group from a failover cluster, you must explicitly grant the MSCS service account permissions to log on to the SQL Server 2005 failover cluster.

The SQL Server 2005 resource starts an instance of the Sqlcmd.exe utility under the security context of the MSCS service account. Then, the SQL Server 2005 resource runs an SQL script over a dedicated administrator connection (DAC) that samples various dynamic management views (DMV). Because a DAC connection is used to collect some diagnostic data, the clustering service account must be provisioned in the SYSADMIN fixed server role. If later someone says that clustering service account cannot be provisioned in the SYSADMIN fixed server role, then we can create a login for cluster service account that is not given the SYSADMIN fixed server role .I have not tested it yet .So cannot confirm that this will work on not ...

Commands :
CREATE LOGIN [\] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
EXEC master.sp_addsrvrolemember @loginame = N'\ ', @rolename = N'sysadmin'

happly learning .....
Regards
Abhay

Monday, September 20, 2010

Msg 22004, Level 16, State 1, Line 0 :Failed to open loopback connection. Please see event log for more information.Failed to open loopback connection. Please see event log for more information.

I am back with one more solution :).The problem was simple but the error made me thinking ..I was trying to do xp_readerrorlog on a small file .But my SPID hanged ..after some time I got this error :

SQL Server error in QA :
Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found

I read somewhere that this error comes when SQL Server Agent failes to come up.Yes my agent was down.But I faied to understand what is the relation between running xp_readerrlog and SQL Agent not running .Still, I tried to run the agent and got the error ...So something is related to SQL Agent here and that something is that If SQL Agent is not running , I can run xp_readerrlog successfully (I will prove it wrong later).

I checked the application logs immediately and got these errors :

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 09/20/2010
Time: 18:24:22
User: N/A
Computer: abchaudh
Description:
Severity: 16 Error:10061, OS: 10061 [Microsoft][SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.


For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 4d 27 00 00 0a 00 00 00 M'......
0008: 12 00 00 00 61 00 62 00 ....a.b.
0010: 63 00 68 00 61 00 75 00 c.h.a.u.
0018: 64 00 68 00 00 00 0e 00 d.h.....
0020: 00 00 6d 00 61 00 73 00 ..m.a.s.
0028: 74 00 65 00 72 00 00 00 t.e.r...


Event Type: Error
Event Source: SQLAgent$CORRUPT
Event Category: Service Control
Event ID: 103
Date: 09/20/2010
Time: 18:24:45
User: N/A
Computer: abchaudh
Description:
SQLServerAgent could not be started (reason: Unable to connect to server 'abchaudh\CORRUPT'; SQLServerAgent cannot start).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type: Information
Event Source: SQLAgent$CORRUPT
Event Category: Service Control
Event ID: 102
Date: 09/20/2010
Time: 18:24:52
User: N/A
Computer: abchaudh
Description:
SQLServerAgent service successfully stopped.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

I also checked the SQL Agent logs :

SQL Agent logs :

2010-09-20 18:25:36 - ! [298] SQLServer Error: 10061, TCP Provider: No connection could be made because the target machine actively refused it. [SQLSTATE 08001]
2010-09-20 18:25:36 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2010-09-20 18:25:36 - ! [298] SQLServer Error: 10061, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
2010-09-20 18:25:36 - ! [000] Unable to connect to server 'abchaudh\CORRUPT'; SQLServerAgent cannot start
2010-09-20 18:25:42 - ! [298] SQLServer Error: 10061, TCP Provider: No connection could be made because the target machine actively refused it. [SQLSTATE 08001]
2010-09-20 18:25:42 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2010-09-20 18:25:42 - ! [298] SQLServer Error: 10061, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
2010-09-20 18:25:42 - ! [382] Logon to server 'abchaudh\CORRUPT' failed (DisableAgentXPs)
2010-09-20 18:25:43 - ? [098] SQLServerAgent terminated (normally)

So, now I have 2 issues : SQL Agent is not running and xp_readerrorlog is timing out.

.If you see one of my older posts on "target machine actively refuses it" , you will have some information .

So I opened CLICONFG and found 3 incorrect aliases which were not using the right port ..

I removed them and SQL Agent came on line ...xp_read errorlog also started working ...

I stopped SQL Agent and stil everything was working ...
So the issue was that xp_readerrlog tries to connect to SQL Server but stucks due to the Alias pointing to incorrect port.

But this does not affect SQL Server service .To check why its not affecting SQL Server service , I disabled the sahred memory protocol and BANG....SQL Server connection failed ...

Happy learning ....