SQL Server queries available disk space

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.