Sql-server – SQL Server 2008 R2 Log File Location

filegroupslogssql servertransaction-log

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

Best Answer

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"
Related Question