January 30, 2004 at 2:59 pm
Hi,
I have a database that has a DATA FILE growth restriction of 4GB. I want to create an alert that would inform me when the DATA FILE increased to 90% of the 4GB of space allocated.
How do I do that ? I know SQL Server has an alert (017-Insufficient Resources), but I believe that this alert will only kick in when the data file is full. Am I correct ?
Any help would be appreciated.
Thanks.
Albert
January 30, 2004 at 4:27 pm
Use a PErformance Monitor alert. You can alert on db getting a certain size.
February 3, 2004 at 3:41 pm
Thanks Steve. I have actually thought about that, but due to some politically issues of my company, it's almost next to impossible to ask somebody to turn on a counter and monitor the data file growth.
I'm trying to write a stored procedure that is similar to sp_spaceused to calculate the percentage of data file used.
For testing, I set up a dummy database and allocated 1MB of datafile and 1MB of transaction log. Currently, from the SQL Server Enterprise Manager Taskpad, it indicates to me that 0.75MB of the DATAFILE has been used and 0.25MB is free. I want to create a stored procedure that will alert me when the DATAFILE reaches 90% of the allocated 1MB size.
I don't have a problem calculating the size of the datafile.
=======================================================
SELECT @datafilesize = sum(convert(dec(15), size))
FROM dbo.sysfiles
WHERE (status & 64 = 0) -- @datafilesize = 128
SELECT @bytesperpage = low
FROM master.dbo.spt_values
WHERE number = 1
AND type = 'E' -- @bytesperpage = 8192
SELECT @pagesperMB = 1048576 / @bytesperpage -- @pagesperMB = 128
SELECT database_name = db_name(),
datafile_size = ltrim(str(@datafilesize / @pagesperMB, 15, 2) + ' MB')
=======================================================
The result of the above code would be datafile_size = 1MB, which corresponds to the TASKPAD result.
However, I can't seem to get the datafile space used to add up to 0.75MB nor the numbers from sp_spaceused really add up to 0.75MB.
0.75MB = 786432 Bytes = 768 KB
RESULTS FROM sp_spaceused:
---------------------------------------
Reserved Data Index_size Unused
552 KB 168 KB 280 KB 104 KB
But any of the combinations of the numbers above does not give me 768KB.
What am I missing here ?
Please help me out if you know how to calculate the space used in a datafile.
Thanks in advance.
Albert
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply