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

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. 1 2-- You can use SQLCMD Mode 3-- :CONNECT <MyServer> 4PRINT '-- SQL Script to SET HADR RESUME'...

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. 1 2--generate disable 3SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs 4...

We have a lot of blogs and documents that can explain how service broker works. https://msdn.microsoft.com/en-us/library/bb839489(v=sql.105).aspx This step by step how to configure SQL Server Service Broker between two servers. You have two Windows Servers with SQL Server each one with a database. We will use this list...

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

You can manually disable or enable the service through SQL Server Management Studio or run the ALTER QUEUE command for: 1 2-- Enable 3ALTER QUEUE [queuename] WITH STATUS = ON; 4 5-- Disable 6ALTER QUEUE [queuename] WITH STATUS = OFF; But, it can be complicated when you need over all database. This script enable all...

Check SQL Version 1 2SELECT 3@@servername AS ServerName 4, CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '8%' THEN 'SQL2000' 5WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '9%' THEN 'SQL2005' 6WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '10.0%' THEN...

Before to start to change everything, you must have a baseline that it established in normal load operation for comparisons. 1 2SELECT TOP 20 3 qs.sql_handle 4, DB_NAME(CAST(pa.value as INT)) as DatabaseName 5, qs.execution_count 6, qs.total_worker_time AS Total_CPU 7, total_CPU_inSeconds = qs.total_worker_time/1000000...

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. 1 2------------------------------------ 3-- Drop user -...

Enable broker service on all databases but it check if Service Broker is enabled. 1 2-- ***************************************** -- 3-- SQL Script to ENABLE_BROKER 4-- ***************************************** -- 5-- 6PRINT @@servername; 7PRINT Getdate(); 8BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH; 9...

We have many ways to drop a database, but if you want to drop many databases. You can have some problems with opened connections, but this script kill all connection and drop the database. 1 2-- ***************************************** -- 3-- SQL Script to drop databases 4-- *****************************************...

How reset the ‘sa’ password of SQL Server: Plan A - If you have an account in sysadmin role then you can use SSMS or T-SQL: 1 2GO 3ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master] 4GO 5USE [master] 6GO 7ALTER LOGIN [sa] WITH PASSWORD=N'Pass1word$' 8GO Plan B- If you do not have an account in sysadmin role, but you are...

Unused indexes should be deleted, but you would not drop all the unused indexes without deep analyse. This a script delete all unused indexes. - Nonclustered indexes Non primary keys Non unique Non unused (#Total reads < 100) Finding Unused Indexes – Only Nonclustered 1 2SELECT 3 db_name() as DatabaseName 4...

Script to show all opened transactions: 1 2SELECT d.name as DatabaseName 3, ses.host_name as [host_name] 4, ses.login_time AS session_login_time 5, ses.last_request_start_time 6, ses.last_request_end_time 7, ses.login_name 8, ses.nt_user_name 9, ses.STATUS 10, tst.session_id as SPID 11, tst.enlist_count AS...

We can easily concatenate column values into a comma-separated list with a variable, but this solution we can avoid to use a variable. 1PRINT '-- Concatenating Column Values into a Comma-Separated List' 2 3GO 4 5--#region Create my test table 6 7begin try drop table YourTable ; end try begin catch end catch -- begin...

This script backup and shrink all database. Everybody says to never use it but shrinking is necessary if your log/data has grown out of control, or as part of a process to remove excessive fragmentation. Source: http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/...

XML original: 1 2declare @xml xml = ' 3<MyRow rowValue2="NewValue3"> 4 <IdProduct>312345</IdProduct> 5 <CodeProduct>CAPEX</CodeProduct> 6 <IdCaract1>12</IdCaract1> 7 <IdCaract2>23</IdCaract2> 8 <Description>XML Support in Microsoft SQL Server</Description> 9 <item> 10 <myitem>13</myitem> 11 </item> 12 <item> 13...

Script pour créer un script de création et manutention de messages d’erreur. Il y a de scripts sur l’internet qui peut vous aider, mais ces scripts ne sont pas “Safe to run”. Sources : https://msdn.microsoft.com/en-us/library/ms178649.aspx http://sqlmag.com/blog/migrating-custom-error-messages-between-servers 1 2PRINT...

We have same SQL Scripts to try to find out about what it happens when we have or had deadlocks. Get current running commands. Create event to find blocked processes and deadlocks. Sources : Deadlocking https://technet.microsoft.com/en-us/library/ms177433%28v=sql.105%29.aspx How to isolate the current running commands...

If you see messages like that.. Failed to retrieve data for this request. Failed to connect to server Logon failed for login OR Cannot connect to server… We have an easy solution! You just need disable all server trigger! but… When it’s happen, “maybe” you don’t know the trigger’s list or you don’t have so much time....