Script:
Declare @UntilNo integer = 100
;With Fibonacci (N, NextN) AS
(
SELECT 0, 1
UNION ALL
SELECT NextN, NextN+ N
FROM Fibonacci
WHERE N < @UntilNo
)
SELECT Substring(
(SELECT cast(', ' as varchar(max)) + cast(N as varchar(max)
)
FROM Fibonacci
FOR XML PATH('')),3,10000000) AS list
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
Msg 9002, Level 17, State 6, Line 10 The transaction log for database ‘Restoredbname’ is full due to ‘REPLICATION’.It is also observed that log_reuse_wait_desc was showing “REPLICATION” for the database.
Select log_reuse_wait_desc,* From sys.databasesAs we know this db was enabled with CDC in Production environment, the first attempt was to disable CDC on restored database.
use Restoredbname sys.sp_cdc_disable_db
Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262 [Batch Start Line 2] Could not update the metadata that indicates database Restoredbname is not enabled for Change Data Capture. The failure occurred when executing the command ‘(null)’. The error returned was 9002: ‘The transaction log for database ‘Restoredbname’ is full due to ‘REPLICATION’.’. Use the action and error to determine the cause of the failure and resubmit the request.We also noticed checkpoint was also not successful due to serious disk issue.
checkpoint
Could not write a checkpoint record in database Restoredbname because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files. Msg 5901, Level 16, State 1, Line 10 One or more recovery units belonging to database ‘Restoredbname’ failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure. Msg 9002, Level 17, State 6, Line 10 The transaction log for database ‘Restoredbname’ is full due to ‘REPLICATION’.Finally, we decided to apply sp_repldone on the database as below. When xactid is NULL, xact_seqno is NULL, and reset is 1, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log. sp_removedbreplication stored procedure removes all replication objects on the publication database on the Publisher instance of SQL Server or on the subscription database on the Subscriber instance of SQL Server. Once we executed the commands, we were able to shrink the file and the size has been reclaimed to os.
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, @reset = 1 sp_removedbreplicationThe solution discussed is NOT a general solution nor for Production environments. This should ONLY be used for non-production or lower environments where the restored database is used for testing purpose and truncating log is not a concern! I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
Object creation script
First step is to create the objects to store the deadlock information. Please note, we are not doing to capture the deadlock information, but just the occurrence of deadlocks. So we need a table with DateAdded to denote the captured date and time and deadlocks column to denote the number of deadlocks occurred.
Drop Table if exists DeadlockTracker
CREATE TABLE DeadlockTracker(
DateAdded datetime NOT NULL
, Deadlocks int NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX IX_DeadlockTracker_DateAdded_U_C ON DeadlockTracker
(
DateAdded
) WITH (FillFactor = 100)
Capture Query
The next step is to define the capture process. The idea is to frequently query a dynamic management view – sys.dm_os_performance_counter and log the data in the table as defined int he first step. We need to capture these information in a defined interval, may be every 15 minutes. We can configure a SQL job to run every 15 minutes.
DECLARE @CounterPrefix NVARCHAR(30)
SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:'
ELSE 'MSSQL$' + @@SERVICENAME + ':'
END
INSERT INTO DeadlockTracker(DateAdded, Deadlocks)
SELECT DateAdded = GetDate()
, Deadlocks = (SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE object_name like @CounterPrefix + '%'
AND instance_name IN ('', '_Total')
AND counter_name ='Number of Deadlocks/sec')
Sample Report Dashboard Queries
This is the last step which is nothing but visualization of data captured. This is absolutely depending on data analyst discretion, but a very basic report sample has provided for your reference. You can change the query to get the data group by month/days/quarter etc.Select *, Deadlocks - Lag(Deadlocks) Over(Order by DateAdded asc) From DeadlockTrackerI’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
Script
DECLARE @DBNAME nvarchar(100) ,@FileName nvarchar(max) ,@Status nvarchar(10) SET @DBNAME = NULL -- Provide [DBNAME] SET @Status = 'OFFLINE' --Provide [OFFLINE / ONLINE] SELECT @FileName=[path] FROM sys.traces WHERE is_default=1 DECLARE @ErrorLogTable table (Logdate datetime, ProcessInfo nvarchar(10), [Text] nvarchar(max)) INSERT INTO @ErrorLogTable EXEC xp_readerrorlog 0,1, @Status, @DBNAME, NULL, NULL, 'desc' SELECT Distinct DatabaseID, DatabaseName, HostName, ApplicationName, LoginName, StartTime, B.ProcessInfo, B.Text FROM sys.fn_trace_gettable( @FileName, DEFAULT ) A Inner join @ErrorLogTable B on A.SPID = cast(SUBSTRING(B.ProcessInfo,5,5) AS int) and CAST(StartTime AS nvarchar)=cast(B.Logdate AS nvarchar)I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
Script
DECLARE @regpath NVARCHAR(128) DECLARE @regkey NVARCHAR(500) DECLARE @Inst VARCHAR(128) DROP TABLE IF EXISTS #AllInstancesOnMachine, #CurVer CREATE TABLE #AllInstancesOnMachine (InstanceName VARCHAR(128), RegPathToUse VARCHAR(128), MajorVersionFound VARCHAR(50)) CREATE TABLE #CurVer (RegValue VARCHAR(128), VersionFound VARCHAR(50)) INSERT INTO #AllInstancesOnMachine (InstanceName, RegPathToUse) EXEC master..xp_instance_regenumvalues @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL' DECLARE VersionCursor CURSOR FOR SELECT InstanceName, RegPathToUse FROM #AllInstancesOnMachine OPEN VersionCursor WHILE (1=1) BEGIN FETCH NEXT FROM VersionCursor INTO @Inst, @regpath IF @@FETCH_STATUS < 0 BREAK SET @regkey = N'Software\Microsoft\Microsoft SQL Server\' +@regpath+'\MSSQLSERVER\CurrentVersion' TRUNCATE TABLE #CurVer INSERT INTO #CurVer (RegValue, VersionFound) EXECUTE master.sys.xp_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = @regkey, @value_name = N'CurrentVersion' UPDATE #AllInstancesOnMachine SET MajorVersionFound = (SELECT CASE Parsename(VersionFound,4) when 10 then 'SQL 2008 or 2008 R2' when 11 then 'SQL 2012' when 12 then 'SQL 2014' when 13 then 'SQL 2016' when 14 then 'SQL 2017' End FROM #CurVer) WHERE InstanceName = @Inst END CLOSE VersionCursor DEALLOCATE VersionCursor SELECT * FROM #AllInstancesOnMachineI’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
