none
Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation... must be serialized

    Question

  • Using SQL Server 2005 Server Management Studio, I attempted to back up a database, and received this error:

    Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the satement after the current backup or file manipulation is completed (Microsoft.SqlServer.Smo)

     

    Program location:

       at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
       at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)

     

    Backup Options were set to:

    Back up to the existing media set

         Overwrite all existing backup sets

     

    I am fairly new to SQL 2005.  Can someone help me get past this issue? What other information do I need to provide?

    Wednesday, July 11, 2007 3:40 AM

Answers

  • nandoliveiraq,

     

    You can run the following:

     

    Code Snippet

    select session_id, Text

    from sys.dm_exec_requests r

    cross apply sys.dm_exec_sql_text(sql_handle) t

     

     

     

     

    and see what commands are actively running by SPID.

    Saturday, July 12, 2008 11:53 PM
    Moderator

All replies

  • Hi,

    There might be different case;

    Case1.

    If a backup is started when either an add or remove file operation is in progress, the backup will wait for a timeout period, then fail. If a backup is running and one of these operations is attempted, the operation fails immediately.

    If a shrink operation tries to truncate a file while a backup is running, the shrink stops without truncating the file, however data pages have been relocated. If a backup is started just as a file is being truncated, backup normally waits long enough for the file truncation to complete.

    Case 2.

    You are tring to backup on different drive or network drive or on compressed drive. All backup command fails on above mentioned if you do not have access on these drives.

    Need to check.

    #  If it’s a compress drive right click and properties uncheck the file compression mark.share the folder with the login account by which you would to execute the backup.

    #. Try to open the network path from destination server including folder path like \\112.10.10.1\backupfolder\

    \\ --is to access network computer then IP address or machine name (If you find problem by resolving name that might be issue with DNS no issue use the IP address then.) then named ‘Backupfolder’is a folder where you need to copy.

    If you successfully open the backup path try to copy any file to check the access rights. Also check by which account you are tring to copy this login shoud have rights on the folder.

    Or try on different machine with the same method may be some security issue is stoping you.

    Also confirm the following accounts in folder like.

    Administrator /Creator Owner/SYSTEM/User.

    If  above are not the case plz  elaborate  you case may be I can help you.

     

    Good Luck.

     

    Praveen Barath

     

    mark as answer if it helps.

    Friday, June 13, 2008 12:13 PM
  • Hello, i have one database that takes care of the files of my company and i' having 3023 SQL error...

    I already try a lot of solutions and nothing... after i find your explanation and i have permissions to write in the place where i'm putting the backup, but i don't know if i have some
    shrink operation running cause i'm new with SQL Server 2005.
    Is possible to type some command to check if i have some shrinking running?

    Thanks for the attention
    Wednesday, July 09, 2008 7:19 AM
  • nandoliveiraq,

     

    You can run the following:

     

    Code Snippet

    select session_id, Text

    from sys.dm_exec_requests r

    cross apply sys.dm_exec_sql_text(sql_handle) t

     

     

     

     

    and see what commands are actively running by SPID.

    Saturday, July 12, 2008 11:53 PM
    Moderator
  • Hi,

    I have also faced the above error while taking a database backup, i ran the above command following is the output

    54 xp_sqlmaint
    55 xp_sqlmaint
    56 BACKUP DATABASE [RTD1_SITE] TO  DISK = N'd:\Program files\Microsoft SQL Server\mssql.1\MSSQL\Backup\RTD1_SITE\RTD1_SITE_db_200905200150.BAK' WITH  INIT ,  NOUNLOAD ,  NOSKIP ,  STATS = 10,  NOFORMAT
    58 CREATE PROCEDURE proc_FetchListItemSearchResults(                    @SearchTerm nvarchar(255),                    @SiteId uniqueidentifier,                    @WebId uniqueidentifier,                    @RankPaging int,                    @ModifiedPaging datetime,                    @ItemIdPaging uniqueidentifier)              AS                    SET NOCOUNT ON                    SELECT                          UserData.nvarchar1,                          UserData.nvarchar3,                          UserData.nvarchar7,                          Docs.DirName,                          Docs.LeafName,                          UserData.tp_ID,                          UserData.tp_ListId,                          Lists.tp_Title,                          UserData.tp_Author,                          UserData.tp_Editor,                          UserInfo.tp_Title,                          UserData.tp_Modified AS LastModified,                          NULL,                          UserData.tp_GUID AS ItemId,                          ISNULL(UserData#CT.Rank,0) + ISNULL(UserInfo#CT.Rank,0)                                AS CT#Rank,                          0                    FROM                          UserData                    LEFT OUTER JOIN                          FreeTextTable(UserData, *, @SearchTerm) AS UserData#CT                    ON                          UserData#CT.[Key] = UserData.tp_GUID                    LEFT OUTER JOIN                          Lists                    ON                          (Lists.tp_WebId = @WebId) AND                          (Lists.tp_ID = UserData.tp_ListId)                    LEFT OUTER JOIN                          UserInfo                    ON                          (UserInfo.tp_ID = UserData.tp_Editor) AND                          (UserInfo.tp_SiteID = UserData.tp_SiteId)                    LEFT OUTER JOIN                          Docs                    ON                          (Docs.SiteId = @SiteId) AND                          (Docs.ListId = UserData.tp_ListId) AND                          (Docs.DocLibRowId = UserData.tp_ID)                    LEFT OUTER JOIN                          FreeTextTable(UserInfo, *, @SearchTerm) AS UserInfo#CT                    ON                          UserInfo#CT.[Key] = UserInfo.tp_GUID                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((Lists.tp_Flags IS NULL) OR                           ((Lists.tp_Flags & 0x400) = 0) OR                           (UserData.tp_ModerationStatus = 0)) AND                          ((UserData#CT.Rank IS NOT NULL) OR                           (UserInfo#CT.Rank IS NOT NULL)) AND                          ((@RankPaging IS NULL) OR                           (ISNULL(UserData#CT.Rank, 0) +                             ISNULL(UserInfo#CT.Rank, 0) < @RankPaging) OR                           ((ISNULL(UserData#CT.Rank, 0) +                              ISNULL(UserInfo#CT.Rank, 0) = @RankPaging) AND                            ((UserData.tp_Modified < @ModifiedPaging) OR                             ((UserData.tp_Modified = @ModifiedPaging) AND                              (UserData.tp_GUID > @ItemIdPaging)))))                    UNION ALL                    SELECT                          Lists.tp_Title,                          NULL,                          NULL,                          Docs.DirName,                          Docs.LeafName,                          NULL,                          Lists.tp_ID,                          NULL,                          NULL,                          NULL,                          NULL,                          Lists.tp_Modified AS LastModified,                          NULL,                          Lists.tp_ID AS ItemId,                          Lists#CT.Rank AS CT#Rank,                          1                    FROM                          Lists                    INNER JOIN                          FreeTextTable(Lists, *, @SearchTerm) AS Lists#CT                    ON                          Lists#CT.[Key] = Lists.tp_ID                    LEFT OUTER JOIN                          Docs                    ON                          Lists.tp_RootFolder = Docs.[Id]                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((@RankPaging IS NULL) OR                           (Lists#CT.Rank < @RankPaging) OR                           ((Lists#CT.Rank = @RankPaging) AND                            ((Lists.tp_Modified < @ModifiedPaging) OR                             ((Lists.tp_Modified = @ModifiedPaging) AND                              (Lists.tp_ID > @ItemIdPaging)))))                    ORDER BY                          CT#Rank DESC,                          LastModified DESC,                          ItemId ASC                    RETURN 0
    59 CREATE PROCEDURE proc_FetchListItemSearchResults(                    @SearchTerm nvarchar(255),                    @SiteId uniqueidentifier,                    @WebId uniqueidentifier,                    @RankPaging int,                    @ModifiedPaging datetime,                    @ItemIdPaging uniqueidentifier)              AS                    SET NOCOUNT ON                    SELECT                          UserData.nvarchar1,                          UserData.nvarchar3,                          UserData.nvarchar7,                          Docs.DirName,                          Docs.LeafName,                          UserData.tp_ID,                          UserData.tp_ListId,                          Lists.tp_Title,                          UserData.tp_Author,                          UserData.tp_Editor,                          UserInfo.tp_Title,                          UserData.tp_Modified AS LastModified,                          NULL,                          UserData.tp_GUID AS ItemId,                          ISNULL(UserData#CT.Rank,0) + ISNULL(UserInfo#CT.Rank,0)                                AS CT#Rank,                          0                    FROM                          UserData                    LEFT OUTER JOIN                          FreeTextTable(UserData, *, @SearchTerm) AS UserData#CT                    ON                          UserData#CT.[Key] = UserData.tp_GUID                    LEFT OUTER JOIN                          Lists                    ON                          (Lists.tp_WebId = @WebId) AND                          (Lists.tp_ID = UserData.tp_ListId)                    LEFT OUTER JOIN                          UserInfo                    ON                          (UserInfo.tp_ID = UserData.tp_Editor) AND                          (UserInfo.tp_SiteID = UserData.tp_SiteId)                    LEFT OUTER JOIN                          Docs                    ON                          (Docs.SiteId = @SiteId) AND                          (Docs.ListId = UserData.tp_ListId) AND                          (Docs.DocLibRowId = UserData.tp_ID)                    LEFT OUTER JOIN                          FreeTextTable(UserInfo, *, @SearchTerm) AS UserInfo#CT                    ON                          UserInfo#CT.[Key] = UserInfo.tp_GUID                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((Lists.tp_Flags IS NULL) OR                           ((Lists.tp_Flags & 0x400) = 0) OR                           (UserData.tp_ModerationStatus = 0)) AND                          ((UserData#CT.Rank IS NOT NULL) OR                           (UserInfo#CT.Rank IS NOT NULL)) AND                          ((@RankPaging IS NULL) OR                           (ISNULL(UserData#CT.Rank, 0) +                             ISNULL(UserInfo#CT.Rank, 0) < @RankPaging) OR                           ((ISNULL(UserData#CT.Rank, 0) +                              ISNULL(UserInfo#CT.Rank, 0) = @RankPaging) AND                            ((UserData.tp_Modified < @ModifiedPaging) OR                             ((UserData.tp_Modified = @ModifiedPaging) AND                              (UserData.tp_GUID > @ItemIdPaging)))))                    UNION ALL                    SELECT                          Lists.tp_Title,                          NULL,                          NULL,                          Docs.DirName,                          Docs.LeafName,                          NULL,                          Lists.tp_ID,                          NULL,                          NULL,                          NULL,                          NULL,                          Lists.tp_Modified AS LastModified,                          NULL,                          Lists.tp_ID AS ItemId,                          Lists#CT.Rank AS CT#Rank,                          1                    FROM                          Lists                    INNER JOIN                          FreeTextTable(Lists, *, @SearchTerm) AS Lists#CT                    ON                          Lists#CT.[Key] = Lists.tp_ID                    LEFT OUTER JOIN                          Docs                    ON                          Lists.tp_RootFolder = Docs.[Id]                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((@RankPaging IS NULL) OR                           (Lists#CT.Rank < @RankPaging) OR                           ((Lists#CT.Rank = @RankPaging) AND                            ((Lists.tp_Modified < @ModifiedPaging) OR                             ((Lists.tp_Modified = @ModifiedPaging) AND                              (Lists.tp_ID > @ItemIdPaging)))))                    ORDER BY                          CT#Rank DESC,                          LastModified DESC,                          ItemId ASC                    RETURN 0
    60 CREATE PROCEDURE proc_FetchListItemSearchResults(                    @SearchTerm nvarchar(255),                    @SiteId uniqueidentifier,                    @WebId uniqueidentifier,                    @RankPaging int,                    @ModifiedPaging datetime,                    @ItemIdPaging uniqueidentifier)              AS                    SET NOCOUNT ON                    SELECT                          UserData.nvarchar1,                          UserData.nvarchar3,                          UserData.nvarchar7,                          Docs.DirName,                          Docs.LeafName,                          UserData.tp_ID,                          UserData.tp_ListId,                          Lists.tp_Title,                          UserData.tp_Author,                          UserData.tp_Editor,                          UserInfo.tp_Title,                          UserData.tp_Modified AS LastModified,                          NULL,                          UserData.tp_GUID AS ItemId,                          ISNULL(UserData#CT.Rank,0) + ISNULL(UserInfo#CT.Rank,0)                                AS CT#Rank,                          0                    FROM                          UserData                    LEFT OUTER JOIN                          FreeTextTable(UserData, *, @SearchTerm) AS UserData#CT                    ON                          UserData#CT.[Key] = UserData.tp_GUID                    LEFT OUTER JOIN                          Lists                    ON                          (Lists.tp_WebId = @WebId) AND                          (Lists.tp_ID = UserData.tp_ListId)                    LEFT OUTER JOIN                          UserInfo                    ON                          (UserInfo.tp_ID = UserData.tp_Editor) AND                          (UserInfo.tp_SiteID = UserData.tp_SiteId)                    LEFT OUTER JOIN                          Docs                    ON                          (Docs.SiteId = @SiteId) AND                          (Docs.ListId = UserData.tp_ListId) AND                          (Docs.DocLibRowId = UserData.tp_ID)                    LEFT OUTER JOIN                          FreeTextTable(UserInfo, *, @SearchTerm) AS UserInfo#CT                    ON                          UserInfo#CT.[Key] = UserInfo.tp_GUID                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((Lists.tp_Flags IS NULL) OR                           ((Lists.tp_Flags & 0x400) = 0) OR                           (UserData.tp_ModerationStatus = 0)) AND                          ((UserData#CT.Rank IS NOT NULL) OR                           (UserInfo#CT.Rank IS NOT NULL)) AND                          ((@RankPaging IS NULL) OR                           (ISNULL(UserData#CT.Rank, 0) +                             ISNULL(UserInfo#CT.Rank, 0) < @RankPaging) OR                           ((ISNULL(UserData#CT.Rank, 0) +                              ISNULL(UserInfo#CT.Rank, 0) = @RankPaging) AND                            ((UserData.tp_Modified < @ModifiedPaging) OR                             ((UserData.tp_Modified = @ModifiedPaging) AND                              (UserData.tp_GUID > @ItemIdPaging)))))                    UNION ALL                    SELECT                          Lists.tp_Title,                          NULL,                          NULL,                          Docs.DirName,                          Docs.LeafName,                          NULL,                          Lists.tp_ID,                          NULL,                          NULL,                          NULL,                          NULL,                          Lists.tp_Modified AS LastModified,                          NULL,                          Lists.tp_ID AS ItemId,                          Lists#CT.Rank AS CT#Rank,                          1                    FROM                          Lists                    INNER JOIN                          FreeTextTable(Lists, *, @SearchTerm) AS Lists#CT                    ON                          Lists#CT.[Key] = Lists.tp_ID                    LEFT OUTER JOIN                          Docs                    ON                          Lists.tp_RootFolder = Docs.[Id]                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((@RankPaging IS NULL) OR                           (Lists#CT.Rank < @RankPaging) OR                           ((Lists#CT.Rank = @RankPaging) AND                            ((Lists.tp_Modified < @ModifiedPaging) OR                             ((Lists.tp_Modified = @ModifiedPaging) AND                              (Lists.tp_ID > @ItemIdPaging)))))                    ORDER BY                          CT#Rank DESC,                          LastModified DESC,                          ItemId ASC                    RETURN 0
    62 CREATE PROCEDURE proc_FetchListItemSearchResults(                    @SearchTerm nvarchar(255),                    @SiteId uniqueidentifier,                    @WebId uniqueidentifier,                    @RankPaging int,                    @ModifiedPaging datetime,                    @ItemIdPaging uniqueidentifier)              AS                    SET NOCOUNT ON                    SELECT                          UserData.nvarchar1,                          UserData.nvarchar3,                          UserData.nvarchar7,                          Docs.DirName,                          Docs.LeafName,                          UserData.tp_ID,                          UserData.tp_ListId,                          Lists.tp_Title,                          UserData.tp_Author,                          UserData.tp_Editor,                          UserInfo.tp_Title,                          UserData.tp_Modified AS LastModified,                          NULL,                          UserData.tp_GUID AS ItemId,                          ISNULL(UserData#CT.Rank,0) + ISNULL(UserInfo#CT.Rank,0)                                AS CT#Rank,                          0                    FROM                          UserData                    LEFT OUTER JOIN                          FreeTextTable(UserData, *, @SearchTerm) AS UserData#CT                    ON                          UserData#CT.[Key] = UserData.tp_GUID                    LEFT OUTER JOIN                          Lists                    ON                          (Lists.tp_WebId = @WebId) AND                          (Lists.tp_ID = UserData.tp_ListId)                    LEFT OUTER JOIN                          UserInfo                    ON                          (UserInfo.tp_ID = UserData.tp_Editor) AND                          (UserInfo.tp_SiteID = UserData.tp_SiteId)                    LEFT OUTER JOIN                          Docs                    ON                          (Docs.SiteId = @SiteId) AND                          (Docs.ListId = UserData.tp_ListId) AND                          (Docs.DocLibRowId = UserData.tp_ID)                    LEFT OUTER JOIN                          FreeTextTable(UserInfo, *, @SearchTerm) AS UserInfo#CT                    ON                          UserInfo#CT.[Key] = UserInfo.tp_GUID                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((Lists.tp_Flags IS NULL) OR                           ((Lists.tp_Flags & 0x400) = 0) OR                           (UserData.tp_ModerationStatus = 0)) AND                          ((UserData#CT.Rank IS NOT NULL) OR                           (UserInfo#CT.Rank IS NOT NULL)) AND                          ((@RankPaging IS NULL) OR                           (ISNULL(UserData#CT.Rank, 0) +                             ISNULL(UserInfo#CT.Rank, 0) < @RankPaging) OR                           ((ISNULL(UserData#CT.Rank, 0) +                              ISNULL(UserInfo#CT.Rank, 0) = @RankPaging) AND                            ((UserData.tp_Modified < @ModifiedPaging) OR                             ((UserData.tp_Modified = @ModifiedPaging) AND                              (UserData.tp_GUID > @ItemIdPaging)))))                    UNION ALL                    SELECT                          Lists.tp_Title,                          NULL,                          NULL,                          Docs.DirName,                          Docs.LeafName,                          NULL,                          Lists.tp_ID,                          NULL,                          NULL,                          NULL,                          NULL,                          Lists.tp_Modified AS LastModified,                          NULL,                          Lists.tp_ID AS ItemId,                          Lists#CT.Rank AS CT#Rank,                          1                    FROM                          Lists                    INNER JOIN                          FreeTextTable(Lists, *, @SearchTerm) AS Lists#CT                    ON                          Lists#CT.[Key] = Lists.tp_ID                    LEFT OUTER JOIN                          Docs                    ON                          Lists.tp_RootFolder = Docs.[Id]                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((@RankPaging IS NULL) OR                           (Lists#CT.Rank < @RankPaging) OR                           ((Lists#CT.Rank = @RankPaging) AND                            ((Lists.tp_Modified < @ModifiedPaging) OR                             ((Lists.tp_Modified = @ModifiedPaging) AND                              (Lists.tp_ID > @ItemIdPaging)))))                    ORDER BY                          CT#Rank DESC,                          LastModified DESC,                          ItemId ASC                    RETURN 0
    63 CREATE PROCEDURE proc_FetchListItemSearchResults(                    @SearchTerm nvarchar(255),                    @SiteId uniqueidentifier,                    @WebId uniqueidentifier,                    @RankPaging int,                    @ModifiedPaging datetime,                    @ItemIdPaging uniqueidentifier)              AS                    SET NOCOUNT ON                    SELECT                          UserData.nvarchar1,                          UserData.nvarchar3,                          UserData.nvarchar7,                          Docs.DirName,                          Docs.LeafName,                          UserData.tp_ID,                          UserData.tp_ListId,                          Lists.tp_Title,                          UserData.tp_Author,                          UserData.tp_Editor,                          UserInfo.tp_Title,                          UserData.tp_Modified AS LastModified,                          NULL,                          UserData.tp_GUID AS ItemId,                          ISNULL(UserData#CT.Rank,0) + ISNULL(UserInfo#CT.Rank,0)                                AS CT#Rank,                          0                    FROM                          UserData                    LEFT OUTER JOIN                          FreeTextTable(UserData, *, @SearchTerm) AS UserData#CT                    ON                          UserData#CT.[Key] = UserData.tp_GUID                    LEFT OUTER JOIN                          Lists                    ON                          (Lists.tp_WebId = @WebId) AND                          (Lists.tp_ID = UserData.tp_ListId)                    LEFT OUTER JOIN                          UserInfo                    ON                          (UserInfo.tp_ID = UserData.tp_Editor) AND                          (UserInfo.tp_SiteID = UserData.tp_SiteId)                    LEFT OUTER JOIN                          Docs                    ON                          (Docs.SiteId = @SiteId) AND                          (Docs.ListId = UserData.tp_ListId) AND                          (Docs.DocLibRowId = UserData.tp_ID)                    LEFT OUTER JOIN                          FreeTextTable(UserInfo, *, @SearchTerm) AS UserInfo#CT                    ON                          UserInfo#CT.[Key] = UserInfo.tp_GUID                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((Lists.tp_Flags IS NULL) OR                           ((Lists.tp_Flags & 0x400) = 0) OR                           (UserData.tp_ModerationStatus = 0)) AND                          ((UserData#CT.Rank IS NOT NULL) OR                           (UserInfo#CT.Rank IS NOT NULL)) AND                          ((@RankPaging IS NULL) OR                           (ISNULL(UserData#CT.Rank, 0) +                             ISNULL(UserInfo#CT.Rank, 0) < @RankPaging) OR                           ((ISNULL(UserData#CT.Rank, 0) +                              ISNULL(UserInfo#CT.Rank, 0) = @RankPaging) AND                            ((UserData.tp_Modified < @ModifiedPaging) OR                             ((UserData.tp_Modified = @ModifiedPaging) AND                              (UserData.tp_GUID > @ItemIdPaging)))))                    UNION ALL                    SELECT                          Lists.tp_Title,                          NULL,                          NULL,                          Docs.DirName,                          Docs.LeafName,                          NULL,                          Lists.tp_ID,                          NULL,                          NULL,                          NULL,                          NULL,                          Lists.tp_Modified AS LastModified,                          NULL,                          Lists.tp_ID AS ItemId,                          Lists#CT.Rank AS CT#Rank,                          1                    FROM                          Lists                    INNER JOIN                          FreeTextTable(Lists, *, @SearchTerm) AS Lists#CT                    ON                          Lists#CT.[Key] = Lists.tp_ID                    LEFT OUTER JOIN                          Docs                    ON                          Lists.tp_RootFolder = Docs.[Id]                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((@RankPaging IS NULL) OR                           (Lists#CT.Rank < @RankPaging) OR                           ((Lists#CT.Rank = @RankPaging) AND                            ((Lists.tp_Modified < @ModifiedPaging) OR                             ((Lists.tp_Modified = @ModifiedPaging) AND                              (Lists.tp_ID > @ItemIdPaging)))))                    ORDER BY                          CT#Rank DESC,                          LastModified DESC,                          ItemId ASC                    RETURN 0
    64 Code Snippet  select session_id, Text    from sys.dm_exec_requests r    cross apply sys.dm_exec_sql_text(sql_handle) t 
    67 xp_sqlmaint
    71 CREATE PROCEDURE proc_FetchListItemSearchResults(                    @SearchTerm nvarchar(255),                    @SiteId uniqueidentifier,                    @WebId uniqueidentifier,                    @RankPaging int,                    @ModifiedPaging datetime,                    @ItemIdPaging uniqueidentifier)              AS                    SET NOCOUNT ON                    SELECT                          UserData.nvarchar1,                          UserData.nvarchar3,                          UserData.nvarchar7,                          Docs.DirName,                          Docs.LeafName,                          UserData.tp_ID,                          UserData.tp_ListId,                          Lists.tp_Title,                          UserData.tp_Author,                          UserData.tp_Editor,                          UserInfo.tp_Title,                          UserData.tp_Modified AS LastModified,                          NULL,                          UserData.tp_GUID AS ItemId,                          ISNULL(UserData#CT.Rank,0) + ISNULL(UserInfo#CT.Rank,0)                                AS CT#Rank,                          0                    FROM                          UserData                    LEFT OUTER JOIN                          FreeTextTable(UserData, *, @SearchTerm) AS UserData#CT                    ON                          UserData#CT.[Key] = UserData.tp_GUID                    LEFT OUTER JOIN                          Lists                    ON                          (Lists.tp_WebId = @WebId) AND                          (Lists.tp_ID = UserData.tp_ListId)                    LEFT OUTER JOIN                          UserInfo                    ON                          (UserInfo.tp_ID = UserData.tp_Editor) AND                          (UserInfo.tp_SiteID = UserData.tp_SiteId)                    LEFT OUTER JOIN                          Docs                    ON                          (Docs.SiteId = @SiteId) AND                          (Docs.ListId = UserData.tp_ListId) AND                          (Docs.DocLibRowId = UserData.tp_ID)                    LEFT OUTER JOIN                          FreeTextTable(UserInfo, *, @SearchTerm) AS UserInfo#CT                    ON                          UserInfo#CT.[Key] = UserInfo.tp_GUID                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((Lists.tp_Flags IS NULL) OR                           ((Lists.tp_Flags & 0x400) = 0) OR                           (UserData.tp_ModerationStatus = 0)) AND                          ((UserData#CT.Rank IS NOT NULL) OR                           (UserInfo#CT.Rank IS NOT NULL)) AND                          ((@RankPaging IS NULL) OR                           (ISNULL(UserData#CT.Rank, 0) +                             ISNULL(UserInfo#CT.Rank, 0) < @RankPaging) OR                           ((ISNULL(UserData#CT.Rank, 0) +                              ISNULL(UserInfo#CT.Rank, 0) = @RankPaging) AND                            ((UserData.tp_Modified < @ModifiedPaging) OR                             ((UserData.tp_Modified = @ModifiedPaging) AND                              (UserData.tp_GUID > @ItemIdPaging)))))                    UNION ALL                    SELECT                          Lists.tp_Title,                          NULL,                          NULL,                          Docs.DirName,                          Docs.LeafName,                          NULL,                          Lists.tp_ID,                          NULL,                          NULL,                          NULL,                          NULL,                          Lists.tp_Modified AS LastModified,                          NULL,                          Lists.tp_ID AS ItemId,                          Lists#CT.Rank AS CT#Rank,                          1                    FROM                          Lists                    INNER JOIN                          FreeTextTable(Lists, *, @SearchTerm) AS Lists#CT                    ON                          Lists#CT.[Key] = Lists.tp_ID                    LEFT OUTER JOIN                          Docs                    ON                          Lists.tp_RootFolder = Docs.[Id]                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((@RankPaging IS NULL) OR                           (Lists#CT.Rank < @RankPaging) OR                           ((Lists#CT.Rank = @RankPaging) AND                            ((Lists.tp_Modified < @ModifiedPaging) OR                             ((Lists.tp_Modified = @ModifiedPaging) AND                              (Lists.tp_ID > @ItemIdPaging)))))                    ORDER BY                          CT#Rank DESC,                          LastModified DESC,                          ItemId ASC                    RETURN 0
    72 CREATE PROCEDURE proc_FetchListItemSearchResults(                    @SearchTerm nvarchar(255),                    @SiteId uniqueidentifier,                    @WebId uniqueidentifier,                    @RankPaging int,                    @ModifiedPaging datetime,                    @ItemIdPaging uniqueidentifier)              AS                    SET NOCOUNT ON                    SELECT                          UserData.nvarchar1,                          UserData.nvarchar3,                          UserData.nvarchar7,                          Docs.DirName,                          Docs.LeafName,                          UserData.tp_ID,                          UserData.tp_ListId,                          Lists.tp_Title,                          UserData.tp_Author,                          UserData.tp_Editor,                          UserInfo.tp_Title,                          UserData.tp_Modified AS LastModified,                          NULL,                          UserData.tp_GUID AS ItemId,                          ISNULL(UserData#CT.Rank,0) + ISNULL(UserInfo#CT.Rank,0)                                AS CT#Rank,                          0                    FROM                          UserData                    LEFT OUTER JOIN                          FreeTextTable(UserData, *, @SearchTerm) AS UserData#CT                    ON                          UserData#CT.[Key] = UserData.tp_GUID                    LEFT OUTER JOIN                          Lists                    ON                          (Lists.tp_WebId = @WebId) AND                          (Lists.tp_ID = UserData.tp_ListId)                    LEFT OUTER JOIN                          UserInfo                    ON                          (UserInfo.tp_ID = UserData.tp_Editor) AND                          (UserInfo.tp_SiteID = UserData.tp_SiteId)                    LEFT OUTER JOIN                          Docs                    ON                          (Docs.SiteId = @SiteId) AND                          (Docs.ListId = UserData.tp_ListId) AND                          (Docs.DocLibRowId = UserData.tp_ID)                    LEFT OUTER JOIN                          FreeTextTable(UserInfo, *, @SearchTerm) AS UserInfo#CT                    ON                          UserInfo#CT.[Key] = UserInfo.tp_GUID                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((Lists.tp_Flags IS NULL) OR                           ((Lists.tp_Flags & 0x400) = 0) OR                           (UserData.tp_ModerationStatus = 0)) AND                          ((UserData#CT.Rank IS NOT NULL) OR                           (UserInfo#CT.Rank IS NOT NULL)) AND                          ((@RankPaging IS NULL) OR                           (ISNULL(UserData#CT.Rank, 0) +                             ISNULL(UserInfo#CT.Rank, 0) < @RankPaging) OR                           ((ISNULL(UserData#CT.Rank, 0) +                              ISNULL(UserInfo#CT.Rank, 0) = @RankPaging) AND                            ((UserData.tp_Modified < @ModifiedPaging) OR                             ((UserData.tp_Modified = @ModifiedPaging) AND                              (UserData.tp_GUID > @ItemIdPaging)))))                    UNION ALL                    SELECT                          Lists.tp_Title,                          NULL,                          NULL,                          Docs.DirName,                          Docs.LeafName,                          NULL,                          Lists.tp_ID,                          NULL,                          NULL,                          NULL,                          NULL,                          Lists.tp_Modified AS LastModified,                          NULL,                          Lists.tp_ID AS ItemId,                          Lists#CT.Rank AS CT#Rank,                          1                    FROM                          Lists                    INNER JOIN                          FreeTextTable(Lists, *, @SearchTerm) AS Lists#CT                    ON                          Lists#CT.[Key] = Lists.tp_ID                    LEFT OUTER JOIN                          Docs                    ON                          Lists.tp_RootFolder = Docs.[Id]                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((@RankPaging IS NULL) OR                           (Lists#CT.Rank < @RankPaging) OR                           ((Lists#CT.Rank = @RankPaging) AND                            ((Lists.tp_Modified < @ModifiedPaging) OR                             ((Lists.tp_Modified = @ModifiedPaging) AND                              (Lists.tp_ID > @ItemIdPaging)))))                    ORDER BY                          CT#Rank DESC,                          LastModified DESC,                          ItemId ASC                    RETURN 0
    73 xp_sqlmaint
    74 CREATE PROCEDURE proc_FetchListItemSearchResults(                    @SearchTerm nvarchar(255),                    @SiteId uniqueidentifier,                    @WebId uniqueidentifier,                    @RankPaging int,                    @ModifiedPaging datetime,                    @ItemIdPaging uniqueidentifier)              AS                    SET NOCOUNT ON                    SELECT                          UserData.nvarchar1,                          UserData.nvarchar3,                          UserData.nvarchar7,                          Docs.DirName,                          Docs.LeafName,                          UserData.tp_ID,                          UserData.tp_ListId,                          Lists.tp_Title,                          UserData.tp_Author,                          UserData.tp_Editor,                          UserInfo.tp_Title,                          UserData.tp_Modified AS LastModified,                          NULL,                          UserData.tp_GUID AS ItemId,                          ISNULL(UserData#CT.Rank,0) + ISNULL(UserInfo#CT.Rank,0)                                AS CT#Rank,                          0                    FROM                          UserData                    LEFT OUTER JOIN                          FreeTextTable(UserData, *, @SearchTerm) AS UserData#CT                    ON                          UserData#CT.[Key] = UserData.tp_GUID                    LEFT OUTER JOIN                          Lists                    ON                          (Lists.tp_WebId = @WebId) AND                          (Lists.tp_ID = UserData.tp_ListId)                    LEFT OUTER JOIN                          UserInfo                    ON                          (UserInfo.tp_ID = UserData.tp_Editor) AND                          (UserInfo.tp_SiteID = UserData.tp_SiteId)                    LEFT OUTER JOIN                          Docs                    ON                          (Docs.SiteId = @SiteId) AND                          (Docs.ListId = UserData.tp_ListId) AND                          (Docs.DocLibRowId = UserData.tp_ID)                    LEFT OUTER JOIN                          FreeTextTable(UserInfo, *, @SearchTerm) AS UserInfo#CT                    ON                          UserInfo#CT.[Key] = UserInfo.tp_GUID                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((Lists.tp_Flags IS NULL) OR                           ((Lists.tp_Flags & 0x400) = 0) OR                           (UserData.tp_ModerationStatus = 0)) AND                          ((UserData#CT.Rank IS NOT NULL) OR                           (UserInfo#CT.Rank IS NOT NULL)) AND                          ((@RankPaging IS NULL) OR                           (ISNULL(UserData#CT.Rank, 0) +                             ISNULL(UserInfo#CT.Rank, 0) < @RankPaging) OR                           ((ISNULL(UserData#CT.Rank, 0) +                              ISNULL(UserInfo#CT.Rank, 0) = @RankPaging) AND                            ((UserData.tp_Modified < @ModifiedPaging) OR                             ((UserData.tp_Modified = @ModifiedPaging) AND                              (UserData.tp_GUID > @ItemIdPaging)))))                    UNION ALL                    SELECT                          Lists.tp_Title,                          NULL,                          NULL,                          Docs.DirName,                          Docs.LeafName,                          NULL,                          Lists.tp_ID,                          NULL,                          NULL,                          NULL,                          NULL,                          Lists.tp_Modified AS LastModified,                          NULL,                          Lists.tp_ID AS ItemId,                          Lists#CT.Rank AS CT#Rank,                          1                    FROM                          Lists                    INNER JOIN                          FreeTextTable(Lists, *, @SearchTerm) AS Lists#CT                    ON                          Lists#CT.[Key] = Lists.tp_ID                    LEFT OUTER JOIN                          Docs                    ON                          Lists.tp_RootFolder = Docs.[Id]                    WHERE                          (@WebId = Lists.tp_WebId) AND                          ((@RankPaging IS NULL) OR                           (Lists#CT.Rank < @RankPaging) OR                           ((Lists#CT.Rank = @RankPaging) AND                            ((Lists.tp_Modified < @ModifiedPaging) OR                             ((Lists.tp_Modified = @ModifiedPaging) AND                              (Lists.tp_ID > @ItemIdPaging)))))                    ORDER BY                          CT#Rank DESC,                          LastModified DESC,                          ItemId ASC                    RETURN 0

    Friday, May 29, 2009 4:55 PM
  • great script man but now that I got my results

    e.g.

    | session_id |Text                                                         |
    -------------------------------------------------------------------
    | 52             | BACKUP DATABASE [dbName] TO DISK... |

     

    so If I want to purge all current outstanding jobs what script do I perform... ?

    thx

    Monday, April 26, 2010 6:27 PM
  • I am getting the same error and I ran the script and I see a backup already running.

    I used the KILL SPID command to kill that spid but its still showing suspended and after issuing the KILL 52 command again , it shows the message:

    SPID 52: transaction rollback in progress. Estimated rollback completion: 1%. Estimated time remaining: 15557563 seconds.

    It issued the command yesterday and its still giving the same message.

    Friday, May 07, 2010 2:07 PM
  • I got this error after severeal failed attemps of backing up the transaction log. Nothing worked. So I applied the method "When all logical attempts do not solve the problem, apply illogical one". Exit the backup program, and restart backup program, worked like a charm. No error.

    I belive that MS and its backup software for SQL is buggy and sends you on a wild goose chase sometimes.

    Thursday, May 27, 2010 6:20 AM