TCP/IP file sharing component
I need to identify which service (if that's indeed what I need to do) which handles the file sharing over TCP/IP.
We are running MS SQL 2005 on Windows Server 2003 and storing one the databases on a NAS device. It is working quite well except whenever the MS SQL machine restarts, MS SQL is not able to access the NAS device. To fix, all I have to do is restart MS SQL
(not the machine, just the database server) and then it works fine again until the next time the machine restarts.
It is as though, when the machine is starting, MS SQL starts before the machine is ready to do TCP/IP file sharing. I suppose the solution is to delay the starting of MS SQL until the machine is ready to network. I just need to know which of the many services
does this. Then I can configure MS SQL to not start until this networking service is started.
Any thoughts? Or if I am going down the wrong path, any corrections?
August 27th, 2010 2:38am
As far as I know, you may want to make the SQL Server Services dependent
on the Workstation service. You could try identifying the name of the
service using the get-service cmdlet or the services MMC snap-in
PS C:\> get-service | where-object {$_.DisplayName.Contains("Workstation")}
Status Name DisplayName
------ ---- -----------
Running LanmanWorkstation Workstation
PS C:\> get-service | where-object {$_.Name.Contains("SQL")}
Status Name DisplayName
------ ---- -----------
Running MSSQL$SQLEXPRESS SQL Server (SQLEXPRESS)
Stopped MSSQLServerADHe... SQL Active Directory Helper Service
Stopped SQLAgent$SQLEXP... SQL Server Agent (SQLEXPRESS)
Stopped SQLBrowser SQL Server Browser
Running SQLWriter SQL Server VSS Writer
You should then be able to configure the SQL Server service to depend on
the Workstation service. From an administrative command prompt,
C:\>sc config "MSSQL$SQLEXPRESS" depend= LanmanWorkstation
[SC] ChangeServiceConfig SUCCESS
Note that this will be different for your specific instance of SQL
Server (I simply modified my SQL Server Express installation),
Also note that if you use Powershell, sc may already be an alias for
set-content
Alias sc
Set-Content
and if you run the sc command from Powershell, you will likely need to
run the full path,
C:\Windows\system32\sc config "MSSQL$SQLEXPRESS" depend= LanmanWorkstation
To verify the dependency has been created,
C:\>sc qc MSSQL$SQLEXPRESS
[SC] QueryServiceConfig SUCCESS
SERVICE_NAME: MSSQL$SQLEXPRESS
TYPE : 10 WIN32_OWN_PROCESS
START_TYPE : 2 AUTO_START
ERROR_CONTROL : 1 NORMAL
BINARY_PATH_NAME : "c:\Program Files\Microsoft SQL
Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -sSQLEXPRESS
LOAD_ORDER_GROUP :
TAG : 0
DISPLAY_NAME : SQL Server (SQLEXPRESS)
DEPENDENCIES : LanmanWorkstation
SERVICE_START_NAME : NT AUTHORITY\NETWORK SERVICE
You can also verify through the service control manager on the
dependencies tab for the SQL Server service for the particular instance
that you are configuring.
-- Mike Burr
Free Windows Admin Tool Kit Click here and download it now
August 27th, 2010 7:55am
Hi,
Thanks for post here.
That’s a good method to change the start dependencies of service by using SC utility
I am still interesting in what’s the root cause of this issue.
Please check the event log to verify if any related Event displayed and post the ID and description here for further investigation.
Thanks
Tiger LiPlease remember to click Mark as Answer on the post that helps you, and to click Unmark as Answer if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
August 27th, 2010 10:49am
Thank you for the advice. This is the direction I need but maybe the WORKSTATION service is not the right service to be dependent on. After I adding the dependency and restarting the machine, SQL Server could still not reach the database on the NAS until
I manually restarted MS SQL Server. Any ideas on a different service to try?
Tiger Li asked for the relevant events. Here they are. First this:
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17137
Date: 2010/08/27
Time: 10:41:33 AM
User: N/A
Computer: DATA3
Description:
Starting up database 'weblogs'.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: f1 42 00 00 0a 00 00 00 ñB......
0008: 06 00 00 00 44 00 41 00 ....D.A.
0010: 54 00 41 00 33 00 00 00 T.A.3...
0018: 00 00 00 00 ....
Then this:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17207
Date: 2010/08/27
Time: 10:41:37 AM
User: N/A
Computer: DATA3
Description:
FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file '\\seagate\mssql\weblogs.mdf'. Diagnose and correct the operating system error, and retry the operation.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 37 43 00 00 10 00 00 00 7C......
0008: 06 00 00 00 44 00 41 00 ....D.A.
0010: 54 00 41 00 33 00 00 00 T.A.3...
0018: 00 00 00 00 ....
Then this:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17204
Date: 2010/08/27
Time: 10:41:37 AM
User: N/A
Computer: DATA3
Description:
FCB::Open failed: Could not open file \\seagate\mssql\weblogs.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 34 43 00 00 10 00 00 00 4C......
0008: 06 00 00 00 44 00 41 00 ....D.A.
0010: 54 00 41 00 33 00 00 00 T.A.3...
0018: 00 00 00 00 ....
Free Windows Admin Tool Kit Click here and download it now
August 27th, 2010 8:23pm
Hi,
Thanks for the update.
Have you checked the article below that given some methods to troubleshooting this issue.
How to troubleshoot Error 17204 and 17207 in SQL Server
http://support.microsoft.com/kb/2015754/en-us
Hope that’s helpful
Tiger LiPlease remember to click Mark as Answer on the post that helps you, and to click Unmark as Answer if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
August 30th, 2010 6:12am
No, KB 2015754 does not really address this specific problem.
I have solved it by running this batch file during computer startup:
sleep 60
sc stop SQLSERVERAGENT
sleep 5
sc stop MSSQLSERVER
sleep 5
sc start MSSQLSERVER
sleep 5
sc start SQLSERVERAGENT
Free Windows Admin Tool Kit Click here and download it now
August 31st, 2010 2:04am