0

Currently we have a drive on your server that stores all user DB's data files along with one tempdb data file. (D: drive) It has 1.4 TB total space with only 98Gigs free space.

The biggest DB size is 1.087 TB, and the second biggest database file is tempdb 262gbs. the other DB sizes are fairly small.

The largest DB (let's call it BigDB) is currently using up all 1.08tbs and can not be shrunk. It's autogrowth is set to 1gb at a time.

The tempdb initial size is set to 261gbs and the space used fluctuates. The largest size I have seen it being used is 8GB.

My plan of action is to request a dedicated drive to store tempdb and create 7 additional data files, with each data file size being 22GB based on Brent Ozar's recommnedation here: https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/

This will free up space in the D: drive and space issue should no longer be an issue.

The steps I will take:

  1. Shrink the current tempdb from 261gbs to 22gbs:

use [tempdb] go dbcc shrinkfile (tempdev, '22000') go

*If shrinking the file does not work, then execute DBCC FREEPROCCACHE. After that has completed, re-issue the shrinkfile command

Question 1: How long is it expected for a shrinking of tempdb data file going to take?

  1. Move the current tempdb data file to the new Z: drive

USE [master] GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'Z:\tempdev.mdf') GO

Question 2: Do I need to move the log file as well even if the log file drive is not experiencing any space issue?

  1. Add the additional data files in Z: drive
USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_1', FILENAME = N'F:\DATA\tempdev_1.ndf' , SIZE = 22528000KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_2', FILENAME = N'F:\DATA\tempdev_2.ndf' , SIZE = 22528000KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_3', FILENAME = N'F:\DATA\tempdev_3.ndf' , SIZE = 22528000KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_4', FILENAME = N'F:\DATA\tempdev_1.ndf' , SIZE = 22528000KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_5', FILENAME = N'F:\DATA\tempdev_2.ndf' , SIZE = 22528000KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_6', FILENAME = N'F:\DATA\tempdev_3.ndf' , SIZE = 22528000KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_7', FILENAME = N'F:\DATA\tempdev_1.ndf' , SIZE = 22528000KB , FILEGROWTH = 65536KB )
GO
  1. Restart SQL Services

  2. Verify the location of the new tempdb files

SELECT name AS FileLogicalName, physical_name AS FileLocation
FROM sys.master_files  
WHERE database_id BETWEEN 1 AND 4

My priority concern is-- putting in a request to add a separate drive, takes up to two weeks to process. If the D: drive becomes full and 0 space free is left, what will happen to the databases?

Also, Can someone correct me on my steps and let me know if they find any discrepancies? If so, please advise. Thanks in advance.

7
  • If your concern is only the drive filling up (and not any of the repercussions of that), why not set a MAXSIZE on those files? Oct 12 at 21:37
  • Just curious, why are you pre-allocating ~261 GB of space to tempdb if the most you've ever seen it use is ~8 GB? Don't get me wrong, it's good to grow your tempdb files in advance to avoid growth operations bottlenecking normal production operations. But that's about 33x the amount of space you've seen get used at the max. Maybe 10x would be a more appropriate starting point, and would save you ~180 GB of space that could possibly be used elsewhere?
    – J.D.
    Oct 12 at 23:51
  • @J.D. - I did not pre-allocate 261 gb space to tempdb. It was like that from previous set up. That is the reason why I want to resize down to 22GB. This is why I want to shrink the DB. is there something else you would suggest? Oct 13 at 0:13
  • @ErikDarling - on what files are you referring to? Oct 13 at 0:14
  • @sqllover2020 That's fair, but you also said you want to make 7 files that are each 22 GB. That would be 154 GB. Still seems like a lot but definitely better than previously. I would personally probably start around 80 GB total, which is 10x the max you've seen. But to each their own.
    – J.D.
    Oct 13 at 1:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.