Sunday, 10 January 2016

detect index fragmentation

Detecting index fragmentation for sql 2005 or above :

SELECT
                    DB_NAME() as database_name,
                    (SELECT DISTINCT ss.name+'.'+so.name FROM sys.objects

so
                        INNER JOIN sys.indexes on so.object_id=si.object_id
                        INNER JOIN sys.schemas AS ss on ss.schema_id = SO.schema_id
                    ) parenttable ,
                    si.name as indexname,
                    ps.avg_fragmentation_in_percent as frag,
                    i.rows,
                    (i.rows*ps.avg_fragmentation_in_percent) as tot_frg
            FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
            INNER JOIN sys.indexes AS si ON ps.OBJECT_ID = si.OBJECT_ID AND ps.index_id

=si.index_id
            INNER JOIN sysindexes i ON ( si.OBJECT_ID = i.id  and i.rows > 1000 )
            WHERE ps.database_id = DB_ID() and si.name is not null
            AND ps.index_id >0
            AND si.name=i.name

Detecting index fragmentation for sql 2000 or below :

 -- change the name of the target database here and in the variable @dbname below
SET NOCOUNT ON;
DECLARE @frag float;
DECLARE @dbname nvarchar(130);
DECLARE @dbid int;
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SET @dbname = N'use your db name'-- change the name of the target database here
SET @frag = 1.0              -- change this value to adjust the threshold for fragmentation
SELECT @dbid = dbid FROM sys.sysdatabases WHERE name = @dbname
SELECT
    PS.object_id AS Objectid,
      O.name AS ObjectName,
      S.name AS SchemaName,
      I.name AS IndexName,
    PS.index_id AS IndexId,
    PS.partition_number AS PartitionNum,
    ROUND(PS.avg_fragmentation_in_percent, 2) AS Fragmentation,
      PS.record_count AS RecordCount
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, 'SAMPLED') PS
      JOIN sys.objects O ON PS.object_id = O.object_id
      JOIN sys.schemas S ON S.schema_id = O.schema_id
      JOIN sys.indexes I ON I.object_id = PS.object_id
            AND I.index_id = PS.index_id
WHERE PS.avg_fragmentation_in_percent > @frag AND PS.index_id > 0
ORDER BY record_count desc;

Thursday, 22 October 2015

SQL service stuck in stopping state

Have came across an interesting scenario where sql service was hung in stopping state.

I do not want to restart the server, however wanted the sql service to be in running state.

I performed the following to get it done




 
 
 

Wednesday, 14 October 2015

SQL Head blocker information

Following are the scripts which can be used to identify head blocking sessions .

script 1:

--create alert to run a job when blocked sessions are more than 200

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'processes blocked',
  @message_id=0,
  @severity=0,
  @enabled=1,
  @delay_between_responses=900,
  @include_event_description_in=0,
  @category_name=N'[Uncategorized]',
  @performance_condition=N'SQLServer:General Statistics|Processes blocked||>|200',
  @job_id=N'give jobid of following job'
GO

/*create job which will run following script . use the job id of the job as parameter in above mentioned @job_id field */

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT spid,hostname,program_name,blocked,loginame FROM master.dbo.sysprocesses with (nolock)
 WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

 if @@ROWCOUNT >0
 begin
DECLARE @Body VARCHAR(8000) ='<br><br> Blocked sessions count is more than 200  Following are the head blocker sessions <br>   <br> <table border=1><tr><td>spid</td><td>status</td><td>program_name</td><td>login_name</td><td>host_name</td><td>open_tran</td><td>databasename</td><td>cmd</td><td>lastbatch</td><tr>'     
SELECT
 spid, status, program_name, loginame,
 hostname,
  blocked,
 open_tran,

  db_name(dbid) as databasename,cmd,
 
  last_batch into #temptable
 FROM master.dbo.sysprocesses
 WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
 AND blocked=0      
Declare @spid int , @status nvarchar(20) ,@programname nvarchar(128), @login nvarchar(128), @hostname nvarchar(128),@opentran smallint,@databasename nvarchar(20),@cmd nvarchar(20), @lastbatch datetime
       DECLARE headblock_Cursor CURSOR LOCAL FOR
              SELECT spid,status,program_name,loginame,hostname,open_tran,databasename,cmd,last_batch FROM #temptable
              OPEN headblock_Cursor;
              FETCH NEXT FROM headblock_Cursor INTO @spid,@status,@programname,@login,@hostname,@opentran,@databasename,@cmd,@lastbatch;
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@spid) + '</td>'
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@status) + '</td>'
         SET @Body = @Body + '<td>' + CONVERT(VARCHAR(128),@programname) + '</td>'
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@login) + '</td>'
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@hostname) + '</td>'
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@opentran) + '</td>'
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@databasename) + '</td>'
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@cmd) + '</td>'
      SET @Body = @Body + '<td>' +CONVERT(NVARCHAR(max),@lastbatch) +'</td>'
                           SET @Body = @Body + '</tr>'
                       FETCH NEXT FROM headblock_Cursor INTO @spid,@status,@programname,@login,@hostname,@opentran,@databasename,@cmd,@lastbatch;
                 END;
              CLOSE headblock_Cursor;
              DEALLOCATE headblock_Cursor;
     drop table #temptable
       SET @BODY = @BODY + '      </tr>              </table>              <br>              <br> '
    EXEC msdb.dbo.sp_send_dbmail @recipients='someone@domain.com;alternate@domain.com',
    @subject = 'winsqla10 blocking header',
    @body = @body,
    @body_format = 'HTML' ;
end


script 2 :

--run following to get head-blocker queries in SQL , it has sql text info as well

SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
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 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS NVARCHAR (10)) + ' ' + BATCH AS BLOCKING_TREE FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO
 

Thursday, 23 April 2015

automation for listing deadlocks in sql server

Following script creates stored procedure which can be called via sql job to run every 6 hours to get detals of dedalock on server through e-mail .

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[listDeadLocks]    Script Date: 04/23/2015 02:41:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[listDeadLocks]
AS
BEGIN
DECLARE @RawLogs TABLE (id INT IDENTITY (1, 1), logdate DATETIME, processinfo NVARCHAR(50), logtext NVARCHAR(MAX))
DECLARE @tab_Deadlocklist TABLE (deadlockNo INT ,  processinfo NVARCHAR(20), logtext VARCHAR(MAX))
--CREATE INDEX IDX_TEMP ON @tab  (logtext);
DECLARE @DEADLOCKQUERIES TABLE (logtext NVARCHAR(MAX))
DECLARE @SNAME AS NVARCHAR(64), @ERRORBODY VARCHAR(MAX), @BODY VARCHAR(MAX) ,@ERRORSUBJECT NVARCHAR(256) ,@TO AS NVARCHAR(100),@ERRORBODY_shell VARCHAR(MAX)
DECLARE @found INT , @insert_flag INT , @v_deadlock_ex_count INT, @DNO INT, @rawlogs_cnt INT
DECLARE @c1_logdate datetime, @c1_processinfo NVARCHAR(50), @c1_logtext varchar(max)
DECLARE @v_currentProcessId NVARCHAR(256),@v_processed NVARCHAR(256),@Db_name NVARCHAR(100)
DECLARE @timeInHours INT, @default_threshold_value INT

SET @found = 0
SET @timeInHours = 16  --- this takes last 6 hours deadlocks from ErrorLog
SET @DNO = 1
SET @insert_flag=0
SET @v_currentProcessId=''
SET @rawlogs_cnt = 0
SET @default_threshold_value=1
SELECT GETDATE() AS CurrentStartTime

INSERT INTO @RawLogs
EXEC sp_readerrorlog 1
SET @rawlogs_cnt = @@ROWCOUNT

SELECT GETDATE() AS CurrentTimeAfterRawlogs1

