April 18, 2007 at 1:15 am
hi,
can any one tell me how to solve the following problem without deattacha nd attach solution.
Tempdb got suspected and found the tempdb data file got corrupted and my users are not able to login user are nearly more than 800 so if dettach and attach the devices again no easy for to update the user linking...
Please help the easy to overcome this problem.
Thanks
Pavan
April 18, 2007 at 2:14 am
you could restart the server, as this rebuilds tempdb from model.
Unless anyone knows why you can't?
April 18, 2007 at 3:49 am
Hi Pavan,
If you're still having issues, following what wangkhar has suggested, you could try moving tempdb with the following to see if this helps
use tempdb
go
EXEC dbo.sp_helpfile
go
use master
Alter database tempdb modify file (name = tempdev, filename = 'P:\MSSQL.1\MSSQL\Data\tempdb_data.mdf')
Alter database tempdb modify file (name = templog, filename = 'P:\MSSQL.1\MSSQL\Data\tempdb_tlog.ldf')
go
Changing the values/filename(s)/drive volumes, etc., as required following running sp_helfile
HTH
April 18, 2007 at 2:13 pm
did you try a reboot?
April 18, 2007 at 9:32 pm
Thanks for your reply the problem got solved as per the query given, i cannot able to reboot my server coz there is another instance used for the web application.
Cheers
pavan
April 19, 2007 at 7:13 am
You should only need to restart the instance of SQL server that needs a fresh tempdb. Not restart the server itself.
April 20, 2007 at 6:42 am
Did you try restarting the SQL instance before running the script Pavan?
For the script to take effect you have to restart the instance anyway, but hopefully you tried a restart first before running the script to see if tempdb was rebuilt. If you did and the instance still had problems and the script worked, guess this was a disk array/drive issue.
Glad you got things working again, though.
rgds
iwg
April 20, 2007 at 9:32 pm
If the tempdb is suspect, I don't think without restarting sql services of the instace will create another tempdb with scripts...
MohammedU
Microsoft SQL Server MVP
April 23, 2007 at 12:32 am
Yes i have restarted the SQL Services and then only i can able to run the script.
Pavan
April 24, 2007 at 6:13 am
Ok,thanks Pavan. Glad its sorted.
Mohammed, I was asking if Pavan had tried a restart before running the script to see if SQL Server was able to rebuild tempdb in the place originally set. If tempdb was still showing problems, then I was suggesting that he may try and move tempdb using the script - which would of course require a restart of SQL Service to initialize in its new directory - and which is what Pavan did. I hope this explains what I was asking Pavan.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply