How to manage temporary tables

Question

How to manage temporary tables

hurricaneDBA

SSCertifiable

Points: 5027
More actions
April 26, 2017 at 11:15 pm

#323141

Dear MVPs
I hope you are all doing great
I am troubleshooting why my Transaction log for one application keeps jumping to 2GB every 2 days and i found that there is a procedure which is run daily by the vendor and the TSQL is below. It insert many values into temporary tables so is there a way to size the temporary database so the transaction log doesnt exponentially grow in size?
SQL Server 2012
Windows 2008 R2 64 bit
Server has 12GB of RAM and 6GB of RAM is assigned to the database
I have already setup the tempdb to use 3 datafiles and 1 log file. The datafiles are 8MB in size and the log file is 1MB
I believe i need to change the size of the tempdb but to what size?
The database in question has a size right now of:
2.7GB
TL is 2.1GB
please advise what i can do to resize the temporary tables and find out why the TL keeps growing. My guess is the insert into the temporary tables
Also the memory utilization is always at 90%
SELECT
CathodicProtection.ID,
CathodicProtection.Network_Name,
CathodicProtection.Line_GUID,
CathodicProtection.Section_GUID,
CathodicProtection.xxKeyxx,
CathodicProtection.xxValuexx,
COALESCE(CathodicProtection.Depletion, -999) AS Depletion,
COALESCE(CathodicProtection.mV * -1, -999) AS mV
INTO #CathodicProtection FROM (
SELECT
NEWID() AS ID,
SC.technicalArea AS Network_Name,
SC.LineId AS Line_GUID,
SC.Id AS Section_GUID,
ISNULL(GoodCategory.xxKeyxx, 'Good') AS 'xxKeyxx',
ISNULL(GoodCategory.xxValuexx/3, 0) AS xxValuexx,
GoodCategory.Depletion,
GoodCategory.mV
FROM #TempSection AS SC
LEFT JOIN (
SELECT
DT.Route_GUID AS Section_GUID,
'Good' AS 'xxKeyxx',
ISNULL(COUNT(DT.cnt),0) AS 'xxValuexx',
Min(DT.mV) AS mV,
Max(DT.Depletion) as Depletion
FROM
(
SELECT
SP.Route_GUID,
'y' AS 'cnt',
CASE
WHEN (COMMENTS LIKE '%mv%' AND COMMENTS NOT LIKE '%Depletion%') THEN LEFT(RIGHT(COMMENTS, 6),4)
WHEN (COMMENTS LIKE '%mv%' AND COMMENTS LIKE '%Depletion%') THEN LEFT(RIGHT(COMMENTS, 6),4)
END AS 'mV',
CASE
WHEN (COMMENTS LIKE '%Depletion 100%%') THEN RIGHT(LEFT(COMMENTS, 20),3)
WHEN (COMMENTS LIKE '%depleted' AND COMMENTS NOT LIKE '%mv') THEN LEFT(RIGHT(COMMENTS, 12),2)
WHEN (COMMENTS LIKE '%Depletion%' AND COMMENTS LIKE '%mv') THEN RIGHT(LEFT(COMMENTS, 19),2)
END AS 'Depletion',
CASE WHEN (EV.Value LIKE '%on%') THEN 850 END AS 'min_mv_on',
CASE WHEN (EV.Value LIKE '%on%') THEN 1200 END AS 'max_mv_on',
CASE WHEN (EV.Value LIKE '%off%') THEN 800 END AS 'min_mv_off',
CASE WHEN (EV.Value LIKE '%off%') THEN 1100 END AS 'max_mv_off'
FROM Anode AS AN
INNER JOIN EVENT_RANGE AS ER
ON AN.EVENT_GUID = ER.EVENT_GUID
INNER JOIN Station_Point AS SP
ON SP.STATION_GUID = ER.STATION_GUID_BEGIN AND SP.STATION_GUID = ER.STATION_GUID_END
INNER JOIN #TempExtensionValue AS EV
ON SP.ROUTE_GUID = EV.ParentId
) AS DT
INNER JOIN #TempSection AS SC
ON DT.ROUTE_GUID = SC.Id
WHERE (DT.Depletion < 50) OR (DT.Depletion IS NULL)
GROUP BY DT.ROUTE_GUID
) AS GoodCategory
ON SC.Id = GoodCategory.Section_GUID
UNION
SELECT
NEWID() AS ID,
SC.technicalArea AS Network_Name,
SC.LineId AS Line_GUID,
SC.Id AS Section_GUID,
ISNULL(MediumCategory.xxKeyxx, 'Medium') AS 'xxKeyxx',
ISNULL(MediumCategory.xxValuexx, 0) AS xxValuexx,
MediumCategory.Depletion,
MediumCategory.mV
FROM #TempSection AS SC
LEFT JOIN (
SELECT
DT.Route_GUID AS Section_GUID,
'Medium' AS 'xxKeyxx',
ISNULL(COUNT(DT.cnt),0) AS 'xxValuexx',
Min(DT.mV) AS mV,
Max(DT.Depletion) as Depletion
FROM
(
SELECT
SP.Route_GUID,
'y' AS 'cnt',
CASE
WHEN (COMMENTS LIKE '%mv%' AND COMMENTS NOT LIKE '%Depletion%') THEN LEFT(RIGHT(COMMENTS, 6),4)
WHEN (COMMENTS LIKE '%mv%' AND COMMENTS LIKE '%Depletion%') THEN LEFT(RIGHT(COMMENTS, 6),4)
END AS 'mV',
CASE
WHEN (COMMENTS LIKE '%Depletion 100%%') THEN RIGHT(LEFT(COMMENTS, 20),3)
WHEN (COMMENTS LIKE '%depleted' AND COMMENTS NOT LIKE '%mv') THEN LEFT(RIGHT(COMMENTS, 12),2)
WHEN (COMMENTS LIKE '%Depletion%' AND COMMENTS LIKE '%mv') THEN RIGHT(LEFT(COMMENTS, 19),2)
END AS 'Depletion',
CASE WHEN (EV.Value LIKE '%on%') THEN 850 END AS 'min_mv_on',
CASE WHEN (EV.Value LIKE '%on%') THEN 1200 END AS 'max_mv_on',
CASE WHEN (EV.Value LIKE '%off%') THEN 800 END AS 'min_mv_off',
CASE WHEN (EV.Value LIKE '%off%') THEN 1100 END AS 'max_mv_off'
FROM Anode AS AN
INNER JOIN EVENT_RANGE AS ER
ON AN.EVENT_GUID = ER.EVENT_GUID
INNER JOIN Station_Point AS SP
ON SP.STATION_GUID = ER.STATION_GUID_BEGIN AND SP.STATION_GUID = ER.STATION_GUID_END
INNER JOIN #TempExtensionValue AS EV
ON SP.ROUTE_GUID = EV.ParentId
) AS DT
INNER JOIN #TempSection AS SC
ON DT.ROUTE_GUID = SC.Id
WHERE (DT.Depletion >= 50 AND DT.Depletion <= 75 )
GROUP BY DT.ROUTE_GUID
) AS MediumCategory
ON SC.Id = MediumCategory.Section_GUID
UNION
SELECT
NEWID() AS ID,
SC.technicalArea AS Network_Name,
SC.LineId AS Line_GUID,
SC.Id AS Section_GUID,
ISNULL(BadCategory.xxKeyxx, 'Bad') AS 'xxKeyxx',
ISNULL(BadCategory.xxValuexx, 0) AS xxValuexx,
BadCategory.Depletion,
BadCategory.mV
FROM #TempSection AS SC
LEFT JOIN (
SELECT
DT.Route_GUID AS Section_GUID,
'Bad' AS 'xxKeyxx',
ISNULL(COUNT(DT.cnt),0) AS 'xxValuexx',
Min(DT.mV) AS mV,
Max(DT.Depletion) as Depletion
FROM
(
SELECT
SP.Route_GUID,
'y' AS 'cnt',
CASE
WHEN (COMMENTS LIKE '%mv%' AND COMMENTS NOT LIKE '%Depletion%') THEN LEFT(RIGHT(COMMENTS, 6),4)
WHEN (COMMENTS LIKE '%mv%' AND COMMENTS LIKE '%Depletion%') THEN LEFT(RIGHT(COMMENTS, 6),4)
END AS 'mV',
CASE
WHEN (COMMENTS LIKE '%Depletion 100%%') THEN RIGHT(LEFT(COMMENTS, 20),3)
WHEN (COMMENTS LIKE '%depleted' AND COMMENTS NOT LIKE '%mv') THEN LEFT(RIGHT(COMMENTS, 12),2)
WHEN (COMMENTS LIKE '%Depletion%' AND COMMENTS LIKE '%mv') THEN RIGHT(LEFT(COMMENTS, 19),2)
END AS 'Depletion',
CASE WHEN (EV.Value LIKE '%on%') THEN 850 END AS 'min_mv_on',
CASE WHEN (EV.Value LIKE '%on%') THEN 1200 END AS 'max_mv_on',
CASE WHEN (EV.Value LIKE '%off%') THEN 800 END AS 'min_mv_off',
CASE WHEN (EV.Value LIKE '%off%') THEN 1100 END AS 'max_mv_off'
FROM Anode AS AN
INNER JOIN EVENT_RANGE AS ER
ON AN.EVENT_GUID = ER.EVENT_GUID
INNER JOIN Station_Point AS SP
ON SP.STATION_GUID = ER.STATION_GUID_BEGIN AND SP.STATION_GUID = ER.STATION_GUID_END
INNER JOIN #TempExtensionValue AS EV
ON SP.ROUTE_GUID = EV.ParentId
) AS DT
INNER JOIN #TempSection AS SC
ON DT.ROUTE_GUID = SC.Id
WHERE (DT.Depletion > 75 )
GROUP BY DT.ROUTE_GUID
) AS BadCategory
ON SC.Id = BadCategory.Section_GUID
) AS CathodicProtection;
PRINT N'Categories are loaded.';
INSERT INTO Tbl_CathodicProtectionDetails
SELECT ID, Network_Name, Line_GUID, Section_GUID, xxKeyxx, xxValuexx, Depletion AS Depletion, mV
FROM #CathodicProtection
PRINT N'Data is loaded into Tbl_CathodicProtection.';
DROP TABLE #TempExtensionValue, #TempSection, #CathodicProtection;
PRINT N'Temporary tables are removed.';
END

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 1

