--> 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'