Why are my logshipping files so large?

Question

Why are my logshipping files so large?

digitalox

SSCommitted

Points: 1830
More actions
March 13, 2014 at 3:28 pm

#305087

Hello
I noticed some restore latency > 30 mins. on one of our databases today. Checked the logshipping transaction files and they are ~1.5 GB every 10 minutes.
I tried to do some profiling to see what might be the main cause but it seems the "writes" column is almost always 0. Any ideas on how I might narrow down the offending activity? I can't see why it would be this high.
Also I tried using a "select top * fn_dblog..." type query but after waiting over 30 seconds for it to return I got nervous and cancelled it. This is a production server.
Thank You,
Scott

Viewing 13 posts - 1 through 12 (of 12 total)

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

Is there any chance there was index, or statistics maintenance going on during this time?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

If everything going fine do not worry. This is because of maintenance task. And I guess it happens after reindex?

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

digitalox SSCommitted Points: 1830 More actions · Answer 3

Thanks for the replies. No, this is happening continuously. It is not the maintenance.

The database is used by a third party application. If I could just tell which queries are doing most of the writes or maybe even which tables are getting updated the most.

digitalox SSCommitted Points: 1830 More actions · Answer 4

I tried using this query which does tell me what is getting updated most frequently. But those are not necessarily the same tables. I could have very large writes going to a different table that are less frequent.

The thing is that the IO was on a steady predictable climb and then suddenly doubled, and logshipping starts to lag more as a result, some blocking has increased etc. I am trying to get an idea of what area of the database is being affected so we know what area of the application to start investigating.

select object_name(object_id) objname, * from

sys.dm_db_index_usage_stats s

where database_id = 6

order by s.user_updates desc

thanks

scott

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

what do you see if you query the active processes, anything apparent there

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

digitalox SSCommitted Points: 1830 More actions · Answer 6

The instance is pretty busy and when I query i see mostly reads, using the insane adhoc sql that this application likes to send.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 7

Do you have any Application Jobs\Processes running during this period ? Start a server side profiler trace by appling filter on that DB and App ID and see what's the major activity. This will give you some insight into the happenings.

--

SQLBuddy

digitalox SSCommitted Points: 1830 More actions · Answer 8

But I need something quantifiable to tell me where the largest Create/Update/Delete activity is coming from or where its occurring.

I kinda get the "where" with this:

SELECT object_name(object_id) objname, * FROM sys.dm_db_index_operational_stats(6, NULL, NULL, NULL)

order by leaf_insert_count + leaf_delete_count + leaf_update_count desc

;

Still not sure how to correlate these counts with sizes though. I guess I would have to write some ugly sql and join it with dbcc log to know for sure. It would be interesting but time consuming and I'd be nervous to run it on the box. The tables/indexes that I suspected are the same ones bubbling to the top of the query above so I guess that will have to do for now.

Thanks

Scott

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 9

Is this database acting as a publisher? Or do you have CDC enabled? What does the log_wait_reuse_desc say?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 10

sqlbuddy123 (3/14/2014)
Do you have any Application Jobs\Processes running during this period ? Start a server side profiler trace by appling filter on that DB and App ID and see what's the major activity. This will give you some insight into the happenings.
--
SQLBuddy

It's good to find "Where it's occurring" like identifying the affected objects. But how about "WHY it's occuring " .. If you know this you can prevent that from happening if poosible ..

--

SQLBuddy

digitalox SSCommitted Points: 1830 More actions · Answer 11

The database is the publisher of logshipping. CDC is not enabled. log_reuse_wait_desc is LOG_BACKUP.

Thanks

Scott

digitalox SSCommitted Points: 1830 More actions · Answer 12

The database is the publisher of logshipping. CDC is not enabled. log_reuse_wait_desc is LOG_BACKUP.

Thanks

Scott