Automatically Getting Log Space
- Getting a Count of Database Users - Part 1
- Part 2 - Automatically Gathering Database Size
- Part 3 - Automatically Getting Log Space
- Part 4 - Getting Organized
- Part 5 - A Daily Report
- Part 6 - Capturing Ad hoc Information
- Part 7 - Checking Job History
- Part 8 - Tracking Your Objects
Introduction
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
- sp_spaceused
- 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 couple paragraphs.
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_spaceusedpresents 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:\MSSQL7\data\DBA_Pri_Data.MDF 3 1 1600 515 DBA_Data C:\MSSQL7\data\DBA_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 members of.
- 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.
Calculations
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 * 64 MB total = kb total / 1024
An extent is 8 8kb pages, so each extent contains 8 x 8kb = 64kb. To convert from kb to MB, you divide by 1024. 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.
Conclusions
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.
Steve Jones
© dkRanch.net October 2000