徐记一、数据库空间管理

徐记一、数据库空间管理

1.1 文件的分配方式及文件空间检查方法

1.1.1 数据文件分配

  MSSQL读取或写入数据的最小单位是以8KB为单位的页。

  区是8个物理上连续的页的集合，用来有效的管理页。

  查看数据页面（打开 dbcc traceon(3604)）：

 DBCC PAGE(<db_id>,<file_id>,<page_id>,<format_id>)

  sp_helpdb --查看数据库信息

  sp_helpfile --查看数据文件信息

1.1.2 数据文件空间使用的计算方法

  按区统计：dbcc showfilestats

  按页面统计：

        1> sys.dm_db_partition_stats 显示用于存储和管理数据库中全部分区的行内数据 LOB 数据和行溢出数据的空间的有关信息。  每个分区对应一行。

SELECT o.name ,

SUM (p.reserved_page_count) as reserved_page_count,

SUM (p.used_page_count) as used_page_count,

SUM (

CASE

WHEN (p.index_id < 2) THEN (p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count)

ELSE p.lob_used_page_count + p.row_overflow_used_page_count

END

) as DataPages,

SUM (

CASE

WHEN (p.index_id < 2) THEN row_count

ELSE 0

END

) as rowCounts

FROM sys.dm_db_partition_stats p

inner join sys.objects o on p.object_id = o.object_id

group by o.name

2>DBCC SHOWCONTIG(或sys.dm_db_index_physical_stats)是检查数据空间分配情况最准确的方法。

若只看数据文件整体使用情况，用DBCC SHOWFILESTATS

若要看每个对象的空间使用情况，用sys.dm_db_partition_stats

若要了解每个页、每个区的使用情况、碎片程度，用DBCC SHOWCONFIG

1.1.3 日志文件

   MSSQL数据库引擎在内部将每一物理日志文件分成多个虚拟日志单元。

   查看日志文件使用情况：

      DBCC SQLPERF(LOGSPACE)--执行不会带来负担

1.1.4 Tempdb

    sys.dm_db_file_space_usage,通过查询这张视图，能了解tempdb的空间使用情况，能知道tempdb的空间是被哪一块对象使用掉的，是用户对象(user_object_reserved_page_count字段)，还是系统对象（internal_object_reserved_page_count字段），还是版本存储区（version_store_reserved_page_count字段）

1.1.5 案例：通过脚本监视Tempdb空间使用

影响大量使用Tempdb的操作：

用户对象(user_object_reserved_page_count)

用户对象由用户显式创建。这些对象可以位于用户会话的作用域中，也可位于创建对象所用例程的作用域中。 可以是存储过程、触发器或用户定义函数。 用户对象可以是下列项之一：

• 用户定义的表和索引
• 系统表和索引
• 全局临时表和索引
• 局部临时表和索引
• table 变量
• 表值函数中返回的表

内部对象（internal_object_reserved_page_count）

内部对象是根据需要由 SQL Server 数据库引擎创建的，用于处理 SQL Server 语句。 内部对象可以在语句的作用域中创建和删除。 内部对象可以是下列项之一：

• 用于游标。
• 用于哈希联接或哈希聚合操作的查询。
• 某些 GROUP BY、ORDER BY 或 UNION 查询的中间排序结果。

版本存储（version_store_reserved_page_count）

版本存储区主要用来支持Snapshot事务隔离级别，以及SQL 2005以后推出的一些其他提高数据库并发度的新功能。

由此可见，光从用户发过来的语句本身，是很难判断这个连接的操作是否会使用Tempdb的。一个典型的例子，就是某些查询。如果表格上有良好的索引做支持，SQL Server不需要做哈希联接(Hash Join)，那这个查询就不会用Tempdb。反之，如果表格很大，又没有好的索引，那Tempdb使用量就可能不小。

tempdb空间使用的一大特点，是只有一部分对象，例如用户创建的临时表、table变量等，可以用sys.allocation_units 和 sys.partitions这样的管理视图来管理。许多内部对象和版本存储在这些管理视图里没有体现。所以，sp_spaceused的结果和真实使用会有很大差异，tempdb的空间使用是不能用sp_spaceused来跟踪的。必须借助sys.dm_db_file_space_usage这样的管理视图和管理函数，才能看到全貌。

--下面的脚本就可以监视和发现当前的Tempdb使用者,需要在使用tempdb的语句开始运行之前开始,将执行结果以文件形式保存,便于分析

USE tempdb  

-- 每隔1秒钟运行一次，直到用户手工终止脚本运行

WHILE 1 = 1 

BEGIN                                                                   

-- Query 1

-- 从文件级看tempdb使用情况

DBCC showfilestats                                                 

-- Query 2

-- 返回所有做过空间申请的session信息

SELECT 'tempdb' AS database_name , GETDATE() AS Time , SUM(user_object_reserved_page_count)/128. AS user_objects_mb ,

        SUM(internal_object_reserved_page_count)/128. AS internal_objects_mb , SUM(version_store_reserved_page_count)/128. AS version_store_kb ,

        SUM(unallocated_extent_page_count)/128. AS freespace_mb

FROM sys.dm_db_file_space_usage

WHERE database_id = 2                                                    

-- Query 3

-- 这个管理视图能够反映当时tempdb空间的总体分配

SELECT t1.session_id , t1.internal_objects_alloc_page_count , t1.user_objects_alloc_page_count , t1.internal_objects_dealloc_page_count ,

        t1.user_objects_dealloc_page_count , t3.login_time , t3.login_name , t3.host_name , t3.nt_domain , t3.nt_user_name , t3.program_name ,

        t3.status , t3.client_interface_name , t3.cpu_time , t3.memory_usage , t3.total_scheduled_time , t3.total_elapsed_time ,

        t3.last_request_start_time , t3.last_request_end_time , t3.reads , t3.writes , t3.logical_reads , t3.is_user_process , t3.row_count ,

        t3.prev_error , t3.original_security_id , t3.original_login_name , t3.last_successful_logon , t3.last_unsuccessful_logon ,

        t3.unsuccessful_logons , t3.group_id

FROM sys.dm_db_session_space_usage t1 ,                               

-- 反映每个session累计空间申请

        sys.dm_exec_sessions AS t3

-- 每个session的信息

WHERE t1.session_id = t3.session_id

        AND (

             t1.internal_objects_alloc_page_count > 0

             OR t1.user_objects_alloc_page_count > 0

             OR t1.internal_objects_dealloc_page_count > 0

             OR t1.user_objects_dealloc_page_count > 0

            )

-- Query 4

-- 返回正在运行并且做过空间申请的session正在运行的语句

SELECT t1.session_id , st.text , GETDATE()

FROM sys.dm_db_session_space_usage AS t1 ,

        sys.dm_exec_requests AS t4

CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st

WHERE t1.session_id = t4.session_id

        AND t1.session_id > 50

        AND (

             t1.internal_objects_alloc_page_count > 0

             OR t1.user_objects_alloc_page_count > 0

             OR t1.internal_objects_dealloc_page_count > 0

             OR t1.user_objects_dealloc_page_count > 0

            ) 

-- Query 5

-- 返回正在运行的活动的空间使用情况以及语句内容和执行计划 

;WITH task_space_usage AS (

    -- SUM alloc/delloc pages

    SELECT session_id,

           request_id,

           SUM(internal_objects_alloc_page_count) AS alloc_pages,

           SUM(internal_objects_dealloc_page_count) AS dealloc_pages

    FROM sys.dm_db_task_space_usage WITH (NOLOCK)

    WHERE session_id <> @@SPID

    GROUP BY session_id, request_id

)

SELECT TSU.session_id,

       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],

       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],

       EST.text,

       -- Extract statement from sql text

       ISNULL(

           NULLIF(

               SUBSTRING(

                   EST.text, 

                   ERQ.statement_start_offset / 2, 

                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END

               ), ''

           ), EST.text

       ) AS [statement text],

       EQP.query_plan

FROM task_space_usage AS TSU

INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)

    ON TSU.session_id = ERQ.session_id

    AND TSU.request_id = ERQ.request_id

OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST

OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP

WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL

ORDER BY 3 DESC, 5 DESC

WAITFOR DELAY '0:0:3'                                                    

END      

1.2 数据文件空间使用与管理

1.2.1 表和索引存储结构

   sys.partitions为表或索引中每个分区返回一行。

   堆：sys.system_internals_allocation_units first_iam_page列指向指定分区中堆数据集合的IAM链，并使用IAM页查找数据页集合中的每一页(如图1-23)。

   聚集索引：sys.system_internals_allocation_units root_page列指向指定分区内聚集索引B树的顶端，使用索引B树链表能够从顶端页面查找到分区中的每个数据页(如图1-24)。 

  非聚集索引：sys.system_internals_allocation_units root_page列指向指定分区内非聚集索引B树的顶端(如图1-25)。

1.2.2 比较存储结构对空间使用的影响

   dbcc showcontig('表名') WITH ALL_INDEXES;--查看区和页

   在相同的字段上，建立聚集索引并不会增加表格大小，而建立非聚集索引会增加空间。

1.2.3 DELETE和TRUNCATE之间的区别

  与delete比，truncate具有以下优点：

  1>所用的事务日志空间较少。delete语句每次删除一行，并在事务日志中为所删除的每行记录一个项。truncate table通过释放用于存储表数据的数据页来删除数据，并在事务日志中只记录页释放这个动作，而不记录每一行。

  2>使用的锁较少。delete 将锁定表中各行以便删除。truncate table始终锁定表和页，而不是锁定各行。

  3>表中将毫不例外地留下任何页。

1.2.4 为什么DBCC SHRINKFILE不起作用

    DBCC SHRINKFILE做的是区一级的动作，不会把页面的空间移除、合并。

1.3 日志文件不停增长

1.3.1 日志文件里到底有什么

   DBCC LOG(<db_id>,<format_id>)

   日志记录特点：

    1>日志记录的是数据的变化，而不是记录用户发过来的操作。若要通过日志推出用户发来的语句是不可能得。

    2>每条记录都有它唯一的编号(LSN)，并且记录了她属于的事务号。

    3>日志记录的行数和实际修改的数据量有关。

    4>日志记录了事务发生的时间，但是不保证记录下了发起这个事务的用户名，更不记录发起者的程序名称。

    5>MSSQL能够从日志记录里面读到数据修改前后的值。但很难了解其修改过程。

    6>日志不是做用户行为监视和记录，而是在对性能影响最小的前提下保证事务一致性。

1.3.2 日志文件增长原因

    增长原因有：

    1>数据库恢复模式不是简单模式，但是没有安排日志备份。对于非简单模式的数据库，只有做完日志备份后记录才会被截断。做完整备份和差异备份都不会起这个作用。

    2>数据库上面有一个很长时间都没有提交的事务。（做过日志备份也没用）

    3>有一个很大的事务正在运行。如建立/重建索引，或DML操作大量数据，或用户端开了一个服务器游标，但是没有把数据及时取走。

    4>数据库复制或镜像出了异常。

1.3.3 案例：日志增长原因定位

    1>检查日志现在使用情况和数据库状态。如果检查发现日志的绝大部分都在使用中（log space used）很高，说明日志记录不能被sqlserver清除掉，需要找原因；如果日志重用等待状态是log_backup，那就说明sql server等待着日志备。这时需要检查备份计划。

      -- 检查日志现在使用情况和数据库状态

      dbcc sqlperf(logspace)

      select name,recovery_model_desc,log_reuse_wait,log_reuse_wait_desc from sys.databases

    2>检查最老的活动事务

     如果日志大部分都在使用中，而且日志重用等待状态是active_transaction，那么就要看这个数据库最久未提交的事务是谁申请的。脚本如下：

  -- 检查最老的活动事务

 dbcc opentran

 go

 SELECT st.text,t2.*                                                         

from 

sys.dm_exec_sessions as t2,

sys.dm_exec_connections as t1

CROSS APPLY sys.dm_exec_sql_text(t1.most_recent_sql_handle) AS st 

where  

 t1.session_id = t2.session_id

and t1.session_id >50

 说明：其中用dbcc opentran先查询出运行中的事务情况，然后用下面的查询查询出具体事务关联的对象。然后如果是某个链接引起的，通过sessionid检查什么链接端什么情况，如果不能，直接在服务器上kill掉这个链接。比如如果sessionid为40。则:kill 40然后再运行dbcc opentran.检查下一个。依次类推。

1.4 文件自动增长和自动收缩

   设置数据库自增长要注意以下几点。

（1）要设置成按固定大小增长，而不能按比例。这样就能避免一次增长太多或者太少所带来的不

必要的麻烦。建议对比较小的数据库，设置一次增长 50 MB 到 100 MB。对大的数据库，设置一次增长

100 MB 到 200 MB。

（2）要定期监测各个数据文件的使用情况，尽量保证每个文件剩余的空间一样大，或者是期望的

比例。

（3）设置文件最大值，以免 SQL Server 文件自增长用尽磁盘空间，影响操作系统。

（4）发生自增长后，要及时检查新的数据文件空间分配情况。避免 SQL Server 总是往个别文件写

数据。

除了自动增长，数据库还有一个自动收缩的功能。如果设定了这个功能，SQL Server 每隔半个

小时就会检查文件使用情况。如果空闲空间大于 25%，SQL Server 就会自动运行 DBCC SHRINKFILE

的动作。所以这个功能能够防止数据库申请过多的空间而不使用。对一个硬盘空间很紧张的系统，

这个设置无疑是有帮助的。但是从数据库自身的健康和性能考虑，这个设置并不建议多用。这是因

为：

（1）SQL Server 只有在空间用尽的情况下才会做自动增长。如果没有找出自增长的原因，从而从

根本上避免空间用尽，虽然能够暂时用 DBCC SHRINKFILE 功能收缩文件大小，但是下次数据库还是

有可能长大。收缩数据库只是一个治标不治本的方法。

（2）数据文件收缩会给文件带来更多的碎片。

（3）不管是数据库收缩，还是增长，对 SQL Server 来讲都是件浪费资源的事情。在负载比较重的

系统里，对性能的影响尤其大。它们是应尽量避免而不是鼓励的操作。

因此，对于一个比较繁忙的数据库，推荐的设置是开启数据库自动增长选项，以防数据库空间用

尽导致应用程序失败，但是要严格避免自动增长的发生。同时，尽量不要使用自动收缩功能。

徐记一、数据库空间管理

1.1 文件的分配方式及文件空间检查方法

1.1.1 数据文件分配

  MSSQL读取或写入数据的最小单位是以8KB为单位的页。

  区是8个物理上连续的页的集合，用来有效的管理页。

  查看数据页面（打开 dbcc traceon(3604)）：

 DBCC PAGE(<db_id>,<file_id>,<page_id>,<format_id>)

  sp_helpdb --查看数据库信息

  sp_helpfile --查看数据文件信息

1.1.2 数据文件空间使用的计算方法

  按区统计：dbcc showfilestats

  按页面统计：

        1> sys.dm_db_partition_stats 显示用于存储和管理数据库中全部分区的行内数据 LOB 数据和行溢出数据的空间的有关信息。  每个分区对应一行。

SELECT o.name ,

SUM (p.reserved_page_count) as reserved_page_count,

SUM (p.used_page_count) as used_page_count,

SUM (

CASE

WHEN (p.index_id < 2) THEN (p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count)

ELSE p.lob_used_page_count + p.row_overflow_used_page_count

END

) as DataPages,

SUM (

CASE

WHEN (p.index_id < 2) THEN row_count

ELSE 0

END

) as rowCounts

FROM sys.dm_db_partition_stats p

inner join sys.objects o on p.object_id = o.object_id

group by o.name

2>DBCC SHOWCONTIG(或sys.dm_db_index_physical_stats)是检查数据空间分配情况最准确的方法。

若只看数据文件整体使用情况，用DBCC SHOWFILESTATS

若要看每个对象的空间使用情况，用sys.dm_db_partition_stats

若要了解每个页、每个区的使用情况、碎片程度，用DBCC SHOWCONFIG

1.1.3 日志文件

   MSSQL数据库引擎在内部将每一物理日志文件分成多个虚拟日志单元。

   查看日志文件使用情况：

      DBCC SQLPERF(LOGSPACE)--执行不会带来负担

1.1.4 Tempdb

    sys.dm_db_file_space_usage,通过查询这张视图，能了解tempdb的空间使用情况，能知道tempdb的空间是被哪一块对象使用掉的，是用户对象(user_object_reserved_page_count字段)，还是系统对象（internal_object_reserved_page_count字段），还是版本存储区（version_store_reserved_page_count字段）

1.1.5 案例：通过脚本监视Tempdb空间使用

影响大量使用Tempdb的操作：

用户对象(user_object_reserved_page_count)

用户对象由用户显式创建。这些对象可以位于用户会话的作用域中，也可位于创建对象所用例程的作用域中。 可以是存储过程、触发器或用户定义函数。 用户对象可以是下列项之一：

• 用户定义的表和索引
• 系统表和索引
• 全局临时表和索引
• 局部临时表和索引
• table 变量
• 表值函数中返回的表

内部对象（internal_object_reserved_page_count）

内部对象是根据需要由 SQL Server 数据库引擎创建的，用于处理 SQL Server 语句。 内部对象可以在语句的作用域中创建和删除。 内部对象可以是下列项之一：

• 用于游标。
• 用于哈希联接或哈希聚合操作的查询。
• 某些 GROUP BY、ORDER BY 或 UNION 查询的中间排序结果。

版本存储（version_store_reserved_page_count）

版本存储区主要用来支持Snapshot事务隔离级别，以及SQL 2005以后推出的一些其他提高数据库并发度的新功能。

由此可见，光从用户发过来的语句本身，是很难判断这个连接的操作是否会使用Tempdb的。一个典型的例子，就是某些查询。如果表格上有良好的索引做支持，SQL Server不需要做哈希联接(Hash Join)，那这个查询就不会用Tempdb。反之，如果表格很大，又没有好的索引，那Tempdb使用量就可能不小。

tempdb空间使用的一大特点，是只有一部分对象，例如用户创建的临时表、table变量等，可以用sys.allocation_units 和 sys.partitions这样的管理视图来管理。许多内部对象和版本存储在这些管理视图里没有体现。所以，sp_spaceused的结果和真实使用会有很大差异，tempdb的空间使用是不能用sp_spaceused来跟踪的。必须借助sys.dm_db_file_space_usage这样的管理视图和管理函数，才能看到全貌。

--下面的脚本就可以监视和发现当前的Tempdb使用者,需要在使用tempdb的语句开始运行之前开始,将执行结果以文件形式保存,便于分析

USE tempdb  

-- 每隔1秒钟运行一次，直到用户手工终止脚本运行

WHILE 1 = 1 

BEGIN                                                                   

-- Query 1

-- 从文件级看tempdb使用情况

DBCC showfilestats                                                 

-- Query 2

-- 返回所有做过空间申请的session信息

SELECT 'tempdb' AS database_name , GETDATE() AS Time , SUM(user_object_reserved_page_count)/128. AS user_objects_mb ,

        SUM(internal_object_reserved_page_count)/128. AS internal_objects_mb , SUM(version_store_reserved_page_count)/128. AS version_store_kb ,

        SUM(unallocated_extent_page_count)/128. AS freespace_mb

FROM sys.dm_db_file_space_usage

WHERE database_id = 2                                                    

-- Query 3

-- 这个管理视图能够反映当时tempdb空间的总体分配

SELECT t1.session_id , t1.internal_objects_alloc_page_count , t1.user_objects_alloc_page_count , t1.internal_objects_dealloc_page_count ,

        t1.user_objects_dealloc_page_count , t3.login_time , t3.login_name , t3.host_name , t3.nt_domain , t3.nt_user_name , t3.program_name ,

        t3.status , t3.client_interface_name , t3.cpu_time , t3.memory_usage , t3.total_scheduled_time , t3.total_elapsed_time ,

        t3.last_request_start_time , t3.last_request_end_time , t3.reads , t3.writes , t3.logical_reads , t3.is_user_process , t3.row_count ,

        t3.prev_error , t3.original_security_id , t3.original_login_name , t3.last_successful_logon , t3.last_unsuccessful_logon ,

        t3.unsuccessful_logons , t3.group_id

FROM sys.dm_db_session_space_usage t1 ,                               

-- 反映每个session累计空间申请

        sys.dm_exec_sessions AS t3

-- 每个session的信息

WHERE t1.session_id = t3.session_id

        AND (

             t1.internal_objects_alloc_page_count > 0

             OR t1.user_objects_alloc_page_count > 0

             OR t1.internal_objects_dealloc_page_count > 0

             OR t1.user_objects_dealloc_page_count > 0

            )

-- Query 4

-- 返回正在运行并且做过空间申请的session正在运行的语句

SELECT t1.session_id , st.text , GETDATE()

FROM sys.dm_db_session_space_usage AS t1 ,

        sys.dm_exec_requests AS t4

CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st

WHERE t1.session_id = t4.session_id

        AND t1.session_id > 50

        AND (

             t1.internal_objects_alloc_page_count > 0

             OR t1.user_objects_alloc_page_count > 0

             OR t1.internal_objects_dealloc_page_count > 0

             OR t1.user_objects_dealloc_page_count > 0

            ) 

-- Query 5

-- 返回正在运行的活动的空间使用情况以及语句内容和执行计划 

;WITH task_space_usage AS (

    -- SUM alloc/delloc pages

    SELECT session_id,

           request_id,

           SUM(internal_objects_alloc_page_count) AS alloc_pages,

           SUM(internal_objects_dealloc_page_count) AS dealloc_pages

    FROM sys.dm_db_task_space_usage WITH (NOLOCK)

    WHERE session_id <> @@SPID

    GROUP BY session_id, request_id

)

SELECT TSU.session_id,

       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],

       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],

       EST.text,

       -- Extract statement from sql text

       ISNULL(

           NULLIF(

               SUBSTRING(

                   EST.text, 

                   ERQ.statement_start_offset / 2, 

                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END

               ), ''

           ), EST.text

       ) AS [statement text],

       EQP.query_plan

FROM task_space_usage AS TSU

INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)

    ON TSU.session_id = ERQ.session_id

    AND TSU.request_id = ERQ.request_id

OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST

OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP

WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL

ORDER BY 3 DESC, 5 DESC

WAITFOR DELAY '0:0:3'                                                    

END      

1.2 数据文件空间使用与管理

1.2.1 表和索引存储结构

   sys.partitions为表或索引中每个分区返回一行。

   堆：sys.system_internals_allocation_units first_iam_page列指向指定分区中堆数据集合的IAM链，并使用IAM页查找数据页集合中的每一页(如图1-23)。

   聚集索引：sys.system_internals_allocation_units root_page列指向指定分区内聚集索引B树的顶端，使用索引B树链表能够从顶端页面查找到分区中的每个数据页(如图1-24)。 

  非聚集索引：sys.system_internals_allocation_units root_page列指向指定分区内非聚集索引B树的顶端(如图1-25)。

1.2.2 比较存储结构对空间使用的影响

   dbcc showcontig('表名') WITH ALL_INDEXES;--查看区和页

   在相同的字段上，建立聚集索引并不会增加表格大小，而建立非聚集索引会增加空间。

1.2.3 DELETE和TRUNCATE之间的区别

  与delete比，truncate具有以下优点：

  1>所用的事务日志空间较少。delete语句每次删除一行，并在事务日志中为所删除的每行记录一个项。truncate table通过释放用于存储表数据的数据页来删除数据，并在事务日志中只记录页释放这个动作，而不记录每一行。

  2>使用的锁较少。delete 将锁定表中各行以便删除。truncate table始终锁定表和页，而不是锁定各行。

  3>表中将毫不例外地留下任何页。

1.2.4 为什么DBCC SHRINKFILE不起作用

    DBCC SHRINKFILE做的是区一级的动作，不会把页面的空间移除、合并。

1.3 日志文件不停增长

1.3.1 日志文件里到底有什么

   DBCC LOG(<db_id>,<format_id>)

   日志记录特点：

    1>日志记录的是数据的变化，而不是记录用户发过来的操作。若要通过日志推出用户发来的语句是不可能得。

    2>每条记录都有它唯一的编号(LSN)，并且记录了她属于的事务号。

    3>日志记录的行数和实际修改的数据量有关。

    4>日志记录了事务发生的时间，但是不保证记录下了发起这个事务的用户名，更不记录发起者的程序名称。

    5>MSSQL能够从日志记录里面读到数据修改前后的值。但很难了解其修改过程。

    6>日志不是做用户行为监视和记录，而是在对性能影响最小的前提下保证事务一致性。

1.3.2 日志文件增长原因

    增长原因有：

    1>数据库恢复模式不是简单模式，但是没有安排日志备份。对于非简单模式的数据库，只有做完日志备份后记录才会被截断。做完整备份和差异备份都不会起这个作用。

    2>数据库上面有一个很长时间都没有提交的事务。（做过日志备份也没用）

    3>有一个很大的事务正在运行。如建立/重建索引，或DML操作大量数据，或用户端开了一个服务器游标，但是没有把数据及时取走。

    4>数据库复制或镜像出了异常。

1.3.3 案例：日志增长原因定位

    1>检查日志现在使用情况和数据库状态。如果检查发现日志的绝大部分都在使用中（log space used）很高，说明日志记录不能被sqlserver清除掉，需要找原因；如果日志重用等待状态是log_backup，那就说明sql server等待着日志备。这时需要检查备份计划。

      -- 检查日志现在使用情况和数据库状态

      dbcc sqlperf(logspace)

      select name,recovery_model_desc,log_reuse_wait,log_reuse_wait_desc from sys.databases

    2>检查最老的活动事务

     如果日志大部分都在使用中，而且日志重用等待状态是active_transaction，那么就要看这个数据库最久未提交的事务是谁申请的。脚本如下：

  -- 检查最老的活动事务

 dbcc opentran

 go

 SELECT st.text,t2.*                                                         

from 

sys.dm_exec_sessions as t2,

sys.dm_exec_connections as t1

CROSS APPLY sys.dm_exec_sql_text(t1.most_recent_sql_handle) AS st 

where  

 t1.session_id = t2.session_id

and t1.session_id >50

 说明：其中用dbcc opentran先查询出运行中的事务情况，然后用下面的查询查询出具体事务关联的对象。然后如果是某个链接引起的，通过sessionid检查什么链接端什么情况，如果不能，直接在服务器上kill掉这个链接。比如如果sessionid为40。则:kill 40然后再运行dbcc opentran.检查下一个。依次类推。

1.4 文件自动增长和自动收缩

   设置数据库自增长要注意以下几点。

（1）要设置成按固定大小增长，而不能按比例。这样就能避免一次增长太多或者太少所带来的不

必要的麻烦。建议对比较小的数据库，设置一次增长 50 MB 到 100 MB。对大的数据库，设置一次增长

100 MB 到 200 MB。

（2）要定期监测各个数据文件的使用情况，尽量保证每个文件剩余的空间一样大，或者是期望的

比例。

（3）设置文件最大值，以免 SQL Server 文件自增长用尽磁盘空间，影响操作系统。

（4）发生自增长后，要及时检查新的数据文件空间分配情况。避免 SQL Server 总是往个别文件写

数据。

除了自动增长，数据库还有一个自动收缩的功能。如果设定了这个功能，SQL Server 每隔半个

小时就会检查文件使用情况。如果空闲空间大于 25%，SQL Server 就会自动运行 DBCC SHRINKFILE

的动作。所以这个功能能够防止数据库申请过多的空间而不使用。对一个硬盘空间很紧张的系统，

这个设置无疑是有帮助的。但是从数据库自身的健康和性能考虑，这个设置并不建议多用。这是因

为：

（1）SQL Server 只有在空间用尽的情况下才会做自动增长。如果没有找出自增长的原因，从而从

根本上避免空间用尽，虽然能够暂时用 DBCC SHRINKFILE 功能收缩文件大小，但是下次数据库还是

有可能长大。收缩数据库只是一个治标不治本的方法。

（2）数据文件收缩会给文件带来更多的碎片。

（3）不管是数据库收缩，还是增长，对 SQL Server 来讲都是件浪费资源的事情。在负载比较重的

系统里，对性能的影响尤其大。它们是应尽量避免而不是鼓励的操作。

因此，对于一个比较繁忙的数据库，推荐的设置是开启数据库自动增长选项，以防数据库空间用

尽导致应用程序失败，但是要严格避免自动增长的发生。同时，尽量不要使用自动收缩功能。