Query to find tables with Forwarded Records

Question

Post reply

Query to find tables with Forwarded Records

ajay.dwivedi2007

SSC-Addicted

Points: 488
More actions
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

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

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 1

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)

Thanks.

ajay.dwivedi2007 SSC-Addicted Points: 488 More actions · Answer 2

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

,'DETAILED'

) AS FWD_TBL

WHEREFWD_TBL.forwarded_record_count IS NOT NULL

ANDFWD_TBL.forwarded_record_count <> 0

Regards,
Ajay Dwivedi
MCP SQL DBA

MadAdmin SSChampion Points: 11261 More actions · Answer 3

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

,'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: 101095 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".