Filter
  • T-SQL: Query to enable/disable sql jobs

    Query to enable/disable sql jobs

    -->> Query to enable/disable sql jobs

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

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

  • T-SQL: 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

    -->> 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

  • T-SQL: 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

    -->> 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

  • T-SQL: 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

    -->> 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

  • T-SQL: 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

    -->> 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

  • T-SQL: Dynamic Query to generate script of Add Datafile of all the datafile of specific drive

    -->> 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

     

  • T-SQL: Query DMV to get all node names of SQL Cluster

    select*fromsys.dm_os_cluster_nodes

  • T-SQL: Query to get current Active node in SQL cluster

    selectserverproperty('ComputerNamePhysicalNetBIOS')

  • T-SQL: Query to get Drive Total and Free Space

    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 ASCROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)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 <>order by 2

  • T-SQL: Query to find datafile logfile size of database

    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=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

  • T-SQL: Add Free Space in datafile/Logfile

    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

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

Additional information