How to Find Out Total Disk Free Space For SQL Server DBs disks using T-SQL

How to Find Out Total Disk Space For DBs disks using T-SQL for SQL Sever with Automatic Version Detiction



 

if
left(cast(serverproperty('productversion') as varchar) , 4) in('10.5','11.0','12.0','13.0')

begin

--2008 R2 SP1

select left(cast(serverproperty('productversion') as varchar) , 4) as SQLServerVersion,A.name, volume_mount_point

-- , (cast(total_bytes as decimal(19,2))/1024/1024/1024)as total_Disk_Capacity_GB --original

, cast((cast(total_bytes as decimal(19,2))/1024/1024/1024)as decimal(19,2)) as total_Disk_Capacity_GB --rounded

-- , (cast(available_bytes as decimal(19,2))/1024/1024/1024)as available_Space_GB --original

, cast((cast(available_bytes as decimal(19,2))/1024/1024/1024)as decimal(19,2)) as available_Space_GB --rounded

from sys.master_files A

CROSS APPLY sys.dm_os_volume_stats(A.database_id, A.file_id) --all DBs files

--CROSS APPLY sys.dm_os_volume_stats(db_id(A.name), A.file_id) --for the current DBs

where (A.file_id=1 or A.file_id=2)and A.database_id >4

end

else


begin

--2008 or earlier

IF OBJECT_ID('tempdb..#TMP_DRIVES') is not Null

DROP TABLE #TMP_DRIVES

CREATE TABLE #TMP_DRIVES (DRIVE CHAR(1) NOT NULL

,MBFREE INT NOT NULL)

INSERT INTO #TMP_DRIVES

EXEC xp_fixeddrives





select
left(cast(serverproperty('productversion') as varchar) , 4)as SQLServerVersion

,DRIVE as Volum_mount_drive

,convert(decimal(19,2),(cast(MBFREE as decimal(19,2))/1024 )) as Available_Space_GB

--,name

from #TMP_DRIVES a--, sys.master_files b

where DRIVE in(select LEFT(physical_name,1) from sys.master_files where database_id>4)

drop table #TMP_DRIVES

end

Running deployed SSIS package from SQL Server job generates an error

If you Do the following Scenario:-

1- Developed an SSIS package in 64Bit with Attunity Connector to connect to Oracle DB. And Configure the package property named "Run64BitRuntime" to fales, to run in 32Bit at runtime.

2- Deploy it to Integration Server.

3- Create an SQL Server Job to schedule running it.

4- Got the following error when running the job "Executed as user: ???. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4000.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  ??? "running job time"  Error: ??? "running job date and time"     Code: 0xC0014005     Source:       Description: The connection type "MSORA" specified for connection manager "??? Name of your attunity connector" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.  End Error  Error: ??? "End job date and time"     Code: 0xC0010018     Source:       Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts">  <DTS:Property DTS:Name="DelayValidation">0</DTS:Property>  <DTS:Property DTS:Name="ObjectName">Attunity Connector Name</DTS:Property>  <DTS:Property DTS:Name="DTSID">???" from node "DTS:ConnectionManager".  End Error  Could not load package "??? Name of your SSIS Package" because of error 0xC0010014.  Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.  Source:   Started:  ???  Finished: ???  Elapsed:  ??? seconds.  The package could not be loaded.  The step failed."


    Kindly open the job>>>open the step that call your SSIS package>>>Excution Options>>> Check the option "Use 32 bit runtime" as shown belo:-

     Run you SQL Server job, it will run correctly.