4

My database size is huge, and recently I noticed that a new table added few months ago is the culprit.

Here is the table script.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Entry_tracker](
    [S.Number] [int] IDENTITY(1,1) NOT NULL,
    [EntryId] [varchar](50) NOT NULL,
    [EventNumber] [varchar](18) NOT NULL,
    [Data] [varbinary](max) NOT NULL,
    [TrackDateTime] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Entry_tracker] ADD  CONSTRAINT [DF_Entry_tracker_TrackDateTime]  DEFAULT (getdate()) FOR [TrackDateTime]
GO

I collected information on this table, and learned that the table has about 16 million rows and the table size is 1.4TB.enter image description here

Still, I think that the table size is huge for the no. of records.

This table is not queried by the application. It just stores different versions of same entries from another table.

I checked the fragmentation information and it shows 0 average fragmentation with 93% average space used. enter image description here

Since I am using SQL Server 2016, I though I could use table compression, so tried sp_estimate_data_compression_savings to estimate possible space savings. However, the results shows no savings.

size_with_current_compression_setting(KB) is equal to size_with_requested_compression_setting(KB)

Can anyone direct me in finding the issue with this table?

It is really important, as this is taking a lot of disk space.

Appreciate your help.

6

Still i think that the table size is huge for the no. of records.

Why? What makes you say this?

what it does is stores different version of same entry from another table.

So it holds versions, which could be large or small, especially given:

[Data] [varbinary](max) NOT NULL,

Have you checked the datalength() on the rows to see if you have some large rows in there eating space?

Seems to me, based on the data the size it is given the function seems reasonable. Versioning is rarely small unless it's just changed bytes and you use various algorithms to understand which bytes, how, and if it builds off previously changed versions or not.

  • 1
    Yeah.I checked the column Data and checked the data in there and calculated the size using below query.SELECT SUM(DATALENGTH(Data)) / 1048576.0 FROM Entry_tracker.It shows 1.3 TB.So it is really the data in there. – user9516827 Jun 14 at 15:41
  • @user9516827 - is the binary data already compressed? If not you may find COMPRESS and DECOMPRESS functions useful - though this would require code changes – Martin Smith Jun 14 at 15:54
  • i don't think that binary data is compressed.How do i check that.?Do i have to ask the application team how they are sending the data? – user9516827 Jun 14 at 16:09
  • 1
    The data, by default, is not compressed. You'll need to talk to the application team that owns this and see what they are doing with the data and how they are managing it. @user9516827 – Sean Gallardy Jun 14 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.