Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server - Find Default Data location and Default Log Location

25 January,2013 by Jack Vamvas

Finding the  SQL Server default data and log location  (or Database default Locations ) is useful. When a CREATE DATABASE command is issued with target location paths explicitly stated , SQL Server will  create the data and log files in the default data and log locations.

That’s not always the intended result.   For example, the DBA may need to change the location of system files or move all databases due to disk issues or to improve database performance - read more on  SQL Server – files and filegroups. Improving database performance

Method 1 : Use SSMS to view Default Data and Log details

  1. In the SSMS Object Explorer panel, right-click a server and click
  2. Properties.
  3. In the left panel,under Select A Page title,  click the Database settings page.
  4. In Database default locations, view the current default locations for new data files and new log files.

 Method 2: T-SQL and xp_instance_regread

 

DECLARE @DataLoc nvarchar(2000);
DECLARE @LogLoc nvarchar(2000);

EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultData', 
    @DataLoc OUTPUT;

EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultLog', 
    @LogLoc OUTPUT;

SELECT @DataLoc AS 'Default Data Location',
       @LogLoc AS 'Default Log Location';

 Read More

sql server scripts and managing databases - SQL Server DBA

Operations DBA description - SQL Server DBA

SQL Server 2005 and SQL Server 2008 Upgrade tasks

SQL Server – Tempdb move files to another drive - SQL Server DBA


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

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

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


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