Moving TEMPDB

Question

Moving TEMPDB

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

see this on how to move system databases.

the data file should be tempdb.mdf, the log file should be templog.ldf, don't change the default file names on system databases, will come back to bite you one day.

But - as Johan (ALZDBA) said you also have access problems on the new location (the OS error 5 you are seeing.) You will have to grant the SQL server service account full access to your new location.

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 2

nice tip on the xcopy by the way Johan.

---------------------------------------------------------------------

Jpotucek SSCoach Points: 18583 More actions · Answer 3

Thanks for all your help. I have moved system files around and never had this kind of problem.. I will uninstall my sharepoint instance and start again. I'll let you know how I make out.

Sachin Diwakar SSC Eights! Points: 860 More actions · Answer 4

When trying connecting to SQL server using Management Studio.

When it pops up to enter SQL server instance name, hit the CANCEL

Now, make sure you are not connected to SQL server and you dont see any server listed in Object explorer.

Once you are all set to obtain the only connection : click on the "NEW Query" button at the top left corner (may be some where else on Management Studio's window).

Here now, your SQL server is in Single user mode and the only connection is taken by you.

Try it.

Regards

-Sachin

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Jpotucek (1/14/2011)
I will uninstall my sharepoint instance and start again.

There is absolutely no need for that.

Make sure SQL has full control over the folder for TEmpDB

Connect to SQL (while it's running in that restricted mode), reissue the alter database statements correctly (with a file name, not just a folder)

Restart SQL normally.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 6

second what gail says, this issue is fixable in a few minutes, no need to cause yourself all that extra work

---------------------------------------------------------------------

sachnam SSCrazy Points: 2317 More actions · Answer 7

Jpotucek (1/14/2011)
Not really sure what to do at this point..
C:\Users\isjxr18>sqlcmd -s kocsql05\sharepoint
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

pls. check if your browser service is running fine.

Also try using DAC

sqlcmd -S kocsql05\sharepoint -A

Make sure that none of the other sql server related service is running which might be accessing your sql server to allow you that one connection ...

sachnam SSCrazy Points: 2317 More actions · Answer 8

sorry i guess i am late to reply the above solution...