August 18, 2008 at 11:17 am
As it stand, I have not 'completed' the move of the DB due to not being able to run the previous script. There are no errors in the event viewer. All other system DB's are where they should be, ok the K\Data and L\Log drives, according to a query on the system.
What are some scripts or tests I can run to sort of test out the server, and make sure everything is healthy?
August 18, 2008 at 11:27 am
Check the error log on restart. If you see something, post it and we'll ask.
August 18, 2008 at 1:39 pm
Thanks everyone for your replies. Here's the errors I see:
2008-08-17 20:12:02.59 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. 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.
2008-08-17 20:12:02.59 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2008-08-17 20:12:03.07 spid5s Starting up database 'msdb'.
2008-08-17 20:12:03.14 spid5s 3 transactions rolled forward in database 'msdb' (4). This is an informational message only. No user action is required.
2008-08-17 20:12:03.18 spid5s 0 transactions rolled back in database 'msdb' (4). This is an informational message only. No user action is required.
2008-08-17 20:12:03.18 spid5s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
2008-08-17 20:12:03.32 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2008-08-17 20:12:03.32 spid5s Recovery is complete. This is an informational message only. No user action is required.
2008-08-17 20:12:04.45 spid52 Using 'xpsqlbot.dll' version '2005.90.3042' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2008-08-17 20:12:04.71 spid52 Using 'xpstar90.dll' version '2005.90.3042' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2008-08-17 20:12:05.24 spid52 Using 'xplog70.dll' version '2005.90.3042' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2008-08-17 20:12:24.34 spid53 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2008-08-17 20:12:24.34 spid53 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2008-08-17 20:12:24.34 spid53 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2008-08-17 20:12:26.31 spid52 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2008-08-17 20:12:26.31 spid52 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2008-08-17 20:12:26.31 spid52 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2008-08-17 20:22:05.78 spid56 Error: 5105, Severity: 16, State: 2.
2008-08-17 20:22:05.78 spid56 A file activation error occurred. The physical file name 'NEW PATH\mssqlsystemresource.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
2008-08-17 20:22:05.83 spid56 Error: 5105, Severity: 16, State: 2.
2008-08-17 20:22:05.83 spid56 A file activation error occurred. The physical file name 'NEW PATH\mssqlsystemresource.ldf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
2008-08-18 00:00:34.43 spid15s This instance of SQL Server has been using a process ID of 1128 since 8/17/2008 8:12:03 PM (local) 8/18/2008 2:12:03 AM (UTC). This is an informational message only; no user action is required.
August 18, 2008 at 4:49 pm
If you can get SQL Server running in single user mode, try running these commands:
[font="Courier New"] ALTER DATABASE mssqlsystemresource
MODIFY FILE (name = data, filename = '[path]\mssqlsystemresource.mdf')
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (name = data, filename = '[path]\mssqlsystemresource.ldf')
GO
[/font]
Then restart.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 18, 2008 at 7:48 pm
I can get the server running in single user but sqlcmd wont connect. It is allowed, TCP named pipes, in surface config utility. I am at a loss.
This a SQL 2005 std install on a 2003 Sp2 cluster.
August 18, 2008 at 7:59 pm
Are you connecting to the SQL Server virtual name (being as it's a cluster)?
What credentials are you logging in to Windows with?
What credentials are you connecting to SQL with?
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 18, 2008 at 8:18 pm
I am connecting to the virtual servername. The cluster is running, although I stop all sql services via administrative tools in control panel and I see the cluster admin doesn't know quite what to do.
I am RDPd to the box, connecting with domain admin account, logged into sql with the same account.
Did I detach the systemresource db?
I tried to reattach the systemresource db but it said it was already there. I really wish I never started this escapade, but I read a few posts online saying it was a good idea.
August 18, 2008 at 8:28 pm
Good idea or not, if you're not sure what you are doing you should test first rather than jump straight onto production. The real trick there is to realise when you don't know what you're doing (how many times has that happened? You're sure you know what you're doing only to have it turn to custard and realise you really don't. Been there, done that).
Are you able to reverse all the changes you have made and get the server back to how it was configured in the first place? That may be the preferred option, then let the DBA sort out moving the files when they get back.
Another possibility is to restore the system databases using the WITH MOVE option to put the files in their new places. Preferably after you have got things back to original configuration.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 18, 2008 at 8:40 pm
Lesson Learned. Here's the latest startup log. Since it doesn't mention any errors or reference the resourcesystem db, can I be fairly sure I'm ok?
2008-08-18 20:34:02.72 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. 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.
2008-08-18 20:34:02.72 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2008-08-18 20:34:02.73 spid4s Starting up database 'msdb'.
2008-08-18 20:34:02.75 spid4s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2008-08-18 20:34:02.75 spid4s Recovery is complete. This is an informational message only. No user action is required.
2008-08-18 20:34:02.97 spid52 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2008-08-18 20:34:02.97 spid52 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2008-08-18 20:34:02.97 spid52 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2008-08-18 20:34:03.11 spid52 Using 'xpsqlbot.dll' version '2005.90.3042' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2008-08-18 20:34:03.17 spid52 Using 'xpstar90.dll' version '2005.90.3042' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2008-08-18 20:34:03.19 spid52 Using 'xplog70.dll' version '2005.90.3042' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
btw I did test on my VM install but it's not a cluster.
August 18, 2008 at 9:33 pm
Looks more or less OK, albeit incomplete. I've attached a sample ERRORLOG to show what to expect (from one of our test 2005 clusters).
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 18, 2008 at 9:39 pm
Alright ,Thanks much everyone. I'm going to hope for the best. What's worst case? If it can't read the systemresource db I'll get some random errors when we install sharepoint?
EDIT- I chopped off the top half of the error log, leaving out the server IP, etc. Mine looks like yours more or less.
August 18, 2008 at 9:51 pm
Try using some of the dynamic management views and functions - they reside in the resource database. E.g., SELECT * FROM sys.dm_exec_sessions. Have a nosy in Books Online for more code samples.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply