windows server服务器（sqlserver服务） C盘满了的一次排查及处理

windows server服务器（sqlserver服务） C盘满了的一次排查及处理

服务器的C盘爆红，由于给C盘扩容太麻烦，开始排查

忽然发现服务器的C盘爆红，由于给C盘扩容太麻烦，开始排查怎么会满了，明明之前还好。从以下开始排查

1. 从服务器的日志方面开始排查，删除了一些日志（直接删除失败了，使用事件查看器删除日志）

搜到说系统的日志，C:\\Windows\\System32\\winevt\\Logs下的可以删除，但是删除提示说已经在winevt打开了，无法删除，后搜了，发现需要在事件查看器中删除，于是开始慢慢删除。删除完，发现效果不明显，C盘还是红的。

2. 删除日志无法解决，想到了sqlserver是不是索引碎片太多导致的，开始处理索引碎片。

查询索引碎片

SELECT i.name AS indexname, o.name AS tablename, s.name AS schemaname, f.index_type_desc AS indextype,
f.avg_page_space_used_in_percent AS indexdensity, f.avg_fragmentation_in_percent AS indexfragmentation,
f.page_count AS pages FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') f
INNER JOIN sys.objects o ON o.object_id = f.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.indexes i ON f.object_id = i.object_id AND f.index_id = i.index_id
WHERE page_count > 50 AND f.index_id > 0
ORDER BY f.avg_fragmentation_in_percent desc

发现碎片率很高，然后，准备停服务，处理碎片。

— Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @sql nvarchar(4000);
DECLARE @dbid INT
SET @dbid=DB_ID()

— Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
— and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

— Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

— Open the cursor.
OPEN partitions;

— Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

SET @sql = 'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' SET ( ALLOW_PAGE_LOCKS = ON );'
— 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

PRINT @objectname + @indexname + '碎片' + CAST(@frag AS varchar(20))
PRINT @command + '——————–start'
EXEC (@sql) –执行 ALLOW_PAGE_LOCKS
EXEC (@command) –执行
PRINT @command + '——————–END'
END;

— Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

— Drop the temporary table.
DROP TABLE #work_to_do;

运行以上，发现报错说表不存在，先不执行最后一句删除这个临时表，执行前面的索引重构sql，一段时间后成功，删除临时表。再运行查询碎片率的sql，可以看到碎片率下降了。
此时，再看C盘，发现C盘空间变化不明显。
继续排查。。。

3. 查看sqlserver的data目录，发现居然有50多个G，然后查看发现有个大文件tempdb.mdf，居然有12G

搜索了一下，发现 tempdb.mdf是临时库，于是考虑能不能移动临时库呢，搜索后发现可以，于是 迁移tempdb数据文件
（1）查看当前数据文件逻辑名称与数据文件位置：

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

果然看到显示文件是在C盘的SQL Server的data目录下的。
（2）运行sql 更改文件位置：
切换到master库

USE master;

然后修改文件位置：

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\\DATA\\tempdb.mdf');

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\\DATA\\templog.ldf');

之后再查看文件位置，发现已经改变了。
（3）重启sqlserver

(4)可以看到新路径下有
tempdb.mdf和templog.ldf文件生成了。
数据库的状态也正常，启动自己的服务，没发现问题。
（5）（先备份tempdb.mdf和templog.ldf到其他盘，【也可以不备份，保险起见先备份了】），然后删除C盘的sql server的data目录下的这2个文件。系统和数据库运行一切正常，可以删除备份文件了。
此时，C盘终于还剩12G了，不再爆红了！！！

此次处理告一段落，下次再出现，再根据情况解决。