October 15, 2009 at 2:49 am
SQL Server is setup to autostart and has done so without problem since installation in June. Over the past week there have been several times sql server has not automatically started when the pc powers up. Now it is not starting each morning. I cleared a few million rows from a table recently and shrunk the files which seems to have triggered the problem. If I start the server manually I see no problems but this is far from ideal. I have searched the forums and other similar problems appear to be related to permissions which does not fit here as it does auto start on some daya and not others.
ANY help would be greatly appreciated.
Log below from this mornings failed startup.
2009-10-15 06:21:57.68 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
2009-10-15 06:21:57.73 Server (c) 2005 Microsoft Corporation.
2009-10-15 06:21:57.73 Server All rights reserved.
2009-10-15 06:21:57.73 Server Server process ID is 2156.
2009-10-15 06:21:57.73 Server Logging SQL Server messages in file 'R:\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2009-10-15 06:21:57.73 Server This instance of SQL Server last reported using a process ID of 1940 at 10/15/2009 03:00:40 (local) 10/15/2009 02:00:40 (UTC). This is an informational message only; no user action is required.
2009-10-15 06:21:57.73 Server Registry startup parameters:
2009-10-15 06:21:57.84 Server -d R:\MSSQL.1\MSSQL\DATA\master.mdf
2009-10-15 06:21:57.84 Server -e R:\MSSQL.1\MSSQL\LOG\ERRORLOG
2009-10-15 06:21:57.84 Server -l R:\MSSQL.1\MSSQL\DATA\mastlog.ldf
2009-10-15 06:21:58.07 Server SQL Server is starting at high priority base (=13). This is an informational message only. No user action is required.
2009-10-15 06:21:58.07 Server Detected 2 CPUs. This is an informational message; no user action is required.
2009-10-15 06:22:03.14 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-10-15 06:22:06.17 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-10-15 06:22:08.93 Server The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
2009-10-15 06:22:09.42 Server Database Mirroring Transport is disabled in the endpoint configuration.
2009-10-15 06:22:09.76 spid5s Starting up database 'master'.
2009-10-15 06:22:10.14 spid5s Error: 17207, Severity: 16, State: 1.
2009-10-15 06:22:10.14 spid5s FCB::Open: Operating system error 32(error not found) occurred while creating or opening file 'R:\MSSQL.1\MSSQL\DATA\master.mdf'. Diagnose and correct the operating system error, and retry the operation.
2009-10-15 06:22:10.17 spid5s Error: 17204, Severity: 16, State: 1.
2009-10-15 06:22:10.17 spid5s FCB::Open failed: Could not open file R:\MSSQL.1\MSSQL\DATA\master.mdf for file number 1. OS error: 32(error not found).
2009-10-15 06:22:10.20 spid5s Error: 5120, Severity: 16, State: 101.
2009-10-15 06:22:10.20 spid5s Unable to open the physical file "R:\MSSQL.1\MSSQL\DATA\master.mdf". Operating system error 32: "32(error not found)".
2009-10-15 06:22:10.85 spid5s Error: 5173, Severity: 16, State: 1.
2009-10-15 06:22:10.85 spid5s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
2009-10-15 06:22:10.85 spid5s Error: 5173, Severity: 16, State: 1.
2009-10-15 06:22:10.85 spid5s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
October 15, 2009 at 3:28 am
You seem to have a few issues listed in the log.
2009-10-15 06:22:10.14 spid5s FCB::Open: Operating system error 32(error not found) occurred while creating or opening file 'R:\MSSQL.1\MSSQL\DATA\master.mdf'. Diagnose and correct the operating system error, and retry the operation.
Do you want to check the windows log and see if you have had any drive or netowrk issues recently.
I take it that the server is up and running at the moment, these errors indicate a problem with the system databases and where they are located.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 15, 2009 at 3:43 am
Thanks for the reply, I have checked the event viewer and cannot find any errors around the same time that would indicate a problem but chekcing the error lead me to reading this help entry;
Check the SQL Server error log and the Event Viewer logs for any other errors that occur at the same time as this error.
Verify that the file exists in the exact path listed in the error. If it does not, move the file to that path.
Verify that the file does not have the read only attribute set to true.
Verify that the SQL Server startup account has read and write permissions to the folder and file.
Verify that the file is not held open by another process such as backup software or virus scanner software. If you can rename the file then it is not locked by another application.
If this error resulted after you moved databases to a new location, put all files back in their original location. For more information about how to move user or system databases to a new location, see the Microsoft Knowledge Base article 314546.
Run hardware diagnostics for the drive where the file resides to verify the disk and file are healthy.
If the file listed is encrypted, verify that the version and edition of Windows and of SQL Server support encrypted files
If you get this error from rebuildm.exe verify that you choose the location of files from the original SQL Server installation CD or of a copy of those original files. If you still have problems with rebuildm.exe, follow the steps in the Microsoft Knowledge Base article 273572.
I will exclude the directory from my antivirus software to see if that makes any difference.
I will thren try and do a checkdsk.
October 16, 2009 at 1:04 am
Success!!
For today at least sql server was running smoothly and all my jobs have run as I logged on this am. I will report again in one week to ensure the action I took has actually fixed the problem. Here is what I did :-
I changed :-
Server Integration Services
Full Text Search
Analysis Services
All to startup = manual (they were all automatic).
Also I added my SQL directory to the exclusion list of my antivirus software.
Reason for changing the SQL jobs was simply that I don not use any of these components and I thought less work at startup had to benefit the whole process.
Excluding from antivirus ensures none of the objects were being locked at startup.
November 4, 2009 at 11:31 pm
Just a quick update that might prove useful to anyone facing this problem in future and other suggested cures haven't worked.
The actions I took above have worked, autostart has not had a problem since making the above changes.
November 5, 2009 at 12:57 am
yes antivirus s/w will definitely lock down resources and will prevent it from working smoothly. You need to get with your vendor and find out solution as you can't live without antivirus s/w
November 5, 2009 at 2:34 am
could please tell us which anti virus software your production server is having.
Regards
Ramu
November 5, 2009 at 1:36 pm
I am using Avast (free edition).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply