Restart Production Server

Restart Server
----------------------


USE master
go

-- On Monday Executes
IF (DATEPART(DW,GETDATE()) = 2)
BEGIN
EXEC Master.dbo.Sp_Configure 'Show Advanced Options', 1
RECONFIGURE
EXEC Master.dbo.Sp_Configure 'XP_CmdShell', 1
RECONFIGURE

EXEC Master.dbo.xp_cmdshell 'C:\WINDOWS\system32\shutdown.exe /r'

EXEC Master.dbo.Sp_Configure 'Show Advanced Options', 1
RECONFIGURE
EXEC Master.dbo.Sp_Configure 'XP_CmdShell', 0
RECONFIGURE
END

Updation of Statistics in Production for Each Table

Updation of Statistics 
-----------------------

The following script is used for update statistics either partial or full  for each table based on day of  the week.We are using this in our production.


DECLARE @dw INT
-- @lastrundate VARCHAR(10),, @Isexecute BIT
SELECT @dw = DATEPART(DW,GETDATE())
--SET @Isexecute = 1

IF (@dw = 6  or @dw =7 ) -- if  Thursday night or Friday night
--IF (@dw = 1 ) -- if  sunday
BEGIN

--SELECT @lastrundate = last_run_date FROM msdb.dbo.sysjobsteps WHERE Job_id = 'C6D5BFD5-61E4-4169-A0C0-D2C163AD69BE' AND step_id = 1

-- IF (@dw = 1 AND (@lastrundate = CONVERT(VARCHAR,GETDATE(),112)) ) -- If saturday and rebuild of index matched with statistics job same day
-- BEGIN
-- SET @Isexecute = 0
-- END
-- ELSE IF (@dw = 1 AND (@lastrundate <> CONVERT(VARCHAR,GETDATE(),112))  )
-- BEGIN                                                                    
-- SET @Isexecute = 0
-- END
--END

--IF (@Isexecute = 1)
--BEGIN
--- UPDATION OF STATISTICS
SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET NUMERIC_ROUNDABORT OFF

--PRINT 'UPDATE STATITSTICS WITH FULL SCAN'
EXEC sp_MSForeachtable "UPDATE STATISTICS ? WITH FULLSCAN"
END

Else
Begin

EXEC sp_updatestats
End

DBCC CHECKDB

DBCC CHECK
-------------------
-------------------

This DBCC command is used for checking the database consistency and it will send mail to recipients .This using in our production environment.

-- DBCC check

IF (DATEPART(DW ,GETDATE()) = 1)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ADMIN',
@recipients='krishna.sqlexpress@gmail.com',
@subject = 'DBCC OUTPUT : if any errors , contact ADMIN',
@query = 'DBCC CHECKDB (KRISHDB)',
@query_attachment_filename= 'DBCC_FULL_CHECK_output.txt',
@query_result_header = 0,
@query_result_width = 105,
@attach_query_result_as_file = 1
--PRINT 'SATURDAY'
END

RE-ORGANIZATION OF INDEXES

RE-ORGANIZATION OF INDEXES
----------------------------------------
This is the script we are using in Production for Re-Organize/Re-Build Indexes.

use KRISHDB

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON
DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

DECLARE @dbid smallint;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SET @dbid = DB_ID();

SELECT

    [object_id] AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag, page_count

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')

WHERE avg_fragmentation_in_percent > 5.0  -- Allow limited fragmentation

AND index_id > 0 -- Ignore heaps

AND page_count > 5; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

BEGIN

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag < 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

PRINT N'Executed: ' + @command;

END

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;

GO