Search code examples
sqlsql-serverdatabase-partitioning

Partition history SQL Server

I'm using SQL Server 2014, is there any possible way to see the history of the partition?

Somehow the partition schema changed and i would like to know how did it happen...

Solution

  • Schema changes get logged to the default trace and should appear in the Schema Changes History report (as long as it is enabled and hasn't rolled over in the meantime).

    To view this report in SSMS, right click the database in consideration and choose 

    Reports -> Standard Reports -> Schema Changes History

    To do the same with TSQL,

    SELECT StartTime
       ,LoginName
       --,f.*
FROM   sys.traces t
       CROSS APPLY fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path),
                                                       CHARINDEX('\', REVERSE(t.path)), 
                                                       260)
                                             ) + N'log.trc', DEFAULT) f
WHERE  t.is_default = 1
       AND ObjectName = 'FOO'
       AND EventClass IN (46, /*Object:Created*/
                          47, /*Object:Dropped*/
                          164 /*Object:Altered*/ )

    For more information refer - Schema Changes History Report