日韩VS欧美国产在线观看,高清女人不卡免费视频

前幾天,在所有數(shù)據(jù)庫(kù)服務(wù)器部署了監(jiān)控磁盤(pán)空間的存儲(chǔ)過(guò)程和作業(yè)后（MS SQL 監(jiān)控磁盤(pán)空間告警），今天突然收到了兩封告警郵件，好吧，存儲(chǔ)規(guī)劃是一方面，但是，是不是要分析一下是什么原因造成磁盤(pán)空間不足的呢？會(huì)不會(huì)是因?yàn)橥蝗槐┰龅娜罩疚募只蚴窍到y(tǒng)業(yè)務(wù)猛增導(dǎo)致數(shù)據(jù)量暴增，還是歷史數(shù)據(jù)累計(jì)原因....分析總得有數(shù)據(jù)來(lái)支撐吧，但是現(xiàn)在只有那些數(shù)據(jù)文件的當(dāng)前大小信息，沒(méi)有數(shù)據(jù)文件的歷史增長(zhǎng)變化信息，所以，今天就想實(shí)現(xiàn)這么一個(gè)功能，每天（頻率可以調(diào)整）去收集一下數(shù)據(jù)文件的信息，放到一個(gè)表里面，這樣方便我們分析數(shù)據(jù)文件的增長(zhǎng)演變例程，甚至你可以將數(shù)據(jù)文件的增長(zhǎng)幅度和業(yè)務(wù)變化關(guān)聯(lián)起來(lái)分析....

那么接下來(lái)就是我的設(shè)計(jì)思路和實(shí)現(xiàn)代碼，目前只是簡(jiǎn)單實(shí)現(xiàn)，以后將繼續(xù)優(yōu)化,豐富一些功能。

首先我們創(chuàng)建一個(gè)表DiskCapacityHistory，用來(lái)保存數(shù)據(jù)庫(kù)文件的歷史增長(zhǎng)變化信息：

復(fù)制代碼代碼如下:

USE msdb;
GO

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'') AND xtype='U')
DROP TABLE DiskCapacityHistory;
GO

CREATE TABLE dbo.DiskCapacityHistory
(
    [Date_CD]            INT                     ,
    [DataBaseID]         INT                     ,
    [FileID]             INT                     ,
    [DataBaseName]       sysname                 ,
    [LogicalName]        VARCHAR(32)             ,
    [FileTypeDesc]       NVARCHAR(60)            ,
    [PhysicalName]       NVARCHAR(260)         　,
    [StateDesc]          NVARCHAR(60)         　 ,
    [MaxSize]            NVARCHAR(32)            ,
    [GrowthType]         NVARCHAR(8)             ,
    [IsReadOnly]         INT                     ,
    [IsPercentGrowth]    SMALLINT                ,
    [Size]               FLOAT                   ,
    [Growth_MOM_RAT]     FLOAT                   ,
    [Growth_YOY_RAT]     FLOAT                   ,
    CONSTRAINT PK_DiskCapacityHistory PRIMARY KEY(Date_CD, DataBaseID, FileID)
);

復(fù)制代碼代碼如下:

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '日期編碼'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'Date_CD';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '數(shù)據(jù)庫(kù)標(biāo)識(shí)'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'DataBaseID';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '文件標(biāo)識(shí)'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'FileID';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '數(shù)據(jù)庫(kù)名稱(chēng)'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'DataBaseName';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '數(shù)據(jù)庫(kù)邏輯名稱(chēng)'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'LogicalName';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '文件類(lèi)型描述'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'FileTypeDesc';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '物理數(shù)據(jù)庫(kù)文件'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'PhysicalName';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '文件最大大小'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'MaxSize';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '文件增長(zhǎng)類(lèi)型'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'GrowthType';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '是否只讀類(lèi)型'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'IsReadOnly';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '是否按百分比增長(zhǎng)'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'IsPercentGrowth';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '數(shù)據(jù)文件大小(GB)'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'Size';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '文件增長(zhǎng)環(huán)比(%)'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'Growth_MOM_RAT';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '文件增長(zhǎng)同比(%)'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'Growth_YOY_RAT';

GO

IF OBJECT_ID(N'sp_diskcapacity_cal') IS NOT NULL
DROP PROCEDURE sp_diskcapacity_cal;
GO

