database log size, log shipping and performance
-
Please execute the following against the sql server instance and post back the results
selectst.text,
start_time,
DATEADD(ms,estimated_completion_time,
GETDATE()) AS EstimatedEndTime,
percent_complete,
((estimated_completion_time / 1000) / 60) as EstCominMins,
((total_elapsed_time / 1000) / 60) as ElapsedMins
from sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as st
where command like 'Backup%' or command like 'restore%'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
I am interested at looking at indexf ragmentations. Please run the below query and post the results
select b.name, b.type, c.name, a.*
from sys.dm_db_index_physical_stats (db_id(), 0, null, 0, 'SAMPLED') as a
join sys.all_objects as b
on a.object_id = b.object_id
join sys.indexes as c
on a.object_id = c.object_id
and a.index_id = c.index_id
order by avg_fragmentation_in_percent desc
I belive it sql 2005.
-
prem.budhu (8/15/2011)
ThanksHere is the result from the sys.database
masterSIMPLENOTHING
tempdbSIMPLEACTIVE_TRANSACTION
modelFULLLOG_BACKUP
msdbSIMPLENOTHING
mydbFULLLOG_BACKUP
mydbFULLLOG_BACKUP
The wait telling you to do more log backups.
What’s the frequency of log backup setup in your log shipping?
Yep definitely too many VLF's cause performance problem. Tune your log file put it in reasonable size.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/ -
did you solve your problem?
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply