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:
- Shrink SQL Server database transaction log(s).
- Backup database after shrinking it.
- Create SQL Server job that automatically shrinks transaction logs when they reach their limit size.
- See the final results, i.e. sizes of the transaction logs after shrinking.
- 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:
- (SQL-DMO) Listing network available SQL Servers.
- (SQL-DMO) Starting SQL server if it is stopped.
- (SQL-DMO) Connecting to SQL server using either Windows or SQL Server authentication.
Database Selection Page
- (SQL-DMO) Listing SQL Server databases, excluding system ones if needed.
- (SQL-NS) Displaying database properties (if SQL-NS is installed).
- (SQL-DMO) Listing SQL Server database transaction log(s).
- (SQL-DMO) Retrieving the properties of the transaction log file(s).
- Formatting backup media set properties (drag and drop is also supported).
- (SQL-DMO) Verifying the backup media set before backup.
- (SQL-DMO) Starting Job Server (SQL Server Agent).
- (SQL-DMO) Creating SQL Server Jobs and their steps.
- (SQL-DMO) Creating SQL Server Alerts that trigger the jobs created.
- (SQL-DMO) Starting SQL Server job (commented).
- (SQL-DMO) Shrinking transaction log(s).
- (SQL-NS) Backing up the selected database using SQL Server Enterprise Manager UI.
- (SQL-DMO) Backing up the selected database.
- (SQL-DMO) Handling Backup Object events (PercentComplete, Complete and NextMedia events) using event sinks. (See
IBackupSink source code for more details).
- (SQL-DMO) Verifying backup media set files after backup is complete.
- Executing SQL Server Service Manager.
- Executing SQL Server Enterprise Manager.
- Trimming CRT and global heap memory.
- Displaying COM errors.
- Displaying Win32 errors.
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
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
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):
static LPCTSTR spszDbs = _T( "[Northwind]" );
const LONG lLogSize = 2;
SQLDMO::_Backup2Ptr spBackup2 = NULL;
_CREATEINSTANCE( spBackup2, SQLDMO::Backup2 );
spBackup2->Database = (LPCTSTR)spszDbs;
spBackup2->Initialize = (VARIANT_BOOL)( TRUE );
spBackup2->SQLBackup( g_spSqlServer2 );
SQLDMO::_Database2Ptr spDatabase2 =
g_spSqlServer2->Databases->Item( (LPCTSTR)spszDbs );
SQLDMO::LogFilesPtr spLogFiles =
SQLDMO::_LogFile2Ptr spLogFile2 = NULL;
LONG lCount = spLogFiles->Count;
for( LONG l = 1; l <= lCount; l++ )
spLogFile2 = spLogFiles->Item( l );
if( spLogFile2->Size > lLogSize )
catch( IN _com_error e )
Sample code (SQL-NS)
Here is a sample code that shows "Northwind" database properties just like the SQL Server Enterprise Manager does:
static LPCTSTR spszDbs = _T( "[Northwind]" );
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 );
(LONG)(HWND)NULL, SQLNS::SQLNamespace_PreferModeless );
catch( IN _com_error e )
CMenuButton (see picture) does not respond to the reflected messages. I have to send
BN_CLICK to it manually.
- If there is no space below the
CMenuButton to show its menu, it pops menu up but the button becomes obscured by it.
- 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.
- 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.
This is my first application in WTL. Beware of numerous unknown and other types of bugs All improvements, optimizations and fixes are only welcomed.
- Microsoft SQL-DMO 8.0
- 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.
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.
- RC Stamp tool for increasing project build number by peterchen.
- 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).
- Self-Centering WTL Property Sheet by same Robert Edward Caldecott.
- WTL bugs by Paul Bludov.
- Article WTL for MFC programmers by Michael Dunn.
- Ten WTL tips and tricks by Ed Gadziemski.
- A Utility to Convert VS.NET 2003 Project Files by dacris.
- 19-Apr-2004 - Posted the article.