4

One of the databases I am going to support does not have a validate log file location. The drive which the log file is pointing to does not exist. But I don't see any error in database error log because of this.

Is there a default location for SQL server log file when the log file path is not set right?

Thanks

  • Try quering sys.master_files ... – M.Ali Sep 24 '14 at 15:55
1

Perhaps the log is on is a network share that is only visible to the SQL Server service account?

If this were the case you could log onto the server using that same service account used by SQL Server, and "see" the drive in Windows Explorer.

SQL Server will refuse to load a database if it cannot locate a valid log file. It will NOT simply create the log file in the "default" Log file location.

An alternate method to see if the log file really exists, and perhaps where it actually is, would be to use the xp_cmdshell extended stored procedure to run a dir command on the SQL Server itself, in the context of the service account. This relies on having exec sp_configure 'xp_cmdshell' being set to 1. If xp_cmdshell is enabled, you could run the following:

EXEC xp_cmdshell 'DIR C:\temp\*.ldf';  --replace with the path to the log file in question
EXEC xp_cmdshell 'net use'; -- this shows the mapped network drives SQL Server can "see"
  • you are probably right. I can not verify it right now since I don't have the password of the service account. – Don Sep 24 '14 at 15:11
3

If you go to server properties, in the database settings, you will find the default database data, log and backup locations :

enter image description here

  • Thanks. Where can I see your screen print? We are using Windows 2008 R2. I can only see the SQL Server properties page like this (technet.microsoft.com/en-us/library/…). In computer management tool, I can not see this either. – Don Sep 24 '14 at 14:55
  • That is a screenshot from SQL Server Management Studio – Max Vernon Sep 24 '14 at 15:04
  • @Kin Thanks again. I can see it now but it is the same I saw in directly querying the database file. – Don Sep 24 '14 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.