Archive
SQL Server – How to find Default data and log path for SQL Server 2012
To identify default data and log directories in SQL Server 2012 you can use SERVERPROPERTY() function. In SQL Server 2012 two new parameters are added to SERVERPROPERTY() function namely, InstanceDefaultDataPath and InstanceDefaultLogPath which returns the default data and log directories respectively.
It can be used as below:
SELECT [Default Data Path] = SERVERPROPERTY('InstanceDefaultDataPath')
SELECT [Default Log Path] = SERVERPROPERTY('InstanceDefaultLogPath')
GO
Result Set:
Default Data Path
C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\
(1 row(s) affected)
Default Log Path
C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\
(1 row(s) affected)
You can also get this information from Server Properties dialog box in SQL Server Management Studio.
To identify this information for earlier version of SQL Server you need to use xp_instance_regread extended stored procedure which returns data from registry.
Earlier I posted on identifying default data directory for multiple instances through registry which returns this information for all instances installed on server.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Finding TCP Port Number SQL Instance is Listening on
By default SQL Server listens on TCP port number 1433, and for named instances TCP port is dynamically configured. There are several options available to get the listening port for SQL Server Instance.
Here are a few methods which we can use to get this information.
Method 1: SQL Server Configuration Manager
Method 2: Windows Event Viewer
Method 3: SQL Server Error Logs
Method 4: sys.dm_exec_connections DMV
Method 5: Reading registry using xp_instance_regread
Let's see how you can use each of these methods in detail:
Method 1: SQL Server Configuration Manager:
Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager
Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <Instance Name>
Step 3. Right Click on TCP/IP and select Properties
Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAll group.
If SQL Server if configured to run on a static port it will be available in TCP Port textbox, and if it is configured on dynamic port then current port will be available in TCP Dynamic Ports textbox. Here my instance is listening on port number 61499.
Method 2: Windows Event Viewer:
When SQL Server is started it logs an event message as 'Server is listening on [ 'any' <ipv4> <port number>' in windows event logs. Here <port number> will be actual port number on which SQL Server is listening.
To view this using Event Viewer:
Step 1. Click Start > Administrative Tools > Event Viewer.
Note: If Administrative Tools are not available on Start menu, go to Start > Control Panel > System and Maintenance > Administrative Tools > View event logs
Step 2. Navigate to Event Viewer > Windows Logs > Application
Step 3. Since huge amount of event are logged, you need to use filtering to locate the required logs. Right click on Application and select Filter Current Log...
Step 4. You can filter the events by Event ID and Event source. The event we are interested in has Event ID of 26022, and it's source is SQL Server Instance. You need to filter by both Event ID and SQL Server Instance if you have multiple instances installed, for a single instance you can filter by Event ID only. Click on OK to apply the filter.
Step 5. Once the filter is applied, Locate message 'Server is listening on [ 'any' <ipv4> …'. As we can see from below screenshot that SQL Server Instance is running on TCP Port 61499.
Method 3: SQL Server Error Logs:
When SQL Server is started it also logs an message to SQL Server Error Logs. You can search for port number in SQL Server Error Logs by opening SQL Server Error Log in notepad or via T-SQL using extended stored procedure xp_ReadErrorLog as below:
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO
Result Set:
LogDate ProcessInfo Text
2013-03-21 13:34:40.610 spid18s Server is listening on [ 'any' <ipv4> 61499].
2013-03-21 13:34:40.610 spid18s Server is listening on [ 'any' <ipv6> 61499].
(2 row(s) affected)
As we can see from the output that SQL Server Instance is listening on 61499.
Note: This method does not work if SQL Server Error Logs have been cycled. See sp_Cycle_ErrorLog for more information.
Method 4: sys.dm_exec_connections DMV:
DMVs return server state that can be used to monitor SQL Server Instance. We can use sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:
SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
GO
Result Set:
local_tcp_port
61499
(1 row(s) affected)
As we can see from the output… same as above 
Method 5: Reading registry using xp_instance_regread:
Port number can also be retrieved from Windows Registry database.
We can use extended stored procedure xp_instance_regread to get port number information using below T-SQL code:
DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value = @portNumber OUTPUT
SELECT [Port Number] = @portNumber
GO
Result Set:
Port Number
61499
(1 row(s) affected)
As we can see … same as above
Note: The above code will only work if SQL Server is configured to use dynamic port number. If SQL Server is configured on a static port, we need to use @value_name = 'TcpPort' as opposed to @value_name = 'TcpDynamicPorts'.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Executing Multiple Script Files Using SQLCMD
There are different options available when you want to execute a .sql script file on a server. You can open the script file in SQL Server Management Studio and execute it, or you can use SQLCMD to execute a script from command line. However, when you have large number of scripts to execute these methods may not be feasible.
You can use SQLCMD and batch programming together to execute a large number of script files easily.
Below is the code for batch file:
@Echo Off
FOR /f %%i IN ('DIR *.Sql /B') do call :RunScript %%i
GOTO :END
:RunScript
Echo Executing %1
SQLCMD -S Server\Instance -U Vishal -P Password -i %1
Echo Completed %1
:END
Copy this to notepad and save it as a .cmd or .bat file, for example save it as "RunMyScripts.cmd" in the same folder where your scripts are stored.
Make sure you replace server details and authentication details.
Then go to command prompt and navigate to this folder and execute “RunMyScripts.cmd”
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Changing Default Database Location for Server
When you create a new database in SQL Server without explicitly specifying database file locations, SQL Server created files in default location. This default location is configured when installing SQL Server.
If you need to change this default location once SQL Server is installed, you can change this in server properties.
Method 1: Change default database location via SQL Server Management Studio:
Step 1. Right Click on Server and Select "Properties".
Step 2. in the "Server Properties" dialog box, navigate to "Database Settings" tab and data/log files location under "Database default locations" group. You can also change default backup location here.
Step 3. Click on "OK" to apply changes.
All new databases will be created to new location unless specified explicitly.
Method 2: Change default database location using TSQL Code:
You can also change default database location using TSQL code, use below code to change data, log and backup location:
USE [master]
GO
– Change default location for data files
EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',
REG_SZ,
N'C:\MSSQL\Data'
GO
– Change default location for log files
EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',
REG_SZ,
N'C:\MSSQL\Logs'
GO
– Change default location for backups
EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
REG_SZ,
N'C:\MSSQL\Backups'
GO
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Start/Stop SQL Server from command line
You can start/stop SQL Server services using Services Console or SQL Server Configuration Manager. You can also perform these operation using command line in Windows. For this you must run command prompt with Administrative privileges.
Method 1: Start/Stop SQL Server using NET command:
You can start/stop SQL Server from command line using NET command as below:
NET Action Service Name or Service Display Name
Where Action can be Start or Stop, and you can either specify Service Name or Service Display Name. Service Name is actual name which is registered as service and Service Display Name is a Descriptive Name for Service. For example, for default instance of SQL Server Service Name is "MSSQLSERVER", and Service Display Name is "SQL Server (MSSQLSERVER)". Below is a screenshot of Service Properties to explain this:
To START SQL Server Service, use:
NET start MSSQLSERVER
OR
NET start "SQL Server (MSSQLSERVER)"
To STOP SQL Server Service, use:
NET stop MSSQLSERVER
OR
NET stop "SQL Server (MSSQLSERVER)"
Method 2: Start/Stop SQL Server using SC command line utility:
The main advantage of using SC utility to start services is you can specify startup parameters while starting a service:
You can start/stop SQL Server from command line using SC utility as below:
SC Action Service Name or Service Display Name [Optional Parameters]
To START SQL Server Service, use:
SC start MSSQLSERVER
OR
SC start "SQL Server (MSSQLSERVER)"
To START SQL Server Service with parameters, use:
SC start MSSQLSERVER -m
OR
SC start "SQL Server (MSSQLSERVER)" -m
* The -m switch starts SQL Server in single user mode.
To STOP SQL Server Service, use:
SC stop MSSQLSERVER
OR
SC stop "SQL Server (MSSQLSERVER)"
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Finding out Database creation time
Finding database creation time is simple if database has not been detached from server. You can find database creation time using below methods.
Using sp_helpdb:
You can find the creation time of database using sp_hepldb system procedure.
EXEC sp_helpdb 'SqlAndMe'
GO
This will return database creation time along with other details:
Using catalog view sys.databases:
Database creation time is also available in sys.databases catalog view:
SELECT create_date
FROM sys.databases
WHERE name = 'SqlAndMe'
GO
Result Set:
create_date
2012-11-14 15:19:31.987
(1 row(s) affected)
However, if the database has been detached and reattached to server, the creation time will be changed to attach time. Also, if the database has been restored from a backup after dropping the database creation time will be changed.
Actual creation time of database is stored in the boot page of the database which is retained after restore or detach/attach. This is stored as dbi_crdate.
You can use DBCC PAGE to read creation time of database from boot page:
DBCC TRACEON(3604)
GO
DBCC PAGE('SqlAndMe', 1, 9, 3)
GO
Result Set (Trimmed):
dbi_createVersion = 661 dbi_ESVersion = 0
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2011-11-07 21:12:46.357
dbi_filegeneration = 2
dbi_checkptLSN
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Check Enabled Trace Flags for Server
In SQL Server you can enable a Trace Flag at session (effective for current session only) level and global level. If you are not sure which Trace Flags have been enabled you can use DBCC TRACESTATUS() command to get a list of enabled Trace Flags.
DBCC TRACESTATUS() takes two arguments <Trace Flag Number> and <Trace Scope>.
It can be used in following different ways.
To display Trace Flags which are enabled in current session + globally enabled Trace Flags
DBCC TRACESTATUS(-1)
GO
DBCC TRACESTATUS()
GO
To display status of individual Trace Flags which are enabled.
DBCC TRACESTATUS(3023, 3604)
GO
The output of DBCC TRACESTATUS() is as below:
This status of Trace Flags can also be obtained from SQL Server error logs. Although it required more effort you can use xp_ReadErrorLog to get this information from error logs using below T-SQL:
EXEC xp_ReadErrorLog 0, 1, 'DBCC TRACE'
GO
You can find more information on xp_ReadErrorLog here.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Enabling backup CHECKSUM with Trace Flag 3023
You can use WITH CHECKSUM option to perform checksum when backup is created. When used this verifies each page for checksum and torn page.
You can use it in a BACKUP command as below:
BACKUP DATABASE [SqlAndMe]
TO DISK = N'C:\SqlAndMe.bak'
WITH CHECKSUM
GO
Once the backup is created you can check the header to verify if backup was created with checksum.
RESTORE HEADERONLY
FROM DISK = N'C:\SqlAndMe.bak'
GO
Result Set:
However, if you do not have control over underlying T-SQL for backup then you can enable backup checksums using Trace Flag 3023. When this Trace Flag is turned on WITH CHECKSUM option is applied automatically for a backup.
In the following example I have run a backup before enabling Trace Flag 3023, and after enabling Trace Flag 3023 to see it’s behavior:
– Run without Trace Flag 3023
BACKUP DATABASE [SqlAndMe]
TO DISK = N'C:\SqlAndMe.bak'
GO
DBCC TRACEON(3023)
GO
– Run after enabling Trace Flag 3023
BACKUP DATABASE [SqlAndMe]
TO DISK = N'C:\SqlAndMe.bak'
GO
DBCC TRACEOFF(3023)
GO
Result Set:
Processed 240 pages for database 'SqlAndMe', file 'SqlAndMe' on file 1.
Processed 1 pages for database 'SqlAndMe', file 'SqlAndMe_Log' on file 1.
BACKUP DATABASE successfully processed 241 pages in 0.213 seconds (8.839 MB/sec).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Processed 240 pages for database 'SqlAndMe', file 'SqlAndMe' on file 2.
Processed 1 pages for database 'SqlAndMe', file 'SqlAndMe_Log' on file 2.
BACKUP DATABASE successfully processed 241 pages in 0.376 seconds (5.007 MB/sec).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now, we can look at the header of the backup file to verify if the backup has been created with checksum:
RESTORE HEADERONLY
FROM DISK = N'C:\SqlAndMe.bak'
GO
Result Set:
As we can see from the output, First backup was created without checksums and second backup (after enabling Trace Flag 3023) is created with checksums without explicitly specifying WITH CHECKSUM option.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Identifying default data directory for multiple instances through registry
This is a follow up post from a comment on my blog.
Earlier I posted about using xp_instance_regread to get default data location for current instance. It translates a given registry path to instance specific registry path. This can return the value for a single instance only. However, if you need to locate default data directories for all available instances on a machine, you need to use xp_regread, which reads from an absolute registry path.
Registry path for default data location is same for different SQL Server versions:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer
So the first thing we need before we can start reading data location from registry is list of instance name; these also can be read from registry using xp_instance_regenumvalues.
Registry path for instance name is as follows:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
We need to retrieve these to a temporary structure so that we can use it further:
CREATE TABLE #tempInstanceNames
(
InstanceName NVARCHAR(100),
RegPath NVARCHAR(100),
DefaultDataPath NVARCHAR(MAX)
)
INSERT INTO #tempInstanceNames (InstanceName, RegPath)
EXEC master..xp_instance_regenumvalues
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'
SELECT InstanceName, RegPath, DefaultDataPath
FROM #tempInstanceNames
Result Set:
InstanceName RegPath DefaultDataPath
MSSQLSERVER MSSQL10_50.MSSQLSERVER NULL
DENALI3 MSSQL11.DENALI3 NULL
SQL08ENT MSSQL10.SQL08ENT NULL
SQL05EXP MSSQL.1 NULL
We have a list of instance names; now we need to iteratively read registry value for each path; and append it to DefaultDataPath. The paths we need to read from registry will be:
1. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer
2. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.DENALI3\MSSQLServer
3. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL08ENT\MSSQLServer
4. HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
These needs to be read using xp_regread. I have used dynamic SQL to iterate through the list:
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'DECLARE @returnValue NVARCHAR(100)'
SELECT @SQL = @SQL + CHAR(13) +
'EXEC master.dbo.xp_regread
@rootkey = N''HKEY_LOCAL_MACHINE'',
@key = N''SOFTWARE\Microsoft\Microsoft SQL Server\' + RegPath + '\MSSQLServer'',
@value_name = N''DefaultData'',
@value = @returnValue OUTPUT;
UPDATE #tempInstanceNames SET DefaultDataPath = @returnValue
WHERE RegPath = ''' + RegPath + '''' + CHAR(13) FROM #tempInstanceNames
EXEC (@SQL)
SELECT InstanceName, RegPath, DefaultDataPath
FROM #tempInstanceNames
Result Set:
InstanceName RegPath DefaultDataPath
MSSQLSERVER MSSQL10_50.MSSQLSERVER C:\Database\Data
DENALI3 MSSQL11.DENALI3 C:\Database\Denali\Data
SQL08ENT MSSQL10.SQL08ENT C:\Database\2008\Data
SQL05EXP MSSQL.1 C:\Database\2005\Data
I have updated the column in #tempInstanceNames so that it can be used further.
You can download the script from here.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