接下來(lái)，我們創(chuàng)建存儲(chǔ)過(guò)程，負(fù)責(zé)來(lái)收集、統(tǒng)計(jì)這些數(shù)據(jù)庫(kù)的文件的相關(guān)信息。關(guān)于環(huán)比/同比,正常情況一般是:
環(huán)比: (指標(biāo)當(dāng)前值 - 指標(biāo)值(上個(gè)月同一天))/ 指標(biāo)值(上個(gè)月同一天) 。
同比: (指標(biāo)當(dāng)前值 - 指標(biāo)值(去年月同一天))/ 指標(biāo)值(去年月同一天) 。
其實(shí)如果關(guān)注每天的數(shù)據(jù)文件變化情況，這個(gè)代碼里面的環(huán)比、同比其實(shí)意義不大，其實(shí)我們可以這樣定義環(huán)比、同比：
環(huán)比：（指標(biāo)當(dāng)前值 - 指標(biāo)值（昨天）)/指標(biāo)值（昨天）。
同比：（指標(biāo)當(dāng)前值 - 指標(biāo)值（上個(gè)月））/指標(biāo)值（上個(gè)月）
當(dāng)然，你也可以把這四個(gè)指標(biāo)都加上，對(duì)比參考，側(cè)重點(diǎn)不同而已。

復(fù)制代碼代碼如下:

IF  OBJECT_ID(N'sp_diskcapacity_cal')IS NOT NULL
    DROP PROCEDURE sp_diskcapacity_cal;
GO

CREATE PROCEDURE dbo.sp_diskcapacity_cal
AS
BEGIN

   INSERT INTO dbo.DiskCapacityHistory
   (
        [Date_CD]           ,
        [DataBaseID]        ,
        [FileID]            ,
        [DataBaseName]      ,
        [LogicalName]       ,
        [FileTypeDesc]      ,
        [PhysicalName]      ,
        [StateDesc]         ,
        [MaxSize]           ,
        [GrowthType]        ,
        [IsReadOnly]        ,
        [IsPercentGrowth]   ,
        [Size]                
   )
     SELECT CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT)
                                                                            AS DateCD        ,
            database_id                                                     AS DataBaseId    ,
            file_id                                                         AS FileID        ,
            DB_NAME(database_id)                                            AS DataBaseName  ,
            name                                                            AS LogicalName   ,
            type_desc                                                       AS FileTypeDesc  ,
            physical_name                                                   AS PhysicalName  ,
            state_desc                                                      AS StateDesc     ,
            CASE WHEN max_size = 0 THEN N'不允許增長(zhǎng)'
                 WHEN max_size = -1 THEN N'自動(dòng)增長(zhǎng)'
                 ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G'
            END                                                             AS MaxSize       ,
            CASE WHEN is_percent_growth = 1
                 THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'
                 ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M'
            END                                                             AS Growth        ,
            Is_Read_Only AS IsReadOnly ,
            Is_Percent_Growth AS IsPercentGrowth ,
            CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4))                 AS Size
     FROM   sys.master_files;

     
     MERGE INTO dbo.DiskCapacityHistory DM USING
     (
     SELECT M.Date_CD        ,
            M.DataBaseID     ,
            M.FileID         ,
            CASE WHEN N.SIZE IS NULL OR N.SIZE = 0 THEN 0 ELSE
                (M.SIZE - N.SIZE)/N.SIZE END AS Growth_MOM_RAT
     FROM dbo.DiskCapacityHistory M
      LEFT JOIN dbo.DiskCapacityHistory  N ON
              CAST(CAST(M.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH, 1, CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
          AND M.DataBaseID = N.DataBaseID AND M.FileID = N.FileID
     WHERE M.Date_CD =  CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT)
     ) TMP
     ON
     (
            DM.Date_CD       = TMP.Date_CD     AND
            DM.DatabaseId    = TMP.DataBaseId  AND
            DM.FileId        = TMP.FileId
     )
     WHEN MATCHED THEN UPDATE SET
        DM.Growth_MOM_RAT = TMP.Growth_MOM_RAT;
END    
GO

順便吐槽一下：由于前兩年一直使用ORACLE數(shù)據(jù)庫(kù)，很少接觸SQL SERVER，在實(shí)現(xiàn)上面功能的時(shí)候，我深深的體會(huì)到了ORACLE和SQL SERVER的巨大差距，如果用PL/SQL實(shí)現(xiàn)，那非常方便快捷，但是用T-SQL讓我遇到了幾個(gè)相當(dāng)痛苦地方，下面順便記錄對(duì)比一下吧：

一：由于我采用INT來(lái)保存日期數(shù)據(jù)，那么需要在DATE類(lèi)型和INT類(lèi)型之間轉(zhuǎn)換，我們來(lái)對(duì)比一下兩者的差別吧：

1.1 DATE類(lèi)型轉(zhuǎn)換為整型:

T-SQL：

SELECT CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT);

