Stopping and starting services

A script to Stop and Start the SQL Server Agent Service

-- Check status
EXEC xp_servicecontrol 'QUERYSTATE','SQLServerAGENT'

-- STOP SQL Server Agent
EXEC xp_servicecontrol 'STOP','SQLServerAGENT'
GO
-- Check status
EXEC xp_servicecontrol 'QUERYSTATE','SQLServerAGENT'

-- START SQL Server Agent
EXEC xp_servicecontrol 'START','SQLServerAGENT'
GO
-- Check status
EXEC xp_servicecontrol 'QUERYSTATE','SQLServerAGENT'

SQL Server Profiler

There are 2 ways to run SQL Profiler

• Run the SQL Profiler client tool through the GUI
• Server-side tracing where events are saved to a physical file on the server by using SQL Server system stored procedures and functions.

To view the number of traces running:

SELECT count(*)
FROM :: fn_trace_getinfo(default)
WHERE property = 5 and value = 1

To obtain more detail about the active traces:-

SELECT *
FROM :: fn_trace_getinfo(default)

To stop a trace using the traceid:-

EXEC sp_trace_setstatus 1, @status = 0
EXEC sp_trace_setstatus 1, @status = 2

status = 0 stops the trace
status = 2 closes the trace and deletes its definition from the server

A sample output :-

traceid,property,value
1,1,2
1,2,D:\MSSQL10.MSSQLSERVER\MSSQL\Log\log_1504.trc
1,3,20
1,4,NULL
1,5,1


This means

Result Set Description
Traceid Unique identifier for the trace
Property = 1 Configured trace options
Property = 2 Trace file name
Property = 3 Max file size for the *.trc file
Property = 4 Stop time for the trace session
Property = 5 Current trace status (1 = On and 0 = Off)
Value Current value for the traceid\property combination

The Default trace
The default trace great for diagnosing performance problems, finding deadlocks, and auditing security information.The default trace does not capture all trace events, but it captures key information such as auditing events, database events, error events, full text events, object creation, object deletion and object alteration. It also captures log growth events which can be invaluable to troubleshooting disk capacity problems.

Create Windows folders

To create windows folders using T-SQL run

EXEC master.dbo.xp_create_subdir 'D:\sqlserverbackups'
GO

Stop the login SID mismatch

To stop the user SID's mismatch in SQL Server 2005 for databases that are continually moved and restored from one database server to another

Run a script to collect the current sid of the user after a user database has been restored

username = bennett
USE ;
GO
SELECT sid FROM sysusers WHERE name = 'bennett';
GO

Then create a server login. Use the SID from the above query output and replace the username and password with the relevant details.

USE master
CREATE LOGIN [bennett]
WITH PASSWORD = 'Ax$ef6!f', SID = 0xA2AE403E43ED084C8B3021E7E8DFD61C,
CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;

Then create a database user for bennett, with the default schema dbo.

USE ;
CREATE USER [bennett] FOR LOGIN [bennett]
WITH DEFAULT_SCHEMA = dbo;
GO

When you restore the database again the database user continues to work with no further action required.

Commands to help tune queries

SET STATISTICS IO ON

This command displays the amount of disk activity for transact SQL statements. Sample output is

Table 'servers'. Scan count 1, logical reads 7,physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

To turn off run
SET STATISTICS IO OFF


SET STATISTICS TIME ON

SQL Server firstly makes logical reads to retrieve data from the data cache, if the data is not in the data cache it makes physical reads from disk. This script displays the amount of CPU resources needed by the query to run, parse, compile, and execute each statement in milliseconds

A sample output is

CPU time = 0 ms, elapsed time = 145 ms

To turn off run
SET STATISTICS TIME OFF