Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Learn about SQL Server DBA, SQL Server database performance,SQL Server optimisation,database tuning,t-sql,ssis
Browse by Tags · View All
DBA Scripts 51
performance 37
SQL Server 29
Object Management 24
#SQLServer 24
Backup and Restore 20
Security Management 20
Powershell 17
Indexes 14
DBA 14

Archive · View All
June 2011 38
January 2011 33
May 2011 32
August 2011 27
July 2011 26
January 2012 24
February 2011 19
April 2011 19
March 2011 17
March 2012 17

Jack Vamvas's Blog

Space Integrity Check

Sep 3 2008 6:59AM 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----------------------------------------------------------

Republished from http://www.sqlserver-dba.com.


Republished from SQL Server DBA [65 clicks].  Read the original version here [32134 clicks].

Jack Vamvas
5 · 26% · 8528
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]