PL/SQL：

SELECT TO_CHAR(Date_CD, 'YYYYMMDD') FROM DUAL;

1.2 整型轉(zhuǎn)換為DATE類(lèi)型（字段DATE_CD）

T-SQL：

SELECT CAST(CAST(DATE_CD AS CHAR(8)) AS DATE) FROM TEST;

PL/SQL：

SELECT TO_DATE(DATE_CD, 'YYYY-MM-DD') FROM TEST;

結(jié)論: 純屬個(gè)人感受,從上面的腳本的簡(jiǎn)單性,方便性上,感覺(jué)ORACLE完勝SQL SERVER

二：計(jì)算數(shù)據(jù)文件增長(zhǎng)同比、環(huán)比值

1：SQL SERVER 2005 沒(méi)有MERGE語(yǔ)句功能，上面的腳本得改寫(xiě)成

復(fù)制代碼代碼如下:

UPDATEdbo.DiskCapacityHistory
 SET     GROWTH_MOM_RAT =( SELECTCASE WHEN N.SIZE IS NULL
                                            OR N.SIZE = 0 THEN 0
                                       ELSE ( dbo.DiskCapacityHistory.SIZE
                                              - N.SIZE ) / N.SIZE
                                  END AS Growth_MOM_RAT
                         FROM     dbo.DiskCapacityHistory N
                         WHERE    CAST(CAST(dbo.DiskCapacityHistory.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH,
                                                            1,
                                                            CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
                                  AND dbo.DiskCapacityHistory.DataBaseID = N.DataBaseID
                                  AND dbo.DiskCapacityHistory.FileID = N.FileID
                       )
 WHEREdbo.DiskCapacityHistory.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120),
                                                     '-', '') AS INT)

UPDATEdbo.DiskCapacityHistory
 SET     GROWTH_YOY_RAT =( SELECTCASE WHEN N.SIZE IS NULL
                                            OR N.SIZE = 0 THEN 0
                                       ELSE ( dbo.DiskCapacityHistory.SIZE
                                              - N.SIZE ) / N.SIZE
                                  END AS Growth_YOY_RAT
                         FROM     dbo.DiskCapacityHistory N
                         WHERE    CAST(CAST(dbo.DiskCapacityHistory.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH,
                                                            12,
                                                            CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
                                  AND dbo.DiskCapacityHistory.DataBaseID = N.DataBaseID
                                  AND dbo.DiskCapacityHistory.FileID = N.FileID
                       )
 WHEREdbo.DiskCapacityHistory.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120),
                                                     '-', '') AS INT)

或

復(fù)制代碼代碼如下:

CREATE TABLE #DiskCapacityHistory
    (
      DATE_CD INT ,
      DataBaseID INT ,
      FileID INT ,
      Growth_MOM_RAT FLOAT
    ) ;

  INSERTINTO #DiskCapacityHistory
        SELECT  M.DATE_CD ,
                M.DataBaseID ,
                M.FileID ,
                CASE WHEN N.SIZE IS NULL
                          OR N.SIZE = 0 THEN 0
                     ELSE ( M.SIZE - N.SIZE ) / N.SIZE
                END AS Growth_MOM_RAT
        FROM    dbo.DiskCapacityHistory M ,
                dbo.DiskCapacityHistory N
        WHERE   CAST(CAST(M.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH, 1,
                                                              CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
                AND M.DataBaseID = N.DataBaseID
                AND M.FileID = N.FileID
                AND M.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE()
                                             - 1, 120), '-', '') AS INT)

  UPDATE dbo.DiskCapacityHistory
     SET Growth_MOM_RAT = M.Growth_MOM_RAT
    FROM #DiskCapacityHistory M
   WHERE dbo.DiskCapacityHistory.DATE_CD = M.DATE_CD
        AND dbo.DiskCapacityHistory.DataBaseID = M.DataBaseID
        AND dbo.DiskCapacityHistory.FileID = M.FileID ;

