February 24, 2021 at 12:24 pm
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 .
February 24, 2021 at 1:50 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 24, 2021 at 2:03 pm
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 .
February 24, 2021 at 2:05 pm
This was removed by the editor as SPAM
February 24, 2021 at 8:39 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 24, 2021 at 9:05 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 25, 2021 at 5:39 am
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply