SQL VDI backup fails with 0x80770007

While taking a SQL Server database non-native backup using an application that calls SQLVDI.DLL, you find that the backup fails following HEX code: 0x080770007.

0x80770007 (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". A way to workaround the same for simple.cpp is mentioned here.

Technorati Tags: ,,

Collecting Server Side Profiler Traces

When we want to monitor the kind of queries that hit across your SQL Server, most of us set up a Profiler Trace. But what most of us are not aware of is that there are two ways to setup a Profiler trace:

1. A GUI based Profiler trace

2. A Server side Profiler trace

A GUI based profiler trace causes a performance impact on the server as high as 25% depending on the events being captured. So if you are monitoring SQL Server with the help of profiler traces on a long term basis then it would always be advisable to setup a server side profiler trace.

Steps to setup a Server side Profiler Trace
1. Create a Profiler Trace using the GUI and then script out the trace to a file called say sql_trace.sql. Open the SQL_TRACE.SQL file in query analyzer/management studio query window and make the following changes

2. In the line “exec @rc = sp_trace_create @TraceID output, 2, N'<save location of trace file>’, @maxfilesize, NULL”, change the location where you want to store the trace file.

3. Make sure @maxfilesize parameter is set to 350 or a desirable size. (This makes sure that you roll over to the next trace file once the current size hits 350 MB)

4. Execute the script to create the trace. This will generate a Trace ID. The trace information can be obtained from fn_trace_getinfo function

5. To stop the trace, use sp_trace_setstatus ,0

6. To close the specified trace and delete its definition from the server us sp_trace_setstatus ,2

The events that you had configured while in the GUI would be present in the generated script and will be set using the Stored Prodecure sp_trace_setevent.

Remember that server side traces shouldn’t be writing to the same disk on which the SQL files are residing on and your profiler traces shouldn’t be writing to network drives/mapped drives or any other UNC shares. The destination file location should be a local disk. These are some recommendations that you need to keep in mind when configuring a server side profiler trace.

Useful Articles

283790 INF: How to Create a SQL Server 2000 Trace

283786 How to monitor SQL Server 2000 traces

Setting up Perfmon Logs

One of best ways to monitor your system performance for disk contention, high CPU, memory crunch etc. is to use Windows Performance Monitor Logs and Alerts. These logs can be used to capture historical information about different performance objects and would help us understand what was happening on the system at that point of time. In Windows Server 2008 & Windows Vista, Perfmon has a cool new utilty (a management MMC snap-in) called Reliability Monitor which helps you a bird’s eye view of your system stability.

Steps to setup Perfmon data collection for Windows Server 2003/XP
This can be done by opening up Perfmon:

  1. Click on “Performance Logs and Alerts
  2. Click on “Counter Logs
  3. Right click on the same and click on “New Log Settings
  4. Give the log a name
  5. Click on “Add Objects” and add all the objects that are needed for your data collection
  6. Click on the “Log Files” tab
  7. You can change the log file location by clicking on the “Configure” button
  8. Click on the “Schedule” tab and then for “Start Log” and “Stop Log”, select the “Manually (Using the Shortcut menu)” option
  9. Click on the “OK” button to create the log
  10. Then right-click on the Log and click on “Start” to begin the logging
  11. Right-click on the log file and click on “Stop” to end the logging

How to setup a Perfmon data collection for Windows Server 2008/ Vista/ Windows Server 2008 R2

  1. Open up the Performance Monitor snap-in (Start -> Run -> perfmon)
  2. Expand the “Monitoring Tools” option and Right Click on “Perfomance Monitor” -> “New” -> “Data Collector Set
  3. Give the data collector set a name and click on Next
  4. Give the location where you want to save the Perfmon Logs and click on Next
  5. Here you can provide the Run As user of leave that as the Default user
  6. Select the option “Save and Close” and click on Finish
  7. Then go back to the Perfmon snap-in main window and you should see a Data Collector set with the same name that you created under Data Collector Sets -> User Defined
  8. Click on the Data Collector Set and on the right hand pane, you should see a System Monitor Log Performance Counter. Right click on it and click on properties.
    • Under the Performance Counters tab, add the relevant performance counters required for your data collection, set the log format (binary, SQL, CSV, comma separated) and the sample collection interval time.
    • Under the File tab specify the file format name and logging properties for the file.
  9. Now you can start the Perfmon data collection by right clicking on the Data Collector Set and click on Start or you could do some more work for maintaining disk space by setting up some rules using the Data Manager to create .CAB files or delete older files in case we are setting up perfmon logs for long term monitoring.

There is hardly any performance impact in collecting perfmon logs on any server. Any perfmon data collection unlike other forms of data collection don’t generate voluminous data in terms of size but contain hordes of infromation which can provide valuable insight to an issue which is impacting critical business applications running on a server.

Windows Reliability and Performance Monitor

http://technet.microsoft.com/en-us/library/cc749154.aspx

Introduction

 

I have been supporting SQL Server for some time now. One of biggest challenge that I have seen people face with troubleshooting is that they are at a loss because they don’t know where to look and what to collect.

If you know what data to collect and how to collect, that’s half your solution. Two reasons for this:
1. Too much unnecessary information can divert you in the wrong direction
2. Troubleshooting becomes much simpler with just the relevant data collection.

Nothing more, nothing less.

So, in this blog we are going to cover two aspects of maintaining SQL Serves:
1. Pro-active work which will help us minimize points of failure and narrow down to the root if a problem does occur
2. Re-active work which involves having the right approach and action plan ready in case a problem does appear

All comments are most welcome.

Two of the resources for Troubleshooting SQL Server Issues which I have found quite helpful at times are:
1. Technet Forums
2. SQL 2005 Practical Troubleshooting: The Database Engine by Ken Henderson (This is a must read. This is same author for Guru’s Guide to SQL Server. This book has inputs from the DEV team for SQL Server)
http://www.khen.com/2007/06/sql-server-2005-practical.html
http://www.amazon.com/SQL-Server-2005-Practical-Troubleshooting/dp/0321447743

I used to blog on Blogspot and Live Spaces but currently I’m in the process of migrating my blog over to WordPress!