Context: Database log file is huge and you are not able to shrink it. You do not have more free space. Disaster is coming, or it is already there.

What you have done? I hope you never use this script, but if you need to use this script, you must to think how you never use again. This atomic database bomb.

Shrink database is bad practice (Increases fragmentation and reduces performance).  "Shrink database" is for puny dba, but this, well, you are god-like DBA.
I am sure, you have no choice at this moment.

This script creates a script to delete the log file when we use Always On.

Steps :

1. Remove database from availability group
2. Set recovery to simple
3. Shrink database
4. Set recovery to full
5. Backup database
6. Add database to availability group
7. Backup database
8. Restore database on secondary
9. Backup transactions
10. Restore transactions on secondary
11. Wait for the replica to start communicating
12. Alter database set HADR on secondary

Required:

1. Network Shared folder.
2. Use SQLCMD mode.
3. You must be sysadmin.
4. Change parameters : dbname, AlwaysOnName, FullPathBackupFile

If you use "Results to Text" on your query, probably, the created script will cut. Use "Results to grid" on you query execution.

/****************************************
*
* Tools for AlwaysOn - Exec on Primary Server
*
* When you use this:
*  - You have Always On Server
*  - Log file is full OR you need to add new database into 'Always On'
*
****************************************/
GO
--------------------------------------------------------------
-- Configuration --
--------------------------------------------------------------

DECLARE @dbname nvarchar(100) = 'Northwind';
DECLARE @AlwaysOnName nvarchar(100) = 'MyAlwaysOn';
DECLARE @FullPathBackupFile nvarchar(1024) = '\\SQL01\backup$\';

--------------------------------------------------------------

DECLARE @sql nvarchar(MAX);
DECLARE @PrimaryServer NVARCHAR(100);
DECLARE @SecondaryServer NVARCHAR(100);
SELECT @PrimaryServer = replica_server_name FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id WHERE role=1;
SELECT @SecondaryServer = replica_server_name FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id WHERE role=2;

IF (NOT (ISNULL(@PrimaryServer,'') = @@servername))
BEGIN
PRINT '--';
PRINT '-- You MUST execute this script on primary server.';
PRINT '--';
raiserror('You MUST execute this script on primary server.', 20, -1) with log
END

IF (NOT EXISTS(select * FROM sys.availability_groups WHERE [name] = @AlwaysOnName))
BEGIN
PRINT '';
PRINT '';
PRINT '-- We can not continue, Availability group does NOT exist: ';
PRINT ' ' + @AlwaysOnName;
PRINT '';
PRINT '';
raiserror('** We can not continue, Availability group does NOT exist **', 20, -1) with log;
END

IF (NOT EXISTS(select * FROM sys.databases WHERE [name] = @dbname))
BEGIN
PRINT '';
PRINT '';
PRINT '-- We can not continue, the database does NOT exist: ';
PRINT ' ' + @dbname;
PRINT '';
PRINT '';
raiserror('** We can not continue, Availability group does NOT exist **', 20, -1) with log;
END

SET NOCOUNT ON;
DECLARE @SHRINKFILE AS nvarchar(MAX) = '';
SELECT @SHRINKFILE=@SHRINKFILE+'USE [#(dbname)];DBCC SHRINKFILE (N'''+mf.name+''' , 0, TRUNCATEONLY);'+CHAR(10)+CHAR(13) FROM sys.master_files AS mf , sys.databases AS db WHERE mf.[type] = 1 AND mf.database_id = db.database_id AND db.name = @dbname;


IF (EXISTS(
SELECT replica_server_name
FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states
ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id
WHERE role=1 and replica_server_name = @@servername) -- role = 1 : primary
)
BEGIN


SET @sql = '
/****************************************
*
* DBA - Disaster Recovery
*
* You MUST exec on Primary Server
*
* Database name : #(dbname)
* Primary Server : #(PrimaryServer)
* Secondary Server : #(SecondaryServer)
*
* When you use this:
*    -  You have "Always On" SQL Server
*    -  Log file is full
*    -  You want to add new database into your "Always On"
*
* Script steps:
*    - Remove database from Availability Group, if it is there.
*    - Set recovery simple
*    - Shrink log file
*    - Backup database
*    - Set recovery full
*    - Backup database
*    - Restore database
*    - Backup transactions
*    - Restore transactions
*    - Add database to Always On
*
* by Sozezzo
* #(getdate)
*
****************************************/

-- Check SQLCMD mode
:SETVAR CHECK SQLCMD
GO
IF (NOT ''$(CHECK)'' = ''SQLCMD'')
BEGIN
PRINT '''';
PRINT '''';
PRINT '' ** YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. **'';
PRINT '''';
PRINT '''';
raiserror(''YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE'', 20, -1) with log
END

DECLARE @PrimaryServer NVARCHAR(100);
DECLARE @SecondaryServer NVARCHAR(100);
SELECT @PrimaryServer = replica_server_name FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id WHERE role=1;
SELECT @SecondaryServer = replica_server_name FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id WHERE role=2;

IF ( NOT ( ''#(PrimaryServer)'' = @PrimaryServer AND ''#(SecondaryServer)'' = @SecondaryServer))
BEGIN
PRINT '''';
PRINT '' ** The context of servers have been changed. **'';
PRINT '''';
PRINT '' This script will be create when '';
PRINT '' * Primary server is : #(PrimaryServer)'';
PRINT '' * Secondary server is : #(SecondaryServer)'';
PRINT '''';
raiserror(''The context of servers have been changed.'', 20, -1) with log
END

PRINT ''---------------------------------------------'';
PRINT '''';
PRINT ''-- Disaster recovery database : #(dbname)''
PRINT '''';
PRINT ''-- '' + CAST(GETDATE() AS NVARCHAR(100));
PRINT '''';
PRINT ''---------------------------------------------'';
GO
-- ALTER AVAILABILITY GROUP
:CONNECT #(PrimaryServer)
GO
USE [master];

IF (NOT EXISTS(select * FROM sys.availability_groups WHERE [name] = ''#(AlwaysOnName)''))
BEGIN
PRINT '''';
PRINT '''';
PRINT ''-- We can not continue, Availability group does NOT exist: '';
PRINT '' #(AlwaysOnName) '';
PRINT '''';
PRINT '''';
raiserror(''** We can not continue, Availability group does NOT exist **'', 20, -1) with log;
END

IF (exists(select * FROM sys.availability_groups AS ag INNER JOIN sys.availability_databases_cluster AS adc ON ag.group_id = adc.group_id WHERE adc.database_name = ''#(dbname)'' and ag.name = ''#(AlwaysOnName)''))
BEGIN
PRINT ''---------------------------------------------'';
PRINT ''-- REMOVE DATABASE [#(dbname)] FROM AVAILABILITY GROUP [#(AlwaysOnName)]'';
ALTER AVAILABILITY GROUP [#(AlwaysOnName)] REMOVE DATABASE [#(dbname)];
END
GO
-- Shrink database on primary server
:CONNECT #(PrimaryServer)
GO
USE [master];
GO
PRINT ''---------------------------------------------'';
PRINT ''Set recovery to simple with no wait'';
ALTER DATABASE [#(dbname)] SET RECOVERY SIMPLE WITH NO_WAIT
GO
PRINT ''---------------------------------------------'';
PRINT ''Shrink database on primary server'';
#(SHRINKFILE)
GO
USE [master];
GO
PRINT ''---------------------------------------------'';
PRINT ''Set recovery to full'';
ALTER DATABASE [#(dbname)] SET RECOVERY FULL WITH NO_WAIT;
GO
PRINT ''---------------------------------------------'';
PRINT ''Backup database : 1'';
-- FIX: Database might contain bulk logged changes that have not been backed up.
BACKUP DATABASE [#(dbname)] TO DISK = N''#(FullPathBackupFile)\#(dbname).bak'' WITH NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
GO
PRINT ''---------------------------------------------'';
PRINT ''Add database to availability group'';
ALTER AVAILABILITY GROUP [#(AlwaysOnName)] ADD DATABASE [#(dbname)];
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
GO
PRINT ''---------------------------------------------'';
PRINT ''Backup database : 2'';
-- FIX: This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.
BACKUP DATABASE [#(dbname)] TO DISK = N''#(FullPathBackupFile)\#(dbname).bak'' WITH NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
GO
-- Restore database on secondary server
:CONNECT #(SecondaryServer)
GO
IF (@@servername = ''#(SecondaryServer)'')
BEGIN
PRINT ''---------------------------------------------'';
-- FIX : Exclusive access could not be obtained because the database is in use
WAITFOR DELAY ''00:00:10'';
PRINT ''Restore database'';
RESTORE DATABASE [#(dbname)] FROM DISK = N''#(FullPathBackupFile)\#(dbname).bak'' WITH NORECOVERY, NOUNLOAD, STATS = 5;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
END
-------------------------------------
GO
-- Backup transaction database on primary server
:CONNECT #(PrimaryServer)
GO
IF (@@servername = ''#(PrimaryServer)'')
BEGIN
PRINT ''---------------------------------------------'';
PRINT ''Backup database log'';
BACKUP LOG [#(dbname)] TO DISK = N''#(FullPathBackupFile)\#(dbname).trn'' WITH NOFORMAT, INIT, NOSKIP, NOREWIND, NOUNLOAD, STATS = 10;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
END

GO
-- Restore transaction database
:CONNECT #(SecondaryServer)
GO
IF (@@servername = ''#(SecondaryServer)'')
BEGIN
PRINT ''---------------------------------------------'';
PRINT ''Restore database log'';
RESTORE LOG [#(dbname)] FROM DISK = N''#(FullPathBackupFile)\#(dbname).trn'' WITH NORECOVERY, NOUNLOAD, STATS = 5;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
END

---------------------------------------------
GO
:CONNECT #(SecondaryServer)
GO
IF (@@servername = ''#(SecondaryServer)'')
BEGIN
PRINT ''---------------------------------------------'';
PRINT ''Wait for the replica to start communicating'';
-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes

if (serverproperty(''IsHadrEnabled'') = 1)
and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty(''ComputerNamePhysicalNetBIOS'') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N''#(AlwaysOnName)''
select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
while @conn <> 1 and @count > 0
begin
set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
if @conn = 1
begin
-- exit loop when the replica is connected, or if the query cannot find the replica status
break
end
waitfor delay ''00:00:10''
set @count = @count - 1
end
end
end try
begin catch
-- If the wait loop fails, do not stop execution of the alter database statement
end catch

PRINT ''---------------------------------------------'';
PRINT ''Alter database set HADR'';
ALTER DATABASE [#(dbname)] SET HADR AVAILABILITY GROUP = [#(AlwaysOnName)];
END
GO
PRINT ''---------------------------------------------'';
PRINT ''-- '' + CAST(GETDATE() AS NVARCHAR(100));
PRINT ''-- done! '';

GO'
;

SET @sql = replace(@sql,'#(SHRINKFILE)' , @SHRINKFILE);
SET @sql = replace(@sql,'#(AlwaysOnName)' , @AlwaysOnName);
SET @sql = replace(@sql,'#(getdate)' , CAST(GETDATE() AS NVARCHAR(100)));
SET @sql = replace(@sql,'#(dbname)' , @dbname);
SET @sql = replace(@sql,'#(PrimaryServer)' , @PrimaryServer);
SET @sql = replace(@sql,'#(SecondaryServer)' , @SecondaryServer);
SET @sql = replace(@sql,'#(FullPathBackupFile)', @FullPathBackupFile);

declare @pText nvarchar(max) = @sql;
declare @pTextNewLine nvarchar(2) = CHAR(13) + CHAR(10); -- ** it is a good practice to use CR and LF together. CHAR(13) + CHAR(10)
declare @pTextMax int = 256;  -- ** default maximum number caracters displayed - SSMS -- but you can change it
declare @pTextPrint nvarchar(max);
declare @pTextCR Int
select @pText = @pText + @pTextNewLine;
while (LEN(@pText) > 0)
begin

 SELECT @pTextCR = CHARINDEX(@pTextNewLine, @pText);
 IF ((@pTextCR =-1) OR (@pTextCR > @pTextMax)) SELECT @pTextCR = @pTextMax;
 
 select @pTextPrint = SUBSTRING(@pText,0,@pTextCR),
 @pText = SUBSTRING(@pText, @pTextCR+len(@pTextNewLine), len(@sql));
 
 print @pTextPrint

end
END

This create a new script, copy and paste on new query, and execute with SQLCMD mode.

If you have any error, you can run over and over again and expecting a good results without errors. At this point, it's safe script.

Sources:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-add-a-database
https://blog.sqlauthority.com/2015/02/08/interview-question-of-the-week-006-is-shrinking-database-good-or-bad/
https://blog.sqlauthority.com/2015/08/08/sql-server-adding-file-to-database-in-alwayson-availability-group/
https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

When we have un have an unhealthy data synchronization state, we can always use graphic interface to resume data movement.

This script resume when the database need it, but you must connect on secondary server to run it.

-- You can use SQLCMD Mode
-- :CONNECT <MyServer>
PRINT '-- SQL Script to SET HADR RESUME'
PRINT ''
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);

DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT sys.databases.[name] INTO #db
FROM sys.dm_hadr_database_replica_states INNER JOIN sys.databases
    ON    sys.dm_hadr_database_replica_states.database_id = sys.databases.database_id
    WHERE
        sys.dm_hadr_database_replica_states.synchronization_health = 0
        AND sys.dm_hadr_database_replica_states.is_primary_replica = 0
        AND sys.databases.state_desc = 'ONLINE'
DECLARE @SqlTemplate nvarchar(MAX) = 'ALTER DATABASE [#(name)] SET HADR RESUME;'
WHILE ((SELECT count(*)    FROM #db)>0)
BEGIN

    SET @sql = '';
    SELECT TOP 1 @dbname = name
    ,            @sql = REPLACE(@SqlTemplate,'#(name)', name)
    FROM #db f ORDER BY f.name;
    DELETE FROM #db WHERE name = @dbname;

    IF NOT(@sql = '')
    BEGIN
        SET @sql = 'PRINT ''Database:'+@dbname+''';' + CHAR(13) + CHAR(10)+@sql;
        PRINT @sql;
        --EXEC (@sql);
    END

END

Lire la suite de Resuming data movement on database HADR

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

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

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/

Faire déverrouiller votre appareil dans un magasin de détail ou un concessionnaire (c'est "se faire voler").

•     Il vous impose un frais d’environ 50$ (taxes exigibles en sus)
•     Vous devez satisfaire une série des critères.
•     Vous pouvez perdre la garantie.
•     L'avertissement que certaines options pourraient ne plus fonctionner correctement.

Pourquoi débloquer mon cellulaire?

•     Économiser de l'argent.
•     Être libre d'utiliser n'importe quel opérateur de cellulaire.
•     C'est écolo, on recycle.

Mais, la question est toujours là. Où puis-je déverrouiller mon cellulaire sans me faire sentir "volé"?

J'ai vérifié dans un magasin de réparation de cellulaire, et j'étais découragé puisqu'il ne pouvait pas déverrouiller mon modelé.

Google est mon ami. Alors, après quelques recherches, je suis trouvé et décidé d'utiliser ce site:

http://www.cellunlocker.net/

Après quelques minutes, le service automatise a envoyé un code pour déverrouiller mon cellulaire, mais le code n'a pas fonctionné.

Heureusement, après un échange de courriels, ils ont envoyé le bon code.

Cellunlocker, Thanks!

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

A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped.

This code can be used to drop Login from SQL Server and user name associated with this Login in different databases.

------------------------------------
-- Drop user - SQL Script         --   
------------------------------------
--
-- Login name to delete
DECLARE @userToDelete nvarchar(200) = 'TestLogin';
--
--
IF (NOT EXISTS (SELECT * FROM sys.syslogins AS L WHERE L.loginname = @userToDelete))
BEGIN
    RAISERROR('
    Login not found.
    ', 50000, 0) WITH log
END


DECLARE @sql nvarchar(max);

PRINT '--  CHECK Databases'
USE [master];
SET @sql = '';
select @sql=@sql+'USE ['+d.name+']; EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false;'+CHAR(13) from  sys.sysdatabases d left join master.sys.syslogins l on d.sid = l.sid WHERE l.name = 'TestLogin';
PRINT @sql;
EXEC (@sql);


PRINT '--  CHECK Jobs'
USE [master];
SET @sql = '';
select @sql=@sql+'EXEC msdb.dbo.sp_update_job @job_name=N'''+s.name+''', @owner_login_name=N''sa'';'+CHAR(13) from  msdb..sysjobs s left join master.sys.syslogins l on s.owner_sid = l.sid WHERE l.name = @userToDelete
PRINT @sql;
EXEC (@sql);


PRINT '--  CHECK Schemas'
SET @sql = '
DECLARE @sql nvarchar(max);
SET @sql = '''';
SELECT @sql=@sql+''ALTER AUTHORIZATION ON SCHEMA::['' + s.name + ''] TO [dbo];''+CHAR(13) FROM [?].sys.schemas AS s INNER JOIN [?].sys.sysusers AS u ON s.principal_id = u.uid WHERE (u.name = '''+@userToDelete+''')
IF NOT (@sql='''')
BEGIN
    SET @sql = ''USE [?]''+CHAR(13)+@sql+''DROP USER ['+@userToDelete+']''+CHAR(13);
    PRINT @sql;
    EXEC (@sql);
END
'
exec sp_MSforeachdb @sql;


PRINT '--  CHECK Connections'
USE [master];
SET @sql = '';
select @sql = @sql + 'KILL ' + cast(spid AS NVARCHAR(10)) + ';'+ char(13) FROM master..sysprocesses Where loginame = @userToDelete;
PRINT @sql;
EXEC (@sql);

PRINT '-- DROP Login'
SET @sql = 'DROP LOGIN ['+@userToDelete+']'+CHAR(13);
PRINT @sql;
EXEC (@sql);

GO

 

source: https://msdn.microsoft.com/en-us/library/ms188012.aspx