how to enable rebuild index

SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' +  QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' REBUILD;'
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 1
go


select
    sys.objects.name,
    sys.indexes.name,is_disabled,sys.objects.type,sys.objects.type_desc,sys.indexes.type_desc
from sys.indexes
    inner join sys.objects on sys.objects.object_id = sys.indexes.object_id
where sys.indexes.is_disabled = 1
order by
    sys.objects.name,
    sys.indexes.name

How to remove publication

How to remove publication  -script to remove publication in

===================
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
SET @publicationDB = N'AdventureWorks';
SET @publication = N'AdvWorksProductTran';

-- Remove a transactional publication.
USE [AdventureWorks2012]
EXEC sp_droppublication @publication = @publication;

-- Remove replication objects from the database.
USE [master]
EXEC sp_replicationdboption
  @dbname = @publicationDB,
  @optname = N'publish',
  @value = N'false';
GO

SQL Blocking Query

        

/*
Discialmer:
The sample scripts are provided AS IS without warranty of any kind. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you.
In no event I shall be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information,
or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation.

*/
 SET NOCOUNT ON

-- Checked for currenlty running queries by putting data in temp table
SELECT s.session_id
    ,r.STATUS
    ,r.blocking_session_id
    ,r.wait_type
    ,wait_resource
    ,r.wait_time / (1000.0) 'WaitSec'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,r.total_elapsed_time / (1000.0) 'ElapsSec'
    ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
            (
                CASE r.statement_end_offset
                    WHEN - 1
                        THEN Datalength(st.TEXT)
                    ELSE r.statement_end_offset
                    END - r.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
    ,r.command
    ,s.login_name
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.last_request_end_time
    ,s.login_time
    ,r.open_transaction_count
INTO #temp_requests
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
    ,r.STATUS
    ,r.blocking_session_id
    ,s.session_id

IF (
        SELECT count(*)
        FROM #temp_requests
        WHERE blocking_session_id > 50
        ) <> 0
BEGIN
    -- blocking found, sent email.
    DECLARE @tableHTML NVARCHAR(MAX);

    SET @tableHTML = N'<H1>Blocking Report</H1>' + N'<table border="1">' + N'<tr>' + N'<th>session_id</th>' + N'<th>Status</th>' +
                     N'<th>blocking_session_id</th><th>wait_type</th><th>wait_resource</th>' +
                     N'<th>WaitSec</th>' + N'<th>cpu_time</th>' +
                     N'<th>logical_reads</th>' + N'<th>reads</th>' +
                     N'<th>writes</th>' + N'<th>ElapsSec</th>' + N'<th>statement_text</th>' + N'<th>command_text</th>' +
                     N'<th>command</th>' + N'<th>login_name</th>' + N'<th>host_name</th>' + N'<th>program_name</th>' +
                     N'<th>host_process_id</th>' + N'<th>last_request_end_time</th>' + N'<th>login_time</th>' +
                     N'<th>open_transaction_count</th>' + '</tr>' + CAST((
                SELECT td = s.session_id
                    ,''
                    ,td = r.STATUS
                    ,''
                    ,td = r.blocking_session_id
                    ,''
                    ,td = r.wait_type
                    ,''
                    ,td = wait_resource
                    ,''
                    ,td = r.wait_time / (1000.0)
                    ,''
                    ,td = r.cpu_time
                    ,''
                    ,td = r.logical_reads
                    ,''
                    ,td = r.reads
                    ,''
                    ,td = r.writes
                    ,''
                    ,td = r.total_elapsed_time / (1000.0)
                    ,''
                    ,td = Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
                            (
                                CASE r.statement_end_offset
                                    WHEN - 1
                                        THEN Datalength(st.TEXT)
                                    ELSE r.statement_end_offset
                                    END - r.statement_start_offset
                                ) / 2
                            ) + 1)
                    ,''
                    ,td = Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) +
                        N'.' + Quotename(Object_name(st.objectid, st.dbid)), '')
                    ,''
                    ,td = r.command
                    ,''
                    ,td = s.login_name
                    ,''
                    ,td = s.host_name
                    ,''
                    ,td = s.program_name
                    ,''
                    ,td = s.host_process_id
                    ,''
                    ,td = s.last_request_end_time
                    ,''
                    ,td = s.login_time
                    ,''
                    ,td = r.open_transaction_count
                FROM sys.dm_exec_sessions AS s
                INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
                CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
                WHERE r.session_id != @@SPID
                    AND blocking_session_id > 0
                ORDER BY r.cpu_time DESC
                    ,r.STATUS
                    ,r.blocking_session_id
                    ,s.session_id
                FOR XML PATH('tr')
                    ,TYPE
                ) AS NVARCHAR(MAX)) + N'</table>';

    EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML
        ,@body_format = 'HTML'
        ,@profile_name = N'DBMAIL'
        ,@recipients = N'sqldba2050@gmail.com'
        ,@Subject = N'Blocking Detected'
END

DROP TABLE #temp_requests

SQL WMI SQL CONFIGURATION MANGER NOT OPENING ISSUE

1) Connect the server
2) Open CMD with RUN as Administrator 

mofcomp "%programfiles(x86)%\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof" 

3) Change SQL Build version and run it .

SQL JOB STATUS REPORT

SELECT  cast(serverproperty('Machinename') as varchar(255)) Servername,isnull(cast(serverproperty('Instancename') as varchar(255)),
cast(serverproperty('Machinename') as varchar(255))) Instancename,
    j.name AS JobName,
    Isnull(Substring(CONVERT(VARCHAR(8), js.last_run_date), 1, 4) + '-' +Substring(CONVERT(VARCHAR (8), js.last_run_date), 5, 2) + '-' +  Substring(CONVERT(VARCHAR( 8), js.last_run_date), 7, 2), '') AS [Run DATE]
         ,CASE
        WHEN j.enabled = 1 THEN 'Enabled'
        ELSE 'Disabled'
    END JobStatus
    ,CASE
        WHEN js.last_run_outcome = 0 THEN 'Failed'
        WHEN js.last_run_outcome = 1 THEN 'Succeeded'
        WHEN js.last_run_outcome = 2 THEN 'Retry'
        WHEN js.last_run_outcome = 3 THEN 'Cancelled'
        ELSE 'Unknown'
    END JobRunStatus
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobservers js on js.job_id = j.job_id
where j.name in('DatabaseBackup - SYSTEM_DATABASES - FULL',
'DatabaseBackup - USER_DATABASES - DIFF',
'DatabaseBackup - USER_DATABASES - FULL',
'DatabaseBackup - USER_DATABASES - LOG','DatabaseIntegrityCheck - SYSTEM_DATABASES',
'DatabaseIntegrityCheck - USER_DATABASES',
'IndexOptimize - USER_DATABASES','Update Log Growth Setting',
'UpdateStats CUSTOM DAILY - USER_DATABASES') and j.enabled=1
ORDER BY j.name, js.last_run_date, js.last_run_time

SQL Query1

SELECT DB_NAME(DATABASE_ID)DBNAME, [NAME] LOGICAL_FILENAME, [SIZE]*8/1024 SIZE_MB
FROM MASTER.SYS.MASTER_FILES
WHERE DB_NAME(DATABASE_ID) = 'TEMPDB'

SQL Query to get ALTER Database Modify command

SELECT  'USE [MASTER] ' + CHAR(13) + 'ALTER DATABASE [' + d.name + N'] '
    + 'MODIFY FILE (NAME = ''' + mf.name + N''', FILENAME= '''+ mf.physical_name+ N''')'
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM    sys.master_files mf
    JOIN sys.databases d ON mf.database_id = d.database_id
WHERE   d.database_id > 4
        AND mf.type=1 and mf.physical_name like 'T:\%' and mf.name like 'WSS_%'


select 'ALTER DATABASE ['+ DB_NAME(database_id) +'] MODIFY FILE ( NAME = ''' +  name  + ''', FILEGROWTH = 100MB ) ' from sys.master_files 

SQL Query to find out SQL Blocking information

SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD -  '
ELSE '|------  ' END
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

SQL Query to get Database size infromation

SELECT Name,
[Filename],
CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2)/1024) [Currently Allocated Space (GB)],
CONVERT (Decimal(15,2), ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)/1024) AS [Space Used (GB)],
CONVERT (Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)/1024) AS [Available Space (GB)]
FROM dbo.sysfiles a (NOLOCK)
JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
ORDER BY b.groupname;