SQL Server 2014 – Disk Space Issue

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.