Hi Neil,

It worked. after clearing the cache, was able to shrink the files.

Thanks a lot for the help. Was reading similar blog post.

https://sqlsunday.com/2013/08/11/shrinking-tempdb-without-restarting-sql-server/

Thank you all for the suggestions.

Thanks everyone for the suggestions. We have decided to purge some unwanted data.

Many thanks Grant and Jeff.

As Jeff pointed out the options , will try to change the values and see.  Thanks Again.

Hi Everyone,

Thank you everyone. It turned out to network firewall issue. network trace helped.

Regards,

Sam

Thank you all for the help. I know that we need to give sysadmin server role. But was looking if there is any specific permissions or possible workarounds to accomplish...

Sam - did you read my comments?

Adding those indexes are not necessarily what needs to be done (as recommendations from the engine are just that)

and is the field I...

Thank you Lynn and Jonathan.

How can we set the right expectations to the Development team on such situations, what can be done by the DBA and how to convey the application team in a...

Change the query to this:

SELECT a.notf_chg_evnt AS notf_chg_evnt, 
       a.notf_btch_dtl_id AS notf_btch_dtl_id, 
       a.chg_detct_time...

Sam,

Rewriting to use ANSI Joins instead of older Equi-Joins isn't going to fix it nor the apparent total absence of any indexes to support the code.  Returning more than...

Thanks everyone for sharing your valuable input.

Finally, we move the data into a separate table. We retained some which is required in the newly created table and drop the existing...

I am using shringfile with truncateonly option.

Yes. I tried the shrinkfile but it doesnt release free space which is around 500MB. I dont know why!!

Sam

Space from deleted data is not automatically released to the operating system.  You have to shrink the data files to get the space back.

Please post the code that your...