Oh Yes.. the auto close on option was enabled and hence those doubts.. I have made it false now and my doubt is cleared.. somebody created with those options ON.

Thanks...

I am logging as sa

Also I found if I am running "use database", that particular database, the value of is_cleanly_shutdown becomes 1

And those databases are not showing up when I...

Those DBs aren't offline.

Oh! I couldn't believe as I thought that the condition "@@rowcount > 0" will take care of it.. however I will try using a condition..

Thank you all for your contribution...

thanks for your interest...

Please check the below code..

set rowcount 40000

Update T1 SET sName = t2.sName

from dbo.t_table1 T1 INNER JOIN dbo.t_table2 T2 ON t1.tID = t2.tID

while @@rowcount>0

begin...

May be.. but I didn't use begin transaction to use commit.. should I include begin tran... end tran with commit..?

Else that query will continue running indefinitely? :w00t:

Hope I can continue on this thread.. since its regarding the same issue..

SQL Server in one of our VM machine is giving timeout expired while logging in through SSMS on...

Actually I found an old thread regarding the same issue.. so I thought of opening a new one 🙂

Most of our queries use lot and lots of temp tables, however the tempdb size will not exceed 2GB. When I try to rebuild index, it takes more than 2...

Sure, I will try to do that. However, how will you conclude on the requirement if reads are more. The performance counter shows 99.8%-100% for SQL Buffer Manager in all...

Thanks for your suggestion, anyways I am responsible to recommend so and so hardware requirements. But our server consultant is not providing sufficient resources that I demand.

So, could you...

you can just use DBCC shrinkfile or DBCC shrinkdatabase command without truncate_only.

visit this site for details.

http://support.microsoft.com/kb/307487

please avoid restarting the SQL Server. Use the below query to shrink tempdb after stopping the queries responsible for its growth.

use tempdb

go

DBCC SHRINKFILE(templog, 80)

GO

BACKUP LOG tempdb WITH TRUNCATE_ONLY

GO

DBCC SHRINKFILE(templog, 80)

GO

80...

I have the same thought as you do. But if the tempdb is too full, find the queries or SPs responsible for the tempdb growth and stop them before shrinking...

Hi Stefan,

I am using the below command just to monitor currently running scheduled job since the output will reflect very late in another table.

EXEC msdb.dbo.sp_help_job @execution_status = 1