INSERT INTO @RawLogs
EXEC  sp_readerrorlog
SET @rawlogs_cnt = @rawlogs_cnt + @@ROWCOUNT

SELECT GETDATE() AS CurrentTimeAfterRawlogs2
SELECT @rawlogs_cnt

IF object_id('tempdb..#Database_Exceptions') is not null
BEGIN
DROP TABLE #Database_Exceptions
END
CREATE TABLE #Database_Exceptions
(
      DBname VARCHAR(50),
      deadlock_count INT
 );

 --trying append date to the error file
--DECLARE @vstrDate AS CHAR(10)
--select @vstrDate = CONVERT(char(10), GETDATE(), 121)
--select @vstrDate

 --- DeadLock threshold counts are defined in the following file.
BEGIN TRY
BULK
INSERT #Database_Exceptions
FROM '\\sharedpath\Admin\Scripts\SQL\Deadlock_DB_Execption_Count.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = '\\sharedpath\Admin\Scripts\SQL\Deadlock_DB_Execption_Count_Error.txt'
)
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;
        RETURN -1
END CATCH

select * from #Database_Exceptions
SELECT GETDATE() AS CurrentTimeAfterFileRead

DECLARE c1 CURSOR FOR
SELECT logdate,processinfo,logtext FROM @RawLogs
WHERE
logdate >= dateadd(hh, -1*@timeInHours, getdate())
and processinfo not in ('Backup','Logon')
ORDER BY processinfo,id asc;

OPEN c1
FETCH NEXT FROM c1 INTO @c1_logdate, @c1_processinfo, @c1_logtext

WHILE @@FETCH_STATUS = 0
BEGIN
     IF @c1_logtext ='deadlock-list' OR @insert_flag=1
     BEGIN
IF @v_currentProcessId=@c1_processinfo OR @v_currentProcessId=''
BEGIN
   SET @v_currentProcessId=@c1_processinfo
   INSERT INTO @tab_DeadLockList VALUES (@DNO,  @v_currentProcessId, @c1_logtext)
   SET @insert_flag=1
END
ELSE
BEGIN
SET @insert_flag=0
--SELECT @v_currentProcessId
SET @v_currentProcessId=''
SET @DNO = @DNO + 1
END
END
     FETCH NEXT FROM c1 INTO @c1_logdate, @c1_processinfo, @c1_logtext
     END
CLOSE c1
DEALLOCATE c1

SELECT GETDATE() AS TimeStampAfterC1
-- total deadlocks
--select @DNO-1
DECLARE @c2_deadId INT
DECLARE @c2_deadlocktext varchar(max)
DECLARE @c2_currentDeadId INT
DECLARE @c2_concattext VARCHAR(MAX)
set @c2_concattext= ''
DECLARE @v_cnt int
set @v_cnt=0
set @c2_currentDeadId=0
DECLARE c2 CURSOR FOR
SELECT deadlockNo ,logtext
      from @tab_Deadlocklist
  where --logtext like '%frame procname=marqueestats.dbo.Dispatcher_UpdateServer li%'
    logtext not like '%deadlock-list%'
and logtext not like '%deadlock victim%'
and logtext not like '%process-list%'
and logtext not like '%executionStack%'
and logtext not like '%process id%'
and logtext not like '%inputbuf%'
and logtext not like '%owner id%'
and logtext not like '%waiter-list%'
and logtext not like '%waiter id%'
and logtext not like '%resource-list%'
and logtext not like '%owner-list%'
and logtext not like '%pagelock fileid%'
and logtext not like '%exchangeEvent%'
and logtext not like '%==%'
-- added on 17/09/12
and logtext not like '--%'
and logtext not like 'keylock %'
ORDER BY deadlockNo ASC


OPEN c2
FETCH NEXT FROM c2 INTO @c2_deadId, @c2_deadlocktext

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @c2_currentDeadId=0 or @c2_currentDeadId=@c2_deadId
            BEGIN
SET @c2_currentDeadId=@c2_deadId
IF @c2_deadlocktext like '%frame procname%'
BEGIN
SET @c2_deadlocktext=LTRIM(REPLACE(@c2_deadlocktext,'frame procname=',CHAR(13)+'Procedure:  '))
SET @c2_deadlocktext=SUBSTRING(@c2_deadlocktext,1,CHARINDEX('line=',@c2_deadlocktext)-1) +'.  '+ CHAR(13)+ 'Query: '
END
SET @c2_concattext = @c2_concattext + @c2_deadlocktext
            END
     ELSE
BEGIN
   --- insert the contactenated queries into a temp table
INSERT INTO @DEADLOCKQUERIES  SELECT  @c2_concattext

SET @c2_concattext=''
SET @c2_currentDeadId=@c2_deadId
IF @c2_deadlocktext like '%frame procname%'
BEGIN
SET @c2_deadlocktext=LTRIM(REPLACE(@c2_deadlocktext,'frame procname=',CHAR(13)+'Procedure:  '))
SET @c2_deadlocktext=SUBSTRING(@c2_deadlocktext,1,CHARINDEX('line=',@c2_deadlocktext)-1) +'.  '+ CHAR(13)+ 'Query: '
--SET @db_name=SUBSTRING(@c2_deadlocktext,1,CHARINDEX('.',@c2_deadlocktext,1)-1)
--SELECT @Db_name
END
SET @c2_concattext = @c2_concattext + @c2_deadlocktext
END
   
    FETCH NEXT FROM c2
     INTO @c2_deadId, @c2_deadlocktext
END
INSERT INTO @DEADLOCKQUERIES SELECT  @c2_concattext

CLOSE c2
DEALLOCATE c2

SELECT GETDATE() AS TimeStampAfterC2

--SELECT TOP 5 COUNT(*),substring(logtext,1,500) FROM @DEADLOCKQUERIES
--GROUP BY substring(logtext,1,500)
----HAVING COUNT(*)>1

SET @SNAME=(SELECT @@SERVERNAME)

SET @TO='e-mail@domain.com;e-mail-group@domain.com;'

--SELECT TOP 5 COUNT(*),substring(logtext,1,500) FROM @DEADLOCKQUERIES
--WHERE lower(logtext) not like '%procedure%adhoc%'
--GROUP BY substring(logtext,1,500)
--HAVING COUNT(*)>1

DECLARE C3 CURSOR FOR
SELECT TOP 5 COUNT(*),substring(logtext,1,500) FROM @DEADLOCKQUERIES
--WHERE lower(logtext) not like '%procedure%adhoc%'
GROUP BY substring(logtext,1,500)
HAVING COUNT(*)>=@default_threshold_value --- min dead lock count changed to 50

OPEN c3
FETCH NEXT FROM c3 INTO @v_cnt, @BODY

WHILE @@FETCH_STATUS = 0
BEGIN
     SET @found =1
     SET @ERRORBODY = 'Following deadlock occured '+ cast(@v_cnt as nvarchar(10)) + ' time(s).   ' + CHAR(13) +@BODY
     --SET @ERRORBODY_shell= 'Following deadlock occured '+ cast(@v_cnt as nvarchar(10)) + ' time(s).`r`n ' +@BODY
     SET @Db_name=SUBSTRING(@BODY,12,CHARINDEX('.',@BODY,1)-12)
     SET @ERRORSUBJECT = @SNAME + ': DeadLocks Found in ' + UPPER(LTRIM(@Db_name)) + ' database.'
IF UPPER(LTRIM(RTRIM(@Db_name))) not like '%ADHOC%' AND upper(@Db_name) not like  '%MSDB%'

BEGIN
--SELECT  * FROM #Database_Exceptions
--WHERE UPPER(DBname)=  UPPER(@Db_name)
--SELECT  TOP 1 deadlock_count FROM #Database_Exceptions ---- to remove any duplicate entried in the file.
--WHERE UPPER(DBname)=  UPPER(LTRIM(RTRIM(@Db_name)))

set @v_deadlock_ex_count =  (SELECT  TOP 1 deadlock_count FROM #Database_Exceptions
WHERE UPPER(DBname)=  UPPER(LTRIM(RTRIM(@Db_name))) )

set @v_deadlock_ex_count = ISNULL(@v_deadlock_ex_count,@default_threshold_value)

SELECT UPPER(LTRIM(RTRIM(@Db_name))),@v_cnt, @v_deadlock_ex_count

IF  @v_cnt > @v_deadlock_ex_count
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Standard Profile',
@recipients = @TO,
@body = @ERRORBODY,
@subject = @ERRORSUBJECT ;

END
    END
   
     FETCH NEXT FROM C3
     INTO @v_cnt, @BODY
END

CLOSE c3
DEALLOCATE c3
SELECT GETDATE() AS TimeStampAfterC3
END



GO

on shared path deadlock_db_exception_count , edit the text file and type
database_name,<deadlock count>
EX: db1,3  -- 3 deadlcoks on db1 database


example e-mail :



Wednesday, 22 April 2015

SQL Configuration Manager WMI issue

If you see this specific issue while launching SQL Configuration Manager, you can follow the workaround specified in this blog post




Workaround:
Open Command Windows as Elevated administrator user
cd %ProgramFiles(x86)%\Microsoft SQL Server\110\Shared
mofcomp sqlmgmproviderxpsp2up.mof



Re launch SQL Configuration manager.

Monday, 6 April 2015

automating rebuild or reorg index on specific large tables of the database in SQL server which are really fragmented

 Following Script can be helpful to automate the Rebuild/Reorg Index on tables with in database which are huge and really fragmented.

Scenario :  While working with few clients I observed there was huge database with large table sizes and the indexes of the table were not much fragmented , but still there was maintenance plan configured to run rebuild index and update stats on all the tables of the database on every weekend

observations : performing index rebuild and update stats can cause performance issue (like CPU being pegged continuously) and log file growing huge.

Thus it makes sense to run the rebuild/reorg index only if its needed

following code serve the purpose :

Below code make use of dmv to identify fragmentation on the indexes with in the tables of the database , and perform either rebuild or reorg index only on affected indexes , later ran update stats only on the tables whose indexes were rebuild/reorganized

USE [provide dbname where table indexes are fragmented]

declare @dbid smallint = DB_ID();

declare @IndexInfo table (
  SchemaName SYSNAME NOT NULL,
  TableName SYSNAME NOT NULL,
  IndexName SYSNAME NOT NULL,
  IndexPageCount BIGINT NOT NULL,
  FragmentationPercent FLOAT NOT NULL,
  CurrentFillFactor TINYINT NOT NULL,
  StatsName NVARCHAR(256) NULL,
  StatsLastUpdate DATETIME2 NULL,
  StatsModificationCount BIGINT NULL,
  PRIMARY KEY (SchemaName, TableName, IndexName)
);

insert @IndexInfo (SchemaName, TableName, IndexName, IndexPageCount, FragmentationPercent, CurrentFillFactor, StatsName, StatsLastUpdate, StatsModificationCount)
select ss.name, so.name, si.name, ips.page_count, ips.avg_fragmentation_in_percent, si.fill_factor, st.name, stmeta.last_updated, stmeta.modification_counter
from sys.indexes si
join sys.[objects] so
  on so.[object_id] = si.[object_id]
join sys.schemas ss
  on ss.[schema_id] = so.[schema_id]
join sys.dm_db_index_physical_stats(@dbid, NULL, NULL, DEFAULT, DEFAULT) ips
on ips.database_id = @dbid
and ips.[object_id] = so.[object_id]
and ips.index_id = si.index_id
left join sys.stats st
  on st.[object_id] = so.[object_id]
  and st.[stats_id] = si.[index_id]
  and st.[is_temporary] = 0
  and st.[auto_created] = 0
  and st.[user_created] = 0
