Database Backup Failed with Timeout Expired from Enterprise Manager

Hi,

I have SQL Server 2000 with SP4. When we take backup from Enterprise manager its fails with error "timeout expired". But when we take backup from query analyzer it works.

It seems that through enterprise manager 30 sec is the non configurable timeout parameter set. Checked sysprocesses table during backup but there was no blocking during that time at least for that spid.

Is there a way to find out which process is blocking the database backup? Also, what are the possible scenarios where database backup can be blocked.

Thanks,

Ramesh

Hi rksingh,

When you run a backup or restore from the Enterprise manager, the querytimeout does not apply.

It timesout because the process is being blocked by another process.

It could be blocked by BACKUP LOG or ALTER DATABASE commands.

Open another QA session and use SP_WHO2 ACTIVE to check for blocking.

regards

Jag

|||

Check SQL error log what error it is recorded...all backup info recorded in sql error log.

|||

Problem is resolved with the help of Microsoft PSS.

There is query time out setting for SQL Server 2000 Enterprise Manager which was set to 100 in my case whereas set to 0 on other servers.

To change settings

1. Open enterprise Manager.

2. Click on Tools - > Options

3. Goto Advanced Tab

3. Under connection setting change the Query time out value.

Thanks,

Ramesh

sql