workthread
https://msdn.microsoft.com/ko-kr/library/ms190219.aspx
https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/06/27/lazy-log-truncation-clearing-of-sql-transaction-log-vlf-status-deferred/
DBCC LOGINFO
SELECT * FROM MASTER.SYS.DATABASES
DBCC SQLPERF(LOGSPACE)
위 sql 을 받아서 주석중 a 한개만 지우면 read i/o 가 1602 에서 13 으로 줄어듭니다.
반면, while 문이 아닌 구간은 그렇게 큰 영향이 없습니다.
위 파일은 주석이 훨씬 많은데도 i/o 에 별 영향이 없습니다.
위 현상은 sql 2012 2016 등에서 재현 가능합니다.
EXEC master.dbo.xp_create_subdir 'd:\XE'
CREATE EVENT SESSION [SQLTAG_BASELINE_01_B_2012] ON SERVER
ADD EVENT sqlos.WAIT_INFO(
WHERE ([package0].[greater_than_equal_uint64]([duration],(3)))),
ADD EVENT sqlos.WAIT_INFO_EXTERNAL(
WHERE ([package0].[greater_than_equal_uint64]([duration],(3)))),
ADD EVENT sqlserver.RPC_COMPLETED(SET collect_statement=(1)
ACTION(sqlserver.query_hash,sqlserver.sql_text)
WHERE ([package0].[greater_than_equal_uint64]([duration],(3000)))),
ADD EVENT sqlserver.SP_STATEMENT_COMPLETED(SET collect_object_name=(1),collect_statement=(1)
ACTION(sqlserver.query_hash,sqlserver.sql_text)
WHERE ([package0].[greater_than_equal_int64]([duration],(3000)))),
ADD EVENT sqlserver.SQL_BATCH_COMPLETED(SET collect_batch_text=(1)
ACTION(sqlserver.query_hash,sqlserver.sql_text)
WHERE ([package0].[greater_than_equal_uint64]([duration],(3000)))),
ADD EVENT sqlserver.SQL_STATEMENT_COMPLETED(SET collect_parameterized_plan_handle=(0),collect_statement=(1)
ACTION(sqlserver.query_hash,sqlserver.sql_text)
WHERE ([package0].[greater_than_equal_int64]([duration],(3000))))
ADD TARGET package0.event_file(SET filename=N'D:\XE\SQLTAG_BASELINE_01_B_2012.XEL',max_file_size=(100),max_rollover_files=(1000))
WITH (MAX_MEMORY=10240 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
CREATE EVENT SESSION [SQLTAG_BLOCKED_REPORTS_03_R_2012] ON SERVER
ADD EVENT sqlserver.BLOCKED_PROCESS_REPORT(
ACTION(package0.callstack,sqlos.cpu_id,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'D:\XE\SQLTAG_BLOCKED_REPORTS_03_R_2012.XEL',max_file_size=(100),max_rollover_files=(10))
WITH (MAX_MEMORY=10240 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
CREATE EVENT SESSION [SQLTAG_ERROR_02_E_2012] ON SERVER
ADD EVENT sqlserver.ERROR_REPORTED(
ACTION(sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username)
WHERE ([PACKAGE0].[NOT_EQUAL_INT64]([ERROR_NUMBER],(8153)) AND [PACKAGE0].[NOT_EQUAL_INT64]([ERROR_NUMBER],(5707)) AND [ERROR_NUMBER]<>(5701) AND [ERROR_NUMBER]<>(8153)))
ADD TARGET package0.event_file(SET filename=N'D:\XE\SQLTAG_ERROR_02_E_2012.XEL',max_file_size=(100),max_rollover_files=(10))
WITH (MAX_MEMORY=10240 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [SQLTAG_BASELINE_01_B_2012]
ON SERVER
STATE = start;
ALTER EVENT SESSION [SQLTAG_BLOCKED_REPORTS_03_R_2012]
ON SERVER
STATE = start;
ALTER EVENT SESSION [SQLTAG_ERROR_02_E_2012]
ON SERVER
STATE = start;
EXEC SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC SP_CONFIGURE 'blocked process threshold (s)', 2
GO
RECONFIGURE WITH OVERRIDE
GO
https://catalog.update.microsoft.com/v7/site/Search.aspx?q=SQL%20SERVER%202012%20SP3%20CU1
Everyone has probably heard that Windows Server 2012will add around 2,300 new Windows PowerShellcommands. Today, however, PowerShell still works with just a subset of the Windows Server management functions. One area that it doesn't directly touch is the ability to configure Windows Server networking and firewall functions, for which you need to use the venerable netsh commands. Even with the advent of PowerShell, netsh has continued to evolve, and it's particularly useful for configuring Windows Server Core.
Related: Network Configuration Tasks with Netsh
In this column, I'll show you 10 handy netsh commands you can use to query and configure your Windows Firewall settings. It's worth noting that you can call these netsh commands from within your PowerShell scripts.
10. Query firewall rules: One of the first things you'll probably need to use netsh for is to discover Windows Firewall's current configuration properties. You can query Windows Firewall settings using the following netsh command:
netsh advfirewall firewall show rule name=all
9. Enable and disable Windows Firewall: It's typically a best practice to leave Windows Firewall enabled, but sometimes when you're performing testing or setting up new applications, you need to turn Windows Firewall off for a period. The following commands illustrate how to turn Windows Firewall off and then back on:
netsh advfirewall set allprofiles state on netsh advfirewall set allprofiles state off
8. Reset Windows Firewall: If you make a mistake configuring Windows Firewall, you might want to use the following netsh command to reset it back to its default settings:
netsh advfirewall reset
7. Set logging: The default path for the Windows Firewall log files is \Windows\system32\LogFiles\Firewall\pfirewall.log. The netsh command below changes the location of the log file to the C:\temp directory:
netsh advfirewall set currentprofile logging filename "C:\temp\pfirewall.log"
6. Allow and prevent ping: You can use netsh to control how and if a given system responds to ping requests. The following two netsh commands show how you can block and then open Windows Firewall to ping requests:
netsh advfirewall firewall add rule name="All ICMP V4" dir=in action=block protocol=icmpv4 netsh advfirewall firewall add rule name="All ICMP V4" dir=in action=allow protocol=icmpv4
5. Enable and delete a port: One of the most common things you need to do with Windows Firewall is open ports that are used by different programs. The following examples show how to use netsh to create a rule to open and then close port 1433, which is used by Microsoft SQL Server:
netsh advfirewall firewall add rule name="Open SQL Server Port 1433" dir=in action=allow protocol=TCP localport=1433 netsh advfirewall firewall delete rule name="Open SQL Server Port 1433" protocol=tcp localport=1433
4. Enable a program: Another common task is opening Windows Firewall for a given program. The following example illustrates how to add a rule that enables Windows Live Messenger to work through Windows Firewall:
netsh advfirewall firewall add rule name="Allow Messenger" dir=in action=allow program="C:\programfiles\messenger\msnmsgr.exe"
3. Enable remote management: Another common requirement, especially when you're setting up new systems, is to enable remote management so that tools such as the Microsoft Management Console can connect to remote systems. To open Windows Firewall for remote management, you can use the following command:
netsh advfirewall firewall set rule group="remote administration" new enable=yes
2. Enable Remote Desktop Connection: One of the first things I do with most of the server systems I set up is enable Remote Desktop Connection for easy remote systems management. The following command shows how to use netsh to open Windows Firewall for Remote Desktop Connections:
netsh advfirewall firewall set rule group="remote desktop" new enable=Yes
1. Export and import firewall settings: After you get Windows Firewall configured, it's a good idea to export your settings so that you can easily reapply them later or import them into another system. In the following netsh commands, you can see how to export and then import your Windows Firewall configuration:
netsh advfirewall export "C:\temp\WFconfiguration.wfw" netsh advfirewall import "C:\temp\WFconfiguration.wfw"
Learn more: Netsh Command Syntax for the Netsh Firewall Context
netsh advfirewall firewall set rule name="Netlogon 서비스(NP-In)" new enable=yes
netsh advfirewall firewall set rule group="remote desktop" new enable=Yes
일단 원격 데이터 수집을 위해 리버스 하면서 수집한 정보를 조금 남겨둔다.
https://msdn.microsoft.com/en-us/library/aa371915(VS.85).aspx
The CounterData table contains a row for each counter that is collected at a particular time. There will be a large number of these rows.
The CounterData table defines the following fields:
The GUID, CounterID, and RecordIndex fields make up the primary key for this table.
SecondValueA 와 B
timestamp 값이다. 결국 1초 차이는 3312642 라는 systemfrequency 이고 TimeStamp 값에 1초마다 차곡 차곡 더해진다. 이 값을 32bit Value 2개에 나누어 담아서 secondValueA 를 하위값 secondValueB 를 상위값으로 기록 (만철이가 리버스 함)
countertype 별 timebasea
=DEC2BIN(MOD(QUOTIENT(A1,256^3),256),8)&DEC2BIN(MOD(QUOTIENT(A1,256^2),256),8)&DEC2BIN(MOD(QUOTIENT(A1,256^1),256),8)&DEC2BIN(MOD(QUOTIENT(A1,256^0),256),8)
timebasea 는 SystemFrequency 이거나 10000000 인데 countertype 에 종속된다. 자세한 컬럼은 위 그림을 참고한다.
virtual Machine 은 10000000 이다.
DefaultSclae 은 모두 0으로 두고 모니터링 할 때 선택한 카운터 배율조정을 선택해 모두 잘 나타낼 수 있도록 만들면 된다.
counterdata 의 multicount 는 모두 0으로 해도 이상없이 데이터가 출력된다.
workgroup 환경에서 원격서버의 데이터를 수집하기 위해서는 가장(Impersonation)을 통해 로그인 한다.
이제 perfmon extendedevent dmv 모두 수집할 수 있다.
일정 잡고 만들면 된다. 만드는건 쉽다........아~ 돌아버릴뻔~
Lisa,
We are just starting to use Availablility Groups so should we make our transaction logs bigger than they were on say SQL2005/SQL2008R2? We also have percent log used alerts to fire off log backups. Will these be affected by the lazy log truncation? We have these set at 65%. Should we change this down or up?
Thanks
Chris
Even though VLFs are not set as cleared, they are still able to be archived and will be in subsequent tlog backups. The space will show as empty when you look at % used (via DBCC SQLPERF for example). The point to keep in mind is that any log fragments that have not yet been hardened on replicas cannot be cleared/overwritten. Therefore, if you have a very long duration of time where a replica is disconnected from the primary, the used tlog space may grow. So, lazy log truncation does not affect the percent used, but other factors may. If you are familiar with mirroring, replication, or CDC, the same scenario is in play because there are now multiple points of truncation in the log..
Brilliant blog post and the answer to the question (Question was also well composed, with thoughts in it). Appreciate it.
Great post Lisa, I ran into this scenario this morning when testing some new availability groups and could not figure out why I was still seeing active vlfs using dbcc loginfo, your post has helped me understand.
We've run into an issue where we need to shrink a tx log. We've run a full backup and a transaction log backup. DBCc SQLPERF now indicates that only 0.16% of the log is used. Sys.databases log_reuse_wait_desc = nothing. But all VLFs have a status of 2. Is Lazy Truncation likely the issue here? Any ideas how we can shrink the log?
Hello,
I know this is an old thread but I thought I'd ask similar question about VLF's. I'm a "DBA" not by choice for a medium sized company ~40 SQL instances. We typically run our DB's in Simple recovery if anything for reduced maintenance. Anyway I have a SQL 2012 database that supports a help desk app (Trackit) that is in Simple mode. I typically set log autogrowth from default % to a set Megabyte value (256Meg in this case). After a few months of use the logfile has fragmented into 605 VLF's and counting and 13Gig.
No big deal right? The weird things is the status on ALL but one of the 605 VLF's is = 2. So this log has one unused VLF and is 99% full. The data file is still at the 256Meg I set it to at the beginning. I'm guessing all relevant data is stuck in the log file. Any ideas for how to fix this situation if indeed it is a problem.
Thanks for any insight
Ron
Just realized I hadn't logged on so I wouldn't get any notifications about the post I just placed.. doh!