I was able to find the issue and moved a sp into another window and the time to run when from 3 hours to 4 minutes.

Amazing

Thanks

That worked...I guess I keep an eye on it I had it do rebuild if > 30% fragmentation

Thanks, I was able to run the two above during the busy times, and I could see all the suspending threads, and then what it was waiting for as I...

It completed.  I will again tomorrow

There is no blocking just sp lock has over 2000 entries and so who2 show suspended

Thank you

Thanks Jeff, we doing a review of all details.

Memory, the server has 109 GIG

• This reply was modified 5 years, 4 months ago by  TRACEY-320982.

We had this, we set the database properties, options, legacy cardinality estimation to ON it was OFF before, it went from hours to secs.

This is good information you are all providing.

When you get to high TB of data, then it does take a little more planning and structure.

Our logs show I/O saturation and...

Single filegroup.   Thanks Steve, that is far easier to do a rebuild index then dbcc shrinkfile, emptyfile and resizing.

I think I have a plan, does this seem like the correct method to use.

Start

datafile1 500

datafile2 500

Add datafile 3 of 250 GIG and set filegrowth to zero.

Add datafile 4 of...

• This reply was modified 5 years, 4 months ago by  Steve Jones - SSC Editor. Reason: format code

Thanks for the details.

What do you mean with redistribution of all data ? Is that a command I have to run ? or does SQL redistribute the data when you...

Model 103 MB

For another test I changed the size to 204 MB
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 204 MB)
This works as increased it.

Them...

I got it to work, I had to do all these extra commands

Question
I ran this
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 1024000 KB)-->1000 MB...

I am trying to reduce the size of the tempdb database

1.  Stop Server Server

Net Stop MSSQLSERVER   (Name of Services)

2. Net Start MSSQLSERVER /c /m...