Shrinking SQL Server Transaction Logs with SQL-DMO

• Download demo executable - 54.0 Kb
• Download project source (WTL) - 73.4 Kb
• Download old project source (MFC, not updated) - 58.8 Kb

Introduction

Workers of the one of our division departments installed an application that used MSDE as a database server. That application was constantly hanging up with no visible reason until we finally found out that the cause of such strange behavior was the too quickly growing transaction log. After digging the MSDN, I found that shrinking transaction log for MSDE/SQL Server 2000 required 2 simple commands (see KB272318):

BACKUP LOG %DB_NAME% WITH TRUNCATE_ONLY
DBCC SHRINKFILE (%LOG_NAME%, %LOG_SIZE%)

But as the workers of that department did not have the slightest idea of what MSDE is and how to run these commands with osql utility, they asked me to write a simple wizard application to ease their job. The first version I wrote used MFC, but the second one I implemented with WTL to avoid dragging the mfc dll that is 1 MB in size, and statically linking to C runtime. I could not minimize CRT use in ATL because C++ exception support was required.

So what does this wizard allow to do?

It allows to:

1. Shrink SQL Server database transaction log(s).
2. Backup database after shrinking it.
3. Create SQL Server job that automatically shrinks transaction logs when they reach their limit size.
4. See the final results, i.e. sizes of the transaction logs after shrinking.
5. Show SQL Server Enterprise Manager user interface elements as its own user interface elements.

Points of Interest

You can use this wizard as a sample for different tasks you can perform using SQL-DMO and SQL-NS. Each page of the wizard is going in a separate section and is a sample for:

Login Page

1. (SQL-DMO) Listing network available SQL Servers.
2. (SQL-DMO) Starting SQL server if it is stopped.
3. (SQL-DMO) Connecting to SQL server using either Windows or SQL Server authentication.

Database Selection Page

1. (SQL-DMO) Listing SQL Server databases, excluding system ones if needed.
2. (SQL-NS) Displaying database properties (if SQL-NS is installed).
3. (SQL-DMO) Listing SQL Server database transaction log(s).
4. (SQL-DMO) Retrieving the properties of the transaction log file(s).

Backup Page

1. Formatting backup media set properties (drag and drop is also supported).
2. (SQL-DMO) Verifying the backup media set before backup.

Shrink Page

1. (SQL-DMO) Starting Job Server (SQL Server Agent).
2. (SQL-DMO) Creating SQL Server Jobs and their steps.
3. (SQL-DMO) Creating SQL Server Alerts that trigger the jobs created.
4. (SQL-DMO) Starting SQL Server job (commented).
5. (SQL-DMO) Shrinking transaction log(s).
6. (SQL-NS) Backing up the selected database using SQL Server Enterprise Manager UI.
7. (SQL-DMO) Backing up the selected database.
8. (SQL-DMO) Handling Backup Object events (PercentComplete, Complete and NextMedia events) using event sinks. (See IBackupSink source code for more details).
9. (SQL-DMO) Verifying backup media set files after backup is complete.

Miscellaneous

1. Executing SQL Server Service Manager.
2. Executing SQL Server Enterprise Manager.
3. Trimming CRT and global heap memory.
4. Displaying COM errors.
5. Displaying Win32 errors.

Implementation

I will not go into deep details because the source code is self-explanatory. Just a few words:

This wizard uses Microsoft SQL-DMO (SQL Distributed Management Objects) and SQL-NS (SQL Namespace API) to perform its tasks. SQL-DMO allows applications written in languages that support Automation or COM to perform all functions performed by SQL Server Enterprise Manager.

SQL-NS allows applications written in languages that support Automation or COM to include parts of the SQL Server Enterprise Manager user interface in their own user interface.

See the image below. SQL-NS is layered on SQL-DMO.

Some years ago, I wrote a Visual Basic application, that converted the Microsoft Access databases to SQL Server ones using SQL-DMO (we had problems with SQL scripts and SQL Server import wizards because of cyclic relations).

I recalled that the only thing I had to do to use the SQL-DMO objects was to add a reference to its type library from the VB project. But starting with VC 5.0, we have compiler built-in COM support - just by using the #import preprocessor directive, the compiler reads a type library and generates C++ wrapper classes and smart pointers for COM interfaces. So we get the ease of VB and the power of C++. I mention that, because the majority of complaints is that almost all the samples are implemented with VB - but conversion of them to C++ is just a game. Besides, using compiler COM support allows us to use the same code both in MFC and ATL and without any framework at all (the old MFC project is also available for download). Just see the sample code.

