Archive

Archive for the ‘Undocumented Functions’ Category

SQL Server – Get values as DATETIME from sysjobhistory

July 22, 2013 Leave a comment

This is a follow-up post on SQL Server – Check SQL Agent Job History using T-SQL.

SQL Server Agent stores SQL jobs history in sysjobhistory. It has two different columns for date and time, Run_Date and Run_Time. Since this is not available as DATETIME we cannot filter based on certain criteria such as jobs that run in last 24 hours.

There is a system function available in msdb database which takes these two columns as input converts output to a DATETIME data type, dbo.agent_datetime. You can use this function as below:

SELECT        TOP 5

[JobName]     JOB.name,

       [StepName]    HIST.step_name,

              [RunDateTime] dbo.agent_datetime(HIST.run_date,HIST.run_time)

FROM          sysjobs JOB

INNER JOIN    sysjobhistory HIST ON HIST.job_id JOB.job_id

ORDERBY      HIST.run_dateHIST.run_time

Result Set:

JobName                  StepName                            RunDateTime

syspolicy_purge_history  Verify that automation is enabled.  2013-01-29 02:00:00.000

syspolicy_purge_history  Purge history.                      2013-01-29 02:00:00.000

syspolicy_purge_history  Erase Phantom System Health Records 2013-01-29 02:00:00.000

syspolicy_purge_history  (Job outcome)                       2013-01-29 02:00:00.000

syspolicy_purge_history  Verify that automation is enabled.  2013-01-30 02:00:00.000

 

(5 row(s) affected)

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 4 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 – Executing Multiple Script Files Using SQLCMD

March 25, 2013 2 comments

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

March 18, 2013 1 comment

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 – Finding out Database creation time

February 19, 2013 1 comment

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:

image

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

January 24, 2013 2 comments

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:

image

 

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

Follow

Get every new post delivered to your Inbox.

Join 263 other followers