Archive for March, 2009

sp_refresh_log_shipping_monitor (Transact-SQL)

This stored procedure refreshes the remote monitor tables with the latest information from a given primary or secondary server for the specified log shipping agent. The procedure is invoked on the primary or secondary server.

sp_refresh_log_shipping_monitor@agent_id = ‘agent_id’,@agent_type =’agent_type’@database = ‘database’@mode n

@agent_id =’agent_id’The primary ID for backup or the secondary ID for copy or restore. agent_id is uniqueidentifier and cannot be NULL.

@agent_type =’agent_type’
The type of log shipping job.
0 = Backup.
1 = Copy.
2 = Restore.
agent_type is tinyint and cannot be NULL.

@database =’database’
The primary or secondary database used by logging by backup or restore agents.

@mode n
Specifies whether to refresh the monitor data or clean it. The data type of m is tinyint, and the supported values are:
1 = refresh (This is the default value.)
2 = delete


sp_refresh_log_shipping_monitor refreshes the log_shipping_monitor_primary, log_shipping_monitor_secondary, log_shipping_monitor_history_detail, and log_shipping_monitor_error_detail tables with any session information that has not already been transferred. This allows you to synchronize the monitor server with primary or a secondary server when the monitor has been out of sync for awhile. Additionally, it allows you to clean up the monitor information on monitor server if necessary.sp_refresh_log_shipping_monitor must be run from the master database on the primary or secondary server.

Only members of the sysadmin fixed server role can run this procedure.



Tracking Sqlserver Waits

Posted: March 4, 2009 in Sqlserver

There are three DMVs available that allow you to view waits directly. You can use sys.dm_exec_ requests to view session-level information. The sys.dm_os_waiting_tasks DMV allows you to see information at the task level. The sys.dm_os_wait_stats DMV shows you an aggregation of wait times.

sys.dm_exec_requests – Session Level Information Only

This DMV shows all the waiting and blocking information that you would have queried sysprocesses for in SQL Server 2000. However, both sysprocesses and sys.dm_exec_requests are based at the session level, and a better view of performance can be obtained by looking at the task level. System processes can run tasks without a session, so they wouldn’t be represented here, and parallel queries are harder to troubleshoot when only a single wait is shown at the session level. Following is a sample script that shows wait information and the T-SQL currently running in each session where available:

SELECT    er.session_id,







FROM sys.dm_exec_requests er

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

sys.dm_os_waiting_tasks – All Waiting Tasks

sys.dm_os_waiting_tasks lists all tasks that are currently waiting on something and is the most accurate for viewing current waits. It contains information to identify a task, an associated session, details of the wait, and blocking tasks as well. However, a task only has an entry for as long as it’s waiting, so sys.dm_os_waiting_tasks tends to be used for interactive investigations rather than for monitoring purposes. You can use the columns that report on blocking tasks to identify blocking locks, which is discussed toward the end of the chapter. Here is a sample script that shows all the information for waiting tasks with the T-SQL currently running when a session_id is available:

SELECT    wt.*,


FROM sys.dm_os_waiting_tasks wt LEFT JOIN sys.dm_exec_requests er

ON wt.waiting_task_address = er.task_address

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

ORDER BY wt.session_id

sys.dm_os_wait_stats – Aggregated Times by Wait Type

This DMV is an aggregation of all wait times from all queries since SQL Server started and is ideal for monitoring and server-wide tuning. You can reset the wait statistics by running DBCC sqlperf (‘sys.dm_os_wait_stats’, clear). The following sample script from Microsoft is a great way to check for CPU pressure by comparing signal wait times (CPU wait) with resource wait times:

Select signalWaitTimeMs=sum(signal_wait_time_ms)

    ,’%signal waits’ = cast(100.0 * sum(signal_wait_time_ms) / sum

(wait_time_ms) as numeric(20,2))

    ,resourceWaitTimeMs=sum(wait_time_ms – signal_wait_time_ms)

    ,’%resource waits’= cast(100.0 * sum(wait_time_ms – signal_wait_time_ms) /

sum (wait_time_ms) as numeric(20,2))

from sys.dm_os_wait_stats

If you want to clear the historical data before you run the load to monitor, run this:

DBCC sqlperf (‘sys.dm_os_wait_stats’,clear)

It will clear out data that we’re not interested in and give you a fairly clean measurement:

signalWaitTimeMs  %signal waits  resourceWaitTimeMs  %resource waits

