Monday, December 22, 2008

SQL Server 2000 SP3 Creates a SQLDebugger Windows User Account

INF: SQL Server 2000 SP3 Creates a SQLDebugger Windows User Account
The SQLDebugger Windows user account is created when the SQL Debugger Registry2 DCOM server process (Sqldbreg2.exe) is registered. By default, SQL Server 2000 SP3 registers this process, and the SQLDebugger Windows user account is created.

SQL Query Analyzer includes
T-SQL Debugger. By using T-SQL Debugger, you can control and monitor
how stored procedures run. T-SQL
Debugger uses the SQLDebugger Windows user account to connect to the database server.

Microsoft Visual Studio .NET applications use SQL Server Debugging to debug SQL Server stored procedures. The SQLDebugger Windows
user account is also created when you install Visual Studio

The SQLDebugger Windows user account has the following characteristics :
  • It belongs to the built-in Windows Users group.
  • It does not have local logon rights.

Sunday, December 21, 2008

How to remove / uninstall SQL Server 2005 Manually

Run the command to uninstall the SQL Server components
At a command prompt, run the following command:

"%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe /Remove"

Uninstall the SQL Server components one at a time until all the SQL Server components are uninstalled.

Note Add or Remove Programs also runs the ARPWrapper.exe program by using the /Remove option. However, the reference to the ARPWrapper.exe program may have been deleted.


SQL Server 2005 - Cluster Tips

1. Installing Tools and Documentation
SQL Server 2005 Setup no longer installs the tools and documentation components of SQL Server 2005 on all the nodes of a cluster. The wizard will only install these
components on the node that the Setup routine is initiated from. If tools and documentation is required on the other nodes, you will need to install these components
manually after finishing the setup of the cluster.

2. Install SQL 2005 Analysis Services on separate Resource group
Microsoft recommends installing Analysis Services clustered installations in a single group with its own disk and IP resources and not on a resource group that has
the database engine installed on it.

3. Create Windows Domain Groups
It is usually recommended that you create four different domain groups when creating a cluster. One for each of the following:
1. SQL Server 2005 DB Service,
2. SQL Server 2005 Agent service,
3. SQL Server 2005 Full-Text Search service,
4. SQL Server 2005 Analysis Services service.

4.Installing Reporting Services
Unfortunately, Reporting Services is not cluster-aware and will have to be installed as stand-alone components on all nodes in the clustered environments. Reporting Services can make use of clustered databases. But when a node fails over the connections to any Reporting Services, instances on the failed node will not be automatically reconnected to the new node. Connections will have to attempt to connect to the new node using a new IP address

5. Installing SQL Server Integration Services
To install SSIS, you must install SSIS on all active nodes and then make the SSIS Service a clustered resource. To make SSIS a clustered resource, perform the following tasks:
1. Open the Cluster Administrator
2. On the File menu, point to New and then click Resource
3. On the New Resource page of the Resource Wizard, type a Name and choose “Generic Service” as the Service Type. Change the value of Group to SQL group. Click Next.
4. On the Possible Owners page, add or remove the nodes of the cluster as the possible owners of the resource. Click Next.
5. To add dependencies on the Dependencies page,select a resource under Available resources, and thenclick Add. In case of a failover, both SQL Server 2005
and the shared disk that stores Integration Services packages should come back online before Integration Services is brought online. After you have selected the
dependencies, click Next.
6. On the Generic Service Parameters page, enter MsDtsServer as the name of the service. Click Next.
7. On the Registry Replication page, click Add to add the registry key that identifies the configuration file for the Integration Services service.
a. This file needs to be located on a shared disk that is in the same group for the Integration Services service, and fails over to the next node along with for
the Integration Services service.
b. In the Registry Key dialog box, type SOFTWARE\Microsoft\MSDTS\ServiceConfigFile. Click OK, and then click Finish. The Integration Services service
has now been added as a clustered service.
8. Locate the configuration file at %ProgramFiles%\Microsoft SQL Server 2005\90\DTS\Binn\MsDtsSrvr.ini.xml. Copy it to the shared disk.
9. Create a new folder named Packages on the shared disk. Grant List Folders and Write permissions on the new folder to the built-in Users group.
10. Open the configuration file from the shared disk in a text or XML editor. Change the value of the ServerName element to the name of the virtual SQL Server 2005 which is in the same group.
11. Change the value of the StorePath element to the fully-qualified path of the Packages folder created on the shared disk in a previous step: ??:\Packages.
12. Update the value of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile in the Registry to the fully-qualified path and filename of
the service configuration file on the shared disk
13. In the Cluster Administrator, select the Integration Services service, right-click, and select Bring Online from the popup menu. The Integration Services service
is now online as a clustered service

Thursday, December 18, 2008

How to Grant ALTER TRACE permission using Management Studio?

In SQL Server 2005 / 2008 to grant SQL Profiler access for a user;
Security tab --> Logins --> right click name to modify select properties --> securables tab --> Click add --> Select the server you want to add the permissions to. The permissions should appear in the effective permissions list

USE master;

-->Where User Name for domain account is [Domain\accountname];

Tuesday, December 16, 2008

PMP - Formula

Tuesday, December 02, 2008

DOS Batch file to create a file name in data stamp

:: 2. Set logfile name based on today's date

for /F "tokens=1,2,3,4 delims=/ " %%I in ('date /T') do set Logfile=%%J%%K%%L.log

:: 4. Delete all but 7 most recent log files

:: Get a sorted list of the log files oldest at the bottom of the list

Dir /b /o-d %Logfolder%\*.log > %Workfolder%\%LogList%

:: The find command counts how many log files there are and for sets TempVar with this value

For /f %%A in ('Dir /b /o %Logfolder%\*.log^| Find /c /i "log"') do set TempVar=%%A

:: If there are more logs than needed skip the number to keep and delete the oldsest one(s)

If %TempVar% GTR %LogsToKeep% For /f "skip=%LogsToKeep%" %%B in (%Workfolder%\%LogList%) do Del /q %Logfolder%\%%B
Del /q %Workfolder%\%LogList%

Saturday, November 22, 2008

Wednesday, September 17, 2008

Query TSM Archive to check file exits

@echo off
set script_dir=c:\progra~1\tivoli\tsm\scripts
echo dsmc query archive %1 -su=yes -fromdate="%2" -todate="%2"
dsmc query archive %1 -optfile="c:\progra~1\tivoli\tsm\baclient\dsm_swift.opt" -fromdate="%2" -fromtime=00:00 -todate="%2" -totime=23:00



echo ####################### FAILED #########################################
echo #
echo # The files you are searching for are not there . Check your syntax
echo # Date range has to be MM/DD/YYYY
echo # Directory must exist , put an asterisk on the end e.g
echo # h:\extend\*
echo # or h:\extend\Orbis_1\OFAS_CN_PRD.001
echo # Archive are kept for 14 days as per Todd Rd setup
echo #
echo ########################################################################
GOTO end

echo ###################### SUCCESS #########################################
echo #
echo # This is the list of available archives within the date range you specified
echo # Use this list in the DSMC RETRIEVE command
echo #
echo # dsmc query archive %1 -optfile="c:\progra~1\tivoli\tsm\baclient\dsm_swift.opt" -fromdate="%2" -fromtime=00:00 -todate="%2" -totime=07:00
echo #
echo #
echo #
echo ########################################################################


To retrieve the data from Remote Server
@echo off
set script_dir=c:\progra~1\tivoli\tsm\scripts
dsmc set access archive %1 %3 -optfile="c:\progra~1\tivoli\tsm\baclient\dsm_swift.opt"
dsmc query archive \\%3\%1 -optfile="c:\progra~1\tivoli\tsm\baclient\dsm_swift.opt" -fromnode=%3 -fromdate="%2" -fromtime=00:00 -todate="%2" -totime=23:00

Wednesday, September 10, 2008

Who dropped objects from database?

Balmukund : Who dropped objects from database?

Sometime I have been asked that how to find out who
dropped/altered/created table/stored procedure. Well, SQL 2005 has
out-of-the-box solution to this problem.

Schema Change History
is the report which would give details. This report takes data from
default trace which is enabled by default in SQL Server 2005 default
installation. Below command will help you in finding current status.

USE master;
EXEC sp_configure 'show advanced option', '1';
EXEC sp_configure 'default trace';

Friday, September 05, 2008

SQL Server 2008 Release announcement

SQL Server 2008 has been released and is ready to deliver a rich set of integrated services that enable you to do more with your data, such as query, search, synchronize, report, and analyze. Many of you have been evaluating the SQL Server 2008 betas, and may be ready to try out the RTM version. TechNet Plus subscribers can find RTM versions now available for download. Speaking of TechNet Plus, read Keith Combs' Blahg for his take on the UI improvements for the newly revamped TechNet Plus Subscriber Downloads page.

TechNet Magazine recently published an overview of the key changes and what you can expect from SQL Server 2008. You can also get a rundown of the various editions and components of SQL Server 2008 and take part in SQL Server 2008 tutorials.

For more overview resources, check out:

•          SQL Server 2008 Videos

•          Webcasts: 24 Hours of SQL Server 2008 for IT Professionals

•          Webcasts: 24 Hours of SQL Server 2008 for Developers

If you are looking for assistance with SQL Server 2008 planning and installation, here are some helpful links:

•          Planning a SQL Server Installation

•          Installing SQL Server 2008

•          Quick Start Installation

•          Installation How-to Topics

•          Locating Previous Versions of SQL Server for SQL Server 2008 migration using Microsoft Assessment and Planning Toolkit

Once you've installed SQL Server 2008, these resources can help you optimize your database environment:

•          Configuring SQL Server 2008

•          Database Engine Tuning Advisor Overview

•          System and Sample Databases

You can also find guidance for upgrading to SQL Server 2008 or migrating to SQL Server 2008. If you are upgrading, be sure to check out Using Upgrade Advisor to Prepare For Upgrades.

From our partners, you can find local partners offering SQL Server 2008 solutions or find training partners near you offering official Microsoft courses that will help you get certified on SQL Server 2008. In addition, you can search for local in-person events and online webcasts on SQL Server 2008.

And finally, from the August edition of TechNet Magazine, Paul Randal answers your SQL Server questions, showing you how to perform consistency checks, how to handle an accidental upgrade, and more in the SQL Q&A column.

If you have any questions.;

Kinga Kovacs
| Technical Account Manager - MICROSOFT Australia

61-419 746 731

| 61-2-9870 2499 |

Saturday, August 23, 2008

SQL Server DBA Telecommuting Tips

1Internet Access
Whether it's Wi-Fi, T1, Air
Card, or a wired connection you can not work remotely as a DBA without being
able to connect to work. There is definitely no need to dwell on this topic,
but it does lead to the next item...
If it is a current or an
antiquated method of connecting to the office I've done it. When I first
started out I was working as an Access Developer. I'd copy my work from my
workstation to disk and then develop on my home PC on the days I'd work from
home using the media as a shuttle. Once it was a day to go back into the office
I'd copy my work back to disk and repeat the process in reverse. Now the
options are usually Citrix or VPN. In order to support your SQL environment you
must be able to connect to it. Using VPN technology on my laptop I end up with
the same connection whether that laptop is on my desk at home or on my desk at
3SQL Management
Chalk this up to the obvious.
You need to have the same software you've access to at work, while at home.
First and foremost for the SQL Server Database Administrator is Microsoft SQL
Server Management Studio.
4Remote Server
This is a must in most (but
not all) SQL Server DBAs' tool belts. It is also a point of contention for many
IT Departments between the Server Engineers, Security, and Database
Administrators. If you think about it, how often do you physically touch the
servers hosting your SQL instances? I think I've seen only five of the
80 servers I support. When it comes right to it, even when I'm in the office,
I'm technically a remote worker. I rely heavily on Microsoft Terminal
Services. After SQL Server Management Studio and Microsoft Outlook, it is
probably my most-used application. If you need to copy backup files or
detach/attach databases to migrate between PROD/TEST/DEV/BUILD, you are wise to
use Terminal Services over Windows Explorer. There is a noticeable performance
hit when copying a file from Server A to Server B using two Explorer windows on
your workstation versus using Terminal Services to connect to Server B and then
copying a file from a Explorer window on Server A. If using your workstation to
move files, you are actually copying the file(s) from Server A to your
workstation and then from your workstation to Server B. Think about the time
difference to do this when moving a 20Gb data file from BUILD to PROD from your
home office using DSL!
Connectivity Software
Just because you're working
away from the office doesn't mean you can ignore your coworkers. Besides the
obligatory email/office productivity software (Outlook, Groupwise, etc.) instant
messaging software is also important. We've used a variety of products in our
company before settling on Microsoft's Office Communication Server (OCS). Nice
thing about it - it uses a SQL Server database. Support your local Relational
Database Management System!
6VOIP Those cell phone bills can add
up over time. Anyone who has ever been on the support line to Microsoft through
two shifts on their personal cell phone can attest to that. Recently I
installed a product from Nortel, IP SoftPhone that allows me to answer calls and
make calls from, my desk phone in the office. While this is not a requirement
to working remotely it does take some of the financial pain away from
7Telecommuting is not for
the Social Butterfly
I do not recommend
telecommuting more than a couple days per week to those individuals who crave
the social aspects of the office environment. You'll find yourself going
stir-crazy if this a personality trait. When in the office you'll spend too
much time socializing to get your "people fix". Telecommuting is just not for
8Don't Become Invisible;
Don't Become a Nuisance
There have been studies
showing that those individuals who work remotely to some extent lag behind
workers that exclusively work in the office when it comes to wage increases.
Much of this has been attributed to the lack of visibility these remote workers
have compared to their office-bound co-workers.

So, how do you get around this? With moderation. I
find that keeping a running email with nothing more than bullet points
highlighting what I worked on over the course of the day, with high-level
detail, provides the proper amount of information to keep my Manager
"in-the-loop". He's often commented that he knows more about what I do than any
of the other staff that report to him. What this also does is prevents the
formation of a bad habit of cc-ing your supervisor on each email you write
during the day in order to simply keep him/her involved and aware of what you're
doing. This documentation also serves a valuable purpose when it comes to your
drafting your annual self-performance review.

9Work Within Your
There is a big difference
between working two days per week remotely and working three days remotely.
There was a large behavioral adjustment when I shifted to three days remote,
even after working remotely two days per week for six years prior. It was
easier to get up and immediately start working, work through lunch, work until
dinner and realize I was still in the clothes I woke up in. It is amazing how
you find yourself working harder and longer and it takes a bit of time to work
out of this mode; a very unhealthy mode. My recommendations to combat

  • Maintain a dedicated home office or area dedicated
    just to work, be it a desk in the dining room or a room solely for this

  • Keep to a routine - get up and take that shower,
    grab your coffee, and walk into your home office. I've known of people who will
    get in their car, drive around the block and then come in through the front door
    and walk into their home office just to put mental separation between working
    from home and being at home.

  • Fight the urge to keep working for just a "few
    minutes more" at the end of the day. It is so easy just to keep working, since
    you don't have to commute back home at the end of the work day. Just like at
    the start of the day, come up with a process that separates work from personal

Oddly enough, when I began working four days remotely
per week I found the transition easier.

10Remember: It's a Benefit,
not an Endowment
Work every day remotely as if
it is something that can be taken away. Sure, you may have situations where
you'll need to step out and take care of something. Follow whatever protocols
your office expects if you were to step away for personal matters if you were
physically in the office. Sure, you may look out and see the lawn needs mowing
or that the laundry is piling up. Resist the urge to do anything about it
though until after work.

Courtesy: MS SQL Tips

Sunday, August 17, 2008

Wednesday, August 06, 2008

SQL Server 2008 Released today

SQL 2008 has today been released to manufacturing:
SQL Server 2008 Released

Some of the links are not yet live and here are some Key Dates
• Aug 6 – SQL Server 2008 RTM
• Aug 6 – Public RTM Announcements for SQL Server 2008 will go live at 10AM PDT; Aug 6 – TechNet Subscription & MSDN Subscription full product available for SQL Server 2008
• Aug 7 – TechNet & MSDN SQL Server 2008 trials available
• Aug 7 – How To Buy page updated with SQL Server 2008 info
• Aug 7 – SQL Server 2008 Feature Pack available
Aug 11 – SQL Server 2008 Express download available
• Aug 21 – SQL Server 2008 Volume Licensing SKUs available
• Aug 25 – SQL Server 2008 Express with Advanced Services and Tools available
• Sep 15 – TechNet & MSDN SQL Server 2008 Books-Online updated for all 10 Katmai languages

Announcing the release of SQL Server 2008, the enterprise data platform
for data warehousing, business intelligence, and LOB applications
The wait is over! SQL Server® 2008 is now available and is ready for purchase.

TechNet Magazine recently published an overview of the key changes and what you can expect from SQL Server 2008. You can also get a rundown of the various Editions and Components of SQL Server 2008 and take part in SQL Server 2008 Tutorials.

An Overview of SQL Server 2008 installation will help you get started with your deployments. You can also find guidance for Upgrading to SQL Server 2008 or Migrating to SQL Server 2008. And if you are upgrading, be sure to check out Using Upgrade Advisor to Prepare For Upgrades.

From the August edition of TechNet Magazine, here are answers to your SQL Server questions, showing you how to perform consistency checks, how to handle an accidental upgrade, and more in the SQL Q&A column.

Finally, you can easily find local partners offering SQL Server 2008 solutions, find training partners near you offering official Microsoft courses on SQL Server 2008, as well as attend local in-person events and online webcasts on SQL Server 2008.

Saturday, July 19, 2008

SQL Server 2005 SP2 installation failed on Active/Passive Cluster

SP2 fails to install database services - MSP Error: 29512 “Unable to install Windows Installer MSP file”
Errors from Summary.log
Product : Database Services (SQL084)
Product Version (Previous): 2153
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29512
Error Description : MSP Error: 29512 SQL Server Setup was unable add user pbs\csparq_service to local group AU\SGG-GM_ServiceAccounts.

Configuration failed.

One or more products failed to install, see above for details
Exit Code Returned: 29512

Errors from SQL9_Hotfix_KB921896_sqlrun_sql.msp.log

MSI (s) (68:4C) [00:21:37:156]: Product: Microsoft SQL Server 2005 (64-bit) - Update 'Service Pack 2 for SQL Server Database Services 2005 (64-bit) ENU (KB921896)' could not be installed. Error code 1603.

Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log.

Local group AU\SGG-GMRISK_ServiceAccounts doesn't exist
Failure adding user pbs\acoe_csparq_service to local group AU\GMRISK_ServiceAccounts (5)
Error Code: 0x80070005 (5)
Windows Error Text: Access is denied.
Source File Name: sqlca\sqlsecurityca.cpp
Compiler Timestamp: Sat Oct 7 09:43:41 2006
Function Name: Do_sqlGroupMember
Source Line Number: 1132

MSI (s) (68:4C) [00:21:37:156]: Note: 1: 1729
MSI (s) (68:4C) [00:21:37:156]: Transforming table Error.

MSI (s) (68:4C) [00:21:37:156]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (68:4C) [00:21:37:188]: Transforming table Error.

MSI (s) (68:4C) [00:21:37:188]: Transforming table Error.



ETVX: Entry, Task, Verification, and Exit (software process developed by IBM)
Quality ensured by:
Test, WP Review, Audit, PMPR

PMPR : Post Milestone Project Review

Configuration Managment:
Configurable item

Baseline : Frozen work product

Levels of Control
Formal Control: Should go thru Change Management
WP delivered to client

Managed & Controlled:
Internal Project documentation

The designations in the approach are defined as follows:

* Responsible: those solely and directly accountable for creating a work product
* Approving: the party (or parties) that reviews and assures the work product's quality
* Supporting: individuals or groups who help to create the work product
* Informed: those who are to be kept informed about proceedings
* Consulted: those who help design the product or put in place quality review criteria

X axis - Roles
Y Axis - Activities

Helps on defining goals
Goal - Question - Metrics

Action Oriented / Aggressive yet attainable
Relevant to Clients expectation / Relevant
Time bound

CoQ - Cost of Quality
The "cost of quality" isn't the price of creating a quality product or service. It's the cost of NOT creating a quality product or service.

Root cause analysis
Cause and Effect / Fishbone / Ishikawa Diagram
5 Whys
5 W & 2 H

Primitive Metrics are basic data
1. Size
Measure how big the application / Product is.
Using of
Function Point - FP
Lines of Code - LOC
Pages of Documentation - POD

2. Effort
is the mo.of labor hours expended to complete project and production support tasks.
helps on measuring the Productivity
helps determining Staff needed for a project
the effort needed depends on the size of the task, complexity, experience, and organization tools and processes.
Effort drives cost
Analysing of efforts involves Time Tracking
Project effort is not the same as billable effort - metric collection and billing have difference objectives
Time tracking ( SAP, PIV, MS Project) accounts for Project time, production support time and administrative time

3. Staff
is the no.of people assigned to a project for a given reporting period
Peak Staff:
The total count of individual who are allocated to and worked on a project during report period.
Peak staff must be a whole number
Count any person assigned to more than one project as one person for any and all of the projects which he or she is assigned.

FTE ( Full Time Equivalent)
a measure of the resources (people) who are allocated during a month.

4. Duration
Duration is the number of calendar days from the defined measurement ‘start date’ of the project to the project measurement ‘end date,’ less any hold time, defined in number of calendar days

5. Defects
A defect is a flaw in the product, system, or service that fails to perform its required functions
helps on assessing Quality & Reliablity
Pre-Release Defect

6. Change
Change is the measure of volatility of a system/application and of project requirements

7. Computer Resources
computing requirement for use by a project in the development of a system or to support the production environment.

Derived metrics form the Basic Primitive data
1. Productivity
2. Delivery Rate
3. Defect Density
4 Estimate Accuracy

Defect & Rework

Standard defect-type classification
Requirement Error
Design Error
Coding Error
Testing Error
Third party Error
Documentation Error
H/W or Systems Software
Human Intervention Error
Project Management Error
Process/ Standard Error

Severity of defects
1 - Catastrophic
2 - Major ( without workaround)
3 - Major ( with workaround)
4 - Minor
5 - Cosmetic
6 - Internal

Service Performance Indicators (SPI)

Information Engineering Metrics Standards and Guidelines (S&G)

Production Support
Safe & Escape

Differnce between Verification & Validation

Verification ensures that you are building a product according to its requirements, specifications, and standards. For Verification, you should ask the following questions:
Are you meeting the specified requirements?
Are you building the product right?

Verification is CORRECTNESS.
performed by the developers on not on intended environment

Validation ensures that your product will be usable once it is in its intended environment. For Validation, you should ask the following questions:
Are you meeting the operational need?
Does this product meet its intended use in the intended environment?
Are you building the right product?
performed by the end users on intended environment
Validation is TRUTH.

Example: UAT

TRUTH is something absolute which cannot be interpretted differently.
Correctness is relative to the system as presented.

When prototypes are developed to ensure that specific requirements can be addressed, it is an example of a verification practice. When end users are asked to evaluate prototypes to ensure that the product will meet their needs, it is an example of a validation practice.

Earned Value Management
Basic on Earned Value Management

CMMi Level

Objective of High Maturity Model
Control: Understand and control process varaince
Predict: Predict Performance from process capability
Improve: Constantly improve process capability

Saturday, July 12, 2008

SQL Server Performance baseline

1 Find Objects using Cursors

SELECT object_name(id) FROM syscomments

2. Find views using Views

Select * from sysComments where text like '%from v%' and type = 'V'

3. TempDB Usage History at instance level
SELECT getdate(),
SUM(user_object_reserved_page_count) * 8 as user_objects_kb,
SUM(internal_object_reserved_page_count) * 8 as internal_objects_kb,
SUM(version_store_reserved_page_count) * 8 as version_store_kb,
SUM(unallocated_extent_page_count) * 8 as freespace_kb
FROM sys.dm_db_file_Space_Usage
where database_id = 2

4. TempDB Usage History at object level

obj.objectid as [Object_Id],
getdate() as SampleDateTime,
sum(tmp.user_objects_alloc_page_count) AS user_objects_alloc_page_count,
sum(tmp.user_objects_dealloc_page_count) AS user_objects_dealloc_page_count,
sum(tmp.internal_objects_alloc_page_count) AS internal_objects_alloc_page_count,
sum(tmp.internal_objects_dealloc_page_count) AS internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage AS tmp
LEFT OUTER JOIN sys.dm_exec_requests AS req
ON tmp.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) AS obj
WHERE tmp.session_id > 50
AND obj.objectid is not null
Group BY obj.ObjectId

5. Object Block history
SELECT s.object_id
, SampleDateTime = getdate()
,, i.index_id
, [block_pct]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
, row_lock_wait_ms = row_lock_wait_in_ms
, [avg_row_lock_waits_ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) s
,sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
And row_lock_wait_count <> 0

6. CPU Usage History
objectid as [Object_Id]
, getdate() As SampleDateTime
, total_cpu_time
, total_execution_count
, number_of_statements
, sum(qs.total_worker_time) as total_cpu_time
, sum(qs.execution_count) as total_execution_count
, count(*) as number_of_statements
sys.dm_exec_query_stats qs
GROUP BY qs.sql_handle ) dt
Cross Apply sys.dm_exec_sql_text(dt.sql_handle) eqp
WHERE ObjectId is not null

7. I/O Usage History
Select ObjectId As [Object_ID], GetDate() as SampleDateTime
, (total_logical_reads/Cast(execution_count as Decimal(38,16))) as avg_logical_reads
, (total_logical_writes/Cast(execution_count as Decimal(38,16))) as avg_logical_writes
, (total_physical_reads/Cast(execution_count as Decimal(38,16))) as avg_physical_reads
, Execution_Count
from (
Sum(total_logical_reads) as total_logical_reads
, Sum(total_logical_writes) as total_logical_writes
, Sum(total_physical_reads) as total_physical_reads
, Sum(execution_count) as execution_count
, sh.objectid
from sys.dm_exec_query_stats
Cross Apply sys.dm_exec_sql_text(sql_Handle) sh
Where objectId is not null
Group By objectid
) SubQry

8. Table Data Growth volume History
@END_DATE datetime

SET @START_DATE = getdate()
SET @END_DATE = getdate()

SET @END_DATE = Convert(varchar(10), DateAdd(day, 1, @END_DATE), 101)

SELECT startdt.object_id, object_name(startdt.object_id) as [Object_Name], startdt.[RowCount] as Starting_Row_Count,
enddt.[RowCount] as Ending_Row_Count,
pct_Growth = case when startdt.[rowcount] = 0 then 0
else (enddt.[rowCount] - startdt.[rowCount])/Convert(Decimal(38, 16), startdt.[rowcount])
FROM Benchmark_Table_DataVolumeHistory startdt
Select object_id, min(SampleDateTime) as minSampleDateTime,
max(SampleDateTime) As maxSampleDateTime
FROM Benchmark_Table_DataVolumeHistory
WHERE SampleDateTime >= Convert(varchar(10), Getdate(), 101)
And SampleDateTime < @END_DATE
GROUP BY object_Id
) sub
ON startdt.object_id = sub.object_id
and startdt.SampleDateTime = minSampleDateTime
INNER JOIN BenchMark_Table_DataVolumeHistory enddt
ON enddt.object_id = sub.object_id
and enddt.SampleDateTime = maxSampleDateTime

SELECT UseCaseDesc, startdt.object_id, object_name(startdt.object_id) as [Object_Name], startdt.[RowCount] as Starting_Row_Count,
enddt.[RowCount] as Ending_Row_Count,
pct_Growth = case when startdt.[rowcount] = 0 then 0
else (enddt.[rowCount] - startdt.[rowCount])/Convert(Decimal(38, 16), startdt.[rowcount])
FROM Benchmark_Table_DataVolumeHistory startdt
Select uc.UseCaseDesc, hist.object_id, min(SampleDateTime) as minSampleDateTime,
max(SampleDateTime) As maxSampleDateTime
FROM Benchmark_Table_DataVolumeHistory hist
INNER JOIN Benchmark_useCase_Object_assn ua
ON hist.object_id = ua.object_id
inner join benchmark_usecase uc
on ua.usecaseid = uc.usecaseid
WHERE SampleDateTime >= Convert(varchar(10), Getdate(), 101)
And SampleDateTime < @END_DATE
GROUP BY hist.object_Id, usecasedesc
) sub
ON startdt.object_id = sub.object_id
and startdt.SampleDateTime = minSampleDateTime
INNER JOIN BenchMark_Table_DataVolumeHistory enddt
ON enddt.object_id = sub.object_id
and enddt.SampleDateTime = maxSampleDateTime

Friday, July 11, 2008

MOC Client Conversation Logger

MOC Client Conversation Logger can be downloaded from

Tuesday, July 08, 2008

Hyber Threading - HT

A thread is a unit of execution that runs on a CPU that can only execute one thread at a time. However, a CPU is very good at switching between threads very quickly to share its power, giving the illusion of simultaneous execution.

Hyper-Threading is a technology found only on Intel processors, that tries to make more efficient use of the CPU by duplicating the architectural state to provide two logical CPUs.
This allows more than 1 thread to be scheduled and enables simultaneous execution when the threads require different processor functions. The effect of this is that you will see two logical CPUs to which the operating system can assign different threads. However, it is only the architectural state that is duplicated, not the physical execution engine. Figure shows a 4 dual-core system with Hyper-Threading enabled, giving 16 logical processors.

Hyper-Threading is only ever going to give you a maximum of 1.3 times nonHyper-Threaded performance and in practice it will be closer to 1.1 to 1.15 times. This knowledge helps put any decision about enabling it back into perspective.

Disable Hyper-Threading unless you have the time to test it thoroughly. Don’t use it on Windows 2000 as it’s not Hyper-Threading aware.

SQL Server license Sacket level not at core CPU

Multi-core effectively means more than one CPU on the same chip so when you buy a dual-core processor you’re actually buying two CPU cores, Windows will see two CPUs, and you should treat it for capacity planning purposes like two single-core CPUs.

It gets even better from a licensing perspective because Microsoft per-processor licensing is per socket, not per core. Socket refers to the physical socket that you plug the processor into.

This licensing model gives Microsoft a competitive advantage over its competitors in the database market, where others charge customers a license fee per core. This is great news for SQL Server customers because a server with four dual-core CPUs will perform comparably with an eight single-core server but at half the license cost.
Image from book

Multi-core processors are only licensed per socket so if you can get an 8-core processor you’ll only need a Windows and SQL Server license for 1 socket!

Monday, July 07, 2008

Setting Unrestricted growth

On changing the log file growth to unrestricted. Using the management studio UI, simply clicking on the properties of the DB and then files and then change the option to unrestricted growth on the log file and hitting ok and check the properties, it flips back to unrestricted

Database file was created with the maxsize defined, we altered the database and change the maxsize to unlimited. But afterwards it reverted to the previous max size.

It is by design as advised on


Specifies that the file grows until the disk is full. In SQL Server 2005, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.


To get this 'unrestricted' radio button enabled for the log file growth again is to;

- backup db
- detach db
- rename log file to something xx_old.ldf
- attach db again using only the MDF file again and letting SQL create a new log file for you
- delete xx_old.ldf file if all successful

Sunday, July 06, 2008

Free SQL Server tools that might make your life a little easier

check out on below link for free SQL Server tools that might make your life a little easier

Wednesday, July 02, 2008

Verify the TCP Port

Verify the server is listening on the correct port (1433 in my case). Do this by running the following command in a command prompt window:

netstat -ano | findstr 1433

Terminal Citrix Server Connection issue

1. On Run type “regedit”
2. Go to Registry entry “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSLicensing\Store”
3. Delete the LICENSE001
4. Try connecting on Citrix Terminal Server
5. Let me know if it works.

Tuesday, July 01, 2008

Missing performance counters X64 OS with SQL2000 and SQL 2005

If you’re using x64 Windows and x64 SQL Server you shouldn’t have any issues. System Monitor will by default have the correct counters loaded and available for you to view and the same applies to IA64 Windows and IA64 SQL Server deployments.

However, if you’re using SQL Server in a Windows-On-Windows (WOW) mode on x64 (which is to say that x64 Windows is emulating a 32-bit environment to facilitate you running an x86 instance of SQL Server), When you start System Monitor on the x64 host, you’ll find that none of the counters relating to the 32-bit SQL Server instance are available. This is be cause on an x64 host you’ll be running an x64 version of System Monitor by default.

In the logfile I overlooked Event ID 1021:

Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a 64-bit environment. Contact the file vendor to obtain a 64-bit version. Alternatively, you can open the 32-bit extensible counter DLL by using the 32-bit version of Performance Monitor. To use this tool, open the Windows folder, open the Syswow64 folder, and then start Perfmon.exe.

Start C:\WINDOWS\SysWOW64\Perfmon.exe, this is the 32-bit version, and all the counters are there.

Monitoring the 32-bit SQL Server instance in the scenario will require running a 32-bit instance of System Monitor, and this can be achieved by running the following

mmc /32perfmon.msc

Once the 32-bit MMC console has started with the Perfmon snap-in, the 32-bit SQL Server counters should be available for addition to your log.


There are two types of dynamic management views and functions:

* Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
* Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission

Dynamic management views and functions have been organized into the following categories.

1. Common Language Runtime Related Dynamic Management Views

2. I/O Related Dynamic Management Views and Functions

3. Database Mirroring Related Dynamic Management Views

4. Query Notifications Related Dynamic Management Views

5. Database Related Dynamic Management Views

6. Replication Related Dynamic Management Views

7. Execution Related Dynamic Management Views and Functions

8. Service Broker Related Dynamic Management Views

9. Full-Text Search Related Dynamic Management Views

10. SQL Server Operating System Related Dynamic Management Views

11. Index Related Dynamic Management Views and Functions

12. Transaction Related Dynamic Management Views and Functions

Excerpt from

DMV Scripts are on Scripts Repository:

Monday, June 30, 2008

Isolation Level

The phenomena that are allowed at each isolation level in SQL Server 2000

Friday, June 27, 2008

Getting Detail of SQL Server from Registry

From a Command prompt you can run the following REG instructions

To Get remote server details:
- Add \\servername\... before HKLM to get remote machine details

reg query HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion /v CurrentVersion

For SQL Server 2000
reg query "HKLM\Software\Microsoft\Microsoft SQL Server" /v InstalledInstances

reg query "HKLM\Software\Microsoft\Microsoft SQL Server\80\MSSQLLicenseInfo\MSSQL8.00" /v ConcurrentLimit

-- For each instance, replace MSSQLServer with 80\InstanceName from in the following
reg query HKLM\Software\Microsoft\MSSQLServer\Setup /v Edition
reg query HKLM\Software\Microsoft\MSSQLServer\Cluster /v ClusterName
reg query HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion /v CSDVersion
reg query HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp /v TcpPort

----- Using Registy Server Listing ---
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\80\Tools\Client\PrefServers" ServerN (where N represents the server number )

reg query "HKLM\Software\Microsoft\Microsoft SQL Server\80\Tools\Client\PrefServers" UserN (where N represents the default login corresponding the ServerN )

For SQL Server 2005
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names"
- Subkeys represent a different SQL Server Product (SQL=SQL Server, RS=Reporting Server, etc)

- Replace MSSQL.1 with each of the keys from "Inteance Names" on below
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Setup" /v Edition
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\CurrentVersion" /v CurrentVersion
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" /v TcpPort

Tuesday, June 24, 2008

Troubleshooting SQL Server 2005/2008 Performance and Scalability Flowchart

Flowchart on Troubleshooting SQL Server 2005/2008 Performance and Scalability Flowchart is available on SQL Server Customer Advisory Team ( SQL CAT ) site.

Monday, June 23, 2008

5 Rules of Data Normalization - poster

This is the place to obtain a copy of the aging but still popular "5 Rules of Data Normalization" poster.

The poster was written in 1989 by Marc Rettig as a subscription premium for Database Programming and Design magazine.

Sunday, June 15, 2008

What not to do as a DBA

Here are some tips for what not to do as DBA – Newbie or not!

1. Rebuild an index in working hours
This will hit disk I/O very heavily. It is rarely useful to do this during normal working hours, so always schedule it for the evening or overnight - that is, during the period of lowest user activity. Difficult if you work in a 24/7 environment like myself. Look for maintenance window !

2. Stopping the database engine without warning
Why? Lots of frustrated users and a telephone (helpdesk or yours) that won't stop ringing. Trust me I’ve been here – inadvertently shutting down database servers without informing the application users is a cardinal sin!

3. Performing a service pack upgrade during working hours
Usually this involves re-starting the core database engine. Don't do it, again it'll annoy many people. And besides that’s what overtime is for. Follow the Change control !

