您的位置:新葡亰496net > 网络数据库 > Server远程还原,获取磁盘空间使用情况

Server远程还原,获取磁盘空间使用情况

发布时间:2019-11-15 13:01编辑:网络数据库浏览(76)

    对此DBA来讲,监察和控制磁盘使用途境是不可缺少的劳作,然后未有比较简单的不二等秘书诀能收获到磁盘空间使用率信息,下边计算下近几来攒下的剧本:

    数据库用来寄放数据,那么一定需求仓库储存空间,所以对磁盘空间的监视自然就很有十分重要了。

    1、假使备份文件xxxx.bak大小约300G,还原后所并吞的长空为900G

    最常用的查看磁盘剩余空间,这几个归于DBA入门必记的事物:

    一. 磁盘可用空间

    2、磁盘空间唯有1T,若将备份文件拷贝过来,空间剩余700G,不恐怕成功还原,因而通过远程方式还原。

    -- 查看磁盘可用空间
    EXEC master.dbo.xp_fixeddrives
    

    1. 操作系统命令或脚本、接口或工具

    事举例下:

    xp_fixeddrives方式有一点点是系统自带,可一直动用,缺点是不能够查看磁盘总大小和不能够查看SQL Server未使用到的磁盘消息

    (1) DOS命令: fsutil volume diskfree

    SQLSEQX56VE宝马X3服务实例名称: 192.168.12.163
    内需备份的数据库名称: a
    备份机器名称(Client端卡塔 尔(阿拉伯语:قطر‎: 192.168.12.30
    备份机客户: administrator 密码: 123
    备份机域名: B28-1230
    备份机提供备份必要的文书夹:D:backup

    ==============================================================

    C:windowssystem32>fsutil volume diskfree C:

    备份数据库
    率先步: 在备份机创建分享文件夹
    在程序代码中调用(也许CMD窗口卡塔尔
    net share sqlbakup=D:backup 或者用NetShareAdd这个API
    简易表达:
    net share:是WINDOWS内部的网络命令。
    成效:创立地点的分享能源,展现当前计算机的分享能源音信。
    第二步: 建构分享信用关联
    exec master..xp_cmdshell 'net use \192.168.12.30backup 123 /user:B28-1230Administrator'

    使用sys.dm_os_volume_stats函数

    Total # of free bytes        : 9789493248

    若报错提醒这几个:“SQL Server 阻止了对组件'xp_cmdshell' 的过程'sys.xp_cmdshell' 的探访,因为此组件已充任此服务器安全布局的后生可畏有的而被关门。
    系统管理员能够通过使用sp_configure 启用'xp_cmdshell'。有关启用'xp_cmdshell' 的详细音讯,请参阅SQL Server 联机丛书中的"外围应用配置器"。”

    --======================================================================
    --查看数据库文件使用的磁盘空间使用情况
    WITH T1 AS (
    SELECT DISTINCT
    REPLACE(vs.volume_mount_point,':','') AS Drive_Name ,
    CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Total_Space_GB ,
    CAST(vs.available_bytes / 1024.0 / 1024 / 1024  AS NUMERIC(18,2)) AS Free_Space_GB
    FROM    sys.master_files AS f
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
    )
    SELECT
    Drive_Name,
    Total_Space_GB,
    Total_Space_GB-Free_Space_GB AS Used_Space_GB,
    Free_Space_GB,
    CAST(Free_Space_GB*100/Total_Space_GB AS NUMERIC(18,2)) AS Free_Space_Percent
    FROM T1
    

    Total # of bytes             : 64424505344

    启用’xp_cmdshell’:
    USE master
    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE WITH OVERRIDE
    EXEC sp_configure 'xp_cmdshell', 1
    RECONFIGURE WITH OVERRIDE
    EXEC sp_Server远程还原,获取磁盘空间使用情况。configure 'show advanced options', 0
    RECONFIGURE WITH OVERRIDE

    询问功效:

    Total # of avail free bytes  : 9789493248

    关闭’xp_cmdshell’:
    USE master
    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE WITH OVERRIDE
    EXEC sp_configure 'xp_cmdshell', 0
    RECONFIGURE WITH OVERRIDE
    EXEC sp_configure 'show advanced options', 0
    RECONFIGURE WITH OVERRIDE

    图片 1

    这里运用了fsutil,贰个文件系统管理工科具(file system utility),应该还应该有任何一些命令可能脚本也是能够的。

    开启’xp_cmdshell’后再施行建构信用关系:
    exec master..xp_cmdshell 'net use \192.168.12.30backup 123 /user:B28-1230Administrator'

    sys.dm_os_volume_stats函数很好用,能一向询问到总空间和空闲空间,缺憾只帮助SQL Server 2010 Lacrosse2 SP1即更加高版本,此外不能查到数据库文件未使用到的磁盘

     

    苏醒备份库a:
    restore database peking from disk = '\192.168.12.30backupa.bak'
    with move 'a_Data' to 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAa.mdf',
    move 'a_Log' to 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAa_log.ldf'

    ==============================================================

    (2) WMI/WMIC: wmic logicaldisk

    利用 SQL Server restore filelistonly 命令来查阅逻辑文件名
    restore filelistonly from disk='\192.168.12.30backupa.bak'

    为同盟低版本,可选取xp_fixeddrives xp_cmdshell形式来获得,作者写了多少个存款和储蓄进程来赢得磁盘音信:

    WMI是个Windows系统的拘押接口,在WMIC现身早先,假如要运用WMI管理体系,必得使用部分特意的WMI应用,举个例子SMS,也许应用WMI的台本编制程序API,也许利用象CIM Studio之类的工具。假设面生C 之类的编制程序语言或VBScript之类的脚本语言,也许不精通WMI名称空间的基本知识,要用WMI处理系列是很辛勤的。WMIC改变了这种场所,它为WMI名称空间提供了二个精锐的、友好的通令行接口。

    restore with move 用法:
    restore database Northwind from disk = 'c:Northwind.bak'
    with move 'Northwind' to 'd:microsoft sql servermssql.1mssqldataNorthwind.mdf'
    ,move 'Northwind_log' to 'd:microsoft sql servermssql.1mssqldataNorthwind.ldf'

    USE [monitor]
    GO
    
    /****** Object:  StoredProcedure [dbo].[usp_get_disk_free_size]    Script Date: 2016/5/25 18:21:11 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    -- =============================================
    -- Author:        GGA
    -- Create date:    2016-2-1
    -- Description:    收集磁盘剩余空间信息
    -- =============================================
    CREATE PROCEDURE [dbo].[usp_get_disk_free_size]
    AS
    BEGIN
        SET NOCOUNT ON;
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    
    --==========================================
    --创建相关表
    
    IF OBJECT_ID('server_disk_usage') IS NULL
    BEGIN
        CREATE TABLE [dbo].[server_disk_usage](
            [disk_num] [nvarchar](10) NOT NULL,
            [total_size_mb] [bigint] NOT NULL CONSTRAINT [DF_server_disk_usage_total_size_mb]  DEFAULT ((0)),
            [free_siez_mb] [bigint] NOT NULL CONSTRAINT [DF_server_disk_usage_free_siez_mb]  DEFAULT ((0)),
            [disk_info] [nvarchar](400) NOT NULL CONSTRAINT [DF_server_disk_usage_disk_info]  DEFAULT (''),
            [check_time] [datetime] NOT NULL CONSTRAINT [DF_server_disk_usage_check_time]  DEFAULT (getdate()),
             CONSTRAINT [PK_server_disk_usage] PRIMARY KEY CLUSTERED 
            (
                [disk_num] ASC
            )
        ) ON [PRIMARY]
    END
    
    
    --==========================================
    --查看所有数据库使用到的磁盘剩余空间
    DECLARE @disk TABLE(
            [disk_num] VARCHAR(50),
            [free_siez_mb] INT)
    INSERT INTO @disk
    EXEC xp_fixeddrives
    
    --更新当前磁盘的剩余空间信息
    UPDATE M
    SET M.[free_siez_mb]=D.[free_siez_mb]
    FROM [dbo].[server_disk_usage] AS M
    INNER JOIN @disk AS D
    ON M.[disk_num]=D.[disk_num]
    
    --插入新增磁盘的剩余空间信息
    INSERT INTO [dbo].[server_disk_usage]
    (
        [disk_num],
        [free_siez_mb]
    )
    SELECT 
    [disk_num],
    [free_siez_mb]
    FROM @disk AS D
    WHERE NOT EXISTS(
        SELECT 1
        FROM [dbo].[server_disk_usage] AS M 
        WHERE M.[disk_num]=D.[disk_num] )
    
    END
    
    
    
    GO
    
    /****** Object:  StoredProcedure [dbo].[usp_get_disk_total_size]    Script Date: 2016/5/25 18:21:11 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    
    -- =============================================
    -- Author:        GGA
    -- Create date:    2016-2-1
    -- Description:    收集磁盘总空间信息
    -- =============================================
    CREATE PROCEDURE [dbo].[usp_get_disk_total_size]
    AS
    BEGIN
        SET NOCOUNT ON;
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    
    IF NOT EXISTS(SELECT * FROM [dbo].[server_disk_usage]
            WHERE [total_size_mb] = 0)
    BEGIN
        RETURN;
    END
    
    --==========================================
    --开启CMDShell
    EXEC sp_configure 'show advanced options',1;
    
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'xp_cmdshell',1;
    
    RECONFIGURE WITH OVERRIDE
    
    --========================================
    --创建临时表用来存放每个盘符的数据
    CREATE TABLE #tempDisks
    (
        ID INT IDENTITY(1,1),
        DiskSpace NVARCHAR(200)
    )
    --============================================
    --将需要检查的磁盘放入临时表#checkDisks
    SELECT 
    ROW_NUMBER()OVER(ORDER BY [disk_num]) AS RID,
    [disk_num]
    INTO #checkDisks
    FROM [dbo].[server_disk_usage] 
    WHERE [total_size_mb] = 0;
    
    --============================================
    --循环临时表#checkDisks检查每个磁盘的总量
    
    DECLARE @disk_num NVARCHAR(20)
    DECLARE @total_size_mb INT
    DECLARE @sql NVARCHAR(200)
    DECLARE @max INT
    DECLARE @min INT
    SELECT @max=MAX(RID),@min=MIN(RID) FROM #checkDisks
    
    WHILE(@min<=@max)
    BEGIN
    SELECT @disk_num=[disk_num] 
    FROM #checkDisks WHERE RID=@min
    
    SET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree ' @disk_num ':' ''''
    PRINT @sql
    
    INSERT INTO #tempDisks
    EXEC sys.sp_executesql @sql
    
    SELECT  @total_size_mb=CAST((RIGHT(DiskSpace,LEN(DiskSpace)
        -CHARINDEX(': ',DiskSpace)-1)) AS BIGINT)/1024/1024
    FROM #tempDisks WHERE id = 2
    
    SELECT @total_size_mb,@disk_num
    
    UPDATE [dbo].[server_disk_usage]
    SET [total_size_mb]=@total_size_mb
    WHERE [disk_num]=@disk_num
    
    --SELECT * FROM  #tempDisks
    
    TRUNCATE TABLE #tempDisks
    
    SET @min=@min 1
    
    END
    
    
    --==========================================
    --CMDShell
    
    EXEC sp_configure 'xp_cmdshell',0;
    
    EXEC  sp_configure 'show advanced options',1;
    
    RECONFIGURE WITH OVERRIDE;
    
    
    END
    
    
    
    
    GO
    
    /****** Object:  StoredProcedure [dbo].[usp_get_disk_usage]    Script Date: 2016/5/25 18:21:11 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:        GGA
    -- Create date:    2016-2-1
    -- Description:    收集磁盘总空间信息
    -- =============================================
    CREATE PROCEDURE [dbo].[usp_get_disk_usage]
    AS
    BEGIN
        SET NOCOUNT ON;
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    
        EXEC [dbo].[usp_get_disk_free_size]
        EXEC [dbo].[usp_get_disk_total_size]
    
        SELECT 
        [disk_num] AS Drive_Name
        ,CAST([total_size_mb]/1024.0 AS NUMERIC(18,2)) AS Total_Space_GB
        ,CAST(([total_size_mb]-[free_siez_mb])/1024.0 AS NUMERIC(18,2)) AS Used_Space_GB
        ,CAST([free_siez_mb]/1024.0 AS NUMERIC(18,2)) AS Free_Space_GB
        ,CAST([free_siez_mb]*100/[total_size_mb] AS NUMERIC(18,2)) AS Free_Space_Percent
        ,[disk_info]
        ,[check_time]
        FROM [monitor].[dbo].[server_disk_usage]
    
    END
    GO
    
    
    --==================================
    --查看磁盘空间使用
    EXEC [dbo].[usp_get_disk_usage]
    

    C:windowssystem32>wmic logicaldisk get caption,freespace,size

    轻易表明:
    1:xp_cmdshell :是SQLSE悍马H2VE瑞虎的增加存款和储蓄进度。
    效果:以操作系统命令行解释器的措施实行给定的下令字符串, 并以文本行方式赶回任何输出。
    语法:参见SQLSEEnclaveVE智跑联机协助

    功效显示:

    Caption  FreeSpace     Size

    2:net use : 是WINDOWS内部的网络命令。
    功能:将微微电脑与共享财富连接或断开,或然呈现关于计算机连接的新闻。
    该命令还调控长久互连网连接。

    图片 2

    C:       9789071360    64424505344

    3:备份数据库
    backup database a to disk='\192.168.12.30sqlbaka.bak'
    backup database a to disk='\192.168.12.30sqlbaka1.bak' WITH DIFFERENTIAL (差距备份)

    唯有第二次访问磁盘新闻或第一遍收集新磁盘音讯时,才会调用xp_cmdshell来博取磁盘的总大小,尽量裁减xp_cmdshell开启带来的高风险,可相配SQL Server Agent Job来使用,准时调用存款和储蓄进度刷新磁盘消息,监控程序直接访谈数据表来或者最终一回刷新时的磁盘消息。

    D:       189013438464  255331397632

    4: 删除分享文件夹
    在程序代码中调用(或许CMD窗口卡塔尔 net share sqlbakup/delete
    或者用NetShareDel这个API
    复原数据库
    restore database a from disk='\192.168.12.30backupa.bak'

    此形式有风姿浪漫劣点是张开xp_cmdshell后得到磁盘总大小时期,其余进程恐怕关闭xp_cmdshell,形成存款和储蓄进度实行停业,尽管发出几率极低,但到底存在。

    这里透过wmic的get命令获取了logicaldisk 的多少个参数列。

    以下是异样还原
    restore database scs from disk='\192.168.12.30backupa.bak' WITH NORECOVERY
    restore database scs from disk='\192.168.12.30backupa1.bak' WITH NORECOVERY
    restore database scs from disk='\192.168.12.30backupa2.bak'

    ==============================================================

     

    若是想跳过存款和储蓄进程 SQL Server Agent Job方式,间接通进程序来调用xp_cmdshell,当程序行使“RECONFIGURE WITH OVESportageENCOREIDE”来布局时,会报如下错误:

    (3) 性格监视器

    CONFIG statement cannot be used inside a user transaction.DB-Lib error message 574
    

    LogicalDisk: %Free Space

    张冠李戴相似于咱们在SSMS中动用工作包裹sp_configure语句,如:

    LogicalDisk: Free Megabytes

    BEGIN TRAN
    EXEC sp_configure 'show advanced options',1;
    RECONFIGURE WITH OVERRIDE;
    EXEC sp_configure 'xp_cmdshell',1;
    RECONFIGURE WITH OVERRIDE;
    COMMIT
    

    总大小 = LogicalDisk: Free Megabytes/ LogicalDisk: %Free Space

    谬误新闻为:

    质量监视器就算用于现场确诊依然挺方便的,但达成自动化监察和控制,并不太好用。

    配置选项 'show advanced options' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。
    消息 574,级别 16,状态 0,第 3 行
    在用户事务内不能使用 CONFIG 语句。
    配置选项 'xp_cmdshell' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。
    消息 574,级别 16,状态 0,第 5 行
    在用户事务内不能使用 CONFIG 语句。
    

     

    难道说不可能通进度序调用RECONFIGURE WITH OVELAND本田CR-VIDE语句?

    2. SQL 语句

    理所必然不是,google下有关错误,仅开采下边一个相关,有意思味的能够参见下:

    (1) 扩充存款和储蓄进度xp_cmdshell (依然在调用操作系统命令)

    DECLARE @Drive TINYINT,
          @SQL VARCHAR(100)
    DECLARE @Drives TABLE
    (
    Drive CHAR(1),
    Info VARCHAR(80)
    )
    
    SET @Drive = 97
    WHILE @Drive <= 122
    BEGIN
        SET @SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree '   CHAR(@Drive)   ':'''
    
        INSERT @Drives
        (
        Info
        )
        EXEC(@SQL)
    
        UPDATE @Drives
           SET Drive = CHAR(@Drive)
         WHERE Drive IS NULL
    
        SET @Drive = @Drive   1
    END
    
    SELECT Drive,
    SUM(CASE WHEN Info LIKE 'Total # of bytes%' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END)/1024.0/1024/1024 AS TotalMBytes,
    SUM(CASE WHEN Info LIKE 'Total # of free bytes%' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END)/1024.0/1024/1024 AS FreeMBytes,
    SUM(CASE WHEN Info LIKE 'Total # of avail free bytes%' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END)/1024.0/1024/1024 AS AvailFreeMBytes
    FROM(
    SELECT Drive,
           Info
      FROM @Drives
     WHERE Info LIKE 'Total # of %'
    ) AS d
    GROUP BY Drive
    ORDER BY Drive
    

    简单看了下,使用存款和储蓄进度套存款和储蓄进度的点子来绕过报错,本身从未切实可行测量试验,以为太繁缛,于是利用简易严酷的诀窍,既然报“在客户业务内不可能采纳CONFIG 语句”,哪小编是否能够先COMMIT下干掉“顾客业务”呢?

    xp_cmdshell能够进行操作系统命令行,这段脚本用fsutil volume diskfree命令对30个假名的盘符遍历了叁回,不是很好,改用wmic会方便些,如下:

    借助此思路,末了测量检验得到下边方式:

    EXEC xp_cmdshell 'wmic logicaldisk get caption,freespace,size';
    
    DECLARE @sql VARCHAR(2000)
    SET @sql ='
    COMMIT;
    EXEC sp_configure ''show advanced options'',1;
    RECONFIGURE WITH OVERRIDE;
    EXEC sp_configure ''xp_cmdshell'',1;
    RECONFIGURE WITH OVERRIDE;
    '
    EXEC(@sql)
    

     

    全面的爱侣开采自家先执行了COMMIT, 您没看错,那样的张开药格局固然古怪但实乃大器晚成种展开药方式,在SSMS中实施结果为:

    (2) 恢宏存款和储蓄进度xp_fixeddrives

    消息 3902,级别 16,状态 1,第 2 行
    COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION。
    配置选项 'show advanced options' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。
    配置选项 'xp_cmdshell' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。
    
    --exec xp_fixeddrives
    IF object_id('tempdb..#drivefreespace') IS NOT NULL
        DROP TABLE #drivefreespace
    CREATE TABLE #drivefreespace(Drive CHAR(1), FreeMb bigint)
    INSERT #drivefreespace EXEC ('exec xp_fixeddrives')
    SELECT * FROM #drivefreespace
    

    就算报错,可是的只是,xp_cmdshell的值已经被设置为1,即脚本施行生效啦!

    Drive

    FreeMb

    C

    9316

    D

    180013

    将此代码移植到代码中,然后经过TTiggoY CATCH将那些捕获并遗弃,你就能够欢腾地调用xp_cmdshell啦。

     

    ==============================================================

     

    使用xp_cmdshell开了头,当然相关音信也足以运用相像方法来收获啦!

     

    比方说获取磁盘的扇区音讯:

     

    --====================================
    --使用xp_cmdshell来执行CMD命令
    --获取磁盘扇区信息
    EXEC sp_configure 'show advanced options',1 
    GO
    RECONFIGURE
    GO
    sp_configure 'xp_cmdshell',1 
    GO
    RECONFIGURE
    GO
    EXEC xp_cmdshell 'fsutil fsinfo ntfsinfo D: | find "每个"';
    GO
    sp_configure 'xp_cmdshell',0 
    GO
    RECONFIGURE
    GO
    sp_configure 'show advanced options', 0 
    GO
    RECONFIGURE
    GO
    

     

    运营效果为:

    归根结底不依据操作系统命令了,可是,这几个蕴藏进程只可以回到磁盘可用空间,未有磁盘总空间。

    图片 3

     

    自然你可以利用fsutil fsinfo ntfsinfo D:来博取完整新闻,可是更值得您关怀的就是下边这几行。

    (3) DMV/DMF: sys.dm_os_volume_stats

    ==============================================================

    SELECT DISTINCT
           @@SERVERNAME as [server]
          ,volume_mount_point as drive
          ,cast(available_bytes/ 1024.0 / 1024.0 / 1024.0 AS INT) as free_gb
          ,cast(total_bytes / 1024.0 / 1024.0 / 1024.0 AS INT) as total_gb
    FROM sys.master_files AS f
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
    ORDER BY @@SERVERNAME, volume_mount_point
    

    感言:

    server

    drive

    free_gb

    total_gb

    C:

    9

    59

    D:

    175

    237

    当了这么经过了相当长的时间的SQL Server DBA,未来找份像样的SQL SEKugaVETiguanDBA的做事真不轻巧,一方面是近日商场趋向招致,另一面也是笔者DBA自身“作死”变成的,见到众多同行李包裹罗自我要好都还处在“茹毛饮血”时期,不寻常就在分界面上点来点去,给外界风度翩翩种“SQL Server超轻松运转”的假象,而再看看MySQL DBA,只要您能粉饰太平“研商下源码”,立马给人豆蔻梢头种“很牛逼”的赶脚,于是乎年收入三三十万不再是期望!

     

    智能运营的口号已经吹响,在转MySQL的途中,仍全日不要忘自身是个老SQL Server DBA。

     

    ==============================================================

     

    蒋厅长的字,与各位共勉!

     

    图片 4

     

    从SQL Server 二零一零 R2 SP1在这里之前,有了那么些很好用的DMF: sys.dm_os_volume_stats,弥补了事先xp_fixeddrives未有磁盘总空间的阙如。

    只是,看它的参数就足以知道,没被别的数据库使用的磁盘,是查看不了的,所以xp_fixeddrives还应该有存在的药到回春。

     

    二. 数据库可用空间

    1. 文本可用空间查看

    (1) 文本已用空间,当前高低(已分配空间),最大值,如下:

    select @@SERVERNAME as server_name
          ,DB_NAME() as database_name
          ,case when data_space_id = 0 then 'LOG'
                else FILEGROUP_NAME(data_space_id) 
                end as file_group
          ,name as logical_name
          ,physical_name
          ,type_desc
          ,FILEPROPERTY(name,'SpaceUsed')/128.0 as used_size_Mb
          ,size/128.0 as allocated_size_mb 
          ,case when max_size = -1 then max_size 
                else max_size/128.0 
                end as max_size_Mb
          ,growth
          ,is_percent_growth
     from sys.database_files
    where state_desc = 'ONLINE'
    

     

    (2) 再算上磁盘的空余空间,改正如下:

    select @@SERVERNAME as server_name
          ,DB_NAME() as database_name
          ,case when data_space_id = 0 then 'LOG'
                else FILEGROUP_NAME(data_space_id) 
                end as file_group
          ,name as logical_name
          ,physical_name
          ,type_desc
          ,FILEPROPERTY(name,'SpaceUsed')/128.0 as used_size_mb
          ,size/128.0 as allocated_size_mb
          ,case when max_size = -1 then max_size 
                    else max_size/128.0 
                    end as max_size_mb
          ,vs.available_bytes/1024.0/1024 as disk_free_mb
          ,growth
          ,CAST(is_percent_growth as int) as is_percent_growth
    from sys.database_files df
    cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vs
    where state_desc = 'ONLINE'
    

     假使是SQL Server 2008SP1在此以前的版本,可用xp_fixeddrives生成磁盘空闲空间表,再开展关联。

     

    (3) 结缘文件是不是自拉长,文件最大值,磁盘空间,算出文件可用空间比率,更换如下:

    select @@SERVERNAME as server_name
          ,DB_NAME() as database_name
          ,case when data_space_id = 0 then 'LOG'
                else FILEGROUP_NAME(data_space_id) 
                end as file_group
          ,name as logical_name
          ,physical_name
          ,type_desc
          ,FILEPROPERTY(name,'SpaceUsed')/128.0 as used_size_mb
          ,size/128.0 as allocated_size_mb
          ,case when max_size = -1 then max_size 
                    else max_size/128.0 
                    end as max_size_mb
          ,vs.available_bytes/1024.0/1024 as disk_free_mb
          ,case when growth = 0 then  (size - FILEPROPERTY(name,'SpaceUsed'))*1.0/size
                when growth > 0 and max_size = -1 then ((size/128.0   vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,'SpaceUsed')/128.0)/(size/128.0   vs.available_bytes/1024.0/1024)
                when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) >= 0 then ((size/128.0   vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,'SpaceUsed')/128.0)/(size/128.0   vs.available_bytes/1024.0/1024)
                when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) <  0 then (max_size - FILEPROPERTY(name,'SpaceUsed'))*1.0/max_size
                else null 
                end as free_space_percent
          ,growth
          ,CAST(is_percent_growth as int) as is_percent_growth
    from sys.database_files df
    cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vs
    where state_desc = 'ONLINE'
    

     

    (4) 假如有多少个数据库,注意fileproperty()和filegroup_name()函数,都只在近些日子数据库下生效,退换如下:

    if object_id('tempdb..#tmp_filesize') is not null
        drop table #tmp_filesize
    GO
    create table #tmp_filesize
    (
    server_name          varchar(256),
    database_name        varchar(256),
    file_group           varchar(256),
    logical_name         varchar(256),
    physical_name        varchar(1024),
    type_desc            varchar(128),
    used_size_mb         float,
    allocated_size_mb    float,
    max_size_mb          float,
    disk_free_mb         float,
    free_space_percent   float,
    growth               int,
    is_percent_growth    int
    )
    GO
    
    exec sp_msforeachdb 'use [?]  
    insert into #tmp_filesize
    select @@SERVERNAME as server_name
          ,DB_NAME() as database_name
          ,case when data_space_id = 0 then ''LOG''
                else FILEGROUP_NAME(data_space_id) 
                end as file_group
          ,name as logical_name
          ,physical_name
          ,type_desc
          ,FILEPROPERTY(name,''SpaceUsed'')/128.0 as used_size_mb
          ,size/128.0 as allocated_size_mb
          ,case when max_size = -1 then max_size 
                    else max_size/128.0 
                    end as max_size_mb
          ,vs.available_bytes/1024.0/1024 as disk_free_mb
          ,case when growth = 0 then  (size - FILEPROPERTY(name,''SpaceUsed''))*1.0/size
                when growth > 0 and max_size = -1 then ((size/128.0   vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,''SpaceUsed'')/128.0)/(size/128.0   vs.available_bytes/1024.0/1024)
                when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) >= 0 then ((size/128.0   vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,''SpaceUsed'')/128.0)/(size/128.0   vs.available_bytes/1024.0/1024)
                when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) <  0 then (max_size - FILEPROPERTY(name,''SpaceUsed''))*1.0/max_size
                else null 
                end as free_space_percent
          ,growth
          ,CAST(is_percent_growth as int) as is_percent_growth
    from sys.database_files df
    cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vs
    where state_desc = ''ONLINE'''
    
    select * from #tmp_filesize
    

     

    2. 数据库可用空间告急

    2.1 报告急察方的格式

    数据库可用空间告急,平时不报告警察方有些文件,也不报警整个数据库,而是某些确切的文本组/表空间,日志文件是从未公文组的,全体能够把日志文件归拢为LOG这几个组。

    (1) Oracle能够给表空间设置最大尺寸,表空间里的各种文件相继使用,直到末了一个文件也没空间时,就能够提醒空间不足;

    (2) SQL Server 不可能对文件组织设立置最大尺寸,只好给文件组里各样文件钦命最大尺寸,所以要先总括:是不是当前文件组下全数的公文都早已满了?

    将同叁个文件组/LOG下的具有文件都检查一下,如若具备文件都满了(以三分之一为例),那么就满意告急条件了,如下:

    --#tmp_filesize 在上面的脚本里生成了
    select server_name,
           database_name,
           file_group,
           MAX(free_space_percent) as max_free_space_percent
      from #tmp_filesize
     group by server_name,database_name,file_group
     having MAX(free_space_percent) <= 0.2 --20%
    

    邮件告急的格式大概为:

    邮件标题:主机名实例名数据库名文本组名,@@servername已经包涵了SQL Server实例名;

    邮件内容:文件组 ”file group name” 空间不足,已低于十分之二。

     

    2.2 报警后怎么管理?

    (1) 报警中的文件组里的公文,所在的磁盘还应该有空间吗?

    exec xp_fixeddrives
    

    假定当前磁盘没空间,能够给当下文件组在其余磁盘上增多新的文书,并关闭老的文书自拉长或约束最大值;

    生龙活虎旦具有磁盘都没空间,能够假造删除磁盘上的任何文件,或许减少数据库文件(数据/日志),或然磁盘扩展空间(加磁盘)。

     

    (2) 固然磁盘有空中,文件是还是不是关闭了机关增加?

    恐怕是在开立文件时,给了文件非常的大的size,如500G,并关闭了文本自动拉长;

    ALTER DATABASE test
    ADD FILE 
    (
        NAME = test_02,
        FILENAME = 'D:Program Files (x86)Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAtest_02.ndf',
        SIZE = 500 GB,
        FILEGROWTH = 0
    )
    TO FILEGROUP [PRIMARY];
    GO
    

     

    (3) 比如磁盘有空间,自动增加也开了,是或不是约束了文件最大值?

    范围最大值和破产自增进,应该都以不想单个文件变得太大,个人认为一个文本决定在500G以内相比客观,那二种情况,都建议扩充学一年级个新文件。

     

    小结

    假如没有监察和控制工具,那么可选拔系统视图,扩充存款和储蓄进度,结合数据库邮件的措施,作活动物检疫查,并报告急察方文件组/日志空闲空间不足。差相当的少步骤如下 :

    (1) 铺排数据库邮件;

    (2) 陈设作业:依期检查文件组/日志空闲空间,发邮件告急。

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:Server远程还原,获取磁盘空间使用情况

    关键词:

上一篇:哈希索引,独一索引和日常索引的分别

下一篇:没有了