MS Sql Server查询数据库文件大小和剩余空间，数据库日志压缩

A、磁盘空间的使用情况及各数据库数据文件及日志文件的大小及使用利用率

在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)

5、一次性的查询数据库中所有的表的大小

-- =============================================
--
--  更新查询数据库中各表的大小，结果存储到数据表中
--  
--  运行方式： EXEC sp_UpdateTableSpaceInfo
--  结果查询： SELECT * FROM temp_tableSpaceInfo
--
-- =============================================
create procedure [dbo].[sp_UpdateTableSpaceInfo]
    
AS
begin
    --查询是否存在结果存储表
    if not exists (select * from sysobjects where id = object_id(N'temp_tableSpaceInfo') AND objectproperty(id, N'IsUserTable') = 1)
    begin
        --不存在则创建
        create table temp_tableSpaceInfo
        (name nvarchar(128), 
        rows char(11), 
        reserved varchar(18),
        data varchar(18),
        index_size varchar(18),
        unused varchar(18))
    end
    --清空数据表
    delete from temp_tableSpaceInfo

    --定义临时变量在遍历时存储表名称
    declare @tablename varchar(255)

    --使用游标读取数据库内所有表表名
    declare table_list_cursor cursor for --申明游标
    select name from sysobjects 
    where objectproperty(id, N'IsTable') = 1 and name not like N'#%%' order by name

    --打开游标
    open table_list_cursor
    --将提取结果代入游标
    fetch next from table_list_cursor into @tablename 

    --遍历查询到的表名
    while @@fetch_status = 0 --最近一条FETCH语句的标志
    begin
        --检查当前表是否为用户表
        if exists (select * from sysobjects where id = object_id(@tablename) AND objectproperty(id, N'IsUserTable') = 1)
        begin
            --当前表则读取其信息插入到表格中
            execute sp_executesql N'insert into temp_tableSpaceInfo exec sp_spaceused @tbname', N'@tbname varchar(255)', @tbname = @tablename
        end
        --读取下一条数据
        fetch next from table_list_cursor into @tablename 
    end

    --释放游标
    close table_list_cursor --解除游标
    deallocate table_list_cursor --将游标内容代入最后结果
end

GO

B、数据库日志收缩

1、查看当前的存放位置

--查看当前的存放位置 
select database_id,name,physical_name AS CurrentLocation,state_desc,size from sys.master_files  
where database_id=db_id(N'数据库名'); 
  

2、修改文件的存放位置（下次启动生效 ）

--修改文件的存放位置下次启动生效 
--testDb为数据库名， 
alter database 数据库名 modify file ( name = 文件名(不包含后缀), filename = '文件存储路径'); 
alter database 数据库名 modify file ( name = 文件名(不包含后缀), filename = '文件存储路径'); 
eg. 
  alter database testDb modify file ( name = testDb, filename = 'G:\SQL_DATA\testDb\testDb.mdf'); 
  alter database testDb modify file ( name = testDb_log, filename = 'G:\SQL_DATA\testDb\testdb_log.ldf'); 
  

3、修改默认的数据库文件存放位置（即时生效）

--修改默认的数据库文件存放位置（即时生效） 
EXEC xp_instance_regwrite  
@rootkey='HKEY_LOCAL_MACHINE',  
@key='Software\Microsoft\MSSQLServer\MSSQLServer',  
@value_name='DefaultData',  
@type=REG_SZ,  
@value='E:\MSSQL_MDF\data' 
GO  

4、修改默认的日志文件存放位置 （即时生效）

--修改默认的日志文件存放位置 （即时生效）
EXEC master..xp_instance_regwrite  
@rootkey='HKEY_LOCAL_MACHINE',  
@key='Software\Microsoft\MSSQLServer\MSSQLServer',  
@value_name='DefaultLog',  
@type=REG_SZ,  
@value='E:\MSSQL_MDF\log' 
GO

5、日志压缩步骤

1）、修改数据库为 简单模式

-- 修改数据库为 简单模式
USE[master]
    GO
    
    ALTER DATABASE dbstore-pole-an SET RECOVERY SIMPLE WITH NO_WAIT
    GO
    
    ALTER DATABASE dbstore-pole-an SET RECOVERY SIMPLE
    GO

2）、收缩相关数据库的指定数据文件或日志文件大小

-- 收缩相关数据库的指定数据文件或日志文件大小
USE dbstore-pole-an 
DBCC SHRINKFILE (N'dbstore-pole-an_log' , 700)

3）、还原数据库为 完全模式

--还原数据库为 完全模式
USE[master]
    GO

    ALTER DATABASE dbstore-pole-an SET RECOVERY FULL WITH NO_WAIT
    GO

    ALTER DATABASE dbstore-pole-an SET RECOVERY FULL
    GO

4）、查询收缩日志后数据库信息

-- 查询收缩日志后数据库信息
	select database_id,name,physical_name AS CurrentLocation,state_desc,size from sys.master_files  
where database_id=db_id(N'dbstore-pole-an');