T-log Alert

  • i need to configure alert on multiple servers  when the t-log reaches or exceeds threshold limit . i dont want to configure alert on each server   for this . how can we configure for all servers in a single script  and request to guide  to achieve this .

  • Pretty sure you are going to need to configure that per-server.  The servers don't share any configuration information, so you would need to set that on on a per-database level.

    Alternately, if you have a database monitoring tool, those should have methods to configure tlog alerts.  RedGate I know does for sure and I expect that the others on the market do as well.

  • i dont have any db monitoring in place ,  i need to configure alert of t-log utilisation exceeds the limit  of all servers in one server .i.e on central server .

  • This was removed by the editor as SPAM

  • My recommendation is going to still be get a monitoring tool.

    If that is not an option, you are going to need a linked server or SSIS or something in place so your central server can query all of the other servers and get the information.  And what will make that fun is what sort of limits are you putting in place?  is it a generic limit (like when less than 10% is free in the log send an alert) OR is it configured per server?

    Personally, I like the configured per server approach as having 10% free on a 4 GB tlog is a lot different than 10 % free on a 1 TB tlog.  And it is likely that your databases have different configurations for tlog size.

    The other fun part about that is your central server is going to need a scheduled job to handle the data retrieval and alerting.  So you will also need to determine how frequently it needs to run AND make sure you don't have overlap.  You also need to decide if you care about historical data or not as that will change the design as well.

    My opinion, you are reinventing the wheel and would benefit from a DB monitoring tool.  But if that is an approach that you have no control over, I would decide if you want to use linked servers OR SSIS or some other method.  The pain in the butt part is that any time you make a new database, you will likely need to update the process.  Going with the linked server approach,  you can query [LinkedServer].[master].[sys].[databases] to get the list of databases then some fun dynamic SQL to query all of the database sizes (using the DMV sys.dm_db_log_space_usage).

    The problem that MAY come up from this is that DMV isn't available in all versions of SQL Server (2008 R2 for example doesn't have it).  But if all of your SQL instances are 2016 or newer, you should be good to go.

  • SQL Server Agent provides Alerts that can be configured to do that.  However, they are per server and, indeed, per db for log file counters.

    The default trace also contains entries for autogrowth, which could trigger you to go check that db and see if the log size is not larger than you want / has grown more than you want it to.

    I don't know of any way to make all these checks for multiple instances with a single script.  That's a huge request.

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • We monitor the size of TLog backup files - as an indicator of any database that is having way more TLog activity than normal and, indeed, if an individual backup is "big" relative to TLog configured size.

    I only mention it in case your underlying requirement might be answered via that route. We did it that way 'coz it was quick and easy. We did that years ago expecting to replace it with something much more slick ... but have never got around to it!

Viewing 7 posts - 1 through 7 (of 7 total)

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