Have you ever got this TempDB error ?

My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
By default, the TempDB locates at C drive and I increased its size by adding
2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
However, I don't know why the TempDB always cannot recognize these 2 D-drive
files when I come to office every morning.
The 2 D-drive files still exist in D drive but from the SQL Enterprise
Manager tells me the TempDB only has C-drive files.
Why, why the D-drive file pointers always become invvalid ?
Hi,
Can you execute the below statement in query analyzer:-
sp_helpdb tempdb
If it is not showing D drive file, then you have not added the new files
properly.
Refer ALTER DATABASE in books online to add a new file to D Drive. (You can
also use Enterprise manager)
Thanks
Hari
MCDBA
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>
|||What does "cannot recognize" mean? Do you get an error message (as your
subject implies)? If so, what is it?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>
|||It means in SQL Enterprise Manager, it only says the TempDB has 2 C-drive
files but not have the 2 D-drive files I added last night.
However, the 2 D-drive files still exist in the D drive when I used File
Explorer to search them.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:#uPqAqMREHA.2972@.TK2MSFTNGP09.phx.gbl...
> What does "cannot recognize" mean? Do you get an error message (as your
> subject implies)? If so, what is it?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c97hfp$rtr2@.imsp212.netvigator.com...
> adding
> D-drive
>
|||Hi,
Can you execute the below statement in query analyzer:-
sp_helpdb tempdb
If it is not showing D drive file, then you have not added the new files
properly. Please recreate the files using using ALTER DATABASE command.
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezZkyaMREHA.3944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you execute the below statement in query analyzer:-
> sp_helpdb tempdb
> If it is not showing D drive file, then you have not added the new files
> properly.
> Refer ALTER DATABASE in books online to add a new file to D Drive. (You
can
> also use Enterprise manager)
> Thanks
> Hari
> MCDBA
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c97hfp$rtr2@.imsp212.netvigator.com...
> adding
> D-drive
>

Have you ever got this TempDB error ?

My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
By default, the TempDB locates at C drive and I increased its size by adding
2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
However, I don't know why the TempDB always cannot recognize these 2 D-drive
files when I come to office every morning.
The 2 D-drive files still exist in D drive but from the SQL Enterprise
Manager tells me the TempDB only has C-drive files.
Why, why the D-drive file pointers always become invvalid ?Hi,
Can you execute the below statement in query analyzer:-
sp_helpdb tempdb
If it is not showing D drive file, then you have not added the new files
properly.
Refer ALTER DATABASE in books online to add a new file to D Drive. (You can
also use Enterprise manager)
Thanks
Hari
MCDBA
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>|||What does "cannot recognize" mean? Do you get an error message (as your
subject implies)? If so, what is it?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>|||It means in SQL Enterprise Manager, it only says the TempDB has 2 C-drive
files but not have the 2 D-drive files I added last night.
However, the 2 D-drive files still exist in the D drive when I used File
Explorer to search them.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:#uPqAqMREHA.2972@.TK2MSFTNGP09.phx.gbl...
> What does "cannot recognize" mean? Do you get an error message (as your
> subject implies)? If so, what is it?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c97hfp$rtr2@.imsp212.netvigator.com...
> > My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> > By default, the TempDB locates at C drive and I increased its size by
> adding
> > 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> >
> > However, I don't know why the TempDB always cannot recognize these 2
> D-drive
> > files when I come to office every morning.
> > The 2 D-drive files still exist in D drive but from the SQL Enterprise
> > Manager tells me the TempDB only has C-drive files.
> >
> > Why, why the D-drive file pointers always become invvalid ?
> >
> >
> >
>|||Hi,
Can you execute the below statement in query analyzer:-
sp_helpdb tempdb
If it is not showing D drive file, then you have not added the new files
properly. Please recreate the files using using ALTER DATABASE command.
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezZkyaMREHA.3944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you execute the below statement in query analyzer:-
> sp_helpdb tempdb
> If it is not showing D drive file, then you have not added the new files
> properly.
> Refer ALTER DATABASE in books online to add a new file to D Drive. (You
can
> also use Enterprise manager)
> Thanks
> Hari
> MCDBA
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c97hfp$rtr2@.imsp212.netvigator.com...
> > My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> > By default, the TempDB locates at C drive and I increased its size by
> adding
> > 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> >
> > However, I don't know why the TempDB always cannot recognize these 2
> D-drive
> > files when I come to office every morning.
> > The 2 D-drive files still exist in D drive but from the SQL Enterprise
> > Manager tells me the TempDB only has C-drive files.
> >
> > Why, why the D-drive file pointers always become invvalid ?
> >
> >
> >
>

Have you ever got this TempDB error ?

My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
By default, the TempDB locates at C drive and I increased its size by adding
2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
However, I don't know why the TempDB always cannot recognize these 2 D-drive
files when I come to office every morning.
The 2 D-drive files still exist in D drive but from the SQL Enterprise
Manager tells me the TempDB only has C-drive files.
Why, why the D-drive file pointers always become invvalid ?Hi,
Can you execute the below statement in query analyzer:-
sp_helpdb tempdb
If it is not showing D drive file, then you have not added the new files
properly.
Refer ALTER DATABASE in books online to add a new file to D Drive. (You can
also use Enterprise manager)
Thanks
Hari
MCDBA
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>|||What does "cannot recognize" mean? Do you get an error message (as your
subject implies)? If so, what is it?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>

Hardware specs( No. of RAID Controllers)

We want to go with SQL recommendations of seperate drives for
backups,data,log,tempdb and OS.
So we are thinking of
2*72 RAID 1 for OS
2*72 RAID 1 for Tempdb
2*72 RAID 1 for Log
4*72 RAID 10 for Data
3*72 RAID 5 for backups,etc.
So the question is should I use one RAID controller to tie all these drives
too ? Just looking at it from a price perspective. If not, then 2 and if so,
what areas should I distribute the above layout to ?
Let me know if 1 RAID controller would work.
Thanks
I would combine the tempdb and log drive and treat tempdb files like the
rest of the databases. It is better to have logs on 0+1. If the controller
is decent, one will be able to handle that spindle count.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:OMbGoL8TIHA.4684@.TK2MSFTNGP06.phx.gbl...
> We want to go with SQL recommendations of seperate drives for
> backups,data,log,tempdb and OS.
> So we are thinking of
> 2*72 RAID 1 for OS
> 2*72 RAID 1 for Tempdb
> 2*72 RAID 1 for Log
> 4*72 RAID 10 for Data
> 3*72 RAID 5 for backups,etc.
> So the question is should I use one RAID controller to tie all these
> drives too ? Just looking at it from a price perspective. If not, then 2
> and if so, what areas should I distribute the above layout to ?
> Let me know if 1 RAID controller would work.
> Thanks
|||Are you saying as such ?
2*72 RAID 1 for OS
4*72 RAID 10 for Tempdb(data and log) and Log Files
4*72 RAID 10 for Data Files
3*72 RAID 5 for backups,etc.
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:185DB471-3652-44A0-AAD0-33E056397B92@.microsoft.com...
>I would combine the tempdb and log drive and treat tempdb files like the
>rest of the databases. It is better to have logs on 0+1. If the controller
>is decent, one will be able to handle that spindle count.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
> "Hassan" <hassan@.test.com> wrote in message
> news:OMbGoL8TIHA.4684@.TK2MSFTNGP06.phx.gbl...
>
|||>I would combine the tempdb and log drive and treat tempdb files like the
>rest of the databases. It is better to have logs on 0+1. If the controller
>is decent, one will be able to handle that spindle count.
Logs shouldn't be shared with other files. Log writes during COMMIT are
synchronous and so response time will be negatively affected due to
competing with other file I/O.
Hope this helps.
Dan Guzman
SQL Server MVP
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:185DB471-3652-44A0-AAD0-33E056397B92@.microsoft.com...
>I would combine the tempdb and log drive and treat tempdb files like the
>rest of the databases. It is better to have logs on 0+1. If the controller
>is decent, one will be able to handle that spindle count.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
> "Hassan" <hassan@.test.com> wrote in message
> news:OMbGoL8TIHA.4684@.TK2MSFTNGP06.phx.gbl...
>
|||is it best to put both the log files for the data and tempdb together ? Or
have tempdb on its own disks that include both its data files and log files
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:7C67FDC2-4E72-4DEE-80F7-F5EEDCC44DD2@.microsoft.com...
> Logs shouldn't be shared with other files. Log writes during COMMIT are
> synchronous and so response time will be negatively affected due to
> competing with other file I/O.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:185DB471-3652-44A0-AAD0-33E056397B92@.microsoft.com...
>
|||> is it best to put both the log files for the data and tempdb together ? Or
> have tempdb on its own disks that include both its data files and log
> files
I would place the tempdb log file to the same drive/array as the other log
files and on a controller with a lot of write cache. Tempdb data files may
be heavily used and separating data files from log files will help optimize
the sequential writes to the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:OqcmMl8TIHA.280@.TK2MSFTNGP03.phx.gbl...
> is it best to put both the log files for the data and tempdb together ? Or
> have tempdb on its own disks that include both its data files and log
> files
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:7C67FDC2-4E72-4DEE-80F7-F5EEDCC44DD2@.microsoft.com...
>
|||> 2*72 RAID 1 for OS
> 4*72 RAID 10 for Log Files
> 4*72 RAID 10 for Data Files
> 3*72 RAID 5 for backups,etc.
And treat tempdb as any other database with this spindle count. Data files
on data drive. Log files on log drive. When you don't don't have many
spindles, it is best to keep it simple.
There is even a case that could be made this config:
> 2*72 RAID 1 for OS
> 8*72 RAID 10 for Data Files and Log Files
> 3*72 RAID 5 for backups,etc.
"Hassan" <hassan@.test.com> wrote in message
news:umf6Sb8TIHA.5288@.TK2MSFTNGP04.phx.gbl...
> Are you saying as such ?
> 2*72 RAID 1 for OS
> 4*72 RAID 10 for Tempdb(data and log) and Log Files
> 4*72 RAID 10 for Data Files
> 3*72 RAID 5 for backups,etc.
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:185DB471-3652-44A0-AAD0-33E056397B92@.microsoft.com...
>
|||Would you also do it with one controller ?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:467C193D-BFB7-45C1-BC99-DF7084FF714C@.microsoft.com...
> I would place the tempdb log file to the same drive/array as the other log
> files and on a controller with a lot of write cache. Tempdb data files
> may be heavily used and separating data files from log files will help
> optimize the sequential writes to the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hassan" <hassan@.test.com> wrote in message
> news:OqcmMl8TIHA.280@.TK2MSFTNGP03.phx.gbl...
>
|||> Would you also do it with one controller ?
It depends on budget and controller capabilities. A single controller is
probably adequate as long as it's a decent one with features like lots of
cache, configurable cache read/write ratio and battery backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:usb%23Wc9TIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Would you also do it with one controller ?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:467C193D-BFB7-45C1-BC99-DF7084FF714C@.microsoft.com...
>

Hardware specs( No. of RAID Controllers)

We want to go with SQL recommendations of seperate drives for
backups,data,log,tempdb and OS.
So we are thinking of
2*72 RAID 1 for OS
2*72 RAID 1 for Tempdb
2*72 RAID 1 for Log
4*72 RAID 10 for Data
3*72 RAID 5 for backups,etc.
So the question is should I use one RAID controller to tie all these drives
too ? Just looking at it from a price perspective. If not, then 2 and if so,
what areas should I distribute the above layout to ?
Let me know if 1 RAID controller would work.
ThanksI would combine the tempdb and log drive and treat tempdb files like the
rest of the databases. It is better to have logs on 0+1. If the controller
is decent, one will be able to handle that spindle count.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:OMbGoL8TIHA.4684@.TK2MSFTNGP06.phx.gbl...
> We want to go with SQL recommendations of seperate drives for
> backups,data,log,tempdb and OS.
> So we are thinking of
> 2*72 RAID 1 for OS
> 2*72 RAID 1 for Tempdb
> 2*72 RAID 1 for Log
> 4*72 RAID 10 for Data
> 3*72 RAID 5 for backups,etc.
> So the question is should I use one RAID controller to tie all these
> drives too ? Just looking at it from a price perspective. If not, then 2
> and if so, what areas should I distribute the above layout to ?
> Let me know if 1 RAID controller would work.
> Thanks|||Are you saying as such ?
2*72 RAID 1 for OS
4*72 RAID 10 for Tempdb(data and log) and Log Files
4*72 RAID 10 for Data Files
3*72 RAID 5 for backups,etc.
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:185DB471-3652-44A0-AAD0-33E056397B92@.microsoft.com...
>I would combine the tempdb and log drive and treat tempdb files like the
>rest of the databases. It is better to have logs on 0+1. If the controller
>is decent, one will be able to handle that spindle count.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
> "Hassan" <hassan@.test.com> wrote in message
> news:OMbGoL8TIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> We want to go with SQL recommendations of seperate drives for
>> backups,data,log,tempdb and OS.
>> So we are thinking of
>> 2*72 RAID 1 for OS
>> 2*72 RAID 1 for Tempdb
>> 2*72 RAID 1 for Log
>> 4*72 RAID 10 for Data
>> 3*72 RAID 5 for backups,etc.
>> So the question is should I use one RAID controller to tie all these
>> drives too ? Just looking at it from a price perspective. If not, then 2
>> and if so, what areas should I distribute the above layout to ?
>> Let me know if 1 RAID controller would work.
>> Thanks
>|||>I would combine the tempdb and log drive and treat tempdb files like the
>rest of the databases. It is better to have logs on 0+1. If the controller
>is decent, one will be able to handle that spindle count.
Logs shouldn't be shared with other files. Log writes during COMMIT are
synchronous and so response time will be negatively affected due to
competing with other file I/O.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:185DB471-3652-44A0-AAD0-33E056397B92@.microsoft.com...
>I would combine the tempdb and log drive and treat tempdb files like the
>rest of the databases. It is better to have logs on 0+1. If the controller
>is decent, one will be able to handle that spindle count.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
> "Hassan" <hassan@.test.com> wrote in message
> news:OMbGoL8TIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> We want to go with SQL recommendations of seperate drives for
>> backups,data,log,tempdb and OS.
>> So we are thinking of
>> 2*72 RAID 1 for OS
>> 2*72 RAID 1 for Tempdb
>> 2*72 RAID 1 for Log
>> 4*72 RAID 10 for Data
>> 3*72 RAID 5 for backups,etc.
>> So the question is should I use one RAID controller to tie all these
>> drives too ? Just looking at it from a price perspective. If not, then 2
>> and if so, what areas should I distribute the above layout to ?
>> Let me know if 1 RAID controller would work.
>> Thanks
>|||is it best to put both the log files for the data and tempdb together ? Or
have tempdb on its own disks that include both its data files and log files
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:7C67FDC2-4E72-4DEE-80F7-F5EEDCC44DD2@.microsoft.com...
> >I would combine the tempdb and log drive and treat tempdb files like the
> >rest of the databases. It is better to have logs on 0+1. If the
> >controller is decent, one will be able to handle that spindle count.
> Logs shouldn't be shared with other files. Log writes during COMMIT are
> synchronous and so response time will be negatively affected due to
> competing with other file I/O.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:185DB471-3652-44A0-AAD0-33E056397B92@.microsoft.com...
>>I would combine the tempdb and log drive and treat tempdb files like the
>>rest of the databases. It is better to have logs on 0+1. If the controller
>>is decent, one will be able to handle that spindle count.
>> --
>> Jason Massie
>> www: http://statisticsio.com
>> rss: http://feeds.feedburner.com/statisticsio
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OMbGoL8TIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> We want to go with SQL recommendations of seperate drives for
>> backups,data,log,tempdb and OS.
>> So we are thinking of
>> 2*72 RAID 1 for OS
>> 2*72 RAID 1 for Tempdb
>> 2*72 RAID 1 for Log
>> 4*72 RAID 10 for Data
>> 3*72 RAID 5 for backups,etc.
>> So the question is should I use one RAID controller to tie all these
>> drives too ? Just looking at it from a price perspective. If not, then 2
>> and if so, what areas should I distribute the above layout to ?
>> Let me know if 1 RAID controller would work.
>> Thanks
>|||> is it best to put both the log files for the data and tempdb together ? Or
> have tempdb on its own disks that include both its data files and log
> files
I would place the tempdb log file to the same drive/array as the other log
files and on a controller with a lot of write cache. Tempdb data files may
be heavily used and separating data files from log files will help optimize
the sequential writes to the log.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:OqcmMl8TIHA.280@.TK2MSFTNGP03.phx.gbl...
> is it best to put both the log files for the data and tempdb together ? Or
> have tempdb on its own disks that include both its data files and log
> files
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:7C67FDC2-4E72-4DEE-80F7-F5EEDCC44DD2@.microsoft.com...
>> >I would combine the tempdb and log drive and treat tempdb files like the
>> >rest of the databases. It is better to have logs on 0+1. If the
>> >controller is decent, one will be able to handle that spindle count.
>> Logs shouldn't be shared with other files. Log writes during COMMIT are
>> synchronous and so response time will be negatively affected due to
>> competing with other file I/O.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "jason" <jason-r3move@.statisticsio.com> wrote in message
>> news:185DB471-3652-44A0-AAD0-33E056397B92@.microsoft.com...
>>I would combine the tempdb and log drive and treat tempdb files like the
>>rest of the databases. It is better to have logs on 0+1. If the
>>controller is decent, one will be able to handle that spindle count.
>> --
>> Jason Massie
>> www: http://statisticsio.com
>> rss: http://feeds.feedburner.com/statisticsio
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OMbGoL8TIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> We want to go with SQL recommendations of seperate drives for
>> backups,data,log,tempdb and OS.
>> So we are thinking of
>> 2*72 RAID 1 for OS
>> 2*72 RAID 1 for Tempdb
>> 2*72 RAID 1 for Log
>> 4*72 RAID 10 for Data
>> 3*72 RAID 5 for backups,etc.
>> So the question is should I use one RAID controller to tie all these
>> drives too ? Just looking at it from a price perspective. If not, then
>> 2 and if so, what areas should I distribute the above layout to ?
>> Let me know if 1 RAID controller would work.
>> Thanks
>>
>|||> 2*72 RAID 1 for OS
> 4*72 RAID 10 for Log Files
> 4*72 RAID 10 for Data Files
> 3*72 RAID 5 for backups,etc.
And treat tempdb as any other database with this spindle count. Data files
on data drive. Log files on log drive. When you don't don't have many
spindles, it is best to keep it simple.
There is even a case that could be made this config:
> 2*72 RAID 1 for OS
> 8*72 RAID 10 for Data Files and Log Files
> 3*72 RAID 5 for backups,etc.
"Hassan" <hassan@.test.com> wrote in message
news:umf6Sb8TIHA.5288@.TK2MSFTNGP04.phx.gbl...
> Are you saying as such ?
> 2*72 RAID 1 for OS
> 4*72 RAID 10 for Tempdb(data and log) and Log Files
> 4*72 RAID 10 for Data Files
> 3*72 RAID 5 for backups,etc.
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:185DB471-3652-44A0-AAD0-33E056397B92@.microsoft.com...
>>I would combine the tempdb and log drive and treat tempdb files like the
>>rest of the databases. It is better to have logs on 0+1. If the controller
>>is decent, one will be able to handle that spindle count.
>> --
>> Jason Massie
>> www: http://statisticsio.com
>> rss: http://feeds.feedburner.com/statisticsio
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OMbGoL8TIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> We want to go with SQL recommendations of seperate drives for
>> backups,data,log,tempdb and OS.
>> So we are thinking of
>> 2*72 RAID 1 for OS
>> 2*72 RAID 1 for Tempdb
>> 2*72 RAID 1 for Log
>> 4*72 RAID 10 for Data
>> 3*72 RAID 5 for backups,etc.
>> So the question is should I use one RAID controller to tie all these
>> drives too ? Just looking at it from a price perspective. If not, then 2
>> and if so, what areas should I distribute the above layout to ?
>> Let me know if 1 RAID controller would work.
>> Thanks
>|||Would you also do it with one controller ?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:467C193D-BFB7-45C1-BC99-DF7084FF714C@.microsoft.com...
>> is it best to put both the log files for the data and tempdb together ?
>> Or have tempdb on its own disks that include both its data files and log
>> files
> I would place the tempdb log file to the same drive/array as the other log
> files and on a controller with a lot of write cache. Tempdb data files
> may be heavily used and separating data files from log files will help
> optimize the sequential writes to the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hassan" <hassan@.test.com> wrote in message
> news:OqcmMl8TIHA.280@.TK2MSFTNGP03.phx.gbl...
>> is it best to put both the log files for the data and tempdb together ?
>> Or have tempdb on its own disks that include both its data files and log
>> files
>> Thanks
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:7C67FDC2-4E72-4DEE-80F7-F5EEDCC44DD2@.microsoft.com...
>> >I would combine the tempdb and log drive and treat tempdb files like
>> >the rest of the databases. It is better to have logs on 0+1. If the
>> >controller is decent, one will be able to handle that spindle count.
>> Logs shouldn't be shared with other files. Log writes during COMMIT are
>> synchronous and so response time will be negatively affected due to
>> competing with other file I/O.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "jason" <jason-r3move@.statisticsio.com> wrote in message
>> news:185DB471-3652-44A0-AAD0-33E056397B92@.microsoft.com...
>>I would combine the tempdb and log drive and treat tempdb files like the
>>rest of the databases. It is better to have logs on 0+1. If the
>>controller is decent, one will be able to handle that spindle count.
>> --
>> Jason Massie
>> www: http://statisticsio.com
>> rss: http://feeds.feedburner.com/statisticsio
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OMbGoL8TIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> We want to go with SQL recommendations of seperate drives for
>> backups,data,log,tempdb and OS.
>> So we are thinking of
>> 2*72 RAID 1 for OS
>> 2*72 RAID 1 for Tempdb
>> 2*72 RAID 1 for Log
>> 4*72 RAID 10 for Data
>> 3*72 RAID 5 for backups,etc.
>> So the question is should I use one RAID controller to tie all these
>> drives too ? Just looking at it from a price perspective. If not, then
>> 2 and if so, what areas should I distribute the above layout to ?
>> Let me know if 1 RAID controller would work.
>> Thanks
>>
>|||> Would you also do it with one controller ?
It depends on budget and controller capabilities. A single controller is
probably adequate as long as it's a decent one with features like lots of
cache, configurable cache read/write ratio and battery backup.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:usb%23Wc9TIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Would you also do it with one controller ?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:467C193D-BFB7-45C1-BC99-DF7084FF714C@.microsoft.com...
>> is it best to put both the log files for the data and tempdb together ?
>> Or have tempdb on its own disks that include both its data files and log
>> files
>> I would place the tempdb log file to the same drive/array as the other
>> log files and on a controller with a lot of write cache. Tempdb data
>> files may be heavily used and separating data files from log files will
>> help optimize the sequential writes to the log.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OqcmMl8TIHA.280@.TK2MSFTNGP03.phx.gbl...
>> is it best to put both the log files for the data and tempdb together ?
>> Or have tempdb on its own disks that include both its data files and log
>> files
>> Thanks
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:7C67FDC2-4E72-4DEE-80F7-F5EEDCC44DD2@.microsoft.com...
>> >I would combine the tempdb and log drive and treat tempdb files like
>> >the rest of the databases. It is better to have logs on 0+1. If the
>> >controller is decent, one will be able to handle that spindle count.
>> Logs shouldn't be shared with other files. Log writes during COMMIT
>> are synchronous and so response time will be negatively affected due to
>> competing with other file I/O.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "jason" <jason-r3move@.statisticsio.com> wrote in message
>> news:185DB471-3652-44A0-AAD0-33E056397B92@.microsoft.com...
>>I would combine the tempdb and log drive and treat tempdb files like
>>the rest of the databases. It is better to have logs on 0+1. If the
>>controller is decent, one will be able to handle that spindle count.
>> --
>> Jason Massie
>> www: http://statisticsio.com
>> rss: http://feeds.feedburner.com/statisticsio
>> "Hassan" <hassan@.test.com> wrote in message
>> news:OMbGoL8TIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> We want to go with SQL recommendations of seperate drives for
>> backups,data,log,tempdb and OS.
>> So we are thinking of
>> 2*72 RAID 1 for OS
>> 2*72 RAID 1 for Tempdb
>> 2*72 RAID 1 for Log
>> 4*72 RAID 10 for Data
>> 3*72 RAID 5 for backups,etc.
>> So the question is should I use one RAID controller to tie all these
>> drives too ? Just looking at it from a price perspective. If not,
>> then 2 and if so, what areas should I distribute the above layout to
>> ?
>> Let me know if 1 RAID controller would work.
>> Thanks
>>
>>
>