December 10, 2018 at 8:13 am
I am experiencing an issue with my SQL Server Standard version 11.0.6020.0. I first noticed the problem about a month and a half ago. What's happening is that when running my routine maintenance on the server the job runs forever. Here is an example of the command that will execute for days:
ALTER INDEX [IX_NonClustedIndex] ON [Database].[dbo].[Table] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF)
Now this statement usually completes in no time at all:
StartTime EndTime
2018-12-05 02:00:01.057 2018-12-05 02:00:01.673
But for some reason it out of the blue it began having issues. I have googled this problem to death and the only clue I found was an article somewhere saying the tempdb might be corrupted. So, I stopped the services. Deleted my tempdb's and then restarted the server. Tested the alter index statement and BAM! Done in seconds. Thinking I had the problem solved; I went about my business. The fix lasted approximately 4 weeks before it started all over again. Repeated what I did before thinking that I would get another few weeks out of it and nope. The fix only lasted for days this time.
Any idea what is causing my problem or what I need to investigate? Thanks in advance.
December 10, 2018 at 12:03 pm
How many cores on the server and how many files does tempdb have? I'm wondering if this could be related to allocation page contention.
December 10, 2018 at 12:11 pm
2 CPU's with 8 cores each. 192 GB RAM. tempDB has 20 data files each 5 GB in size. Both the data and log files for tempDB are on their own separate drive. Reading your link now and thanks for replying. I really need to get this figured out but I am at a loss as to what the underlying problem might be.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply