SQLMAINT Running Too Long! (SQL 2005)

Question

SQLMAINT Running Too Long! (SQL 2005)

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

ok - best bet is to call Product Support to help work out what's wrong - we're not going to be able to do it over the forums. Let us know what you find out.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

VincentP SSC Enthusiast Points: 109 More actions · Answer 2

I will do that today. I will let you know what they come up with.

Pei Zhu-415513 SSCarpal Tunnel Points: 4040 More actions · Answer 3

Pei Zhu-415513

SSCarpal Tunnel

Points: 4040

May 12, 2009 at 7:40 am

#992909

Pls keep us posted. Thx.

VincentP SSC Enthusiast Points: 109 More actions · Answer 4

Ok, so I opened a ticket with Microsoft on the issue. We used the web support system, so updates are not so fast. They asked me to run the typical PSSDiag while the SQL MAINT -chkDB is running, and to send the application/system event logs. I am in the process of doing that now. Just wanted to give everyone update incase you thought I forgot 🙂

abishekshroff SSCrazy Points: 2040 More actions · Answer 5

can you send the errorlogs. i can have a look and give you any suggestions .

VincentP SSC Enthusiast Points: 109 More actions · Answer 6

Sorry I haven't sent the error logs, I have been extremely busy these past few days. There really isn't anything that shows up in the error log that is related to the problem anyway. I posted the only errors that occured in an earlier post. Would you still like to look through them?

Well I have another update from Microsoft. They believe that some component of SQLMAINT must have been updated in SP3, but they were unsure as what that might be. They wanted me to seperate out the SQLMAINT -chkDB step from the rest of my maintenance plan (database backup, reindex, rebuild stats, etc ...). I tried that by creating a new job with only the SQLMAINT - chkDB command, but it hung up just the same. They now want me to create an SSIS maintenance plan to see if that resolves the issue. So, basically we are looking for workarounds at this point. I plan on trying the SSIS package this week. I will post another update with the results.

VincentP SSC Enthusiast Points: 109 More actions · Answer 7

Finally a solution from Microsoft! The problem appears to be related to parallelism and DBCC Check DB. See the below Knowledge Base Article for details.

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

There is a trace flag that you can turn on that disables parallelism for just the check DB operation. See the TSQL code below, and the documentation from Microsoft. The trick with this is that you have to turn the trace flag on every time the SQL service restarts. You can incorporate this code inside your job for running Check DB.

http://msdn.microsoft.com/en-us/library/ms188396.aspx

-- Turn on the trace flag globally

DBCC TRACEON(2528,-1)

-- Check the status of the trace flag

DBCC TRACESTATUS(2528)

-- Turn off the trace flag

DBCC TRACEOFF(2528,-1)

abishekshroff SSCrazy Points: 2040 More actions · Answer 8

Buddy

I suspected the same KB and thats why in past i asked you to check the errorlog to see if you have seen any similar to the one which i pasted. but you said no.

Anyways you got a solution.

VincentP SSC Enthusiast Points: 109 More actions · Answer 9

I actually didn't have any errors like that in the SQL error log. It wasn't until the Microsoft rep went through the PSSDIAG that he discovered the problem with parallelism.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 10

Ah - I'd forgotten all about that one. Luckily not a bug of mine 🙂

Glad you got a solution finally.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005