August 31, 2015 at 8:43 am
tempdb on a server is at 171 GB with 99% free space.
I tried shrinking the file, the database and using the ALTER Database command.
I tried shrinking the data file from the GUI and eventually I loses my connection.
Any ideas?
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/
August 31, 2015 at 9:05 am
What code did you run?
What I find is that tempdb is hard, because there's usually a load in it. I'd shrink files, not the database, and shrink it in stages. Do not try to shrink it all at once.
Also does this stay mostly free? I'd track this over time. If it bursts back up, you're better off leaving it larger. Not to say this needs to be 171GB, but if it gets to 80GB usage, I'd leave it at 88-100.
August 31, 2015 at 9:29 am
Steve Jones - SSC Editor (8/31/2015)
What code did you run?What I find is that tempdb is hard, because there's usually a load in it. I'd shrink files, not the database, and shrink it in stages. Do not try to shrink it all at once.
Also does this stay mostly free? I'd track this over time. If it bursts back up, you're better off leaving it larger. Not to say this needs to be 171GB, but if it gets to 80GB usage, I'd leave it at 88-100.
I have tried just about everything.
I'm going to have to start the SQL Server Agent Service.
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/
August 31, 2015 at 9:38 am
Welsh Corgi (8/31/2015)
I'm going to have to start the SQL Server Agent Service.
why did you stop it in the first place?
What is the default size for your tempdb files and how many do you have?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 31, 2015 at 9:39 am
You need to restart SQL Server. What is the initial size for tempdb? Also, what code did you run? Did you manually run dbcc shrinkfile?
You do want to be in single user mode. Sorry, forgot to mention that. There can be corruption issues with tempdb.
August 31, 2015 at 9:44 am
Steve,
I ran everything in T-SQL and threw the GUI.
Shrink Database
Shrink Files
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = 1000)
DBCC FREESYSTEMCACHE('ALL') which ran 33 minutes before I killed it.
Thanks for the tips.
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/
August 31, 2015 at 9:52 am
Don't use the GUI.
Use DBCC Shrinkfile( tempdbdata, 1500);
Or something close to the current size. Just get it to run. If it does, then work on slowly shrinking.
August 31, 2015 at 11:39 am
Steve Jones - SSC Editor (8/31/2015)
Don't use the GUI.Use DBCC Shrinkfile( tempdbdata, 1500);
Or something close to the current size. Just get it to run. If it does, then work on slowly shrinking.
Thanks.
I do not like using the GUI.
When I run the Shrinkfile statement it comes back in less than a second, completed.
Very strange.:unsure:
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/
August 31, 2015 at 12:07 pm
TEMPDB will automatically shrink whenever SQL Server is restarted, which might be most reliable way to do this, if needed. However, unless your TEMPDB is competing for storage with other databases, then there is no practical reason to shrink it ever. Ideally, TEMPDB should be allocated on a dedicated disk of it's own and just allowed to grow as needed.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 31, 2015 at 1:45 pm
Eric M Russell (8/31/2015)
TEMPDB will automatically shrink whenever SQL Server is restarted, which might be most reliable way to do this, if needed. However, unless your TEMPDB is competing for storage with other databases, then there is no practical reason to shrink it ever. Ideally, TEMPDB should be allocated on a dedicated disk of it's own and just allowed to grow as needed.
Thank you.
Well I had no disk space available. Before I made my post I was able to free up 13.6 of Disk Space.
I had to restart SQL Server.
There was not a lot of activity on the Server.
tempdb is on a Disk of it's own.
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/
August 31, 2015 at 2:04 pm
Welsh Corgi (8/31/2015)
Eric M Russell (8/31/2015)
TEMPDB will automatically shrink whenever SQL Server is restarted, which might be most reliable way to do this, if needed. However, unless your TEMPDB is competing for storage with other databases, then there is no practical reason to shrink it ever. Ideally, TEMPDB should be allocated on a dedicated disk of it's own and just allowed to grow as needed.Thank you.
Well I had no disk space available. Before I made my post I was able to free up 13.6 of Disk Space.
I had to restart SQL Server.
There was not a lot of activity on the Server.
tempdb is on a Disk of it's own.
Unless it's necessary to free up space on that disk for something other than TEMPDB, then there is no reason to shrink the file. The allocation just gets reused, similar to other .mdf database files, transactin log files, or the Windows swap file.
On one database, I've got a 1 TB TEMPDB file sitting on a 1 TB dedicated disk. A buggy ETL process filled up the disk months ago leaving that tempdb file bloated to the max. I've since refactored the ETL process so it doesn't utilize but a small fraction of tempdb, but since the server hasn't been rebooted, the file is still maxed out. But it doesn't matter; it's actually beneficial not to have the file shrinking and auto growing. Having the tempdb file consuming the entire disk just ensures that no one else has an opportunity to place database files or whatever on a drive that's intended only for tempdb in the first place.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 1, 2015 at 7:08 am
As mentioned if you restart the SQL Service the tempdb is dropped and re-created so if you can restart the service this is a good option. If you need to completely shrink the file because you need to remove it, then the EmptyFile option of the Shrinkfile command will be needed.
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.
September 1, 2015 at 8:56 am
Eric M Russell (8/31/2015)
Welsh Corgi (8/31/2015)
Eric M Russell (8/31/2015)
TEMPDB will automatically shrink whenever SQL Server is restarted, which might be most reliable way to do this, if needed. However, unless your TEMPDB is competing for storage with other databases, then there is no practical reason to shrink it ever. Ideally, TEMPDB should be allocated on a dedicated disk of it's own and just allowed to grow as needed.Thank you.
Well I had no disk space available. Before I made my post I was able to free up 13.6 of Disk Space.
I had to restart SQL Server.
There was not a lot of activity on the Server.
tempdb is on a Disk of it's own.
Unless it's necessary to free up space on that disk for something other than TEMPDB, then there is no reason to shrink the file. The allocation just gets reused, similar to other .mdf database files, transactin log files, or the Windows swap file.
On one database, I've got a 1 TB TEMPDB file sitting on a 1 TB dedicated disk. A buggy ETL process filled up the disk months ago leaving that tempdb file bloated to the max. I've since refactored the ETL process so it doesn't utilize but a small fraction of tempdb, but since the server hasn't been rebooted, the file is still maxed out. But it doesn't matter; it's actually beneficial not to have the file shrinking and auto growing. Having the tempdb file consuming the entire disk just ensures that no one else has an opportunity to place database files or whatever on a drive that's intended only for tempdb in the first place.
I ran out of disk space.
I had to shrink the 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/
September 1, 2015 at 9:08 am
Welsh Corgi (9/1/2015)
Eric M Russell (8/31/2015)
Welsh Corgi (8/31/2015)
Eric M Russell (8/31/2015)
TEMPDB will automatically shrink whenever SQL Server is restarted, which might be most reliable way to do this, if needed. However, unless your TEMPDB is competing for storage with other databases, then there is no practical reason to shrink it ever. Ideally, TEMPDB should be allocated on a dedicated disk of it's own and just allowed to grow as needed.Thank you.
Well I had no disk space available. Before I made my post I was able to free up 13.6 of Disk Space.
I had to restart SQL Server.
There was not a lot of activity on the Server.
tempdb is on a Disk of it's own.
Unless it's necessary to free up space on that disk for something other than TEMPDB, then there is no reason to shrink the file. The allocation just gets reused, similar to other .mdf database files, transactin log files, or the Windows swap file.
On one database, I've got a 1 TB TEMPDB file sitting on a 1 TB dedicated disk. A buggy ETL process filled up the disk months ago leaving that tempdb file bloated to the max. I've since refactored the ETL process so it doesn't utilize but a small fraction of tempdb, but since the server hasn't been rebooted, the file is still maxed out. But it doesn't matter; it's actually beneficial not to have the file shrinking and auto growing. Having the tempdb file consuming the entire disk just ensures that no one else has an opportunity to place database files or whatever on a drive that's intended only for tempdb in the first place.
I ran out of disk space.
I had to shrink the file.
TEMPDB reuses space within the file, so if you ran out of disk space, then I'm not sure that shrinking the file will help. Something, probably a hash join between two very large tables or another disk spooling operation, filled up tempdb to the point where it exceeded the size of the disk. That's what happened to me a few months back and how I got that 1 TB tempdb file. I rebooted the server, which truncated tempdb, but then that crazy damn ETL process would run all day long until it filled up 1 TB worth of tempdb drive space. I fixed the ETL by redesigning indexes so it performed a merge join, but I just left that 1 TB tempdb file on the 1 TB drive. It won't grow past 1 TB unless a process allocates that much insane temp storage again, which hopefully won't happen.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 1, 2015 at 10:44 am
Welsh Corgi (9/1/2015)
Eric M Russell (8/31/2015)
Welsh Corgi (8/31/2015)
Eric M Russell (8/31/2015)
TEMPDB will automatically shrink whenever SQL Server is restarted, which might be most reliable way to do this, if needed. However, unless your TEMPDB is competing for storage with other databases, then there is no practical reason to shrink it ever. Ideally, TEMPDB should be allocated on a dedicated disk of it's own and just allowed to grow as needed.Thank you.
Well I had no disk space available. Before I made my post I was able to free up 13.6 of Disk Space.
I had to restart SQL Server.
There was not a lot of activity on the Server.
tempdb is on a Disk of it's own.
Unless it's necessary to free up space on that disk for something other than TEMPDB, then there is no reason to shrink the file. The allocation just gets reused, similar to other .mdf database files, transactin log files, or the Windows swap file.
On one database, I've got a 1 TB TEMPDB file sitting on a 1 TB dedicated disk. A buggy ETL process filled up the disk months ago leaving that tempdb file bloated to the max. I've since refactored the ETL process so it doesn't utilize but a small fraction of tempdb, but since the server hasn't been rebooted, the file is still maxed out. But it doesn't matter; it's actually beneficial not to have the file shrinking and auto growing. Having the tempdb file consuming the entire disk just ensures that no one else has an opportunity to place database files or whatever on a drive that's intended only for tempdb in the first place.
I ran out of disk space.
I had to shrink the file.
If tempdb is on a disk of its own, and you ran out of disk space on the drive, it was probably because something was causing SQL Server to grow tempdb bigger than the disk it is on. If that is the case, shrinking tempdb isn't going to help if that same process runs again. You need to figure out why SQL Server was trying to grow tempdb larger than the drive it resides and fix that code or process.
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply