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.


  • Options
    Alex BAlex B Posts: 1,142 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) 
    EXEC master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWAREMicrosoftMSSQLServerSetup', 'SQLDataRoot', @logPath OUTPUT SELECT @logPath = @logPath + N'LOG' 
    IF((SELECT 1 WHERE @logPath LIKE '%:') = 1) 
    SELECT @logPath = @logPath + N'' 
    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,
    Product Support Engineer | Redgate Software

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