February 13, 2009 at 5:05 pm
We have 6 or 7 databases set up for logshipping. The MSDB write % is very high compared to all databases, even our main database. Can anyone explain why it would be so great when the database is in Simple Recovery mode? I'm assuming logshipping is the culprit since MSDB is 2GB and the log_shipping_monitor_history_detail table is > 1.5GB.
% WritesWrites Write Wait Time (ms) Avg Write Wait (ms)% Total IO
main_db 6.44% 265502 10865530 40.9 19.23%
jira 0.00% 188 1822 9.7 0.02%
LiteSpeedCentral0.05% 2096 5173 2.5 0.05%
LiteSpeedLocal 39.75% 1637875 16416446 10.0 32.81%
Maintenance 0.00% 6 10 1.7 0.00%
master 0.03% 1290 1543 1.2 0.03%
model 0.00% 10 16 1.6 0.00%
msdb 47.07% 1939637 5626323 2.9 41.99%
% WritesWrites Write Wait Time (ms) Avg Write Wait (ms) % Total IO
MSDBData 6.16% 119532 2986681 24.98645551 14.07
MSDBLog93.84% 1820105 2639642 1.450269078 85.93%
Sorry for the messy table format, can't seem to get it aligned in here.
Thanks,
Scott
February 14, 2009 at 4:37 am
Simple recovery has no effect on what's written to a DB's log. Just how long it's retained after it's written.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2009 at 6:31 am
digitalox (2/13/2009)
We have 6 or 7 databases set up for logshipping. The MSDB write % is very high compared to all databases, even our main database. Can anyone explain why it would be so great when the database is in Simple Recovery mode? I'm assuming logshipping is the culprit since MSDB is 2GB and the log_shipping_monitor_history_detail table is > 1.5GB.% WritesWrites Write Wait Time (ms) Avg Write Wait (ms)% Total IO
main_db 6.44% 265502 10865530 40.9 19.23%
jira 0.00% 188 1822 9.7 0.02%
LiteSpeedCentral0.05% 2096 5173 2.5 0.05%
LiteSpeedLocal 39.75% 1637875 16416446 10.0 32.81%
Maintenance 0.00% 6 10 1.7 0.00%
master 0.03% 1290 1543 1.2 0.03%
model 0.00% 10 16 1.6 0.00%
msdb 47.07% 1939637 5626323 2.9 41.99%
% WritesWrites Write Wait Time (ms) Avg Write Wait (ms) % Total IO
MSDBData 6.16% 119532 2986681 24.98645551 14.07
MSDBLog93.84% 1820105 2639642 1.450269078 85.93%
Sorry for the messy table format, can't seem to get it aligned in here.
Thanks,
Scott
I am thinking its because you have Log shipping configured and all the jobs are wirtten to the log_history table.
If the msdb size is an issue you can clean up the tables if you want or you can shrink the msdb database to reclaim some space.
Are you taking any backups of your system databases then?
February 14, 2009 at 10:14 am
Yes all the system (and user) databases are backed up nightly. My concern is that the msdb hitting the log LUN that heavily could be stealing some resources the user databases need - we have plenty of space it can grow all it wants. I'm just surprised its surpassing the user databases by multiples in terms of write usage. I'll dig a little deeper and see what exactly is doing this.
February 14, 2009 at 10:18 am
since your have log shipping on 6 databases. You can set up a job in your maintenance plan to clean up the log_shipping_history_job tables as your maintenance.
February 14, 2009 at 10:20 am
GilaMonster (2/14/2009)
Simple recovery has no effect on what's written to a DB's log. Just how long it's retained after it's written.
Good point, not sure why but I've always been under the impression you had less logging with simple. I'm more curious what its logging so much. Our user database is pretty busy so to see it writing 5x more to log than user database seems odd.
February 14, 2009 at 10:27 am
Krishna (2/14/2009)
since your have log shipping on 6 databases. You can set up a job in your maintenance plan to clean up the log_shipping_history_job tables as your maintenance.
Thanks, I'm planning to add that to the weekly maintenance run.
February 14, 2009 at 10:39 am
digitalox (2/14/2009)
Krishna (2/14/2009)
since your have log shipping on 6 databases. You can set up a job in your maintenance plan to clean up the log_shipping_history_job tables as your maintenance.Thanks, I'm planning to add that to the weekly maintenance run.
Before this you also need to analyze the data in the tables? Do you need them for auditing purposes or for anything related to your business needs?
May be you can create a table to load all that information from into a table before cleaning up and you can set up a second job for cleaning the any redundant info in the table you created do that you store only required data that is neede according to your SLA's
February 14, 2009 at 1:52 pm
Thanks so much for all the feedback.
--
Scott
February 14, 2009 at 5:40 pm
Our pleasure! and let us know how u did go about it and what did you land on finally
February 16, 2009 at 8:25 am
digitalox (2/14/2009)
GilaMonster (2/14/2009)
Simple recovery has no effect on what's written to a DB's log. Just how long it's retained after it's written.Good point, not sure why but I've always been under the impression you had less logging with simple.
I just want to check that we're talking about the same kind of logging, that which goes into the .ldf (transaction log) file.
With the exception of bulk operations (bulk insert, bcp, index rebuilds, etc) simple and full have exactly the same logging behavior. The only difference is that in simple, the lg is truncated on checkpoint while in full it's truncated on log backup.
If you do have bulk operations, then simple logs the same as bulk-logged
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2009 at 11:08 am
GilaMonster (2/16/2009)
digitalox (2/14/2009)
GilaMonster (2/14/2009)
Simple recovery has no effect on what's written to a DB's log. Just how long it's retained after it's written.Good point, not sure why but I've always been under the impression you had less logging with simple.
I just want to check that we're talking about the same kind of logging, that which goes into the .ldf (transaction log) file.
With the exception of bulk operations (bulk insert, bcp, index rebuilds, etc) simple and full have exactly the same logging behavior. The only difference is that in simple, the lg is truncated on checkpoint while in full it's truncated on log backup.
If you do have bulk operations, then simple logs the same as bulk-logged
hey everyone I am sorry to get into the post 😀 but by truncate the log you mean that the logical log will be truncated(the physical file is divided into several logical log files). But the physical log file will NOT change (reduce) it's size. Is that right?
February 16, 2009 at 11:14 am
chileu17 (2/16/2009)
GilaMonster (2/16/2009)
digitalox (2/14/2009)
GilaMonster (2/14/2009)
Simple recovery has no effect on what's written to a DB's log. Just how long it's retained after it's written.Good point, not sure why but I've always been under the impression you had less logging with simple.
I just want to check that we're talking about the same kind of logging, that which goes into the .ldf (transaction log) file.
With the exception of bulk operations (bulk insert, bcp, index rebuilds, etc) simple and full have exactly the same logging behavior. The only difference is that in simple, the lg is truncated on checkpoint while in full it's truncated on log backup.
If you do have bulk operations, then simple logs the same as bulk-logged
hey everyone I am sorry to get into the post 😀 but by truncate the log you mean that the logical log will be truncated(the physical file is divided into several logical log files). But the physical log file will NOT change (reduce) it's size. Is that right?
Yes, you understood it correctly. If you want to reduce the physical size you need to explicitly shrink the file. Truncating the log will remove the inactive portions of the log and will keep the active portions.
February 16, 2009 at 1:29 pm
GilaMonster (2/16/2009)
digitalox (2/14/2009)
GilaMonster (2/14/2009)
Simple recovery has no effect on what's written to a DB's log. Just how long it's retained after it's written.Good point, not sure why but I've always been under the impression you had less logging with simple.
I just want to check that we're talking about the same kind of logging, that which goes into the .ldf (transaction log) file.
With the exception of bulk operations (bulk insert, bcp, index rebuilds, etc) simple and full have exactly the same logging behavior. The only difference is that in simple, the lg is truncated on checkpoint while in full it's truncated on log backup.
If you do have bulk operations, then simple logs the same as bulk-logged
Yes, Gila I'm referring to MSDB's log file. I did a little tracing and the only thing I saw at quick glance was the MSDB queries that were constantly try to delete the various job history, step tables etc. I looked and the history retention was still at default which was probably to low for the amount of jobs running. I made the following changes but this seems to have had little effect.:
Maximum job history log size rows 1000 -> 50000
Maximum Job History rows per job 100 -> 5000
Next items to look at when I have a few are
1) the jobs themselves to see if someone has something crazy going
2) LiteSpeed, which we're using for logshipping, to see if its possible to change anything in the logging levels
February 18, 2009 at 1:41 pm
I've finally figured this out for the most part.
When Litespeed's logshipping initially fires up at its interval, it proceeds in an orderly fashion creating log files and cleaning up. While its doing so it deletes the entries for the stale files from it's own litespeedbackupfile based on the retention period.
Then all hell breaks loose. The ID Litespeed is running as didn't have Delete permissions to the logshipping folder on the network share. And it is smart enough (if you want to call it that) such that if it failed to delete the file, it leaves it in the table and tries again on the next run. This has been going on for months and I didn't realize it - it raised no errors in the system or sql log files. So we had over 30k files in the table that it was trying to delete every 15 minutes. Additionally I believe the Litespeed Logshipping logging was on verbose , and it was just creating a mass of entries in its own history tables during this.
I'm still not sure why MSDB's log_shipping_monitor_history_detail needs 100k rows when I've chopped the retention all the way down to 6 hours (~8 DBs shipped every 15 mins.), but there's probably less I can do about that one.
Thanks everyone.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply