September 24, 2017 at 11:20 am
Hi All,
I am getting an odd error when trying to shrink tempdb a little bit
DBCC SHRINKFILE: Page 7:2275824 could not be moved because it is a work table page.
I;ve isolated the table and dropped it, and instantly the error changes to a new table
Any thoughts? I am loathed to free up the proc cache and such for its impact on query plans and such...
Cheers
Alex
September 25, 2017 at 12:34 am
alex.sqldba - Sunday, September 24, 2017 11:20 AMHi All,I am getting an odd error when trying to shrink tempdb a little bit
DBCC SHRINKFILE: Page 7:2275824 could not be moved because it is a work table page.
I;ve isolated the table and dropped it, and instantly the error changes to a new table
Any thoughts? I am loathed to free up the proc cache and such for its impact on query plans and such...
Cheers
Alex
Quick question, why are you shrinking the tempdb file(s)?
😎
September 25, 2017 at 12:44 am
We had a report get run against the wrong instance. Tempdb grew from 50-ish GB to 160GB.
September 25, 2017 at 12:54 am
My suggestion is to leave it as is until the next scheduled restart of the server / sql server services if possible, the shrink process fragments the files and my result in degraded performance.
😎
Is the tempdb properly configured (number of files, initial sizes, growth etc.)?
September 25, 2017 at 1:16 am
It was built with the Installers recommended/default -- the number of files match the number of virtual processors. And growth is set to 1024 MB.
September 25, 2017 at 2:23 am
This was removed by the editor as SPAM
September 25, 2017 at 6:19 am
alex.sqldba - Monday, September 25, 2017 1:16 AMIt was built with the Installers recommended/default -- the number of files match the number of virtual processors. And growth is set to 1024 MB.
Apparently its doing lot of IO involving temp tables with high number of record sets with Inserts/updates other business logic before doing the projection of the results to the presentation layer. In this situation it takes what it takes ...Terminating it at this stage can also do no good .. Eirikur's advice looks sensible.
September 25, 2017 at 6:31 am
prettsons - Monday, September 25, 2017 2:23 AMTry this:
ALTER DATABASE tempdb MODIFY FILE
(
NAME = tempdev,
SIZE = DesiredInitialTempdbSize
)
Considering that this was posted in the SQL Server 2016 forum, I doubt that TempDB only has 1 file. From SQL Server 2016 the installation process will create TempDB files based on the following logic:
The number of files depends on the number of (logical) cores on the machine. The value will be the number of cores or 8, whichever is lower.
The default value for the number of data files is based on the general guidelines in KB 2154845.
(https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database)
Unless the OP is using a VM with only 1 virtual core or a physical machine with 1 logical core (can you even buy those any more..?), they won't have 1 file. And if the OP is, I would not recommend that as a good environment for a SQL Server Instance. :hehe:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 25, 2017 at 7:16 am
alex.sqldba - Sunday, September 24, 2017 11:20 AMHi All,I am getting an odd error when trying to shrink tempdb a little bit
DBCC SHRINKFILE: Page 7:2275824 could not be moved because it is a work table page.
I;ve isolated the table and dropped it, and instantly the error changes to a new table
Any thoughts? I am loathed to free up the proc cache and such for its impact on query plans and such...
Cheers
Alex
Before doing that FREEPROCCACHE work around, you would want to try a few manual checkpoints first.
Sue
September 25, 2017 at 8:15 am
alex.sqldba - Monday, September 25, 2017 1:16 AMIt was built with the Installers recommended/default -- the number of files match the number of virtual processors. And growth is set to 1024 MB.
And the initial file size?
😎
September 25, 2017 at 8:47 am
Tiny. 64MB I am told.
No one knows if that's per file or total. But assuming total that is less than a GB.
Is the initial size stored somewhere?
September 25, 2017 at 8:59 am
Yes. Right-click on tempdb > Properties > Files > Initial Size.
September 25, 2017 at 9:21 am
Oh. I was assuming that value is inaccurate has they are all set to the current file sizes. And they are all slightly different. Hovering between 15 and 25 GB each.
Which I know cannot be accurate as the drive wasn't big enough initially to support that.
September 25, 2017 at 10:19 am
Here's a query which I use to find out which sessions are using tempdb - original auhtor unknown - I only made a reformating:
USE [tempdb];
WITH [task_space_usage] AS (
-- SUM alloc/delloc pages
SELECT [session_id],
[request_id],
SUM([internal_objects_alloc_page_count]) AS [alloc_pages],
SUM([internal_objects_dealloc_page_count]) AS [dealloc_pages]
FROM sys.dm_db_task_space_usage WITH (NOLOCK)
WHERE [session_id] != @@SPID
GROUP BY [session_id], [request_id]
)
SELECT [tskspc].[session_id],
[tskspc].[alloc_pages] * 1.0 / 128 AS [internal object MB space],
[tskspc].[dealloc_pages] * 1.0 / 128 AS [internal object dealloc MB space],
[exsql].[text],
-- Extract statement from sql text
ISNULL(
NULLIF(
SUBSTRING(
[exsql].[text],
[exrq].[statement_start_offset] / 2,
CASE WHEN [exrq].[statement_end_offset] < [exrq].[statement_start_offset]
THEN 0
ELSE([exrq].[statement_end_offset] - [exrq].[statement_start_offset]) / 2 END
), ''
), [exsql].[text]
) AS [statement text],
[expl].[query_plan]
FROM [task_space_usage] AS [tskspc]
INNER JOIN sys.dm_exec_requests AS [exrq] WITH (NOLOCK) ON [tskspc].[session_id] = [exrq].[session_id] AND [tskspc].[request_id] = [exrq].[request_id]
OUTER APPLY sys.dm_exec_sql_text([exrq].[sql_handle]) AS [exsql]
OUTER APPLY sys.dm_exec_query_plan([exrq].[plan_handle]) AS [expl]
WHERE [exsql].[text] IS NOT NULL OR [expl].[query_plan] IS NOT NULL
ORDER BY 3 DESC;
-- You can try to end some of the sessions and prey that the shrink will work.
--Another thing I came around once was (forgot where I found it or who the author was) and I would not want to run it on production!
/*
DBCC DROPCLEANBUFFERS
Clears the clean buffers. This will flush cached indexes and data pages. You may want to run a CHECKPOINT command first, in order to flush everything to disk.
*/
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
/*
DBCC FREEPROCCACHE
Clears the procedure cache, which may free up some space in tempdb, although at the expense of your cached execution plans, which will need to be rebuilt the next time. This means that ad-hoc queries and stored procedures will have to recompile the next time you run them. Although this happens automatically, you may notice a significant performance decrease the first few times you run your procedures.
*/
DBCC FREEPROCCACHE;
GO
/*
DBCC FREESYSTEMCACHE
This operation is similar to FREEPROCCACHE, except it affects other types of caches.
*/
DBCC FREESYSTEMCACHE ('ALL');
GO
/*
DBCC FREESESSIONCACHE
Flushes the distributed query connection cache. This has to do with distributed queries (queries between servers), but I’m really not sure how much space they actually take up in tempdb.
*/
DBCC FREESESSIONCACHE;
GO
/*
.. and finally, DBCC SHRINKFILE
DBCC SHRINKFILE is the same tool used to shrink any database file, in tempdb or other databases. This is the step that actually frees the unallocated space from the database file.
Warning: Make sure you don’t have any open transactions when running DBCC SHRINKFILE. Open transactions may cause the DBCC operation to fail, and possibly corrupt your tempdb!
*/
All the best and kind regards
Gerald
September 27, 2017 at 12:01 pm
alex.sqldba - Monday, September 25, 2017 9:21 AMOh. I was assuming that value is inaccurate has they are all set to the current file sizes. And they are all slightly different. Hovering between 15 and 25 GB each.Which I know cannot be accurate as the drive wasn't big enough initially to support that.
Correct...the properties of the files from SSMS doesn't display the initial size even though the column lists it as initial size.
Check the following for more information:
SQL Server: Misleading Database Initial Size Label
Sue
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply