-3

I am having some issues with my DB autogrowing because of transaction log sizes. I have implemented a DB maintenance procedure where the transaction logs will be truncated regularly so this should not longer happen, but I have a requirement to notify users when the DB enters an autogrow state. It does get recorded in my DB logs, but I need to find away to display something visually on the screen. This particular system has an application with a local DB both residing on the user's machine, so some sort of popup on the screen the DB resides on is ideal. Any ideas on how I can accomplish this??

Update NOTE: I would prefer not to use any 3rd party software. The operating system is Windows Server 2003, I am using MS SQL Server 2005.

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??

mgrenier
  • 123
  • 6
  • Why the down vote? Is this not where I would ask such a question? – mgrenier Mar 03 '16 at 19:22
  • 1
    I can think of a few reasons you might be getting downvotes, but ultimately, you're just plain "doing it wrong" on a couple levels here... your question would probably be better received if you asked about what you're actually trying to achieve, instead of asking for help on how to do this specific thing you probably shouldn't be doing. – HopelessN00b Mar 03 '16 at 20:20
  • net send ??? ;) – yagmoth555 Mar 04 '16 at 01:32
  • @HopelessN00b, sorry if I wasn't clear. What I am trying to accomplish is have a visual notification on screen (i.e. a popup of some sort) when the database enters an autogrow state. – mgrenier Mar 04 '16 at 13:59
  • @yagmoth555 if there is a solution involving net send could you go into a little more detail please? I am unsure how to accomplish my goal via net send. Also please put it in an answer rather than comment and if it resolves my issue I can give you credit. – mgrenier Mar 04 '16 at 14:02
  • @mgrenier You detect the status how? a script, homemade app, etc.. Your solution and answer reside more there. Please add some detail – yagmoth555 Mar 04 '16 at 14:08
  • @yagmoth555 All that happens now is it is recorded in my event logs, but the autogrow causes the system that is interacting with it to become unresponsive so I want a visual queue to notify users what it happening so they wait until it is finished. What happened previously was the user restarted the system and corrupted the database, I want to avoid that. – mgrenier Mar 04 '16 at 14:11
  • @mgrenier It's not unclear, it's just... why would you do such a thing? You need to adjust your database and/or SQL configurations so that the database autogrow doesn't cause problems, rather than sending an email saying the sytem is still misconfigured and will be unresponsive for a little bit while the database auto-grows. – HopelessN00b Mar 04 '16 at 14:14
  • @HopelessN00b as mentioned in the original question, I am implementing a DB maintenance procedure that should eliminate this from happening, but I have been given a requirement that in the event that it does happen I notify the user. So I am attempt to figure out how I can do so. – mgrenier Mar 04 '16 at 14:21
  • 1
    I wouldn't recommend shrinking the transaction log file if it is going to grow again because it requires additional space. Autogrowth if set correctly shouldn't have a lot of impact on the system. Leaving a reasonably sized TLog file for the database will reduce the possible impacts of the database having to re-grow the TLog file again, because you shrank it. You could say basically, that your shrinking of the TLog file is the root cause of the users' issues. If the SQL Server is having issues with TLog autogrowth you should see an Error 5144 or 5145 in the ERRORLOG. – John K. N. Mar 04 '16 at 14:35
  • 1
    Well, a simple job to truncate the tlog periodically isn't the right solution to this either. (*Part* of the solution, sure, but only part. You should probably adjust the actual autogrow settings for this database so that it's not going to grow, and when it does, it's not impacting system performance. With the root problem solved, there's no point in sending out an email about a non-impacting event like a database growing.) – HopelessN00b Mar 04 '16 at 14:38
  • @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. – mgrenier Mar 04 '16 at 15:12
  • @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. – mgrenier Mar 04 '16 at 15:14

3 Answers3

3

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.

Katherine Villyard
  • 18,510
  • 4
  • 36
  • 59
1

What you are trying to do is a horrible idea. You should rarely be truncating your logs, if at all. Log growth is normal. If you are experiencing huge unexpected log growth, you have code or user issues. If you are in simple mode and don't require transactional backups, simply schedule regular nightly backups and the log gets reused. If you are in FULL recovery, schedule regular transactional log backups and nightly FULL backups and you solve your problem.

You'd be much better served managing your logs in this fashion than by truncating them regularly.

Steve Mangiameli
  • 186
  • 6
  • The only reason I mentioned the truncation is because the SQL Server does this automatically with a Transaction Log backup, I am not explicitly doing so. Again, I believe the backups will take care of most of the issue but because of the issue happening in the past I have been give the requirement to notify users if the DB enters an autogrow state which is what I am attempting to accomplish. – mgrenier Mar 04 '16 at 15:49
  • 2
    Actually, SQL Server does not truncate the log on backup. It does, however, issue a checkpoint allowing the log to be reused. – Steve Mangiameli Mar 04 '16 at 18:46
0

I think that @Katherine Villyard has summed it up pretty good.

You can take some assumptions from your existing database. Leave the database's transaction log size as big as it is (or as big as it gets during that freak transaction) and ensure you have "Auto_shrink" option turned off in the database's settings.

BOL:

When set to ON, the database files are candidates for periodic shrinking. Both data file and log files can be shrunk automatically by SQL Server. AUTO_SHRINK reduces the size of the transaction log only if the database is set to SIMPLE recovery model or if the log is backed up.

When set to OFF, database files are not automatically shrunk during periodic checks for unused space.

Here is an article I found about Auto-shrink – turn it OFF! and the same article in the SQL Server Database Engine Blog

You have to track the transaction log usage for your database and the events that are causing the database's tlog to grow and size it accordingly.

Community
  • 1
John K. N.
  • 1,955
  • 1
  • 16
  • 26