2: 幸好SQL 2008還把ORACLE的MERGE的功能給模仿了過(guò)來(lái)，但是T-SQL缺少ORACLE數(shù)據(jù)庫(kù)強(qiáng)大的分析函數(shù)LAG，如果有這個(gè)，我計(jì)算環(huán)比，同比就非常方便了，一個(gè)SQL就搞定了，下面是個(gè)例子，本想把ORACLE的SQL也做個(gè)例子展現(xiàn)，但是又要建表、造數(shù)，折騰起來(lái)比較麻煩。

復(fù)制代碼代碼如下:

MERGE INTO DM.TM_WGGBO_IDCTOBUSVOLDTL_DAY DM
USING    (
             SELECT *
               FROM (
                       SELECT    DATE_CD,
                                 CITY_ID,
                                 IDC_NODE,
                                 VOL_TYPE,
                                 LAG(IDC_VOL_RAT   ) OVER(PARTITION BY CITY_ID,IDC_NODE,VOL_TYPE,SUBSTR(DATE_CD,7,2) ORDER BY SUBSTR(DATE_CD,0,6)) AS IDC_MOM_RAT                ,
                                 LAG(IDC_VOL_RAT   ) OVER(PARTITION BY CITY_ID,IDC_NODE,VOL_TYPE,SUBSTR(DATE_CD,5,4) ORDER BY SUBSTR(DATE_CD,0,4)) AS IDC_YOY_RAT                 ,

                         FROM DM.TM_WGGBO_IDCTOBUSVOLDTL_DAY
                       ) T
                 WHERE EXISTS(SELECT 1 FROM ETL.T_IDCVOL_DAY_${ssid} WHERE DATE_CD = T.DATE_CD)
          ) TEMP
                        ON (
                                DM.DATE_CD     = TEMP.DATE_CD     AND
                                DM.CITY_ID     = TEMP.CITY_ID     AND
                                DM.IDC_NODE    = TEMP.IDC_NODE    AND
                                DM.VOL_TYPE    = TEMP.VOL_TYPE
                                )
WHEN MATCHED THEN
  UPDATE
       SET DM.IDC_MOM_RAT    =       TEMP.IDC_MOM_RAT                    ,
           DM.IDC_YOY_RAT    =       TEMP.IDC_YOY_RAT                     

COMMIT;

作者：瀟湘隱者
出處：http://www.cnblogs.com/kerrycode/

您可能感興趣的文章:

清空MSSQL日志與set recovery simple
mssql無(wú)數(shù)據(jù)庫(kù)日志文件恢復(fù)數(shù)據(jù)庫(kù)的方法
mssql自動(dòng)備份及自動(dòng)清除日志文件服務(wù)器設(shè)置
sqlserver 數(shù)據(jù)庫(kù)日志備份和恢復(fù)步驟
sqlserver 日志恢復(fù)方法(搞定drop和truncate)
SQLServer2005 沒(méi)有日志文件(*.ldf) 只有數(shù)據(jù)文件(*.mdf) 恢復(fù)數(shù)據(jù)庫(kù)的方法
收縮數(shù)據(jù)庫(kù)日志文件的方法(僅適用于mssql2005)
MSSQL 2005/2008 日志壓縮清理方法小結(jié)
刪除sqlserver數(shù)據(jù)庫(kù)日志和沒(méi)有日志的數(shù)據(jù)庫(kù)恢復(fù)辦法
拯救你的數(shù)據(jù) 通過(guò)日志恢復(fù)MSSQL數(shù)據(jù)

標(biāo)簽：周口湘潭預(yù)約服務(wù) 鄂爾多斯寧夏營(yíng)口河源欽州

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MSSQL 監(jiān)控?cái)?shù)據(jù)/日志文件增長(zhǎng)實(shí)現(xiàn)方法》，本文關(guān)鍵詞 MSSQL,監(jiān)控,數(shù)據(jù),日志,文件,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。