What makes the SQL tempdb grow?

rosenbergl · 2012-04-18

Hello NAV Friends,

What makes the SQL tempdb grow? I recently completed an executable upgrade on NAV 4.0 SP3 from one build to another. Since then, on our live system, the SQL tempdb seems to grow and it is filling up our c: drive partition. This is causing the tape backup to fail. I cannot shrink the tempdb without rebooting the server or stopping and restarting SQL services. SQL also will not let me apply a maintenance plan to tempdb. It did allow me to schedule a recurring SHRINK action, but that worked (maybe?) for one day and then the problem returned today.

The same upgrade was applied to our NAV test server and database, but I don't have the problem there - probably because there is very little activity on that system.

Any help or imput is appreciated.

Thanks!

Lewis.

kriki · 2012-04-18

First question: why does it make your tape backup fail? You shouldn't backup the tempDB.

the tempDB can grow because of these:
-rebuild index with sorting in the tempDB (But if I remember well, you need enterprise edition for this)
-SELECT with ORDER BY that has a lot of records and thus SQL sorts it in the tempDB (this could be a big reason for your problem). You should do a trace on statements that have a lot of disk I/O.
-TSQL temptables and table variables (but probably this is not your problem: NAV doesn't use them [NAV-temptables are maintained in the client memory and client disk]).

-Another thing you can trace are the autogrowth events on the tempDB. This way you know when they are happening and maybe you can correlate them with what is happening at that moment.

David_Singleton · 2012-04-18

rosenbergl wrote:

Hello NAV Friends,

What makes the SQL tempdb grow? I recently completed an executable upgrade on NAV 4.0 SP3 from one build to another. Since then, on our live system, the SQL tempdb seems to grow and it is filling up our c: drive partition.

It depends on what you upgraded. The most likely cause is that you switched from Fast forward to Dynamic cursors.

What build number did you go from and to?

Did Navision ask to update to a newer database version? You are probably now on DB version 62 or later, I think 62 (build 26410) was where Dynamic Cursors were introduced.

rosenbergl · 2012-04-23

I think the tape (Veritas Backlup Exec) is failing because of lack of space on C: (not sure what it needs space for - probably a temp work file).ter

I discovered that the NAV backup (from NAV Tools Menu) is now growing SQL tempdb.

We upgraded from NAV 4.0 SP3 Build 23305 to Build 26565. We never had this problem with the old build.

I think what I need to do now is try to figure out how to move the SQL tempdb from C: to my E: partition. Can I use SQL Server Enterprise Manager (SQL 2000) to delete the tempdb and then recreate it on E: ?

kriki · 2012-04-28

http://www.sql-server-performance.com/2011/moving-sql-server-system-databases/

rosenbergl · 2012-04-30

Thanks everyone. I did move the tempdb to a larger partition with assistance from this article: http://blogs.sqlauthority.com./2007/04/ ... ove-tempdb

We use Expand-IT to run a NAV backup every night. It seems that is the cause of the growing tempdb. If the backup is run manually, there is no problem, but when run by an automated Windows task that executes and Expand-IT script we have the problem.

I think we'll be OK with the tempdb on the larger partition.

bbrown · 2012-04-30

Whay are you bothering with Expand-IT and NAV backups in a SQL environment?

kriki · 2012-05-02

bbrown wrote:

Whay are you bothering with Expand-IT and NAV backups in a SQL environment?

I think the same.
You have a backup-system that is good and fast and easy-to-use. Why using something that you must pay and is slow (native backup IS slow)?

What makes the SQL tempdb grow?

Comments

Categories