我了解 sql server 将更新写入内存中的数据页(使它们变脏),并在向用户发送提交通知之前将 tlog 写入磁盘。
这个问题是在以下任一情况下提出的:
- 将脏页从内存刷新到磁盘后
- 或者在重新启动后 sql 崩溃
sql server 如何知道哪些脏页(由于最近的一组 tlogs 导致的数据更改)已写入磁盘或尚未写入磁盘?
我了解 sql server 将更新写入内存中的数据页(使它们变脏),并在向用户发送提交通知之前将 tlog 写入磁盘。
这个问题是在以下任一情况下提出的:
sql server 如何知道哪些脏页(由于最近的一组 tlogs 导致的数据更改)已写入磁盘或尚未写入磁盘?
我正在阅读有关 SQL Server延迟持久性功能的文档,并看到了这张表:
它指出数据库级别设置始终优先于提交级别查看。但我无法理解这张桌子。
据我了解,2 是数据库级别设置,3 必须是提交级别设置。但是为什么他们的交叉点位置 4 表明结果是延迟持久的呢?
有人可以帮我理解如何阅读这张表吗?
当对Temporal Table执行 DML 和 DDL 查询时,这些更改会适当地传播到相关的 History 表。例如UPDATE
,临时表会导致将新记录INSERT
编辑到历史表中以反映该更改。
是INSERT
记录在事务日志中的历史表以及其他传播的更改吗?
这是否意味着启用临时表将有效地使通过事务日志的数据量翻倍(至少对于恢复模型设置为完整的数据库)?
在我之前的问题中,我询问了如何知道我的 SQL-Server 2019 数据库中发生了什么。过了一会儿,我发现有一种表格,叫做fn_dblog
,它提供了一些有趣的信息,但我仍然有一些问题(字面意思是从上一个问题复制而来):
SELECT Top 10 [Begin Time] [End Time]
FROM fn_dblog(null,null)
WHERE UPPER(AllocUnitName) LIKE '%TABLENAME_STATUS%'
...由我的 Microsoft SQL Server Management Studio 提出的位置[Begin Time]
和位置。[End Time]
令我惊讶的是,结果如下所示:
Title: End Time
NULL
...
NULL
现在我有以下问题:
[Begin Time]
柱子怎么了?[End Time]
没有填写?fn_dblog
,列名不会出现在剪贴板中。为什么会这样,我该如何改变呢?除此之外,我还发现了另一个“表”,名为fn_full_dblog
,但更糟糕的是:
SELECT Top 10 *
FROM sys.fn_full_dblog(null,null, null, null, null, null,
null, null, null, null,null)
WHERE UPPER(AllocUnitName) LIKE '%TABLENAME_STATUS%'
=> 根本没有结果!
有人可以给我一些关于如何阅读fn_dblog
以及可能fn_full_dblog
或任何其他可能使用有趣信息的“表格”的信息吗?
提前致谢
假设数据文件在 D: 并且日志文件在 E:
假设 E 驱动器崩溃并且日志文件 (.ldf) 丢失。我将一个新的空磁盘附加到 E: 并启动 sql server。
启动时,SQL Server 将意识到 .mdf 文件存在,但日志文件不存在。由于缺少日志文件,SQL 服务器将无法执行任何撤消/重做恢复步骤,并将数据库视为可疑对象。
注意:我试过这个并注意到数据库进入了可疑模式。这里的最佳做法是使用备份(完整 + tlog)来恢复数据库。在我的情况下,我从上午 12 点开始进行完整备份,但没有 tlog 备份。如果我从上午 12 点的备份中恢复,那么我会丢失一整天的数据(假设崩溃发生在中午)。我正在尝试通过利用现有数据文件来考虑如何在这种情况下进行。
事务日志中数据修改的日志记录:是只包含受影响的行和列的前后状态还是包含所有列(受影响的行)的前后状态,即日志记录包含整行还是仅包含被修改的行的一部分?
谢谢
编辑:我现在创建了一个实验,似乎表中的列数不会影响数据修改的事务日志的大小,即如果我col2
在表中更新,那么表是否有 5 列或100 列,事务日志的增长将是相同的。
我正在运行 SQL Server 2019 企业版。每个月,我们都会运行一个存储过程来加载数百万条服务日期可以追溯到五年前的记录。我为 4 个数据文件分配了 400GB 的空间,并为日志文件分配了 100GB 的空间。作业经常失败,因为日志文件因活动事务而被填满。数据库处于简单恢复模式。所以,我相信它应该在每笔交易结束时清除。开发人员更改了工作,使其一次循环并加载一年的记录。
DROP TABLE IF EXISTS #UnpvtDx;
SELECT ClaimHeader_ID
,ClaimDetail_ID
,ClaimServiceLine
,Unpvt.CodeLine
,Unpvt.DxCode
INTO #UnpvtDx
FROM PRINCE.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT
(
DxCode FOR CodeLine
IN
(
Diagnosis1CD,Diagnosis2CD,Diagnosis3CD,Diagnosis4CD,Diagnosis5CD,
Diagnosis6CD,Diagnosis7CD,Diagnosis8CD,Diagnosis9CD,
Diagnosis10CD,Diagnosis11CD,Diagnosis12CD,Diagnosis13CD
)
) as Unpvt ---53 secs
WHERE YEAR(ServiceFromDT) = @year;
DROP TABLE IF EXISTS #UnpvtPointer;
SELECT ClaimHeader_ID
,ClaimDetail_ID
,ClaimServiceLine
,Unpvt.CodeLine
,Unpvt.Pointer
INTO #UnpvtPointer
FROM PRINCE.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT
(
Pointer FOR CodeLine
IN (DiagPointer1,DiagPointer2,DiagPointer3,DiagPointer4)
) as Unpvt ---40 secs
WHERE YEAR(ServiceFromDT) = @year;
INSERT INTO PROD.Claim.ClaimDiag
(
ClaimHeader_ID,ClaimDetail_ID,SourceID,EDWLoadDTS,PartnerCD,
PartnerNM,ClaimID,ClaimServiceLine,ClaimStatus,CCOMemberID,
MemberID,PlaceOfServiceCD,ServiceFromDT,ServiceToDT,ClaimForm,
TypeOfBillCD,DiagnosisCD,DiagnosisDESC,DiagPointer
)
SELECT DISTINCT
det.ClaimHeader_ID,det.ClaimDetail_ID,det.SourceID,det.EDWLoadDTS,
det.PartnerCD,det.PartnerNM,det.ClaimID,det.ClaimServiceLine,
det.ClaimStatus,det.CCOMemberID,det.MemberID,det.PlaceOfServiceCD,
det.ServiceFromDT,det.ServiceToDT,det.ClaimForm,det.TypeOfBillCD,
DiagnosisCD = dx.DxCode,
DiagnosisDESC = diag.DiagnosisDESC,
DiagPointer = point.Pointer
FROM PROD.Claim.ClaimDetail det WITH (NOLOCK)
INNER JOIN PROD.Claim.ClaimHeader ch WITH (NOLOCK)
ON ch.ClaimHeader_ID = det.ClaimHeader_ID
INNER JOIN #UnpvtDx dx
ON dx.ClaimDetail_ID = det.ClaimDetail_ID
AND dx.ClaimHeader_ID = det.ClaimHeader_ID
AND dx.ClaimServiceLine = det.ClaimServiceLine
LEFT JOIN #UnpvtPointer point
ON point.ClaimDetail_ID = det.ClaimDetail_ID
AND point.ClaimHeader_ID = det.ClaimHeader_ID
AND point.ClaimServiceLine = det.ClaimServiceLine
LEFT OUTER JOIN Reference.Reference.Diagnosis diag WITH (NOLOCK)
ON dx.DxCode = diag.DiagnosisCD
AND diag.ICDVersion = 'ICD10CM'
AND diag.ActiveFLG = 1
WHERE YEAR(det.ServiceFromDT) = @year;
使用以下命令从 SQL 代理作业执行存储过程:
DECLARE @year INT
DECLARE cur CURSOR FOR
SELECT yr = YEAR(hdr.MinServiceFromDT)
FROM PROD.Claim.ClaimHeader hdr WITH (NOLOCK)
GROUP BY YEAR(hdr.MinServiceFromDT)
ORDER BY YEAR(hdr.MinServiceFromDT)
OPEN cur
FETCH NEXT FROM cur INTO @year
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC Claim.sp_UpdateClaimDiag @year
FETCH NEXT FROM cur INTO @year
END
CLOSE cur
DEALLOCATE cur
循环的结束是否被视为一个事务,因此日志文件应该在处理完每一年的记录后清空,或者日志文件是否继续填充,直到作业遍历每个循环并加载所有年份的记录?
我还将日志文件增加到 150GB,但这会最大化可用空间(不会低于 10% 的缓冲区)。
发布整个存储过程代码。
USE [Prod]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [Claim].[sp_UpdateClaimDiag] @year INT
AS
BEGIN
SET ANSI_DEFAULTS, ARITHABORT, NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- variable declaration
DECLARE
@transactional BIT
, @trancount INT
, @err INT
, @procname SYSNAME
, @error INT
, @message VARCHAR(4000)
, @xstate INT
, @RecordCount int;
SELECT @procname = OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()) + '.' + OBJECT_NAME(@@PROCID, DB_ID())
-- 0 = no: will not execute batches inside a transaction; a partial success of procedure is possible
-- 1 = yes: batches in procedure will be bound together by a transaction, partial success is impossible
, @transactional = 0
-- optionally begin transaction and begin try block
IF @transactional = 1 SET @trancount = @@TRANCOUNT
BEGIN TRY
IF @trancount = 0 and @transactional = 1
BEGIN TRANSACTION
ELSE IF @transactional = 1
SAVE TRANSACTION p1
----------------------------------------------------------------------------------------
---Unpivot columns to Rows into Temp tables
----------------------------------------------------------------------------------------
---ICD9CM & ICD10CM
DROP TABLE IF EXISTS #UnpvtDx;
SELECT
ClaimHeader_ID
,ClaimDetail_ID
,ClaimServiceLine
,Unpvt.CodeLine
,Unpvt.DxCode
INTO #UnpvtDx
FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT
(
DxCode FOR CodeLine IN
(
Diagnosis1CD,Diagnosis2CD,Diagnosis3CD,
Diagnosis4CD,Diagnosis5CD,Diagnosis6CD,
Diagnosis7CD,Diagnosis8CD,Diagnosis9CD,
Diagnosis10CD,Diagnosis11CD,Diagnosis12CD,
Diagnosis13CD
)
) as Unpvt ---53 secs
WHERE YEAR(ServiceFromDT) = @year;
--Select top 100 * from #UnpvtDx where DxCode is null
DROP TABLE IF EXISTS #UnpvtPointer;
SELECT
ClaimHeader_ID
,ClaimDetail_ID
,ClaimServiceLine
,Unpvt.CodeLine
,Unpvt.Pointer
INTO #UnpvtPointer
FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
UNPIVOT
(
Pointer FOR CodeLine IN
(
DiagPointer1, DiagPointer2,
DiagPointer3,DiagPointer4
)
) as Unpvt ---40 secs
WHERE YEAR(ServiceFromDT) = @year;
--Select top 100 * from #UnpvtPointer
----------------------------------------------------------------------------------------
--- INSERT INTO yearly records from the temp table
----------------------------------------------------------------------------------------
INSERT INTO Prod.Claim.ClaimDiag (
ClaimHeader_ID,
ClaimDetail_ID,
SourceID,
EDWLoadDTS,
PartnerCD,
PartnerNM,
ClaimID,
ClaimServiceLine,
ClaimStatus,
CCOMemberID,
MemberID,
PlaceOfServiceCD,
ServceFromDT,
ServiceToDT,
ClaimForm,
TypeOfBillCD,
DiagnosisCD,
DiagnosisDESC,
DiagPointer
)
SELECT DISTINCT
det.ClaimHeader_ID,
det.ClaimDetail_ID,
det.SourceID,
det.EDWLoadDTS,
det.PartnerCD,
det.PartnerNM,
det.ClaimID,
det.ClaimServiceLine,
det.ClaimStatus,
det.CCOMemberID,
det.MemberID,
det.PlaceOfServiceCD,
det.ServiceFromDT,
det.ServiceToDT,
det.ClaimForm,
det.TypeOfBillCD,
DiagnosisCD = dx.DxCode,
DiagnosisDESC = diag.DiagnosisDESC,
DiagPointer = point.Pointer
FROM Prod.Claim.ClaimDetail det WITH (NOLOCK)
INNER JOIN Prod.Claim.ClaimHeader ch WITH (NOLOCK)
ON ch.ClaimHeader_ID = det.ClaimHeader_ID
INNER JOIN #UnpvtDx dx
ON dx.ClaimDetail_ID = det.ClaimDetail_ID
AND dx.ClaimHeader_ID = det.ClaimHeader_ID
AND dx.ClaimServiceLine = det.ClaimServiceLine
LEFT JOIN #UnpvtPointer point
ON point.ClaimDetail_ID = det.ClaimDetail_ID
AND point.ClaimHeader_ID = det.ClaimHeader_ID
AND point.ClaimServiceLine = det.ClaimServiceLine
LEFT OUTER JOIN Reference.Reference.Diagnosis diag WITH (NOLOCK)
ON dx.DxCode = diag.DiagnosisCD
AND diag.ICDVersion = 'ICD10CM'
AND diag.ActiveFLG = 1
WHERE YEAR(det.ServiceFromDT) = @year
--AND Year(det.ServiceFromDT) = 2021--for testing
--and det.ClaimID ='21006E06455'--for testing
----------------------------------------------------------------------------------------
--insert into updatelog table
SET @RecordCount = @@ROWCOUNT;
DECLARE @procName1 SYSNAME
SET @procName1 = @procname + ' ' + CAST(@year AS varchar(4))
INSERT INTO Prod.dbo.UpdateLog(EventTimestamp,EventDescription,ProcName,TableName)
SELECT GETDATE(),
'Inserted ' + CAST(@RecordCount AS varchar(100)) + ' records',
@procName1,
'Claim.ClaimDiag'
----------------------------------------------------------------------------------------
DROP TABLE IF EXISTS #UnpvtDx
DROP TABLE IF EXISTS #UnpvtPointer
----------------------------------------------------------------------------------------
SPEXIT:
IF @transactional = 1 and @trancount = 0 COMMIT
END TRY
----------------------------------------------------------------------------------------
-- error handling with catch
BEGIN CATCH
SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE()
IF @transactional = 1 and @xstate = -1 ROLLBACK
IF @transactional = 1 and @xstate = 1 and @trancount = 0 ROLLBACK
IF @transactional = 1 and @xstate = 1 and @trancount > 0 ROLLBACK TRANSACTION p1
DROP TABLE IF EXISTS #claims
SET @procName1 = @procname + ' ' + CAST(@year AS varchar(4)) ---+ ', ' + CAST(@month AS varchar(4))
RAISERROR ('%s, Error %d, %s', 16, 1, @procname1, @error, @message)
RETURN @error
END CATCH
RETURN 0
END
GO
我正在运行 SQL Server 2019,我需要更好地管理我的备份和日志。
我的主数据库(mdf)只有 9GB,但是日志(ldf)是 792GB。我已经使用 SSMS > Tasks > Backup 创建了这些备份...我期待在备份数据库后日志会重置。由于这并没有违反谷歌的建议,我试图缩小日志文件,没有发生任何事情。
我尝试将日志文件的最大大小设置为较小,但是由于日志文件已经太大,因此不允许这样做。这是一个开发服务器,很快我将把它切换到生产服务器。所以目前不需要历史备份,但一旦上线就可以了。
与此同时,我不得不删除其他数据库和日志文件以释放空间。我把 mdf 和 ldf 放在不同的分区上。
编辑:我处于完全恢复模式,但我现在已将其切换为简单。我希望将来有一个时间点,但现在就可以了。我进行了备份,但是在简单恢复模式中,只有完整备份和差异备份类型可用,我现在无法备份日志。我做了两个可用的选项,希望这会减少日志的大小,但它没有。
问题:现在我有一个备份,我可以删除日志文件,然后 SQL Server 会创建一个新的。当磁盘填满时,我可以重复这个过程吗?