A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Disk Space Monitoring Procedure without OLE Automation Procedures

 
The procedure "Monitor_Disk_Space" was created to return more information than the widely used "capacity_DiskSpaceTracking" procedure which is used for monitoring.
 
 The capacity_diskspacetracking procedure can be found all over the web a simple google search will return many results.
 
Compare the output of two procedures: "capacity_DiskSpaceTracking"  and  "Disk_Space_Monitoring"

"capacity_DiskSpaceTracking"
 
 
 
"monitor_disk_space"  - the procedure code for this is below.
 
 
 
 as you can see , we're gathering much more information in the new procedure without  enabling OLE Automation Procedures.
 
Procedure: Monitor_Disk_Space

 

 

 

 

create proc monitor_disk_space 

as 

begin 

 -- CREATED BY Don Rickman 
-- 9/1/2007

 

create table #disk_usage ( 

   drive char(1) 

 , free_space_mb float 

 , total_space_mb float 

 , isShared bit) 

 

 insert into #disk_usage (drive, free_space_mb) 

 exec master..xp_fixeddrives 

 

 create table #output (drive char(1), [output] varchar(256)) 

 

 declare @drive char(1), @cmd varchar(256), @t bigint, @u bigint 

 

 declare drive_cursor cursor for select drive from #disk_usage 

 open drive_cursor 

 fetch next from drive_cursor into @drive  

 

   while @@fetch_status =

   begin 

    set @cmd = 'fsutil volume diskfree ' + @drive + ':' 

    insert into #output ([output]) EXEC XP_CMDSHELL @cmd 

    update #output set drive = @drive where drive is null 

 

    fetch next from drive_cursor into @drive 

   end 

 

 close drive_cursor 

 deallocate drive_cursor 

 

 

 SELECT drive, 

   cast(SUM(CASE WHEN [output] LIKE 'Total # of bytes             : %' THEN CAST(REPLACE(SUBSTRING([output], 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) / 1024.00 / 1024 / 1024 as decimal(10,2)) AS total_gigabytes, 

   cast(SUM(CASE WHEN [output] LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING([output], 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) / 1024.00 / 1024 / 1024 as decimal(10,2))AS avail_free_gigabytes 

   , case when s.drivename is not null then 1 else 0 end is_shared_drive 

   , sa.name 

   , sa.[filename] [file_name] 

   , CASE   

    WHEN sa.groupid = 1 THEN 'data' 

    WHEN sa.groupid = 0 THEN 'log' 

   END as 'file_type' 

 , cast( cast(sa.size as bigint) * 8192 / 1024.00 / 1024.00 / 1024.00 as decimal(10,3)) file_size_gigabytes 

 into #temp 

 FROM ( 

    SELECT Drive, 

     [output] 

    FROM #output 

    WHERE [output] LIKE 'Total # of %' 

   ) AS

 left join master.dbo.sysaltfiles sa on left(sa.filename,1) = d.drive 

 left join ::fn_servershareddrives() s on s.drivename COLLATE SQL_Latin1_General_CP1_CI_AS = d.drive 

 

 GROUP BY Drive, s.drivename, sa.size, sa.name, sa.filename, sa.groupid 

 

 select @@SERVERNAME [server] 

 , drive 

 , total_gigabytes 

 , avail_free_gigabytes 

 , cast( ((avail_free_gigabytes/ total_gigabytes)*100) as decimal(10,2) ) [percent_avail_disk_space] 

 , is_shared_drive 

 , name 

 , [file_name] 

 , file_type 

 , file_size_gigabytes  

 , getdate() [date]

 from #temp 

 ORDER BY Drive, name 

 

end  

 

 

GO

 

 

 

 

 

Shrink Transaction Logs

 
Running the following code truncates the transaction log. A full database backup should be run after this script to insure point of time recovery in any production environment. 

USE <DB>
GO
DBCC SHRINKFILE(<TLOGNAME>, 1)
BACKUP LOG <DB> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TLOGNAME>, 1)
GO

 
 
-- New way
use <DB>
ALTER DATABASE <DB> SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(<DB_LOG>, 1)
ALTER DATABASE <DB> SET RECOVERY FULL WITH NO_WAIT

TSQL Query Default Database Path for SQL Server


 
declare @SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT

declare @SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT

SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]