October 5, 2009 at 9:29 am
Marcin Gol [SQL Server MVP] (10/4/2009)
after Kimberly Trip:1) SQL 2000 - #tempdb files = #logical cpus
2) SQL2005+ - #tempdb files = 1/4 ~ 1/2 #logical cpus (typically not more then 8)
Hmm....
I don't agree with the second statement.
According to Microsoft recommendations for the TempDB configuration for SS2005:
...As a general guideline, create one data file for each CPU on the server ....
Note that a dual-core CPU is considered to be two CPUs.
Make each data file the same size; this allows for optimal proportional-fill performance.
October 5, 2009 at 9:33 am
Sergey Vavinskiy (10/5/2009)
Marcin Gol [SQL Server MVP] (10/4/2009)
after Kimberly Trip:1) SQL 2000 - #tempdb files = #logical cpus
2) SQL2005+ - #tempdb files = 1/4 ~ 1/2 #logical cpus (typically not more then 8)
Hmm....
I don't agree with the second statement.
According to Microsoft recommendations for the TempDB configuration for SS2005:
...As a general guideline, create one data file for each CPU on the server ....
Note that a dual-core CPU is considered to be two CPUs.
Make each data file the same size; this allows for optimal proportional-fill performance.
To be honest I dont agree either with the second statement, we had someone from microsoft come in for a session where i was working, and we were told it was 1 tempdb file per CPU.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 5, 2009 at 9:40 am
SQLCAT (Customer Advisory Team) did some tests a while back and found little improvement after 8 files. Hence the updated recommendation.
The other point is that SQL 2005 is much less likely to have tempDB allocation contention and so it's less likely to need large numbers of files. I don't recall the details, but there may even be a degradation with lots and lots (32+) files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2009 at 11:57 am
Hi
I divided the TempDB into 8 files.
I modified the location for the 8 files. The new drive totally assigned to TempDB is 35GB, so I broke each file into 4587520KB (4480MB or 4.375 GB), and put all of them into one folder. So drive H has one folder called TempDB and it is supposed to hold the 8 TempDB files each of 4.375Gb, making up for 35GB.
I stopped the SQL Server service and started again, hoping SQL Server looks in the new directory, and it did. I could see that 7 files were created, but the 8th one was not. Then I tried to stop it again and start it, but since then, I am not able to start the service not connect to the instance.
Perhaps the Folder is using a few bytes and is the reason for the 8th file to crash (not enough memory space for the 8th file); I really wish I had not tried to use every bit of the memory available.. too late, now 🙁
The system is down. What are my options to get the system up and running?
Thanks much
Dan
October 5, 2009 at 12:08 pm
Also want to add that this is on a Cluster, and the 35GB data Drive and the other ones also are in offline/failed state.
October 5, 2009 at 12:24 pm
The error from the log is
http://msdn.microsoft.com/en-us/library/aa258778%28SQL.80%29.aspx
Bbut I am not sure how to free that 2MB Space.
October 5, 2009 at 12:28 pm
What does the SQL error log say? It's a file called Errorlog, just search for it, should be easy enough to find.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2009 at 12:30 pm
The drive is offline - until your cluster can connect to it, there really isn't much SQL server can do?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 5, 2009 at 12:51 pm
Gail
These are the 3 repeated errors in the logs..
The SQL Server (SIS008) service terminated with service-specific error 17058 (0x42A2).
Cluster resource in Resource Group failed.
The SQL Server (SIS008) service terminated with service-specific error 1814 (0x716).
http://msdn.microsoft.com/en-us/library/aa258778%28SQL.80%29.aspx talks about the 1814 error.
Thanks
Dan
October 5, 2009 at 12:52 pm
Matt
the tempDB issue was powerful enough to knock out the cluster drives.
Any thoughts how to restore it back from Cluster Administration?
thx
Dan
October 5, 2009 at 1:02 pm
repent_kog_is_near (10/5/2009)
GailThese are the 3 repeated errors in the logs..
Please post the contents (or partial contents) of the SQL error log. Need the end of the log, probably from when SQL brings master online right up to the end of the log.
NB the SQL error log, not the windows event log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2009 at 1:08 pm
repent_kog_is_near (10/5/2009)
the tempDB issue was powerful enough to knock out the cluster drives.
A disk full error shouldn't take a disk offline. What does the cluster log say?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2009 at 1:13 pm
repent_kog_is_near (10/5/2009)
Mattthe tempDB issue was powerful enough to knock out the cluster drives.
Any thoughts how to restore it back from Cluster Administration?
thx
Dan
Fail over to the other instance. If it can't see the drives either, then your disk subsystem is having trouble. Like Gail mentioned - filling up a disk should NOT take it offline.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 5, 2009 at 1:21 pm
17058 = "access denied" or "cannot access a resource"
1814 = cannot create tempDB/disk full.
Still - it's interesting that the errors occurred in that error. Having the access denied to a drive could easily cascade into a "cannot create tempDB", since you don't have access to the drive. Of course - trying to access an offline resource could also get picked up as "access denied".
Gail's on the right track: I'd think based on the logs that you lost the cluster resource FIRST, causing the other errors.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 5, 2009 at 1:40 pm
This was the first failure error I see in Cluster log..
00000b14.00000bb4::2009/10/05-17:24:32.721 INFO [FM] FmpHandleResourceFailure: taking resource adca9ab1-56de-484b-88b1-42dd21d4e849 and dependents offline
000006c4.00000c2c::2009/10/05-17:24:32.721 ERR SQL Server Agent <SQL Server Agent >: [sqagtres] CheckServiceAlive: Service is dead
00000b14.00000bb4::2009/10/05-17:24:32.721 INFO [CP] CppResourceNotify for resource SQL Server Agent
I feel the SQL Server log is in the data drives. I could not find it in the OS drive. (I searched for any files like err, error, log etc)
Thanks
Dan
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply