January 30, 2017 at 8:06 pm
Our Tempdb is set at 16 MB and I wanted to know if there was a way to read all our tables, determine if I enter a page of data, which one of these tables would fill up this tempdb causing it to just keep grewing everytime a new page was added.
Anyone got any kind of script that would tell me that this is way too small?
January 30, 2017 at 8:53 pm
TRACEY-320982 - Monday, January 30, 2017 8:06 PMOur Tempdb is set at 16 MB and I wanted to know if there was a way to read all our tables, determine if I enter a page of data, which one of these tables would fill up this tempdb causing it to just keep grewing everytime a new page was added.
Anyone got any kind of script that would tell me that this is way too small?
16 MB is too small for anything you are doing.
Do you mean 16GB or is it really set to 16 MB ?
January 30, 2017 at 9:33 pm
matak - Monday, January 30, 2017 8:53 PMTRACEY-320982 - Monday, January 30, 2017 8:06 PMOur Tempdb is set at 16 MB and I wanted to know if there was a way to read all our tables, determine if I enter a page of data, which one of these tables would fill up this tempdb causing it to just keep grewing everytime a new page was added.
Anyone got any kind of script that would tell me that this is way too small?16 MB is too small for anything you are doing.
Do you mean 16GB or is it really set to 16 MB ?
No definitely 16mb, so i have to find some way to determine the largest table size and when inserted will cause it to grew, i am sure there is a script somewhere
January 31, 2017 at 12:55 am
Hm,
I don't believe you would be happy with 16 MB TempDB
Maybe you can calculate the size of the TempDB with this script:
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
/*
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
*/
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
I found this with google in a wounderful community:
How to find a temporary table in tempdb?
If the tempDB is only 16 MB, what about the size of your database?
Kind regards,
Andreas
January 31, 2017 at 10:40 pm
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply