Setting All Tempdb Data Files to the Same Size

Question

Setting All Tempdb Data Files to the Same Size

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

paul.kohlmiller Valued Member Points: 68 More actions · Answer 1

I've been told that all of this goes out the window if you storage is on a SAN. With fiber-attached SAN you can put all of the tempdb storage on one disk and you don't need one file per processor and balancing the sizes doesn't matter. True?

BTW, the phrase "I've been told" is the old problem of software types like me being told how to do things from hardware types. The information is sometimes valid and sometimes not relevant to what we are doing. So it goes.

SQLBalls SSCarpal Tunnel Points: 4621 More actions · Answer 2

Once Again, I think it depends. If you get the page io latch error then it can only be one thing. depending on capacity and load everything eventually has a limit.

I worked in a place where we did not have a SAN guy for quite some time, when we got one on board he made a lot of changes and performance increased. But i wasn't a network or SAN guy, and was a newer DBA so the changes he made with regards to partitions, striping, and even out of date driver updates, network gear in between the DB and web servers, were all eye openers to me at the time. But are things that have stayed with me ever since.

I would say if the tempdb is appropriately sized, with 2-3 datafiles more you are probably okay. but you would want to make sure using the queries refrenced that the temp db is indeed your problem and it is not an IO problem. Do the investigation and the truth will out.

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field

Keith Edwards Default port Points: 1446 More actions · Answer 3

Based on what I have seen I don't believe the "it all goes out the window if SAN is involved" statement would be correct in most cases. Most of my TempDB data files reside on SAN luns and we dedicate different sets of spindles to those Luns to maximize performance. Typically our TempDB's are broken into four or eight files and that has performed well for us. Thanks

mhoglund Valued Member Points: 59 More actions · Answer 4

Thanks for a good step by step instruction!

I have a failover cluster (SQL 2005 Standard) and followed your instructions but could not connect to the instance once it was started in single user mode. Probably the cluster service IsAlive-polling started to quick? I've fixed it anyway since it wasn't any other logged in at the moment, but for the sake of understanding - how can I connect to a clustered server which is started in single user mode?

/Martin

Aldo Gonzalez SSC Enthusiast Points: 149 More actions · Answer 5

Martin,

Thank you for your observation, which shows a problem with using Cluster Administrator to start a clustered instance in single user mode. The problem is that even if SQL Server Agent is not started, the single connection is exhausted. I assume the connection is being used by the isAlive session the cluster starts frequently.

The proper way here is to log into the Active Node and start SQL Server service in single user mode using any of the methods described as if the instance were not a clustered instance. Once the changes are done, stop the service before using Cluster Administrator to bring SQL Server online.

Thanks again for pointing this out.

Aldo

VS Raavi SSC Enthusiast Points: 130 More actions · Answer 6

I tried your method, It didn't worked because when I started the server with single user mode, I myself was not able to access the db..

I tried using ALTER DATABASE and DBCC SHRINKFILE..with EMPTYFILE option and it worked. I documented the process in the below link:

How to Best Remove Extra TempDB Data Files and Log Files[/url]

Thanks,

Suresh

Aldo Gonzalez SSC Enthusiast Points: 149 More actions · Answer 7

I'm curious, was the sql server agent running? Anyway, I'm glad you found another way. Thanks for sharing.

Aldo