Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Data Management Objects: Logging the Process
Last updated Mar 28, 2003.
The military, like all government agencies, loves paperwork. When I was in the Air Force, we used to say that perfect government job equilibrium is achieved when you spend 100% of your time documenting the job you aren't doing.
While too much government paperwork isn't a good thing, documentation is good for maintenance programs. In the last article on SQL DMO Coding, I showed you a maintenance program that backs up a database, updates statistics, and checks the tables and indexes. What it lacks is a method to provide adequate feedback and a historical record of the maintenance. I'll add that code today to make a complete maintenance program.
The method for creating a log file is to open a FileSystem object, tie it to a file, write to the file, and then close the file. While that works, it still isn't complete enough for our needs. I'll show you my preferred method for the logging process, although there are others.
First, let's take care of the basics. I need to set aside four new variables to start. The first two are objects that represent the file system and the file. You can use the FileSystem object as the parent (just like the SQL DMO parent is for our server) and the File object as the child. I'll move to the "Variables" section of the code from the last article and type those in:
Dim oFileSystem ' File System Object for logs Dim oLogFile ' File object
The next variable I set holds the logging file's name. You might be tempted to hard-code the name when you create the object, but making a variable allows you to ensure unique log files by prepending the system name or date and time. It also gives you the freedom to let the user set the location, if that's more in keeping with your code distribution. I'll add that variable now:
Dim FileName ' File name variable
Finally, I create a variable for each line in the log file. I'll explain that in a moment:
Dim strMessage ' Logging Message
Now that the variables are set aside, I use the FileSystem object mentioned earlier to create the file:
' Set up Logging File FileName = "c:\temp\Maintenance.log" Set oFileSystem = CreateObject("Scripting.FileSystemObject") Set oLogFile = oFileSystem.OpenTextFile(FileName, 8, True)
In this example, I set the FileName variable to a constant location. You might want to allow the user to pick a location, or perhaps use the Date() function (with a little formatting to remove the illegal / characters) to create multiple logs, in case they run the code multiple times.
The next line sets the file system object. There's nothing to modify here; it's just the code that creates the FileSystem object.
The next line needs a bit of explaining. The first part, oFileSystem.OpenTextFile, calls a method on the oFileSystem object to open a text file. The parts we're more interested in are the three variables that follow.
The first is the filename, and for this I use the FileName variable I created earlier. The number that follows is the way the function should open the file. Here are the codes you can use and what they mean:
- 1=ForReading: Open a file for reading. You cannot write to this file.
- 2=ForWriting: Open a file for writing.
- 8=ForAppending: Open a file and write to the end of the file.
The True that follows the number tells the ForAppending mode that the file should be created if it doesn't exist.
I place this code just before I notify the user about the maintenance. There's a reason for that. I want to know if the user started the program and then just bailed out. You might not care about that distinction, but the point is to make sure that you think about the placement of each section of your code.
So now we have a file object that we can write to. Here's the code to write a line containing "Text to Write" in the log file:
oLogFile.WriteLine("Text to Write")
I also want to add the current time to each line. By placing an entry before and after each operation I can get a historical timeline for each section of the maintenance. I can also use these as entries for a log-parsing program later.
The modified line looks like this:
oLogFile.WriteLine(Time() & "-" & "Text to Write")
I used the Time() function along with some formatting to add the system time in the log, plus a dash to enable parsing later. You can modify this to any format you want.
I could repeat that line for each entry in the log file, but that means I'd have to type that long string each time I update the log file. Plus, if I ever want to change the format of the time or change the dash to a colon, I'll have to make sure I locate each one of these lines and edit them. This leaves me open to errors.
A better method is to create a reusable piece of code called a subroutine, or a function. We won't go into the differences here, but the upshot is that a subroutine or function is like a small program within a program. You can "call" these routines to do repetitive work within your code. Otherwise, they sit idle. They are also very easy to create and use.
Down at the bottom of my code, just before the end of the program, I create a subroutine:
Sub LogProcess() End Sub
That's the format of a subroutine. Enter the name you want prefaced by Sub and then close it out with open and closed parenthesis marks like this: (). On the last line, place the words End Sub.
That's all you need to make a subroutine (albeit one that doesn't do anything). To make it more useful, I place some code between the two lines:
Sub LogProcess(strMessage) oLogFile.WriteLine("Text to Write") End Sub
This subroutine will now write the line "Text to Write" each time it is called. To call it, just type the line:
Anywhere in the code. Of course, I don't really want the words "Text to Write" recorded over and over in our log file, so let's dress this subroutine up a bit.
What I want is to pass some text to the subroutine, and have it write that to the file. To do that, I need to tell the subroutine to expect some text:
All this requires is a variable name – here it's strMessage.
Now I can use the variable within the subroutine:
Sub LogProcess(strMessage) oLogFile.WriteLine(Time() & "-" & strMessage) End Sub
And when I call the function, I use this format:
LogProcess("Checking the tables..")
You can see that the function name, with the parenthesis, activates the subroutine. The string "Checking the Tables.." is the text I want to have entered into the log file. The subroutine takes it from there.
This really all comes together when you see the complete code:
' DMOMaintenance.vbs ' Created: 1/10/05 - Buck Woody ' Change Log: 1/17/05 - Added logging capability ' Variables Dim oServer ' SQL Server object Dim oDatabase ' Database object Dim oTable ' Table object Dim oIndex ' Index Object Dim oFileSystem ' File System Object for logs Dim oLogFile ' File object Dim ReturnVal ' Object to hold the user's choice Dim FileName ' File name variable Dim strMessage ' Logging Message ' Set up Logging File FileName = "c:\temp\Maintenance.log" Set oFileSystem = CreateObject("Scripting.FileSystemObject") Set oLogFile = oFileSystem.OpenTextFile(FileName, 8, True) 'Provide feedback to the user, allow them to bail ReturnVal = MsgBox("Checking Databases, Tables and Indexes. " _ & "Click Yes to continue, No to Cancel.", _ VBYesNo, "DMO Maintenance") If ReturnVal = VBYes then MsgBox "Maintenance has started." _ & " You'll receive a confirmation when" _ & " the maintenance is complete. Click" _ & " OK to begin.", VBOK ' Create and connect to a server Set oServer = CreateObject("SQLDMO.SQLServer") oServer.LoginSecure = True oServer.Connect "(local)" LogProcess("Local Server connected.") ' Perform Server maintenance ' Place any server maintenance you want here. ' Perform database maintenance ' Get a database. Could prompt here or use a ' collection to do them all. Set oDatabase = oServer.Databases("pubs") LogProcess("Pubs Database connected.") ' Backup the database. Use an in-line ' Backup object method. LogProcess("Database Backup...") Dim oBackup Set oBackup = CreateObject("SQLDMO.Backup") oBackup.Action = SQLDMOBackup_Database oBackup.Database = "pubs" oBackup.Files="c:\temp\pubs.bak" oBackup.SQLBackup oServer LogProcess("Database Backup Complete.") ' Perform Table maintenance LogProcess("Checking Tables...") oDatabase.CheckTables() LogProcess("Tables Checked.") LogProcess("Updating Statistics...") oDatabase.UpdateIndexStatistics( ) LogProcess("Statistics Updated.") For each oTable in oDatabase.Tables ' Don't really need this, ' since we did it three lines above. ' Just another method if you want ' to do it table by table. LogProcess("Updating Table " & oTable.Name) oTable.UpdateStatistics() ' Perform Index maintenance For each oIndex in oTable.Indexes IndexList = IndexList & "Index Checked: " & oTable.Name & " - " & oIndex.Name LogProcess(IndexList) oIndex.CheckIndex() Next Next ' Notify the user you're done. LogProcess("Maintenance Complete.") MsgBox "Maintenance complete. See log file " & FileName ' Clean up. Set oTable = Nothing Set oDatabase = Nothing Set oServer = Nothing End If oLogFile.Close Sub LogProcess(strMessage) oLogFile.WriteLine(Time() & "-" & strMessage) End Sub
If you run this code, make sure you have a C:\TEMP directory on your system. Once you run the code, you'll get a log file stamped onto that directory. Note that the log file is created where the code runs, not the server it runs against. That's an important distinction.
In the next set of articles, I'll use these logging techniques again. In future articles, I'll explain how you can go the opposite way with the file and read it with your programs.
Online Resources Data Management Objects
The full documentation for the SQL Server Object Library can be found here.
I've been reading a lot of Perl programming lately, and thought I'd share this tidbit on creating log files.
InformIT Tutorials and Sample Chapters
If you're using a 3-GL language (such as one of the .NET flavors) you can also write significant entries to one of the event logs that Windows maintains. This article by Jim Mischel shows you how.