sql碎片
清除数据库日志语句
--清除sqlserver 2000/2005 数据库日志
替换下红字部分数据库名称
declare @databasename varchar(15)
select @databasename='hdjjgl_journal'
DUMP TRANSACTION @databasename WITH NO_LOG
BACKUP LOG @databasename WITH NO_LOG
DBCC SHRINKDATABASE(@databasename)
或者
USE 数据库名
DUMP TRANSACTION 数据库名 WITH NO_LOG
BACKUP LOG 数据库名 WITH NO_LOG
DBCC SHRINKFILE(2)
**********************************
--清除sqlserver 2008 数据库日志 ,执行两遍
替换下红字部分数据库名称，建立datalogbak清楚的日志备份文件夹，注意路径自定义。
use gdzjg_journal
declare @databasename varchar(100)
declare @databasenamelog varchar(100)
declare @databasenamedir varchar(100)
select @databasename='gdzjg_journal'
select @databasenamelog=@databasename+'_log'
select @databasenamedir='g:\datalogbak\'+@databasename
BACKUP LOG @databasename to disk=@databasenamedir
DBCC SHRINKFILE (@databasenamelog,1)
或者
ALTER DATABASE 数据库名称SET RECOVERY SIMPLE
ALTER DATABASE 数据库名称SET RECOVERY FULL
DBCC SHRINKDATABASE(数据库名称,0)
sqlserver 2000、2008会保存所有的数据库操作过程，将指令保存在ldf文件中，如果误删除数据，想恢复数据的话，可以通过Lumigent Log Explorer For SQLServer 软件分析ldf文件，可以看到所有执行过的insert、update、delete数据，导出来再逆向执行即可，本人曾经用过一回，确实奏效。
1、sql server 2000清理数据库日志
USE 数据库名
DUMP TRANSACTION 数据库名 WITH NO_LOG
BACKUP LOG 数据库名 WITH NO_LOG
DBCC SHRINKFILE(2)
SHRINKFILE(2)，后面的2是file_id，可以在当前数据库用select * from sysfiles看得到，看数据库的log文件的fileid是多少，正常情况下log的ldf文件是2，mdf文件的fileid是1。
这个查询语句可以随时执行，不影响数据库的运行。
sql server 2005、2008清理数据库日志
USE 数据库名
ALTER DATABASE 数据库名 SET RECOVERY SIMPLE
ALTER DATABASE 数据库名 SET RECOVERY FULL
DBCC SHRINKDATABASE(数据库名,0)
这个查询语句可以随时执行，不影响数据库的运行。
清理ldf的操作可以使用sql server代理，每天自动执行一次，就不怕文件增长撑爆硬盘了。
2、一般mdf用不着收缩，收缩多了容易产生文件碎片，因为delete数据之后，mdf文件中可用页面空间会保留在那里，等下次有新数据进来时，会继续使用。如果实在要清理，可以参考下面的语句：
sql server 2000、2005、2008 收缩mdf文件
DBCC SHRINKDATABASE(数据库名)
DBCC SHRINKFILE(1,0)
DBCC UPDATEUSAGE(0)
执行上述操作后，你会发现mdf的文件减少了，ldf的文件增大了，再用上面1的日志清理操作一次即可。
3、最后再附送一个查看数据表的行数、占用文件空间、存储情况的查询语句
-- drop table #test
create table #test(
name varchar(50),
rows int,
reserved varchar(20),
data varchar(20),
index_size varchar(20),
unused varchar(20)
)
set nocount on
insert into #test
EXEC sp_MSforeachtable @command1="sp_spaceused '?'"
select * from #test order by cast(replace(reserved,'KB','') as int) desc
B. 什么是内部碎片什么是外部碎片各种存储管理中都可能产生何种碎片
1.内部碎片：
当一个进程装入到固定大小的分区块（比如页）时，假如进程所需空间小于分区块，则分区块的剩余的空间将无法被系统使用，称为内部碎片。
2.外部碎片：
指的是还没有被分配出去（不属于任何进程），但由于太小了无法分配给申请内存空间的新进程的内存空闲区域。
3.存储管理中都可能产生的碎片：
除了内部碎片和外部碎片，在“分页存储”中，可能产生“页内碎片”，页内碎片是由于进程的最后一页经常装不满一块而形成了不可利用的碎片。
(2)sql碎片扩展阅读
在数据存储领域中，碎片(fragmentation）是指存储空间使用效率低下，结果导致功能、运行效率变低或二者兼而有之的现象。碎片化所造成的影响取决于具体的存储系统以及碎片化的种类。
大部分情况下，碎片化都会导致都会导致存储空间的浪费，此时“碎片”一词亦可指代闲置的空间本身。对于其他的一些系统来说（比如FAT文件系统），数据量一定的前提下，用于存储数据所占的存储空间是一定的，和碎片化的程度无关。
C. 如何检查sql数据库索引填充因子是否产生碎片以及如何处理
这是收藏的一些资料：
SQLServer提供了一个数据库命令――DBCC SHOWCONTIG――来确定一个指定的表或索引是否有碎片。
示例：
显示数据库里所有索引的碎片信息
DBCC SHOWCONTIG WITH ALL_INDEXES
显示指定表的所有索引的碎片信息
DBCC SHOWCONTIG (authors) WITH ALL_INDEXES
显示指定索引的碎片信息
DBCC SHOWCONTIG (authors,aunmind)
DBCC 执行结果：
扫描页数：如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数，如果明显比你估计的页数要高，说明存在内部碎片。
扫描扩展盘区数：用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。
扩展盘区开关数：该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。
每个扩展盘区上的平均页数：该数是扫描页数除以扫描扩展盘区数，一般是8。小于8说明有外部碎片。
扫描密度［最佳值:实际值］：DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片。
逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。
扩展盘区扫描碎片：无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0％，高了则说明有外部碎片。
每页上的平均可用字节数：所扫描的页上的平均可用字节数。越高说明有内部碎片，不过在你用这个数字决定是否有内部碎片之前，应该考虑fill factor（填充因子）。
平均页密度（完整）：每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片。
解决碎片问题 ：
1. 删除并重建索引
2. 使用DROP_EXISTING子句重建索引
3. 执行DBCC DBREINDEX
4. 执行DBCC INDEXDEFRAG
删除并重建索引 ：
用DROP INDEX和CREATE INDEX或ALTER TABLE来删除并重建索引有些缺陷包括在删除重建期间索引会消失。在索引删除重建时，对于查询它不在可用，查询性能也许会受到明显的影响，直到重建索引为止。另一个潜在的缺陷是当都请求索引的时候会引起阻塞，直到重建索引为止。通过其他的处理也能解决阻塞，就是索引被使用的时候不删除索引。另一个主要的缺陷是在用DROP INDEX和CREATE INDEX重建聚集索引时会引起非聚集索引重建两次。删除聚集索引时非聚集索引的行指针会指向数据堆，聚集索引重建时非聚集索引的行指针又会指回聚集索引的行位置。
删除并重建索引的确有一个好处就是通过重新排序索引页，使索引页紧凑并删除不需要的索引页来完全重建索引。你也许需要考虑那些内部和外部碎片都很高的情况下才使用，以使那些索引回到它们应该在的位置。
使用DROP_EXISTING子句重建索引 ：
为了避免在重建聚集索引时表上的非聚集索引重建两次，可以使用带DROP_EXISTING子句的CREATE INDEX语句。这个子句会保留聚集索引键值，以避免非聚集索引重建两次。和删除并重建索引一样，该方法也可能会引起阻塞和索引消失的问题。该方法的另一个缺陷是也强迫你去分别发现和修复表上的每一个索引。
除了和上一个方法一样的好处之外，该方法的好处是不必重建非聚集索引两次。这样可以对那些带约束的索引提供正确的索引定义以符合约束的要求。
执行DBCC DBREINDEX ：
DBCC DBREINDEX类似于第二种方法，但它物理地重建索引，允许SQLServer给索引分配新页来减少内部和外部碎片。DBCC DBREINDEX也能动态的重建带约束的索引，不象第二种方法。
DBCC DBREINDEX的缺陷是会遇到或引起阻塞问题。DBCC DBREINDEX是作为一个事务来运行的，所以如果在完成之前中断了，那么你会丢失所有已经执行过的碎片。
执行DBCC INDEXDEFRAG ：
DBCC INDEXDEFRAG（在SQLServer2000中可用）按照索引键的逻辑顺序，通过重新整理索引里存在的叶页来减少外部碎片，通过压缩索引页里的行然后删除那些由此产生的不需要的页来减少内部碎片。它不会遇到阻塞问题但它的结果没有其他几个方法彻底。这是因为DBCC INDEXDEFRAG跳过了锁定的页且不使用任何新页来重新排序索引。如果索引的碎片数量大的话你也许会发现DBCC INDEXDEFRAG比重建索引花费的时间更长。DBCC INDEXDEFRAG比其他方法的确有好处的是在其他过程访问索引时也能进行碎片整理，不会引起其他方法的阻塞问题。
D. 为什么sql server数据库索引碎片整理
本文需要你对索引和SQL中数据的存储方式有一定了解
在SQL Server中，存储数据的最小单位是页，每一页所能容纳的数据为8060字节.而页的组织方式是通过B树结构（表上没有聚集索引则为堆结构，不在本文讨论之列)如下图:
在聚集索引B树中，只有叶子节点实际存储数据，而其他根节点和中间节点仅仅用于存放查找叶子节点的数据.
每一个叶子节点为一页，每页是不可分割的. 而SQL Server向每个页内存储数据的最小单位是表的行(Row).当叶子节点中新插入的行或更新的行使得叶子节点无法容纳当前更新或者插入的行时，分页就产生了.在分页的过程中，就会产生碎片.
理解外部碎片
首先，理解外部碎片的这个“外”是相对页面来说的。外部碎片指的是由于分页而产生的碎片.比如，我想在现有的聚集索引中插入一行，这行正好导致现有的页空间无法满足容纳新的行。从而导致了分页:
因为在SQL SERVER中，新的页是随着数据的增长不断产生的，而聚集索引要求行之间连续，所以很多情况下分页后和原来的页在磁盘上并不连续.
E. sqlserver索引碎片怎么避免
毫无疑问，给表添加索引是有好处的，你要做的大部分工作就是维护索引，在数据更改期间索引可能产生碎片，所以一些维护是必要的。碎片可能是你查询产生性能问题的来源。
那么到底什么是索引碎片呢？索引碎片实际上有2种形式：外部碎片和内部碎片。不管哪种碎片基本上都会影响索引内页的使用。这也许是因为页的逻辑顺序错误（即外部碎片）或每页存储的数据量少于数据页的容量（内部错误）。无论索引产生了哪种类型的碎片，你都会因为它而面临查询的性能问题。
外部碎片
当 索引页不在逻辑顺序上时就会产生外部碎片。索引创建时，索引键按照逻辑顺序放在一组索引页上。当新数据插入索引时，新的键可能放在存在的键之间。为了让新 的键按照正确的顺序插入，可能会创建新的索引页来存储需要移动的那些存在的键。这些新的索引页通常物理上不会和那些被移动的键原来所在的页相邻。创建新页 的过程会引起索引页偏离逻辑顺序。
下面的例子将比实际的言论更加清晰的解释这个概念。
假定在任何另外的数据插入你的表之前存在索引上的结构如下
（注：下面图片里应该是7和8，原文里是6和8）：
INSERT语句往索引里添加新的数据，假定添加的是5。INSERT将引起新页创建，为了给5在原来的页上留出空间，7和8被移到了新页上。这个创建将引起索引页偏离逻辑顺序。
在有特定搜索或者返回无序结果集的查询的情况下，偏离顺序的索引页不会引起问题。对于返回有序结果集的查询，搜索那些无序的索引页需要进行额外的处理。有序结果集的例子如查询返回4到10之间的记录。为了返回7和8，查询不得不进行额外的页切换。虽然一个额外的页切换在一个长时间运行里是无关紧要的，然而想象一下一个有好几百页偏离顺序的非常大的表的情形。
内部碎片
当索引页没有用到最大量时就产生了内部碎片。虽然在一个有频繁数据插入的应用程序里这也许有帮助，然而设置一个fill factor（填充因子）会在索引页上留下空间，服务器内部碎片会导致索引尺寸增加，从而在返回需要的数据时要执行额外的读操作。这些额外的读操作会降低查询的性能。
怎样确定索引是否有碎片？
SQLServer提供了一个数据库命令――DBCC SHOWCONTIG――来确定一个指定的表或索引是否有碎片。
DBCC SHOWCONTIG
数据库平台命令，用来显示指定的表的数据和索引的碎片信息。
DBCC SHOWCONTIG 权限默认授予 sysadmin固定服务器角色或 db_owner 和 db_ddladmin固定数据库角色的成员以及表的所有者且不可转让。
语法（SQLServer2000）
DBCC SHOWCONTIG
[ ( { table_name | table_id| view_name | view_id }
[ , index_name | index_id ]
)
]
[ WITH { ALL_INDEXES
| FAST [ , ALL_INDEXES ]
| TABLERESULTS [ , { ALL_INDEXES } ]
[ , { FAST | ALL_LEVELS } ]
}
]
语法（SQLServer7.0）
DBCC SHOWCONTIG
[ ( table_id [,index_id ]
)
]
示例：
显示数据库里所有索引的碎片信息
SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG WITH ALL_INDEXES
GO
显示指定表的所有索引的碎片信息
SET NOCOUNT ONUSE pubs
DBCC SHOWCONTIG (authors) WITH ALL_INDEXES
GO
显示指定索引的碎片信息
SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors,aunmind)
GO
结果集
DBCC SHOWCONTIG将返回扫描页数、扫描扩展盘区数、遍历索引或表的页时，DBCC 语句从一个扩展盘区移动到其它扩展盘区的次数、每个扩展盘区的页数、扫描密度（最佳值是指在一切都连续地链接的情况下，扩展盘区更改的理想数目）。
DBCC SHOWCONTIG 正在扫描 'authors' 表...
表: 'authors'（1977058079）；
索引 ID: 1，数据库 ID: 5
已执行 TABLE 级别的扫描。
- 扫描页数.....................................: 1
- 扫描扩展盘区数...............................: 1
- 扩展盘区开关数...............................: 0
- 每个扩展盘区上的平均页数.....................: 1.0
- 扫描密度［最佳值:实际值］....................: 100.00%［1:1］
- 逻辑扫描碎片.................................: 0.00%
- 扩展盘区扫描碎片.............................: 0.00%
- 每页上的平均可用字节数.......................: 6010.0
- 平均页密度（完整）...........................: 25.75%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
寻找什么
扫描页数：如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数，如果明显比你估计的页数要高，说明存在内部碎片。
扫描扩展盘区数：用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。
扩展盘区开关数：该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。
每个扩展盘区上的平均页数：该数是扫描页数除以扫描扩展盘区数，一般是8。小于8说明有外部碎片。
扫描密度［最佳值:实际值］：DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片。
逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。
扩展盘区扫描碎片：无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0％，高了则说明有外部碎片。
每页上的平均可用字节数：所扫描的页上的平均可用字节数。越高说明有内部碎片，不过在你用这个数字决定是否有内部碎片之前，应该考虑fill factor（填充因子）。
平均页密度（完整）：每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片。
备注
DBCC SHOWCONTIG实际上仅对那些大表有用。小表显示的结果根本不符合正常标准，因为他们也许没有由多于8个的页面组成。你在查看小表上执行DBCC SHOWCONTIG的结果时应该忽略一些结果。在处理小表时只需关心扩展盘区开关数、逻辑扫描碎片、每页上的平均可用字节数、平均页密度（完整）。
DBCC SHOWCONTIG默认输出的结果是：扫描页数、扫描扩展盘区数、扩展盘区开关数、每个扩展盘区上的平均页数、扫描密度［最佳值:实际值］、逻辑扫描碎片、扩展盘区扫描碎片、每页上的平均可用字节数、平均页密度（完整）。可以用FAST和TABLERESULTS选项来控制这个输出结果。
FAST选项指定执行索引的快速扫描，输出结果是最小的，该选项不读索引的叶或数据页且只返回扫描页数、扫描扩展盘区数、扫描密度［最佳值:实际值］、逻辑扫描碎片。
TABLERESULTS选项将用行集的形式显示信息，将返回扩展盘区开关数、扫描密度［最佳值:实际值］、逻辑扫描碎片、扩展盘区扫描碎片、每页上的平均可用字节数、平均页密度（完整）。
如果既指定FAST选项又指定TABLERESULTS选项，那么将返回对象名、对象ID、索引名、索引ID，页数、扩展盘区开关数、扫描密度［最佳值:实际值］和逻辑扫描碎片。
ALL_INDEXES选项将显示指定表和试图的所有索引的结果，即使指定了一个索引。
ALL_LEVELS选项指定是否为所处理的每个索引的每个级别产生输出（默认只输出索引的页级或表数据级的结果），并且只能与 TABLERESULTS 选项一起使用。
解决碎片问题
一旦你确定表或索引有碎片问题，那么你有4个选择去解决那些问题：
删除并重建索引
使用DROP_EXISTING子句重建索引
执行DBCC DBREINDEX
执行DBCC INDEXDEFRAG
尽管每一个技术都能达到你整理索引碎片的最终目的，但各有各的优缺点。
删除并重建索引
用DROP INDEX和CREATE INDEX或ALTER TABLE来 删除并重建索引有些缺陷包括在删除重建期间索引会消失。在索引删除重建时，对于查询它不在可用，查询性能也许会受到明显的影响，直到重建索引为止。另一个 潜在的缺陷是当都请求索引的时候会引起阻塞，直到重建索引为止。通过其他的处理也能解决阻塞，就是索引被使用的时候不删除索引。另一个主要的缺陷是在用DROP INDEX和CREATE INDEX重建聚集索引时会引起非聚集索引重建两次。删除聚集索引时非聚集索引的行指针会指向数据堆，聚集索引重建时非聚集索引的行指针又会指回聚集索引的行位置。
删除并重建索引的确有一个好处就是通过重新排序索引页，使索引页紧凑并删除不需要的索引页来完全重建索引。你也许需要考虑那些内部和外部碎片都很高的情况下才使用，以使那些索引回到它们应该在的位置。
使用DROP_EXISTING子句重建索引
为了避免在重建聚集索引时表上的非聚集索引重建两次，可以使用带DROP_EXISTING子句的CREATE INDEX语句。这个子句会保留聚集索引键值，以避免非聚集索引重建两次。和删除并重建索引一样，该方法也可能会引起阻塞和索引消失的问题。该方法的另一个缺陷是也强迫你去分别发现和修复表上的每一个索引。 除了和上一个方法一样的好处之外，该方法的好处是不必重建非聚集索引两次。这样可以对那些带约束的索引提供正确的索引定义以符合约束的要求。 执行DBCC DBREINDEX DBCC DBREINDEX类似于第二种方法，但它物理地重建索引，允许SQLServer给索引分配新页来减少内部和外部碎片。DBCC DBREINDEX也能动态的重建带约束的索引，不象第二种方法。 DBCC DBREINDEX的缺陷是会遇到或引起阻塞问题。DBCC DBREINDEX是作为一个事务来运行的，所以如果在完成之前中断了，那么你会丢失所有已经执行过的碎片。 执行DBCC INDEXDEFRAG DBCC INDEXDEFRAG（在SQLServer2000中可用）按照索引键的逻辑顺序，通过重新整理索引里存在的叶页来减少外部碎片，通过压缩索引页里的行然后删除那些由此产生的不需要的页来减少内部碎片。它不会遇到阻塞问题但它的结果没有其他几个方法彻底。这是因为DBCC INDEXDEFRAG跳过了锁定的页且不使用任何新页来重新排序索引。如果索引的碎片数量大的话你也许会发现DBCC INDEXDEFRAG比重建索引花费的时间更长。DBCC INDEXDEFRAG比其他方法的确有好处的是在其他过程访问索引时也能进行碎片整理，不会引起其他方法的阻塞问题。
F. sql server 2008怎么用SQL语句查询索引碎片
检查索引的碎片的步骤：
1. 在“对象资源管理器”中，连接到 数据库引擎的实例。
2. 在标准菜单栏上，单击“新建查询”。
3. 将以下示例复制并粘贴到查询窗口中，然后单击“执行”。
USEAdventureWorks2012;
GO
--
--intheHumanResources.Employeetable.
SELECTa.index_id,name,avg_fragmentation_in_percent
FROMsys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2012'),OBJECT_ID(N'HumanResources.Employee'),NULL,NULL,NULL)ASa
JOINsys.indexesASbONa.object_id=b.object_idANDa.index_id=b.index_id;
GO
重新组织碎片索引
1. 在“对象资源管理器”中，连接到 数据库引擎的实例。
2. 在标准菜单栏上，单击“新建查询”。
3. 将以下示例复制并粘贴到查询窗口中，然后单击“执行”。
USEAdventureWorks2012;
GO
--ReorganizetheIX_Employee_OrganizationalLevel_.Employeetable.
ALTERINDEXIX_Employee_OrganizationalLevel_.Employee
REORGANIZE;
GO
参考文档： https://msdn.microsoft.com/zh-cn/library/ms189858.aspx?