—————-  ————-  ——————  —————

          445837          17.92             2042154             82.08

Signal waits should be tiny in comparison to resource waits, so these results indicate a very heavy demand for the CPU.


A transaction is one or more actions that are defined as a single unit of work. In the Relational Database Management System (RDBMS) world they also comply with ACID properties:

§  Atomicity: An atomic transaction means either all the actions happen or none of them. If an action fails half-way through a transaction, then all previous actions in the transaction must be rolled back as if they never happened.

§  Consistency: A transaction cannot break the integrity rules of the database; it must leave the database in a consistent state. For example, you might specify that stock levels cannot be a negative value, a spare part cannot exist without a parent object, or the data in a sex field must be male or female.

§  Isolation: SQL Server is designed to service many concurrent users, but from the viewpoint of each user, the data set must look like the user is the only one on the system. Each transaction must be entirely self-contained, and changes it makes must not be readable by any other transaction. SQL Server allows flexibility in the degree of isolation you can specify for your transaction so that you can find a balance between the performance and business requirements.

§  Durability: When a transaction is committed, it must persist even if there is a system failure immediately afterwards. When you commit a transaction in SQL Server, the information needed to replay it is physically written to the transaction log before the commit is returned to the user as successful.

Atomicity, consistency, isolation, and durability are inherent properties of SQL Server transactions. Isolation has a degree of flexibility and is a choice which requires a more detailed understanding, so we will explore it further in the following section.

Isolation Levels

There are five transaction isolation levels available in SQL Server 2005 that provide increasing levels of isolation.

§  Read uncommitted: This is the least isolated and best performing level, but it does allow dirty reads, non-repeatable reads, and phantoms. It can be used when you don’t care about dirty reads and you want to read the data with the lightest touch possible. It doesn’t hold any locks on the data when reading.

§  Read committed: This is the default isolation level for SQL Server and usually provides the best balance between performance and business requirements. It does not allow dirty reads, but non-repeatable reads and phantoms are still possible. Any locks held are released when the statement that caused the read operation is complete, even within a transaction. SQL Server 2005 also has a new flavor of read-committed based on row versioning called read-committed snapshot which is covered later in this chapter.

§  Repeatable read: A repeatable read is possible by holding read locks for the duration of a transaction to prevent other transactions from modifying the data so you can have a repeatable read. It prevents dirty reads and non-repeatable reads but phantoms can still occur.

§  Serializable: This serializes access to data and prevents all of the side effects by holding locks for the duration of the transaction and effectively locking rows that don’t even exist yet through key range locks. This is the most isolated level and the most damaging to high concurrency.

§  Snapshot: The snapshot isolation level is the only optimistic isolation level available and uses row versioning rather than locking. It prevents all of the previously mentioned side effects just like serializable, but it does allow for an update conflict to occur that wouldn’t if transactions were run serially. This conflict occurs when data to be changed inside a snapshot transaction is changed concurrently by another transaction. Detection occurs automatically and causes a rollback of the snapshot transaction to prevent a lost update. Snapshot and row versioning will be covered in more depth after looking at locking in more detail.

summarizes the isolation levels and possible anomalies.

 Isolation Levels and Possible Anomalies

Isolation level

Dirty Read

Non-Repeatable Read

Phantom Read

Update Conflict

Concurrency Model

Read Un-Committed






Read Committed






1 – Locking






2 – Snapshot






Repeatable Read


















To use a transaction isolation level other than the default read-committed, run the SET TRANSACTION ISOLATION LEVEL T-SQL command. For example:




Rebuilding the Master

Posted: March 4, 2009 in Sqlserver

If the instance can’t start due to a damaged master database, it can be rebuilt. However, this will revert all the system databases to their original state, requiring additional steps to restore each of the system databases. Additionally, all of the user databases will need to be restored to allow the rebuilt master database to learn of them.

There are two instances when the master must be rebuilt:

§  A current backup of the master database is not available.

§  The SQL Server instance won’t start due to a corrupt master.





The Rebuildm.exe program has been discontinued. Notice this does not read “deprecated.” If you have experience with a previous edition of SQL, you may have used rebuildm, but it is not available in SQL Server 2005.

Once the master database is rebuilt, it can be restored from a recent master database backup.