Handling SQL-DMO events is just a bit more complicated. The SQL-DMO Backup, BulkCopy, Replication, Restore, SQLServer, and Transfer objects are connectable COM objects, supporting callback to the client application. We have to implement functions to handle callbacks from the server, called a sink. Using the IConnectionPoint interface, the client notifies the server of its ability to handle callbacks, providing its sink implementation as an argument. The client-implemented sink is a COM object. As with any COM application development task, implementing a sink for any SQL-DMO connectable object is fairly painless when using C++. The client application defines a class, inheriting from a defined SQL-DMO sink interface definition, then implements members to handle the callbacks of interest. The IBackupSink and Shrink Page (CWizPgShrink) source code illustrate implementation for a COM object that can handle backup object events (you can also use _COM_SMARTPTR_TYPEDEF macro to generate IConnectionPointContainer and IConnectionPoint smart pointers).

Use of SQL-NS is also implemented through the #import directive to import the type library and generating C++ wrapper classes and smart pointers for its COM interfaces. See the sample code.

Sample code (SQL-DMO)

Here is a sample code that shrinks "Northwind" database transaction log(s):

//

// g_spSqlServer2 is SQL Server object global pointer

    
static LPCTSTR spszDbs  = _T( "[Northwind]" );
const  LONG    lLogSize = 2;
//

try
{
    SQLDMO::_Backup2Ptr spBackup2 = NULL;
    _CREATEINSTANCE( spBackup2, SQLDMO::Backup2 );

    spBackup2->Database    = (LPCTSTR)spszDbs;
    spBackup2->Initialize  = (VARIANT_BOOL)( TRUE );
    spBackup2->Action      = 
      (SQLDMO::SQLDMO_BACKUP_TYPE)SQLDMO::SQLDMOBackup_Log;
    spBackup2->TruncateLog = 
      (SQLDMO::SQLDMO_BACKUP_LOG_TYPE)SQLDMO::SQLDMOBackup_Log_TruncateOnly;
    
    spBackup2->SQLBackup( g_spSqlServer2 );

    SQLDMO::_Database2Ptr spDatabase2 = 
        g_spSqlServer2->Databases->Item( (LPCTSTR)spszDbs );
    SQLDMO::LogFilesPtr   spLogFiles  = 
        spDatabase2->TransactionLog->LogFiles;
    SQLDMO::_LogFile2Ptr  spLogFile2  = NULL;
        
    LONG lCount = spLogFiles->Count;

    for( LONG l = 1; l <= lCount; l++ )
    {
        spLogFile2 = spLogFiles->Item( l );

        if( spLogFile2->Size > lLogSize )
            spLogFile2->Shrink( lLogSize, 
                SQLDMO::SQLDMOShrink_Default );
    }
}
catch( IN _com_error e )
{
    // Display com error

}
//

Sample code (SQL-NS)

Here is a sample code that shows "Northwind" database properties just like the SQL Server Enterprise Manager does:

//

// g_spSqlNamespace is ISQLNamespace object global pointer

static LPCTSTR spszDbs = _T( "[Northwind]" );
//

try
{
    LONG lServer    = g_spSqlNamespace->GetRootItem();
    LONG lDatabases = spSqlNamespace->GetFirstChildItem( lServer, 
        SQLNS::SQLNSOBJECTTYPE_DATABASES, (LPCTSTR)NULL );
    LONG lDatabase  = spSqlNamespace->GetFirstChildItem( lDatabases, 
        SQLNS::SQLNSOBJECTTYPE_DATABASE,  (LPCTSTR)spszDbs );

    SQLNS::SQLNamespaceObjectPtr spSQLNSNamespaceObj = 
        g_spSqlNamespace->GetSQLNamespaceObject( lDatabase );
    spSQLNSNamespaceObj->ExecuteCommandByID( SQLNS::SQLNS_CmdID_PROPERTIES, 
        (LONG)(HWND)NULL, SQLNS::SQLNamespace_PreferModeless );
}
catch( IN _com_error e )
{
    // Display com error

}
//

Global Functions

• GetSqlServerPath - As you can see on the picture above, the program is able to execute SQL Server Service Manager and SQL Server Enterprise Manager if they are available on the target machine. The path to SQL Server tools folder can be retrieved from the registry HKLM\SOFTWARE\Microsoft\Microsoft SQL Server \80\Tools\ClientSetup key's SQLPath value. So appending "Binn\sqlmangr.exe" to the retrieved value forms the SQL Server Service Manager full path, and appending "Binn\SQL Server Enterprise Manager.msc" forms SQL Server Enterprise Manager full path, we can pass to the ::ShellExecute or ::ShellExecuteEx functions.

  The HKLM\SOFTWARE\Microsoft\MSSQLServer\Setup registry key's SQLPath value contains SQL Server data root folder. GetSqlServerPath function does this all for you.

