SQL Server Performance Forum – Threads Archive

rebuild indexes :cannot login to SQL

Hi I was rebuilding indexes and as process took too loong I cancel the rebuild
in query analyzer. Now I can’t connect to SQL through Query analyzer or Enter.Manager. I can’t restart sql What should i do? Error: SQL Server Connection open(PreLoginHandShake()

I believe PreLogonHandshake error is normally caused by some type of time out. If you change the server name in the connection to use the IP address of the SQL Server do the problem go away. If so then, there could be some latency on the network that is slowing down network resolution of the name. You can resolve this by placing an entry in the HOST file on the client machine that
points to the SQL Server machine. BTW what is the level of Service pack on SQL & OS?
What was the result of Re-index?
Check thru SQL error logs & windows event viewer logs. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I restarted the server and connected okey
Thank you

sorry : I restarted the server that time and problem disappeared But today I was running reindexing again
and the server freese again. I need to reindex is that happening because I run reindexing from
Query Analyser?
Will it help to schedule job instead?

The idea is to run reindex in non working time.
In this case you can use a job to run reindex and schedule it.
In any case I don’t recommend you to cancel reindex.
If you are working with one table at time, run reindex between begin and end trasaction. If there is any cancelation, then rollback is automatically.
Some experts said that rebuild has rollback in built, but I prefer to write my own rollback.
Luis Martin
Moderator
SQL-Server-Performance.com

Actually what I ‘ve done I took a backup
of the database and restore it on development server. I run one index in a time . The first indext goes okey.(60 000 pages) Thes second one runs too long -20 min(200 000 pages)
and cause the PreLoginHandShake error
I agree that this is timeout
So I guess if I schedule job to run I expect it
not timing out. Hopefully….
Will see…now i am in a process of restoring again as
I interapted the process first time and db might be not the same anymore. DB=50 Gb

Can you confirm status on above points referred on my post. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Of course. Creating the job with the same statemets for reindexing
resolved all probles. The setting on this server for Query time out is 600 sec
Tables are big and reindexing taking more than 10 min because
of that I was getting that PreLoginShakeHand error.
What I am trying to say :it’s not canceling reindexing caused the problem. I never modify the Host file :just restarted the box by itself As
I couldn’t even use windows explorer :everything was frosen. Once I restarted the box ,the problem was resolved .But as soon as I was reindexing
again through the Query Analyzer it was happening again and again. Once again : I created the job with the same reindexing and
it resolved my problem. Thank you

What kind of h/w it is, anything special?
Refer to event viewer for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I just recheck the windows event log and sql log: nothing

Are you still getting PreLoginHandShake() errors? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

No.
As i stated above after running reindexing as a job PreLoginShakeHand disappeared.
So everything is resolved . PreLoginShakeHand was cause by running reindexing in Query Analyzer.
No issue anymore Thank you.

]]>