After 12.0.1 upgrade tempdb fills disk

I opened a support case as well but wanted to bounce this off other users on here. I recently applied the 12.0.1 update for NPM and since then I have been seeing the tempdb file for the SolarWinds database bloat and eat up all the free space on the disk it resides on. I can restart the server or bounce the SQL server service and it knocks it back down to under 100 MB, given an hour or so it will reach 130+ GB.

Anyone have anything I can try to get this under control? I'm definitely not a DBA here so dealing with queries and scripts is something I've got no problem doing, it's the understanding what the database spits out part which is difficult to understand. I believe that these scripts on this site will have the answer--but I just don't know exactly what I'm looking for and how to get the database to behave again.

For reference: https://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessiv…

Find more posts tagged with

TempDB

database size

Accepted answers

mbird

Ultimately the resolution to this was to reindex all the tables in the Orion database, and then re-run the database configuration wizard--after doing this and restarting all the services the tempdb file no longer expands at the rate which it was. Hope this can help someone else someday!

All comments

mbird

Okay, so I have more information after digging around for a bit yesterday. This dude posts the queries and what the end result will be so it has been super helpful for me, Mr. Non-DBA: Fast Growing temp file

When I run the queries I can see the top process, then get the actual text of it which is... and here's the whole dump:

/** * Copy data from old one to new one. There is logic for version source * version of database. */ CREATE PROCEDURE dbo.upgrade_WirelessIndexes_APs_CopyOneChunk @instanceID UNIQUEIDENTIFIER AS BEGIN SET NOCOUNT ON; DECLARE @startDate AS datetime DECLARE @endDate AS datetime DECLARE @origEndDate AS DATETIME DECLARE @chunkSize AS INT DECLARE @realChunkSize BIGINT DECLARE @movedRecords BIGINT BEGIN TRY BEGIN TRAN -- we get actual progress SELECT @origEndDate = StartDate, @endDate = EndDate, @chunkSize = StartInt32 FROM dbo.LazyUpgradeStatusProgress WHERE LazyUpgradeStatusID = @instanceID SET @startDate = dateadd(dd, @chunkSize, @endDate) SET @movedRecords = 0 DELETE t1 FROM Wireless_AccessPoints_DETAIL t1 INNER JOIN Migration_WirelessIndexMapping imap ON t1.NodeID = imap.NodeID AND t1.[Index] = imap.NewIndex INNER JOIN Wireless_AccessPoints_DETAIL t2 ON t1.LastUpdate = t2.LastUpdate AND t1.NodeID = t2.NodeID AND t2.[Index] = imap.[OldIndex] WHERE t1.[LastUpdate]>@startDate AND t1.[LastUpdate]<=@endDate SET @realChunkSize = @@ROWCOUNT SET @movedRecords = @movedRecords + @realChunkSize UPDATE ap SET ap.[Index] = imap1.OldIndex FROM Wireless_AccessPoints_DETAIL ap INNER JOIN Migration_WirelessIndexMapping imap1 ON ap.NodeID = imap1.NodeID AND ap.[Index] = imap1.NewIndex AND imap1.Prefix = 'A' WHERE ap.[LastUpdate]>@startDate AND ap.[LastUpdate]<=@endDate SET @realChunkSize = @realChunkSize + @@ROWCOUNT SET @movedRecords = @movedRecords + @realChunkSize IF @realChunkSize>0 AND @chunkSize>-1 BEGIN SET @chunkSize=-1 END UPDATE t SET t.[Available] = s.[Available] , t.[Clients] = s.[Clients] , t.[ClientsMin] = s.[ClientsMin] , t.[ClientsMax] = s.[ClientsMax] , t.[InBytes] = s.[InBytes] , t.[OutBytes] = s.[OutBytes] , t.[InPackets] = s.[InPackets] , t.[OutPackets] = s.[OutPackets] , t.[InBps] = s.[InBps] , t.[InBpsMin] = s.[InBpsMin] , t.[InBpsMax] = s.[InBpsMax] , t.[OutBps] = s.[OutBps] , t.[OutBpsMin] = s.[OutBpsMin] , t.[OutBpsMax] = s.[OutBpsMax] , t.[InPps] = s.[InPps] , t.[InPpsMin] = s.[InPpsMin] , t.[InPpsMax] = s.[InPpsMax] , t.[OutPps] = s.[OutPps] , t.[OutPpsMin] = s.[OutPpsMin] , t.[OutPpsMax] = s.[OutPpsMax] FROM Wireless_AccessPoints_HOURLY t INNER JOIN ( SELECT t1.[NodeID], coalesce(t3.[Index], t1.[Index]) as [Index], MIN(t1.[ParentIndex]) as [ParentIndex], AVG(t1.[Available]) as [Available], t1.[LastUpdate], AVG(t1.[Clients]) as [Clients], MIN(t1.[ClientsMin]) as [ClientsMin], MAX(t1.[ClientsMax]) as [ClientsMax], AVG(t1.[InBytes]) as [InBytes], AVG(t1.[OutBytes]) as [OutBytes], AVG(t1.[InPackets]) as [InPackets], AVG(t1.[OutPackets]) as [OutPackets], AVG(t1.[InBps]) as [InBps], MIN(t1.[InBpsMin]) as [InBpsMin], MAX(t1.[InBpsMax]) as [InBpsMax], AVG(t1.[OutBps]) as [OutBps], MIN(t1.[OutBpsMin]) as [OutBpsMin], MAX(t1.[OutBpsMax]) as [OutBpsMax], AVG(t1.[InPps]) as [InPps], MIN(t1.[InPpsMin]) as [InPpsMin], MAX(t1.[InPpsMax]) as [InPpsMax], AVG(t1.[OutPps]) as [OutPps], MIN(t1.[OutPpsMin]) as [OutPpsMin], MAX(t1.[OutPpsMax]) as [OutPpsMax] FROM Wireless_AccessPoints_HOURLY t1 LEFT JOIN Migration_WirelessIndexMapping imapOld ON t1.NodeID = imapOld.NodeID AND t1.[Index] = imapOld.OldIndex LEFT JOIN Wireless_AccessPoints_HOURLY t2 ON t1.NodeID = t2.NodeID AND t2.[Index] = imapOld.NewIndex LEFT JOIN Migration_WirelessIndexMapping imapNew ON t1.NodeID = imapNew.NodeID AND t1.[Index] = imapNew.NewIndex LEFT JOIN Wireless_AccessPoints_HOURLY t3 ON t1.NodeID = t3.NodeID AND t3.[Index] = imapNew.OldIndex WHERE (t1.LastUpdate = t2.LastUpdate OR t1.LastUpdate = t3.LastUpdate) AND t1.[LastUpdate]>@startDate AND t1.[LastUpdate]<=@endDate GROUP BY t1.[NodeID], coalesce(t3.[Index], t1.[Index]), t1.LastUpdate ) s ON t.NodeID = s.NodeID AND t.[Index] = s.[Index] AND t.LastUpdate = s.LastUpdate SET @realChunkSize = @@ROWCOUNT SET @movedRecords = @movedRecords + @realChunkSize DELETE t1 FROM Wireless_AccessPoints_HOURLY t1 INNER JOIN Migration_WirelessIndexMapping imap ON t1.NodeID = imap.NodeID AND t1.[Index] = imap.NewIndex INNER JOIN Wireless_AccessPoints_HOURLY t2 ON t1.NodeID = t2.NodeID AND t2.[Index] = imap.[OldIndex] AND t1.LastUpdate = t2.LastUpdate WHERE t1.[LastUpdate]>@startDate AND t1.[LastUpdate]<=@endDate SET @realChunkSize = @realChunkSize + @@ROWCOUNT SET @movedRecords = @movedRecords + @realChunkSize UPDATE ap SET ap.[Index] = imap1.OldIndex FROM Wireless_AccessPoints_HOURLY ap INNER JOIN Migration_WirelessIndexMapping imap1 ON ap.NodeID = imap1.NodeID AND ap.[Index] = imap1.NewIndex AND imap1.Prefix = 'A' WHERE ap.[LastUpdate]>@startDate AND ap.[LastUpdate]<=@endDate SET @realChunkSize = @realChunkSize + @@ROWCOUNT SET @movedRecords = @movedRecords + @realChunkSize IF @realChunkSize>0 AND @chunkSize>-10 BEGIN SET @chunkSize=-10 END UPDATE t SET t.[Available] = s.[Available] , t.[Clients] = s.[Clients] , t.[ClientsMin] = s.[ClientsMin] , t.[ClientsMax] = s.[ClientsMax] , t.[InBytes] = s.[InBytes] , t.[OutBytes] = s.[OutBytes] , t.[InPackets] = s.[InPackets] , t.[OutPackets] = s.[OutPackets] , t.[InBps] = s.[InBps] , t.[InBpsMin] = s.[InBpsMin] , t.[InBpsMax] = s.[InBpsMax] , t.[OutBps] = s.[OutBps] , t.[OutBpsMin] = s.[OutBpsMin] , t.[OutBpsMax] = s.[OutBpsMax] , t.[InPps] = s.[InPps] , t.[InPpsMin] = s.[InPpsMin] , t.[InPpsMax] = s.[InPpsMax] , t.[OutPps] = s.[OutPps] , t.[OutPpsMin] = s.[OutPpsMin] , t.[OutPpsMax] = s.[OutPpsMax] FROM Wireless_AccessPoints_DAILY t INNER JOIN ( SELECT t1.[NodeID], coalesce(t3.[Index], t1.[Index]) as [Index], MIN(t1.[ParentIndex]) as [ParentIndex], AVG(t1.[Available]) as [Available], t1.[LastUpdate], AVG(t1.[Clients]) as [Clients], MIN(t1.[ClientsMin]) as [ClientsMin], MAX(t1.[ClientsMax]) as [ClientsMax], AVG(t1.[InBytes]) as [InBytes], AVG(t1.[OutBytes]) as [OutBytes], AVG(t1.[InPackets]) as [InPackets], AVG(t1.[OutPackets]) as [OutPackets], AVG(t1.[InBps]) as [InBps], MIN(t1.[InBpsMin]) as [InBpsMin], MAX(t1.[InBpsMax]) as [InBpsMax], AVG(t1.[OutBps]) as [OutBps], MIN(t1.[OutBpsMin]) as [OutBpsMin], MAX(t1.[OutBpsMax]) as [OutBpsMax], AVG(t1.[InPps]) as [InPps], MIN(t1.[InPpsMin]) as [InPpsMin], MAX(t1.[InPpsMax]) as [InPpsMax], AVG(t1.[OutPps]) as [OutPps], MIN(t1.[OutPpsMin]) as [OutPpsMin], MAX(t1.[OutPpsMax]) as [OutPpsMax] FROM Wireless_AccessPoints_DAILY t1 LEFT JOIN Migration_WirelessIndexMapping imapOld ON t1.NodeID = imapOld.NodeID AND t1.[Index] = imapOld.OldIndex LEFT JOIN Wireless_AccessPoints_DAILY t2 ON t1.NodeID = t2.NodeID AND t2.[Index] = imapOld.NewIndex LEFT JOIN Migration_WirelessIndexMapping imapNew ON t1.NodeID = imapNew.NodeID AND t1.[Index] = imapNew.NewIndex LEFT JOIN Wireless_AccessPoints_DAILY t3 ON t1.NodeID = t3.NodeID AND t3.[Index] = imapNew.OldIndex WHERE (t1.LastUpdate = t2.LastUpdate OR t1.LastUpdate = t3.LastUpdate) AND t1.[LastUpdate]>@startDate AND t1.[LastUpdate]<=@endDate GROUP BY t1.[NodeID], coalesce(t3.[Index], t1.[Index]), t1.LastUpdate ) s ON t.NodeID = s.NodeID AND t.[Index] = s.[Index] AND t.LastUpdate = s.LastUpdate SET @realChunkSize = @@ROWCOUNT SET @movedRecords = @movedRecords + @realChunkSize DELETE t1 FROM Wireless_AccessPoints_DAILY t1 INNER JOIN Migration_WirelessIndexMapping imap ON t1.NodeID = imap.NodeID AND t1.[Index] = imap.NewIndex INNER JOIN Wireless_AccessPoints_DAILY t2 ON t1.NodeID = t2.NodeID AND t2.[Index] = imap.[OldIndex] AND t1.LastUpdate = t2.LastUpdate WHERE t1.[LastUpdate]>@startDate AND t1.[LastUpdate]<=@endDate SET @realChunkSize = @realChunkSize + @@ROWCOUNT SET @movedRecords = @movedRecords + @realChunkSize UPDATE ap SET ap.[Index] = imap1.OldIndex FROM Wireless_AccessPoints_DAILY ap INNER JOIN Migration_WirelessIndexMapping imap1 ON ap.NodeID = imap1.NodeID AND ap.[Index] = imap1.NewIndex AND imap1.Prefix = 'A' WHERE ap.[LastUpdate]>@startDate AND ap.[LastUpdate]<=@endDate SET @realChunkSize = @realChunkSize + @@ROWCOUNT SET @movedRecords = @movedRecords + @realChunkSize IF @realChunkSize>0 AND @chunkSize>-100 BEGIN SET @chunkSize=-100 END DECLARE @alreadyMigrated AS INT SET @alreadyMigrated = DATEDIFF(HOUR, @startDate, @origEndDate) /* and actualize info tables*/ UPDATE dbo.LazyUpgradeStatusProgress SET EndDate = @startDate, StartInt32 = @chunkSize WHERE LazyUpgradeStatusID = @instanceID UPDATE dbo.LazyUpgradeStatus SET LastUpdateDateUtc = GETUTCDATE(), MigratedRowCount = @alreadyMigrated WHERE ID = @instanceID COMMIT TRAN END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK END CATCH END

