January 5, 2012 at 9:26 am
I had too many tempdb file so i had to delete one mdf file by right cliking the tempdb and restarted the service. sql server seems to runing fine when i chekc in services but i can not connect to SQL SErver now, what would be the problem?
January 5, 2012 at 9:33 am
Check windows & sql error logs & post the error messages here.
January 5, 2012 at 9:39 am
What error do you get connecting?
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
January 5, 2012 at 11:11 am
It is taking too long for connecting to the server from managment studiio andfrom the event log i see this message "Starting up database 'tempdb'." Looks like after deleting one of the tempdb file it is just hanging starting the tempdb database, how do i resolve this?
January 5, 2012 at 11:25 am
Tara-1044200 (1/5/2012)
It is taking too long for connecting to the server from managment studiio
Is it timing out or giving an error?
andfrom the event log i see this message "Starting up database 'tempdb
That's an informational message that will always appear in the log and it means exactly what it says, SQL is starting up tempDB, as it does with every database on startup. It's not an error.
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
January 5, 2012 at 11:27 am
yes it is timing out. also just to refresh databases by right clicking it is taking atleast 10min which should be only in seconds.
January 5, 2012 at 11:36 am
Ok, have a look in the error log for any errors.
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
January 5, 2012 at 11:36 am
Where is the current tempdb? Is there enough space for it? How big is it? Is it set to autogrow?
January 5, 2012 at 11:42 am
here is how i laied out.
tempdev1D:\DATA\tempdb.mdfPRIMARY1073741824 KB1073741824 KB10%data only
templog2D:\LOG\templog.ldfNULL512 KBUnlimited10%log only
tempdb23F:\DATA\tempdb2.ndfPRIMARY3072 KBUnlimited1024 KBdata only
tempdb34E:\DATA\tempdb3.ndfPRIMARY3072 KBUnlimited1024 KBdata only
and the D drive also has 450gb od space for OS paging
January 5, 2012 at 12:13 pm
Are all drives accessable? Is compression turned on for any of the locations? What do the error logs say?
Also
January 5, 2012 at 12:31 pm
all drives accessible and compresseion not enbaled on the disks.
January 5, 2012 at 12:59 pm
Tara-1044200 (1/5/2012)
here is how i laied out.tempdev1D:\DATA\tempdb.mdfPRIMARY1073741824 KB1073741824 KB10%data only
templog2D:\LOG\templog.ldfNULL512 KBUnlimited10%log only
tempdb23F:\DATA\tempdb2.ndfPRIMARY3072 KBUnlimited1024 KBdata only
tempdb34E:\DATA\tempdb3.ndfPRIMARY3072 KBUnlimited1024 KBdata only
and the D drive also has 450gb od space for OS paging
Which one did you delete?
January 5, 2012 at 1:08 pm
tempdb2.
January 5, 2012 at 3:46 pm
Tara-1044200 (1/5/2012)
here is how i laied out.tempdev1D:\DATA\tempdb.mdfPRIMARY1073741824 KB1073741824 KB10%data only
templog2D:\LOG\templog.ldfNULL512 KBUnlimited10%log only
tempdb23F:\DATA\tempdb2.ndfPRIMARY3072 KBUnlimited1024 KBdata only
tempdb34E:\DATA\tempdb3.ndfPRIMARY3072 KBUnlimited1024 KBdata only
and the D drive also has 450gb od space for OS paging
If tempdev has an initial size of 1024 GB (1073741824 KB) this may take a while to initialize, particulalry if you haven't granted Volume Maintenance to the SQL Server Service account (Run: Secpol.msc go to Local Policies/User Rights Assignment).
I rcommend setting all the tempdb mdf & ndf files to the same initial size and assigning the policy as above.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply