September 12, 2019 at 12:30 pm
I have 2 script which I want to run in our production environment since we don't have a monitoring tool in place and I have a feeling that there is a runaway query which is filling up the log file. The log file grows to be 300+ sometimes. We use DPM (Microsoft product) to take log backups (I am not sure how DPM works as of right now but I know taking a regular t-log backup shouldn't grow the log this much if there is no open transaction). I am trying to capture mdf and ldf file size every hour for each DB and I am trying to capture queries which are running for over 5 minutes. This server is part of AG and I have been instructed to run these 2 scripts on the secondary server but I am not sure about that. I think i should run it on the primary since I want to capture DML operations. Any advise from experts is highly appreciated.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
September 12, 2019 at 12:34 pm
Attaching files
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
September 12, 2019 at 2:27 pm
These will capture information, and it may be useful here. However, the length of the query doesn't necessarily impact the log growth. It can, but the log growth is a result of the amount of data being logged. A large SELECT query will not necessarily change the db log file. It might change tempdb, but the log doesn't grow unless changes are made to data. This can be one transaction or many transactions. The log grows to accomodate changes between log backups. The size ought to be the largest load between log backups.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply