Forum Discussion

Rob_Swift's avatar
Rob_Swift
Level 3
18 years ago

Backup Exec Bug with SQL Server causes backups to hang?

While trying to backup database "A" in a SQL Server 2005 environment (or SQL Server 2000), the Backup Exec process gets blocked by another SQL process and hangs until the other process completes.  The problem is that for some unknown reason Backup Exec is running the following SQL in a different database (database "B"):

select dpages,name
from B..sysindexes
where indid<>0 and indid<>1 and indid<>255

Both database A and B are user databases (not master, msdb). As far as I can tell, Backup Exec runs this in EVERY SQL Server database before every backup is executed. In other words, if I have databases A, B, C, ...Z then Backup Exec would run the above query in each of the 26 databases before every backup is run.  In our environment we have users that will be creating a new table (which indeed puts a lock on sysindexes as it tries to add a row with indid = 0) and this create is a long running procedure. This causes Backup Exec to be blocked in SQL Server because it cannot get the shared lock needed for the query above.  Several times this has caused us to lose an entire night of backups.
 
To me this is a bug in Backup Exec since there is no need for Backup Exec to query other user databases (i.e. database "B") when I asked it to backup database "A".
 
I posted essentially this same comment/question in the 10d forum a couple of months ago, and got no response.  We have now upgraded to the latest 11d version and the problem still exists.  Is there a way to change this behavior?
 
Thanks
Rob

11.x and Earlier
Backup and Recovery
Backup Exec

6 Replies

Sort By
  • Patty_McGowan's avatar
    Patty_McGowan
    Level 6
    18 years ago
    Hi Rob,
     
    I will do some testing here to see what is occurring.
     
    I will post what I find out.
     
    Patty
  • Patty_McGowan's avatar
    Patty_McGowan
    Level 6
    18 years ago
    Hi Rob,
     
    This command is getting all of the information on the size of the databases.
    It is done on all databases even if they are not selected for backup.
    I am working on finding out why and will post more information when I have it.
     
    Thanks.
    Patty
  • Patty_McGowan's avatar
    Patty_McGowan
    Level 6
    18 years ago
    Hi Rob,
     
    I have gotten a response on this issue.
     
    The reason for the query on all databases is to be able to be flexible with selections, it allows Backup Exec to be prepared for all possible selections that may be in the job.  It keeps it universal.
     
    My question to you is when the users are creating tables why is the lock on the DB so time intensive?  A table creation should only take seconds, releasing the lock as soon as it is created.
     
    Is there something different in the way that this is done in your environment?
     
    Patty
     
     
     
  • Rob_Swift's avatar
    Rob_Swift
    Level 3
    18 years ago
    The users are either creating tables with the "Select x,y,z into.." type syntax or, in one case a user was creating a new index.  Since we are pulling data from a 6TB warehouse, these table creations can run a long time.  We are trying different techniques, but they then grenrate SQL logging which then makes the overall task take longer.
     
    I'm not sure I understand how getting table/database size of other databases is really giving Backup Exec any flexability in selections.  At this point in the process I have already made the selection, so Backup Exec should know exactly which database(s) I am trying to backup.  I don't think I had it in the original post since I thought it to be irrelavent, but I am just submitting an existing Backup Exec job via batch (using command line applet) so there is no interaction with the selection screens of Backup Exec.
     
    If BE needs the database size, there are better places to get that information so you don't need to sum all of the indexes.  Worse case, BE could use the (NOLOCK) hint for its read so it doesn't wait on processes in other databases
  • James_McKey's avatar
    James_McKey
    Level 5
    18 years ago
    While the NOLOCK implementation could result in an inaccurate result due to row updates in progress, for the purposes of db size reporting (used in restore selections & estimation for completion of a job) it probably would be an OK margin of error. I'll see about contacting you directly to discuss the issue in more detail for a possible future release consideration.
  • Rob_Swift's avatar
    Rob_Swift
    Level 3
    18 years ago
    One additional item I just noticed last week that I suspect is related to this same query activity.  Once Backup Exec has started performing a backup, the Backup Exec process still holds a share lock on every database on our SQL Server.  Normally this isn't an issue since the lock is at the database level, but I was trying to perform maintenance on database B while database A was being backed up.  I needed to detach database B but I could not because Backup Exec had this share lock on database B.  So the detach had to wait until the backup on database A was complete before the kill on this process would take effect.