CodeProject: Shrinking SQL Server Transaction Logs with SQL-DMO. Free source code and programming help

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):

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?

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:

Database Selection Page

Backup Page

Shrink Page

Miscellaneous

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.

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)

Sample code (SQL-NS)

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

Global Functions

Known Bugs

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

Program Dependencies

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

History