In reply to your comments:
@HopelessNoob the thing is if it needs to grow then I want it to be
able to grow. What can I do as far as have it not impact performance?
During an autogrow the entire database locks up and there is no way to
change that right? Part of the issue is the system is run 24s a day so
its not like I can grow the DB over night or something. The things is,
I can try as I might to ensure the autogrow doesn't happen but I have
still been given the requirement to notify the users if it does so I
have to fulfill that requirement.
and:
@hot2use how can I set autogrow such that it would not impact the
system? I am under the impression that the entire DB locks when in
this state and there is no way around that. You are correct I do see
5144 in the logs. This isn't a common things but it is a very critical
system and I need to avoid the autogrow and if it happens make sure
the users do not reset the system as it will cause a loss of data.
You can reduce the performance hit of autogrow growing the mdf--go into local security policy and add perform volume maintenance tasks
--but this doesn't apply to the ldf growing. (This is called "Instant File Initialization.")
What I would recommend is that you attempt to "pre-size" your database manually so that autogrow doesn't occur at all. Leave it on, in case you guessed wrong, but don't shrink your transaction log. Also perform regular log backups, and maybe add some room to the log file. (Don't go crazy, though. An example would be if autogrow is occurring a lot during heavy load with a 4GB log file, pre-size it to 10GB and back up your logs more frequently and see if that helps.)
Also, look at your autogrowth settings. If the database is very large, you will probably get better performance growing by a fixed amount than a percentage. (Again, don't go crazy. Growing by a MB is nobody's friend. I'm just saying that if the database is 2TB and you're autogrowing by 10%, that might take awhile.)
Beyond that, what Steve Mangiameli said.
As for notifying the users, if you're already running any kind of monitoring on the server that allows script execution, you might be able to write something that checks the file size. (It's klugey, but it would do what the users are asking you to do.)
Edited in response to your edit:
Update: New potential solution is, since the transaction logs should
never reach a state where they need to autogrow I may be able to
disable autogrow altogether. To feel comfortable doing this though, I
would like to know what would happen in the event that an autogrow is
required but it is disabled? Would the older transaction logs simply
be over written in this scenario??
No. The transactions would fail.