Disk Space

  • Dear Friends,

    I want to automatically alert my colleagues reg the disk space for every two hours.

    Thanks & Regards

    Chandra Mohan N

    [font="Verdana"]Thanks
    Chandra Mohan[/font]

  • have a look at sp_spaceused. you could run it in each database to generate a report.

    ---------------------------------------
    elsasoft.org

  • Or set a performance alert on this counter.

  • I did not ask for the space left in the database. I want the diskspace in the drive where the database resides to be sent to everyone of my colleauge through net send for every two hours.

    [font="Verdana"]Thanks
    Chandra Mohan[/font]

  • I don't know about net send but you can use the extended stored procedure xp_fixeddrives to tell you how much disk space you have left...

     EXEC master..xp_fixeddrives

     

    Mark

  •  

    I second Steve Jones.

  • Write a VBS Script (WMI) to check up the space left and set up a scheduled task. In my opinion this is a OS task for the system administrator.

  • Hello

    I use the following SP (I think taken from SQL server central and altered) and SQL job to monitor disk space and issue an e mail alert when any of the drives falls below 'X%' free space.  I am sure that you could amend the part that sends the email to be specific to a drive and issue an net send instead.  The SQL job can be scheduled to your own requirements.

    --Create the procedure

     /****** Object:  Stored Procedure dbo.sp_server_disk_space    Script Date: 10/11/2006 15:31:46 ******/ 

     

    CREATE       procedure sp_disk_growth_Monitor(@Limit int) 

    as 

    /** 

    Issues e mail when percentage of available disk space on any drive falls below 'X' 

    Uses SQL job scheduled to run every hour between 08:00 and 17:00 

    **/ 

    set nocount on 

    declare  

     @SQL varchar(1000), 

     @subject varchar(100), 

     @message varchar(100) 

    SET @SUBJECT =  @@servername + ' Low Disk Space' 

    set @message = 'Available disk space is reported as below ' + convert(varchar(2), @limit)  + '%' + char(10) 

     

     

    create table Tbl_Temp_disk_space  

      (Extract_date datetime,  

      dletter varchar(2),  

      fspace DECIMAL(9,2),  

      tspace DECIMAL(9,2)) 

     

     

     /***************************************** 

     * populate table 

     *****************************************/ 

     

     

     INSERT INTO  

     Tbl_Temp_disk_space (dletter, fspace)  

     EXEC master.dbo.xp_fixeddrives 

     Update Tbl_Temp_disk_space 

     set extract_date = convert(varchar(11), getdate(), 113) 

     where extract_date is null 

      

     

    /***************************************** 

     * Update temp table info with total disk sizes 

     *****************************************/ 

     -- Create cursor for disk space table 

     DECLARE c_disks CURSOR FOR 

      SELECT dletter, fspace, tspace FROM Tbl_Temp_disk_space 

      FOR UPDATE 

     DECLARE @dletter VARCHAR(2), @fspace INT, @tspace BIGINT 

     

     -- Create FileSystemObject 

     DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT 

     EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT 

      

     -- Open cursor and fetch first row 

     OPEN c_disks 

     FETCH NEXT FROM c_disks 

     INTO @dletter, @fspace, @tspace 

     

     -- Loop through all records in the cursor 

     WHILE @@fetch_status = 0 

     BEGIN 

      -- Get disk size 

      SET @dletter = @dletter + ':\' 

      EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter 

      EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT 

      -- Update table 

      UPDATE Tbl_Temp_disk_space 

      SET tspace = --CAST(@drsize AS BIGINT) 

      isnull(convert(varchar(10), round((CAST(CAST(@drsize AS BIGINT) AS FLOAT) / 1024) /1024,0)), 'Not recorded')  

     

      WHERE CURRENT OF c_disks 

      -- Destory oDrive 

      EXEC master.dbo.sp_OADestroy @oDrive 

      -- Fetch next row 

      FETCH NEXT FROM c_disks 

      INTO @dletter, @fspace, @tspace 

     END 

     

     -- Close cursor 

     CLOSE c_disks 

     DEALLOCATE c_disks 

     

     -- Destroy FSO 

     EXEC master.dbo.sp_OADestroy @oFSO 

    --end 

    if exists (select *  from Tbl_Temp_disk_space where CAST((fspace/tspace)*100 as int) <=@limit )  

    begin 

     

    set @sql =  'select ' + char(10) + 

      'dletter,' + char(10) + 

      'fspace Current_Free_Space_mb,' + char(10) + 

      'tspace Current_Total,' + char(10) + 

      'CAST((fspace/tspace)*100 AS DECIMAL(9, 2)) Percentage_free,' + char(10) + 

      'case' + char(10) + 

      ' when CAST((fspace/tspace)*100 AS int)  <= ' + convert(varchar(3), @limit) + ' then ''Low Disk Space''' + char(10) + 

      ' else ''''' + char(10) + 

      'end warning' + char(10) + 

      'from database_administrator_monitoring.dbo.Tbl_Temp_disk_space'

    --exec(@sql) 

    EXEC master..xp_sendmail @recipients = '[Your recipients]',  

     @query = @sql,  

     @subject =@subject

     @message = @message 

     

     

     

      

    end 

    drop table Tbl_Temp_disk_space 

     

     

     

    go

     

    --create the job

    -- Script generated on 02/05/2007 10:19

    -- By: sa

    -- Server: [Your server name]

    BEGIN TRANSACTION           

      DECLARE @JobID BINARY(16) 

      DECLARE @ReturnCode INT   

      SELECT @ReturnCode = 0    

    IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1

      EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

      -- Delete the job with the same name (if it exists)

      SELECT @JobID = job_id    

      FROM   msdb.dbo.sysjobs   

      WHERE (name = N'sp_disk_growth_Monitor')      

      IF (@JobID IS NOT NULL)   

      BEGIN 

      -- Check if the job is a multi-server job 

      IF (EXISTS (SELECT  *

                  FROM    msdb.dbo.sysjobservers

                  WHERE   (job_id = @JobID) AND (server_id <> 0)))

      BEGIN

        -- There is, so abort the script

        RAISERROR (N'Unable to import job ''sp_disk_growth_Monitor'' since there is already a multi-server job with this name.', 16, 1)

        GOTO QuitWithRollback 

      END

      ELSE

        -- Delete the [local] job

        EXECUTE msdb.dbo.sp_delete_job @job_name = N'sp_disk_growth_Monitor'

        SELECT @JobID = NULL

      END

    BEGIN

      -- Add the job

      EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'sp_disk_growth_Monitor', @owner_login_name = N'sa', @description = N'Monitors disk space and issues E mail', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 2, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0, @notify_email_operator_name = N'Jim Exon'

      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

      -- Add the job steps

      EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'sp_disk_growth_Monitor', @command = N'sp_disk_growth_Monitor 5', @database_name = N'Database_Administrator_Monitoring', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2

      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

      EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

      -- Add the job schedules

      EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'sp_disk_growth_monitor', @enabled = 1, @freq_type = 8, @active_start_date = 20061120, @active_start_time = 80000, @freq_interval = 127, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 170000

      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

      -- Add the Target Servers

      EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

      IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    COMMIT TRANSACTION         

    GOTO   EndSave             

    QuitWithRollback:

      IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    EndSave:

     

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply