How to find SQL Server Instance Default Path and Instance Default Log Path

07 September,2017 by Jack Vamvas

Finding the SQL Server Instance Default Path and the Instance Default Log Path in the old days (pre SQL Server 2012) was generally limited to either checking SSMS for the Instance Default Path or Log Path , and you could also use xp_regread to extract the information from the Registry.

To read more about these options read SQL Server - Find Default Data location and Default Log Location ....    

For the last few versions of SQL Server similar information can be extracted using the SERVERPROPERTY functionality. This has introduced the benefit of being able to programmatically extract the details and include the code as part of other DBA scripts used to manage SQL Server.

An example of using the SERVERPROPERTY('InstanceDefaultPath') and SERVERPROPERTY('InstanceDefaultLogPath') :

 

select

 Default_Data_Path = serverproperty('InstanceDefaultDataPath'),

 Default_Log_Path = serverproperty('InstanceDefaultLogPath')

 

Typically this information is used to discover the target path of the data and log file when the CREATE DATABASE is executed. If no paths are detailed in the CREATE DATABASE statement - SQL Server will place the files on the Default Data and Log files.

It could also be used to discover where the data files are maintained - if troubleshooting  a full disk or a performance issue. Although you might be better off using sys.master_files and list all database files  

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to find SQL Server Instance Default Path and Instance Default Log Path


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer