SQL Server DBA Tutorial 46-How to Reduce TempDB Size without Restarting SQL Server Services

welcome to tech brothers in this video we're going to learn how to reduce temp DB size without restarting sequel server services and we'll be using two ways to do it one is secret server management studio graphic user interface and other is using T sequel script and I'm connected with my sequel server instance using sequel server management studio in order to go to temp DB you need to expand your databases you need to expand your system databases and here is temp TB let's go ahead and take a look at the current size of temp TB we're gonna go ahead and right click and go to properties of temp TB as you can see right here the size is ten point seven five in my case and available space is 7.57 MB in my case keep in mind this size is basically data size data file size and log file size together of 10 TB so we are interested to find out how much the data files of 10 PB holds and how much the log file of temp TB holds so let's go ahead and cancel this in order to find the file size you need to right click on empty B go to task go to shrink and go to files first up here as you can see the file type is data and file name is temp def usually in production system you have more than one data files in temp DB and if you are looking into different data template empty the data file you need to click on file name and choose your desired data file name right here in my case I have only one data file which is temp tab and currently allocated space of my data file is 10 MB and 59 percent of my of my space is free 59 percent and up here as you can see the minimum is required 5 MB keep in mind if minimum required required space is equal to this number right here currently currently allocated space then your this method is not going to help you to reduce step depth that means your temp TB basically is being used used sessions are open in there and that space is needed and it will not reduce any size in my case I'm taking a scenario that sessions came in used temp DB my temp DB file might empty besides men up to 10 MB and sessions are completed successfully now minimum required is 5 MB and I want to retrieve some of the space basically for from my temp TB data file so this method is going to help you in that case so let's go ahead and click reorganize pages before releasing the space I'm going to go ahead and use 10 up 8 up here and I'm going to go ahead and retrieve a couple MB back from this is just a demo or in your case it might be huge size and you want it reduces reduce it to close to or equal to your minimum required size if you ever want to basically shrink the tab DB file so let's go ahead and click OK and now we're going to go back and see if it really shrunk as you can see the currently allocated space reduced from 10 MB to 8 MB and minimum required is 4 MB so we still have a room to basically retrieve some space so we're going to go ahead and reduce the size equal to probably 5 MB or 6 MB and for that we'll be using our T sequel script so I'm going to cancel this and I'm going to go ahead and load our script it is really easy script I'm using shrink file and up here is the file size in MB keep in mind that desire size right here needs to be equal or more than what you're shrinking then minimum required in temp DB so in my case it's 8 MB and I'm trying to shrink to 10 MB that's not gonna work so I'm going to go ahead I know the minimum required size was 4 MB basically when you run this command it will tell you that what is the required size so I'm going to go ahead and shrink it to 6 MB because I want to retrieve couple more embiez back so let's go ahead and run this command if you notice right here it says that minimum size required right here is 512 and current size is 760 so let's go ahead and take a look on the shrink on the data size of temp TB let's see that if it goes back to 6 MB it went back to 6 MB so click on file as you can see right here is currently allocated space right here is 6 MB if you're interested to take a look on the log file of 10 baby temp TB I'm sorry and click on file a file type and choose log right now the minimum required log is 0 so I don't need to basically shrink that but in just just in case if you wanted to shrink your log file of temp TB you can go ahead and use the same method to shrink your log file so basically this is it this is how you reduce your temp DB size without restarting your sequel server basically basic method back behind the scene is shrink DB just like you shrink any other DB in your database user DB database so we use sequel server management studio graphic user interface and we used T sequel script to reduce the size of temp TB and I hope this video helps

Channel: TechBrothersIT

Views: 42,825

Rating: 4.7647057 out of 5

Keywords: How to Reduce TempDB Size without Restarting SQL Server Services, SQL, Server, Developer, TSQL, DBA, SQL Developer, SQL Server Interview Questions, TSQL Interview Questions and Answer, transact sql, SQL Server 2014, SQL Server Advance Tutorial, SQL Server Tutorial for beginners, SQL Server 2014 DBA, SQL Server DBA tutorial for Beginners, SQL Data Administration Tutorial Step by Step, Database Administrator (Job Title), TechBrothersIT

Id: -tx8bz-rvCA

Length: 5min 35sec (335 seconds)

Published: Wed Apr 15 2015