?
Solved

tempdb property size is not available

Posted on 2015-02-16
7
Medium Priority
?
608 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 27

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
Suggested Courses

800 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