October 2, 2015 at 11:36 am
I need a script to Backup & Shrink tempdb.
namesize
tempdev1024
templog64
tempdev21024
tempdev31024
tempdev41024
tempdev51024
tempdev61024
tempdev71024
tempdev81024
I can't believe how many tempdb's there are?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 2, 2015 at 11:53 am
Welsh Corgi (10/2/2015)
I need a script to Backup & Shrink tempdb.namesize
tempdev1024
templog64
tempdev21024
tempdev31024
tempdev41024
tempdev51024
tempdev61024
tempdev71024
tempdev81024
I can't believe how many tempdb's there are?
I know many places build SQL Server instances with this many tempdb data files, and at this size to start off with. What problems are you having that you want to shrink / reduce these?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 2, 2015 at 12:06 pm
Welsh Corgi (10/2/2015)
I need a script to Backup & Shrink tempdb.namesize
tempdev1024
templog64
tempdev21024
tempdev31024
tempdev41024
tempdev51024
tempdev61024
tempdev71024
tempdev81024
I can't believe how many tempdb's there are?
Because at some point someone probably read an article recommending the creation of tempdb files equal to the number of processor cores. 8 cores = 8 tempdb files.
What problems are you trying to solve?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 2, 2015 at 12:33 pm
Tempdb keeps growing because a Report Writer uses extensive uses of #temp tables
It is currently at 87,026 GB with 99% free.
I grew to 113 GB last week with 99% free.
You can' backup tempdb but someone told me that they used to do that and shrink a long time ago.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 2, 2015 at 5:15 pm
You can't back up TempDB and shrinking it is pointless if there's a process which uses a lot of TempDB, it'll just grow again. It's like complaining that the 20 litre rain barrel gets full and deciding to replace it with a 5 litre.
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
October 3, 2015 at 4:33 pm
GilaMonster (10/2/2015)
You can't back up TempDB and shrinking it is pointless if there's a process which uses a lot of TempDB, it'll just grow again. It's like complaining that the 20 litre rain barrel gets full and deciding to replace it with a 5 litre.
I was asked to backup tempdb and said that you can't do that.
They said that they used to diid many years ago,
I told shortly after that it can't be done,
There is 99% od free space on the log file,
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 3, 2015 at 7:55 pm
Welsh Corgi (10/3/2015)
GilaMonster (10/2/2015)
You can't back up TempDB and shrinking it is pointless if there's a process which uses a lot of TempDB, it'll just grow again. It's like complaining that the 20 litre rain barrel gets full and deciding to replace it with a 5 litre.I was asked to backup tempdb and said that you can't do that.
They said that they used to diid many years ago,
I told shortly after that it can't be done,
There is 99% od free space on the log file,
If someone told you that they did it years ago, ask them how they did it.
As for shrinking tempdb, it grows as big as it need to be to do the workload and it doesn't shrink back down. You can revert it back to its initial size by restarting the SQL Server service. But, just like Gail said, unless you find and fix what's causing it to grow, it'll just grow again and you'll be in the same exact position you're in right now. Looking on the bright side, if it's 113 GB and mostly free space, at least it won't have to grow again to handle the workload you're throwing at it that caused it to grow.
October 4, 2015 at 4:48 am
Welsh Corgi (10/3/2015)
GilaMonster (10/2/2015)
You can't back up TempDB and shrinking it is pointless if there's a process which uses a lot of TempDB, it'll just grow again. It's like complaining that the 20 litre rain barrel gets full and deciding to replace it with a 5 litre.I was asked to backup tempdb and said that you can't do that.
They said that they used to diid many years ago,
They're probably mis-rmembering. TempDB cannot be backed up. There's no point anyway, it's a temporary storage space, there's no reason to want to back it up.
There is 99% od free space on the log file,
That's not a problem.
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
October 5, 2015 at 9:30 am
When you look at tempdb you will often find a very high % of free space. This is normal, because you are looking at the current usage of tempdb, you are not looking at the transaction that caused tempdb to grow to the size it currently has. It is the same a looking outside after it has stopped raining and seeing it is sunny and wondering why the street is wet.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply