SQL Server Performance

data comaprison - SQL server 2008 database

Discussion in 'SQL Server 2008 General DBA Questions' started by AJITH123, Sep 5, 2010.

  1. AJITH123 Member

    Hello all,
    I have a migration project, some time I need to compare the databases (both source and target db are sql server 2008), since the data may got updated. The database size is 50 to 100 gb range. What is the best approach can be use for the data comparison? If i use, Redgate Data comparison tool, how long it will take for the mentioned db size?
    Thanks
    Ajith
  2. ashish287 New Member

    not so exact but you can compare the size of the table. As you mentioned database size is between 50-100gb, then I am sure there would be some 6-12 tables which are quiet big in size and you can compare the size of tables using below query. It will obviously not tell you that all the data are similar but with size you will get rough idea which table is out of sync. Script to be run in specific database which you want to compare:-
    --CREATE PROCEDURE GetAllTableSizes
    --AS
    /*
    Obtains spaced used data for ALL user tables in the database
    */
    DECLARE @TableName VARCHAR(100) --For storing values in the cursor
    --Cursor to get the name of all user tables from the sysobjects listing
    DECLARE tableCursor CURSOR
    FOR
    select [name]
    from dbo.sysobjects
    where OBJECTPROPERTY(id, N'IsUserTable') = 1
    FOR READ ONLY
    --A procedure level temp table to store the results
    CREATE TABLE #TempTable
    (
    tableName varchar(100),
    numberofRows int,
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
    )
    --Open the cursor
    OPEN tableCursor
    --Get the first table name from the cursor
    FETCH NEXT FROM tableCursor INTO @TableName
    --Loop until the cursor was not able to fetch
    WHILE (@@Fetch_Status >= 0)
    BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT #TempTable
    EXEC sp_spaceused @TableName
    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
    END
    --Get rid of the cursor
    CLOSE tableCursor
    DEALLOCATE tableCursor
    --Select all records so we can use the reults
    SELECT *
    FROM #TempTable order by numberofrows desc
    --Final cleanup!
    --DROP TABLE #TempTable
    GO
    hope this will help.
  3. Luis Martin Moderator

    I suggest to use Redgate tool and you can ask the same question (how long...) in RegGate Blogs.
    Anyway, should be fast.
  4. satya Moderator

    I second Redgate tools too, they are good, reliable and not-costly [:)]

Share This Page