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

Improve this question
13
  • Why the down vote? Is this not where I would ask such a question? Commented Mar 3, 2016 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. Commented Mar 3, 2016 at 20:20
  • net send ??? ;) Commented Mar 4, 2016 at 1:32
  • 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. Commented Mar 4, 2016 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.) Commented Mar 4, 2016 at 14:38

3 Answers 3

Reset to default
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.

Improve this answer
5
  • 1
    Thank you for your response, I believe there is enough info in here to come up with a good solution. Commented Mar 7, 2016 at 13:48
  • You may want to revise your statement on Instant File Initialization. This only works with the data files. The log files will still be initialized with each growth regardless of the security policy setting. Commented Mar 7, 2016 at 14:44
  • Yeah, I actually already said that. "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." Commented Mar 7, 2016 at 15:27
  • 1
    Sorry, missed the "mdf" part of that sentence. I was focused on the log. Commented Mar 7, 2016 at 18:54
  • It's an important point, yeah. Commented Mar 7, 2016 at 19:10
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.

Improve this answer
2
  • 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. Commented Mar 4, 2016 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. Commented Mar 4, 2016 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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.