关于数据库文件虚大的问题
由于客户哪里积累了十几年的数据，需要对数据库进行备份，将前几年的数据备份出来，并删除这些数据。所有的数据备份做完之后，数据量基本上减少了一半，但是数据库只减少了六分之一，数据文件已经收缩过，日志也已经清空。但是在我新建了一个数据库，将所有数据库导过去之后，发现其实数据才500多M。
下面上几个业务表的结构：
//这个是个主表
CREATE TABLE [BO_Deliver] (
[OID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_BO_Deliver_OID] DEFAULT (newid()),
[DeliverNo] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[TwoNo] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CustomerNo] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[DeliverDate] [datetime] NULL ,
[DeliverAddress] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[TransportCompany] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[TruckNo] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[KB] [int] NULL ,
[BackKB] [int] NULL ,
[KBSource] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[TransFactSign] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[AllWeight] [int] NULL ,
[EditDate] [datetime] NULL ,
[Remark] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CreateDate] [datetime] NOT NULL ,
[ModifyDate] [datetime] NOT NULL ,
CONSTRAINT [PK_BO_Deliver] PRIMARY KEY CLUSTERED
(
[OID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
//下面这个是明细表
CREATE TABLE [BO_DeliverItems] (
[OID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_BO_DeliverItems_OID] DEFAULT (newid()),
[ParentOID] [TOID] NOT NULL ,
[DeliverItemsNo] [varchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[OrderNo] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[OrderItemsNo] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomerOrderNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[OrderDate] [datetime] NULL ,
[Sort] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[Type] [varchar] (14) COLLATE Chinese_PRC_CI_AS NULL ,
[Class] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[Thickness] [int] NULL ,
[TheSize] [varchar] (21) COLLATE Chinese_PRC_CI_AS NULL ,
[ProduceArea] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[Piece] [int] NULL ,
[PieceUnit] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[DeliverWeight] [int] NULL ,
[AdjustWeight] [int] NULL CONSTRAINT [DF_BO_DeliverItems_AdjustWeight] DEFAULT (0),
[OweWeight] [int] NULL ,
[OweCue] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[CreateDate] [datetime] NOT NULL ,
[ModifyDate] [datetime] NOT NULL ,
CONSTRAINT [PK_BO_DeliverItems] PRIMARY KEY CLUSTERED
(
[ParentOID],
[DeliverItemsNo]
) ON [PRIMARY] ,
CONSTRAINT [IX_BO_DeliverItems] UNIQUE NONCLUSTERED
(
[OID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
请各位给分析一下。
[解决办法]
反复收缩就可以减下来.
[解决办法]
新建一个数据库,在新建这两个表,然后把数据导进去试试(实现先把这两表数据导出来.)
[解决办法]
select object_name(a.id) name,
sum(a.rows) rows,
str(sum(cast(reserved as bigint) * b.low / 1024.00),15,0) + 'KB ' reserved,
str(sum(cast(dpages as bigint) * b.low / 1024),15,0) + ' KB ' data,
str(sum((cast(a.used as bigint) - cast(a.dpages as bigint)) * b.low / 1024),15,0) + ' KB ' index_size,
str(sum((cast(a.reserved as bigint) - cast(a.used as bigint)) * b.low / 1024),15,0) + 'KB ' unused
from sysindexes a,master.dbo.spt_values b
where a.indid in (0, 1, 255)
and b.number = 1
and b.type = 'E '
group by a.id,b.low
order by a.reserved desc
查出数据库表格大小后，你看看unused这个字段，如果异常的大，那很可能这个表没有主键，考虑健一个主键值就可以小下来。
再可以用如下方法减少数据库单个表格大小。
select * into 备份表名 from 原表
truncate table 原表
insert into 原表
select * from 备份表名
drop table 备份表名
反复操作多个表格后，再压缩数据库，可以小很多。
[解决办法]
备份日志,压缩数据库
