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

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;

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

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