Category Archives: Best Practices

How to ReBoot all the machines at a time.


Pre-requisite:

a. Windows 2003 machine
b. Login or the scheduled job that runs this reboot.bat should have admin access to all the machines

Steps:

1. Step1: Create D:\scripts\reboot\serverlist.txt and the list all the machines that you want to reboot.

Example:
MyServer
SQL1Prod
SQL1QA
DOTNET1a

2. Step2: Create D:\scripts\reboot\reboot.bat 

REM Objective: TO reboot all the 
  machines listed in D:\script\reboot\serverlist.txt
REM Created by : MAK
REM Created Date: Jan 5, 2005
REM Save this file as 
  D:\scripts\reboot\reboot.bat
for /f "tokens=1,2,3" %%i in 
 (D:\scripts\reboot\serverlist.txt) 
 do c:\windows\system32\shutdown.exe /m \\%%i /r /f /c 
 "You machine is going to be restarted in 10 minutes" /t 600

3. Step3: Create D:\scripts\reboot\Abortreboot.bat

REM Objective: TO abort reboot on all the machine listed in 
  D:\script\reboot\serverlist.txt
REM Created by : MAK
REM Created Date: Jan 5, 2005
REM Save this file as D:\scripts\reboot\abortreboot.bat
for /f "tokens=1,2,3" %%i in 
  (D:\scripts\reboot\serverlist.txt) 
  do c:\windows\system32\shutdown.exe /a /m \\%%i 
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Code for Reboot.bat
----------------------
REM Objective: TO reboot all the machine listed in D:\script\reboot\serverlist.txt
REM Created by : MAK
REM Created Date: Jan 5, 2005
REM Save this file as D:\scripts\reboot\reboot.bat
for /f "tokens=1,2,3" %%i in (D:\scripts\reboot\serverlist.txt) do c:\windows\system32\shutdown.exe /m \\%%i /r /f /c "You machine is going to be restarted in 10 minutes" /t 600

Code for AbortReboot.bat
-------------------------
REM Objective: TO abort reboot on all the machine listed in D:\script\reboot\serverlist.txt
REM Created by : MAK
REM Created Date: Jan 5, 2005
REM Save this file as D:\scripts\reboot\abortreboot.bat
for /f "tokens=1,2,3" %%i in (D:\scripts\reboot\serverlist.txt) do c:\windows\system32\shutdown.exe /a /m \\%%i
Advertisements

Disable and Create Triggers


————

— Disable all triggers and constraints in a database

————
— Disable trigger stored procedure – disable constraint stored procedure

USE CopyOfAdventureWorks;

— SQL disable all triggers – disable all triggers sql server – t sql disable trigger

EXEC sp_MSforeachtable @command1=”ALTER TABLE ? DISABLE TRIGGER ALL”

GO

— SQL disable all constraints – disable all constraints sql server

EXEC sp_MSforeachtable @command1=”ALTER TABLE ? NOCHECK CONSTRAINT ALL”

GO

— Enable all triggers on a table

ALTER TABLE Production.Product ENABLE TRIGGER ALL

— Enable all check contraints on a table

ALTER TABLE Production.Product CHECK CONSTRAINT ALL

GO

— SQL enable all triggers – enable all triggers sql server – t sql enable trigger

EXEC sp_MSforeachtable @command1=”ALTER TABLE ? ENABLE TRIGGER ALL”

GO

— SQL enable all constraints – enable all constraints sql server
— sp_MSforeachtable is an undocumented system stored procedure

EXEC sp_MSforeachtable @command1=”ALTER TABLE ? CHECK CONSTRAINT ALL”

GO

————

— Single constraint disable and enable

————

USE CopyOfAdventureWorks;

— SQL disable constraint – alter table remove constraint

ALTER TABLE Production.Product NOCHECK CONSTRAINT CK_Product_DaysToManufacture

GO

— SQL enable constraint

ALTER TABLE Production.Product CHECK CONSTRAINT CK_Product_DaysToManufacture

GO

— SQL enable constraint with check of current data

ALTER TABLE Production.Product  WITH CHECK

                                CHECK CONSTRAINT CK_Product_DaysToManufacture
GO

— SQL enable constraint with no check of current data

ALTER TABLE Production.Product WITH NOCHECK

CHECK CONSTRAINT CK_Product_DaysToManufacture

— Check integrity of all constraints on a table

DBCC CHECKCONSTRAINTS(‘Production.Product’);

GO

/* DBCC execution completed. If DBCC printed error messages,

   contact your system administrator.

*/

————

————

— Single trigger disable and enable

————

— SQL disable trigger – alter table disable trigger

ALTER TABLE Sales.SalesOrderHeader DISABLE TRIGGER uSalesOrderHeader

GO

— SQL enable trigger

ALTER TABLE Sales.SalesOrderHeader ENABLE TRIGGER uSalesOrderHeader

GO

————

————

— SQL Server 2008 T-SQL insert, update, delete trigger demo

————

USE tempdb;

CREATE TABLE TriggerDemo (

      ID int identity(1,1) Primary Key,

      TextData varchar (64)

)

GO

IF OBJECT_ID (‘dbo.PrintTrigger’,’TR’) IS NOT NULL

    DROP TRIGGER Sales.reminder2;

GO

CREATE TRIGGER PrintTrigger

ON TriggerDemo

AFTER INSERT, UPDATE, DELETE

AS

BEGIN

    DECLARE @InsertedMessage varchar(max) = ‘New: ‘+(SELECT TextData FROM inserted)

    DECLARE @DeletedMessage  varchar(max) = ‘Old: ‘+(SELECT TextData FROM deleted)

      PRINT @InsertedMessage

      PRINT @DeletedMessage

END

GO

INSERT TriggerDemo (TextData) VALUES (‘Enable trigger demo’)

GO

— In messages: New: Enable trigger demo

UPDATE TriggerDemo SET TextData = ‘Disable trigger demo’

GO

/*

New: Disable trigger demo

Old: Enable trigger demo

*/

DELETE TriggerDemo

GO

— Old: Disable trigger demo

DROP TABLE TriggerDemo

GO

————

————

— Create a check constraint for a table and enable it

————

— SQL create check constraint

— Range constraint – column value must be between 0 and 100

USE AdventureWorks;

ALTER TABLE [Production].[ProductInventory]  WITH CHECK

ADD  CONSTRAINT [CK_ProductInventory_Bin]

CHECK  (([Bin]>=(0) AND [Bin]<=(100)))

GO

— SQL enable check constraint

ALTER TABLE [Production].[ProductInventory]

CHECK CONSTRAINT [CK_ProductInventory_Bin]

GO

SQL Server Best Practices (T-SQL)


  • Always qualify objects by owner
  • Do not use * in SELECT statements, always specify columns
  • Query “with (nolock)” when you don’t require high transactional consistency
  • Do not use GOTO
  • Avoid cursor use. If necessary always declare the correct type of cursor (FAST_FORWARD)
  • Avoid SELECT INTO for populating temp tables. Create the table then use INSERT SELECT.
  • Always use ANSI join syntax
  • Always check for object existance
  • Use SCOPE_IDENTITY() instead of @@IDENTITY
  • Always check @@TRANCOUNT and commit/rollback as necessary
  • Order DML to avoid deadlocks
  • Always check @@ERROR and @@ROWCOUNT by assigning to a variable
  • Always check sp return values
  • Do not create cross database dependencies
  • Avoid table value UDF – performance problems
  • Avoid dynamic SQL – if necessary use sp_executesql over EXEC
  • Avoid using NULL values
  • When there are only two values, ISNULL is more efficient than COALESCE
  • Try to avoid “SELECT *”, except these two cases: “WHERE EXISTS (SELECT * …)” and “SELECT COUNT(*) …”
%d bloggers like this: