January 10, 2020 at 2:36 pm
Hello, I ran a bad query which failed due to disk space running out. I've managed to claim back some space using DBCC SHRINKDATABASE however I can't do anymore. I've also tried to DBCC SHRINKFILE to EMPTYFILE on all the tempDB files, but nothing changes.
EXEC SP_HELPFILE outputs;
tempdev1D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdfPRIMARY40013056 KBUnlimited65536 KBdata only
templog2D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\templog.ldfNULL40960 KBUnlimited65536 KBlog only
temp23D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndfPRIMARY39824640 KBUnlimited65536 KBdata only
temp34D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndfPRIMARY39942784 KBUnlimited65536 KBdata only
temp45D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndfPRIMARY39923712 KBUnlimited65536 KBdata only
temp56D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_5.ndfPRIMARY39857664 KBUnlimited65536 KBdata only
temp67D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_6.ndfPRIMARY40038080 KBUnlimited65536 KBdata only
temp78D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_7.ndfPRIMARY39835264 KBUnlimited65536 KBdata only
temp89D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_8.ndfPRIMARY39861120 KBUnlimited65536 KBdata only
Can I just delete all these files from the windows explorer? They amount to 300Gb?!
January 10, 2020 at 2:58 pm
This was removed by the editor as SPAM
January 10, 2020 at 3:34 pm
If you can stop the service and start again do these steps.
I didn't saw your last question: if you can delete them from windows explorer? yes, you can but first, you have to make sure that when you go to tempdb>Properties>files in the size column they dont have the same size, if they do, when you stop the service, remove the files and start again SQL server will automatically recreate them using the same space and have the same size.
Regards!
This is what is currently happening and I'm locked out of the server. I guess I'll have to wait until they have been rebuilt.
January 10, 2020 at 4:05 pm
Hi Alejandro Santana, I accidentally clicked the Report button instead of Reply. Now I've flagged you as Spam and I can't see how to undo it. I apologise.
However, I've tried what you suggested and I'm still in the same position after the files were created as soon as the instance started up again.
January 10, 2020 at 4:41 pm
It's ok,R no worries.
Lets remove the files
Alter database tempdb remove file temp3
Not the mdf and ldf but the ndfs.
Restart again and you should only have these two.
If you are locked down and can't access the server, you have to start the instance in single user mode.
As CMD administrator execute
To stop the service:
Net stop mssqlserver
To start as single user:
Net start mssqlserver /m
Connect to the instance fast before someone else connects
In cmd:
Sqlcmd -S localhost
Remove the files
Use tempdb
Go
Alter database tempdb remove file temp3
Go
Remove them all only leaving the mdf and ldf
Sorry if not very structured, I'm on the phone
January 10, 2020 at 4:49 pm
Have you analyzed what is taking up all your tempdb space? With this many large data files I find it difficult to believe that after restarting the service this much space is completely used.
Some things to consider, are you using row versioning snapshot isolation level? Do you have a lot of queries that create #temp tables? Do you have a lot of queries that have spills to tempdb in their execution plans?
Some basic troubleshooting can be done like this, here's a query to see high level how it is being used:
-- space usage summary
USE tempdb;
SELECT SUM(unallocated_extent_page_count) AS FreePages,
CAST(SUM(unallocated_extent_page_count)/128.0 AS decimal(9,2)) AS FreeSpaceMB,
SUM(version_store_reserved_page_count) AS VersionStorePages,
CAST(SUM(version_store_reserved_page_count)/128.0 AS decimal(9,2)) AS VersionStoreMB,
SUM(internal_object_reserved_page_count) AS InternalObjectPages,
CAST(SUM(internal_object_reserved_page_count)/128.0 AS decimal(9,2)) AS InternalObjectsMB,
SUM(user_object_reserved_page_count) AS UserObjectPages,
CAST(SUM(user_object_reserved_page_count)/128.0 AS decimal(9,2)) AS UserObjectsMB
FROM sys.dm_db_file_space_usage;
here's a query to show sessions using the most space:
--top space users
SELECT s.login_name, ssu.*
FROM sys.dm_db_session_space_usage ssu
INNER JOIN sys.dm_exec_sessions s ON ssu.session_id = s.session_id
--ORDER BY (ssu.user_objects_alloc_page_count + ssu.internal_objects_alloc_page_count) DESC
ORDER BY (ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count) + (ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count) DESC;
here's a query to show the largest temp tables:
--tables
SELECT s.name AS schema_name, t.object_id, t.name AS table_name,
t.create_date, i.name AS index_name, SUM(ps.row_count) as row_count,
SUM(used_page_count) / 128 AS total_mb, SUM(used_page_count) * 8 AS total_KB
FROM tempdb.sys.tables t
INNER JOIN tempdb.sys.schemas s on t.schema_id = s.schema_id
INNER JOIN tempdb.sys.indexes i ON t.object_id = i.object_id
INNER JOIN tempdb.sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE i.index_id IN (0,1)
GROUP BY s.name, t.object_id, t.name, t.create_date, i.name
ORDER BY total_KB desc, s.name, t.name;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply