Solved

tempdb property size is not available

Posted on 2015-02-16
7
444 Views
Last Modified: 2015-02-18
Hi,

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.
sqlerror.PNG
0
Comment
Question by:ts11
7 Comments
 
LVL 75

Expert Comment

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

Author Comment

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

Accepted Solution

by:
Vikas Garg earned 500 total points
ID: 40613524
Hi,

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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 40614193
Run these queries see what you get:
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
SELECT HAS_PERMS_BY_NAME(null, null, 'CONTROL 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.
0
 
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
0
 
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!
0
 

Author Closing Comment

by:ts11
ID: 40617613
An open query still had a session open.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now