Archive

Archive for the ‘Undocumented Functions’ Category

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

SQL Server – Enabling backup CHECKSUM with Trace Flag 3023

January 15, 2013 2 comments

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:

image

 

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:

image

 

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

January 5, 2012 10 comments

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

SQL Server – Hide an Instance of SQL Server

August 23, 2011 1 comment

You can browse available SQL Instances on network by choosing "<Browse for More..>" from Server Name drop-down list in "Connect to Server" dialog box in Management Studio:

image

As you can see from the screen shot, all of my instances are visible on the network. If I want to hide this information from people on my network, it can be done easily. SQL Server allows us to hide an instance from being listed on the network. This can be done using SQL Server Configuration Manager.

For example, If I need to hide my Developer Edition instance SQL01:

1. Launch SQL Server Configuration Manager

2. Under "SQL Server Network Configuration" > Right-Click "Protocols for SQL01" > Select Properties

3. Set "Hide Instance" value to "Yes" from the drop-down list:

image

4. The Instance needs to be restarted in order for changes to take effect:

image

Once the instance is restarted it will not longer appear in the "Network Servers" list:

image

 

Hiding an Instance using T-SQL:

The information to hide/show instance is stored in registry. You can use extended stored procedure xp_instance_regwrite to update the registry value to hide/show instance. Below T-SQL will hide the instance. To unhide instance set @value to 0 :

EXEC master..xp_instance_regwrite

      @rootkey = N'HKEY_LOCAL_MACHINE',

      @key =
N'SOFTWARE\Microsoft\Microsoft SQL Server
\MSSQLServer\SuperSocketNetLib',

      @value_name = N'HideInstance',

      @type = N'REG_DWORD',

      @value = 1

      — 0 = No, 1 = Yes

To check if an instance is hidden you can use xp_instance_regread to check registry values:

DECLARE @getValue INT

EXEC master..xp_instance_regread

      @rootkey = N'HKEY_LOCAL_MACHINE',

      @key=

N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',

      @value_name = N'HideInstance',

      @value = @getValue OUTPUT

SELECT @getValue

 

This method only prevents the instance from being listed on the network, It does not prevent users from connecting to server if they know the instance name.

 

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 – Monitoring/Controlling Windows Services using xp_servicecontrol

August 15, 2011 1 comment

xp_servicecontrol is an undocumented extended stored procedure, which can be used to start/stop or check the state of Windows services. It has the following syntax:

xp_servicecontrol @Action = N'Action' @ServiceName = N'Service Name'

Arguments:

@Action can be any of the following:

start Starts the service if it is not running. If the service is already in running state, an error is raised.
stop Stops the service if it is running. If the service is not running, an error is raised.
pause Pauses a running service. An error is raised if the service is not running. Also, not all services support pausing.
continue Continues running a paused service. An error is raised if the serviced is not in paused state.
querystate Returns the current state of the service.

@ServiceName is the name of the service, Service Name is different than the Display name of service. You can check the service name from Services Management Console.

1. Launch Services Management Console –> Start –> Run –> "Services.msc",

2. Open Service Properties – Right-click the required service –> Select “Properties” from the context menu. You can find both display name and actual service name here:

image

 

Using xp_servicecontrol to check service state:

xp_servicecontrol can check state for any windows service using:

— Check Current State of any Service

 

— SQL Server (MSSQLServer)

EXEC xp_servicecontrol N'querystate',N'MSSQLServer'

— SQL Server Browser

EXEC xp_servicecontrol N'querystate',N'SQLBrowser'

— Tablet PC Input Service

EXEC xp_servicecontrol N'querystate',N'TabletInputService'

Result Set:

Current Service State

————————————————————————————
Running.

 

(1 row(s) affected)

 

Current Service State

————————————————————————————
Stopped.

 

(1 row(s) affected)

 

Current Service State

————————————————————————————
Stopped.

 

(1 row(s) affected)

 

Start/Stop/Pause/Continue a service using xp_servicecontrol:

Note: Not all services support paused state.

— Start/Stop Services

EXEC xp_servicecontrol N'start',N'SQLServerAGENT'

EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'

WAITFOR DELAY '00:00:05'

EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'

Result Set:

Service Started.

Msg 22003, Level 1, State 0

 

(0 row(s) affected)

 

Current Service State

————————————————————————————
Starting…

 

(1 row(s) affected)

 

Current Service State

————————————————————————————
Running.

 

(1 row(s) affected)

SQL Server Agent service does not support paused state, so an error will be raised when below code is executed:

— Pause/Continue Services

EXEC xp_servicecontrol N'pause',N'SQLServerAGENT'

EXEC xp_servicecontrol N'pause',N'Netlogon'

EXEC xp_servicecontrol N'continue',N'Netlogon'

Result Set:

Msg 22003, Level 16, State 1, Line 0

StartService() returned error 1052, 'The requested control is not valid for this service.'

Service Paused.

Msg 22003, Level 1, State 1

 

(0 row(s) affected)

Service Continued.

Msg 22003, Level 1, State 1

 

(0 row(s) affected)

Since Netlogon service does support paused state, pause/continue actions can be performed on the service.

 

When trying to start/stop a service, whose Startup mode is set to disabled, xp_servicecontrol will raise an error. On my system I have disabled Tablet PC Input Service, as I do not use pen devices to input:

— Disabled service

EXEC xp_servicecontrol N'querystate',N'TabletInputService'

 

EXEC xp_servicecontrol N'start',N'TabletInputService'

Result Set:

Current Service State

————————————————————————————
Stopped.

 

(1 row(s) affected)

 

Msg 22003, Level 16, State 1, Line 0

StartService() returned error 1058,

'The service cannot be started, either because it is disabled or because it has no enabled devices associated with it.'

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