4. Running test queries against live servers
Do you really know how long they'll run for or how much disk I/O they will demand? I thought not! Again, past experience has led me to be extremely cautious in this department. Test, test out on Development/ Test environment before hitting on Production always !

5. Being arrogant towards other teams in the department
Why? A few can be a complete pain, but explaining to them the issues and trying to work towards a good working compromise is usually more productive than treating them unsympathetically. Likewise the helpdesk guys - you need each other. Foster good relationships with your work colleagues, in the long term it pays dividends.

6. Backing up during working hours
Again it's all about disk I/O. It serves the backup or it serves your users. If you have to do it, look at differentials or transaction log backups: they take less time and reduce dropped connections as a result. I’ve recently had to change from full to differential backups because of space restrictions too. Alternatively, monitor the server and talk to your users/tape guys about the best time to perform one - but only if you really have to.

7. Executing patches/updates against live data
You are kidding, right? At the very least, test it against a live copy, and backup the live database before you apply it. And if possible have a regression script which will allow you to back out the updates if reverting to a backup is not possible. In my experience when users say they have confirmed and tested everything is ok – they haven’t really, give it 3/4 days later – they’ll spot something else!

8. Not securing your database servers
Get clued up on security. Your organisation may have its own Information Security Officers like mine; define user roles and password retention policies etc

To find the DBCC execution progress

To find the DBCC execution progress;

select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, * from sys.dm_exec_requests

Wednesday, June 11, 2008

SLA - Service Level Agreement for SQL Server 2005

Do you know what an SLA is?

SLA = Service Level Agreement. SLAs are agreements between you and your customers. If you're a DBA, then your customer is typically the company for whom you work. Examples of SLAs are:

1. In the event of a corruption, or other disaster, the maximum amount of data loss is the last 15 minutes of transactions.
2. In the event of a corruption, or other disaster, the maximum amount of downtime the application can tolerate is 20 minutes.

Usually, it's a combination of SLAs such as those above.

Do you know why SLAs are important?

Here's the catch - an SLA is really more than just an agreement between you and your customers - it's more like a contract that you're obligated to meet. This means that if you're a DBA with zero-downtime and zero-data loss SLAs, you need to make sure that in the event of a corruption you can actually meet those SLAs. The obvious thing is that if the SLAs cannot be met then the business will suffer downtime and data loss. The not so obvious thing is that if you're the one who agreed to the SLAs in the first place, and when the disaster strikes, the capabilities of the system are far below the SLA's requirements, then you could lose your job - resume/CV time - I've heard of it happening...

Do you know your SLAs?

You have to know what your SLAs are so you can make sure the system can meet them. Several DBAs I discussed this with don't know what their business' SLAs are, even though they are responsible for making sure they are met. I find this astounding - how can you sign up for meeting an SLA when you don't know that the SLA is? Especially if failing to meet the SLA could lead to resume/CV time...

Do you think you can meet your SLAs?

The other reason to know your SLAs, of course, is so that you can correctly architect your system to meet them. There are a bunch of technologies you can use and strategies you can employ to work towards meeting your SLAs (well beyond the scope of this blog post but will be covered through the year). If you find that you can't meet your SLAs, you need to push-back on your management - otherwise you're setting yourself up for trouble when a disaster occurs and you can't meet the SLAs - you'll be held responsible.

Do you know you can meet your SLAs?

Your disaster recovery plan looks great on paper - but have you actually tried it? I know of one company that has a 15 minute downtime SLA for a 300+GB database but the DBA is relying on clusters to provide that for him. That won't work if the database is corrupt (remember a failover cluster has a single point of failure in its shared-nothing configuration - the disks) and needs to be restored from the last full backup... Another company I know of relies on database mirroring to failover in the event of a disaster but has never tried it to see if their application fails over gracefully... You have to make sure you've practiced recovering from a disaster before the first real disaster happens - you'll be amazed at the little things that are discovered (e.g. if the on-site backups are bad, how long will it take to get the offsite copies brought in-house from the off-site location 100 miles away? Can you still meet your 15 minute downtime SLA in that case?)

SQL Server Specific on SLA

Hours of Operation

1. Hours that the partition/table/database is available to users. The SLAs may be different for various parts of the database, depending on what applications need access to them. Why is it important to differentiate at various granularities? For example, depending on how the unit of data is used, it may require different maintenance than other data and so knowing the availability SLA allows maintenance downtime to be planned.
2. Hours reserved for planned downtime. Again, this may differ at various granularities of data.
3. Amount of advance notice for extended downtime or other changes that affect users. For instance, when my bank upgraded its computer system last year, they gave a series of warnings over the preceeding few months so that people weren't surprised.

Service Availability

1. Percentage of time SQL Server service is running and able to service connections.
2. Percentage of time a particular partition/table/database is available for use (i.e. not exclusively locked for maintenance or restore).

System Performance

1. Number of concurrent users the system supports.
2. Number of transactions supported per unit of time.
3. Acceptable level of performance, such as latency experienced by users for a variety of operations.
4. Minimum time for an update to be replicated to various remote sites.

Disaster Recovery

1. Time allowed for recovery from each type of failure (e.g. accidental data deletion, database corruption, SQL Server crash, OS crash, server failure, site failure).
2. Time it takes to bring critical data online (e.g. the read/write partitions of a sales database) such that operations can continue and less critical data can be recovered later.
3. Time taken to recover data to the point of failure.
4. Maximum acceptable data/transaction/work loss for various kinds of failures.
5. Maximum time for application failover to a remote server/site.


1. Methods available for application users to get help.
2. Maximum response/resolution time from a DBA to respond to various types of problems.


1. Maximum amount of space for user tables/databases.
2. Amount of users in specific roles.

Some other things to consider are how you define an SLA - for example, in transactions per second or in commit latency that users experience - and the interplay between SLAs - for example, the commit latency SLA may be affected if the acceptable data loss SLA is zero and a solution such as synchronous database mirroring or remote SAN mirroring is used.

Bottom line is that although it can be simple to quickly define and announce a set of SLAs for a given application, its very difficult to make sure that each is palatable to all involved, guarantee that each can be met, and allow easy diagnosis of the system to work out which component is failing when an SLA is not met. SLAs really need to be defined while a sytem is being designed as retro-fitting SLAs after-the-fact can be very time-consuming and costly.

Tuesday, June 10, 2008

SQL Server 2005 Performance Statistics Script

PSS SQL Server Engineers : SQL Server 2005 Performance Statistics Script

"Perf Stats Script", since it's useful for troubleshooting lots of issues in addition to blocking. The script takes advantage of the extremely rich set of data exposed by the new Dynamic Management Views in SQL Server 2005.

Download the script from

Monday, June 09, 2008

SAN SRDF based Replication

The replication mode is "Synchronous". This means that when the host server commits a write to a SAN disk, that bit of data will be written to the DR array in Norwest first, the DR array then sends an acknowledgement back to the production array and that bit of data will be written to the prod array when it receives that acknowledgement from the DR side. Meaning that the data between the R1 (prod) and R2 (DR) devices are always in-sync when the SRDF mirrors are established.
• When the SRDF mirror/replication is established, the process write-disables the target devices on the DR array in Norwest, so your DR server will not be able to access the DR SAN devices while that is on. If you want to make the DR LUNs available to the DR host, we will have to split the SRDF mirror for that set of servers.
• Zero data loss? As long as the data has been committed to the SAN disks, it should be covered. As for data that might still be sitting in the server cache but have NOT been flushed out/committed to SAN disk, well, the SAN storage has no control over data that has not been written to it.

