Query to find tables with Forwarded Records

  • ajay.dwivedi2007

    Old Hand

    Points: 348

    Comments posted to this topic are about the item Query to find tables with Forwarded Records

    Regards,
    Ajay Dwivedi
    MCP SQL DBA

  • SQL_Hunt

    SSC-Dedicated

    Points: 33264

    Getting the below error. Tested this in SQL 2014.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    Msg 2561, Level 16, State 10, Line 2

    Parameter 2 is incorrect for this statement.

    (0 row(s) affected)

    (0 row(s) affected)

    Thanks.

  • ajay.dwivedi2007

    Old Hand

    Points: 348

    Hi,

    I could not reproduce your error. But, still try below code. If it works (ie, you do not receive any error :-D), then perhaps you are not providing proper parameters in DMF [sys].[dm_db_index_physical_stats]

    Code to Try:-

    SELECT DB_NAME() as DbName,OBJECT_NAME(object_id) as TableName,index_id, index_type_desc, avg_fragmentation_in_percent,page_count, record_count, forwarded_record_count

    FROM sys.dm_db_index_physical_stats

    (

    DB_ID('msdb')

    ,OBJECT_ID('dbo.Suspect_Pages')

    ,NULL

    ,NULL

    ,'DETAILED'

    ) AS FWD_TBL

    WHERE FWD_TBL.forwarded_record_count IS NOT NULL

    AND FWD_TBL.forwarded_record_count <> 0

    Regards,
    Ajay Dwivedi
    MCP SQL DBA

  • MadAdmin

    SSChampion

    Points: 11260

    I got same error and then after fixing got the error of converting from int into tinyint.

    Here is a less iterative way that does not include global temp tables and nasty joins.

    Do cross apply to avoid using 'foreachtable'.

    SELECT DB_NAME(db_id()) as DbName,o.name as TableName,index_id, index_type_desc, avg_fragmentation_in_percent,page_count, record_count, forwarded_record_count

    FROM sys.objects o cross apply

    sys.dm_db_index_physical_stats

    (

    DB_ID()

    ,o.object_id

    ,NULL

    ,NULL

    ,'DETAILED'

    )

    where forwarded_record_count > 0 and o.is_ms_shipped =0

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • ScottPletcher

    SSC Guru

    Points: 98087

    Because sys.dm_db_index_physical_stats is so much overhead, you'll want to filter out non-heaps before you do the CROSS APPLY to that function.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply