Skip to content

Archive for

Find total and free disk space from SQL Server

You can use below query to find the total disk space of each drive in the server and total free space availble for each disk. This script will be handy for level 2 DBA’s who don’t have direct access to database server and perform their duties by connecting through SQL Server Management Studio (SSMS) from a jump server.

Here are the two steps that you have to follow. Step 1 can be ignored if xp_cmdshell is already enabled for your instance.

1) Enable xp_cmdshell if not already enabled.

EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
go
-- To update the currently configured value for this feature.
RECONFIGURE
GO

2) Execute below script to collect the required details.

Declare @Used_Bytes Varchar(1000),
@Free_Bytes Varchar(1000),
@TotalBytes BIGINT,
@IDENTITY INT,
@Drive Char(1),
@sql Varchar(1000)

SET NOCOUNT ON
Create table ##DiskSpace ( Drive Char(1), Total_Space Bigint, Free_Space Bigint,
Percentage_Free as (Free_Space*100 / Total_Space ) )
Create table #Fixeddrives ( Drive Char(1), Free_Space Bigint)
create table ##Dir ( ID INT IDENTITY , Drive_Size Varchar(2000))

Insert into #Fixeddrives exec master.dbo.xp_fixeddrives
insert into ##DiskSpace ( Drive , Free_Space)
select Drive , Free_Space from #Fixeddrives

DECLARE Drive_cursor CURSOR FOR
SELECT Drive from ##DiskSpace
OPEN Drive_cursor
FETCH NEXT FROM Drive_cursor INTO @Drive

WHILE @@FETCH_STATUS = 0
BEGIN

select @sql = 'insert into ##Dir exec master.dbo.xp_cmdshell ''dir '+ @Drive+': /S /A /-C'''
exec(@sql)
SELECT @IDENTITY = @@IDENTITY
delete from ##Dir where ID < @IDENTITY - 4 select @Used_Bytes = substring (Drive_Size, charIndex ('File(s)', Drive_Size, 0)+ 9 , 1000) from ##Dir where Drive_Size like '%File(s)%' while patindex('%[^0-9]%', @Used_Bytes) > 0
begin
set @Used_Bytes = stuff( @Used_Bytes, patindex('%[^0-9]%', @Used_Bytes), 1, '' )
end

select @Free_Bytes = substring (Drive_Size, charIndex ('Dir(s)', Drive_Size, 0)+ 9 , 1000)
from ##Dir where Drive_Size like '%Dir(s)%'

while patindex('%[^0-9]%', @Free_Bytes) > 0
begin
set @Free_Bytes = stuff( @Free_Bytes, patindex('%[^0-9]%', @Free_Bytes), 1, '' )
end

select @TotalBytes = Convert(bigint, @Used_Bytes)+ Convert(bigint, @Free_Bytes)
select @TotalBytes = (@TotalBytes/ 1024)/1024
Update ##DiskSpace set Total_Space = @TotalBytes
WHERE Drive = @Drive

TRUNCATE TABLE ##Dir
FETCH NEXT FROM Drive_cursor INTO @Drive

END
CLOSE Drive_cursor
DEALLOCATE Drive_cursor

select * from ##DiskSpace

drop table ##DiskSpace
drop table #Fixeddrives
drop table ##Dir

Netezza – Backups status showing active

ISSUE:
When you check the database backup history using below query for your Netezza you may find some active backups.

select * from _V_Backup_History

CAUSE:
Now when you try to check active conenctions, you are not able to find any backup running. Here you may also see the start time being too old. So why is system table/view showing us wrong information? Reason for this wrong information is that if you kill any backup process directly from OS by issuing Kill Linux process id, then Netezza is not able to understand if backup is really killed and mark it as failed or if backup is still running. So, system table is not updated and you get wrong information.

RESOLUTION:
In order to fix this you may have to directly update the system table _T_Backup_History which holds this information.

Below query will return list of all active backups

select * from _V_Backup_History where status = 'ACTIVE'

Below query will return the output as total count of active, completed or failed backups

select distinct status, count(*) from _T_Backup_History group by status

Not, Here is the decoding for status field in _T_Backup_History table.
0 = Active
1 = Completed
2 = Failed

RUn below query to update the system table and mark the actually failed backups (still showing active in system table) to failed.

update _T_Backup_History
set Status = 2 where status=0

ADDITIONAL USEFUL INFO:
Query to find active backups in Netezza

Select DBNAME, STATUS,USERNAME,STARTTIME, LASTUPDATE from _V_Backup_History
where DBNAME != 'NULL' and status = 'ACTIVE'

Here we have put DBNAME not equal to NULL because if a database is dropped from Netezza then it updates the backup tale and replace the database name with NULL.