Monday, September 18, 2023

SQL Server: How to run multiple scripts in batch mode

 

  1. In the SQL Management Studio open a new query and type all files as below

    :r c:\Scripts\script1.sql
    :r c:\Scripts\script2.sql
    :r c:\Scripts\script3.sql
    
  2. Go to Query menu on SQL Management Studio and make sure SQLCMD Mode is enabled
  3. Click on SQLCMD Mode; files will be selected in grey as below

    :r c:\Scripts\script1.sql
    :r c:\Scripts\script2.sql
    :r c:\Scripts\script3.sql
    
  4. Now execute

Thursday, August 17, 2023

SQL Server: Running Jobs Query

 SELECT j.name AS job_name, 
       ja.start_execution_date AS StartTime,
   COALESCE(CONVERT(VARCHAR(5),ABS(DATEDIFF(DAY,(GETDATE()-ja.start_execution_date),'1900-01-01'))) + ' '
               +CONVERT(VARCHAR(10),(GETDATE()-ja.start_execution_date),108),'00 00:00:00') AS [Duration] 
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY session_id DESC)
  AND start_execution_date is not null
  AND stop_execution_date is null;

Sunday, June 25, 2023

SQL Server: alter physical file names

select 'ALTER DATABASE ' + d.name +' MODIFY FILE ( NAME = ' + f.name + ', FILENAME = ''' + f.physical_name +''' );'
from sys.master_files f, sys.databases d
where f.database_id > 4
and f.database_id = d.database_id
and f.physical_name like 'C:\ClusterStorage\Test UDB\Data\%'
order by d.name

SQL Server set databases offline

select 'ALTER DATABASE [' + A.Name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;'
from sys.databases a
where a.database_id > 4
and state_desc ='ONLINE'
and a.name not in ('SSISDB')
order by a.name

SQL Server set databases online

select 'ALTER DATABASE [' + A.Name + '] SET ONLINE;'
from sys.databases a
where a.database_id > 4
and state_desc ='OFFLINE'
and a.name not in ('SSISDB')
order by a.name

Friday, June 23, 2023

SQL Server ETA of running SQL e.g. dbcc

 select  T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
        , R.cpu_time, R.total_elapsed_time, R.percent_complete
from    sys.dm_exec_requests R
        cross apply sys.dm_exec_sql_text(R.sql_handle) T

SQL Server Agent Job Startup to remove a tempdb file

 USE [tempdb]
GO
DBCC SHRINKFILE (N'temp5', EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [temp5]
GO