SKYBVI (2/22/2011)

---

Hi,

i have a prod database of around 340 gb .

it has a tempdb file size of 10 gb.

I ran this command:- ( in a job at night)

USE...

I dont understand the question. But looks like you have also a sql 2005 instance running if you have a 90 agent

There are no jobs\maintance plans the OP stated, but i agree cant believe this. unless they work with Sqlcmd schedule...

open it in youre SQL profiler

imho check first the default trace (which is always running) it is located...

isnt the error then login for user '' failed...

it will enable xp_cmdshell

you will notice that if you run sp_configure again.

probably you re query will run then correct as now xp_cmdshell features are disabled

there is youre answer

its not enabled

sp_configure 'xp_cmdshell' , '1'

reconfigure

go

will enable it

temptables long running transactions ect??

check out:

http://www.sqlservercentral.com/blogs/steve_jones/archive/2009/11/30/what-s-using-space-in-tempdb.aspx

Upgrade Advisor is available on the SQL Server 2005 product media and this Microsoft Web site: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1470e86b-7e05-4322-a677-95ab44f12d75&displaylang=en

quote from the SQL server Setup Help file 😀

TECHBABU (2/22/2011)

---

Did you try running profiler during this time if you see this happen at particular time or manually run sp_who2 when this is occuring ?

Also suggest querying the MSDB...

sp_configure 'show advanced options' , '1'

reconfigure

go

sp_configure

cant you logship or mirror the databases?

Always make sure both nodes have the same HW specs

xp_cmdshell is enabled?