• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 835
  • Last Modified:

Migration with Fullbackup and incremental/differential

Hi People

I have a situation where we have installed a new server with MS SQL 2005 and we need to migrate the database to the new server. The problem is that the Database is very large and  highly fragmented. De-fragmenting it will take hours. So we have decided we would like to restore the full backup before the day of migrating so that we can defragment it on the new server and then restore the differential backup on the day of migrating.

Now we have two issues;
1. Will this work properly to restore the DB, defragment and then later restore the differential DB
2. Can we use logshipping, stop it on the way and defragment it, then continue with logshipping
3. Is there a better way of achieving this task.

Thanks in advance
0
ackimc
Asked:
ackimc
  • 9
  • 7
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> The problem is that the Database is very large and  highly fragmented.

What is the size of your database in GB..
I don't think that it will take more time for database defragmentation as after migration it is recommended to rebuild indexes to defragment the table and update statistics in target server.

>> 1. Will this work properly to restore the DB, defragment and then later restore the differential DB

If your database size is less than 100 GB, then you can go easily with Full backup restoration in Target server.

>> 2. Can we use logshipping, stop it on the way and defragment it, then continue with logshipping

Hope it would not be required for your scenario

>> 3. Is there a better way of achieving this task.

You can do it by properly planning your downtime so that you can avoid wasting lot of time on additional activities like Differential backup, log shipping or so on.
0
 
ackimcAuthor Commented:
The size of the DB is 71GB

This issue is that we have not rebuilt indexes on the current DB for more than a year up until now. And because this a a busy transactional server, we can not afford to be offline for more than two hours. The process of taking the backup is about 40 mins, restoring is about 25 mins and deframenting may take close to an hour.

The problem that we have with restoring the differential is that the fullbackup restore is supposed to be in no recovery mode, that means we can not run defragmentation on it, I stand to be corrected on that. I would like to go straight and just do a full restore on the migration day but am worried the deframentation may take too long. Also please educate me on the updating statistics, what is the importance and how do u do that.

Thanks in advance
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> This issue is that we have not rebuilt indexes on the current DB for more than a year up until now.

Not an issue.
But it is recommended to rebuild your indexes at least once in a week for better performance of your database.

>> The process of taking the backup is about 40 mins, restoring is about 25 mins and deframenting may take close to an hour.

Ok, in that case,

1. Take a full backup before the day of migration and restore it in your new database along with creation of all logins, jobs, packages, etc.,
2. On the day of Migration, create a diff backup in source server and restore it in Target server with Recovery mode.
Before that you can set database to be in NORECOVERY mode by restoring the Full backup with NORECOVERY mode.
3. Restore your DIFF backup with Recovery mode to bring database up.
4. Rebuild all indexes using the simple script
sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80)'
5. Above Rebuild index script will also update the Statistics of tables.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Also please educate me on the updating statistics, what is the importance and how do u do that.

Once you rebuilt your indexes, statistics would be up to date and hence Query optimizer can choose the correct/ appropriate index and hence your queries can perform much better.
Detailed information can be found here:
http://msdn.microsoft.com/en-us/library/ms190397.aspx
0
 
Pradeep DubeyConsultantCommented:
Best solution is to take a new backup, you can use the zenith BDR backup technology, Its reliable. As they use shadowprotect you can go for the shadow protect also.
In this backup solution you can simply take a full backup and then restore it. It takes VSS backup so all data will be in to the full backup.

Best part is easy to restore the database. just connect to the other server and do the restore. I can help you with that.

Also there is no downtime for this migration. You may suffer with new databaase sync. but no problem you can take another VSS backup using this and restore it to the new server.

Its very easy.
Let me know if you need more help on shadow protect.
0
 
ackimcAuthor Commented:
Does the differential backup restore change some things in the database? I have an automated index rebuilding script failing when we do the differential. If we do the full restoration, its works well, but not with the differential. Now we are worried that there is possibly more thing it changes, any idea?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Does the differential backup restore change some things in the database?

Yes, whatever changes that are captured in Differential backup will be changed..

>> I have an automated index rebuilding script failing when we do the differential. If we do the full restoration, its works well, but not with the differential.

Running Backup/Restore and Index rebuilding should not be done in parallel.
Since Differential will try to place a lock on the tables for data changes, it will block your Index rebuild activities.
Whereas Full backup Restore will place an exclusive lock on your database blocking your Index rebuild till restore is completed so that you don't receive errors at that time.

>> Now we are worried that there is possibly more thing it changes, any idea?

No need to worry about the changes as Differential backup will hold only incremental changes after your Full backup..
0
 
ackimcAuthor Commented:
>>Running Backup/Restore and Index rebuilding should not be done in parallel.
Since Differential will try to place a lock on the tables for data changes, it will block your Index rebuild activities.
Whereas Full backup Restore will place an exclusive lock on your database blocking your Index rebuild till restore is completed so that you don't receive errors at that time.

Am not sure what you mean by the above statement. Here is what is happening, When we do a full restoration, the re-indexing utility that gives us the reports works well, however when we restore in no recovery mode and later do the differential, the re-indexing maintenance tool fails when you run it after the differential has been restored. We are not running the differential restore in parallel while rebuilding the index. We wait for the differential to finish then we run the tool.  We need to use this tool immediately we finish the differential restore so that we can rebuild indexes and review the report.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok, let me explain this in more detail..
When you are trying to restore your Differential backup, you will

1. Make your database to No Recovery mode - By doing this, your database is inaccessible to others. Hence your index rebuilding utility or any other activity will fail.
2. Restore your Differential backup with Recovery mode to bring it online - Now your index rebuilding utility can work without any issues.

If the index rebuilding is done after your Differential backup restore is completed, then ideally you should not be having any issues. Can you confirm this by doing a profiler trace to identify the events happening in SQL Server.

For a full backup restore, for a small fraction of time, it will place an exclusive lock on the database to do the restore.
Since there is an exclusive lock, index rebuilding utility will not fail whereas it will wait for the restore operation to complete..
0
 
ackimcAuthor Commented:
Hi see the error we are getting when we run the script after the differential restore.

"Could not find database 'dbo'. The database either does not exist, or was dropped before a statement tried to use it. Verify if the database exists by querying the sys.databases catalog view"

Any idea?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Verify if the database exists by querying the sys.databases catalog view

As I mentioned earlier in my earlier comment:

1. Make your database to No Recovery mode - By doing this, your database is inaccessible to others. Hence your index rebuilding utility or any other activity will fail.

Your database is inaccessible and hence you are receiving the above error.
0
 
ackimcAuthor Commented:
That is the strange part, when restoring the differential, we set the Db to recovery and actually other applications that access the DB are working fine. We are also able to query and update the database. The only thing we have picked that is not working so far is the re-indexing tool. And we are worried that there could be other things that may not be working.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> when restoring the differential, we set the Db to recovery

A small correction.. it is set to NO Recovery mode.

>> actually other applications that access the DB are working fine. We are also able to query and update the database.

I think you need to check it once again..
Kindly check it yourself by querying your database when it is set to NO Recovery mode and it would produce the same error you specified above.
0
 
ackimcAuthor Commented:
I have done some work and thanks to you for telling me to check the process myself.

Here are my findings;
1. The re-indexing tool which comes with the application works with a fresh DB setup when the application is installed afresh. If you do the full restoration from another machine, the tool gives the above error. So the problem has got nothing to do with the incremental restoration neither has it got anything to do the Db being in recovery or no recovery. Sorry for misleading you.

2. I suspect the error is coming from the restored DB from another machine (where we are trying to migrate from) which also has a different name from the destination machine (Which is a better server). I also notice slight differences in the way the users are on the new server and the original server after restoration. Please help, see below the procedure for re-indexing.

 Below is the procedure we are running;
========================================================================
     USE [master]
