SQL表不一致增长 [英] SQL table growing inconsistently

问题描述

有一个SQL表与其内部数据相比增长迅速且不一致.简而言之，此表中有一个Windows服务备份.txt文件的内容，文件的权重从1KB到45KB左右.因此nvarchar(max)列用于存储这些文本文件的内容.

There is a SQL table which is growing rapidly and inconsistently compared to it's intrinsic data. To make it short, there is a windows service backing up the content of .txt files in this table, the files weight from 1KB to 45KB approx. hence the nvarchar(max) column used to store the content of those text files.

在此表上运行sp_spaceused命令时，结果如下:

When running the sp_spaceused command on this table, here is the result:

name    rows    reserved       data      index_size   unused
Files   20402   814872 KB    813416 KB     1048 KB     408 KB

但是，当运行此简单查询时，该查询提供了该表使用的字节数据总量，结果不在附近:(97231108字节).

But when running this simple query, which gives me the total amount of data in bytes used by this table, the result is not anywhere near: (97231108 bytes).

SELECT (SUM(DATALENGTH(A)) +
        SUM(DATALENGTH(B)) +
        SUM(DATALENGTH(C)) +
        SUM(DATALENGTH(D)) +
        SUM(DATALENGTH(E)) +
        SUM(DATALENGTH(F)) +
        SUM(DATALENGTH(G)) + 
        SUM(DATALENGTH(H)) +
        SUM(DATALENGTH(I))) AS BytesUsed
FROM Files

RESULT: 97231108 bytes

此表的create语句如下:

The create statement for this table goes like this:

CREATE TABLE [dbo].[Files](
    [A] [int] IDENTITY(33515427,1) NOT NULL,
    [B] [nvarchar](100) NOT NULL,
    [C] [nvarchar](max) NOT NULL,  
    [D] [nvarchar](100) NOT NULL,
    [E] [datetime] NULL,
    [F] [nvarchar](2) NULL,
    [G] [datetime] NULL,
    [H] [nvarchar](100) NULL,
    [I] [int] NULL,

    CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED 
    (
    [A] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT [UK_Files_FileType_FileDate] UNIQUE NONCLUSTERED 
    (
    [D] ASC,
    [E] ASC
            )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,                           
            ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Files]  WITH CHECK ADD  CONSTRAINT [FK_Files_FileStatus] FOREIGN   
    KEY([F])
    REFERENCES [dbo].[F] ([F])
    GO

    ALTER TABLE [dbo].[Files] CHECK CONSTRAINT [FK_Files_FileStatus]
    GO

临时修复:我已经重新创建了表(DROP&CREATE)，然后将旧表的数据复制到了新表中，这使得该表从65GB变为108MB.

Temporary Fix: I have recreated the table (DROP & CREATE), then copied the old table's data into the new one, this made the table go from 65GB to 108MB.

我的问题是:

• 什么可以使这张桌子占用这么多的空间?如何防止它再次长出来?

推荐答案

安装最新的Service Pack解决了该问题.

Installing the latest service pack fixed the problem.

这篇关于SQL表不一致增长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！