How to move tempdb datafiles and logfile | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to move tempdb datafiles and logfile

Hi, How to move tempdb datafiles and logfile to c: to D: coz c we dont have much space Regards
Srini
Hi srini,
There is no need to move tempdb db as generally it hardly takes much space.
When you start the sql server it gets created again with 2mb size.
So i dont see any space gain in moving it.
If your application uses tempdb alot then check this links: http://www.sql-server-performance.com/tempdb.asp
http://www.databasejournal.com/features/mssql/article.php/3379901
http://www.tkdinesh.com/faq/ans/tempdbmove.html
To move tempdb have a look at BOL for ALTER DATABASE. The last example (example I<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> at the bottom there shows how to accomplish this.<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
As referred above it is not compulsory to move the tempdb and if you’re using different collation to Tempdb then ensure to set the same on destination server. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ks9072</i><br /><br />Hi,<br /><br />How to move tempdb datafiles and logfile to c: to D: coz c we dont have much space<br /><br />Regards<br />Srini<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hi Srini,<br />Considering that theirs no space on your c: drive and you would have to move your database on another drive, but as Ranjit says that theirs no need to move tempdb because it will resized(2 mb) when your sql server restarts… but if the issue is if your applicaton uses it a lot and you dont want to mive it to another drive then you could do it another way..if your drive is formated with NTFS then compress it or if not formated with NTFS you could convert it using convert c: /fs:ntfs<br /><br /><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />][:I]<br />Regards.<br /><br />hsGoswami<br />[email protected]
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ghemant</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ks9072</i><br /><br />Hi,<br />How to move tempdb datafiles and logfile to c: to D: coz c we dont have much space<br />Regards<br />Srini<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hi Srini,<br />Considering that theirs no space on your c: drive and you would have to move your database on another drive, but as Ranjit says that theirs no need to move tempdb because it will resized(2 mb) when your sql server restarts… but if the issue is if your applicaton uses it a lot and you dont want to mive it to another drive then you could do it another way..if your drive is formated with NTFS then compress it or if not formated with NTFS you could convert it using convert c: /fs:ntfs<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />][:I]<br />Regards.<br />hsGoswami<br />[email protected]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />NTFS is good option for compressing the disk.<br />But then you need to call your h/w support guys and need to format the disk.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ranjitjain</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ghemant</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ks9072</i><br /><br />Hi,<br />How to move tempdb datafiles and logfile to c: to D: coz c we dont have much space<br />Regards<br />Srini<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hi Srini,<br />Considering that theirs no space on your c: drive and you would have to move your database on another drive, but as Ranjit says that theirs no need to move tempdb because it will resized(2 mb) when your sql server restarts… but if the issue is if your applicaton uses it a lot and you dont want to mive it to another drive then you could do it another way..if your drive is formated with NTFS then compress it or if not formated with NTFS you could convert it using <b>convert c: /fs:ntfs</b><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />][:I]<br />Regards.<br />hsGoswami<br />[email protected]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />NTFS is good option for compressing the disk.<br />But then you need to call your h/w support guys and need to format the disk.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />No no , no need to call a hardware support guy its just a simple command to be given as i have mentioned , just issue the following command :<br /><b>convert c: /fs:ntfs</b><br /><br />and after rebooting a system it is done . so simple [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />][:I]<br /><br /><br />hsGoswami<br />[email protected]
Hi hemant,
i din’t knew about converting the existing partion to NTFS.
This is really easier.
You can covert from FAT to NTFS but not backwards…. thought NTFS offers good security as compared to FAT. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>