Thursday, October 6, 2011

How to get last restore date and backup file name

This script is with a small modification to include the backup file name from the article published in
This script will output the Orignal DB Name, Destination DB Name, Last Restore date and Backup file name for the databases in a SQL Server. 

bus.database_name Org_DBName,Restored_To_DBName,Last_Date_Restored, Backup_nameFROMmsdb..backupset busINNER
..restorehistoryINNER JOIN(
SELECT .destination_database_name Restored_To_DBName,

Max(rh.restore_date) Last_Date_RestoredFROM ..restorehistory rhGROUP BYrh

.destination_database_name) AS InnerRestONdestination_database_name = Restored_To_DBName ANDrestore_date = Last_Date_Restored) As RestDataONbus

You get a specific database just add a where clause against the appropriate field. It makes use of the restorehistory and backupset tables in the MSDB database.
.backup_set_id = RestData.backup_set_id

Friday, September 2, 2011

Performance Monitoring

Performance monitoring is an essential task for every DBA. Windows system monitor, Performance Logs and Alerts can be used for performance monitoring task. A 24-hour performance log is generated using the counters on a normal working day. This log can be used as a performance baseline document. SQL performance Baseline Template can be downloaded here.

If there exists sudden increase or decrease in threshold levels of any of the counters in everyday monitoring, an alert can be raised and to notify us.

The following is a partial list of counters that can be used for performance monitoring. Values of each counter may differ from server to server. So you should check with your baseline document.

SQL Server: SQL Statistics: Batch Requests/secAnalyzing Statement Recompiles
SQL Server: SQL Statistics: SQL Compilations/sec
SQLServer: SQL Statistics: SQL Recompilations/sec
SQL Server: Access Methods: Mixed Page Allocations /SecAnalyzing tempdb contention

SQL Server: Access Methods: Workfiles Created /Sec
SQL Server: Access Methods: Worktables Created /Sec
SQL Server: General Statistics: Temp Tables Created /Sec
SQL Server: General Statistics: Temp Tables For Destruction
LogicalDisk: % Free SpaceEvaluating Disk Usage
Physical Disk: % Free Space
Physical Disk: Avg. Disk Queue Length
PhysicalDisk: % Disk Time
Memory: Available BytesEvaluating Memory Usage
Memory: Pages/sec
SQLServer: Memory Manager: Total Server Memory (KB)
Physical Disk: Disk Reads/sec
Physical Disk: Disk Writes/sec
SQLServer: Buffer Manager: Buffer Cache Hit Ratio
Network Interface: Bytes Received/secEvaluating Network Usage
Network Interface: Bytes Sent/sec
Network Interface: Bytes total/sec
Network Interface: Bytes/sec
Network Interface: Output Queue Length
Processor: % Privileged TimeEvaluating Processor Usage
Processor: % Processor Time
System: Processor Queue Length
SQLServer: General Statistics: User ConnectionsEvaluating User Connections
SQLServer: Databases: Log GrowthsMonitoring Database Availability and Transaction Log

SQLServer: Databases: Percent Log Used
SQLServer: Databases:Data File(s) Size (KB)
SQLServer: SQL Errors: Errors/sec
SQLServer: Latches: Average Latch Wait Time (ms)Monitoring Latch Wait Times
SQLServer: Latches:Latch Waits/sec
SQLServer: Latches:Total Latch Wait Time (ms)
SQLServer: Access Methods: Full Scans/secMonitoring Server Availability
SQLServer: Databases: Transactions/sec
Paging File: % UsageMonitoring Server Health
Process: % Processor Time
SQLServer: User Settable: Query
SQLServer: Locks: Lock Timeouts/secMonitoring Waits and Queues
SQLServer: Locks: Lock Waits/sec
SQLServer: Locks:Average Wait Time (ms)
SQLServer: Locks:Lock Requests/sec
SQLServer: Locks:Lock Timeouts (Timeout > 0)/sec
SQLServer: Locks:Lock Wait Time (ms)
SQLServer: Locks:Number of Deadlocks/sec
SQLServer:Wait Statistics:Lock Waits
SQLServer:Wait Statistics:Log Buffer Waits
SQLServer:Wait Statistics:Log Write Waits
SQLServer:Wait Statistics:Memory Grant Queue Waits
SQLServer:Wait Statistics:Network IO Waits
SQLServer:Wait Statistics:Non-Page Latch Waits
SQLServer:Wait Statistics:Page IO Latch Waits
SQLServer:Wait Statistics:Page Latch Waits
SQLServer:Wait Statistics:Thread-Safe Memory Objects Waits
SQLServer:Wait Statistics:Transaction Ownership Waits
SQLServer:Wait Statistics:Wait For The Worker
SQLServer:Wait Statistics:Workspace Synchronization Waits
.NET CLR Memory: # GC HandlesSQL CLR Memory Usage
.NET CLR Memory: % Time In GC
.NET CLR Memory: Allocated Bytes/Sec

