February 23, 2023 at 7:31 pm
First disclaimer, I'm not a full-time DBA. I didn't initially set this configuration up, but it's been in place for years and I don't typically make any wholesale changes unless absolutely necessary. I help our group manage our SQL Server cluster. I have an instance that houses our reporting services, utilizing a read-only version of our transactional database. Most of our SSRS reports and some custom reports hit this R/O instance pretty heavily. It's a 24x7 system, so restarting is not typically an option. I'm attaching a file that I hope highlights the issue.
Recently we've been getting some alerts about the volumes housing the data files nearing capacity. The two in particular are logical names tempdev and temp2. When I look within SSMS via the Shrinkfile dialog, I see that they each have plenty of free space. When I look at Disk Management of the server, I see that each of those volumes shows a very small amount of disk space available. During the last maintenance window, I did run ShrinkDB and ShrinkFile to see if it made a difference, but it doesn't ever seem to affect the Free Space that shows up in Disk Management. I get emails and calls from the server group because they are forced to address any volumes that are below the 5% free space threshold. Are there any actions I can take to have the Free Space show as available on the file system? I have tried to find this answer elsewhere and have maybe just not searched with the right criteria. I've always gotten a lot of answers from this site and am hopeful someone can guide and educate me. Thanks in advance.
February 23, 2023 at 8:03 pm
You also need to rebalance your tempdb files -- they should all be the same size. This will, unfortunately, require restarting SQL Server service.
e.g.,
ALTER DATABASE tempdb
MODIFY FILE (Name=tempdev, size = 94GB,filegrowth=0KB),
MODIFY FILE (NAME=tempdb2 , size = 94GB,filegrowth=0KB),
MODIFY FILE (NAME=tempdb3 , size = 94GB,filegrowth=0KB),
MODIFY FILE (NAME=tempdb4 , size = 94GB,filegrowth=0KB),
MODIFY FILE (NAME=tempdb5 , size = 94GB,filegrowth=0KB),
MODIFY FILE (NAME=tempdb6 , size = 94GB,filegrowth=0KB),
MODIFY FILE (NAME=tempdb7 , size = 94GB,filegrowth=0KB),
MODIFY FILE (NAME=tempdb8 , size = 94GB,filegrowth=0KB);
February 23, 2023 at 9:09 pm
Shrink files 3 thru 8 to, say, 85GB. Then, wait a while. Once the tempdb file sizes get more balanced out, shrink files 1 and 2 back to 85GB. Ultimately you want all temp db data files to have the same size, so reset the internal file configs to 85GB each.
USE tempdb;
DBCC SHRINKFILE(temp3, 87040) --87040MB=85GB
DBCC SHRINKFILE(temp4, 87040) --87040MB=85GB
DBCC SHRINKFILE(temp5, 87040) --87040MB=85GB
DBCC SHRINKFILE(temp6, 87040) --87040MB=85GB
DBCC SHRINKFILE(temp7, 87040) --87040MB=85GB
DBCC SHRINKFILE(temp8, 87040) --87040MB=85GB
--wait for some data to naturally move to tempdev and temp2 from temp3/4/....
--then, when tempdev and temp2 are (decently) below 85GB in usage:
--**WARNING**: these commands could take a while and/or could cause a failure.
-- **SKIP** unless you are sure activity and usage is light in tempdb (and you can risk a fail).
--DBCC SHRINKFILE(tempdev, 87040) --87040MB=85GB
--DBCC SHRINKFILE(temp2, 87040) --87040MB=85GB
--finally, for next restart, try to get all tempdb data files to start with same size and growth:
ALTER DATABASE tempdb MODIFY FILE ( NAME = [tempdev], SIZE=85GB, FILEGROWTH=1GB );
ALTER DATABASE tempdb MODIFY FILE ( NAME = [temp2], SIZE=85GB, FILEGROWTH=1GB );
ALTER DATABASE tempdb MODIFY FILE ( NAME = [temp3], SIZE=85GB, FILEGROWTH=1GB );
ALTER DATABASE tempdb MODIFY FILE ( NAME = [temp4], SIZE=85GB, FILEGROWTH=1GB );
ALTER DATABASE tempdb MODIFY FILE ( NAME = [temp5], SIZE=85GB, FILEGROWTH=1GB );
ALTER DATABASE tempdb MODIFY FILE ( NAME = [temp6], SIZE=85GB, FILEGROWTH=1GB );
ALTER DATABASE tempdb MODIFY FILE ( NAME = [temp7], SIZE=85GB, FILEGROWTH=1GB );
ALTER DATABASE tempdb MODIFY FILE ( NAME = [temp8], SIZE=85GB, FILEGROWTH=1GB );
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 23, 2023 at 9:21 pm
Thanks to both for this input. Will these actions have any impact on what shows as free space in Disk Management? I will definitely take this action during the next outage, but my ultimate goal is for that be reflected in the OS disk management.
February 23, 2023 at 9:34 pm
The way I figured it:
Currently you have 10 files * 100GB reserved = 1000GB.
If you drop each file to 85GB, you'll have 850GB reserved, i.e., 150GB less. On a drive size of roughly 1000GB, that's around 14% or so free. If you need to increase to 90GB per file, you'd still have 9-10% free. Personally, I wouldn't go higher than that in initial allocations. You want to leave something for emergency growth, although not much, given how much space you already have allocated to tempdb files.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply