hello experst,
I am trying to execute the following procedure and it is showing me the error:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 50000, Level 16, State 1, Procedure spCheckDBInfo, Line 193 [Batch Start Line 0]
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
I use sql 2016, the same sp procedure is working ok in another database sql 2008
thanks
USE [DBname]
GO
/****** Object: StoredProcedure [dbo].[spCheckDBInfo] Script Date: 2019-02-15 10:12:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCheckDBInfo]
@dbName sysname = NULL, -- Database name. If NULL, will check all databases
@PHYSICAL_ONLY bit = 0, -- Set to 1 to perform physical check only. Defaults to 0.
@allMessages bit = 0, -- Set to 1 to return all the messages generated by DBCC
-- Set to 0 to return one summary message for each database (default)
@dbmail_profile sysname = NULL, -- DBMail profile to use when sending the results
@dbmail_recipient sysname = NULL, -- DBMail recipient
@log_to_table bit = 0 -- Set to 1 to enable logging to table DBCC_CHECKDB_HISTORY
AS
BEGIN
SET NOCOUNT,
XACT_ABORT,
QUOTED_IDENTIFIER,
ANSI_NULLS,
ANSI_PADDING,
ANSI_WARNINGS,
ARITHABORT,
CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
DECLARE @version INT,
@sql NVARCHAR(4000),
@ErrorMessage NVARCHAR(2048),
@body NVARCHAR(max),
@Message NVARCHAR(4000),
@Severity INT,
@State INT,
@impt VARCHAR(10)
-- determine major version: DBCC output can be different
SELECT @version = CAST(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS char(2)),'.','') AS int)
IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
DROP TABLE ##DBCC_OUTPUT
-- SQL2005, SQL2008, SQL2008R2 produce this output:
CREATE TABLE ##DBCC_OUTPUT(
Error int NULL,
[Level] int NULL,
State int NULL,
MessageText nvarchar(2048) NULL,
RepairLevel nvarchar(22) NULL,
Status int NULL,
DbId int NULL,
ObjectId int NULL,
IndexId int NULL,
PartitionId bigint NULL,
AllocUnitId bigint NULL,
[File] smallint NULL,
Page int NULL,
Slot int NULL,
RefFile smallint NULL,
RefPage int NULL,
RefSlot int NULL,
Allocation smallint NULL
)
-- Add a computed column
ALTER TABLE ##DBCC_OUTPUT ADD Outcome AS
CASE
WHEN Error = 8989 AND MessageText LIKE '%0 allocation errors and 0 consistency errors%' THEN 0
WHEN Error <> 8989 THEN NULL
ELSE 1
END
-- Add an identity column to sort results when sending the email
ALTER TABLE ##DBCC_OUTPUT ADD RowID int IDENTITY(1,1)
DECLARE @localTran bit
IF @@TRANCOUNT = 0
BEGIN
SET @localTran = 1
BEGIN TRANSACTION LocalTran
END
BEGIN TRY
DECLARE c_databases CURSOR LOCAL FAST_FORWARD FOR
SELECT QUOTENAME(name) AS name FROM master..sysdatabases
WHERE name NOT IN ('master','tempdb','msdb','model')
OPEN c_databases
FETCH NEXT FROM c_databases INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build an SQL string
SET @sql = 'DBCC CHECKDB('+ @dbName +') WITH TABLERESULTS, ALL_ERRORMSGS '
IF @PHYSICAL_ONLY = 1
SET @sql = @sql + ', PHYSICAL_ONLY '
BEGIN TRY
INSERT INTO ##DBCC_OUTPUT
EXEC(@sql)
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'Unable to run DBCC on database ' + @dbName + ': ' + ERROR_MESSAGE()
INSERT INTO ##DBCC_OUTPUT (Error, MessageText)
SELECT Error = 8989, MessageText = @ErrorMessage
END CATCH
FETCH NEXT FROM c_databases INTO @dbName
END
CLOSE c_databases
DEALLOCATE c_databases
IF NOT EXISTS (SELECT 1 FROM ##DBCC_OUTPUT)
BEGIN
RAISERROR('No database matches the name specified.',10,1)
END
-- Build the final SQL statement
SET @sql =
'SELECT ISNULL(DB_NAME(DbId),''resourcedb'') AS DatabaseName, ' +
CASE @allMessages
WHEN 1 THEN '*'
ELSE 'MessageText, Outcome'
END + '
FROM ##DBCC_OUTPUT
WHERE 1 = 1 ' +
CASE @allMessages WHEN 1 THEN '' ELSE 'AND Error = 8989' END
IF @dbmail_profile IS NULL OR @dbmail_recipient IS NULL
BEGIN
-- Query DBCC output directly
EXEC(@sql)
END
ELSE
BEGIN
-- Pipe DBCC output to a variable
SET @sql = '
SELECT @body = (
SELECT ISNULL(MessageText,'''') + char(10) AS [text()]
FROM ( ' + @sql + ' AND Error = 8989 ) AS src
ORDER BY 1 DESC FOR XML PATH(''''))'
EXEC sp_executesql @sql, N'@body nvarchar(max) OUTPUT', @body OUTPUT
-- Send CHECKDB report
IF @body IS NOT NULL
BEGIN
IF @version > 8
BEGIN
IF EXISTS(SELECT Outcome FROM ##DBCC_OUTPUT WITH (NOLOCK) WHERE Error = 8989 AND Outcome = 1)
BEGIN
SET @impt = 'High'
END
ELSE
BEGIN
SET @impt = 'Normal'
END
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @dbmail_profile,
@recipients = @dbmail_recipient,
@subject = 'CheckDB Monthly Report',
@body = @body,
@importance = @impt,
@query = '
SET NOCOUNT ON;
SELECT TOP(5000) ISNULL(DB_NAME(DbId),''resourcedb'') + '' -'' AS DatabaseName, MessageText
FROM ##DBCC_OUTPUT WITH (NOLOCK)
WHERE DbId IN (SELECT DbId FROM ##DBCC_OUTPUT WITH (NOLOCK) WHERE Error = 8989 AND Outcome = 1)
ORDER BY RowId ASC',
@attach_query_result_as_file = 1,
@query_result_no_padding = 1,
@query_result_header = 0,
@exclude_query_output = 1,
@query_attachment_filename = 'DBCC_CHECKDB_Errors.log'
END
END
END
IF @localTran = 1 AND XACT_STATE() <> 0
COMMIT TRAN LocalTran
IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
DROP TABLE ##DBCC_OUTPUT
END TRY
BEGIN CATCH
SELECT @Message = ERROR_MESSAGE(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE()
IF @localTran = 1 AND XACT_STATE() <> 0
ROLLBACK TRAN
RAISERROR ( @Message, @Severity, @State)
END CATCH
END