outer apply sys.dm_db_stats_properties(so.[object_id], st.stats_id) stmeta
where si.type_desc in ('clustered', 'nonclustered')
and so.is_ms_shipped = 0
  and ips.alloc_unit_type_desc = 'IN_ROW_DATA'
order by ips.page_count desc;

declare @line nvarchar(max) = null;
declare cur cursor local static for
select t.MaintenanceCommand
from (
  select 1 [Ordinal], p.IndexPageCount,
    N'SET QUOTED_IDENTIFIER ON;ALTER INDEX [' + p.IndexName + N'] ON [' + p.SchemaName + N'].[' + p.TableName + N'] ' +
    case when p.FragmentationPercent >= 30.0 then N'REBUILD PARTITION=ALL  WITH (ONLINE = ON);' else N'REORGANIZE PARTITION=ALL;' end + N'' [MaintenanceCommand]
  from @IndexInfo p
  where p.IndexPageCount >= 50
    and p.FragmentationPercent >= 5.0
union all
  select 2 [Ordinal], p.IndexPageCount,
    N'UPDATE STATISTICS [' + p.SchemaName + N'].[' + p.TableName + N'] [' + p.IndexName + N'] WITH FULLSCAN;' [MaintenanceCommand]
  from @IndexInfo p
  where p.StatsModificationCount > 100
    and (p.IndexPageCount < 50
      or p.FragmentationPercent < 30.0)
    and ((p.StatsModificationCount >= (p.IndexPageCount / 3))
      or datediff(day, p.StatsLastUpdate, sysdatetime()) >= 14)
) t
order by t.Ordinal asc, t.IndexPageCount desc;


open cur;
fetch next from cur into @line;
while @@fetch_status = 0 begin

exec sp_executesql @line

fetch next from cur into @line;
end;

close cur;
deallocate cur;

unable to shrink log file of database in SQL server due to log_reuse_wait_desc shows replication

Issue : When you try to shrink the database log file , you are unable to shrink

try to investigate the column select log_reuse_wait_desc,* from sys.databases

if the column shows replication for the referred database name , below are the observations to be made

1, Is replication really configured on the database (the reason i said this is because some times database is restored from backup file , and the backup file is of the database on source server where replication is configured )

2, replication is configured (i.e database is publisher and there observes to be replication issue in launch replication monitor for the specified publication)

3, CDC (change data capture) is enabled for the database and the job cdc.dbname_capture isn't running or in an bad state


Case I :  The database log_reuse_wait_desc column shows replication and there wasn't any replication configured on the server

later I observed the database was restored form the backup file , which was of the database (db_a) which has replication configured on server a (servera is source server where db_a database exists and acts as a publisher)

Steps taken to fix the issue :

Use [provide dbname where the issue is observed]

EXEC sp_repldone @xactid = NULL,
  @xact_segno = NULL, @numtrans = 0,
  @time= 0, @reset = 1

sp_removedbreplication [provide dbname where the issue is observed]

Case II : Database was configured with CDC (Change data Capture) and observed job status through sql server agent suggests cdc.dbname_capture jobs was idle and last run outcome was failed.

restarted the job and  soon available free space in log file of database increased (thus shrinking of log file is possible now)

note : cdc.dbname_capture job is expected to be running continuously or at specified intervals , based up on the requirement or business needs for CDC configuration

** for checking database log file free space and shrinking the log file to release back the space to OS

Run the following code to see the free space of the database files (all data and log files might appears)

USE  [provide dbname where the issue is observed]

select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,50),
FILENAME = left(a.FILENAME,100)
from
sys.sysfiles a

** If above code shows log file of database has more than 60% free space , proceed with shrinking log file by code

USE  [provide dbname where the issue is observed]
DBCC shrinkfile ('logical_name_of_file',size_in_MB)

--size_in_MB is the amount of space that to be left in the file after shrink finishes