Could not maintain connection, files on wrong drive

ngarrisngarris Posts: 19
edited February 1, 2016 5:12AM in SQL Monitor Previous Versions
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.

Comments

  • Alex BAlex B Posts: 1,153 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.