Author Archives: Amit Banerjee

Initial Data Collection Script

Posted on by

Where would you be able to use it?

The cases where you can use this script effectively are:

1. Performance issues where a quick initial look at the general state of the server is very valuable.

2. Situations where issue is actually happening right now. However, that said, it is always good to collect post mortem data as well for further diagnostics. Performance Dashboard is a good option here but if you want a snapshot of the server and store it somewhere, then this script can help you.

3. Situations where the problem is happening and you need to quickly verify that you are not hitting some known issues.

How is this different from the other scripts that are already existing?

There are scripts already out there which do the same thing. The advantage here is the fact that the output is in HTML format with bookmarks which makes it easy to determine what information is available. Also, it is easy to get to the sections that are of interest in the current scenario.

Usage

  1. Download the script using the link given at the bottom of the page and save it to a file named INITIAL_DIAG.SQL. Open the script file in a SSMS Query Window.
  2. Press CTRL+SHIFT+F so that the output results are put into a file. Doing this will not produce a message or any other notification.
  3. Execute the script and specify INITIAL_DIAG.html as the output file name so that we can get the output in the require HTML format.

Sample Output

********************************************************************************************
INITIAL MICROSOFT PSS DATA COLLECTION
********************************************************************************************

INDEX
1. General Server Configuration
a. Server Info
b. Non-default sp_configure settings
c. Server Settings
d. Active Trace Flags
e. Profiler Trace Information
2. Memory Diagnostics
a.DBCC MEMORYSTATUS output
b.Memory Clerks rollup output
3.Procedure Cache Statistics
4.Database Diagnostics
a.Replicated Databases
b.Database File Usage Stats
c.Index Statistics
d.sp_helpdb output
e.DBCC SQLPERF (LOGSPACE)
f.Hypothetical Indexes
5.Missing Indexes report
6.Blocking Information
7.Batch/Query Statistics

########################################

****************** SERVER INFO *********
########################################

—————————————————————–
Microsoft SQL Server 2005 – 9.00.3257.00 (X64)
Jun 12 2008 16:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

—————————————————————
SQL SERVER NAME: SERVER1

CLUSTERED: No

MACHINE NAME: SERVER1
NET BIOS NAME: SERVER1

SQL PID: 5076
FTS Installed: YES
Security Mode: MIXED
Current SPID: 51

Click here to download the script.

Modification: June 20, 2012: Added additional code to account for the following:

  • Added code to identify read-write workload ratios
  • Added code to find out non-Microsoft DLLs loaded in SQL address space using sys.dm_os_loaded_modules
  • Added code to get output for SQL Server 2012 instances

How to troubleshoot Windows Internal Database setup issues

Posted on by

Windows Internal Database is bundled along with Windows applications that need to use a backend database solely for their application purpose. Eg: WSUS, Sharepoint.

The application’s installation files will call the MSI package for installing the Windows Installer Database. In case the application setup fails due to a failure in installing the Windows Internal Database, then you would need the MSI log file for the WID MSI package. This can be found normally in the <System Drive:>\Windows folder and would have the following naming convention: *WSSEE*.log. If you are unable to locate the file, then find out all *.log files in the folder which were created recently.

Once you have located the file, you would need to search for the following string “Return Value 3” in the file.

Eg:

MSI (s) (E0:88) [16:02:14:252]: Product: Windows Internal Database — Error 1402. Could not open key: <Reg Key Name>.  System error 5.  Verify that you have sufficient access to that key, or contact your support personnel.

Error 1402. Could not open key: <Reg Key Name> System error 5.  Verify that you have sufficient access to that key, or contact your support personnel.
Action ended 16:02:14: InstallFinalize. Return value 3.

If you check what Operating System error 5 stands for, you will find out that it is a permissions issue.

C:\>net helpmsg 5

Access is denied.

Based on the error message, you will have to take corrective actions and re-run the setup.

Microsoft Virtualization Support Policy

Posted on by

We do work on a lot of environments which have VMWare Virtualization. The first thing that needs to be done is to find the version of the ESX in use in the environment.

VMware ESX Update

Build Number

ESX 3.5.0 Update 1

64607

ESX 3.5.0 Update 2

110268

ESX 3.5.0 Update 3

123630

ESX 3i (3.5.0) Update 3

123629

