| Author |
Topic |
|
rocking25
Starting Member
5 Posts |
 Posted - 2008-08-05 : 07:18:52
|
| I am using sql server 2005. I have 4 online database. Now there is a problem is that the tempdb database datafile size increases tremendously. In 8 hours it size increases from 8Mb to 100 GB. The four database datafile sizes are 3Gb, 64Mb, 63Mb, 60Mb. I know that after restarting the server, tempdb will come to its initial size of 8Mb.But to restart the server then and now is not possible as it is an online server.Is there any other solution to this problem. |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-08-05 : 08:13:54
|
| http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx#ECNAC |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-05 : 08:20:06
|
| SQL server 2005 uses lot of TempDB than SQL 2000due to:1)online indexing 2)snapshot isolation3)sorting and integrity checkup.4)Temporary tables and table variableYou should enable autogrow and try avoid table variables if you are dealing with more than 100 rows. |
|
|
|
rocking25
Starting Member
5 Posts |
Posted - 2008-08-09 : 03:28:04
|
| I have already enabled the autogrow option. But in one day it grows from just 8Mb to vast 300Gb.I have a 500Gb hard disk installed in the server. So how could I restrict or minimize the tempdb overflowing with data. |
|
|
|
hitesh6221
Starting Member
28 Posts |
Posted - 2008-08-09 : 04:01:41
|
| You should use profiler to see all transection done in TempDB then check these transections and send log in this forum,or check for which trn. tempdb is used.Regards,Hitesh Soni |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-09 : 20:37:21
|
| Had long running transactions on the server? |
|
|
|
hitesh6221
Starting Member
28 Posts |
Posted - 2008-08-11 : 01:30:33
|
| Yes, Now take TLogs backup with "Backup log (Database) to Disk = 'Location'"Then check TempDB sizeRegards,Hitesh Soni |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-11 : 08:07:40
|
quote:
Originally posted by hitesh6221
Yes, Now take TLogs backup with "Backup log (Database) to Disk = 'Location'"Then check TempDB sizeRegards,Hitesh Soni
Why? You can't take log backup in simple recovery model? |
|
|
|
hitesh6221
Starting Member
28 Posts |
Posted - 2008-08-11 : 08:27:08
|
| No You can'tRegards,Hitesh Soni |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-11 : 13:46:52
|
quote:
Originally posted by rocking25
I have already enabled the autogrow option. But in one day it grows from just 8Mb to vast 300Gb.I have a 500Gb hard disk installed in the server. So how could I restrict or minimize the tempdb overflowing with data.
How come it grows from 8MB to 300GB? Did you check activities that involves TempDB? |
|
|
|
rocking25
Starting Member
5 Posts |
Posted - 2008-08-18 : 05:16:49
|
| I have got the solution. now it has stop increasing and tempdb database has a max size of 1 GB. Actually in our company an erp software is being implemented.By mistake one of the user login screen's code got wrong and when the user tries to enter his area he has to wait for hours but the software doesn't let him enter the work area.Actually at the backend the code for login was looking for a report whose path was reset, due to which it was again and again trying to load the whole user data in loop. Which resulted in huge tempdb database size. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-08-18 : 09:27:07
|
| I have similar problem - Got 8 TEMP files...was set at 1024 now each of these have grown to 20 GIG there for size of TEMPDB is 150 GIGHow to get the size back down to 1024 for the tempdev2.ndf - tempdev8.ndfTried doing shrink size is remaining as this.Thanks |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-18 : 09:31:46
|
quote:
Originally posted by TRACEYSQL
I have similar problem - Got 8 TEMP files...was set at 1024 now each of these have grown to 20 GIG there for size of TEMPDB is 150 GIGHow to get the size back down to 1024 for the tempdev2.ndf - tempdev8.ndfTried doing shrink size is remaining as this.Thanks
To shrink , it has to be in Single-User mode Or can restart service to reset default value. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-08-18 : 09:37:49
|
| When you say single mode that means set tempdb with the properties and then set restrict access to single user ..then i can shrink tempdb.Or just restart SQL as the other choice. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-18 : 12:43:59
|
| Donot shrink TempDB. It will again grow. Also do you have acceptable maintenance time to restart service? You will need bigger disk space for TEMPDB as SQL 2005 uses lot of it than SQL 2000. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-18 : 12:47:30
|
| We had a similar issue with tempdb data files growing bigger than they should. The solution was to enable "lock pages in memory" setting. This was on a 64-bit server. All of our 32-bit servers already had this enabled. If you've got this message in your Error Log, then enable it:http://support.microsoft.com/kb/918483Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-18 : 12:51:14
|
| Good point. Tara, did you see any performance impact while enabling it? We have not enabled it as Microsoft said us to use it as last option. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-18 : 12:56:12
|
| The problem only occurred in a test environment. We saw a huge performance problem without it enabled. A report was taking one hour to complete and the tempdb data files grew to 12GB each (we've got 4 tempdb data files, so almost 50GB total). We did not see this issue in any of our other environments where we run the same report. Once we enabled the setting, the report returned in 2 minutes (similar performance as production) and the tempdb data files never went over 1GB each. I've been working with a Microsoft engineer for about a week on another issue, and he said the setting should be enabled on all servers.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-18 : 12:58:07
|
| Thanks for info Tara. |
|
|
|
|
size of tempdb database