Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

tempdb.ldf growing out of control after server migration

Status
Not open for further replies.
edoherty

edoherty

Programmer
Oct 1, 2002
28
0
0
US
We upgraded our primary production SQL Server box a few weeks ago from a 2-processor/1-GB RAM/4 year-old server to a 4-processor/2-GB RAM/brand new server. We've consolidated a few more small database onto this new box, but it added less than 5% more transactions and connections.

Since the migration, the tempdb log file has been growing out of control...it reached 80-GB in less than 3 weeks. The total size of all the data files on the server is approx. 25-GB.

We've noticed 1 particular View, which utilizes a temporary table (@Temp_Table), will cause the tempdb log file to grow nearly 1-GB every time it's executed and it's been the only cause of real growth of the file since we started logging it's size (file size & space used) on Mon. When we execute this View on the old SQL Server box the tempdb log file doesn't always grow and when it does the space used eventually decreases too. In the 4+ years that we used the old box we never noticed the log file size and we had to address issues w/ other log files so we would have noticed if it were ever more than a couple gigs...the disks weren't that large.

We're confused on why the View is causing the tempdb log file grow so quickly and why so large, shouldn't the space used decrease after checkpoints?

Thx for the help!!!
 
Sort by date Sort by votes
What version of SQL was the old box and what version is the new box? Including Service Packs?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
I wanted the space used throughout the execution of the View on the OLD box and the spaced used grew linearally from approx. 20-MB to 800-MB and then about 75% through the execution the space used dropped to approx. 500-MB.

Why isn't it dropping on our new box? The Recovery Model for the tempdb on both boxes is Simple, I don't think it's possible for it to be anything else though.
 
Upvote 0 Downvote
Both boxes have version 8.00.760, which I believe is SP3?
 
Upvote 0 Downvote
It can be changed (not sure why you'd want to). What happens when you try to shrink the files manually using dbcc shrinkfile?

How much of the log file is data and how much is free space?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
That's SQL Server 2000 SP3...

The OS are different though...the 1st box was Windows 2000 Server and the OS of the 2nd box is Windows 2003 Server.
 
Upvote 0 Downvote
I haven't tried shrinking this tempdb log file using dbcc shrinkfile and I don't want to do it at the moment since it's a production box. I've used it before on user db's but I've read that it shouldn't be used on the tempdb log file unless the server is in single user mode because it can cause corruption...which only requires a restart of SQL Server but this isn't a good time.

The space used vary rarely decreases and the amount of free space depends on the increases, because the physical file is consistently growing.
 
Upvote 0 Downvote
If the log isn't purging that would explain why it isn't shrinking. Try doing a backup log tempdb with truncate_only and see what happens.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
It's resolved!!! We discovered a startup parameter (-T3608) used during the migration so we could move the Model & MSDB database for performance and standards reasons hadn't been removed. After removing it and bouncing SQL Server yesterday afternoon the tempdb log file hasn't been larger than 1-GB once and the View that exposed the issue has been executed 3 times since then.

Thx for the help!
 
Upvote 0 Downvote
What is that trace flag used for? I don't see it in BOL?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
ah, I knew I had seen in somewhere. I just couldn't remember where.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
SQL Server in Windows
Replies
1
Views
125
pjw001
pjw001
TheBugSlayer
  • Locked
  • Question
High Availability - insert fails on primary replica but runs on secondary after failover.
Replies
0
Views
79
TheBugSlayer
TheBugSlayer
CodeWell
  • Locked
  • Question
SQL Server 2017 Install Issue - Database Not in Configuration Manager
Replies
0
Views
99
CodeWell
CodeWell
dgillz
  • Locked
  • Question
SQL Server timeouts after updating workstations to Windows 10
Replies
0
Views
75
dgillz
dgillz
bethabernathy
  • Locked
  • Question
Front End Access 365 Database dsn Connection Fails when attempting to link to 2016 SQL server
Replies
1
Views
92
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top