If you trying to seek Microsoft CSS support for a non-Microsoft virtualized environment, then you need to be aware of the Virtualization Support Policy:

http://support.microsoft.com/kb/897615

Also if you want to find out if your virtualized environment is supported, then you can use the Support Policy Wizard.

Here is link for a step-by-step guide to find out the ESX versions and builds:

http://www.techhead.co.uk/how-to-determine-the-vmware-esx-or-esxi-build-version

Reference articles:

Determining detailed build number information for VMware ESX 4.0.x hosts

Determining detailed build number information for VMware ESX 3.0.x and 3.5.x hosts

VDI application throwing back HEX – 0x080770007

Posted on by

While taking a VDI backup of a SQL Server database, you get a VDI error with the following HEX code 0x080770007.

If you used simple.exe to perform the backup, then you would get the following error:

C:\TEMP>simple.exe B <SERVERNAME>\<INSTANCENAME> testdb
Connected to Server: <SERVERNAME>\<INSTANCENAME>
Backing up database: testdb
Performing a BACKUP using a virtual device.
VDS::Create fails: x80770007

If you lookup the 0x080770007 (VD_E_INSTANCE_NAME VD_ERROR) translates to failed to recognize the SQL Server instance name.

Then check if the following condition holds true:
There is no DEFAULT instance of SQL Server on the machine where you are trying to take a VDI backup and the SQL instance that you are connecting to perform a backup is a named instance. If above condition is true, then the issue is with CreateEx function of the interface IClientVirtualDeviceSet2. The CreateEx function is used to create the virtual device set and has the following syntax:

HRESULT IClientVirtualDeviceSet2::CreateEx (
LPCWSTR lpInstanceName,
LPCWSTR lpName,
VDConfig* pCfg);

The "lpInstanceName" parameter identifies the SQL Server instance to which the SQL command needs to be sent to. If the CreateEx method has NULL as the first parameter, then it would always connect to the Default instance. If the server doesn’t have a default SQL instance, then the first parameter needs to be provided with the instance name.
Eg. If you have a named instance on the server as "SERVER1\SQLINST1, then the first parameter for CreateEx should be "SQLINST1".

Another issue that can cause this error is when you use simple.exe to take a backup from an instance that is not installed on the box from which you are running simple.exe from. This can be true for other backup applications as well.

The reason for this is that simple.exe or any backup application using SQLVDI.DLL calls, you get the interface to the device set using the following piece of code. Eg: For simple.exe:

hr = CoCreateInstance (
        CLSID_MSSQL_ClientVirtualDeviceSet, 
        NULL,
        CLSCTX_INPROC_SERVER,
        IID_IClientVirtualDeviceSet2,
        (void**)&vds);

CoCreateInstance is used when you want to create only one object on the local system. If you planning to take a backup from a remote instance and using code similar to the one used in simple.exe, then you need to use CoCreateInstanceEx since the interface set would reside on the remote computer.

Another issue is that when we call CreateEx to create the virtual device set, we can only pass in the instance name and not the computer name. So, the backup application’s code should be designed in a correct manner for this to work which means .

Most known vendor applications Legato, NetBackup, Tivoli, LiteSpeed, SQLSafe etc. take care of these considerations!

Links: Modified simple.exe application code

Quick facts about applying updates on a SQL Cluster

Posted on by

When a SQL Server 2005 Service Pack is applied on a clustered SQL instance (SQL 2005 and above), the Service Pack/Cumulative Update/Hotfix will upgrade all the member nodes, if a cluster-aware component is selected on the component selection screen. There is no need to run the SP installation after failing over the SQL resources to the passive nodes and re-applying the SPs. The passive nodes are upgraded using a Task Scheduler job created by the SQL setup.

As for the non-cluster aware components like SSIS, Tools etc., if you select them along with the clustered components, then the patches will be applied for the non-cluster aware components as well on the member nodes. In case, you do NOT select the non-cluster aware components while running the update for the cluster aware components, then you will manually have to patch these components.

When applying patches for non-cluster aware components, you do not need to failover the resources.

Setup takes the SQL resource OFFLINE/ONLINE on the active node. The resource is taken offline to patch the binaries and then it is brought online by SQL Setup to apply the configuration scripts. There are no failovers involved during SQL 2005 patching.

Cluster Aware components: Database Services, Analysis Services

Non-cluster Aware components: Integration Services, Reporting Services, Tools