Moving the tempdb database


	Moving the tempdb database

Osolage

5-18-2007 12:54 PM

828 views

tags:

sql server, dba, configuration

Osolage says:

While trying to optimize the performance of some data loads on one of our QA database servers, I decided to move the tempdb to a different disk array hoping that would improve the time it takes to assemble the large set of records to be inserted into a denormalized table. This clip contains the information I used to move the database.

Add a Comment

Login to Comment. Not a member yet? Sign up

Today's Top Clips

visit the Top Clips page

View the Top Clips from May 18, 2007

Embed This Clip In Your Site...

<div style="margin: 12px 0px; font-family: arial; color: #333333; background: #ffffff; border: solid 4px #e5e5e5; width: 100%; clear: left;"><div class="CM_CTB_Content_Wrap" style="margin: 0px; padding: 0px;background-color: #ffffff;"><div style="border-bottom: solid 1px #dcdcdc; white-space: nowrap; margin-bottom: 8px; background-color: #eeeeee ;background-image: url(http://clipmarks.com/images/source-bg.gif); background-repeat: repeat-x; height: 24px; line-height: 24px; vertical-align: middle; padding-bottom: 4px; color: #666666; font-size: 10px;" ><a href="http://clipmarks.com/clip-to-blog/" title="see clips that are hot right now"><img src="http://content.clipmarks.com/blog_embed/970da2ec-aaf1-42d2-a4ea-1d03fb356496/DAA19191-B6D1-4DD3-AA6B-570412F130E7/" alt="" width="19" height="19" border="0" style="vertical-align: middle; margin: 0px 4px; display: inline; border: none; float:none;" /></a>clipped from <a title="http://support.microsoft.com/kb/224071" href="http://support.microsoft.com/kb/224071" style="font-size: 11px;">support.microsoft.com</a></div><blockquote style="text-align: left; padding: 0px 8px; margin: 4px 0px 8px 0px; background: transparent; border: none;" cite="http://support.microsoft.com/kb/224071"> You can move <B>tempdb</B> files by using the ALTER DATABASE statement.</blockquote><div style="height: 2px; font-size: 2px; background: #dcdcdc; border-bottom: solid 1px #f5f5f5; margin: 2px 4px;"></div><blockquote style="text-align: left; padding: 0px 8px; margin: 4px 0px 8px 0px; background: transparent; border: none;" cite="http://support.microsoft.com/kb/224071"><table background="undefined" bgcolor=""><tr><TD class="text">Determine the logical file names for the <B>tempdb</B> database by using <B>sp_helpfile</B> as follows:<CODE></CODE><PRE class="code">use tempdb
go
sp_helpfile
go</PRE> The logical name for each file is contained in the <B>name</B> column. This example uses the default file names of <B>tempdev</B> and <B>templog</B>.</TD></tr></table></blockquote><div style="height: 2px; font-size: 2px; background: #dcdcdc; border-bottom: solid 1px #f5f5f5; margin: 2px 4px;"></div><blockquote style="text-align: left; padding: 0px 8px; margin: 4px 0px 8px 0px; background: transparent; border: none;" cite="http://support.microsoft.com/kb/224071"><table background="undefined" bgcolor=""><tr><TD class="text">Use the ALTER DATABASE statement, specifying the logical
				file name as follows:<CODE></CODE><PRE class="code">use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go</PRE> You should receive the following messages that confirm the change:<DIV class="indent">
Message 1</DIV><DIV class="message">File 'tempdev' modified in sysaltfiles.
				Delete old file after restarting SQL Server.</DIV><DIV class="indent">
Message 2</DIV><DIV class="message">File 'templog' modified in sysaltfiles.
				Delete old file after restarting SQL Server.</DIV></TD></tr></table></blockquote><div style="height: 2px; font-size: 2px; background: #dcdcdc; border-bottom: solid 1px #f5f5f5; margin: 2px 4px;"></div><blockquote style="text-align: left; padding: 0px 8px; margin: 4px 0px 8px 0px; background: transparent; border: none;" cite="http://support.microsoft.com/kb/224071"><table background="undefined" bgcolor=""><tr><TD class="text">Using <B>sp_helpfile</B> in <B>tempdb</B> will not confirm these changes until you restart SQL
				Server.</TD></tr></table></blockquote><div style="height: 2px; font-size: 2px; background: #dcdcdc; border-bottom: solid 1px #f5f5f5; margin: 2px 4px;"></div><blockquote style="text-align: left; padding: 0px 8px; margin: 4px 0px 8px 0px; background: transparent; border: none;" cite="http://support.microsoft.com/kb/224071"><table background="undefined" bgcolor=""><tr><TD class="text">Stop and then restart SQL Server.</TD></tr></table></blockquote></div><div style="margin: 0px 6px 6px 4px;"><table style="font-size: 11px;border-spacing: 0px;padding: 0px;" cellpadding="0" cellspacing="0" width="100%"><tr><td style="background:transparent;border-width:0px;padding:0px;">&nbsp;</td><td align="right" style="background:transparent;border-width:0px;padding:0px;width:107px" width="107"><a href="http://clipmarks.com/share/DAA19191-B6D1-4DD3-AA6B-570412F130E7/blog/" title="blog or email this clip"><img src="http://content7.clipmarks.com/images/c2b-foot.png" border="0" alt="blog it" width="107" height="17" style="border-width:0px;padding:0px;margin:0px;" /></a></td></tr></table></div></div>

New from the makers of Clipmarks: Amplify.com - Don't just share the news...Amplify it!

Clipmarks: Home; New Clips; Top Clips; Dashboard

Popular Topics: News; Life; Science; Technology; Entertainment

Get Started: Sign Up; Install Clipping Tool; How Clipping Works; Clip-to-Blog™; ClipSearch

Tools and Resources: FAQ; ClipWeek; Top Clippers; Top Tags; Site Map

About Clipmarks: About Us; Contact; Copyright; Privacy; EULA

Search Options

Today's Top Clips