Wait Type of MSQL_XP

ChrisAVWoodChrisAVWood Posts: 361
edited November 24, 2008 11:04AM in SQL Backup Previous Versions
Hi,

I raised a question about wait type MSQL_XP in the Summer and was told it was fixed in 5.4. We currently have a unique situation where a backup is taking a very long time because of slow disk transfer rates that might be caused by contention issues. Does a wait type of MSQL_XP cause a slowdown when taking a backup?

I am probably hoping that the MSQL_XP wait type could be causing a slowdown rather than just be a symptom of the disk problems. We are using the VERIFY in the backup job and it was this that is supposed to be fixed in 5.4.

Is 5.4 going to be released soon?

Thanks

Chris
English DBA living in CANADA

Comments

  • Eddie DEddie D Posts: 1,642 Rose Gold 5
    Thank you for your post into the forum and sorry for the delay in replying to you.

    I suspect that if you receive a wait type MSQL_XP this will cause a slow down of the backup.

    It is possible to obtain detailed backup information from SQL Server using a trace flag.

    If you open a new query window in SQL Server Management Studio or Query Analyzer, and connect to the server's MASTER database using an account with a SYSADMIN role, executing the following query will turn on extra logging for the backup and restore operations of the SQL Server.

    DBCC TRACEON (3004, 3605, -1)

    When it is no longer necessary to log the restore operations, the logging can be turned off again using

    DBCC TRACEOFF (3004, 3605, -1)

    Once you have executed the trace command, you may start to see the extra logging in the SQL Server activity logs immediately upon the next backup or restore operation. For instance, in Management->SQL Server Logs->Current in SQL Server Management Studio.

    SQL Backup 5.4 is still under development and testing. I do not have an accurate release date, I would not expect to see SQL Backup 5.4 until week beginning the 1st December 2008.

    Many Thanks
    Eddie
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • Thanks Eddie.

    We had a situation where new external disks were added to a server but the hardware communicating with these disks was transferring the data at a lower rate than the disk arrays could handle. This was causing a particular SQLBackup 5.3 job to take many hours to perform a backup of a 100Gb database that included a VERIFY. I could see the MSQL_XP waittype while the job was running. At the time of raising this issue we did not know of the hardware problem so that's why I wondered if thw wait type was part of the problem or just a symptom. The hardware has now been replaced and the slow backup has sped up. I have no need to run with the trace flag now.

    As I had heard that the waittype is fixed in 5.4 I expect that I might be an early adopter once it is released.

    Thanks

    Chris
    English DBA living in CANADA
  • Eddie DEddie D Posts: 1,642 Rose Gold 5
    Thank you for your reply.

    When I obtain further information regarding the release of SQL Backup 5.4 and a definite release date, I will update this forum post.

    Also a new sticky post will appear to announce the Version 5.4 release.

    Many Thanks
    Eddie
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • Thanks Eddie.
    English DBA living in CANADA
  • Eddie DEddie D Posts: 1,642 Rose Gold 5
    SQL Backup V5.4 is now available.

    To obtain SQL Backup, you have the following options:

    Use Check for Updates:

    1. Open the SQL Backup GUI.
    2. Help menu ->Select 'Check for Updates'.
    3. Follow the Check for Updates wizard and install SQL Backup 5.4.
    4. Steps 1 -3 will upgrade the SQL Backup GUI only.
    5. Right Click each registered server and then deploy the SQL Backup 5.4 Server Components to your SQL Server.

    Download from our ftp Site:

    Use this link to download from our ftp site:
    ftp://ftp.red-gate.com/SQLToolbelt.zip

    1. Extract out the SQL Toolbelt.exe file from the zip.
    2. Run the executable, which runs the SQL Toolbelt installer.
    3. Select the the option to Upgrade SQL Backup.
    4. Open the SQL Backup GUI, Right Click each registered server and then deploy the SQL Backup 5.4 Server Components to your SQL Server.

    Many Thanks
    Eddie
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • Thanks Eddie. I have downloaded it and will start testing soon.

    Chris
    English DBA living in CANADA
Sign In or Register to comment.