InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 15:43:30 GMT20http://www.sqlservercentral.com/Forums/Topic1398721-391-1.aspx[quote][b]@SQLFRNDZ (12/20/2012)[/b][hr]This is what I got so far and I need help with looping script to add files to number of times based on condition cpu_count.Here is the script I'm able to write so farDEclare @value1 intDEclare @value2 intDECLARE @OUTPUT Varchar(100)select @value1=COUNT(*)-1 from sys.sysfiles SELECT @value2=cpu_count FROM sys.dm_os_sys_infoselect @OUTPUT =Case when @value1 = @value2 THEN 'GoodToGo'ELSE "-- Needs a Script Here --"ENDSELECT @OUTPUT [TEMPDB FILES]GO[/quote]Ok, from there - what would you pseudo-code to achieve your goal?Thu, 20 Dec 2012 21:30:59 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1398721-391-1.aspx[quote][b]SQLRNNR (12/20/2012)[/b][hr]Well, what have you got for your script so far?[/quote]This is what I got so far and I need help with looping script to add files to number of times based on condition cpu_count.Here is the script I'm able to write so farDEclare @value1 intDEclare @value2 intDECLARE @OUTPUT Varchar(100)select @value1=COUNT(*)-1 from sys.sysfiles SELECT @value2=cpu_count FROM sys.dm_os_sys_infoselect @OUTPUT =Case when @value1 = @value2 THEN 'GoodToGo'ELSE "-- Needs a Script Here --"ENDSELECT @OUTPUT [TEMPDB FILES]GOThu, 20 Dec 2012 17:42:32 GMT@SQLFRNDZhttp://www.sqlservercentral.com/Forums/Topic1398721-391-1.aspxWell, what have you got for your script so far?Thu, 20 Dec 2012 16:33:21 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1398721-391-1.aspxI knew it is bad practise for tempdb but want to see how we can write a query to dynamically add files based on a condition.Thu, 20 Dec 2012 16:29:26 GMT@SQLFRNDZhttp://www.sqlservercentral.com/Forums/Topic1398721-391-1.aspxErr...bad idea. Really bad idea.One file per core is an old recommendation, it's not a good idea under all circumstances without consideration or investigation.[url]http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx[/url]Thu, 20 Dec 2012 12:26:05 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1398721-391-1.aspxYes, I need only the script..Appreciate your help!Thu, 20 Dec 2012 11:07:38 GMT@SQLFRNDZhttp://www.sqlservercentral.com/Forums/Topic1398721-391-1.aspxAre these the only considerations for this script? Or do you also want to adhere to BP recommendations as can be found [url=http://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/][b][u]here[/u][/b][/url]?Wed, 19 Dec 2012 21:24:05 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1398721-391-1.aspxHere is the scenario:Step1: Verify number of CPU's SELECT cpu_count FROM sys.dm_os_sys_infoStep2: Verify number of Datafiles select COUNT(*)-1 from tempdb.sys.sysfiles Step 3: If Count in steps and step2, we need add additional files using t-sql ?Can I get help with script please ?Wed, 19 Dec 2012 17:44:03 GMT@SQLFRNDZ