• DisplayComError - What do you think it does?
• SkipODBCPrefix - SQL-DMO ODBC error messages have prefixes, for example [Microsoft][ODBC SQL Server Driver][SQL Server][SQL DMO]msg... or something like that. Though setting SQL Server object ODBCPrefix property to false removes them, sometimes these prefixes are appended by all means. You can use this function to remove the remaining.
• FormatMessage - Formats Win32 API message strings from message table resource in already-loaded modules.
• TrimWorkingSet - The function compacts CRT and global heaps and temporarily trims the working set of the process to zero, that essentially reduces the use of the physical RAM memory by application.
• DoEvents - Dinosaur from Visual Basic. Yields the program execution until the operating system processes all messages pending. Useful in heavy loops.

Known Bugs

1. My CMenuButton (see picture) does not respond to the reflected messages. I have to send BN_CLICK to it manually.
2. If there is no space below the CMenuButton to show its menu, it pops menu up but the button becomes obscured by it.
3. If you shrink transaction log on a remote server, the backup is not supported (it gives path errors). I must try to use temporary backup devices to see if the problem persists.
4. Verifying backup media set before backup sometimes gives an error (try to add two files of the same media set and one new). I do not like that piece of code at all. And do not like the previous implementation even more (it is commented). If someone has any idea of improving that, I'll be very grateful to him.

Unknown Bugs

This is my first application in WTL. Beware of numerous unknown and other types of bugs :) All improvements, optimizations and fixes are only welcomed.

Project Dependencies

• Microsoft Platform SDK 2003
• Microsoft WTL (Windows Template Library) 7.1
• Microsoft SQL-DMO (SQL Distributed Management Objects) 8.0
• Microsoft SQL-NS (SQL Namespace API) 8.0
• Microsoft SQL-DMO and SQL-NS 8.0 Development Tools (installed with Microsoft SQL Server 2000)

Program Dependencies

1. Microsoft SQL-DMO 8.0
2. Microsoft Windows 2000 or later (Windows 9x is not supported)

Project Dependency Notes

I have removed the rcstamp.exe (tool for increasing project build number) from the project to decrease its size. If you want to automatically increase the project build number, download the RCStamp Tool, copy it into the solution's bin subdirectory and add the following lines to the project post-build step:

For VC 6.0: "$(WkspDir)\bin\RCStamp.exe" "$(WkspDir)\$(TargetName) \$(TargetName).rc" *.*.+.*

For VC 7.0 and later: "$(SolutionDir)\bin\RCStamp.exe" "$(ProjectDir)$(TargetName).rc" *.*.+.*

This project was originally developed with VS.NET 2003 and then converted to VC 6.0 manually and to VS.NET 2002 using Utility to Convert VS.NET 2003 Project Files. Original VS.NET 2003 solution files are named to tlsw.sln.2003 and tlswa.vcproj.2003 - you can rename them back.

Program Setup

The setup inf file is also included in the project. You can build a small setup exe with IExpress utility. IExpress is included with Microsoft Internet Explorer Administration Kit - it allows to build small and fast setup packages. My setup inf file copies application executable and help-files to the Windows system folder (as far as this is an administrative utility), creates shortcuts to them in the [Start Menu]>[Programs]>[Company Name]>[Transaction Log Shrink Wizard] directory, and registers the information required for the uninstall (you can remove it from the [Add/Remove Programs] in the Control Panel). NOTE: SQL-DMO and SQL-NS setup script is not included. Creating setup inf files is slightly off-topic and I'll try to cover that subject in another article.

Acknowledgements

1. RC Stamp tool for increasing project build number by peterchen.
2. Thanks to Robert Edward Caldecott for his great idea of using Marlett font for displaying glyphs. I found that tip in MSDN (Creating Wizards) only after downloading his article code (see the wizard intro page bullets).
3. Self-Centering WTL Property Sheet by same Robert Edward Caldecott.
4. WTL bugs by Paul Bludov.
5. Article WTL for MFC programmers by Michael Dunn.
6. Ten WTL tips and tricks by Ed Gadziemski.
7. A Utility to Convert VS.NET 2003 Project Files by dacris.

History

• 19-Apr-2004 - Posted the article.