Questions:
😎
1. what is the recovery model of the database?
2. what are the backup details (type/frequency)?
3. any index maintenance operations on schedule?
4. any database integrity checks on schedule?
5. can you share the CPU details / NUMA?
6. are you shrinking the log files?
7. when the files are growing, what is the log_reuse_wait_desc (from sys.databases)?

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 2

Hi Eirikur

Here are your answers

1. Full
2. Backups are done via EMC:
Differential is done daily
Full is done once a week and monthly once a month
But TL backups arent covered by EMC so i run those manually every 4 hours due to space limitations on the server
3. No
4. No
5. CPU details 4 *

6. Yes every 3 days
7. The value for log_reuse_wait_desc right now is NOTHING
thanks
KY

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 3

hurricaneDBA - Thursday, April 27, 2017 1:05 AM
Hi Eirikur
Here are your answers
1. Full
2. Backups are done via EMC:
Differential is done daily
Full is done once a week and monthly once a month
But TL backups arent covered by EMC so i run those manually every 4 hours due to space limitations on the server
3. No
4. No
5. CPU details 4 *
6. Yes every 3 days
7. The value for log_reuse_wait_desc right now is NOTHING
thanks
KY

Suggest you increase the frequency of the LOG backups which will reduce the size of the backup files, i.e. start with 10 minutes intervals and monitor the log_reuse_wait_desc and the file growth. For a busy server, four hours interval is a long time and the logs will retain all data until the log or full backup is run which may and most likely will result in large backup files.
😎
Stop shrinking the log files, those will only grow again with all the cost of the file growth topped with file level fragmentation.
Add 1 more data file to the tempdb and size all four files to exactly the same size, if those are on a separate dedicated drive as they should be, then size them to fill the drive. Makes no sense of having them growing as that is just adding effort for the server. This CPU has eight cores, monitor for tempdb page congestion and add four more (8 total) files if needed.
Check the VLF counts and adjust the log file sizes and growth as needed.

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 4

Ok so i will increase the file sizes to 500MB each and add 4 more just to be on the same safe side
What is a good size to use? i have them on their own drive but i will check if this drive is using one disk or not.
I will check the VLF counts and act accordingly

Thanks Eirikur
Have a good weekend
KY.

RandomEvent SSCrazy Points: 2963 More actions · Answer 5

If that's the query causing the problem would it not make sense to review the query and see if it can be optimised. I've only had a brief look but there is a large amount of code duplication in there. Could you not take the DT part of the code an create a single temporary table to hold this data then reference that table with appropriate indexes etc. I fully agree with Eirikur's suggestions but would also recommend a code review.

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 6

I wanted to know the thoughts of the MVPs and yes i think they can create a better query but before going back to the vendor i wanted to get some hints from you guys first 🙂

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

I agree with RandomEvent. Do you have any control over the query? The two things I would consider are as follows:
(1) Don't stage in #CathodicProtection unless doing so provides a proven performance benefit;
(2) Use UNION ALL instead of UNION. UNION eliminates duplicates, which requires a sort operation, which could spill into tempdb.

John

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 8

The SQL code in slightly more readable format, definitely has room for improvements.
😎
SELECT CathodicProtection.id, CathodicProtection.network_name, CathodicProtection.line_guid, CathodicProtection.section_guid, CathodicProtection.xxkeyxx, CathodicProtection.xxvaluexx, COALESCE(CathodicProtection.depletion, -999) AS Depletion, COALESCE(CathodicProtection.mv * -1, -999) AS mV INTO #cathodicprotection FROM ( SELECT Newid() AS ID, SC.technicalarea AS Network_Name, SC.lineid AS Line_GUID, SC.id AS Section_GUID, Isnull(GoodCategory.xxkeyxx, 'Good') AS 'xxKeyxx', Isnull(GoodCategory.xxvaluexx/3, 0) AS xxValuexx, GoodCategory.depletion, GoodCategory.mv FROM #tempsection AS SC LEFT JOIN ( SELECT DT.route_guid AS Section_GUID, 'Good' AS 'xxKeyxx', Isnull(Count(DT.cnt),0) AS 'xxValuexx', Min(DT.mv) AS mV, Max(DT.depletion) AS Depletion FROM ( SELECT SP.route_guid, 'y' AS 'cnt', CASE WHEN ( comments LIKE '%mv%' AND comments NOT LIKE '%Depletion%') THEN LEFT(RIGHT(comments, 6),4) WHEN ( comments LIKE '%mv%' AND comments LIKE '%Depletion%') THEN LEFT(RIGHT(comments, 6),4) END AS 'mV', CASE WHEN ( comments LIKE '%Depletion 100%%') THEN RIGHT(LEFT(comments, 20),3) WHEN ( comments LIKE '%depleted' AND comments NOT LIKE '%mv') THEN LEFT(RIGHT(comments, 12),2) WHEN ( comments LIKE '%Depletion%' AND comments LIKE '%mv') THEN RIGHT(LEFT(comments, 19),2) END AS 'Depletion', CASE WHEN ( EV.value LIKE '%on%') THEN 850 END AS 'min_mv_on', CASE WHEN ( EV.value LIKE '%on%') THEN 1200 END AS 'max_mv_on', CASE WHEN ( EV.value LIKE '%off%') THEN 800 END AS 'min_mv_off', CASE WHEN ( EV.value LIKE '%off%') THEN 1100 END AS 'max_mv_off' FROM anode AS AN INNER JOIN event_range AS ER ON AN.event_guid = ER.event_guid INNER JOIN station_point AS SP ON SP.station_guid = ER.station_guid_begin AND SP.station_guid = ER.station_guid_end INNER JOIN #tempextensionvalue AS EV ON SP.route_guid = EV.parentid ) AS DT INNER JOIN #tempsection AS SC ON DT.route_guid = SC.id WHERE ( DT.depletion < 50) OR ( DT.depletion IS NULL) GROUP BY DT.route_guid ) AS GoodCategory ON SC.id = GoodCategory.section_guid UNION SELECT Newid() AS ID, SC.technicalarea AS Network_Name, SC.lineid AS Line_GUID, SC.id AS Section_GUID, Isnull(MediumCategory.xxkeyxx, 'Medium') AS 'xxKeyxx', Isnull(MediumCategory.xxvaluexx, 0) AS xxValuexx, MediumCategory.depletion, MediumCategory.mv FROM #tempsection AS SC LEFT JOIN ( SELECT DT.route_guid AS Section_GUID, 'Medium' AS 'xxKeyxx', Isnull(Count(DT.cnt),0) AS 'xxValuexx', Min(DT.mv) AS mV, Max(DT.depletion) AS Depletion FROM ( SELECT SP.route_guid, 'y' AS 'cnt', CASE WHEN ( comments LIKE '%mv%' AND comments NOT LIKE '%Depletion%') THEN LEFT(RIGHT(comments, 6),4) WHEN ( comments LIKE '%mv%' AND comments LIKE '%Depletion%') THEN LEFT(RIGHT(comments, 6),4) END AS 'mV', CASE WHEN ( comments LIKE '%Depletion 100%%') THEN RIGHT(LEFT(comments, 20),3) WHEN ( comments LIKE '%depleted' AND comments NOT LIKE '%mv') THEN LEFT(RIGHT(comments, 12),2) WHEN ( comments LIKE '%Depletion%' AND comments LIKE '%mv') THEN RIGHT(LEFT(comments, 19),2) END AS 'Depletion', CASE WHEN ( EV.value LIKE '%on%') THEN 850 END AS 'min_mv_on', CASE WHEN ( EV.value LIKE '%on%') THEN 1200 END AS 'max_mv_on', CASE WHEN ( EV.value LIKE '%off%') THEN 800 END AS 'min_mv_off', CASE WHEN ( EV.value LIKE '%off%') THEN 1100 END AS 'max_mv_off' FROM anode AS AN INNER JOIN event_range AS ER ON AN.event_guid = ER.event_guid INNER JOIN station_point AS SP ON SP.station_guid = ER.station_guid_begin AND SP.station_guid = ER.station_guid_end INNER JOIN #tempextensionvalue AS EV ON SP.route_guid = EV.parentid ) AS DT INNER JOIN #tempsection AS SC ON DT.route_guid = SC.id WHERE ( DT.depletion >= 50 AND DT.depletion <= 75 ) GROUP BY DT.route_guid ) AS MediumCategory ON SC.id = MediumCategory.section_guid UNION SELECT Newid() AS ID, SC.technicalarea AS Network_Name, SC.lineid AS Line_GUID, SC.id AS Section_GUID, Isnull(BadCategory.xxkeyxx, 'Bad') AS 'xxKeyxx', Isnull(BadCategory.xxvaluexx, 0) AS xxValuexx, BadCategory.depletion, BadCategory.mv FROM #tempsection AS SC LEFT JOIN ( SELECT DT.route_guid AS Section_GUID, 'Bad' AS 'xxKeyxx', Isnull(Count(DT.cnt),0) AS 'xxValuexx', Min(DT.mv) AS mV, Max(DT.depletion) AS Depletion FROM ( SELECT SP.route_guid, 'y' AS 'cnt', CASE WHEN ( comments LIKE '%mv%' AND comments NOT LIKE '%Depletion%') THEN LEFT(RIGHT(comments, 6),4) WHEN ( comments LIKE '%mv%' AND comments LIKE '%Depletion%') THEN LEFT(RIGHT(comments, 6),4) END AS 'mV', CASE WHEN ( comments LIKE '%Depletion 100%%') THEN RIGHT(LEFT(comments, 20),3) WHEN ( comments LIKE '%depleted' AND comments NOT LIKE '%mv') THEN LEFT(RIGHT(comments, 12),2) WHEN ( comments LIKE '%Depletion%' AND comments LIKE '%mv') THEN RIGHT(LEFT(comments, 19),2) END AS 'Depletion', CASE WHEN ( EV.value LIKE '%on%') THEN 850 END AS 'min_mv_on', CASE WHEN ( EV.value LIKE '%on%') THEN 1200 END AS 'max_mv_on', CASE WHEN ( EV.value LIKE '%off%') THEN 800 END AS 'min_mv_off', CASE WHEN ( EV.value LIKE '%off%') THEN 1100 END AS 'max_mv_off' FROM anode AS AN INNER JOIN event_range AS ER ON AN.event_guid = ER.event_guid INNER JOIN station_point AS SP ON SP.station_guid = ER.station_guid_begin AND SP.station_guid = ER.station_guid_end INNER JOIN #tempextensionvalue AS EV ON SP.route_guid = EV.parentid ) AS DT INNER JOIN #tempsection AS SC ON DT.route_guid = SC.id WHERE ( DT.depletion > 75 ) GROUP BY DT.route_guid ) AS BadCategory ON SC.id = BadCategory.section_guid ) AS CathodicProtection;

