table with zero rows showing data size

Question

table with zero rows showing data size

pmadhavapeddi22

SSCertifiable

Points: 5306
More actions
May 29, 2014 at 3:57 am

#286022

Hi all,
Today, I have found a situation like the below.
Table has zero rows but when i have run sp_spaceused for this table I got the below info
table name:dead_lock_info
rows:0
reserved:3309448 KB
data:3275768 KB
index_size:15824 KB
unused:17856 KB
What is wrong on this table. why it is showing data size so high ? Your help is appreciated
FYI.
the above table is used to track any deadlocks occurred on the server. We keep clearing the data. As of now
we have zero rows in the table

Viewing 12 posts - 1 through 11 (of 11 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Does the table have a clustered index?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 2

yes Gail,

please find the create table syntax for that table

CREATE TABLE [dead_lock_info](

[Dead_Lock_Info_Id] [bigint] IDENTITY(1,1) NOT NULL,

[LogDate] [datetime] NULL,

[ProcessInfo] [varchar](30) NULL,

[Dead_Lock_Log] [varchar](max) NULL,

CONSTRAINT [pk_dead_lock_info] PRIMARY KEY CLUSTERED

(

[Dead_Lock_Info_Id] ASC

)

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 3

Run this script:

SELECT t.name,

i.index_id,

i.type_desc,

i.name,

p.data_compression_desc,

p.rows,

au.type_desc,

au.total_pages,

au.used_pages,

au.data_pages

FROM sys.tables t

JOIN sys.indexes i ON t.object_id = i.object_id

JOIN sys.partitions p ON i.object_id = p.object_id

AND i.index_id = p.index_id

JOIN sys.allocation_units au ON p.partition_id = au.container_id

WHERE t.name = 'dead_lock_info'

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 4

please find the results as below

nameindex_idtype_descnamerowstype_desctotal_pagesused_pagesdata_pages

dead_lock_info1CLUSTEREDpk_dead_lock_info0LOB_DATA000

dead_lock_info1CLUSTEREDpk_dead_lock_info0IN_ROW_DATA411713407826405853

my apologies, I have to run your script in 2005 version, so I have no data about data_compression_desc

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 5

SQL Guy 1

SSCoach

Points: 15979

May 29, 2014 at 9:15 am

#1717386

Run this:

DBCC CHECKTABLE('dead_lock_info')

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 6

Also try this:

DBCC UPDATEUSAGE ('your_database_name','dead_lock_info')

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Your [Dead_Lock_Log] [varchar](max) probably had some off-row data (blobs). If you recently deleted the records, the size information can lag behind due to the blob info.

Have you run a rebuild of your clustered index?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 8

Please find the results after dbcc checktable

DBCC results for 'dead_lock_info'.

There are 0 rows in 405853 pages for object "dead_lock_info".

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 9

Did you have a chance to run DBCC updateusage ('db','table') ?

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 10

I have executed DBCC UPDATEUSAGE ('db_name','dead_lock_info')

but the rebuilding the clustered index did the trick,

Now I have data, index_size everything zero

Thank you all for your support

Jason,

when I have run the query before rebuilding it showed in-row data, but i dont know about off-row data though

but after rebuild, every thing is correct

thanks for the support

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

You are welcome.

fwiw - here is a script to help see the table and index sizes a little more clearly.

http://bit.ly/tablespace

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events