December 2, 2009 at 9:15 am
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?
December 2, 2009 at 10:03 am
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://qa.sqlservercentral.com/scripts/Miscellaneous/31547/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 2, 2009 at 10:05 am
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://qa.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?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 2, 2009 at 11:43 am
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
December 2, 2009 at 12:06 pm
What is the current autogrowth settings for your DB files?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 3, 2009 at 12:14 am
Both the DAT and the LOG file are set to 100mb
December 3, 2009 at 5:26 am
100 MB for Master Database is more than enough. Keep it that way.
So, you recovered 6.8 GB right ! 🙂
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 3, 2009 at 5:31 am
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
December 3, 2009 at 5:46 am
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.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 3, 2009 at 5:53 am
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)
---------------------------------------------------------------------
December 3, 2009 at 6:37 am
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
December 3, 2009 at 6:48 am
go straight to SP3. may well fix it.
---------------------------------------------------------------------
December 3, 2009 at 7:05 am
Can we go straight to three? don't we need 1 then 2 or is it recursive?
December 3, 2009 at 7:24 am
alan.nichol (12/3/2009)
Can we go straight to three? don't we need 1 then 2 or is it recursive?
No you don't need SP1 and SP2. Service Packs are cumulative, so the latest is good to go.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 3, 2009 at 8:39 am
Thanks very much...I really appreciate your help
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply