I checked the previous error log also. It does not say anything. There was a differential backup completed 5 minutes before SQL Server restarted.

Nope. It's not a cluster.

There was nothing else in the Error , Application or System log.

Thanks

Taiyeb

Nope. Server didn't crash. When SQL Server restarted by itself, I see the new log created. SQL Server log does not say that it stopped or restarted, only Application log...

I ran updstats with fullscan, now the query takes 1 minute to complete. It's still bad. I will try running dbcc dbreindex.

We take full backup every 2 weeks and differential daily. Last successfull diiff backup was day before yesterday and the transaction log size is 70GB of which only 600MB used...

We run DBCC UPDATEUSAGE every week. I updated the statistics using "update statistics tableX" command.

I have not done the reindex after deleting 50 million rows. This table is huge and...

I ran trace when I am excuting "Select top 10 * from tableX" and I captured all the Lock events.

In the profiler I am seeing "Lock:Acquired" and "Lock:Released" event continously....

There are no updates against the table X, but "select top 10 from table X with (nolock)" executed in 5 seconds. I don't understand why?

Hardware description: It's an HP DL760 with...

Nobody is doing any updates on TableX currently, therefore I don't understand why ""SELECT TOP 10 * FROM TableX WITH (NOLOCK)" executed so quickly.

I ran "SELECT TOP 10 * FROM TableX WITH (NOLOCK)" and it ran in 5 seconds.

Why it executed so fast? Any ideas.

Thanks for all suggestions.

There is no DISTINCT/WHERE/ORDER/GROUP. It's just a simple "Select top 10 * from table X"

Autoupdatestats is already enabled and I tried running "set rowcount 10 select * from table X"....

Hi Mccork

ClientProcessId is same as that of working application, but the SPID is different. What can cause 'Invalid Login error'?

Thanks

Taiyeb

Thanks all for the reply.

I captured all the information using SQL Profiler. Application name is blank,   host name is the name of the same server where the Application is running,...

It looks like there is no easy way of taking the backups on the Standby server. Thanks