SQL-Server数据库容量查询/数据压缩/迁移

 

 

 

SQL - SQL Server：简单查询所占空间

在MS Sql Server中可以能过以下的方法查询出磁盘空间的使用情况及各数据库数据文件及日志文件的大小及使用利用率:

 

1、查询各个磁盘分区的剩余空间：
        Exec master.dbo.xp_fixeddrives   

2、查询数据库的数据文件及日志文件的相关信息（包括文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径等）   
     select * from [数据库名].[dbo].[sysfiles]
     转换文件大小单位为MB：
    select name, convert(float,size) * (8192.0/1024.0)/1024. from [数据库名].dbo.sysfiles

3、查询当前数据库的磁盘使用情况：
    Exec sp_spaceused

４、查询数据库服务器各数据库日志文件的大小及利用率
    DBCC SQLPERF(LOGSPACE) 

---

--主要原理： 

exec sp_spaceused '表名' --取得表占用空间
exec sp_spaceused ''--数据库所有空間

---

SQL - SQL Server ：列出每个表所占用空间大小

---

SELECT 

    t.NAME AS TableName,

    s.Name AS SchemaName,

    p.rows AS RowCounts,

    SUM(a.total_pages) * 8 AS TotalSpaceKB, 

    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB, 

    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

FROM 

    sys.tables t

INNER JOIN      

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

INNER JOIN 

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN 

    sys.schemas s ON t.schema_id = s.schema_id

WHERE 

    t.NAME NOT LIKE 'dt%' 

    AND t.is_ms_shipped = 0

    AND i.OBJECT_ID > 255 

GROUP BY 

    t.Name, s.Name, p.Rows

ORDER BY 

    UsedSpaceMB  DESC

---

SQLServer数据库收缩/可用空间查询

收缩数据库虽然可以腾出磁盘空间，但是不一定是一种优化操作

收缩前考虑：

1.在执行会产生许多未使用空间的操作（如截断表或删除表操作）后，执行收缩操作最有效。
2.大多数数据库都需要一些可用空间，以供常规日常操作使用。如果反复收缩数据库并注意到数据库大小变大，则表明收缩的空间是常规操作所必需的。在这种情况下，反复收缩数据库是一种无谓的操作。
3.收缩操作不会保留数据库中索引的碎片状态，通常还会在一定程度上增加碎片。这是不要反复收缩数据库的另一个原因。

4.除非有特定要求，否则不要将 AUTO_SHRINK 数据库选项设置为 ON。

权限要求：

要求具有 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员身份

查数据库空闲空间：

SELECT name ,size/128.0 as size --文件大小

,CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0,--已用空间
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB --可收缩空间
FROM sys.database_files

数据库收缩前需要把数据库恢复模式改为简单模式：

ALTER DATABASE 数据库名 SET RECOVERY SIMPLE WITH NO_WAIT --

WITH NO_WAIT ：指定如果请求的数据库状态或选项更改只有等待事务自主提交或回滚才能立即完成，否则该请求将失败。

ALTER DATABASE 数据库名 SET RECOVERY SIMPLE

执行完毕后把数据库模式改回原来状态：

ALTER DATABASE 数据库名 SET RECOVERY FULL WITH NO_WAIT

ALTER DATABASE 数据库名 SET RECOVERY FULL

数据库收缩个人分为两种：1.数据库收缩（SHRINKDATABASE）；2.数据库文件收缩。

一.数据库收缩：

DBCC SHRINKDATABASE([你的数据库名称]):

DBCC SHINKDATABASE (USERDB,10);--收缩数据文件和日志文件的大小，在数据库中流出10%的可用空间；

DBCC SHINKDATABASE (USERDB,TRUNCATEONLY) ;--把数据文件收缩到最后分配的区。

工作原理：

DBCC SHRINDATABASE 以每个文件为单位对数据库文件进行收缩，然而，DBCC SHRINKDATABASE 在对日志文件进行收缩时，它将视为所有的日志文件都存在于一个连续的日志池中。文件始终从末尾开始收缩。
假设名为 mydb 的数据库有一个数据文件和两个日志文件。数据文件和日志文件分别是 10 MB，并且数据文件包含 6 MB 数据。

1.对于每个文件，数据库引擎都会计算一个目标大小。这就是文件将要收缩到的大小。

2.将 target_percent 与 DBCC SHRINKDATABASE 一起指定时，数据库引擎计算的目标大小是收缩后文件中的 target_percent 可用空间大小。例如，如果在收缩 mydb 时将 target_percent 指定为 25，则数据库引擎将此文件的目标大小计算为 8 MB（6 MB 数据加上 2 MB 可用空间）。因此，数据库引擎将任何数据从数据文件的后 2 MB 中移动到数据文件前 8 MB 的可用空间中，然后对该文件进行收缩。

假设 mydb 的数据文件包含 7 MB 的数据。将 target_percent 指定为 30，以允许将此数据文件收缩到可用空间的 30％。但是，将 target_percent 指定为 40 却不会收缩数据文件，因为数据库引擎收缩文件的目标大小不能小于数据当前占用空间大小。

您还可以用另一种方法来考虑此问题：所要求的 40％ 可用空间加上整个数据文件大小的 70％（10 MB 中的 7 MB），超过了 100％。因为所要求的可用百分比加上数据文件占用的当前百分比大于 100%（多出 10%），所以任何大于 30 的 target_size 都不会收缩此数据文件。

      对于日志文件，数据库引擎使用 target_percent 来计算整个日志的目标大小；因此，target_percent 是收缩操作后日志中的可用空间大小。之后，整个日志的目标大小转换为每个日志文件的目标大小。
DBCC SHRINKDATABASE 尝试立即将每个物理日志文件收缩到其目标大小。如果虚拟日志中的所有逻辑日志部分都没有超出日志文件的目标大小，则该文件将成功截断，DBCC SHRINKDATABASE 完成且不显示任何消息。但是，如果部分逻辑日志位于超出目标大小的虚拟日志中，则数据库引擎将释放尽可能多的空间，并发出一条信息性消息。该消息说明需要执行哪些操作来将逻辑日志移出位于文件末尾的虚拟日志。执行该操作以后，DBCC SHRINKDATABASE 可用于释放剩余空间。
因为日志文件只能收缩到虚拟日志文件边界，所以不可能将日志文件收缩到比虚拟日志文件更小（即使现在没有使用该文件）。虚拟日志文件的大小在创建或扩展这些日志文件时由数据库引擎动态选择。
注：

1.不能在备份的时候收缩数据库，也不能收缩数据库的时候备份数据库；

2.收缩数据库不必再单用户模式下，但是会有一定程度的性能影响；

3.收缩后的数据库不能小于数据库的最小大小。

二、数据文件收缩

收缩当前数据库的指定数据或日志文件的大小，或通过将数据从指定的文件移动到相同文件组中的其他文件来清空文件，以允许从数据库中删除该文件。文件大小可以收缩到比创建该文件时所指定的大小更小。这样会将最小文件大小重置为新值。

DBCC SHRINKFILE (N’日志文件名或数据文件名', 7); --收缩到指定的大小7MB

DBCC SHRINKFILE(N’日志文件名’,0,TRUNCATEONLY)；--截断日志文件

DBCC SHRINKFILE (Test1data, EMPTYFILE); --清空文件，以便于删除数据库，谨慎操作

TRUNCATEONLY
将文件末尾的所有可用空间释放给操作系统，但不在文件内部执行任何页移动。数据文件只收缩到最后分配的区。
如果随 TRUNCATEONLY 指定了 target_size，则会忽略该参数。TRUNCATEONLY 只适用于数据文件。
 

---

Windows清理磁盘碎片

---

1、udefrag绿色汉化版通常多久整理磁盘一次？

般家庭用户1个月整理一次，商业用户以及服务器半个月整理一次。但要根据碎片比例来考虑，如在windows7中，碎片超过10%，则需整理，否则不必。

2、磁盘碎片是怎么产生的？

应用程序所需的物理内存不足时，一般操作系统会在硬盘中产生临时交换文件，用该文件所占用的硬盘空间虚拟成内存。虚拟内存管理程序会对硬盘频繁读写，产生大量的碎片，这是产生硬盘碎片的主要原因。其他如IE浏览器浏览信息时生成的临时文件或临时文件目录的设置也会造成系统中形成大量的碎片。

 

我们的硬盘由于长时间的使用中反复地写入和删除文件，磁盘中的空闲扇区会分散到整个磁盘中不连续的位置中，再次读写文件的话就需要到不同的地方去读取，降低了访问速度。这款udefrag绿色汉化版，能够快速地整理你的磁盘碎片，大大地缩短重组的执行时间，高效、实用；本站提供udefrag官方下载！