SQLMAINT.EXE Timeouts

Question

SQLMAINT.EXE Timeouts

shew

SSChampion

Points: 11389
More actions
June 14, 2004 at 4:57 am

#85294

We run SQLMAINT.EXE from an NT DOS script, and one of our servers started getting timeouts. Here is the syntax and the error:

sqlmaint -S RKE055 -D ACPTNEWSURETY -Rpt \\RKE055\DBADATA\SQLWeeklyMaintenance_ACPTNEWSURETY.rpt -CkDB -CkAl -CkTxtAl -CkCat -UpdOptiStats 100

[Microsoft SQL-DMO (ODBC SQLState: HYT00)] Error 0: [Microsoft][ODBC SQL Server Driver]Timeout expired

Any ideas?

Thanks,

Jon

Viewing 7 posts - 1 through 6 (of 6 total)

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

shew SSChampion Points: 11389 More actions · Answer 1

By the way, database size does not seem to make a difference. We are getting the same error on the pubs and Northwind databases too.

Jon

beechpilot Right there with Babe Points: 779 More actions · Answer 2

Jon,

It looks as though you're trying to do all of the maintenance in one shot (at least from just looking at your command line). We had the same problem when trying to do it all at once.

We do nightly and weekly maintenance, however we stagger it through the wee-hours. We also use Maintenance Plans, but should be adaptable to a command line run of SQLMAINT. Here's the schedule I use:

1:05 AM: Reorg Data/Index Pages and space reclaimation

2:05 AM: DB and Index integrity with repair

3:05 AM: DB Backup (each database backup start staggered 5 minutes apart)

4:05 AM: Start Tranlog backups, every hour from 4:05 AM through 2:59 AM the following day.

This would probably turn into several SQLMAINT commands, scheduled to perform the appropriate task.

Hope this helps for you. Ever since we staggered things this way, we've not had the problem since.

Cheers,

-- Joe

shew SSChampion Points: 11389 More actions · Answer 3

Joe,

The odd thing is that this code has not changed in over a year, and it used to work fine. The overall sizes of the databases are not that much different from what they were a year go, and I'm getting the errors on even tiny databases such as pubs and Northwind. Only one SQLMAINT.EXE command is active at a time. The same code continues to work fine on our other 35+ servers, some of which have much more data.

I'm stumped.

sa24 SSCrazy Eights Points: 8027 More actions · Answer 4

Are you running all your scripts from one server ?

shew SSChampion Points: 11389 More actions · Answer 5

No, each server has a script of its own. Essentially, each script consists of a series of SQLMAINT.EXE commands. For each server, each job runs in a serial fashion in order to avoid swamping the server with SQLMAINT activity.

sa24 SSCrazy Eights Points: 8027 More actions · Answer 6

Just some thoughts.

Is the share \\RKE055\DBADATa\ valid ? Can you also verify your backup path ? Also, user permissions