Thursday, May 08, 2008

SQL Server 2005 Installation takes longer time

SQL Server 2005 Installation takes longer time

KB Article Number(s): 910070, 917872, 918105, 919357
Language: English
Platform: x64
Location: (
Password: $@fBYZW
Password Changes On: 04/10/2008
Next Password: eXzd@+RVK

Friday, April 11, 2008

Which stored procedure is executing most in the database?

How to list all the columns in the database which are used as identity key in my database?

How to list all the columns in the database which are used as identity key in my database?

Answer: Run following query in query editor.

USE AdventureWorks


SELECT SCHEMA_NAME(schema_id) AS schema_name, AS table_name, AS column_name

FROM sys.tables AS t

JOIN sys.identity_columns c

ORDER BY schema_name, table_name;


Sending Email without a Client

Sending Email without a Client

Sending e-mail is typically done from an e-mail client such as Microsoft Outlook. There may be times when you wish you could send an e-mail message without needing a client application.

Thankfully there are a number of ways to accomplish this task.

1) Troubleshooting SMTP can be done via telnet,

2) you can send e-mail from a VBS script or using ASP code,

3) There are tools like MAPISend and Blat that allow you to send email from the command line or a batch file
MAPISend.exe is a command line tool that is available from the Exchange Server 2000 Resource Kit.
You can use MAPISend to connect to a mailbox on your Exchange server and send an email message.
There are a few basic switches you need to know in order to make it work.

* -u is used to specify the profile

* -r specifies the recipients e-mail address

* -s specifies the subject of the e-mail message

* -m specifies the body of the e-mail message

In order for MAPISend to work, a MAPI profile must exist on the computer that the e-mail message is being sent from.

Let’s look at an example of the MAPISend command.

C:\&gt; MAPISend –u “Exchange Profile” –r FFlintstone@thelazyadmin.lab –s “Interested in Bowling Tonight?” –m “Hi Fred Are you interested in bowling tonight? Barney”

Pretty simple isn’t it?

This command can be very useful if you wish to send a message once a batch job has completed running. The message can be sent anywhere. I use this command to send me an e-mail to the SMS address of my Smartphone when certain automated tasks are complete.

If you do not have a copy of the Exchange Server 2000 Resource Kit there is another option called Blat.
Blat is a free, command line, SMTP mailer application. It is very similar to MAPISend; however it does not require a mail profile to be present on the sending computer.

The important command line switches you need to know are:

* -t specifies the sender’s e-mail address

* -r specifies the recipient’s e-mail address

* -subject specifies the subject

The only big difference between MAPISend and Blat is the body of a message sent with Blat is pulled from a TXT file.

C:\&gt; Blat.exe body.txt –t fflintstone@thelazyadmin.lab –f brubble@thelazyadmin.lab –subject “Interested in Bowling Tonight?”

Blat is also very useful for sending messages from batch files to SMS addresses or just regular e-mail addresses.

Wednesday, April 09, 2008

How to troubleshoot problems that occur when you use a Database Maintenance plan in SQL Server

If any task in a Database Maintenance plan fails or encounters an error
message, the maintenance plan job itself may show as failed. The Job
History Details shows the following Error message:

sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

However, this does not necessarily indicate that the Maintenance Plan
as a whole has failed.

The Maintenance Plan job shows a status of
failed in order to raise a flag to the user that a task in the
maintenance plan requires attention.


Any time a maintenance plan job fails, you must check the plan history
to see what the failure is and then take the appropriate action to
resolve the problem.

How to troubleshoot problems that occur when you use a Database Maintenance plan in SQL Server

Tuesday, April 08, 2008

Monitoring Reliability and Availability of Windows 2000-based Server Systems

Event ID 1074
Events are logged when you manually shutdown the machine using the Event Tracker.

Event ID 1076
Events are logged when the machine shuts down unexpectedly and the Event Tracker pops up when the Administrator (or first user with shutdown rights) logs on to the machine.

Event ID is 6006
The time of this event is approximately the time the operating system becomes unavailable to applications.

Event ID is 6008
The event is recorded when the system restarts and Windows 2000 discovers that the previous shutdown was not clean.

Event ID is 6009
The Event Log service itself is the source of this event whenever the system is started., and the Event ID is 6009.

Event ID: 4097
Dr. Watson utility records application failure events in the Windows 2000 Application Event Log as shown below.

Event ID: 4353
Service Pack installation on Windows

Event ID: 1123
The node lost communication with cluster node

Event ID: 1069
Cluster resource 'SQL Server Fulltext ' in Resource Group 'E38SQL' failed.

Viewing Shutdown Event Tracker events

As you can see in the image above, the Description indicates the reason for the shutdown, the time, the user that initiated the shutdown, as well as the comment that was typed in the Comment box.

To view previous Shutdown Event Tracker event logs, go to the Event Viewer (Start > Programs > Administrative Tools > Event Viewer or Control Panel - Administrative Tools - Event Viewer) and under the System Log, search for Information Events with ID 1074 or 1076. Double click the event to bring up the Event Properties page.

1074 Events are logged when you manually shutdown the machine using the Event Tracker.
1076 Events are logged when the machine shuts down unexpectedly and the Event Tracker pops up when the Administrator (or first user with shutdown rights) logs on to the machine.

MSI Installer issue on SQL 2005 Setup

Error Message:
The Windows Installer Service could not be accessed. This can occur if you are running Windows in safe mode, or if the Windows Installer is not correctly installed. Contact your support personnel for assistance.
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files
• SQLSetup0001_BOXNAME_SQL.log
• SQLSetup0003_BOXNAME_SQL.log

MSI (s) (68:E8) [23:38:12:124]: Product: Microsoft SQL Server 2005 (64-bit) -- Error 1719. The Windows Installer Service could not be accessed. This can occur if you are running Windows in safe mode, or if the Windows Installer is not correctly installed. Contact your support personnel for assistance.

Error message when you try to add or remove a program on a computer that is running Windows XP or Windows Server 2003: "The Windows Installer service could not be accessed"

Trap custom SQL 2005 Installation Success state

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files>find /I "comp
leted successfully" SQLSetup0007*.log > Result.txt

Sunday, April 06, 2008

How to restore cluster's share resource

1. Right-click on the desktop and select New Shortcut.

2. In
the window that appears, find the path to the dsm.exe executable
(located by default in directory C:\program
Note: If you type the path in, instead of using the Browse button, the path should be enclosed in double quotes. For example: "C:\Program Files\tivoli\tsm\baclient\dsm.exe"

you enter the path and executable in the text field, add the following
information after the closing double quotes (add a space between the
double quotes and the following):
-optfile="x:\path\to\cluster\dsm.opt" This
identifies the proper Tivoli Storage Manager cluster options file you
want to use. This example assumes that the cluster options file is
located in the folder ″x:\path\to\cluster\″ and has a filename dsm.opt.

The complete line in the text field now should look similar to the following: "C:\Program Files\tivoli\tsm\baclient\dsm.exe" -optfile="x:\path\to\cluster\ dsm.opt"

Click Next and give this shortcut a meaningful name, such as Backup-Archive GUI: Cluster Group X.

Click Finish.
A desktop icon should now be available. The properties of this icon
will show the following correct Target, as noted in step 4:
"C:\Program Files\tivoli\tsm\baclient\dsm.exe" -optfile="x:\path\to\cluster\ dsm.opt"

Saturday, April 05, 2008

Configuring SSIS to work on Named Instance

Configuring the Integration Services Service to work on named instance:

Modify the MsDtsSvrv.ini file - this is requires for SSIS for SQL2K5 named instance on SQL Server 2005.

File Location:

Changes Requried on:
1) .
Instead of . that denotes default instance Named instance should be there.

