August 7, 2012 at 2:26 pm
Hi,
We have log shipping configured in SQL Server 2008 R2 SP2.
Log shipping copy job is failing and I'm getting the below error most of the times when I'm trying to see the view history for that job.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
and sometimes it's showing the actual error as below:
Message
2012-08-07 13:01:52.03*** Error: The handle is invalid.
(mscorlib) ***
Please advise.
August 7, 2012 at 4:31 pm
This may be the GUI tming out trying to build the job history due to too much volume, or a slow or overburdened server. Does your server generate a lot of job history? When is the last time you cleaned it out?
-- delete all job history older than a couple weeks, adjust to suit
DECLARE @dt DATETIME ;
SET @dt = DATEADD(day, -15, GETDATE())
EXEC msdb.dbo.sp_purge_jobhistory
@oldest_date = @dt ;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 23, 2012 at 9:05 am
even i am facing the same problem..and the sql server machines i have are..newly created ones,how do i have any job history....?
please recommend any other solution....
thank you
P.Naveen kumar reddy
August 23, 2012 at 9:12 am
How new? Log Shipping jobs can fill up the job history in a hurry. So can jobs with very frequent schedules, e.g. run every 15 seconds. Have you checked the job history directly via the tables in msdb? There are many reasons why the UI can timeout. Is your server extremely busy?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 23, 2012 at 11:12 am
not at all busy......it's all a new configured ones....no transaction ran except a few before a full backup taken.....no work currently is being done on it.............?
August 23, 2012 at 11:28 am
Did you check the job tables to see what was in them? If you're running replication or log shipping or other subsystems you might be surprised at what is in there.
Like I said, if it's not job history it could be any number of other things including your install of SSMS or other client/network conditions.
Google for ssms times out job history and you may find a nice workaround or two that you like better.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 23, 2012 at 11:36 am
yes i did check everything.....after all i didn't configure any.....?i have all protocols,remote admin connections,query governor cost limit enabled for sure.....
August 23, 2012 at 11:40 am
All I can say is look for a workaround because there is no way for us to know what is happening in your environment as there are too many variables.
If you see the results of the link I posted there is a Connect item open for this exact issue and Microsoft says performance was improved in SSMS 2012.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 23, 2012 at 12:05 pm
a last thing....what would be the possible error reason for a newly installed sql server machine with no job alerts n nothing in it....?if i am configuring it for the first time.............?
thank you
P.Naveen kumar reddy
August 23, 2012 at 12:32 pm
Could be lots of things. Some are related to SQL Server, some related to your environment at large, e.g. network issues, slow server in general due to non SQL Server processes, blocking on the server due to activity in msdb although you're saying there are no scheduled jobs (which begs the question, why are you trying to view job history). The issue you're experiencing is usually related to an over-abundance of job history, however if that is not the issue then there are simply too many possibilities and without having direct access to your environment to start testing some things I cannot even venture a valid guess at what it could be...good luck.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 27, 2012 at 1:37 am
Mani-584606 (8/7/2012)
Hi,We have log shipping configured in SQL Server 2008 R2 SP2.
Log shipping copy job is failing and I'm getting the below error most of the times when I'm trying to see the view history for that job.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
and sometimes it's showing the actual error as below:
Message
2012-08-07 13:01:52.03*** Error: The handle is invalid.
(mscorlib) ***
Please advise.
You question has two questions inside it:
1) Log shipping job fails intermittently: This could be due to the network issue. If I were you, I would have kept a report file for each job and the report file can give us some clues about the job failure.
for me, it has often been: 'specified network no longer available ' or 'insufficient disk space'
2) You try to open up the job history and it times out- Is something blocking your msdb tables? Are the history tables becoming too big? Try opening this history again and side by side Run sp_who2 or sp_WhoIsActive procedure to find out what process is blocking you.
Thanks
Chandan
June 14, 2018 at 6:45 am
This is the case and solution to my server a SQL Server 2008R2 installation.
Cause of pain, the job agent history log was let unlimited, none of the history ticks were ticked.
I was fighting on two fronts, small HDD size and large MSDB and TEMPDB system databases.
1. checked MSDB size, Data 7gb Log 33gb - this was due to running the sp_purge_jobhistory - bloats MSDB and tempDB
2. checked via script the msdb for large objects; table dbo.sysjobhistory had loads of rows and size wise was the bulk of the 7gb MSDB.
3. i made sure i can shrink tempdb (restart sqls erver) and MSDB by putting it to single mode, shrink and multi again.
4. Kept run sp_purge_jobhistory with shorter and shorter 'oldest date' keeping an eye on size of MSDB and TEMPDB shrinking as i go. this was probably not essential but i am a bit careful with large purges. you could try one off, keep only 14 days for example.
5. rerun the object dbo.sysjobhistor size check in MSDB, noticed that the actual size was due to the table index being huge although the rows are not only 100.
6. open mdsb table dbo.sysjobhistory and found the two index the clustered and the non clustered; right click and check fragmentation. 96% - ticked Reorganise.
7. right click on MSDB and task shring database - this shrunk the msdb to a mnimum size 120 MB
Went on Sql Agent job and clicked for the history - no time out and faster than ever!
MSDB Object size code.
USE msdb
GO
SELECT TOP(10)
o.[object_id]
, obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
, o.[type]
, i.total_rows
, i.total_size
FROM sys.objects o
JOIN (
SELECT
i.[object_id]
, total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
, total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
FROM sys.indexes i
JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
WHERE i.is_disabled = 0
AND i.is_hypothetical = 0
GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U', 'S')
ORDER BY i.total_size DESC
June 14, 2018 at 11:36 pm
Thanks for sharing that, Leon. A lot of people forget about such things. MSDB IS a database and it does need to be maintained.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply