SQL Server Log文件对磁盘的写操作大小是多少
时间：2022-03-10 17:20
原文:SQL Server Log文件对磁盘的写操作大小是多少
SQL Server 数据库有三种文件类型，分别是数据文件、次要数据文件和日志文件，其中日志文件包含着用于恢复数据库的所有日志信息，SQL Server总是先写日志文件ldf，数据变化写入mdf则可以滞后，所以日志写入的速度在一定程序上决定了SQL Server所能承载的写事务量，那么ldf写入大小是多少呢？
要知道SQL Server写 Log的大小，这里使用工具Process Monitor
这里设置一个Filter，以满足只收集SQL Server 写日志，如下图所示：
那么你的是多少呢？可以用Process Monitor下测下。
Process Monitor下载地址
收集到的数据存成Logfile.CSV.
然后用下列语句进行分析：
?
USE tempdb
GO
CREATE TABLE [dbo].[Logfile]
(
[Time
of Day] [nvarchar](MAX) NULL
,
[Process Name] [nvarchar](MAX) NULL
,
[PID] [nvarchar](MAX) NULL
,
[Operation] [nvarchar](MAX) NULL
,
[Path] [nvarchar](MAX) NULL
,
[Result] [nvarchar](MAX) NULL
,
[Detail] [nvarchar](MAX) NULL
)
ON
[PRIMARY]
GO
BULK INSERT
[Logfile] FROM
‘D:\Tmp\Logfile.CSV‘
WITH (
FIELDTERMINATOR =‘,‘,
FIRSTROW=2
)
SELECT
[Time of Day] ,
[Process Name] ,
PID ,
Operation ,
REPLACE(PATH, ‘DBname‘, ‘myDB‘) AS
Path ,
Result ,
Detail
FROM
[Logfile]
--WHERE PATH LIKE ‘%DBname%‘
SELECT
SUBSTRING(detail, CHARINDEX(‘Length:‘, detail) + 7,
CHARINDEX(‘Flags‘, detail) - CHARINDEX(‘Length:‘, detail)
- 13) AS
size
INTO
#
FROM
[Logfile]
WHERE
PATH LIKE ‘%HighEndSeekerDB%‘
SELECT
size AS [SIZE(Bypte)] ,
COUNT(*) AS
CNT ,
LTRIM(CAST(COUNT(*) * 1.0 / ( SELECT
COUNT(*)
FROM
#
) * 100 AS
NUMERIC(18, 4))) + ‘%‘
AS
ratio
FROM
#
GROUP BY
size
--ORDER BY count(*) desc
--ORDER BY CAST(REPLACE(size, ‘,‘, ‘‘) AS BIGINT)
ORDER BY ratio DESC |
SQL Server Log文件对磁盘的写操作大小是多少,布布扣,bubuko.com