PRINT N'Categories are loaded.';

INSERT INTO tbl_cathodicprotectiondetails SELECT id, network_name, line_guid, section_guid, xxkeyxx, xxvaluexx, depletion AS Depletion, mv FROM #cathodicprotectionPRINT N'Data is loaded into Tbl_CathodicProtection.';

DROP TABLE #tempextensionvalue, #tempsection, #cathodicprotection;

PRINT N'Temporary tables are removed.';END

RandomEvent SSCrazy Points: 2963 More actions · Answer 9

"I wanted to know the thoughts of the MVPs " so you have an issue but want to avoid responses from the majority of the people who use this site. Seems a bit short sighted there are a large number of people out there with decades of experience who either chose not to pursue or couldn't get the sponsorship to apply down the MVP route. Before I go into rant mode can I ask why you only want MVP responses ?

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 10

Thanks everyone for the help
I just wanted to know what is the general rule of thumb for setting the tempdb
I would think it depends on the following items:
- number of databases in the instance
- application type i.e. OLAP, Datawarehouse, OLTP, etc.
- number of reads / writes
- number of CPUs
I am also guessing it affects memory utilization? Hence how to set it up besides the wonderful ideas which you guys gave me here?

thanks
KY

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 11

Hold the phone... the problem the OP is talking about is with TempDB. That pretty much has nothing to do with the query he posted except if you look at the FROM clauses, which all select from Temp Tables. I believe the real problem is that the 3rd party vendor is damned near rebuilding the database (or at least a substantial portion of it) in TempDB. While I absolutely do believe in the principle of using Temp Tables to achieve performance gains using "Divide'n'Conquer" methods, this seems like serious overkill and the code that creates the Temp Tables and the posted code needs to be rewritten to make it operate a whole lot more efficiently.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RandomEvent SSCrazy Points: 2963 More actions · Answer 12

