Sqlserver 各表、各数据文件占用空间大小 ，缩减日志文件

 

--更新统计信息

exec sp_updatestats

--各数据文件大小

SELECT DB_NAME() AS DbName,name AS FileName,size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files

=====搜集信息

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table tablespaceinfo
(nameinfo varchar(50) ,
rowsinfo int , reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20) )

delete from tablespaceinfo --
declare @tablename varchar(255) --
declare @cmdsql varchar(500)
DECLARE Info_cursor CURSOR FOR
select o.name
from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1
and o.name not like N'#%%' order by o.name OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute sp_executesql
N'insert into tablespaceinfo exec sp_spaceused @tbname',
N'@tbname varchar(255)',
@tbname = @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO

--knowsky.com 数据使用情况
sp_spaceused @updateusage = 'TRUE'
--各表大小
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc

 

--缩减日志文件

1 备份数据文件  任务-备份--选择事务日志--确认   （只有备份了，可用空间才会变大）

2属性-选项-恢复模式-简单  （改为简单模式 可以理解为 切断了日志）

3 缩减日志文件  任务-收缩-文件--文件类型选择日志（可以看到当前空间--占用的磁盘大小、可用空间--占用了但是没有数据的大小）-确定   

4属性-选项-恢复模式-完整

--缩减日志文件

USE [master]
GO
ALTER DATABASE ncdb SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE ncdb SET RECOVERY SIMPLE
GO
USE ncdb
GO
DBCC SHRINKFILE (N'NCDB_log' , 0,TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE ncdb SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE ncdb SET RECOVERY FULL
GO
ALTER DATABASE ncdb MODIFY FILE ( NAME = N'NCDB_log', MAXSIZE = 40240,filegrowth=2%)

 

转载于:https://www.cnblogs.com/qtong/p/9453814.html