Emre GÜNERTÜRK

CREATE PROCEDURE [dbo].[GetDiskSpaceStats] (@RunLocal bit = 0)
AS
BEGIN 

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'DiskSpaceStats' AND type='U')
BEGIN
    CREATE TABLE [dbo].[DiskSpaceStats]
    (
        DriveLetter        char(1),
        TotalSize        decimal(20,0),
        AvailableBytes    decimal(20,0),
        DriveType        char(10),
        PercentFree        decimal(18,3),
        Report_Date        datetime default getdate()
    )
    ON [PRIMARY]
END 

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#DiskSpace%')
DROP TABLE #DiskSpace
CREATE TABLE #DiskSpace
(
    RowID int Identity Primary Key,
    DriveLetter char(1),
    TotalSize decimal(20,0),
    AvailableBytes decimal(20,0),
    DriveType char(10),
    PercentFree decimal(18,3)
)

DECLARE @counter int
DECLARE @FSOobject int
DECLARE @RecordCount int
DECLARE @count int
DECLARE @string char(35)
DECLARE @DriveLetter char(1)
DECLARE @TotalSize char(50)
DECLARE @AvailableSpace char(50)
DECLARE @DriveType char(10)
DECLARE @error int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @ServerName sysname

SET @ServerName = @@ServerName
SET @count=0
SET @counter=67
SET @error=0

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSOobject OUT

IF @hr <> 0
    BEGIN
        EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
        PRINT @desc
        RETURN
    END

WHILE @counter < 91 OR @error <3
    BEGIN
        SET @String='Drives.item("'+char(@counter)+'").DriveLetter'
        EXEC @HR = sp_OAGetProperty @FSOobject, @String, @DriveLetter OUT
        IF @HR <> 0
            BEGIN
                EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
                SELECT @counter=@counter+1
                SELECT @error=@error +1
                GOTO ErrorPoint
            END

        SET @String='Drives.item("'+char(@counter)+'").TotalSize'
        EXEC @HR = sp_OAGetProperty @FSOobject, @string, @TotalSize OUT
        IF @HR <> 0
            BEGIN
                EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
                SELECT @counter=@counter+100
                SELECT @error=1
            END 

        SET @String='Drives.item("'+char(@counter)+'").AvailableSpace'
        EXEC @HR = sp_OAGetProperty @FSOobject, @string, @AvailableSpace OUT
        IF @HR <> 0
            BEGIN
                EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
                PRINT @desc
                RETURN
            END

        SET @String='Drives.item("'+char(@counter)+'").DriveType'
        EXEC @HR = sp_OAGetProperty @FSOobject, @String, @DriveType OUT
        IF @HR <> 0
            BEGIN
                EXEC sp_OAGetErrorInfo @FSOobject, @src OUT, @desc OUT
                PRINT @desc
                RETURN
            END

        IF @Error < 3
            BEGIN
                INSERT INTO #DiskSpace (DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree)
                VALUES (@DriveLetter, @TotalSize, @AvailableSpace, @DriveType, (convert(decimal(20,0), @AvailableSpace)/convert(decimal(20,0), @TotalSize)))
            END

        SELECT @counter=@counter+1
    END

ErrorPoint:

INSERT INTO [dbo].[DiskSpaceStats] (DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree)
SELECT DriveLetter, TotalSize, AvailableBytes, DriveType, PercentFree
FROM #DiskSpace

    EXEC @hr = sp_OADestroy @FSOobject
    IF @hr <> 0
        BEGIN
            EXEC sp_OAGetErrorInfo @FSOobject
        RETURN
    END

IF @RunLocal = 1
BEGIN
 SELECT * FROM #DiskSpace
END
ELSE
BEGIN

        DECLARE @Loop int
        DECLARE @Subject varchar(100)
        DECLARE @strMsg varchar(4000)

        SELECT @Subject = 'SQL Monitor Alert: ' + @@servername

        SELECT @Loop = min(RowID)
        FROM #DiskSpace
        WHERE PercentFree <= .15

        WHILE @Loop IS NOT NULL
        BEGIN

            SELECT     @strMsg = convert(char(15),'Drive:') + isnull(DriveLetter, 'Unknown') + char(10) +
                    convert(char(15),'') + convert(varchar, convert(decimal(18,1), PercentFree*100)) + '% free space remaining.'+ char(10) +
                    convert(char(15),'') + char(10) +
                    convert(char(15),'EventTime:') + convert(varchar, getdate())
            FROM #DiskSpace
            WHERE RowID = @Loop

            EXEC dbo.SendEmailNotification @Subject, @strMsg

            SELECT @Loop = min(RowID)
            FROM #DiskSpace
            WHERE PercentFree <= .15
             AND RowID > @Loop

        END

END

DROP TABLE #DiskSpace

END

SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as LastBackUpTaken,
COALESCE(Convert(varchar(12), MAX(T2.user_name), 101),'NA') as UserName
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name

SET NOCOUNT ON

DECLARE @MyTable table
  ( RowID     int  IDENTITY,
   Category   varchar(5),
   [ID]     varchar(5),
   [Description] varchar(25),
   Price     decimal(10,2)
  )

INSERT INTO @MyTable VALUES ( 'Pot', 'A1', 'Small Saucepan', 21.50 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A2', '1 Qt Saucepan', 29.95 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A3', '1.5 Qt Saucepan', 33.95 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A4', 'Double Boiler', 39.50 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A5', 'Stewpot', 49.50 )
INSERT INTO @MyTable VALUES ( 'Pot', 'A6', 'Pressure Cooker', 79.95 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B1', '8"" Pie', 6.95 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B2', '8"" Sq Cake', 7.50 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B3', 'Bundt Cake', 12.50 )
INSERT INTO @MyTable VALUES ( 'Pan', 'B4', '9x12 Brownie', 7.95 )
INSERT INTO @MyTable VALUES ( 'Bowl', 'C1', 'Lg Mixing', 27.50 )
INSERT INTO @MyTable VALUES ( 'Bowl', 'C2', 'Sm Mixing', 17.50 )
INSERT INTO @MyTable VALUES ( 'Tools', 'T1', '14"" Spatula', 9.95 )

SELECT
  RowID,
  Category,
  [ID],
  [Description],
  Price
FROM (SELECT
     ROW_NUMBER() OVER ( PARTITION BY Category ORDER BY Price DESC ) AS 'RowNumber',
     RowID,
     Category,
     [ID],
     [Description],
     Price
   FROM @MyTable
   ) dt
WHERE RowNumber <= 2

-- Sonuçlar
RowID Category ID Description   Price
11  Bowl   C1 Lg Mixing    27.50
12  Bowl   C2 Sm Mixing    17.50
9   Pan    B3 Bundt Cake   12.50
10  Pan    B4 9x12 Brownie  7.95
6   Pot    A6 Pressure Cooker 79.95
5   Pot    A5 Stewpot     49.50
13  Tools   T1 14" Spatula   9.95

SELECT DISTINCT
  RowID,
  Category,
  [ID],
  [Description],
  Price
FROM @MyTable t1
WHERE RowID IN (SELECT TOP 2
          RowID
        FROM @MyTable t2
        WHERE t2.Category = t1.Category
        ORDER BY Price DESC
        )
ORDER BY
  Category,
  Price DESC

-- Sonuçlar
RowID Category ID Description   Price
11  Bowl   C1 Lg Mixing    27.50
12  Bowl   C2 Sm Mixing    17.50
9   Pan    B3 Bundt Cake   12.50
10  Pan    B4 9x12 Brownie  7.95
6   Pot    A6 Pressure Cooker 79.95
5   Pot    A5 Stewpot     49.50
13  Tools   T1 14" Spatula   9.95