January 7, 2014 at 3:53 am
How can I clear the used space for TEMPDB database in sql server 2012 w/o restarting the sql service and w/o shrinking it.
Currently my tempDB is 20 GB in size which is huge and due to which some of my scheduled jobs have failed to run.
I tried shrinking the tempDB which doesn't help me.
Awaiting for other answers from you.
Thanks,
Suresh.
January 7, 2014 at 4:33 am
A SQL re-start is probably the cleanest way to do this but if this isn't possible then shrinking is your next option. I can't think of how else you would do this!
You say that you tried to shrink the database but it didn't help you. Why not? Is tempdb still being used? is there enough space in the tempdb mdf file to actually shrink it?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
January 7, 2014 at 4:41 am
Don't restart, it could well be a waste of time.
20GB is not huge for a TempDB and if the files grew to that size is means that TempDB needs to be 20GB to support normal operation. Shrinking TempDB will just result in it growing again and slowing down the workload while it does so
Why are the jobs failing? What's the exact errors that they are giving you?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2014 at 8:20 am
Suresh.. (1/7/2014)
How can I clear the used space for TEMPDB database in sql server 2012 w/o restarting the sql service and w/o shrinking it.Currently my tempDB is 20 GB in size which is huge and due to which some of my scheduled jobs have failed to run.
I tried shrinking the tempDB which doesn't help me.
Awaiting for other answers from you.
Thanks,
Suresh.
What is the proof you have that a 20GB tempdb caused some scheduled jobs to fail or fail to run?? Did the error information on the jobs actually state that?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 8, 2014 at 12:41 pm
If by "scheduled jobs" you are referring to work-load that is using non-SQL mediated disk-space on the same volume containing TempDB, then I'd say your request is just a band-aid to your problem and not a solution for it.
It is not unusual for TempDB to grow periodically, and sporadically (depending on the particular environment {dev, test, qa, staging, release}, and regardless of how well you've done your initial sizing and extension analysis); also, 20GB is not large for TempDB!
IMHO you should:
[h5]Create TempDB appropriately for each environment[/h5]
One rule-of-thumb says that the number of files should be the number of CPU cores allocted to SQL, or 8, which ever is least.
[h5]Run any non-SQL mediated disk accessing jobs on non-SQL volumes[/h5]
Give a man a fish and he will eat for a day. Teach him to fish and he will get fat (if he doesn't use his new bounty appropriately). Extend this metaphor to your environment and run a lean, mean, SQL machine! :smooooth:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply