04 Oct

SQL Server Snapshot Creation Error – “All files must be specified for database snapshot creation”

There are several reasons where you will get this error, but I’m going to talk about the one I faced and can be fixed in seconds. I was working with an IMDB dataset. The reason for my error was a database rename. I created the database initially with the name “IMDB” and later renamed it to “IMDB_001”.

Command:

CREATE DATABASE IMDB_001_snapshot ON
( NAME = IMDB_001, FILENAME ='--path--\IMDB_001_snapshot.ss' )
AS SNAPSHOT OF IMDB_001;

Error: All files must be specified for database snapshot creation. Missing the file “IMDB”. (5127) (SQLExecDirectW)’)

Fix (Using SSMS):

Right-click on the database > Properties > (left menu)Files > Rename the file logical names to current database name (in my case to IMDB_001)

30 Sep

Codenames Duet Editable Digital Mission Map

Codenames Duet keeps the basic elements of Codenames — give one-word clues to try to get someone to identify your agents among those on the table — but now you’re working together as a team to find all of your agents. (Why you don’t already know who your agents are is a question that Congressional investigators will get on your back about later!) – BoardGamesGeek

I found it easy to have a digital map when you are playing an online version (ex: https://codenames.game/). This map can keep your status (editable).

How to use: Each mission has two parameters. The first number is the total number of turns. When setting up a mission, put this many tokens in the time bank and leave the rest in the box. Moscow uses only 8, while Berlin uses all 11. Cairo uses 9, just like the standard Prague mission. The second number is the number of acceptable mistakes. Only this many tokens are bystander side up. – Rule Book

11 Feb

VS Code Won’t Open After Unplanned Restart (Failed to deserialize the V8 snapshot blob)

Error

Fatal error in , line 0
Failed to deserialize the V8 snapshot blob. This can mean that the snapshot blob file is corrupted or missing.
FailureMessage Object: 00000071D3DFF2C0
1: 00007FF60A57E91F node::Buffer::New+130911
2: 00007FF60A3F7CDA IsSandboxedProcess+1850986
3: 00007FF608E1D798 v8::Isolate::Initialize+744
4: 00007FF60A3FD1A0 uv_mutex_unlock+21184
5: 00007FF607A28793 std::__1::__vector_base >::__end_cap+102515
6: 00007FF607AE56C8 v8::internal::JSMemberBase::JSMemberBase+54872
7: 00007FF6079513A0 Ordinal0+5024
8: 00007FF60D6FDB02 uv_random+18066594
9: 00007FFB77EF4034 BaseThreadInitThunk+20
10: 00007FFB781F3691 RtlUserThreadStart+33

Solution:

I reinstalled the Visual Studio Code without uninstalling which fixed the issue. In addition, it started without any loss to previous plugins and open projects.

20 Jan

PostgreSQL – BULK INSERTING from a delimited file, Most common errors in Windows

PostgreSQL – BULK INSERTING from a delimited file, Most common errors in Windows

PostgreSQL alternative for MS SQL Server BULK INSERT is the equally simple COPY command. In this article, we are going to take a step by step look at how to use this and possible errors. So I tried below command, which is completely correct. I faced a couple of issues when fixed it worked fine.

COPY part FROM '...Desktop\TPCH_001\pg_part.tbl' WITH (DELIMITER  '|')

Errors

ERROR: could not open file "file.tbl" for reading: Permission denied.
HINT:  COPY FROM instructs the PostgreSQL server process to read a file.  You may want a client-side facility such as psql's \copy. 
SQL state: 42501

To resolve this error, you need to update the permission of the file so that PostgreSQL can read them. Get to the folder/file, right-click and get to properties. Go to the Security tab. You need to add “Everyone” to the list. More info

ERROR:  extra data after last expected column data ending with dilimiter

This mean you have more columns the CSV than expected. As example you might have 4 column in CSV file and only 3 columns in the table. If it was other way around (table having more columns than file) we can define the needed columns in the query like below.

Read More
15 Jan

PostgreSQL – How to get the total index size used by each table in a database

PostgreSQL - How to get the total index size used by each table in a database

As per the documentation, To get the total size of all indexes attached to a table, you use the function. The pg_indexes_size() function accepts the OID or table name as the argument and returns the total disk space used by all indexes attached to that table.

We will use this funcion to get the index sizes of each table in the database.

select relname as table_name,
       pg_size_pretty(pg_indexes_size(relid)) as index_size
from pg_catalog.pg_statio_user_tables;
16 Dec

Sri Lanka Holidays Calendar 2021 for office365, Google etc. in .ics format

Sri Lanka Holidays Calendar 2016 for Outlook & Google

Honouring everyone’s request I added the Sri Lankan Holiday calendar for 2021. This will allow you to add the Sri Lanka Holidays Calendar 2021 to your digital calendar (Most of the online digital calendars that we use these days don’t have inbuilt option to add the holiday calendar for Sri Lanka).

Adding to office365

  1. Add calendar
  2. From file (don’t go to the holiday calendar, Sri Lanka is not available there)
  3. Select file to upload and a calendar that holidays will be added (Create a separate new calendar is recommended. Use RED colour)
  4. Save
Read More
11 Nov

UVA 893 – Y3K Problem: Handling Python years above 9999

I was working on a problem in UVA online judge where I needed to do a simple data addition. However, the catch was year can go beyond 9999 (which is the limit in python). Below code is the python solution for this problem. I simply divided the date-delta with (1200 years, i.e. 438291 days) and added it separately after the computation. If you have unclear areas, let me know in the comments.

Read More
26 Oct

DBA bandits: Self-driving index tuning under ad-hoc, analytical workloads with safety guarantees

Abstract:

“Automating physical database design has remained a long-term interest in database research due to substantial performance gains afforded by optimised structures. Despite significant progress, a majority of today’s commercial solutions are highly manual, requiring offline invocation by database administrators (DBAs) who are expected to identify and supply representative training workloads. Unfortunately, the latest advancements like query stores provide only limited support for dynamic environments. This status quo is untenable: identifying representative static workloads is no longer realistic; and physical design tools remain susceptible to the query optimiser’s cost misestimates (stemming from unrealistic assumptions such as attribute value independence and uniformity of data distribution). We propose a self-driving approach to online index selection that eschews the DBA and query optimiser, and instead learns the benefits of viable structures through strategic exploration and direct performance observation. We view the problem as one of sequential decision making under uncertainty, specifically within the bandit learning setting. Multi-armed bandits balance exploration and exploitation to provably guarantee average performance that converges to a fixed policy that is optimal with perfect hindsight. Our comprehensive empirical results demonstrate up to 75% speed-up on shifting and ad-hoc workloads and 28% speed-up on static workloads compared against a state-of-the-art commercial tuning tool.” [1]

[1] Full Paper: https://arxiv.org/abs/2010.09208

23 Aug

CH-BenCHmark for MS SQL Server – HTAP benchmarking

There aren’t many benchmarks which allow you to test your systems against a hybrid OLTP and OLAP workloads. CH-BenCHmark fills that gap by combining TPC-C and TPC-H. You can download the source from the linked site or you can use something like OLTPBench (a collection of benchmarks). However, the TPC-H modified queries are not written for SQL server. In this article, I will add the modified CH-BenCHmark OLAP queries for SQL Server.

Read More