要如何將系統磁碟空間,記錄在資料庫中,語法如下。
要如何將系統磁碟空間,記錄在資料庫中,語法如下。
IF NOT EXISTS
(SELECT * FROM [tempdb].sys.objects
WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[logicaldisk]')
AND type in (N'U'))
BEGIN
CREATE TABLE tempdb.dbo.logicaldisk(
line nvarchar(1000)
) ON [PRIMARY]
END
ELSE
TRUNCATE TABLE tempdb.dbo.logicaldisk;
INSERT INTO tempdb.dbo.logicaldisk (line)
EXEC xp_cmdshell 'wmic logicaldisk get size,freespace,caption';
DELETE FROM dw.dbo.usage_disk
WHERE CONVERT(VARCHAR(10),collecttime,111) = CONVERT(VARCHAR(10),GETDATE(),111);
WITH WhatIWant AS
(SELECT SUBSTRING(line,1,CHARINDEX(' ',line)) Caption
, SUBSTRING(LTRIM(SUBSTRING(line,CHARINDEX(' ',line),LEN(line))),1,CHARINDEX(' ',LTRIM(SUBSTRING(line,CHARINDEX(' ',line),LEN(line))))) FreeSpace
, LTRIM(SUBSTRING(LTRIM(SUBSTRING(line,CHARINDEX(' ',line),LEN(line))),CHARINDEX(' ',LTRIM(SUBSTRING(line,CHARINDEX(' ',line),LEN(line)))),LEN(line))) TotalSize
FROM tempdb.dbo.logicaldisk
WHERE SUBSTRING(line,2,1) = ':');
INSERT INTO dw.dbo.usage_disk
SELECT GETDATE() collect_time
, Caption
, convert(bigint,[FreeSpace]) FreeSpace
, convert(bigint,replace(TotalSize,Char(13),'')) Size
FROM WhatIWant
WHERE isnumeric(Totalsize) = 0;
DROP TABLE tempdb.dbo.logicaldisk;