SQL Server queries available disk space

--> Title: SQL Server queries available disk space of a shard.

--> Author: wufeng4552

--> Date: 09:59:09

Preface

If your organization has programs that require a large amount of disk space or a large proportion of a specific disk, make sure that the disk drive has sufficient storage at the beginning or key points of the program. A quick check can save a lot of time, reduce troubles and re-work

If the disk space is very tight or a large proportion of the disk needs to be used for this process, it is wise to check the disk space before continuing a process. use the xp_fixeddrives stored procedure provided by SQL Server 2000 to 2008 to solve this problem.

 

If object_id ('SP _ exec_getdiskspace ', 'P') is not null

Drop proc sp_exec_getdiskspace

Go

Create proc sp_exec_getdiskspace

(

@ Minmbfree int,

@ Drive varchar (2)

)

As

Begin

Declare @ mbfree int

Select @ mbfree = 0

Create Table # tb_diskspace

([Drive] varchar (2 ),

[Mbfree] INT)

Insert # tb_diskspace exec master. DBO. xp_fixeddrives

Select @ mbfree = [mbfree]

From # tb_diskspace

Where [Drive] = @ drive

If @ mbfree> @ minmbfree

Return

Else

Begin

Raiserror ('*** error ***-there is not enough disk space! ', 16, 1)

End

Drop table # tb_diskspace

End

Go

Exec sp_exec_getdiskspace 10240, 'C'

/*

(Five data columns are affected)

Interest 50000, interval limit 16, interval limit 1, program sp_exec_getdiskspace, row 21

* ** Error **-insufficient space for the magnetic field!

*/

Exec sp_exec_getdiskspace 10240, 'C'