Archive

Archive for the ‘SQL Configuration’ Category

SQL Server – Show/Hide Results Pane in Management Studio 2012

May 20, 2013 6 comments

In earlier version of SQL Server Management Studio (2005, 2008 and 2008 R2) you can show/hide results pane using keyboard shortcut Ctrl+R. This shortcut was also present in Query Analyzer (SQL Server 2000).

This shortcut is no longer available in SQL Server Management Studio 2012. In this version you need to use Ctrl+Shift+Alt+R to show/hide query results pane. Another alternative shortcuts you can use to show or hide results pane is Alt+WS and Alt+WI respectively. I find the later to be more easy to use.

If you have gotten used to using old shortcut Ctrl+R you can customize shortcuts in SQL Server Management Studio.

To change keyboard shortcut for show/hide results pane:

Step 1: Go to Tools > Options in SQL Server Management Studio:

image

Step 2: Navigate to Environment > Keyboard > Keyboard

image

Step 3: Search for Window.ShowResultsPane using Show commands containing: textbox.

image

Step 4:

(1) Select Ctrl+Shift+Alt+R (SQL Query Editor) in Shortcuts for selected command: list.
(2) Select SQL Query Editor in Use new shortcut in: list.
(3) Go to Press shortcut keys: textbox and press Ctrl+R or any other shortcut you need.
(4) Click on Assign.
(5) Click on OK to apply changes.

image

Step 5: You need to restart SQL Server Management Studio to apply these changes.

 

Note: The Window menu will still show Ctrl+Shift+Alt+R as shortcut we have only added an additional shortcut, we have not removed existing shortcut.

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 – How to find Default data and log path for SQL Server 2012

May 6, 2013 1 comment

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

May 1, 2013 6 comments

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

image

Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAll group.

image

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…

image

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.

image

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.

image

 


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 Smile

 


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 Smile Smile

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 – How to check if you are running 32-bit or 64-bit version

April 22, 2013 Leave a comment

There are two different command you can use to check if you are running 32-bit or 64-bit version of SQL Server.

Using @@VERSION:

You can user @@VERSION system variable to determine edition and architecture of SQL Server as below:

SELECT @@VERSION

Result Set:

Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (Intel X86)

       Oct 19 2012 13:43:21

       Copyright (c) Microsoft Corporation

       Developer Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

 

 

(1 row(s) affected)

This command returns both SQL Server and Operating system information. In the first line you get information about SQL Server, it's version and architecture. Here, x86 = 32-bit architecture and x64 = 64-bit architecture. Same it true for operating system.

 

Using SERVERPROPERTY():

We can also user SERVERPROPERTY() function to get information about SQL Server Edition and architecture.

SELECT SERVERPROPERTY('Edition')

Result Set:

Developer Edition

 

(1 row(s) affected)

From the output we can see that it's a Developer Edition instance and architecture is 32-bit. If it is 64-bit then '(64-bit)' will be appended to edition. i.e. For 64-bit the output will be 'Developer Edition (64-bit)'

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

March 18, 2013 2 comments

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".

image

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.

image

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

March 11, 2013 1 comment

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:

image

 

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 – Cycle Error Logs for SQL Server and SQL Server Agent

February 8, 2013 1 comment

SQL Server error logs can fill up quickly, and when you are troubleshooting something you may need to go through huge log. However you can cycle the error log to manage the amount of log you need to go through. When you cycle error log the current log file is renamed from ERRORLOG to ERRORLOG.1, ERRORLOG.1 is renamed to ERRORLOG.2 and so on.

Restarting SQL Server will cycle the error logs. You can also use sp_cycle_errorlog to cycle error logs without restarting SQL Server.

Below is an example how to use it:

EXEC sp_Cycle_ErrorLog

GO

 

Result Set:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

You can verify that the log has cycled using Management Studio or you can use xp_ReadErrorLog also.

 

Same effect can be achieved by using DBCC ErrorLog. In fact, sp_Cycle_ErrorLog calls DBCC ErrorLog to cycle error logs.

DBCC ErrorLog

GO

 

Result Set:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

DBCC ErrorLog and sp_Cycle_ErrorLog only cycles SQL Server error logs. It does not cycle SQL Server Agent error logs. In order to achieve this you can restart SQL Server Agent or use another stored procedure sp_Cycle_Agent_ErrorLog.

USE   [msdb]

GO

 

EXEC  sp_Cycle_Agent_ErrorLog

GO

 

Result Set:

Command(s) completed successfully.

 

You can verify that the SQL Server Agent log has cycled using xp_ReadErrorLog.

* Use sp_helptext to see what is called from sp_Cycle_Agent_ErrorLog.

 

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