March 28, 2006 at 4:39 am
I wonder if any of you got this issue. I am creating Replication in a test environment (Thank god!!) and one of the databases has a little over 26 hundred objects to replicate, tables, views, stored procs, etc. I changed the amount of objects due to space for tempdb. See below
Well, I found that the tempdb got to a point that used all the available space. I did shrink it, but don't want to run in the same issue when I start with live replication. So, I searched about moving tempdb. I tested that in a laptop.
The query recommended by Microsoft and many others returned a success on the alter files.
Here comes the issue, you need to restart SQL Server after you have done that. I did that, but SQL Server doesn't start. It tries, but it goes back to Stop state. I used sqlsrvr.exe to try, but no luck either. I used the service directly from Windows services and it didn't work either.
Any suggestions? What I have in the laptop is just work stuff, but nothing I can not save some other place, but if this doesn't work in the laptop, how can I ensure the move is going to work in the test environment or the live systems??
Your assistance will be very much appreciated. Thank you
March 28, 2006 at 5:13 am
Fernando
Did you save the query that you used to move the tempdb files? If so, check that you specified a location that actually exists and on which the account that starts SQL Server has the correct permissions. If that isn't the problem, please post your SQL errorlog - there should be some clues in there.
John
March 28, 2006 at 7:47 am
John has the right idea. I'd guess security or an invalid path. The SQL or Windows error log should show this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 28, 2006 at 12:23 pm
Steve,
I used a query you posted back in 2004 and it is exactly as the one I found in Microsoft/Support.
Maybe is a permissions issue, but I am the administrator of my laptop. I will verify as soon as I get home tonight.
There is no error messages shown, I didn't have the time this morning to check event viwer though. Will do that also.
SQL Server showed as started for just a couple of seconds and it goes back to Stop state.
Query reads:
use master
go
Alter database TempDB modify file (name = tempdev, filename = 'C:\TestTempDB\TempDB.mdf')
go
Alter database TempDB modify file (name = templogv, filename = 'C:\TestTempDB\Templog.ldf')
go
The query message returned a success. That was the time to restart SQL Server. I did it through Service Manager, Enterprise Manager, Services, and command line using sqlservr.exe. All of them did exactly the same. It turn the service on for just one or two seconds and then went back to Stop state. Thank you for your help!!
I know for a fact that I will need to move TempDB when the time comes, but need to figure out why and how to fix it, if this happens with any of the work machines. Thank again.
By the way, Steve, the place I found was databasejournal.
March 28, 2006 at 1:36 pm
Steve and John are correct. It may be just a typo in the path that you provided for the query. If there is no path name in the error log or Windows application log error than you may want to restore Master on the working SQL server UNDER DIFFERENT NAME. See what is in sysdatabases in Master_Copy. Maybe you will find a discrepancy. Also if you have a tempdb.mdb file you may attach it to another server under another name and see what path is in the sysfiles. "Restore FileListOnly" also can give you the file list but I assume you don't have a new temdb backup ( I am not sure anyone does).
Regards,Yelena Varsha
March 28, 2006 at 2:57 pm
I appreciate all your assitance. The main problem at this time is that SQL Server does not start. So, I cannot see the master or anything to check the path. I will check the query I used, if there is an incosistency on the names, maybe I can rename the test folder and try to start SQL Server again.
Thank you again. Is not critical though. Is my personal laptop, if I need to unistall and reinstall, I can is juts a matter of figuring out what was the problem that do not allow me to restart SQL Server. Thank you
March 28, 2006 at 5:23 pm
Thank you guys! With your input I figured out the problem. I did create te folder as TestTempDB. When I created the query, I wrote TestTempDB.MDF and LDF!!! My father used to say that all cars must have a red blinking light before you start telling you "IS THE DRIVER IS THE DRIVER" LOL Well, it was me!!! The driver made the mistake. Lucky me I always test everything in my laptop before I go to any of the company servers!!!
I am gpoing to uninstall and reinstall. I don't have anything I could not drop. All my scripts, etc are in a complete different location than SQL Server. Thank you for your input. I discovered, again!! I am just human!!!
March 28, 2006 at 9:05 pm
You are uninstalling SQL Server because it is not coming up?
Don't give up... Start the SQLServer from the command prompt with Trace Flag 3608 and alter temdb again.
If you nee any assistance ping me at bmlakhani@yahoo.com
March 28, 2006 at 9:52 pm
I got it runing fine again. Thank you I use the utility to restore Windows back in time. I did restore from Sunday night and everything is working fine now. I will keep your info anyway in case anything happens again.
I did test the query to move temptdb back and for and everything worked fine. It was the way I created the query to modify the file. Thank you anyway.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply