Could not maintain connection, files on wrong drive

ngarris

Hi, I wanted to post this resolution to possibly help someone else with the same problem. We built a new SQL Server with database files defaulting to E drive and log files defaulting to F drive. However when we built it, we mistakenly added the drives as D and E rather than E and F.

Symptom: The new SQL Server was running fine. SQL Monitor would connect (Windows connection) and that connection was solid. It would make the SQL connection, then immediately it would drop. A little later, it would retry, make the connection (we would get a few alerts), them immediately drop. We ran all the SQLMonitor connection tests and all were successful.

Resolution: Change the default database data and log locations to the correct locations. (These can be changed in SSMS in Server Properties, on the Database Settings tab.)

The clue was the error message on the SQL Monitor ConfigurationMonitored Servers page. In the Actions column, the Show Log command shows the error message from the attempted connection. The error message referred to the missing F drive.

Alex B

Hi NIcole,

Thank you for sharing your situation and resolution! I believe we get the log locations from the registry entries for those startup values, which as you showed were out of date. We usually use this query to see where we think the logs should be:

USE tempdb 
SET nocount ON 
DECLARE @logPath VARCHAR(500) 
EXEC master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWAREMicrosoftMSSQLServerMSSQLServer', 'DefaultLog', @logPath OUTPUT IF (@logPath IS NULL) 
BEGIN 
EXEC master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWAREMicrosoftMSSQLServerSetup', 'SQLDataRoot', @logPath OUTPUT SELECT @logPath = @logPath + N'LOG' 
END 
IF((SELECT 1 WHERE @logPath LIKE '%:') = 1) 

BEGIN 
SELECT @logPath = @logPath + N'' 
END 

SELECT @logPath AS logPath

I still find it odd that SQL Server happily works when these are out of date, but oh well :-)

Kind regards,
Alex