I've seen cases like this before and you tend to find this type of code works fine until you reach a certain "event horizon" level of data. At this point things slowly start to implode as more and more procedures kill off the system either through overuse of TempDB or you start to get an explosion effect in the transaction logs. There are some remediations possibly archiving of data but ultimately they tend to require hardware, a code redevelopment or process alteration. By the sound of it you might have hit the event horizon.

Contributors here have highlighted the options for the TempDb and the code and they are probably your best starting point but ultimately something has to change.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

My quick read says this is a no-brainer: you KNOW how big things get due to repeatedly seeing it get that big (presumably after shrinks - BAD). So simply make the files that big (bigger preferably) and LEAVE THEM THERE.

For tlog sizing go find Kimberly Tripps 2 blog posts on optimizing/sizing tlogs. I use them routinely, which means EVERYONE should be using them. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Eric M Russell SSC Guru Points: 125560 More actions · Answer 14

hurricaneDBA - Wednesday, April 26, 2017 11:15 PM
Dear MVPs
I hope you are all doing great
I am troubleshooting why my Transaction log for one application keeps jumping to 2GB every 2 days and i found that there is a procedure which is run daily by the vendor and the TSQL is below. It insert many values into temporary tables so is there a way to size the temporary database so the transaction log doesnt exponentially grow in size?
SQL Server 2012
Windows 2008 R2 64 bit
Server has 12GB of RAM and 6GB of RAM is assigned to the database
I have already setup the tempdb to use 3 datafiles and 1 log file. The datafiles are 8MB in size and the log file is 1MB
I believe i need to change the size of the tempdb but to what size?
The database in question has a size right now of:
2.7GB
TL is 2.1GB
please advise what i can do to resize the temporary tables and find out why the TL keeps growing. My guess is the insert into the temporary tables
Also the memory utilization is always at 90%

I have already setup the tempdb to use 3 datafiles and 1 log file. The datafiles are 8MB in size and the log file is 1MB

I'm assuming that the startup size. 8 MB and 1 MB ? Wow, that's tiny. When I create a new database, or configure TEMPDB, I start out at 1 GB and then grow at 1 GB increments.

Also the memory utilization is always at 90%

This is normal, healthy, and not related to the issue at hand. What's happening is that pages read by the stored procedure are cached in the buffer, and the buffer will increase until it reaches max limit at which point it will remain at that level and start recycling unused pages to make room for more reads. Despite popular belief, temporary tables are not persisted in memory, but written to TEMPDB.

please advise what i can do to resize the temporary tables and find out why the TL keeps growing. My guess is the insert into the temporary tables

It's not clear what we mean by resizing the temp tables, because their size is determined by the volume of data inserted into them. But, yes, all DML operations are transacted, even for temp tables.

If it helps, you can use the following query to determine what objects are currently allocated in TEMPDB, their size, and other information.
http://www.sqlservercentral.com/scripts/tempdb/151252/

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho