SQL Server - Space Integrity Check

03 September,2008 by Jack Vamvas

There was a requirement to allow  non -technical  users , to manage archiving
their databases on a SQL Server 2005 platform. They wanted to have the capacity to move the databases from a SAN drive to SATA drive, but they continued to have  a problem regarding space on the destination drive. In other words , having knowledge of the existing database and comparing to the space available on the destination drive.

The following code satisfied the requirement .

Firstly , create the following procedure (fnSpace_Integrity) , which has 2 input parameters .
@db  (which is the db name)
@intendedDrive   (the targe drive )
Secondly , call the procedure with code similar to the CODE 2 section. You can do whatever you want from the result

-------------------------------------CODE 1 START-------------------------------------
CREATE PROCEDURE [dbo].[fnSpace_Integrity]
@db VARCHAR(200) ,
@intendedDrive char(1),
@spResult int OUTPUT

AS

DECLARE @dbsize bigint,@logsize bigint,
@MB_Free int,
@reservedpages  bigint,
@database_size VARCHAR(100)

--establish db size in MB 
select   @database_size   = convert(int,
                                    case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
                                    when convert(bigint, sum(s_mf.size)) >= 268435456
                                    then null
                                    else (sum(s_mf.size)*8)/1024 -- Convert from 8192 byte pages to Kb then to MB
                                    end)
from   sys.master_files s_mf
where
        s_mf.state = 0 and -- ONLINE
        has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
        and db_name(s_mf.database_id) = @db
    group by s_mf.database_id
    order by 1

PRINT @database_size

create table #FreeSpace(
Drive char(1), 
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
select @MB_Free = MB_Free  from #FreeSpace WHERE drive = @intendedDrive
drop table #FreeSpace

--compare intended drive space with the database size  
if @MB_Free > @database_size 
begin
select  @spResult = 1 
end 
else 
begin
select  @spResult = 0
end 
--------------------------------CODE 1 END-----------------------------------------------------

--------------------------------CODE 2 START------------------------------------------------------

declare @spaceResult INT 
exec fnSpace_Integrity  @DB_NAME_EXISTING,@BackupDrive,@spaceResult OUT
select @spaceResult

if @spaceResult = 0 
begin
   
  PRINT 'There is not enough space on drive ' + @BackupDrive + ' to move the database : ' + 

@DB_NAME_EXISTING

end 

------------------------------CODE 2 END----------------------------------------------------------

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - Space Integrity Check


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer