home *** CD-ROM | disk | FTP | other *** search
- USE master
- GO
-
- CREATE PROC sp_warn_if_full_db @fillpercentage DECIMAL(5,2) = NULL AS
- /*******************************************************************************/
- /* Generates warning is db is fuller than specified value. */
- /* @fillpercentage is how full the db should be for a warning to be generated. */
- /* Copyright (c) Tibor Karaszi and B÷rje Carlsson 1998. */
- /* Tested on verion 6.5. Does nothing if executed on 7.0. */
- /*******************************************************************************/
- SET NOCOUNT ON
-
- DECLARE @ver VARCHAR(5)
- SELECT @ver = CASE
- WHEN CHARINDEX('6.50', @@VERSION) > 0 THEN '6.50'
- WHEN CHARINDEX('7.00', @@VERSION) > 0 THEN '7.00'
- END
- -- This is not supported on 7.0.
- IF @ver = '6.50'
- BEGIN
- IF @fillpercentage NOT BETWEEN 1 AND 99 OR @fillpercentage IS NULL
- BEGIN
- RAISERROR("Bad parameters: @fillpercentage = How full db for warning to be generated.", 16, 1)
- RETURN -101
- END
-
- DECLARE @db_size FLOAT, @db_full_pages FLOAT, @db_full_percentage FLOAT, @msg VARCHAR(255)
-
- -- User defined segments are not supported
- IF (SELECT COUNT(*) FROM sysusages WHERE segmap NOT IN(3, 4, 7)) > 0
- BEGIN
- RAISERROR("User defined segments are not supported.", 16, 1)
- RETURN -102
- END
-
- IF (SELECT COUNT(*) FROM sysusages WHERE segmap = 7 AND dbid = DB_ID()) > 0
- -- Data and log not separated
- BEGIN
- SELECT @db_size = SUM(size) FROM sysusages WHERE dbid = DB_ID()
- SELECT @db_full_pages= SUM(reserved) FROM sysindexes WHERE indid IN(0,1)
- END
- ELSE IF (SELECT COUNT(*) FROM sysusages WHERE segmap IN(3, 4)) > 0
- -- Data and log separated
- BEGIN
- SELECT @db_size = SUM(size) FROM sysusages WHERE dbid = DB_ID() AND segmap = 3
- SELECT @db_full_pages= SUM(reserved) FROM sysindexes WHERE indid IN(0,1) AND id != OBJECT_ID('syslogs')
- END
- SELECT @db_full_percentage = @db_full_pages/@db_size*100
-
- IF @db_full_percentage > @fillpercentage
- BEGIN
- SELECT @msg = 'Database ' + DB_NAME() + ' is ' + CONVERT(VARCHAR(10), @db_full_percentage) + ' %% full.'
- RAISERROR(@msg, 15, 1)
- END
- END
- /*
- Test execution:
- sp_warn_if_full_db 70
- */
-
- GO
-
-