September 25, 2014 at 9:45 am
Environment Details:
DB is set to FULL:
TLOG backup every hour:
Size of the .ldf file is 9GB, autogrowth by 10%.
Just a minor issue:
Something ran last night in the DB, which filled up log file quickly. Is there a way to find out information about which job, or what was running that produced good amount of log etc. I also just started using SQL Diagnostic Manager 2 days ago to monitor the environment, but don't know where to go and find out the details about the log file. Any help will be 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 25, 2014 at 2:46 pm
The log fills with transaction activity. Something created lots of log records, so you'd have to find a lot of data changes or inserts. (or maintenance).
The log clears with log backups, except if mirroring or replication are not caught up. In which case, the log cannot clear.
Check for those things
September 25, 2014 at 3:11 pm
Steve Jones - SSC Editor (9/25/2014)Something created lots of log records, so you'd have to find a lot of data changes or inserts. (or maintenance).
Check for those things
How do I even find out, what's running which is causing tons of log or what ran(in case I am not around my work station)?
"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 25, 2014 at 3:53 pm
no simple way.
If you have modified dates in your tables, check those.
If you track insert dates, look there.
Check on sizes of log backups. Larger ones may help you determine times of more activity.
Set up a Trace to record insert/update activity
Run counts on tables and track this.
As far as replication or mirroring, you'll have to dig in and look. These may help
http://stackoverflow.com/questions/8045127/check-if-replication-enabled
September 25, 2014 at 4:12 pm
Quick questions, have you any checks on long running transactions? Any changes or new implementations? Why is autogrowth set to 10%?
😎
September 25, 2014 at 4:16 pm
Eirikur Eiriksson (9/25/2014)
Quick questions, have you any checks on long running transactions? Any changes or new implementations? Why is autogrowth set to 10%?😎
We usually have a job which runs twice a week, deletes old records which generates good amount of log, but that job didn't run last night. It is set to 10% because we only have 20GB left on that drive.
I also started a trace just now and it will run until midnight.
"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 25, 2014 at 4:24 pm
New Born DBA (9/25/2014)
Eirikur Eiriksson (9/25/2014)
Quick questions, have you any checks on long running transactions? Any changes or new implementations? Why is autogrowth set to 10%?😎
We usually have a job which runs twice a week, deletes old records which generates good amount of log, but that job didn't run last night. It is set to 10% because we only have 20GB left on that drive.
I also started a trace just now and it will run until midnight.
As the log file grows in the size of the virtual log files, you might want to look into this. You could possibly switch to a bulk for the delete job, may or may not be beneficial depending on other activities.
😎
September 26, 2014 at 3:44 am
You can see some long running queries in the system_health extended event session. It might show you where you had some issues. It might not, but it's something.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply