SQLSERVERBASE.COM Run with SQL Server DBA and BI

DMV is dynamic management view. It gives us an insight of SOL server. Its a collection of system tables name start with dm_.

I will have detailed explanation of DMV in subsequent article.

Query to enable/disable sql jobs

-->> Query to enable/disable sql jobs

exec msdb..sp_update_job@job_id = 'PassJobIDHere' , @enabled = 0  

exec msdb..sp_update_job@job_name = 'PassJobNameHere' , @enabled = 1

Query to find all the Log Shipping copy job which is disable, to verify

-->> Query to find all the Log Shipping copy job which is disable, to verify

select * from msdb..sysjobs  

where name like '%LSCopy%TESTDB%' and enabled=1

Query to find all the Log Shipping restore job which is enable/disable, to verify

-->> Query to find all the Log Shipping restore job which is enable/disable, to verify

select * from msdb..sysjobs 

where name like '%LSrestore%TESTDB%' and enabled=1

Query to find all the Log Shipping backup job which is disable, to verify

-->> Query to find all the Log Shipping backup job which is disable, to verify

select * from msdb..sysjobs 

where name like '%LSBackup_TESTDB%' and enabled=0

Dynamic Query to generate script to disable autogrowth of all the files of specific drive

-->> Dynamic Query to generate script to disable autogrowth of all the files of specific drive

select ' alter database ' + DB_NAME() + ' MODIFY FILE ( NAME = ''' + df.name + ''' , FILEGROWTH = 0 )'

FROM sys.database_files df LEFT OUTER JOIN sys.data_spaces ds 

ON df.data_space_id = ds.data_space_id

where physical_name  like 'K%' --and physical_name not like '%mdf%' and physical_name not like '%ldf%'

and growth <>0

-->> Dynamic Query to generate script of Add Datafile of all the datafile of specific drive

select ' alter database '+DB_NAME()+' ADD FILE ( NAME = '''+ df.name +'_1 '' , FILENAME = '''+  replace(REPLACE( physical_name ,'.ndf','_1.ndf'),'K:','I:')+''' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP ['+ds.name +']'

FROM sys.database_files df LEFT OUTER JOIN sys.data_spaces ds  ON df.data_space_id = ds.data_space_id

where physical_name  like 'K%'--and physical_name not like '%mdf%' and physical_name not like '%ldf%'

and growth <> 0

select*fromsys.dm_os_cluster_nodes

selectserverproperty('ComputerNamePhysicalNetBIOS')

Query to get Drive Total and Free Space

-->> Query to get Drive Total and Free Space

SELECT f.database_id,DB_NAME(f.database_id),f.file_id,f.growth,f.name, f.physical_name,x.volume_mount_point, x.total_bytes/1024/1024/1024 as TotalSpaceGB, x.available_bytes/1024/1024/1024 as AvailableSpaceGB FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) x where (DB_NAME(f.database_id) like 'TESTDB%' or SUBSTRING(f.physical_name,1,3) in(select SUBSTRING(physical_name,1,3) from sys.master_files where DB_NAME(database_id) like 'TESTDB%') ) and growth <> 0 order by 2

Query to find total log fie size

-->> query to find total log fie size

select db_name(database_id), name, sum(size)/128 from sys.master_files where db_name(database_id) like 'TESTDB%' and type=1 group by db_name(database_id), name

select db_name(database_id), name, size/128, physical_name from sys.master_files where db_name(database_id) like 'TESTDB%' and type=1

Below is the script to add free space in datafile or logfile. You just need to pass database name, datafile name, log file name, space to add, and total space. 

------------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>

declare @databaes varchar(100), @datafile varchar(250), @Current_size int, @SpaceBunch int, @sql varchar(500)

declare @TotalSpaceToAdd int

set @databaes='TESTDB' -->> Pass Database name here

set @datafile= 'TESTDB_Log_2'   -->> Pass Logical Name of data/log file

set @SpaceBunch=20   -->> It will add THIS much of space of chunck

set @TotalSpaceToAdd=100 -->> It will add space till THIS much, and will starts from current size+@SpaceBunch

select @Current_size=size/128 from sys.master_files where db_name(database_id)=@databaes and name=@datafile

print 'Space Before Addition ' + convert(varchar,@Current_size/1024) -->> It will print current size in GB

while @Current_size/1024 <@TotalSpaceToAdd

begin

 set @sql='ALTER DATABASE [' + @databaes + '] MODIFY FILE ( NAME = N''' + @datafile + ''''+ ', SIZE = ' + convert(varchar,@Current_size+ @SpaceBunch*1024) + 'MB )'

 print @sql

 exec(@sql)

 select @Current_size=size/128 from sys.master_files where db_name(database_id)=@databaes and name=@datafile

 print 'Space After Addition ' + convert(varchar,@Current_size/1024)  -->> It will print new current size in GB after space addition

end

------------------>>>>>>>>>>>>>>>>>>>>>>>>>>>>