GO
/****** Object:  StoredProcedure [dbo].[pm_IdxMaintenance]    Script Date: 05/28/2011 08:36:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[pm_IdxMaintenance]
(
      --disables all index rebuilding, forcing only defragmentation to occur.
      @disable_index_rebuild      INT      =      1,
      @verbosity            INT   =  0 -- include date/time and progress info.
)
AS
BEGIN
      -----------------------------------------------------------------------------
      --      This stored procedure performs a selective online index defrag on all   --
      --      indexes in Postilion databases (databases named 'post*') which have a       --
      --      high fragmentation > 10% or a low logical density < 90%.                                    --
      --                                                                                                                                                 --
      --      After defragmentation, tables are re-analyzed, and all non OLTP tables  --
      --      that still have high fragmentation > 15% or low logical density < 85%   --
      --      have their indexes rebuilt to ensure optimal performance and stability. --
      --                                                                                                                                                      --
      --      This procedure can be terminated at any point in the process, and any       --
      --      completed work is retained.                                                                                          --
      -----------------------------------------------------------------------------
      SET XACT_ABORT ON
      SET NOCOUNT ON

      DECLARE @dbname                                     SYSNAME
      DECLARE @scan_density                        INT
      DECLARE @logical_fragmentation      INT

      SET @scan_density                               = 90
      SET @logical_fragmentation             = 10

      CREATE TABLE #databases(
            rid                                    int identity (1,1) PRIMARY KEY CLUSTERED ,
            dbname                               sysname,
            dbsize                              nvarchar(128),
            dbowner                              nvarchar(256),
            id                                          int,
            created                              nvarchar(11),
            dbstatus                              nvarchar(600),
            compatibility_level       int
      )

      CREATE TABLE #tables(
            rid       int identity (1,1) PRIMARY KEY CLUSTERED ,
            tabid int,
            name       sysname
      )

      CREATE TABLE #indexes(
            rid       int identity (1,1) PRIMARY KEY CLUSTERED ,
            indid int,
            name       sysname
      )

      CREATE TABLE #pm_fragmentation(
            fid                                     int       IDENTITY (1, 1) PRIMARY KEY CLUSTERED      NOT NULL ,
            Date                                     datetime default getdate(),
            ObjectName                         sysname                                     NOT NULL ,
            ObjectId                         int                                           NULL ,
            IndexName                         sysname                                     NOT NULL ,
            IndexId                               int                                           NULL ,
            Level                               int                                           NULL ,
            Pages                               int                                           NULL ,
            Rows                                     int                                           NULL ,
            MinimumRecordSize            int                                           NULL ,
            MaximumRecordSize       int                                           NULL ,
            AverageRecordSize       float                                          NULL ,
            ForwardedRecords             int                                           NULL ,
            Extents                               int                                           NULL ,
            ExtentSwitches             int                                           NULL ,
            AverageFreeBytes             float                                     NULL ,
            AveragePageDensity       float                                     NULL ,
            ScanDensity                   float                                     NULL ,
            BestCount                         int                                           NULL ,
            ActualCount                   int                                           NULL ,
            LogicalFragmentation float                                     NULL ,
            ExtentFragmentation       float                                     NULL ,
            DBName                               sysname                                     NULL ,
            PrePost                               varchar (20)                         NULL
      )

      CREATE TABLE #reindex(
            rid                   int identity (1,1) PRIMARY KEY CLUSTERED ,
            ObjectName       sysname,
            IndexName       sysname
      )

      -----------------------------------------------------------------------------
      --                               Truncate the necessary reporting tables.                                    --
      -----------------------------------------------------------------------------
      TRUNCATE TABLE pm_fragmentation
      TRUNCATE TABLE pm_idx_rebuild
      TRUNCATE TABLE pm_skipped_tables

      DECLARE @dbcount int,
            @numdatabases            int,
            @numtables                   int,
            @numindexes             int,
            @numreindexes             int,
            @tabcount                   int,
            @indcount                   int,
            @recount                   int,
            @currtable                   int,
            @tabname                   sysname,
            @currind                   int,
            @indname                   sysname,
            @dynamic_sql            varchar(8000),
            @skip_table                  int

      -----------------------------------------------------------------------------
      -- Find all Postilion databases, including PostCard and Office                         --
      -----------------------------------------------------------------------------
      INSERT INTO #databases
      (
            dbname,
            dbsize,
            dbowner,
            id,
            created,
            dbstatus,
            compatibility_level
      )
            EXEC sp_helpdb

      -- Delete non relevant databases
      DELETE FROM #databases WHERE dbname NOT LIKE 'post%'

      SET @dbcount = 1
      SELECT @numdatabases = max(rid) FROM #databases

      WHILE @dbcount <= @numdatabases
      BEGIN
            TRUNCATE TABLE #tables
            TRUNCATE TABLE #reindex
            TRUNCATE TABLE #indexes
            TRUNCATE TABLE #pm_fragmentation

            SELECT @dbname = dbname FROM #databases WHERE rid = @dbcount

            SET @dynamic_sql =
                  'SELECT id, ltrim(rtrim(su.name + ''.'' + so.name)) ' +
                  ' FROM [' + @dbname + '].dbo.sysobjects so INNER JOIN ['
                  + @dbname + '].dbo.sysusers su ON su.uid = so.uid ' +
                  'WHERE so.xtype = ''U'' AND so.name <> ''dtproperties'''

            INSERT INTO #tables(tabid, name)
                  EXEC (@dynamic_sql)

            SET             @tabcount = 1
            SELECT       @numtables = max(rid) FROM #tables

            WHILE @tabcount <= @numtables
            BEGIN
                  SET @indcount = 1

                  SELECT
                        @currtable = tabid,
                     @tabname = ltrim(rtrim(name))
                  FROM
                        #tables
                  WHERE
                        rid = @tabcount

                  SET @dynamic_sql = 'USE [' + @dbname +
                        '] DBCC SHOWCONTIG (''' + @tabname + ''') WITH ALL_INDEXES,'  +
                        ' TABLERESULTS'

                  INSERT INTO #pm_fragmentation(ObjectName, ObjectId, IndexName, IndexId, Level, Pages, Rows, MinimumRecordSize, MaximumRecordSize, AverageRecordSize, ForwardedRecords, Extents, ExtentSwitches, AverageFreeBytes, AveragePageDensity, ScanDensity, BestCount, ActualCount, LogicalFragmentation, ExtentFragmentation)
                        EXEC(@dynamic_sql)

                  -- This ensures that we don't look at system indexes
                  DELETE FROM
                        #pm_fragmentation
                  WHERE
                        IndexID IN (0,255)

                  UPDATE
                        #pm_fragmentation
                  SET
                        PrePost = 'PRE'
                  WHERE
                        PrePost is NULL

                  -- System generated stats are named with _WA in their names.
                  -- TODO: Is there a better way to identify system stats?
                  INSERT #indexes(indid, name)
                        EXEC ('SELECT indid, name FROM [' + @dbname + '].dbo.sysindexes WHERE id = ' + @currtable + ' AND name not like ''_WA%'' AND indid NOT IN (0, 255) AND first IS NOT NULL')

                  SELECT @numindexes = max(rid) FROM #indexes

                  -------------------------------------------------------------
                  -- Check whether we have excluded this table from the list --
                  -------------------------------------------------------------
                  SET @skip_table = 0

                  SELECT
                        @skip_table = 1
                  FROM
                        pm_idx_excluded_tables
                  WHERE
                        @dbname LIKE DatabaseName AND
                        @tabname LIKE 'dbo.' + TableName   AND
                        DisallowIndexDefrag = 1

                  IF (@skip_table = 0 AND @dbname = 'postcard')
                  BEGIN
                        -- Check whether its an inactive table

                        SELECT
                              @skip_table = 1
                        FROM
                              postcard..pc_issuers
                        WHERE
                              @tabname = 'pc_cards_' + CAST(issuer_nr AS VARCHAR) + '_' +
                              CASE
                                    current_table_set_cards
                              WHEN
                                    'A' THEN 'B'
                              ELSE
                                    'A'
                              END


                        --current_table_set_accounts
                        SELECT
                              @skip_table = 1
                        FROM
                              postcard..pc_issuers
                        WHERE
                              @tabname =
                              'pc_accounts_' + CAST(issuer_nr AS VARCHAR) + '_' +
                              CASE
                                    current_table_set_accounts
                              WHEN
                                    'A' THEN 'B'
                              ELSE
                                    'A'
                              END

                        --current_table_set_cardaccounts
                        SELECT
                              @skip_table = 1
                        FROM
                              postcard..pc_issuers
                        WHERE
                              @tabname =
                              'pc_card_accounts_' + CAST(issuer_nr AS VARCHAR) + '_' +
                              CASE
                                    current_table_set_cardaccounts
                              WHEN
                                    'A' THEN 'B'
                              ELSE
                                    'A'
                              END

                        --current_table_set_accountbalances
                        SELECT
                              @skip_table = 1
                        FROM
                              postcard..pc_issuers
                        WHERE
                              @tabname =
                              'pc_account_balances_' + CAST(issuer_nr AS VARCHAR) + '_' +
                              CASE
                                    current_table_set_accountbalances
                              WHEN
                                    'A' THEN 'B'
                              ELSE
                                    'A'
                              END

                        --current_table_set_statements
                        SELECT
                              @skip_table = 1
                        FROM
                              postcard..pc_issuers
                        WHERE
                              @tabname =
                              'pc_statements_' + CAST(issuer_nr AS VARCHAR) + '_' +
                              CASE
                                    current_table_set_statements
                              WHEN
                                    'A' THEN 'B'
                              ELSE
                                    'A'
                              END

                        --current_table_set_accountoverridelimits
                        SELECT
                              @skip_table = 1
                        FROM
                              postcard..pc_issuers
                        WHERE
                              @tabname =
                              'pc_account_override_limits_' + CAST(issuer_nr AS VARCHAR) + '_' +
                              CASE
                                    current_table_set_accountoverridelimits
                              WHEN
                                    'A' THEN 'B'
                              ELSE
                                    'A'
                              END

                        --current_table_set_cardoverridelimits
                        SELECT
                              @skip_table = 1
                        FROM
                              postcard..pc_issuers
                        WHERE
                              @tabname =
                              'pc_card_override_limits_' + CAST(issuer_nr AS VARCHAR) + '_' +
                              CASE
                                    current_table_set_cardoverridelimits
                              WHEN
                                    'A' THEN 'B'
                              ELSE
                                    'A'
                              END

                        --current_table_customers
                        SELECT
                              @skip_table = 1
                        FROM
                              postcard..pc_issuers
                        WHERE
                              @tabname =
                              'pc_customers_' + CAST(issuer_nr AS VARCHAR) + '_' +
                              CASE
                                    current_table_customers
                              WHEN
                                    'A' THEN 'B'
                              ELSE
                                    'A'
                              END

                        --current_table_cust_accounts
                        SELECT
                              @skip_table = 1
                        FROM
                              postcard..pc_issuers
                        WHERE
                              @tabname =
                              'pc_customer_accounts_' + CAST(issuer_nr AS VARCHAR) + '_' +
                              CASE
                                    current_table_cust_accounts
                              WHEN
                                    'A' THEN 'B'
                              ELSE
                                    'A'
                              END
                  END

                  IF (ISNULL(@skip_table,0) = 1)
                  BEGIN
                        INSERT INTO
                              pm_skipped_tables (DatabaseName, TableName)
                        VALUES
                              (@dbname, @tabname)
                  END

                  --DEBUG LOGGING
                  IF @verbosity > 0
                        IF (@skip_table = 0)
                              PRINT CONVERT(VARCHAR(23), GETDATE(), 121) + ' ' +
                                    'Online Defrag Starting for object: [' + @dbname + '].dbo.' + @tabname
                        ELSE
                              PRINT CONVERT(VARCHAR(23), GETDATE(), 121) + ' ' +
                                    'Online Defrag Skipped for object: [' + @dbname + '].dbo.' + @tabname

                  -----------------------------------------------------------------------
                  -- Perform an Online Defragmentation on the Indexes (non-blocking)   --
                  -----------------------------------------------------------------------
                  WHILE @indcount <= @numindexes AND @skip_table = 0
                  BEGIN
                        SELECT
                              @currind = indid,
                           @indname = ltrim(rtrim(name))
                        FROM
                              #indexes
                        WHERE
                              rid = @indcount

                        SET @dynamic_sql = 'DBCC INDEXDEFRAG (''' + @dbname + ''',''' + @tabname + ''',''' + @indname + ''') WITH NO_INFOMSGS '

                        --DEBUG LOGGING
                        IF @verbosity > 1
                              PRINT CONVERT(VARCHAR(23), GETDATE(), 121) + ' ' +
                                    'Database:' + @dbname + ': Stmt Starting:' + @dynamic_sql

                        EXEC (@dynamic_sql)

                        --DEBUG LOGGING
                        IF @verbosity > 1
                              PRINT CONVERT(VARCHAR(23), GETDATE(), 121) + ' ' +
                                    'Database:' + @dbname + ': Stmt Completed.'

                        SET @indcount = @indcount + 1

                   END

                  SET @dynamic_sql = 'USE [' + @dbname + '] DBCC SHOWCONTIG (''' + @tabname + ''') WITH ALL_INDEXES, TABLERESULTS'

                  INSERT INTO #pm_fragmentation(ObjectName, ObjectId, IndexName, IndexId, Level, Pages, Rows, MinimumRecordSize, MaximumRecordSize, AverageRecordSize, ForwardedRecords, Extents, ExtentSwitches, AverageFreeBytes, AveragePageDensity, ScanDensity, BestCount, ActualCount, LogicalFragmentation, ExtentFragmentation)
                        EXEC(@dynamic_sql)

                  DELETE FROM
                        #pm_fragmentation
                  WHERE
                        IndexID IN (0,255)

                  UPDATE
                        #pm_fragmentation
                  SET
                        PrePost = 'POST'
                  WHERE
                        PrePost is NULL

                  SET @tabcount = @tabcount + 1
                  TRUNCATE TABLE #indexes
            END

            --DEBUG LOGGING
             IF @verbosity > 0
                  PRINT CONVERT(VARCHAR(23), GETDATE(), 121) + ' ' +
                        'Database:' + @dbname + ': Online Defragmentation Complete'

            IF @disable_index_rebuild = 0
            BEGIN
                  -----------------------------------------------------------------------
                  -- Perform a REINDEX on indices that still have a high fragmentation --
                  -- after defragmentation.                                                                                          --
                  --                                                                                                                                          --
                  -- We skip tables with the following naming convention, to avoid             --
                  -- locking the OLTP tables: tm_%, --pc_%, hc_%                                                      --
                  -----------------------------------------------------------------------

                  INSERT INTO #reindex(ObjectName,IndexName)
                        SELECT
                              #tables.name, #pm_fragmentation.IndexName
                        FROM
                              #pm_fragmentation
                        INNER JOIN
                              #tables on #tables.tabid = #pm_fragmentation.objectid
                        WHERE
                              #pm_fragmentation.IndexId NOT IN (0, 255)
                              AND
                              (
                                    #pm_fragmentation.ScanDensity < @scan_density - 5 OR
                                    #pm_fragmentation.LogicalFragmentation > @logical_fragmentation + 5
                              )
                              AND #pm_fragmentation.PrePost = 'POST'
                              AND #tables.name NOT LIKE 'dbo.tm_%'
                              --AND #tables.name NOT LIKE 'dbo.pc_%'
                               AND #tables.name NOT LIKE 'dbo.hc_%'
                               AND #tables.name NOT IN
                               (
                                     SELECT
                                           'dbo.' + TableName
                                     FROM
                                           master.dbo.pm_idx_excluded_tables
                                     WHERE
                                           DatabaseName LIKE @dbname AND
                                           DisallowIndexRebuild = 1
                               )

                  -----------------------------------------------------------------------
                  -- Flag tables skipped tables for manual re-indexing
                  -----------------------------------------------------------------------
                  INSERT INTO dbo.pm_idx_rebuild(ObjectName, IndexName, DatabaseName , ScanDensity, LogicalFragmentation)
                        SELECT
                              #tables.name, #pm_fragmentation.IndexName, @dbname, #pm_fragmentation.ScanDensity, #pm_fragmentation.LogicalFragmentation
                        FROM
                              #pm_fragmentation
                        INNER JOIN
                              #tables on #tables.tabid = #pm_fragmentation.objectid
                        WHERE
                              #pm_fragmentation.IndexId NOT IN (0, 255)
                              AND
                              (
                                    #pm_fragmentation.ScanDensity < @scan_density - 5 OR
                                    #pm_fragmentation.LogicalFragmentation > @logical_fragmentation + 5
                              )
                              AND #pm_fragmentation.PrePost = 'POST'
                              AND
                              (
                                    #tables.name LIKE 'dbo.tm_%' OR
                                    --#tables.name LIKE 'dbo.pc_%' OR
                                     #tables.name LIKE 'dbo.hc_%' OR
                                     #tables.name IN
                                     (
                                           SELECT 'dbo.' +
                                                 TableName
                                           FROM
                                                 master.dbo.pm_idx_excluded_tables
                                           WHERE
                                                 DatabaseName = @dbname AND
                                                 DisallowIndexRebuild = 1
                                     )
                               )

                  SELECT @numreindexes = max(rid) FROM #reindex

                  --DEBUG LOGGING
                   IF @verbosity > 0
                        PRINT CONVERT(VARCHAR(23), GETDATE(), 121) + ' ' +
                              'Database:' + @dbname + ': Re-Indexing Starting'

                  SET @recount = 1

                  WHILE @recount <= @numreindexes
                  BEGIN
                        SELECT
                              @tabname = ObjectName,
                           @indname = IndexName
                        FROM
                              #reindex
                        WHERE
                              rid = @recount

                        SET @dynamic_sql = 'DBCC DBREINDEX(''' + @dbname + '.' + @tabname + ''',''' + @indname + ''') WITH NO_INFOMSGS '

                        --DEBUG LOGGING
                        IF @verbosity > 1
                              PRINT CONVERT(VARCHAR(23), GETDATE(), 121) + ' ' +
                                    'Database:' + @dbname + ': Stmt Starting:' + @dynamic_sql

                        EXEC (@dynamic_sql)

                        --DEBUG LOGGING
                        IF @verbosity > 1
                              PRINT CONVERT(VARCHAR(23), GETDATE(), 121) + ' ' +
                                    'Database:' + @dbname + ' Stmt Completed.'

                        SET @dynamic_sql = 'USE [' + @dbname + '] DBCC SHOWCONTIG (''' + @tabname + ''',''' + @indname + ''') WITH TABLERESULTS'

                        INSERT INTO #pm_fragmentation(ObjectName, ObjectId, IndexName, IndexId, Level, Pages, Rows, MinimumRecordSize, MaximumRecordSize, AverageRecordSize, ForwardedRecords, Extents, ExtentSwitches, AverageFreeBytes, AveragePageDensity, ScanDensity, BestCount, ActualCount, LogicalFragmentation, ExtentFragmentation)
                              EXEC(@dynamic_sql)

                        SET @recount = @recount + 1
                  END

                  UPDATE
                        #pm_fragmentation
                  SET
                        PrePost = 'REINDEXED'
                  WHERE
                        PrePost is NULL

                  --DEBUG LOGGING
                   IF @verbosity > 0
                        PRINT CONVERT(VARCHAR(23), GETDATE(), 121) + ':' +
                              'Database:' + @dbname + ':       Re-Indexing Complete'

            END       -- IF @disable_index_rebuild = 0
            ELSE
            BEGIN
                  -----------------------------------------------------------------------
                  -- Flag tables skipped tables for manual re-indexing
                  -----------------------------------------------------------------------
                  INSERT INTO dbo.pm_idx_rebuild(ObjectName, IndexName, DatabaseName , ScanDensity, LogicalFragmentation)
                        SELECT
                              #tables.name, #pm_fragmentation.IndexName, @dbname, #pm_fragmentation.ScanDensity, #pm_fragmentation.LogicalFragmentation
                        FROM
                              #pm_fragmentation
                        INNER JOIN
                              #tables on #tables.tabid = #pm_fragmentation.objectid
                        WHERE
                              #pm_fragmentation.IndexId NOT IN (0, 255)
                              AND
                                    (
                                    #pm_fragmentation.ScanDensity < @scan_density - 5 OR
                                    #pm_fragmentation.LogicalFragmentation > @logical_fragmentation + 5
                                    )
                              AND #pm_fragmentation.PrePost = 'POST'

            END       -- IF @disable_index_rebuild = 1

            UPDATE #pm_fragmentation SET DBName = @dbname

            INSERT INTO master.dbo.pm_fragmentation(Date, ObjectName, ObjectId, IndexName, IndexId, Level, Pages, Rows, MinimumRecordSize, MaximumRecordSize, AverageRecordSize, ForwardedRecords, Extents, ExtentSwitches, AverageFreeBytes, AveragePageDensity, ScanDensity, BestCount, ActualCount, LogicalFragmentation, ExtentFragmentation, DBName, PrePost)
                  SELECT Date, ObjectName, ObjectId, IndexName, IndexId, Level, Pages, Rows, MinimumRecordSize, MaximumRecordSize, AverageRecordSize, ForwardedRecords, Extents, ExtentSwitches, AverageFreeBytes, AveragePageDensity, ScanDensity, BestCount, ActualCount, LogicalFragmentation, ExtentFragmentation, DBName, PrePost FROM #pm_fragmentation

            SET @dbcount =  @dbcount + 1

      END -- while: there are more postilion databases

      -----------------------------------------------------------------------------
      -- Post Process the pm_fragmentation table                                                               --
      -- We want to overwrite 'POST' values with the values from 'REINDEXED'         --
      -----------------------------------------------------------------------------
      UPDATE pm_fragmentation
      SET
            [DBName]                                    = f2.DBName,
            [ObjectId]                              = f2.ObjectId,
            [IndexName]                              = f2.IndexName,
            [Date]                                      = f2.Date,
            [Level]                                     = f2.Level,
            [Pages]                                     = f2.Pages,
            [Rows]                                    = f2.Rows,
            [MinimumRecordSize]             = f2.MinimumRecordSize,
            [MaximumRecordSize]             = f2.MaximumRecordSize,
            [AverageRecordSize]             = f2.AverageRecordSize,
            [ForwardedRecords]            = f2.ForwardedRecords,
            [Extents]                              = f2.Extents,
            [ExtentSwitches]                  = f2.ExtentSwitches,
            [AverageFreeBytes]            = f2.AverageFreeBytes,
            [AveragePageDensity]            = f2.AveragePageDensity,
            [ScanDensity]                        = f2.ScanDensity,
            [BestCount]                              = f2.BestCount,
            [ActualCount]                        = f2.ActualCount,
            [LogicalFragmentation]      = f2.LogicalFragmentation,
            [ExtentFragmentation]        = f2.ExtentFragmentation
      FROM
            pm_fragmentation, pm_fragmentation f2
      WHERE
            pm_fragmentation.DBName       = f2.DBName      AND
            pm_fragmentation.IndexName = f2.IndexName      AND
            pm_fragmentation.PrePost      = 'POST' AND
            f2.PrePost                                     = 'REINDEXED'

      DELETE FROM pm_fragmentation WHERE PrePost = 'REINDEXED'

      -----------------------------------------------------------------------------
      --      END: Post Processing
      -----------------------------------------------------------------------------

      DROP TABLE #tables
      DROP TABLE #indexes
      DROP TABLE #pm_fragmentation
      DROP TABLE #reindex
      DROP TABLE #databases

      SET NOCOUNT OFF
      SET XACT_ABORT OFF
END
==============================================================================

The error is
"Msg 2520, Level 16, State 5, Line 1
Could not find database 'dbo'. The database either does not exist, or was dropped before a statement tried to use it. Verify if the database exists by querying the sys.databases catalog view."

I will provide any more information you may need, am getting too much pressure.



0
 
ackimcAuthor Commented:
Hi rrjegan17,

Please help on the above, Any clues?

Thanks in advance.


0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
ackimc,

Sorry for the delay as I got stuck in personal works.
Will reply to your shortly..
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Could not find database 'dbo'. The database either does not exist, or was dropped before a statement tried to use it. Verify if the database exists by querying the sys.databases catalog view."

Have gone through the script provided above and seems like it is created for SQL Server 2000 where Schema names are defaulted as dbo.
For eg., If your table name is Schema_name.table_name it will now look like dbo.table_name.
Since that is an invalid object, it errors out and hence your procedure is failing in SQL Server 2005.
Since you are working in SQL Server 2005, I would suggest using this script to do re-indexing based upon Index Statistics available in "D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes" section of the link below

http://msdn.microsoft.com/en-us/library/ms188917.aspx

Or using this procedure:

http://ola.hallengren.com/scripts/IndexOptimize.sql

Kindly let me know if you need more information.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now