Failed SP2 install causing SQL service to fail...
-
Hey guys,
Setting up some new machines and after installing 2005 Standard I moved all the system databases as per http://msdn2.microsoft.com/en-us/library/ms345408.aspx
I then installed SP2, but got an error during the install for the Database Service.
Event Type: Error
Event Source: MsiInstaller
Event Category: None
Event ID: 1023
Description:
Product: Microsoft SQL Server 2005 - Update 'Service Pack 2 for SQL Server Database Services 2005 ENU (KB921896)' could not be installed. Error code 1603. Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log.
The patch completed everything else after than and then rebooted, but now I can not get the SQL server to even start. It's spitting out the following.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 5173
Description:
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.
0000: 35 14 00 00 10 00 00 00 5.......
0008: 0f 00 00 00 4e 00 50 00 ....B.P.
0010: 42 00 53 00 51 00 4c 00 B.S.Q.L.
0018: 48 00 52 00 51 00 41 00 9.3.2.A.
0020: 55 00 41 00 30 00 31 00 Z.A.0.3.
0028: 00 00 07 00 00 00 6d 00 ......m.
0030: 61 00 73 00 74 00 65 00 a.s.t.e.
0038: 72 00 00 00 r...
I can completely uninstall/reinstall if I need to, as the boxes aren't production yet, I just want to know what I did wrong so I can avoid doing it again. Could moving the DB's prior to the patch be the problem? And if so, what'll happen when I go to install SP3 someday?
Thanks!
-
Have you looked in the C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log file, which system databases did you move, was one of them the mssqlsystemresource database?
-
If it turns out you have an issue with the mssqlsystemresource files then I may have come accroos this myself.
After much digging it appears there is a bug in service pack 2, on install it recreates the mssqlsystemresource.mdf and mssqlsystemresource.ldf files in the folder where the mssqlsystemresource.mdf data file is.
This is an issue if you move data and log files to different volumes, when you then try and start the services they will not come up, but the error message and error logs do not tell you why.
We started SQL in minimal mode using these start up switches -c;-m;-T3608 and figured out the issue, the lsn on the mssqlsystemresource.ldf log file on the logfile volume did not match that of the newly created mssqlsystemresource.ldf file in the data file volume.
We renamed the old file and pasted in the newly created file into the log file location, leave the newly created log file in the data file location and that way the patch will not recreate it.
We where then able to bring up the services, before patching them again with SP2, awe deleted the newly created mssqlsystemresource.ldf file in the data file volume after the SQP2 install.
-
Yes, I've moved them all, splitting the mdb and ldb onto different drives, including the mssqlsystemresource db.
Used the following commands to move it from the command prompt.
sqlcmd -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = data, FILENAME = 'Newfilepath\mssqlsystemresource.mdf')"
sqlcmd -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = log, FILENAME = 'Newfilepath\mssqlsystemresource.ldf')"
Both times I got a successful message, "The file "log" has been modified in the system catalog. The new path will be used next time the database is started"
-
Check if you have a mssqlsystemresource.ldf file in you .mdf folder, if so follow the steps I laid out to get the services up and then reaply the SP2 patch and finaly delete this newly created .ldf file.
-
Yep, that worked like a charm. Thanks!
On a somewhat related note, on another server I was setting up, I installed SP2 prior to moving any databases and everything seems to be working fine.
-
Thats how I have set these up in the past as well, but when you do the standard install the data and log files are always in the same folder, so when you do the patch the file validation finds the correct files in the same folder and does not recreate new ones so all is ok for the patch and the restart of SQL services. This was a pain in the back side to figure out but there you go.
-
so, please answer this question, just for completeness.... if, after moving the files to the desired locations, you then copy the physical files back to the original location (while the db or ss2k5 is down, of course), and then subsequently implement SP2, does SP2 leave the files alone? or does having the files in two locations confuse SS 2k5?
-
Right after you install SP2 you can delete the "old" version of the .ldf that is sitting in the same location as the .mdf. There must be a check in the SP logic that checks the location of the mssqlsystemresrouce DB and as long as it sees both files it just assumes everything is alright.
Of course, once SP3 comes out some day, who knows if this whole thing will have the same issue.
To avoid the whole thing, whenever possible, I'd install SP2 before moving any system database files.
-
I came across the same issue with SP2 and mssqlsystemresource. My view is the documented method of moving mssqlsystemresource is now broken, and it is not safe to move this DB.
Therefore our SQL 2005 build now leaves mdf and ldf files for master in their original locations. After completing the SQL 2005 install, we stop SQL and take a copy of the current master and mssqlsystemresource files to a backuplocation, so they can be restored quickly if needed.
Ultimately, I think Microsoft made a bad mistake when they decided mssqlsystemresource should be hidden. This stops us taking backups of it and restoring it. However, if it ever gets corrupted SQL will not start. IMHO, all databases, including master, should support transaction log backups, point-in-time restore, and have their files placed wherever convenient to the DBA.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
You perform a file backup of the resource database.
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
-
In my case...
The SP2 install failed due to hard drive space (seperate issue) but mysqlsystemresource database was dated to 2/10/2007 instead of 4/14/2006. The SQL Server service wouldn't start due to incompatible file versions. LUCKILY, I had a copy of the original files from when I installed the server. Why I had these, I copied them from the default install to the raid 10 drives and then changed the path in master.
I replaced the files after renaming the newer versions and the server came online as expected.
Thanks to this post!
-
I have a similar issue, I haven't moved the resource databse but I am still getting the same problem. sp2 installs for everything but the database services. Using the developer edition. Any ideas?
-
Hi Martin,
Have you deleted or moved the replication db (distmdl.mdf)? I've also experienced this problem when having deleted or moved this db.
-
I have to ask, why does Microsoft spend so much time emphasizing best practices like moving data and log files to different disks, only to stab us in the back when we install updates? Is it really that difficult to write an installation program that can handle having the files for system databases in other folders? Things like this discourage people from following these practices.
“If you're not outraged at the media, you haven't been paying attention.”
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply