April 4, 2012 at 6:45 pm
Immediately after a reboot we can access the properties of the tempdb. However, a few minutes later when you try to examine those properties you get:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
Property Size is not available for Database '[tempdb]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Size&LinkId=20476
==============
I'm not sure if this is a symptom or the problem but this server and db are experiencing timeouts for inserts and updates. The vendor is working on it but I'm hoping we could lend some assistance. None of our other servers that do much higher volumes have this issue. We've been over and over network, memory etc. Any ideas or thoughts are appreciated.
April 4, 2012 at 7:44 pm
This is not a lot of information to go on. How are you trying to see the database properties and what properties are you looking at? e.g. File size? Also when you first can see the properties what values are you getting?
What are the TempDB file sizes and growth factor? Have you tried using Management Studio to check the properties? sp_helpdb 'tempdb'is a good place to start.
Also what are you seeing in the SQL error log?
It sounds a bit like the tempdb files are being expanded but there is a disk performance issue so this is taking longer than expected, but I'm taking a shot in the dark here.
More information may help.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
April 5, 2012 at 7:33 am
thanks the tempdb info is:
namedb_sizeownerdbidcreatedstatuscompatibility_level
tempdb 775.06 MBsa2Apr 4 2012Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=655, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics100
and
namefileidfilenamefilegroupsizemaxsizegrowthusage
tempdev1D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdfPRIMARY194816 KBUnlimited10%data only
templog2D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\templog.ldfNULL598848 KBUnlimited10%log only
the only sql error is the one that is referrenced by this kb and message:
http://support.microsoft.com/kb/2543687
Error: 18056, Severity: 20, State: 29.
The client was unable to reuse a session with SPID 96, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
The error does not seem to perfectly correspond to user timeouts. I've looked through sql monitor and it shows alerts but they seem to be symptoms and not causes as near as I can tell. I "feel" like the vendor has a table(s) that have crossed a boundary of too many rows and now their process can't finish reliably with a failure or contention.
Thanks for your input
April 9, 2012 at 6:11 pm
Rhosenfeld (4/4/2012)
this server and db are experiencing timeouts for inserts and updates.
I'd investigate the timeouts first as I suspect the TempDB issue is a symptom of other issues. The sort of thing you could test is to stop the application that's getting timeout issues and see if the symptoms go away.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 18, 2013 at 2:51 am
Hi,
I have had the same issue. The database owner is set for my tempdb.
Did you ever find the cause of your problem?
Thanks
Kevin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply