Triggering excessive SQL 2005 Autogrowth

Question

Triggering excessive SQL 2005 Autogrowth

alan.nichol

Old Hand

Points: 359
More actions
December 2, 2009 at 9:15 am

#214425

As a newbie, I need help in looking at triggers and how they would work, if at all, with any change in the Autogrowth of one of my databases.
If the autogrowth goes above a particular percentage I want to activate a script that will issue the ALTER command below
ALTER DATABASE [database] MODIFY FILE ( NAME = N'[database]_dat', FILEGROWTH = 100mb)
Can anyone help me please?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

What do you mean by autogrowth going above a certain percentage? The autogrowth is by a number or a percentage and is fixed. Do you mean the total size of the db?

Typically people set an alert here and based on the alert, they run a script. I suppose that you could use a DDL trigger on a growth event and check if the size is above some value. If you want to look at an alert, try here: http://www.sqlservercentral.com/scripts/Miscellaneous/31547/

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 2

alan.nichol (12/2/2009)
As a newbie, I need help in looking at triggers and how they would work, if at all, with any change in the Autogrowth of one of my databases.

Find the link below that lists all the Articles on Triggers.

http://www.sqlservercentral.com/search/?q=triggers&t=a

If the autogrowth goes above a particular percentage I want to activate a script that will issue the ALTER command below
ALTER DATABASE [database] MODIFY FILE ( NAME = N'[database]_dat', FILEGROWTH = 100mb)
Can anyone help me please?

I am unable to understand what are you trying to achieve.

A Database file has enable autogrowth, maxsize options to make it automatically grow when the file is full. Have you got them enabled and set?

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

alan.nichol Old Hand Points: 359 More actions · Answer 3

I am sorry that I have not explained myself enough, but as I said I am a newbie. The problem is that for some reason, the Autogrowth on one of our 300gb databases will expand to 12800%, causing the current size to take up the entire 750gb SAN disk.

To get round this, I have to reset the 12800% figure using the ALTER command to change this to 100mb. Once this is done it becomes stable for a few weeks, but can then go wild again. We realise that a fix may be available but I am unable to find anything definate.

So, what I would like to do is capture this rise to 12800% and issue the ALTER command automatically so if this does happen then it may clear the problem.

Obviously we want this fixed, but as I mentioned I am unable to find a way out at the moment.

Regards

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 4

What is the current autogrowth settings for your DB files?

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

alan.nichol Old Hand Points: 359 More actions · Answer 5

alan.nichol

Old Hand

Points: 359

December 3, 2009 at 12:14 am

#1086919

Both the DAT and the LOG file are set to 100mb

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 6

100 MB for Master Database is more than enough. Keep it that way.

So, you recovered 6.8 GB right ! 🙂

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

alan.nichol Old Hand Points: 359 More actions · Answer 7

No...we reclaimed around helf of the 750gb drive when I issued the ALTER DATABASE command, however, were a bit concerned that this excessive growth with come back again and what I am trying to do is capture this, if and when it happens, and then issu the ALTER DATABASE command automatically to set the 12800% that mysteriously appears back to 100mb

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 8

alan.nichol (12/3/2009)
No...we reclaimed around helf of the 750gb drive when I issued the ALTER DATABASE command, however, were a bit concerned that this excessive growth with come back again and what I am trying to do is capture this, if and when it happens, and then issu the ALTER DATABASE command automatically to set the 12800% that mysteriously appears back to 100mb

What is the current size of the Master DB now? it was eralier 7 GB right ?

A little bit of confusion towards the end of your reply.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

george sibbald SSC Guru Points: 104210 More actions · Answer 9

either you have hit a bug (what version of SQL are you on?) or someone\something is issuing this command. I suggest you run a server side trace and include the security audit\audit database management and database\data file autogrow events and capture who\what makes this change.

It may be the application trying to be too clever for its own good. Those that attempt to handle normal DBA activity for you sometimes do this type of thing (MS are prone to do this with sharepoint, softricity,MOM etc)

---------------------------------------------------------------------

alan.nichol Old Hand Points: 359 More actions · Answer 10

SQL 2005.

I have googled this error message and it does mention a bug, but I am unable to fine any fix for it. We do need to add SP1 and SP" to see if this will cure it.

Is there any scripting that can monitor this Autogrowth amount?

Thanks for you help so far...much appreciated