2) ..\Packages
If we want to change the file storage location.


Connetion to SSIS from client computer failed

Consider the following scenario. A server is running Microsoft SQL Server 2005 Integration Services (SSIS). From a client computer, you use SQL Server Management Studio to connect to Integration Services on the server. To connect to Integration Services on the server, you use a user account that is not a member of the Administrators group on the server. In this scenario, you receive the following error message:

Cannot connect to SSISServer
Additional information: Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
Connect to SSIS Service on machine "SSISServer" failed: Access is denied.

Refer solution at

Error message when you install SQL Server 2005

Error message when you install SQL Server 2005: "The file C:\Windows\Microsoft.NET\Framework\Meaningless_string\mscorlib.tlb could not be loaded"


Reason for the Error:
This issue occurs because the path of the Mscorlib.tlb file is incorrectly interpreted during the Setup process.

SQL Server 2005 64 Bit Standard Edition Memory

1)Do we really need AWE to get best performance on SQL 2005 x64 bit Standard Edition ?

No. AWE is not required on 64 bit operating system.

2) Can we configure AWE on SQL 2005 x64 bit Standard Edition?

Yes, if the O.S is 32 bit and memory you have is more than 4 GB.

No, if the O.S is 64 bit O.S.

3) Before configuring AWE we need to enabled Lock Pages in Memory Policy,

Can we configure this Policy with SQL Server 2005 64 bit Standard Edition?

Yes, if the O.S is 32 bit and memory you have is more than 4 GB.

SQL Server 2005 Standard edition do not support "Lock Pages in Memory" privileges on the SQL Service account.

Check on the SQL Server ERRORLOG if you get the below message
"Using locked pages for buffer pool". It confirms SQL Server make uses the "Lock Pages in Memory" privileges.


SQL Server only uses 2 GB of memory even though the AWE option is enabled

It applies only to Microsoft SQL Server 2005 Enterprise Edition

Domain Groups for Clustered Services - SQL 2005 failover cluster Setup

Microsoft KB on setting up Domain Group for SQL Server 2005 Cluster Setup.

It is prerequisite for SQL 2005 Cluster setup.

Reasons to set up domain groups
When you install a SQL Server 2005 failover cluster, SQL Server 2005 requires domain accounts to start the clustered services. The domain accounts must be added to a domain group.

When you perform a stand-alone installation of SQL Server 2005, SQL Server Setup creates local user groups and then adds the service accounts that you specify to these groups. The Setup program grants permissions for files and folders to these local user groups .

Although the Setup program can create local user groups, the local user groups are not visible to another computer in the failover cluster. When the current computer fails over to another computer, permissions that you grant to the local user groups on the current computer is not visible to another computer. Therefore, the Setup program requires that you provide a domain group that is accessible to all computers in the failover cluster. Then you must add the service account to the domain group when you install a SQL Server 2005 cluster. The Domain Groups for Clustered Services page of the SQL Server Installation Wizard will prompt you to enter the domain name and the group name for each clustered service that you are installing. The Setup program will not create local domain groups in the failover cluster. The Setup program only uses the domain group that you specify.

If you want to change your service account on a SQL Server 2005 cluster, make sure that your new service account is in the related domain group.

Thursday, March 20, 2008

How to find domain name using batch

@echo off
echo If no Domain is listed below, then you are not part of an NT Domain.
echo If so, please use your account from one of the supported NT domains,
echo Your current NT Logon is:

net config workstation | find "User"
net config workstation | find "Logon"


Monday, February 11, 2008

SQL Server 2005 Default Configuration check list

SQL Server 2005 Configuration check list

S.No Configuration option Minimum Value Maximum Value Default Value
1 Ad Hoc Distributed Queries (A) 0 1 0
2 affinity I/O mask -2147483648 2147483647 0
3 affinity64 I/O mask (A, only available on 64-bit version of SQL Server) -2147483648 2147483647
4 affinity mask (A) -2147483648 2147483647 0
5 affinity64 mask (A, only available on 64-bit version of SQL Server) -2147483648 2147483647 0
6 Agent XPs (A) 0 1 0
7 allow updates 0 1 0
8 awe enabled (A, RR) 0 1 0
9 blocked process threshold (A) 0 1 0
10 c2 audit mode (A, RR) 0 1 0
11 clr enabled 0 1 0
12 common criteria compliance enabled (A, RR) 0 1 0
13 cost threshold for parallelism (A) 0 32767 5
14 cross db ownership chaining 0 1 0
15 cursor threshold (A) -1 2147483647 -1
16 Database Mail XPs (A) 0 1 0
17 default full-text language (A) 0 2147483647 1033
18 default language 0 9999 0
19 default trace enabled (A) 0 1 1
20 disallow results from triggers (A) 0 1 0
21 fill factor (A, RR) 0 100 0
22 ft crawl bandwidth (max), see ft crawl bandwidth(A) 0 32767 100
23 ft crawl bandwidth (min), see ft crawl bandwidth(A) 0 32767 0
24 ft notify bandwidth (max), see ft notify bandwidth(A) 0 32767 100
25 ft notify bandwidth (min), see ft notify bandwidth(A) 0 32767 0
26 index create memory (A, SC) 704 2147483647 0
27 in-doubt xact resolution (A) 0 2 0
28 lightweight pooling (A, RR) 0 1 0
29 locks (A, RR, SC) 5000 2147483647 0
30 max degree of parallelism (A) 0 64 0
31 max full-text crawl range (A) 0 256 4
32 max server memory (A, SC) 16 2147483647 2147483647
33 max text repl size 0 2147483647 65536
34 max worker threads (A, RR) 128 32767 0
35 media retention (A, RR) 0 365 0
36 min memory per query (A) 512 2147483647 1024
37 min server memory (A, SC) 2147483647 8
38 nested triggers 0 1
39 network packet size (A) 512 32767 4096
40 Ole Automation Procedures (A) 0 1 0
41 open objects (A, RR, obsolete) 0 2147483647 0
42 PH_timeout (A) 1 3600 60
43 precompute rank (A) 0 1 0
44 priority boost (A, RR) 0 1 0
45 query governor cost limit (A) 0 2147483647 0
46 query wait (A) -1 2147483647 -1
47 recovery interval (A, SC) 0 32767 0
48 remote access (RR) 0 1 1
49 remote admin connections 0 1 0
50 remote login timeout 0 2147483647 20
51 remote proc trans 0 1 0
52 remote query timeout 0 2147483647 600
53 Replication XPs Option (A) 0 1 0
54 scan for startup procs (A, RR) 0 1 0
55 server trigger recursion 0 1 1
56 set working set size (A, RR, obsolete) 0 1 0
57 show advanced options 0 1 0
58 SMO and DMO XPs (A) 0 1 1
59 SQL Mail XPs (A) 0 1 0
60 transform noise words (A) 0 1 0
61 two digit year cutoff (A) 1753 9999 2049
62 user connections (A, RR, SC) 0 32767 0
63 User Instance Timeout (A, only appears in SQL Server 2005 Express Edition) 5 65535 60
64 user options 0 32767 0
65 Web Assistant Procedures (A) 0 1 0
66 xp_cmdshell (A) 0 1 0