Post reply

Query to find tables with Forwarded Records

  • May 12, 2016 at 3:23 pm

    #400154

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

    Regards,
    Ajay Dwivedi
    MCP SQL DBA

  • May 23, 2016 at 1:06 pm

    #1880874

    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.

  • May 23, 2016 at 9:44 pm

    #1880937

    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:-

    SELECTDB_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

    FROMsys.dm_db_index_physical_stats

    (

    DB_ID('msdb')

    ,OBJECT_ID('dbo.Suspect_Pages')

    ,NULL

    ,NULL

    ,'DETAILED'

    ) AS FWD_TBL

    WHEREFWD_TBL.forwarded_record_count IS NOT NULL

    ANDFWD_TBL.forwarded_record_count <> 0

    Regards,
    Ajay Dwivedi
    MCP SQL DBA

  • June 9, 2016 at 7:46 am

    #1884102

    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'.

    SELECTDB_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

    FROMsys.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]

  • June 16, 2016 at 7:50 am

    #1885398

    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) A socialist is someone who will give you the shirt off *someone else's* back.

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

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