I have a Microsoft SQL Server 2008 that is used for Sharepoint. I would like to monitor the size of the database does anyone know how to accomplish this? I do not want to monitor the file size using WMI because UNC paths are not allowed and that would be a lot of mapped drives to monitor all the SQL Server databases I am responsible for.
Thanks in Advance
Created on Apr 5, 2011 3:37:21 PM by
The database size can be obtained with system stored procedure sp_helpdb that is included in SQL 2008 and 2005 (not sure about 2000). We cannot use this stored procedure directly with PRTG as it returns to many rows and fields, so here is what we have to do:
CREATE PROCEDURE [dbo].[spSpaceUsed] @name nvarchar(24) AS BEGIN SET NOCOUNT ON -- Create a tempory table DECLARE @SpaceUsed TABLE ( name nvarchar(24), db_size nvarchar(13), owner nvarchar(24), dbid smallint, created char(11), status varchar(340), compatibility_level tinyint ) --Populate the table INSERT INTO @SpaceUsed EXEC sp_helpdb --Select the size of the specified table SELECT CONVERT (DECIMAL, REPLACE (db_size, 'MB', '')) AS [size] FROM @SpaceUsed WHERE name = @name END
On your PRTG server, add a new MSQL sensor. Fill in the required fields and in the "SQL Expression" field enter
exec dbo.spSpaceUsed YOUR_DATABASE_NAME
In the channels tab select the value channel and enter MB as unit.
Created on Apr 5, 2011 7:28:59 PM by
Last change on Apr 28, 2011 3:39:54 PM by
I found an easier way of performing this request after a bit of wrestling. In the SQL Query field I used
select (convert(float,Size)*(1024*8))/1024/1024 from DatabaseName.dbo.sysfiles where GroupId = 1
Just replace "DatabaseName" with the database you are wanting to query.
Thanks for the reply
Created on Apr 5, 2011 8:15:59 PM by
You are right, that is an easier way, but it will also include the log file size and not only the database size.
Created on Apr 6, 2011 12:09:46 PM by
You are correct, I need to sit down and decide exactly what it is that i am wanting to monitor. Thanks for pointing that out.
Created on Apr 6, 2011 2:03:28 PM by
If i use your procedure and your sql expression, i receive following error message: "Could not perform query: Database Server Error: Zeichenfolgen- oder Binärdaten würden abgeschnitten." (i'm not sure if my translation would be the same as yours - so it's german)
In that case the system stored procedure sp_helpdb returns “lager” data than the tempory table @SpaceUsed is set up for.
Run sp_helpdb in a new query window, see what the output is and adjust the field sizes of the tempory table accordingly.
Created on Apr 28, 2011 3:26:44 PM by
After adjusting the field sizes (name is up to 60 digits!) the error was removed. But the value field is '0' (no error but nothing in). When i run the sp_helpdp everything seems okay and all values correspond with the size of the db files.
Please log in or register to enter your reply.
Add comment