Reference: Monitoring Resource Usage (System Monitor)

Tuesday, April 19, 2011

SQL Server 2005 Mainstream support Ended

The Microsoft SQL Server 2005 Mainstream support has ended by 12-April-2011. Therfore the certification exams associated with the product will also be expired. So please note that all the Microsoft SQL Server 2005 exams are scheduled to retire on June 30, 2011.

Choose from the following three MCITP certifications on SQL Server 2005.

MCITP certification
Prerequisite MCTS certification
MCITP: Database Administrator
MCTS: SQL Server 2005
Exam 70-443:* PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005
Exam 70-444:* PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005
MCITP: Database Developer
MCTS: SQL Server 2005
Exam 70-441:* PRO: Designing Database Solutions by Using Microsoft SQL Server 2005
Exam 70-442:* PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
MCITP: Business Intelligence Developer
MCTS: SQL Server 2005, Business Intelligence Development and Maintenance
Exam 70-446:* PRO: Designing a Business Intelligence Solution by Using Microsoft SQL Server 2005
*This exam is scheduled to retire on June 30, 2011.

Buy a Microsoft Certification Pack with Second Shots and save up to 20% Certification Exam Packs provide large discounts plus a Second Shot for every exam in the pack. The Second Shot offer allows you to retake an exam if you do not pass it the first time.

New: If you obtained your Exam Pack voucher code before January 1, 2011, you have until June 30, 2011, to complete your exams and any retakes. If you obtain your Exam Pack voucher code on or after January 1, 2011, you have until June 30, 2011, to schedule and take your first exam. 

You can find the benefits of doing certification from Microsoft’s web site. You can find the salary ranges in INR by job for employees with MCTS – SQL Server 2005 certification.

Those who have are willing to have SQL Server 2005 certification should consider to take the exam before 30-June-2011 followed by a upgrade exam for SQL Server 2008.

Error Handling in SQL Server 2005

TRY…CATCH construct is used for error handling in side T-SQL code. A TRY…CATCH construct consists of 2 blocks. One is TRY block that is immediately followed by a CATCH block. TRY…CATCH construct can be nested. When an error condition is identified in TRY block, the control is immediately transferred to CATCH block where the error can be handled.

The following code represents a basic TRY…CATCH construct.



Error Functions:

The following error functions can be used inside a TRY…CATCH construct to get the information about the error that is occurred.

ERROR_NUMBER() returns the error number.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.
ERROR_SEVERITY() returns the error severity.
ERROR_STATE() returns the error state number.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.


RAISERROR can be used inside a TRY…CATCH construct for error handling. RAISERROR generates error message using user defined messages that is stored inside sys.messages catalog view or build a message dynamically.

The following code returns an error message and the WITH LOG statement causes the error to be logged into windows application log. Using event viewer, you can view the log.

    RAISERROR ('Error raised in TRY block.', 16,10) WITH LOG;
    SELECT Error_Message() as Message_text,
               Error_Severity() as Severity,
               Error_State() as State

1. What types of error cannot be handled by TRY…CATCH construct. Choose all correct answers from the following:

a. All DDL statement errors

b. All DML statement errors

c. Divide by Zero error

d. Syntax errors that prevent a batch from executing.


1. d.