The tool used to rebuild the master database in SQL Server 2005 is setup.exe. Setup.exe is used to rebuild, verify, and repair an SQL Server instance, and to rebuild system databases. The most common use is to rebuild a corrupt master database, since a corrupt master will prevent the instance from starting. To rebuild the master, setup is run from the command line.

A corrupt master database is a little challenging to identify. The error log records what’s going on when an instance starts and is a good place to start.

 Remember, the error log is located in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Log folder.

The following listing is a partial listing of an error log of an instance that won’t start.


2007-03-21 03:06:31.29 Server    Microsoft SQL Server 2005 – 9.00.3050.00

(Intel X86)

      Mar 2 2007 20:01:28

      Copyright (c) 1988-2005 Microsoft Corporation

      Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-03-21 03:06:31.29 Server      (c) 2005 Microsoft Corporation.

2007-03-21 03:06:31.29 Server      All rights reserved.

2007-03-21 03:06:31.29 Server      Server process ID is 156.

2007-03-21 03:06:31.29 Server      Authentication mode is MIXED.

2007-03-21 03:06:31.29 Server          -d C:\Program Files\Microsoft SQL


2007-03-21 03:06:31.29 Server          -e C:\Program Files\Microsoft SQL


2007-03-21 03:06:31.29 Server          -l C:\Program Files\Microsoft SQL


2007-03-21 03:06:31.32 Server Error: 17113, Severity: 16, State: 1.

2007-03-21 03:06:31.32 Server Error 2 (The system cannot find the file

specified) occurred while opening file ‘C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSLQ\DATA\master.mdf to obtain configuration . . .

. . .

This error log shows we have a problem with the master database preventing the instance from starting. It must be rebuilt.


Note:  If the master is corrupt, preventing an SQL Server instance from starting, use the setup.exe program from the command line to rebuild it.

Single User Mode

Under certain circumstances you may be able to recover a damaged master database or other system database by starting SQL Server in single user mode. Consider single user mode an advanced tool that shouldn’t be used lightly.


Note:  Single user mode should not be used for routine maintenance. It’s an advanced tool that should only be used in extreme situations. Single user mode effectively locks out all users from the database.

To access single user mode, both the SQL Server service and the SQL Server Agent service must be stopped. With both those services stopped, you can open a command-line prompt and change the directory to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BINN. This path is for the first instance installed (MSSQL.1). If you were trying to access a different instance, you’d change the path accordingly.

At this point enter the following command to launch SQL Server in single user mode:

sqlservr – m

Following is a partial listing of the error log showing that the server was started in single user mode:

2007-03-22 06:22:30.50 Server   Microsoft SQL Server 2005 – 9.00.3050.00

(Intel X86)

      Mar 2 2007 20:01:28

      Copyright (c) 1988-2005 Microsoft Corporation

      Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-03-22 06:22:30.50 Server      (c) 2005 Microsoft Corporation.

2007-03-22 06:22:30.50 Server      All rights reserved.

2007-03-22 06:22:30.50 Server      Server process ID is 6024.

2007-03-22 06:22:30.50 Server      Authentication mode is MIXED.

2007-03-22 06:22:30.50 Server      Logging SQL Server messages in file

‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG’.

2007-03-22 06:22:30.50 Server      This instance of SQL Server last reported

using a process ID of 2792 at 3/22/2007 6:22:08 AM (local) 3/22/2007 10:22:08

AM (UTC). This is an informational message only; no user action is required.

2007-03-22 06:22:30.50 Server      Registry startup parameters:

2007-03-22 06:22:30.50 Server         -d C:\Program Files\Microsoft SQL


2007-03-22 06:22:30.50 Server         -e C:\Program Files\Microsoft SQL


2007-03-22 06:22:30.50 Server         -l C:\Program Files\Microsoft SQL


2007-03-22 06:22:30.50 Server      Command Line Startup Parameters:

2007-03-22 06:22:30.50 Server         -m

2007-03-22 06:22:30.53 Server      SQL Server is starting at normal priority

base (=7). This is an informational message only. No user action is required.

The complete listing is displayed at the command prompt. Each of the databases in the server instance will be started. It will take some time for the startup to complete.

At this point you can use the setup.exe program to rebuild the master database. Open another command prompt, and identify the path where the setup command is located. In my system, the installation CD is in the D: drive, and the setup command is located in the Servers directory. The basic syntax is


Start /wait d:\servers\setup.exe /qn instancename = instanceName reinstall =

SQL_Engine REBUILDDATABASE=1 sapwd = sapassword