An example that creates the mymsgs table from the pubs2 database, specifying text rather than varchar for the description column in preparation for migrating the column’s contents from off-row to in-row storage.
1> use pubs2 2> go 1> exec sp_drop_object mymsgs, 'table' 2> go 1> create table mymsgs ( error int not null , severity smallint not null , dlevel smallint not null , description text , langid smallint null , sqlstate varchar (5) null ) lock datarows 2> go 1> insert mymsgs select * from master..sysmessages 2> go
(9564 rows affected)
1> exec sp_spaceusage display, 'table', mymsgs 2> go
All the page counts in the result set are in the unit 'KB'. OwnerName TableName IndId NumRows UsedPages RsvdPages ExtentUtil ExpRsvdPages PctBloatUsedPages PctBloatRsvdPages --------- --------- ----- ------- --------- --------- ---------- ------------ ---------------------- ------------------ dbo mymsgs 0 9564.0 372.0 384.0 96.87 320.0 16.25 20.00 dbo mymsgs 255 NULL 19132.0 19140.0 99.95 19136.0 00 0.02
1> use pubs2 1> 2> dump tran pubs2 with no_log 1> 2> /* Drop the spaceusage stats table before each run */ 3> exec sp_drop_object spaceusage_object, 'table'
Dropping table spaceusage_object (return status = 0)
1> 2> exec sp_spaceusage archive, 'table', mymsgs
Data was successfully archived into table 'pubs2.dbo.spaceusage_object'. (return status = 0)