John Liu Blog

Every drop counts

Check SQL DB size and free disk space

Following script will list the db size for all user databases and the free disk space of the driver. SELECT DBName ,Owner ,Create_Date ,DBSizeGB = SUM(DBSizeGB) ,Drive ,DriveFreeSpaceGB = MAX(available_bytes)/1024/1024/1024 FROM (SELECT DBName = D.name ,Owner = P.name ,Create_Date = CONVERT(DATE,D.create_date) ,DBSizeGB = F.size * 8 / 1024 / 1024 ,Drive = LEFT(F.physical_name,2) ,DR.available_bytes FROM sys.databases D INNER JOIN sys.server_principals P ON D.owner_sid = P.sid INNER JOIN sys.master_files F ON D.

Posted by John Liu Thursday, October 27, 2022

Draw digram using Mermaid

Mermaid lets you create diagrams using text and code. In Visual Studio Code, after install the Mermaid Editor extension, you will be able to create diagram easily. The file name needs to have .mmd file extension. Following code will generate an example digram: flowchart TB; title[<font size=6><B><u>Integration Automation Architecture</u></B>] title --> A2 style title fill:#FFF,stroke:#FFF linkStyle 0 stroke:#FFF,stroke-width:0; subgraph SG1[Power Automate Flow<br>] A1(Outlook email received) --> B(Retrieve automation config from DS5Admin DB) A2(Scheduled API call) --> B A3(File created in folder) --> B B --> B1(Load raw contents into DS5Admin DB based on config) end subgraph SG2[Data Process via SQL Agent job<br><br>] B1 --> B2(Process raw contents against target DB<br> from within Admin DB) B2 --> C{Automation type} C -- AAL --> D1(AAL SP) C -- pXRF --> D2(pXRF SP) C -- LAS --> D3(LAS SP) C -- JSON --> D4(JSON SP) C -- General CSV --> D5(CSV SP) C -.

Posted by John Liu Wednesday, October 26, 2022

SQL Server Replication

There a 4 type of replications: Snapshot replication Transactional replication Peer-to-Peer replication Merge replication Peer-to-Peer Replication Column/FK/Constraint changes are replicated but trigger/index changes are not replicated. Changes are replicated almost instantly. Table with timestamp column is not supported, no work around. Identity column needs to be managed carefully. When setup replication, identity column will automatically have the NOT FOR REPLICATION option set to YES. Don’t set the option to NO (even though you can via script after replication setup).

Posted by John Liu Tuesday, September 6, 2022

Remove SQL Server Replication

Following script helps to cleanly remove all replication objects configured on the publisher server. ONLY run this script if you want to completely remove entire replication set. DECLARE @distributionDB SYSNAME = N'distribution' -- CHANGE THIS TO THE DISTRIBUTION DATABASE NAME. default is distribution ,@publisher SYSNAME = N'' -- CHANGE THIS TO THE PUBLISHER NAME. ,@publicationDB SYSNAME = N'' -- CHANGE THIS TO THE PUBLICATION DATABASE NAME. ,@subscriber SYSNAME = N''; -- CHANGE THIS TO THE SUBSCRIBER NAME, comma separated if more than one subscriber.

Posted by John Liu Monday, September 5, 2022

Linked Server DTC

When run query involve transactions using Linked Server, you will need to enable Network DTC Access for MS DTC transaction on both servers. If not configured, you may receive following error message: “The partner transaction manager has disabled its support for remote/network transactions” (if not enabled on remote server) “The transaction manager has disabled its support for remote/network transactions” (if not enabled on the local server) “The operation could not be performed because OLE DB provider “MSOLEDBSQL” for linked server “LinkedServer” was unable to begin a distributed transaction”

Posted by John Liu Friday, July 22, 2022