Got all that? I saw an entry appearing in the event logs in Orion where it was running "Background upgrade for Indexes in Wireless Interfaces historical tables started." This is the process that's causing my problem and I can kill it but it will eventually start back up. I can keep the disk from filling up during the day, but when I came in this morning it had filled the disk right up overnight.

I found this support article: Background upgrade for interfaces cannot complete - SolarWinds Worldwide, LLC. Help and Support However the script provided does not complete successfully, nor do the commands to truncate the data.

Logged on as sa I receive: "Cannot find the object "Migration_InterfaceTraffic_Detail" because it does not exist or you do not have permissions."

Database corruption? Scheduled job? Maybe not related to the 12.0.1 upgrade after all? Can anyone point me in the right direction on this? I've got a "what", but I don't have a "where" or "why" to go along with it so I'm stuck when it comes to a resolution rather than a workaround.

Background upgrade for Indexes in Wireless Interfaces historical tables started.

mbird

jkuvlesk

Hi bmird, Thank you for this post. We are aware that APE installations are confusing and have plans to update our documentation to make it more obvious. We may be reaching out to you soon to get your feedback on the enhancements. I will also provide feedback to our support management team on the need to educate this rep on the customer portal and APEs.

Here are some resources in the meantime that should also help.

Scaling & Performance Tuning Course: This class focuses on maximizing performance. This means tuning your equipment to optimize its capabilities, tuning your polling intervals to capture the data you need without bogging down the database with less critical data, and adding additional pollers for load balancing and better network visibility.

Upcoming Dates: November 15th

Video: https://support.solarwinds.com/Success_Center/Network_Performance_Monitor_(NPM)/How_to_install_an_additional_polling_engine_-_Video

Documentation: https://support.solarwinds.com/Success_Center/Network_Performance_Monitor_(NPM)/Orion_product_installers_for_main_poller_additional_polling_engine_additional_web_servers_and_HA

mbird

Thanks for responding so quickly--I'll have to check those links out and maybe join that training session as well.