Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for the ‘Common’ Category

SQL Saturday Session #563 – Dallas 9/24/2016

Posted by Simon Cho on 09/24/2016

Topic : Build ETL efficiently (10x) with Minimal Logging

Presentation File Download Link

 

2016-08-27_SQLSat_Oklahomajpg 2016-09-24_22-05-59

 

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

Service Broker may not work after Failover in AG.

Posted by Simon Cho on 09/22/2016

We need to check a couple of setting.

  1. Service broker Enable : By default disabled even restored from Primary
    1. Doesn’t allow enabled broker in AG setting. It’s required remove first.
    2. First failover only.
  2. Trustworthy On : By default off even restored from Primary.
    1. First failover only.
  3. Encryption/Decryption issue
    1. Database master key encrypted by “Service Master key”.
    2. So secondary instance can’t encrypt/decrypt master key since Service master key created at the first installation.
    3. Temporary solution : alter master key with current instance service master key
      1. Once failover, it need to execute.

 

#Service Master key

 

Script

 

— #1 : First failover only. Remove AG required. After run the script we can rejoin it.
SELECT name FROM sys.databases WHERE is_broker_enabled=1

— If you don’t see your DB. need to run below command.
ALTER DATABASE <ServiceBrokerDB> SET ENABLE_BROKER
GO

— #2 : Concorrunt connection may be interuppted. First failover only.
SELECT name FROM sys.databases WHERE is_trustworthy_on=1
— If you don’t see your DB. need to run below command.
ALTER DATABASE <ServiceBrokerDB> SET TRUSTWORTHY ON
GO
— #3 : Need to execute it every failover. More detail please see “Service Master Key” document.
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘x’ — Should be encrypted! See below
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

 

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

Service Master Key issue on AG

Posted by Simon Cho on 09/22/2016

https://blogs.msdn.microsoft.com/mattm/2012/09/19/ssis-with-alwayson/

http://help.k2.com/kb001572

https://technet.microsoft.com/en-us/library/ms182754%28v=sql.110%29.aspx

Temporary solution : Alter master key again with service master key

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘x’ — Should be encrypted! See below
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

 

Here is SQL server 2016 AG setting.

It seems like working with SQL 2014 as well.

SQL Server 2016 AlwaysOn Availability Group Enhancements: Support for Encrypted Databases

 

 

Posted in Common | Tagged: | 1 Comment »

Saturday Night SQL VC 9/10 – Build ETL efficiently (10x) with Minimal Logging

Posted by Simon Cho on 09/12/2016

Build ETL efficiently (10x) with Minimal Logging

Here is the Presentation file. Link

http://saturdaynightsql.sqlpass.org/Home.aspx?EventID=5824

I wish to have 2-3 hour presentation length.

1 hour is short to share all those information.

 

Posted in Common | 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 »

7/23/2016 SQL Saturday at Sacramento

Posted by Simon Cho on 07/23/2016

http://www.sqlsaturday.com/540/EventHome.aspx

It was the first session in the moring.

Thank you for joining.

 

Presentation File

 

SQLSaturday_Sancramento

 

 

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 »

SSIS Catalog Execution with T-SQL

Posted by Simon Cho on 06/10/2016

I create one SP to execute SSIS Catalog using T-SQL.

It sometimes helps to execute SSIS package dynamically.

Full script download link.

 

exec [dbo].[USP_EXEC_SSIS_Catalog]
@folder_name NVARCHAR(128)
, @project_name NVARCHAR(128)
, @package_name NVARCHAR(260)
, @Multi_CustomValue VARCHAR(MAX)= NULL –“<ParamName>|#|<ParamValue>|##|<ParamName>|#|<ParamValue>… Ex. “SyncBack_Period_DD|#|-14|##|BackupFolder|#|X:\Test”
, @reference_id BIGINT = NULL
, @use32bitruntime BIT = 0
, @execution_id BIGINT = NULL OUTPUT

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

I’m going to Speak at South Florida, 6/18 – Update Download link

Posted by Simon Cho on 06/10/2016

Please register it on SQL Saturday page

http://www.sqlsaturday.com/524/eventhome.aspx

  1. Build ETL efficiently (10x) with Minimal Logging : Presentation
  2. Myths and Truths about SQL Server Transaction : Presentation, Demo

@sqlsatSoFla #sqlsat524 #sqlsaturday

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

SQL Server 2016 installation

Posted by Simon Cho on 06/03/2016

SQL Server 2016 is released at 6/1.

https://www.microsoft.com/en-us/server-cloud/products/sql-server/

 

SQL Server 2014/2016 Developer edition are free after joining  Visual Studio Dev Essentials.

https://www.microsoft.com/en-us/server-cloud/products/sql-server/

 

Before install Please read release note. They ask VC runtime update before installation.

https://msdn.microsoft.com/en-us/library/dn876712.aspx


 

Install Patch Requirement (GA)

Issue and customer impact: Microsoft has identified a problem that affects the Microsoft VC++ 2013 Runtime binaries that are installed as a prerequisite by SQL Server 2016. An update is available to fix this problem. If this update to the VC runtime binaries is not installed, SQL Server 2016 may experience stability issues in certain scenarios. Before you install SQL Server 2016, check to see if the server needs the patch described in KB 3138367 – Update for Visual C++ 2013 and Visual C++ Redistributable Package.

KB 3138367 is required if the build version of msvcr120.dll is not 12.0.40649.5 or higher. To check the file build version:

  1. Open Windows Explorer.
  2. Navigate to %SystemRoot%\system32\msvcr120.dll.
  3. Right-click the file and then click Properties.
  4. Click the Details tab.
  5. Verify the file version is 12.0.40649.5 or higher.

If the build version of msvcr120.dll is not 12.0.40649.5 or higher you need to install KB 3138367.

Resolution: To install this required update, download and run the appropriate vcredist_*.exe based on your system language and architecture from KB 3138367.

If you have SQL Server 2016 installed on a computer that needs KB 3138367, do the following steps in order:

  1. Download the appropriate vcredist_*exe.
  2. Stop the SQL Server service for all instances of the database engine.
  3. Install KB 3138367.
  4. Reboot the computer.

 

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

“Myths and Truths about SQL Server Transaction” at Saturday Night SQL, 5/7 6:00 PM PST.

Posted by Simon Cho on 05/02/2016

http://saturdaynightsql.sqlpass.org/

Presentation file download link.

Script file download link.

This is all about SQL error handling and nested transaction issue.

 

 

I’ll do a presentation for “Myths and Truths about SQL Server Transaction” at Saturday Night SQL, 5/7 6:00 PM PST.

This is Virtual Chapter. So, you can join on online.

Posted in Common | Leave a Comment »

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads

Posted by Simon Cho on 04/04/2016

https://support.microsoft.com/en-us/kb/2964518

 

Must read who is using SQL Server 2012 or SQL Server 2014.

Posted in Common | Leave a Comment »

4/9 Silicon Valley to speak on SQL Saturday.

Posted by Simon Cho on 03/30/2016

Presentation file is here.
20160409_BestPracticeForETL-MinimalLogging

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

I’m going to Silicon Valley to Speak on SQL Saturday.

Topic: “Best Practice for ETL – How to use minimal logging, and when? “

Location : Microsoft Technology Center, 1065 La Avenida, Mountain View, California, 94043, United States

SiliconValley_speak

Posted in Common | Leave a Comment »

checkDB detail

Posted by Simon Cho on 01/25/2016

http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-complete-description-of-all-checkdb-stages/

 

http://www.sqlskills.com/blogs/paul/corruption-bug-that-people-are-hitting-msg-8914-pfs-free-space/

 

Thanks, Paul

Posted in Common | Leave a Comment »

understading status column of sysfiles and sysaltfiles view

Posted by Simon Cho on 01/04/2016

First of all, let’s take a look system view

sp_helptext ‘sys.sysfiles’
sp_helptext ‘master.dbo.sysaltfiles’

<Sys.sysfiles – SQL2014>

CREATE VIEW sys.sysfiles AS
SELECT
fileid = convert(smallint, fileid & 0x7fff),
groupid = convert(smallint, grpid),
size = isnull(FilePropertyById(fileid, ‘size’), size),
maxsize, growth,
status = convert(int,
case filetype when 1 then 66 else 2 end — x_eft_SQLLog, FCB_LOG_DEVICE, FCB_DSK_DEVICE
+ (status & 8) * 2 — FCB_READONLY_MEDIA
+ (status & 16) * 256 — FCB_READONLY
+ case filestate when 6 then 268435456 else 0 end — OFFLINE, FCB_OFFLINE
+ (status & 256) * 2097152 — FCB_SPARSE_FILE
+ (status & 32) * 32768), — FCB_PERCENT_GROWTH
perf = convert(int, 0),
name = lname,
filename = pname
FROM sys.sysprufiles
WHERE filetype IN (0, 1) — x_eft_SQLData, x_eft_SQLLog (bwkcmpt types)
AND filestate NOT IN (1, 2, 3) — x_efs_Dummy, x_efs_Dropped, x_efs_DroppedReusePending

 

<master.dbo.sysaltfiles – SQL2014>

CREATE VIEW sys.sysaltfiles AS
SELECT fileid = convert(smallint, f.fileid & 0x7fff),
groupid = convert(smallint, f.grpid),
f.size, f.maxsize, f.growth,
status = convert(int,
case f.filetype when 1 then 66 else 2 end — x_eft_SQLLog, FCB_LOG_DEVICE, FCB_DSK_DEVICE
+ (f.status & 8) * 2 — FCB_READONLY_MEDIA
+ (f. status & 16) * 256 — FCB_READONLY
+ case when f.filestate in (1, 2, 3, 6) then 268435456 else 0 end — OFFLINE, FCB_OFFLINE
+ (f.status & 256) * 2097152 — FCB_SPARSE_FILE
+ (f.status & 32) * 32768), — FCB_PERCENT_GROWTH
perf = convert(int, 0),
dbid = convert(smallint, f.dbid),
name = f.lname, filename = f.pname
FROM master.sys.sysbrickfiles f
WHERE f.filetype IN (0, 1) AND has_access(‘MF’, 1) = 1 — x_eft_SQLData, x_eft_SQLLog (bwkcmpt types)

 

You can find out the source code looks like below for status

status = convert(int,
case filetype when 1 then 66 else 2 end — x_eft_SQLLog, FCB_LOG_DEVICE, FCB_DSK_DEVICE
+ (status & 8) * 2 — FCB_READONLY_MEDIA
+ (status & 16) * 256 — FCB_READONLY
+ case filestate when 6 then 268435456 else 0 end — OFFLINE, FCB_OFFLINE
+ (status & 256) * 2097152 — FCB_SPARSE_FILE
+ (status & 32) * 32768), — FCB_PERCENT_GROWTH

 

This is bit operation. So, it need to convert back using same operation.

SELECT *
, status
, status & 2 AS [FCB_DSK_DEVICE]
, status & 66 AS [FCB_LOG_DEVICE]
, status & (8 * 2) AS [FCB_READONLY_MEDIA]
, status & (16 * 256) AS [FCB_READONLY]
, status & (268435456) AS [OFFLINE, FCB_OFFLINE]
, status & (256 * 2097152) AS [FCB_SPARSE_FILE]
, status & (32 * 32768) AS [FCB_PERCENT_GROWTH]
FROM master..sysaltfiles

This query equivalent with below query

/*
SELECT CONVERT(VARBINARY(8), 2)
SELECT CONVERT(VARBINARY(8), 66)
SELECT CONVERT(VARBINARY(8), (8 * 2))
SELECT CONVERT(VARBINARY(8), (16 * 256))
SELECT CONVERT(VARBINARY(8), 268435456)
SELECT CONVERT(VARBINARY(8), (256 * 2097152))
SELECT CONVERT(VARBINARY(8), (32 * 32768))
*/
SELECT *
, status
, status & 0x2 AS [FCB_DSK_DEVICE]
, status & 0x42 AS [FCB_LOG_DEVICE]
, status & 0x10 AS [FCB_READONLY_MEDIA]
, status & 0x1000 AS [FCB_READONLY]
, status & 0x10000000 AS [OFFLINE, FCB_OFFLINE]
, status & 0x20000000 AS [FCB_SPARSE_FILE]
, status & 0x100000 AS [FCB_PERCENT_GROWTH]
FROM master..sysaltfiles

BOL saying “0x40 = Log file.” since this is bit operation.

I realized it. It’s due to “FCB_DSK_DEVICE” included. So, 0x40+2 = 0x42.

So, 0x40 is for FCB_LOG_DEVICE

https://msdn.microsoft.com/en-us/library/ms178009.aspx

 

Final query

SELECT *
, status
, CONVERT(BIT, status & 0x2) AS [FCB_DSK_DEVICE]
, CONVERT(BIT, status & 0x40) AS [FCB_LOG_DEVICE]
, CONVERT(BIT, status & 0x10) AS [FCB_READONLY_MEDIA]
, CONVERT(BIT, status & 0x1000) AS [FCB_READONLY]
, CONVERT(BIT, status & 0x10000000) AS [OFFLINE, FCB_OFFLINE]
, CONVERT(BIT, status & 0x20000000) AS [FCB_SPARSE_FILE]
, CONVERT(BIT, status & 0x100000) AS [FCB_PERCENT_GROWTH]
FROM master..sysaltfiles

 

 

 

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

Between 4096 and 512 sector size issue.

Posted by Simon Cho on 11/16/2015

There are 2 issues in case of primary server has 512 sector size, and secondary server has 4096 sector size

  • You may see below message in the SQL server error log

There have been 170613760 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file

  • If you are using Log Shipping with standby mode on secondary server, standby mode may fail frequently.

Msg 9004, Level 16, State 6, Line 7
An error occurred while processing the log for database ‘MessageExchange_Sanpedro’. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

 

Please check 4k and 512 basic concept in below url.

http://blogs.msdn.com/b/saponsqlserver/archive/2014/10/02/message-misaligned-log-ios-which-required-falling-back-to-synchronous-io-in-sql-server-error-log.aspx

http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx

http://blogs.msdn.com/b/psssql/archive/2013/05/15/sql-server-storage-spaces-vhdx-and-4k-sector-size.aspx

 

Solution

https://support.microsoft.com/en-us/kb/3009974 : I don’t know how it work internally. It might force the primary to 4K alignment. Please fully test it out and apply it.

Note After you apply this hotfix, you have to turn on the trace flag 1800 to make this hotfix work correctly.

https://support.microsoft.com/en-us/kb/2987585 : This one is great. Need to turn on trace flag 3057

 

Thanks Bob ward and Robert Dorr for investigation.

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

Speak at Huntington Beach 4/11/2015

Posted by Simon Cho on 08/20/2015

download link

SQLSaturday_HuntingtonBeach_20150411

Posted in Common | Leave a Comment »

SSIS package unexpected Termination

Posted by Simon Cho on 07/08/2015

SQL server 2012 or 2014.

1. Please check service pack first.

SQL server 2012
https://support.microsoft.com/en-us/kb/2837964

SQL server 2014
https://support.microsoft.com/en-us/kb/2993859

 

2. If you have still problem, please check that memory pressure. You may can find warning on event log.

<Unexpected termination>

Unexpected_Termination

Unexpected_Termination

<EventLog>

Unexpected_termination_EventLog

Unexpected_termination_EventLog

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

AlwaysOn AG group status check

Posted by Simon Cho on 06/15/2015

SELECT replica_server_name
, d.role_desc
, d.connected_state_desc
–, endpoint_url
–, availability_mode_desc
–, failover_mode_desc
–, session_timeout
–, backup_priority
–, secondary_role_allow_connections_desc AS Secondary_Readable
, Pri_Check.pri_Status
–, Pri_Check.Sec_Status
–, Pri_Check.Sync_Status
, d.operational_state_desc
, d.recovery_health_desc
, d.synchronization_health_desc AS Sync_Status
, d.last_connect_error_description AS ErrorMsg
, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), d.last_connect_error_timestamp) AS ErrorDateTime
FROM sys.availability_replicas r
OUTER APPLY (
SELECT 1 AS IsPrimary
, c.ip_address AS listnerIP
, c.state_desc AS listnerStatus
, a.primary_recovery_health_desc AS pri_Status
, a.secondary_recovery_health_desc AS Sec_Status
, a.synchronization_health_desc AS Sync_Status
FROM sys.dm_hadr_availability_group_states a
JOIN sys.availability_group_listeners B
ON A.group_id = B.group_id
JOIN sys.availability_group_listener_ip_addresses c
ON b.listener_id = c.listener_id
WHERE primary_replica = r.replica_server_name
) Pri_Check
JOIN sys.dm_hadr_availability_replica_states d
ON r.replica_id = d.replica_id
/*
WHERE d.role NOT IN (1,2) — 1:Primary, 2:Secondary, 0:Resolving
OR d.operational_state <> 2 –0 = Pending failover, 1 = Pending, 2 = Online, 3 = Offline, 4 = Failed, 5 = Failed, no quorum
OR d.recovery_health <> 1 –0:In progress. At least one joined database has a database state other than ONLINE ( database_state is not 0).1- Online. All the joined databases have a database state of ONLINE ( database_state is 0).
OR d.synchronization_health <>2 — 0 = Not healthy. At least one joined database is in the NOT SYNCHRONIZING state.
— 1 = Partially healthy. Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing.
— 2= Healthy. All replicas are in the target synchronization state: synchronous-commit replicas are synchronized, and asynchronous-commit replicas are synchronizing.
OR d.connected_state <> 1 –0 Disconnected. The response of an availability replica to the DISCONNECTED state depends on its role, as follows:
— On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as NOT SYNCHRONIZED on the primary replica, which waits for the secondary to reconnect.
— On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.
— 1 Connected
OR d.last_connect_error_number IS NOT NULL
*/

Posted in Common | Leave a Comment »