SQL Server Standard Edition
-
Hi All
Hi have a problem and need help 🙂 I am out of ideas on this one. I am managing my company's Production SQL Server, there are 30 Databases I need to maintain. Specs of the server: Windows Server
my TEMPDB does not want to release the unallocated space and it's getting to a point that shrinks don't work.
I have restarted the server and still no luck. I take incremental's on this server every 10 min and full backup at night with a cleanup job.
The previous DBA set the autogrowth to 500MB \ Unlimited
I have a 200GB drive allocated just for the TEMPDB Files it is now on 32GB free and I don't know how to release the unallocated space 🙁
Is there another solution i am missing? Because this is a PRoduction Server I am very careful to just run stuff.
Thank you very much
-
What version of SQLServer are you on ?
SELECT SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel -- SQL 2014 SP1 CU5
, SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference
, SERVERPROPERTY('ProductVersion') AS ProductVersionWhat is the startup size of tempdb you are expecting ?
/*
Startup size of tempdb
*/
select cast( size * 8.0 / 1024 as Decimal(13,2)) as Size_MB, *
from sys.master_files
where database_id = 2
order by file_id ;What is consuming your tempdb ?
check: "How to monitor tempdb"
Do you use any form of snapshot isolation?
As you can see, your tempdb files are of different sizes, which is not a good practice.
ref BOL
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
What is the oldest open transaction ? DBCC OPENTRAN
-
must you really shrink the TEMP DB, would it not be better to increase the diskspace.
***The first step is always the hardest *******
-
First things first - you should review this document: https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/. If you are on a version lower than 2016 then you might want to enable the trace flags so auto growth is done across all files.
To resize the files to a known good size - assuming there was some unusual/abnormal process that caused the files to grow to their current sizes, then issue a DBCC SHRINKFILE(..., {size}) for each file. For example: DBCC SHRINKFILE(1, 15000)
Restart SQL Server. If the files did not shrink and are still not the same size, issue the shrink file again for each file right after the system comes back up.
For the log file use DBCC SHRINKFILE(2, 500) to reset back to the smallest size.
With the trace flag set - the files should auto grow at the same time. If you run into an issue where tempdb fills the drive - then repeat this process to resize back to original. And - identify the offending process causing tempdb to grow and fill the drive. Fix that process...
If you cannot fix that process - then add more space to the drive, but you should be able to fix that process.
Final note: if your tempdb regularly grows out to 100GB+ then that is what is needed by the system and you should not worry about shrinking the files. Make sure you have enough space on the drive to accommodate normal daily/weekly/monthly operations.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - jpjacobs207 wrote:
Is there another solution i am missing?
Possibly... what is the size of your largest database?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply