Howto setup a manual logshipping in SQL server

Whenever i receive an error during the setup of the SQL server logshipping i was always hopeless. But from now on i have a procedure to setup a manual logshipping schedule from primary sql server to secondary. It is important to mention that i used the following structure in SQL Server 2000 and it worked like a piece of cake.

In summary, the main steps are as follows;

1- on primary database: create a job to backup transaction logs regularly
2- on primary database: share the transaction log backup directory.
3- backup and restore the database to be log shipped from prımary to secondary database
4- on secondary database: create the following structure (tables and procedures)
5- on secondary database: create the copy and load jobs via the created structure
6- on secondary database: monitor the process

There are 3 stored procedures and 3 tables to create;

USE msdb
CREATE TABLE backup_movement_plans
(
  plan_id         UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED,
  plan_name       sysname          NULL,
  source_dir      NVARCHAR(256)    NOT NULL,
  destination_dir NVARCHAR(256)    NOT NULL,
  database_subdir BIT              NOT NULL DEFAULT (1)
)

USE msdb
CREATE TABLE backup_movement_plan_databases
(
plan_id  UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES       backup_movement_plans(plan_id),
  source_database      sysname          NOT NULL,
  destination_database sysname          NOT NULL,
  source_server        sysname          NOT NULL DEFAULT (@@servername),
  load_delay           INT              NOT NULL DEFAULT(0),  -- In minutes
  load_all             BIT              NOT NULL DEFAULT(1),
  retention_period     INT              NOT NULL DEFAULT(48), -- In hours
  last_file_copied     NVARCHAR(256)    NULL,
  date_last_copied     DATETIME         NULL,
  last_file_loaded     NVARCHAR(256)    NULL,
  date_last_loaded     DATETIME         NULL
)

USE msdb
CREATE TABLE backup_movement_plan_history
(
  sequence_id          INT              NOT NULL IDENTITY UNIQUE CLUSTERED,
  plan_id              UNIQUEIDENTIFIER NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),
  plan_name            sysname          NOT NULL DEFAULT ('All ad-hoc plans'),
  destination_server   sysname          NOT NULL DEFAULT (@@servername),
  source_server        sysname          NOT NULL DEFAULT (@@servername),
  source_database      sysname          NOT NULL,
  destination_database sysname          NOT NULL,
  activity             BIT              NOT NULL DEFAULT (0),
  succeeded            BIT              NOT NULL DEFAULT (1),
  num_files            INT              NOT NULL DEFAULT (0),
  last_file            NVARCHAR(256)    NULL,
  end_time             DATETIME         NOT NULL DEFAULT (GETDATE()),
  duration             INT              NULL     DEFAULT (0),
  error_number         INT              NOT NULL DEFAULT (0),
  message              NVARCHAR(512)    NULL
)  

USE msdb
CREATE PROCEDURE sp_create_backup_movement_plan
    @name          sysname,
    @source_dir    VARCHAR(256),
    @dest_dir      VARCHAR(256),
    @sub_dir       BIT = 1, -- Each database has it's own sub-directory
    @load_job_freq INT = 5, -- In Minutes
    @copy_job_freq INT = 5  -- In Minutes
AS
BEGIN

BEGIN TRANSACTION
  SET       NOCOUNT             ON
  SET       QUOTED_IDENTIFIER   OFF
  SET       ANSI_NULLS          ON 

  DECLARE   @PlanID        uniqueidentifier
  DECLARE   @CopyJobName   sysname
  DECLARE   @LoadJobName   sysname
  DECLARE   @CopyCommand   VARCHAR(500)
  DECLARE   @LoadCommand   VARCHAR(500)
  DECLARE   @ReturnCode    INT

  -- Create a GUID for the plan
  SELECT @PlanID = NEWID()
    
  -- Check if a plan with the same name exists
  IF (EXISTS (SELECT * 
              FROM   msdb.dbo.backup_movement_plans
              WHERE  plan_name = @name ))
  BEGIN
    RAISERROR('A backup movement plan with the same name already exists. Specify a different name.'', 16, 1)
    GOTO QuitWithRollback
  END

  -- Insert plan in the table
  INSERT msdb.dbo.backup_movement_plans 
         (plan_id, plan_name, source_dir, destination_dir, database_subdir)
  VALUES
         (@PlanID, @name, @source_dir, @dest_dir, @sub_dir)

  SELECT @CopyJobName = N'Copy Job For ' + @name
  SELECT @LoadJobName = N'Load Job For ' + @name
  SELECT @CopyCommand = N'EXECUTE master.dbo.xp_sqlmaint ''-CopyPlanName "' + @name + '" '' '
  SELECT @LoadCommand = N'EXECUTE master.dbo.xp_sqlmaint ''-LoadPlanName "' + @name + '" '' '
  
  -- Create the load job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_name = @LoadJobName

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_name = @LoadJobName, 
     @step_id=1,
     @step_name = N'step1', 
     @command = @LoadCommand, 
     @subsystem = N'TSQL', 
     @on_success_step_id = 0, 
     @on_success_action = 1, 
     @on_fail_step_id = 0, 
     @on_fail_action = 2, @flags = 4

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name = @LoadJobName, 
     @freq_subday_interval = @load_job_freq, 
     @name = N'sch1', 
     @enabled = 1, 
     @freq_type = 4, 
     @active_start_date = 19980402, 
     @active_start_time = 0, 
     @freq_interval = 1, 
     @freq_subday_type = 4, 
     @freq_relative_interval = 0, 
     @freq_recurrence_factor = 0, 
     @active_end_date = 99991231, 
     @active_end_time = 235959

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_name = @LoadJobName, @server_name = N'(local)' 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Create the Copy Job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_name = @CopyJobName

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_name = @CopyJobName, 
     @step_id = 1, 
     @step_name = N'step1', 
     @command = @CopyCommand, 
     @subsystem = N'TSQL', 
     @on_success_step_id = 0, 
     @on_success_action = 1, 
     @on_fail_step_id = 0, 
     @on_fail_action = 2, 
     @flags = 4

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name = @CopyJobName, 
     @freq_subday_interval = @copy_job_freq, 
     @name = N'sch1', 
     @enabled = 1, 
     @freq_type = 4, 
     @active_start_date = 19980402, 
     @active_start_time = 0, 
     @freq_interval = 1, 
     @freq_subday_type = 4, 
     @freq_relative_interval = 0, 
     @freq_recurrence_factor = 0, 
     @active_end_date = 99991231, 
     @active_end_time = 235959

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_name = @CopyJobName, @server_name = N'(local)' 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 
END 

USE msdb
CREATE PROCEDURE sp_add_db_to_backup_movement_plan
    @plan_id       uniqueidentifier   = NULL,
    @plan_name     sysname            = NULL,
    @source_db     sysname,
    @dest_db       sysname,
    @load_delay    INT                = 0,            -- In Minutes
    @load_all      BIT                = 1,
    @source_server sysname            = @@servername,
    @retention_period INT             = 48            -- In Hours. 0 implies do not delete files 
AS
BEGIN
    SET       NOCOUNT             ON
    SET       QUOTED_IDENTIFIER   OFF
    SET       ANSI_NULLS          ON 
    DECLARE   @PlanID             uniqueidentifier

    if((@plan_id IS NULL) AND (@plan_name IS NULL))
    BEGIN
      RAISERROR('You must supply the plan name or the plan id.', 16, 1)
      RETURN(1)
    END

    IF (@plan_id IS NULL)
    BEGIN        
        IF (NOT EXISTS (SELECT * 
                        FROM   msdb.dbo.backup_movement_plans
                        WHERE  plan_name = @plan_name ) )
        BEGIN
          RAISERROR('Backup movement plan with this name was not found.', 16, 1)
          RETURN(1)
        END

        IF (SELECT COUNT(*) 
            FROM   msdb.dbo.backup_movement_plans
            WHERE  plan_name = @plan_name) > 1
        BEGIN
          RAISERROR('There are more than one backup movement plans with this name.', 16, 1)
          RETURN(1)
        END

        SELECT @PlanID = plan_id 
        FROM   msdb.dbo.backup_movement_plans
        WHERE  plan_name = @plan_name
    END
    ELSE
    BEGIN
        SELECT @PlanID = @plan_id 
        IF (NOT EXISTS (SELECT * 
                        FROM   msdb.dbo.backup_movement_plans
                        WHERE  plan_id = @plan_id ) )
        BEGIN
          RAISERROR('Backup movement plan with this id.', 16, 1)
          RETURN(1)
        END

        IF (SELECT COUNT(*) 
            FROM   msdb.dbo.backup_movement_plans
            WHERE  plan_id = @plan_id) > 1
        BEGIN
          RAISERROR('There are more than one backup movement plans with this id.', 16, 1)
          RETURN(1)
        END
    END

    IF (EXISTS ( SELECT *
                 FROM msdb.dbo.backup_movement_plan_databases
                 WHERE plan_id = @PlanID AND source_database = @source_db AND destination_database = @dest_db ))
    BEGIN
      RAISERROR('These databases are already included in this plan', 16, 1)
      RETURN(1)
    END
        
    INSERT msdb.dbo.backup_movement_plan_databases
   (plan_id, source_database, destination_database, load_delay, load_all, source_server, retention_period)
    VALUES
          (@PlanID, @source_db, @dest_db, @load_delay, @load_all, @source_server, @retention_period)
END

After creating the infrastructure you can create the transaction log backup copy and load jobs by running the following script. After running the script logshipping started via the created jobs and the process can be monitored by creating and using the following stored procedure.

exec msdb..sp_create_backup_movement_plan 
  @name = "DB01_logshipping",
  @source_dir = "\\PRMDB01\trnlogs", 
  @dest_dir = "D:\logshipping\",
  @sub_dir = "DB01",
  @load_job_freq =30,
  @copy_job_freq = 30

exec msdb..sp_add_db_to_backup_movement_plan 
  @plan_name = "DB01_logshipping",
  @source_db = "DB01", 
  @dest_db = "DB01",
  @load_delay = 10, 
  @load_all = 1,
  @source_server = 'PRMYDB01', 
  @retention_period = 30  --in days

Here is the stored procedure to monitor the log shipping process.

USE msdb
CREATE PROCEDURE dbo.sp_log_ship_status
 @p_svr         varchar( 30 ) = NULL,
 @p_db          varchar( 30 )= NULL
AS
Begin

set nocount on
DECLARE @dest_db char(30),
 @history_id int,
 @time_delta int

CREATE TABLE #table ( destination_db  CHAR(30),
   time_delta INT)

DECLARE log_ship_cursor CURSOR
 FOR SELECT destination_database 
 from backup_movement_plan_databases

OPEN log_ship_cursor

FETCH NEXT FROM log_ship_cursor into @dest_db

WHILE @@FETCH_STATUS = 0
BEGIN
  set nocount on

  select  @history_id = (select max(restore_history_id) 
                           from restorehistory 
                           where destination_database_name = @dest_db)

  select  @time_delta = (select datediff(mi, (select backup_start_date 
                           from backupset 
                           where backup_set_id = (select backup_set_id 
                                                   from restorehistory 
                                                   where restore_history_id = @history_id)), getdate()))

  INSERT INTO #table VALUES( @dest_db, @time_delta)
  FETCH NEXT from log_ship_cursor into @dest_db 
end

close log_ship_cursor
DEALLOCATE log_ship_cursor

SELECT "Primary Srv" = CONVERT(char(30),source_server),
 "Primary DB" = CONVERT(char(30),source_database),
 "Secondary DB" = CONVERT(char(30),destination_database),
 "Delta" = time_delta,
 "Load All" = CASE WHEN (load_all = 0) THEN "No" ELSE "Yes" end,
 "Load Delay" = load_delay,
 "Save Period" = retention_period,
 "Last File Copied" = CONVERT(char(75),last_file_copied),
 "Copy Logged Time" = date_last_copied,
 "Last File Loaded" = CONVERT(char(75),last_file_loaded),
 "Load Logged Time" = date_last_loaded
FROM  msdb..backup_movement_plan_databases,
 #table
WHERE (@p_svr is NULL or source_server like @p_svr)
AND (@p_db is NULL or source_database like @p_db)
AND destination_database = destination_db

drop table #table
END

-------------------------
-- it seems it is working
-------------------------
dbo.log_ship_status
/*
Primary Srv Primary DB      Secondary DB      Delta    Load All Load Delay  Save Period Last File Copied                                 Copy Logged Time        Last File Loaded                               Load Logged Time
----------- --------------- ----------------- -------- -------- ----------- ----------- ------------------------------------------------ ----------------------- -------------------------------------------    -----------------------
crmsbdb01   siebeldb        siebeldb          34       Yes      1           60          \\crmsbdb01\t-logs\siebeldb\siebeldb_tlog_201... 2011-07-13 15:10:01.610 E:\t-logs\siebeldb\siebeldb_tlog_2011071314... 2011-07-13 15:00:12.563
crmsbdb01   IVR_INTEGRATE   IVR_INTEGRATE     34       Yes      1           60          \\crmsbdb01\t-logs\IVR_INTEGRATE\IVR_INTEGRAT... 2011-07-13 15:10:03.313 E:\t-logs\IVR_INTEGRATE\IVR_INTEGRATE_tlog_... 2011-07-13 14:50:07.627
*/

testing SQL Server 2005 partitioning option

This post is a version of the pervious "SQL Server Partitioning Option" explanatory presentation of mine which i wrote for a friend of mine. The presentation starts with what is partitioning, why to use table partitions, how to use the partitons and advantages. All the chapters basis on one test case which consists of partitioning a table and examine the IO performance before and after the partition operation.

What is partitioning
Partitioning is basically aggregation of the data by its meaning or value on different physical structures and this also brings retrieve times to acceptible values.

As this option can be used for archiving needs it is usually used for faster reporting and query performances.

Why to use partitioning
The mostly used area of the partitioning is increasing full table scan performances. One of the most obvious reason of the unavoidable full table scans which are; accessing more than certain amount of table data (There are different methods to avoid or increasing performance on full table scans but they are not in the scope of this article while we are concerning partitioning option of Microsoft SQL Server).

As well as performance considerations, partitioning also helps archiving in large tables especially log tables that are stored on daily/monthly or even yearly basis. There are lots of structures that holds the older data of the table (say older than 6 months) on SATA disks which are cheaper and on compressed partitions.

How to use partitioning
I have used a Windows Server 2003 Standart Edition Operating system with SQL Server 2005 Enterprise edition (By the way SQL Server Standart Edition does not support partitioning). Recommended hardware requirements were 1Gb of ram 1 CPU and minimum 10Gb of disk space which i installed my virtual machine with these configurations.

First we need a comparison sql query which I used a standart built-in sql server Adventureworks database.

select 
cat.name as category_name, 
subcat.name as subcategory_name, 
prd.* 
from 
AdventureWorks.Production.Product prd LEFT JOIN 
AdventureWorks.Production.ProductSubCategory subcat ON
(prd.ProductSubcategoryID=subcat.ProductSubcategoryID) LEFT JOIN 
AdventureWorks.Production.ProductCategory cat ON
cat.ProductCategoryID=subcat.ProductCategoryID
where
cat.name is not null and
subcat.name is not null
order by 
cat.Name,subcat.name

Now i decided to build a structure that has four different filegroups which correspond to each category so that one query can stick on four of these datafiles simultaneously. To achieve this first i will create four filegroups and add one file to each filegroups as follows (corresponds creating tablespaces on a Oracle database and adding one datafile to each tablespace).

In the real world i would want to spread the datafiles over four individual drives to increase the disk throughput but in this test case i dont have that kind of test environment.

ALTER DATABASE AdventureWorks ADD FILEGROUP [FG1]
ALTER DATABASE AdventureWorks ADD FILEGROUP [FG2]
ALTER DATABASE AdventureWorks ADD FILEGROUP [FG3]
ALTER DATABASE AdventureWorks ADD FILEGROUP [FG4]

ALTER DATABASE AdventureWorks
ADD FILE 
(NAME = N'FG1_F1',
FILENAME = N'C:\FG1_F1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [FG1]

ALTER DATABASE AdventureWorks
ADD FILE 
(NAME = N'FG2_F1',
FILENAME = N'C:\FG2_F1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [FG2]

ALTER DATABASE AdventureWorks
ADD FILE 
(NAME = N'FG3_F1',
FILENAME = N'C:\FG3_F1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [FG3]

ALTER DATABASE AdventureWorks
ADD FILE 
(NAME = N'FG4_F1',
FILENAME = N'C:\FG4_F1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [FG4]

I decided to define a partition strategy over the "category" column which has evenly distributed datasets (you can check by aggregating the data with "group by and count").

The first thing we need is a partitioning function which will create a basis for partition scheme. And then create the partitioning schema based on the parititon function and on the desired filegroups. After all i will create the table on the partition scheme which means using the partition function and all of the filegroups.

-- create the partition function
CREATE PARTITION FUNCTION [f_part_category] (char(20))
AS RANGE LEFT FOR VALUES ('Bikes', 'Components', 'Clothing', 'Accessories');

-- check the partition function
select * from sys.partition_functions

name                function_id   type_desc       
------------------  ------------- --------------- 
f_part_category     65539         RANGE           

(1 row(s) affected)

-- create the partition scheme
CREATE PARTITION SCHEME s_part_category
AS PARTITION f_part_category
TO ([FG1], [FG2], [FG3], [FG4], [FG4]);

-- check the partition scheme
select * from sys.partition_schemes

name                 data_space_id      type  type_desc                  
-------------------- -----------------  ----  -----------------
s_part_category      65601              PS    PARTITION_SCHEME           

(1 row(s) affected)

CREATE TABLE [dbo].[t_product_part](
[category_name] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[subcategory_name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductID] [int] NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ProductNumber] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MakeFlag] [bit] NOT NULL,
[FinishedGoodsFlag] [bit] NOT NULL,
[Color] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SafetyStockLevel] [smallint] NOT NULL,
[ReorderPoint] [smallint] NOT NULL,
[StandardCost] [money] NOT NULL,
[ListPrice] [money] NOT NULL,
[Size] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SizeUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WeightUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Weight] [decimal](8, 2) NULL,
[DaysToManufacture] [int] NOT NULL,
[ProductLine] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Class] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Style] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductSubcategoryID] [int] NULL,
[ProductModelID] [int] NULL,
[SellStartDate] [datetime] NOT NULL,
[SellEndDate] [datetime] NULL,
[DiscontinuedDate] [datetime] NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON s_part_category (category_name)

Now; time to test the performance issue. As the data is not heavily loaded we can check the execution plans of the before and after queries.


insert into t_product_part select * from master.dbo.v_products2

select * from t_product_part

BEFORE PARTITIONING


AFTER PARTITIONING


Conclusion
Effective IO usage with using more than one datafile simultaneously.
Remarkable increase in the IO performance.
2.3 times faster IO performance with the sample data (with only one drive)

SQL Server detected a logical consistency-based I/O error

After migrating the SQL Server 2005 SP2 32-bit database to SP3 64-bit environment some queries started to complain about the following error in the SQL Server Error Logs.


Message:
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x0; actual signature: 0x5be5d620). It occurred during a read of page (1:718204) in database ID 7 at offset 0x0000015eaf8000 in file 'E:\MSSQL\DATA\SMPPGatewayBEP.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.


It is obvious that there happened an IO inconsistency. Maybe while the backup or the restore operation. I decided to execute DBCC CHECKDB('SMPPGatewayBEP') But the following output generated by the server.


Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.
DBCC results for 'SMPPGatewayBEP'.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'SMPPGatewayBEP'.


After a little search on msdn i decided to try to set the database into the single user mode by running:


alter database SMPPGatewayBEP SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB('SMPPGatewayBEP', REPAIR_ALLOW_DATA_LOSS)


Now it started to repair the data pages by reading the log pages. There is thousands of lines of this output so i put the head and tail part of the output.


DBCC results for 'SMPPGatewayBEP'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352523) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352524) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352525) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352526) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352527) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:719092) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:719093) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.

...
..
.

Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
The error has been repaired.
CHECKDB found 0 allocation errors and 1857 consistency errors not associated with any single object.
CHECKDB fixed 0 allocation errors and 973 consistency errors not associated with any single object.
DBCC results for 'sys.sysrowsetcolumns'.
There are 716 rows in 8 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 101 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 115 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 716 rows in 9 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 101 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 115 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 15 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 135 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 134 rows in 4 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 629 rows in 13 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".

...
..
.

Repair: The page (1:718716) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:718717) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:718718) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:718719) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719048) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719049) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719050) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719051) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719052) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719053) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719054) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719055) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).

...
..
.

The error has been repaired.
The error has been repaired.
The error has been repaired.
There are 7270488 rows in 35601 pages for object "WSLog".
CHECKDB found 0 allocation errors and 8 consistency errors in table 'WSLog' (object ID 398624463).
CHECKDB fixed 0 allocation errors and 8 consistency errors in table 'WSLog' (object ID 398624463).
DBCC results for 'SettingsInbound'.
There are 1 rows in 1 pages for object "SettingsInbound".
DBCC results for 'SettingsDelivered'.
There are 1 rows in 1 pages for object "SettingsDelivered".
DBCC results for 'sysdiagrams'.
There are 0 rows in 0 pages for object "sysdiagrams".
DBCC results for 'ThreadMonitoring'.
There are 0 rows in 0 pages for object "ThreadMonitoring".
DBCC results for 'Settings'.
There are 1 rows in 1 pages for object "Settings".
DBCC results for 'SMPPIncoming'.
There are 95 rows in 4 pages for object "SMPPIncoming".
DBCC results for 'BlankMessages'.
There are 0 rows in 0 pages for object "BlankMessages".
DBCC results for 'TestTrace'.
There are 2134061 rows in 46701 pages for object "TestTrace".
DBCC results for 'ServiceParams'.
There are 18 rows in 1 pages for object "ServiceParams".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
CHECKDB found 0 allocation errors and 6622 consistency errors in database 'SMPPGatewayBEP'.
CHECKDB fixed 0 allocation errors and 5738 consistency errors in database 'SMPPGatewayBEP'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SMPPGatewayBEP, repair_allow_data_loss).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


After one hour finally the summary shows that lots of the page errors are repaired. Maybe I should run the same DBCC CHECKDB second time, to be sure if the remaining errors would be gone but we didnt have to wait for that. After setting the database into multiuser mode again and backing it up users were able to run their reports again.


alter database SMPPGatewayBEP SET MULTI_USER;