When you have several sql jobs that need to be disabled all.
If you can, you can just turn off SQL Agent but, sometime, it is not possible.
This script generates the script to enable and disable.
--generate disable
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs
--generate enable
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 1' FROM msdb..sysjobs
When you have your jobs organized by category, you can use this script:
-------------------------------------
-- Script to enable or disable Jobs
-- Filter by Category
-------------------------------------
DECLARE @Categoryname AS NVARCHAR(100) = null;
DECLARE @newJobStatus AS NVARCHAR(1) = '0';
SET NOCOUNT ON
DECLARE @JobName AS NVARCHAR(255);
DECLARE @sqlEnable AS NVARCHAR(MAX);
DECLARE @sql AS NVARCHAR(MAX);
DECLARE @isEnabled AS NVARCHAR(1);
SET @sql = 'EXEC msdb.dbo.sp_update_job @job_name = N''${JobName}'', @enabled = ' + @newJobStatus + ';'
BEGIN TRY drop table #jobs; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH;
SELECT replace(@sql, '${JobName}', sysjobs.NAME) AS sqlEnable
, sysjobs.NAME AS JobName
, job_id
, [enabled] isEnabled
INTO #jobs
-- SELECT sysjobs.name 'Job Name' , job_id, [enabled] isEnabled, syscategories.name
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
WHERE
(syscategories.NAME = @Categoryname OR @Categoryname IS NULL) AND NOT ([enabled] = @newJobStatus)
WHILE ( EXISTS ( SELECT JobName FROM #jobs ) )
BEGIN
SELECT TOP 1 @JobName = JobName
, @sqlEnable = sqlEnable
, @isEnabled = isEnabled
FROM #jobs;
DELETE FROM #jobs
WHERE
JobName = @JobName;
print @sqlEnable;
EXEC (@sqlEnable)
END
Source : https://www.sqlservercentral.com/Forums/Topic1214637-391-1.aspx
Create mirror between two server without witness (High safety without automatic failover - synchronous).
This exemple use TechNet Virtual Labs :
Exploring AlwaysOn Availability Groups in SQL Server 2014
This script will not change anything in your database, it just will create the scripts for.
All script was create for TechLabs :
Primary Server SQL Server : SQLONE
Secondary Server SQL Server : SQLTWO
Mirroring User : contoso\SQLService
Temp Path : \\SQLONE\s$
Mirroing Port : 5022
ENDPOINT name : Hadr_endpoint
You need change it for yours servers.
Step zero:
We use TechLabs to do this tutorial then we delete all configurations before we start.
** Attention when you run on your SQL Server.**
1. Remove Database Mirroring;
2. Remove Certificates, Master Key, EndPoints. (if it's possible, and maybe, additional steps are necessary).
Step 1: Prepare mirror
1. Add Login user
You need to create a user on AD, and add this user on both SQL Servers
2. Create End Points
You need to use the name of login created the End Points, and you cannot use your login.
Step 2: Mirroring a database
1. Set full recovery model on database.
2. Change owner user to "sa".
It's not necessary, but if you do not change it, you must be sure that you have the same user on secondary server.
3. Set Trustworthy OFF
Trustworthy is always OFF on a new mirror database.
4. Backup database
5. Backup login
6. Restore database
7. Restore log
8. Set partners
* High safety without automatic failover:
All committed transactions are guaranteed to be written to disk on the mirror server.
Manual failover is possible when the partners are connected to each other and the database is synchronized.
The loss of a partner has the following effect:
* If the mirror server instance becomes unavailable, the principal continues.
* If the principal server instance becomes unavailable, the mirror stops but is accessible as a warm standby; the database owner can force service to the mirror server instance (with possible data loss).
* We will use xp_cmdshell.
Lire la suite de Setting Up Database Mirroring
You can manually disable or enable the service through SQL Server Management Studio or run the ALTER QUEUE command for:
-- Enable
ALTER QUEUE [queuename] WITH STATUS = ON;
-- Disable
ALTER QUEUE [queuename] WITH STATUS = OFF;
But, it can be complicated when you need over all database.
This script enable all queue over all databases:
print '-- SQL Script enable all queue on all databases'
print '-- '+ @@servername
print '-- '+ CAST(getdate() as nvarchar(50));
SET NOCOUNT ON;
Declare @sql nvarchar(max);
declare @dbname nvarchar(255);
BEGIN TRY drop table #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE' AND (f.database_id > 4) AND is_broker_enabled = 1;
BEGIN TRY DROP TABLE #temp; END TRY BEGIN CATCH END CATCH;
CREATE TABLE #temp(
[Id] INT IDENTITY,
[DatabaseName] [nvarchar](200) NOT NULL,
[Schemaname] [sysname] NOT NULL,
[QueueName] [sysname] NOT NULL,
[Sql] NVARCHAR(MAX)
) ON [PRIMARY]
DECLARE @sqlQueueOn AS NVARCHAR(MAX) = 'USE [@(dbname)]; ALTER QUEUE [@(SchemaName)].[@(QueueName)] WITH STATUS = ON;'
DECLARE @sqlSearch AS NVARCHAR(MAX) = 'insert into #temp (DatabaseName, SchemaName, QueueName) select ''@(dbname)'' as DatabaseName, s.name as Schemaname, q.name as QueueName FROM @(dbname).sys.service_queues q INNER JOIN @(dbname).sys.schemas s ON q.schema_id = s.schema_id where is_ms_shipped = 0 AND is_enqueue_enabled = 0';
while ((select count(*) from #db)>0)
BEGIN
SET @sql = '';
SELECT TOP 1 @dbname = [name] FROM #db f ORDER BY f.name;
DELETE FROM #db WHERE [name] = @dbname;
SET @sql = REPLACE(@sqlSearch, '@(dbname)',@dbname);
--PRINT @sql;
EXEC (@sql);
END
UPDATE #temp SET [Sql] = @sqlQueueOn;
UPDATE #temp SET [Sql] = REPLACE([Sql], '@(dbname)',[DatabaseName]);
UPDATE #temp SET [Sql] = REPLACE([Sql], '@(SchemaName)',[Schemaname]);
UPDATE #temp SET [Sql] = REPLACE([Sql], '@(QueueName)',[QueueName]);
DECLARE @id INT;
WHILE ((SELECT COUNT(*) FROM #temp) > 0)
BEGIN
SELECT TOP 1 @id = [Id], @sql = [Sql] FROM #temp;
DELETE FROM #temp WHERE [Id] = @id;
PRINT @sql;
EXEC(@sql);
END
Check SQL Version
SELECT
@@servername AS ServerName
, CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '8%' THEN 'SQL2000'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '9%' THEN 'SQL2005'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '10.0%' THEN 'SQL2008'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '10.5%' THEN 'SQL2008 R2'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '11%' THEN 'SQL2012'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '12%' THEN 'SQL2014'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '13%' THEN 'SQL2016'
ELSE 'unknown' END AS MajorVersion , SERVERPROPERTY('ProductLevel') AS ProductLevel
, SERVERPROPERTY('ProductVersion') AS ProductVersion , SERVERPROPERTY('Edition') AS Edition
, (SELECT COUNT(*) AS CPUs FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE' AND is_online = 1) AS CPUs
, (SELECT COUNT(*) as [Databases] FROM sys.databases where database_id > 4) AS [Databases]
, (SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) FROM master.sys.master_files) AS UsedSpace_GByte
, (SELECT sqlserver_start_time FROM sys.dm_os_sys_info) AS LastSQLServiceRestart
Source:
https://www.mssqltips.com/sqlservertip/1140/how-to-tell-what-sql-server-version-you-are-running/
https://www.mssqltips.com/sqlservertip/2501/find-last-restart-of-sql-server/
Before to start to change everything, you must have a baseline that it established in normal load operation for comparisons.
SELECT TOP 20
qs.sql_handle
, DB_NAME(CAST(pa.value as INT)) as DatabaseName
, qs.execution_count
, qs.total_worker_time AS Total_CPU
, total_CPU_inSeconds = qs.total_worker_time/1000000
, average_CPU_inSeconds = (qs.total_worker_time/1000000) / qs.execution_count
, qs.total_elapsed_time
, total_elapsed_time_inSeconds = qs.total_elapsed_time/1000000
, st.text , qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
cross apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
where pa.attribute = 'dbid'
ORDER BY qs.total_worker_time DESC
go
You can use SQL Server Performance Dashboard Reports.
https://sqldashboards.codeplex.com/
Source :
https://blogs.msdn.microsoft.com/sqltips/2005/10/05/find-top-n-costly-query-plans-in-adhoc-batches-or-modules/
https://www.johnsansom.com/how-to-identify-the-most-costly-sql-server-queries-using-dmvs/
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql