tempdb property size is not available

Posted on 2015-02-16
Last Modified: 2015-02-18

I'm trying to look into the properties of my sql server's tempdb.  I keep getting this error (attached).  This is an online production system so it is under quite heavy load.

This is MS SQL 2012 SP2 running on Windows Server 2012 R2 in a cluster.
Question by:ts11
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40613381
Do you belong to the sysadmin server role?
What property are you trying to view?

Author Comment

ID: 40613394
Yes I am.
I'm just trying to view the properties of the db using sql management studio.
LVL 15

Accepted Solution

Vikas Garg earned 500 total points
ID: 40613524

This is due to some long running query which us using tempdb. so when the query running will finish this error will simply go.
If you want to find that query you can use the profiler while clicking on property for temp db if still that is not visible than  enabled the "User Error Message"
That will give you the query.
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

LVL 26

Expert Comment

ID: 40614193
Run these queries see what you get:
SELECT * FROM fn_my_permissions(NULL, 'SERVER');

Open in new window

first one will give you all permissions you have at server level abd the second one will return 1 if your current login has admin rights.
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40614645
Don't use the GUI for those types of requests.  Instead, use:

EXEC tempdb.sys.sp_helpdb
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40615629
I'm just trying to view the properties of the db using sql management studio.
Can you be a tad more specific?  I mean what specific properties do you need?

Don't use the GUI for those types of requests.
Exactly, that is what I was trying to get at!

Author Closing Comment

ID: 40617613
An open query still had a session open.

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question