Automatically Getting Log Space
This article continues the same topic of Automatically Gathering
Server Information. If you want to read the previous articles, click on one of these links:
Getting a Count of Database Users – Part 1
Automaticall Gather Database Size – Part 2
Up to Part 3 already! I never quite thought this series would grow beyond two articles, but after
quite a bit of feedback, here we are. Most of the feedback involved getting the log space percentage
or amount used out of the total database size. I also got quite a few suggestions, but none of them
worked as I would like. Especially since the idea is to automate this process. I am going to look at
a few of the suggestions and why they do not work that well and then present a way to actually automate
the process of gathering the log and database space and presenting them the same way that Enterprise
Manager presents them in a front end GUI. If you are interested in the other articles,
Part 1 deals with getting the users in a database and Part 2 gathers the total size of
each database automatically.
I had a couple of suggestions for getting the log space sent to me by colleagues. And a request. If you
use Enterprise Manager and click on the name of your database, in the right hand pane you get a summary
of what the SQL team in Redmond considers to be important information. In the SQL 2000 Enterprise Manager
you get a slightly different summary. I guess they are incorporating feedback into the tools (at least
I hope they are and its not just different preferences from different developers). What I would hope (since
this is an HTML page) is that we could build MyEM and get what each of us considers important.
Enough daydreaming and wishing, under the Space Allocated tab (SQL 7) or at the bottom of the
display (SQL 2000) there is a data space item which shows a nice bar graph of the total and used
spaces of your database split into data and log sections. It’s nice, color coded, and concise, all things that
I like to see in tools that present information. If I could get this bargraph delivered to me everyday in
email from every database, then I would be thrilled. Or if I could click on the Databases item in EM
and get the information for all databases I might even use that (Redmond SQL Team, hint, hint). So to keep
this article short, let’s fire up the database maintenance plan and select the “Get daily email of database
space” option and we’re done.
Well, if it were that simple, I’d be writing less and you would probably not be reading this. There is
no option in the maintenance plan. One day…(sigh)
So can we duplicate this? You bet! and I am about to show you how. The method I present will actually
use one of the suggestions that was sent to me, though it is not as simple as the sender thought.
Getting the Log Space
What were the suggestions? There were three of them that I received along with numerous requests
asking how to get the data. The three were:
- Query the system tables directly and perform calculations on the data
- dbcc sqlperf( logspace)
So which of these can we use? Well, since I hate rewriting code and building temporary solutions, and I
have learned that building unsupported code is not a good idea, number 1 is out of the question. Especially
since there are other methods. So we are left with two possibilities that I will look at in the next
When I received these suggestions, the second thing I did (after a quick “why didn’t I think of that?”) was
to try sp_spaceused out on my SQL Server. I have a database called DBA and when I ran this from
that database I got:
database_name database_size unallocated space ------------------ ------------------ ------------------ DBA 153.88 MB 121.95 MB reserved data index_size unused ------------------ ------------------ ------------------ ------------------ 32696 KB 30312 KB 1464 KB 920 KB
I don’t know about you, but this doesn’t appear to be showing me log space or usage anywhere. In fact,
it still mixes log space into the data space, though it does give me a breakdown of my data v index
space allocation. However, the use of this information is another article. So how does Microsoft get the
data that Enterprise Manager presents as a nice little bar graph?
DBCC SQLPERF( LOGSPACE) accounts for a portion of the data. When you run this little query, you will
get some data that looks like the following:
Database Name Log Size (MB) Log Space Used (%) Status ----------------- ---------------- ------------------------ ----------- DBA 48.867188 14.268585 0 QA 88.679688 57.477314 0 Northwind 0.9921875 38.23819 0 pubs 0.484375 82.963707 0 msdb 1.7421875 41.171524 0 tempdb 0.7421875 50.376492 0 model 0.7421875 72.631577 0 master 1.2421875 48.034592 0
Well, getting closer here to what we want. This result set shows the log space and the percentage of
this space that is used. So we can easily determine the percentage not used and generate our own little
bar graph. However, bar graphs in a text environment are not terribly useful or aesthetically pleasing
to me, so I will stick with numbers.
Neither of these suggestions gives the desired result, but together they present the information that
we need to build a solution. So now I will describe the solution that I put together to automatically
gather this information from the server.
Gathering the Information
Gatering the information from these two commands proves to be difficult at best. There are a few problems
with getting this data directly as I did with sp_helpdb. DBCC commands do not produce result
sets. Instead they print information messages to the standard output device as an asynchronous stream or
return them as messages to a calling program. Since I am trying to keep this within T-SQL as simple as
possible, we have to “trick” T-SQL into getting these results into a table. To do this, we will modify
our EXEC command slightly as follows:
CREATE TABLE #logspace ( DBName varchar( 100), LogSize float, PrcntUsed float, status int ) INSERT INTO #logspace EXEC ('DBCC sqlperf( logspace)') /* process the data */ select * from #logspace /* Cleanup - drop the temp table */ drop table #logspace
Notice that the EXEC() command now runs the command as a string of T-SQL being executed instead
of directly as in Part 2. Using this command, we can then get the log
space amounts and process them as we see fit. So how do we process them? Well, from the previous article,
we have the total size of the database from sp_helpdb, in this case 153.88MB. The dbcc sqlperf(logspace)
query shows the log space to be 48.86MB with 14.3% used ( 6.98MB). If we subtract the log space from the
total we get 105.02. The EM GUI shows 105MB data space for this database, so we are pretty much on target
for gathering the information.
What about the database space usage? There is a bit of a problem here and I am struggling with the
solution. sp_spaceused presents a problem. To get this to report on space used by the database
we need to leave the parameters out. This stored procedure, however, then returns two result sets for
the database. With two different numbers of columns!!! With parameters, it returns one, but then you only get
the usage for the object you specify. So I could then get the space for all objects and add them up, right?
Well, this tends to violate my simple concept for solutions. Also, for this database I have two data
files and sp_spaceused only reported on them both as a single database. Not a big deal, but if these are on two separate
physical devices, then I may want to know how close each is to getting full.
The alternative? Use an undocumented (read this as unsupported) DBCC call to get the usage by each of the
files. This DBCC call is actually made by the Enterprise Manager GUI when building the display. It also
works in SQL Server 7 and SQL Server 2000. However, it is unsupported and if you were to upgrade to SQL Server 2001,
I would hate for all your information gathering to break in a single day.
As much as I hate to do it, I do not have a good solution at this time and so will work with the undocumented
DBCC command. I am making this comprimise since my hunch is that this is the procedure that is being
used by Enterprise Manager to generate the bar graph. It is definitely being called, so there is at least
a chance that I am correct.
Now that I have decided to use an undocumented, unsupported query, how does this help us? Well, the
data that is being returned by DBCC SHOWFILESTATS is the following:
Fileid FileGroup TotalExtents UsedExtents Name FileName ----------- ----------- ------------ ----------- ------------- -------------------------------- 1 1 80 38 DBA_Pri C:MSSQL7dataDBA_Pri_Data.MDF 3 1 1600 515 DBA_Data C:MSSQL7dataDBA_Data.NDF
Since there is no documentation, I had to decode this output myself. The following are the descriptions
that I believe apply to the results.
NOTE: This is my opinion only. It is based on testing and experience,
but is an opinion only. Use this information with caution and at your own risk.
This appears to show only the data space information, so no log information for this database is
being displayed. I did create two data files for this database and the file names match my creation
script. Here are my thoughts on the definitions:
- Fileid – This is sysfiles.fileid. A unique identifier for each file associated with this database.
- FileGroup – This is sysfiles.groupid for each file which also corresponds to an entry
in sysfilegroups. In this case I only have a primary filegroup of which both these files are
- TotalExtents – Total number of extents that exist in the data file. Using the calculations below
this number corresponds to the size of the datafile.
- UsedExtents – Number of extents that have been allocated in this datafile. I did not find this
information stored in any table. It is stored as a bitmap internally and is probably calculated by
the stored procedure.
- Name – Logical name of the file. sysfiles.name and the name that is displayed in Enterprise
Manager when looking at space used.
- FileName – The physical path and filename on the server for this data file. These correspond to the
actualy names I had used in the database creation script.
Now that I have decoded this output (at least to my satisfaction), how do I use it? Well, first we get
the data in the same way as we did for the log information.
CREATE TABLE #dataspace ( FileID int, FileGrp int, TotExt int, UsdExt int, LFileNm varchar( 100), PFileNm varchar( 100) ) insert #dataspace exec( 'dbcc showfilestats') select * from #dataspace drop table #dataspace
Note that this gives only the information for the current database. To get the information for
another database, the easiest way to do this is to change the EXEC() statement like this:
insert #dataspace exec( 'use master dbcc showfilestats')
Of course, you would need to build some type of loop to get the data for all databases, but this is
easily get this from the log information gathered previously. I will show this in the solution section. Now
for the calculations.
Calculating a size in MB based on extents, is fairly easy. Here is the calculation:
kb total = extents * 32 MB total = kb total / 512
An extent is 16 2kb pages, so each extent contains 16 x 2kb = 32kb. To convert from kb to MB, you
divide by 512. Fairly simple and this calculations using the results from my DBCC command give me the
sizes for my data files that I setup in the creation script.
The Total Solution
Well, at least total solution for getting the space distribution in your database. So how do we put
this together? First the objects:
- dbaSpaceDist.sql – table to hold the final results of database space usage.
- dbspCalcdbaSpaceDist.sql – stored procedure that loads the current space allocations for
all databases on the server.
You need to compile these both into the same database. I keep a DBA database on all servers that I
use to store this type of information for DBA use only. The first object is the table that will maintain
the space allocation information. I am keeping the information needed to recreate the display in
Enterprise Manager as well as the date this information was gathered.
The stored procedure performs the work by runnung DBCC SQLPERF( LOGSPACE) and storing this information
in a temp table and inserting it into the permanent table. Once this is complete, I use a cursor to
loop through the database names and run DBCC SHOWFILESTATS in each database.
Now you have a way to gather the database size and log size of all databases on your server automatically. You
can use SQLAgent to schedule this process and store the information or build a stored procedure and
check on space usage whenever you want. So, is this how Microsoft gets the information in Enterprise
Manager? Well, partially. Both of these queries are run along with quite a few others when you click on the
database name in Enterprise Manager. Since the information is returned from a few sources, I cannot be
sure where they actually pull the information for the GUI. Most of the queries that are being run, however,
are unsupported officially, so I would hesitate to use any of them. That being said, I chose to use one
of them because I could not get the information in any supported way that I know of and I think the DBCC
command is more likely to become supported in the future as opposed to a query of the system tables. USE THIS
TECHNIQUE AT YOUR OWN RISK, since it it an unsupported comamnd.
As always, please send me your feedback along with suggestions for other information that is you would like
to see me gather from the server. In the next article, I will take a step back and describe how to actually manage
this glut of information that I am gathering.