Under some circumstances, autogrow for database files can be set to some 12000 percent. I think this is limited to SQL Server 2005 and for databases upgraded from SQL Server 2000 (I didn’t bother to search – feel free to comment if you know). So, if you have a reasonably sized database and autogrow kicks in, you can do the maths and realize that pretty soon you are out of disk space.
I wrote a proc that I schedule that check for out-of-bounds values in sys.database files. The proc generates a bunch of messages it prints (handy if you have as Agent job with output file) and also constructs an error message and does RAISERROR (handy if you implemented alerting, for instance according to http://karaszi.com/agent-alerts-management-pack).
I prefer to schedule below as Agent job and use Agent alerts to notify me if we do have db with autogrow out-of-whack. As always, don’t use code if you don’t understand it.
USE maint
GO
IF OBJECT_ID('check_autogrow_not_percent') IS NOT NULL DROP PROC check_autogrow_not_percent
GO
CREATE PROC check_autogrow_not_percent
AS
DECLARE
@db sysname
,@sql NVARCHAR(2000)
,@file_logical_name sysname
,@file_phyname NVARCHAR(260)
,@growth VARCHAR(20)
,@did_exist bit
,@msg NVARCHAR(1800)
,@database_list NVARCHAR(1000)
SET @did_exist = CAST(0 AS bit)
SET @database_list = ''
--For each database
DECLARE dbs CURSOR FOR
SELECT name FROM sys.databases
OPEN dbs
WHILE 1 = 1
BEGIN
FETCH NEXT FROM dbs INTO @db
IF @@FETCH_STATUS <> 0 BREAK
SET @sql = 'DECLARE files CURSOR FOR
SELECT CAST(growth AS varchar(20)), physical_name, name
FROM ' + QUOTENAME(@db) + '.sys.database_files
WHERE is_percent_growth = 1
AND growth > 20'
EXEC(@sql)
OPEN files
WHILE 1 = 1
BEGIN
FETCH NEXT FROM files INTO @growth, @file_phyname, @file_logical_name
IF @@FETCH_STATUS <> 0 BREAK
SET @did_exist = CAST(1 AS bit)
SET @database_list = @database_list + '["' + @db + '": "' + @file_logical_name + '"]' + CHAR(13) + CHAR(10)
SET @msg = 'Out-of-band autogrow in database "' + @db + '"' +
' with growth of ' + @growth +
', logical file name "' + @file_logical_name + '"' +
', physical file name "' + @file_phyname + '"' +
'.'
RAISERROR(@msg, 10, 1) WITH NOWAIT
END
CLOSE files
DEALLOCATE files
END
CLOSE dbs
DEALLOCATE dbs
IF @did_exist = CAST(1 AS bit)
BEGIN
SET @msg = 'Databases with out-of-control autogrow in databases: ' + CHAR(13) + CHAR(10) + @database_list
RAISERROR(@msg, 16, 1) WITH LOG
END
GO
I’ve been playing on 123win02.net for a few days now and the experience has been positive. Quick payouts and a decent variety of games. Recommend giving it a shot 123win02.net if you’re searching for something new.
Bombing Fishing? Sounds like my kinda party! Just stumbled upon it at Bombingfish.online and it’s looking pretty sweet. Check out the link bombing fishing.
VN123vip? Nghe cái tên là thấy sang xịn mịn rồi đó. Không biết chất lượng thế nào, nhưng mà cứ vào thử xem sao. Biết đâu lại vớ được món hời. Let’s go vn123vip!
Betboom Dota 2 – If you love Dota 2, this might be your jam! Plenty of action and opportunities. I dove right in, will definitely be back for more! Take a look: betboomdota2
Needed a quick and easy login process and WOWPH1 delivered! ‘wowph11 com login’ was a breeze. Let’s see if the games are as good as the initial experience. Check it out for yourself: wowph11 com login
Saw some friends playing on ae888co and decided to jump in. Hoping to get lucky! Check it out and see if you can win big: ae888co
Spotted PKWinlogo and decided to see what it’s all about. The logo looked cool, so why not? Check them out and tell me what you think: pkwinlogo