Tempdb full errors in SQL 2008 not seen in dbs mig

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Tempdb full errors in SQL 2008 not seen in dbs mig

Author

Topic

nhuanlieu
Starting Member

27 Posts

Posted - 2010-01-19 : 17:23:38

Hello folks,

Perhaps our experts may have some insights and how you would troubleshoot to resolve this issue. Here is our situation, all of dbs are in simple mode and the tempdb db kept filling up in 2-3 days after we restarted SQL to clear the same error. Here are the details of the box.

We are using Microsoft SQL Server 2008 (SP1) - Build 10.0.2531.0 (X64) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600).
This server contains a set of databases we are working to replace another box with Windows 2003/SQL 2005. I basically restored the SQL2005 onto here and let it does the stress test before we goes live with it.
WHile users testing on it and its normal scheduled jobs run like it is in PROD.

We are on a SAN and I have the temps db splits up onto 16 files + 1 mdf. Interesting, only 1 of the 16 ldf files are adding data fast and large.
They are in auto growth with unrestricted grow.The tempdb.ldf file started at 1GB and grows to 175GB in 2-3 days.

I Googled and found this workaround and it applied a flag -1140 in the SQL startup parameter section and rebooted the server. Apparently, that fix did not worked for us for some reasons. It seens that tempdb never reuse the space it took.

http://support.microsoft.com/kb/2000471/en-US

FYI: the current SQL 2005 (less powerful in terms of CPUs and RAM) that we tried to cloned from has 8 ldf files and they never reached 23GB combined.

I see someone find out its a case of isolation level issue http://www.sqlservercentral.com/Forums/Topic847098-391-1.aspx#bm847135, but we can rule out since the isolation levels are the same iin both servers for each of the dbs; our SQL codes in the context that the same code did not have that issue in SQL2005 and it was a backup & restore of it.

SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on
from master.sys.databases ORDER BY 2 DESC

01/19/2010 11:55 AM 175,019,917,312 tempdb.ldf
01/18/2010 10:12 PM 1,342,177,280 tempdb.mdf
01/18/2010 10:34 AM 1,073,741,824 tempdev01.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev02.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev03.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev04.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev05.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev06.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev07.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev08.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev09.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev10.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev11.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev12.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev13.ldf
01/18/2010 10:34 AM 1,073,741,824 tempdev14.ldf
01/18/2010 10:15 PM 1,291,649,024 tempdev15.ldf
17 File(s) 192,686,129,152 bytes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-19 : 17:28:48

quote:
Originally posted by nhuanlieu

We are on a SAN and I have the temps db splits up onto 16 files + 1 mdf. Interesting, only 1 of the 16 ldf files are adding data fast and large.
They are in auto growth with unrestricted grow.The tempdb.ldf file started at 1GB and grows to 175GB in 2-3 days.

You are supposed to have multiple data files, not multiple log files. Only one log file can be used at a time. Once that log file fills up and if another one exists that isn't full, then it'll spill over into the next file.

The recommendation is to have the same number of data files (MDF/NDF) as there are CPUs. The recommendation is not for log files.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-19 : 17:29:49

Please see my blog article regarding the tempdb recommendation: http://weblogs.sqlteam.com/tarad/archive/2008/05/20/60604.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

nhuanlieu
Starting Member

27 Posts

Posted - 2010-01-20 : 12:02:51

Thanks Tara,

That is a good catch on my typos where I was using multiple ldf files instead of mdf files. It may as well be the problem there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources