-
March towards SQL Server : Day 5 – SQL DBA Interview Questions Answers – Data File and Transaction Log File Architecture
This Blog illustrates about the most frequent questions which are asked about the Data file and Transaction Log file architecture. Intentionally I did not add any troubleshooting questionnaire in this blog because I will post a separate Questions\Answers blog for the same. 1) Explain the SQL Server Database Data File structure? SQL server divides the…
-
March towards SQL Server : Day 4 – SQL DBA Interview Questions Answers – Database Architecture and Database Properties
This article comprises of Questions answers series on Database Architecture and various Database Properties. Intentionally I skipped Database Recovery Models, Backups, Restore, Data File and Transaction Log Architecture Questions from this series because I will cover them in a complete separate series on Database Backup, restores and File Architectures. 1) How many types of files…
-
March towards SQL Server : Day 3 – SQL DBA Interview Questions Answers – SQL Server Architecture
I am writing this blog based upon the Experience and knowledge which I gained after reading lot of Articles, Blogs, and Microsoft Documentation on SQL Server Architecture. Though it takes lot of time to collect this information and summarize it in a question series but I feel very proud and happy that I am giving…
-
March towards SQL Server : Day 2 – SQL DBA Interview Questions Answers – Security Permissions
1) What is Authentication and Authorization? What is the difference between both? Authentication is the process of verifying who you are. Logging on to a PC with a username and password is authentication. Authorization is the process of verifying that you have access to something. Authorization is gaining access to a resource (e.g. directory on…
-
March towards SQL Server : Day 1 – SQL DBA Interview Questions Answers – Installation
I have been thinking since long time to pen down a comprehensive series on most commonly asked DBA Interview questions. I have planned to segregate the entire questions series, topic wise to make it easier to explore. So here comes the First blog of this series based on SQL Server installation topic. I hope it…
-
SQL DBA Cheatcode Part 3 : Few Important Queries for any DBA
In continuation of our DBA Cheatcode series :- I started this series on Queries\T-SQL commands that we use in our day to day operations or those queries which should be handy for any SQL DBA(Junior or senior) out there. In this series, I’ll try to share as many queries as I have or I…
-
10 Interesting things that ‘CAN NOT’ be done in SQL Server
I was just thinking about few interesting facts about SQL Server properties which are good to be known for a SQL Server DBA. You can not add data and log files in master and model database. You can not add User Defined File Groups in master, model and tempdb database. You can not change the recovery…
-
Common Human Errors as DBA (Part 3)
In continuation of the series of “Common Human Errors as DBA” – here I am posting Third Part of it. You may check others here. Part 1 & Part 2 Poor naming standards & Lack of documentation: This is a common mistake (not human error) that frequently happens. From the very beginning, a DBA…
-
Is tempdb configured correctly?
Let’s learn about tempdb and some of the best practices about tempdb. Before doing this, let’s talk about tempdb and its usage. A Tempdb is a system database that is available to all users who are connected on an instance of SQL Server. Tempdb holds temporary user objects like global/local temporary tables, temporary stored procedures,…
-
Common Human Errors as DBA (part 2)
You must have read my previous post (Part-1) about Common Human Errors as DBA and how to avoid them. Here is another post of the same series. Third error – Data or Log file Shrinking – sometimes, one may not be checking how much free space is available in data or log files and shrinking databases…
-
MAXDOP – What is best approach to use it!!!
The Maximum Degree of Parallelism (MAXDOP) setting will determine the degree to which SQL server will attempt to parallel process transactions which are received from the scheduler. As a general rule of thumb, OLTP environments with high transaction volume and repetitive queries should normally have a MAXDOP value of 1, as any benefit gained from…
-
Database Engine Instances (SQL Server): A Quick Refresher
SQL Server instance of the Database Engine is nothing but a copy of the sqlservr.exe executable that runs as an operating system service. This means for each running SQL Server instance you must see one sqlservr.exe in task manager. Like in screenshot. An instance can manage six system databases. An instance can manage one or more…
-
Common Human Errors as DBA (part 1)
“Human Error” is not a new word. Most of the time if there is screwed up done by any one – these golden words are used. For sure, these are the Human Errors and its hard to control. Here I am trying to consolidate some common human errors that are done by DBAs at different…
-
Database Accidents : Unwanted Restore
Number of times, while working as a DBA many accidents happen. Most common of them is when a database is restored without checking if a backup is available or not. As a thumb rule – a DBA must take backups of everything before performing any action. At any point of time, I must be in…
-
Fragmentation of Log files and VLFs
In a SQL server, having multiple log files does not have any performance benefits as the log files are written sequentially. The log files can have external (disk level) fragmentation or internal (VLF, Virtual Log File) fragmentation. The external fragmentation can be solved by de-fragmenting the disk on which the log file resides on, followed…