Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Posts Tagged ‘Minimal Logging’

SQLAngeles presentation – 12/6/2016

Posted by Simon Cho on 12/08/2016

안녕하세요. Simon Cho입니다.

발표자료 Download : link

Demo script Download : link

SQL Angeles 커뮤니티는 SQL PASS의 공식 회원이며, LA Chapter 그룹으로 PASS 커뮤니티중 유일하게 한국어로 진행되는 모임입니다.

 

2016-12-08_17-32-58

SQL Angeles PASS 공식 홈페이지 : http://SQLAngeles.com / http://sqlangeles.sqlpass.org/

 

SQL Angeles 커뮤니티는 정기적으로 화요일 8PM ~ 10PM (2시간) 스터디를 진행하며(장소 및 시간은 공식 홈페이지를 통해 공지 합니다.) SQL Server를 함께 공부하고 다양한 IT 트렌드를 공유하는 기술 및 네트워크를 공유하는 모임 입니다. SQL Angeles 스터디에 참여하고 싶은 분들은 카카오톡(ID : SQLMVP), 페이스북(https://www.facebook.com/sqlmvp) 메신저, email(jevida@naver.com) 등으로 연락 주시기 바랍니다. 스터디 장소의 출입이 자유롭지 못한 관계로 반드시 사전에 협의가 되어야 합니다.

 

스터디는 회원제로 운영되며 월회비($20)가 있습니다. 불성실 회원의 경우 회칙에 따라 참여 또는 기타 활동이 제한될 수 있습니다.

 

오늘의 주제는 [Build ETL efficiently (10x) with Minimal Logging]으로 제가(Simon Cho)  발표 하였습니다. 오늘 스터디는 총 7분이 참여주셨습니다.

 

kakaotalk_20161208_134934740 kakaotalk_20161208_134935919 kakaotalk_20161208_134936882

Posted in Common | Tagged: , | Leave a Comment »

SQL Saturday – oklahoma city

Posted by Simon Cho on 08/27/2016

I spoke at Oklahoma City 8/27/2016. This is first visit at Oklahoma City.

It was an awesome experience.

You can download my presentation at here.

http://www.sqlsaturday.com/553/Sessions/Schedule.aspx

2016-08-27_SQLSat_Oklahomajpg

Posted in Common | Tagged: , , | Leave a Comment »

Bulk recovery model – Concerning about Point-in-time recovery.

Posted by Simon Cho on 08/01/2016

https://technet.microsoft.com/en-us/library/ms190692.aspx

Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage.

However, the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations, because bulk logging operations prevents recapturing changes on a transaction-by-transaction basis. If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.

 

Solution : We can run Log backup more frequently during a minimal logging operation.

https://technet.microsoft.com/en-us/library/ms190203(v=sql.105).aspx

For a database that uses full recovery, switching to the bulk-logged recovery model temporarily for bulk operations improves performance. However, point-in-time recovery is not possible with bulk-logged model. Therefore, if you run transactions under the bulk-logged recovery model that might require a transaction log restore, these transactions could be exposed to data loss. To maximize data recoverability in a disaster-recovery scenario, we recommend that you switch to the bulk-logged recovery model only under the following conditions:

  • Users are currently not allowed in the database.
  • All modifications made during bulk processing are recoverable without depending on taking a log backup; for example, by re-running the bulk processes.

If you satisfy these two conditions, you will not be exposed to any data loss while restoring a transaction log that was backed up under the bulk-logged recovery model.

We recommend that:

  • Before switching to the bulk-logged recovery model, you back up the log.This is important because, under the bulk-logged recovery model, if the database fails, backing up the log for bulk operations requires access to the data.
  • After performing the bulk operations, you immediately switch back to full recovery mode.
  • After switching back from the bulk-logged recovery model to the full recovery model, you back up the log again.

Posted in Common | Tagged: , , | Leave a Comment »

SQL.LA presentation file 7/21.

Posted by Simon Cho on 07/22/2016

Thank you all.

Here are the presentation file and script.

 

http://sql.la/

2016-07-22_12-14-12

Posted in Common | Tagged: | Leave a Comment »