It’s very important to capture trends of the sizes of your SQL Server 2005 database because it allows you to plan for future space needs, notice types of problems, and plan for time periods of heavy volume. I’ll show you the simple method that I use to capture this information.

An exampleI will capture a snapshot of the information related to the sizes of my database files; in my next article, I will analyze the information to see when my data files and log files grow the most.

Each database on the SQL Server contains information regarding the size of the database files, along with some other related information. In order for me to get to this information, I need a method to retrieve the data from the individual databases one at a time. I have two available options:

sp_spaceused: This system stored procedure will return the size statistics for the current database context in which it is running. It is very useful for returning ad hoc information regarding database or table sizes within the database; however, it is not very friendly for reporting purposes. It is possible to capture the information for each database through a script, but it would require the use of a user-defined cursor.
sp_msforeachdb: This is a very useful system stored procedure that will execute any SQL script you pass to for in each of the databases on your SQL Server instance. The stored procedure just loops through the databases, which is simple to write, but it saves you from having to do it yourself. This is the method I will use for my code to capture database file size information.
The information I want to gather and store is available in the sys.database_files system view. This gives me the size of the database files, along with some other handy information such as the state of the database, the manner in which the files grow (size or percentage), and if it is read-only. I will need to capture this information for each database.

The script below creates a table named DatabaseFiles (if it does not already exist) based upon the structure of the system view sys.database_files; it also adds a new column to capture when the record was added to the table.

IF OBJECT_ID('DatabaseFiles') IS NULL
BEGIN
SELECT TOP 0 * INTO DatabaseFiles
FROM sys.database_files

ALTER TABLE DatabaseFiles
ADD CreationDate DATETIME DEFAULT(GETDATE())
ENDNow it is time to populate the DatabaseFiles table. This script uses the sp_msforeachdb stored procedure and passes a SQL script that inserts data from the sys.database_files view into the DatabaseFiles table that I created above. If you examine the script, you will notice that I am building in the database name for each database. This is subtle, and it’s accomplished by the [?] prefix to the sys.database_files view. This code is actually executed in each database on the instance, and the name of the database is used in place of the [?] marker. Information for each database is inserted into the DatabaseFiles table with one line of code, and it is a lot easier than writing a cursor to do the same. I also added a GETDATE() call to indicate when the records were inserted into the table.

Note: This example somewhat goes against two coding standards that I am typically strict about: using SELECT * and inserting into a table without a column list. I omitted them because the SQL string that I am building would have been a lot less desirable to view. If this was code that I put into a production environment, I would have made the necessary changes accordingly.

EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *, GETDATE() FROM [?].sys.database_files'To make sure that all of my data was captured correctly, I’ll look at what is in the table.

SELECT * FROM DatabaseFiles

Let’s face it, we all deal with the fault on a PC or network as a matter of routine, but how often do we consider that we also need to fix the customer? It may be that their confidence in the equipment/service/company has been strained and maybe even broken, and it may be that some work may be needed to restore the customer’s faith in your work.

Is it enough to mend a fault and leave? It may be that the customer has concerns that a few words and a minute or two of listening might make the difference between leaving a happy customer and leaving somebody considering a move to another support service. A few nods and an “I see” or two and some other empathetic noises can make all the difference. One of my worst failings is to listen to the customer, right up to the point where I think I know what the problem is, then I switch off as I start the fix. There may be more to the problem than I’ve heard from the user, and I have often had to backtrack and hear the rest of the story.

In my keenness to get on and fix the fault, I often forget about the customer and get too involved in the technicalities. I recall an incident when the customer had been reporting some minor fault or other on an almost daily basis. After a couple of “no fault found” callouts, I began to wonder if the problem was with the equipment or with the user, so I decided to get them to show me the fault instead of just describing it. It very soon became obvious that the problem lay with a lack of training, and I was able to sort out the problems quite quickly.

I seem to spend a lot of my time banging on about people skills or soft skills, as they are often referred to. Sometimes you can win with soft skills where you fail on the technical fix. Sometimes we have to give bad news, or maybe we can’t fix the fault straightaway; we may have to wait for parts or get a problem fixed on a remote service. It is the way we communicate this kind of information to the customer that determines whether we leave them happy or anxious that we haven’t appreciated the seriousness of the situation.

How do we give bad news without annoying the customer? First, we have to understand that no matter how well you communicate a problem, you can’t always leave the customer happy. It is foolish to think otherwise and could lead to your suffering a lot of stress in the process. Give the news straight and tell the customer what you are going to do about it. If that isn’t good enough, ask what they would like you to do. If you have an idea that might provide a workaround to the problem, run it past them. You will nearly always be able to come to an agreement that will mollify both parties, but it is important to remember that, provided that you have done all you can, you can leave with a clear conscience. Above all else, don’t take the problem home with you.

One problem with running a service-oriented help desk is that people keep coming to you for help.

OK, that’s meant mostly as a joke. Mostly. In my experience I’ve found that creating strong relationships with one’s clients will lead to more service calls. It has something to do with inhibition and intimidation. If customers have a positive experience with a tech, they’ll feel reassured about the support process, and this will make them more inclined to ask for assistance in the future.

Creating comfortable clients is ideal for a freelance or contract technician, who gets paid by the call or by the hour. Every service request is money in one’s pocket. Comfortable clients can be less ideal for a standing in-house support team, though. Users’ inhibitions can become so low that they start asking the help desk to provide support that’s outside appropriate boundaries. This is especially likely in environments that don’t impose any checks on the urge to file a support request, like fees, departmental charge-backs, or ticket accounting.

Responsible IT departments should have published policies about what they’ll support. Even if those policies are out there, though, that won’t keep techs from getting requests for assistance that are beyond the help desk’s authority. Being aware of the types of inappropriate—and sometimes informal—support requests will let you anticipate them and will let you prepare your techs to handle such things, if and when they appear.

Project work or IT engineering tasks. The role of the help desk is, first and foremost, to provide incident-based support to the client. Many places, including my own office, economize by having support techs also work within project teams developing new services. Help desk issues should always trump project work, though. If an IT project or engineering task is important enough that it can’t be set aside in favor of addressing emergent support requests, then it’s important enough that the project’s manager should have dedicated personnel working on it, rather than counting on the help desk techs having slack time.

Requests not related to work. Whether it’s answering questions about problematic home computers or fielding requests to set up MP3 players on company-owned machines, there’s no reason for help desk techs to spend work time answering non-business requests. Let me be clear, I’m not an unfeeling robot. I’ll chat for a minute or two with colleagues about problems they may be having with machines at home, or I’ll provide shopping advice. That’s the extent of it, though. Our support policy excludes privately owned hardware and clearly outlines what’s supported on company-owned machines. That’s where the responsibility of our techs ends, and I’ve had to explain this to a number of users.

Shop talk during social events or off hours. There’s an old cliché that insists that doctors are always being solicited for professional advice at cocktail parties and the like. I don’t know about whether that’s actually true for M.D’.s or not, but it’s certainly true for IT pros. I’ve been at many an office social event, only to have a colleague bring up a problem that they’ve been having. Support pros deserve the opportunity to unplug from work responsibilities now and again. I don’t hesitate to let my users know when I’m “off the clock.”

Those are the three types of inappropriate inquiries I see most often as an office support tech. If you have any others to offer, let me know in the comments.