SQLGeeksPro

Understanding SQL Server Database Files and Filegroups

by Dev on

Hi there! Here we are going to build a complete Understanding of SQL Server Database Files and Filegroups. This is another basic and one of the important topics to help you in your SQL DBA carrier. This is part of SQL Server DBA Tutorial: Beginners to Pro series.

What are SQL Server Database Files:

Every SQL Server database is made up of at least two operating system files: a data file and a log file.

The data file stores data and database objects such as tables, indexes, stored procedures, and views.
The log file records the information required to recover all transactions in the database.

Demo: Create database with Default settings and check the files

Create a database with default settings with the following basic T-SQL script.

 CREATE Database Testdb

Now in SSMS Right Click on the Database > Properties > Click on Files

SQL Server Database Files and Filegroups

Types of SQL Server Database Files:

SQL Server Database have three main types of database files:

Primary Data File (.mdf)
Secondary Data File (.ndf)
Transaction Log File (.ldf)

Primary Data File (.mdf):

Contains startup information for the database.
Stores system tables and metadata.
It points to the other files in the database.
Every database has only one primary data file.
Microsoft recommends the file name extension for primary data files is .mdf.

Secondary Data File (.ndf)

This is optional user-defined data file.
SQL Database can have multiple secondary files.
Data can spread across multiple disks by putting each file on a different disk drive. Improve performance via parallel I/O.
Microsoft recommends file name extension for secondary data files is .ndf.

Transaction Log File (.ldf):

Stores all transactions and changes.
Essential for recovery of the database.
Each database has at least one log file.
Recommended file name extension for transaction logs is .ldf.

Logical and Physical File Name:

There are two types of File Names if you have observed in the above screen shot. The first column says Logical Name and the last column labeled as File Name with extensions .mdf and .ldf are Physical File Name.

Logical File Name:

The file name which is defined when the database is created.
The name used to refer to the physical file in all Transact-SQL statements (BACKUP, RESTORE, CREATE DATABASE, ALTER DATABASE, etc.)
The logical file complies with the rules of SQL Server identifiers.
Logical file name must be unique among all logical file names in the database.

e.g. In the CREATE Database T-SQL, Testdb is the logical file Name

 CREATE Database Testdb  --Testdb is the Logical File Name of the Database

Physical File Name:

Physical file name is the actual name of the file stored on disk.
Represents the real .mdf, .ndf, or .ldf file.
The physical file name complies with the rules of Operation System identifiers.

Example: Let’s display the logical and physical file names for the database ”Testdb” created above

 USE Testdb 
 GO
 SELECT name as logical_name, physical_name FROM sys.database_files

Result Set showing logical and physical file names:

SQL Server Files and Filegroups

What Is Filegroup:

A Filegroup is a logical container of the database files. It allows you to control how and where database objects (like tables and indexes) are physically stored on disk.

Helps in data allocation and administration
Enables performance optimization by spreading data across disks

Types of Filegroups:

Primary Filegroup
User-defined
Filestream
Memory Optimized Data

Primary Filegroup:

Primary filegroup contains the primary data file (.mdf).
It holds the system objects and tables.
It is the default filegroup.

Bear in mind that you can change the default filegroup using T-SQL so that new objects are created in the user defined filegroup. However system objects and tables remains with the Primary Filegroup.

Example: Let’s check the filegroup information for the database “Testdb” created above.

Right Click on the Database > Properties > Click on Files

SQL Server Files and Filegroups

As can be seen above, the default Filegroup for TestDB data file is showing as PRIMARY. Point often overlooked is the Filegroup for TestDB_log file is Not Applicable.

Hence, keep in mind that SQL Server does not allow you to place transaction log files in any filegroup

User-defined Filegroup:

As the name suggests, DBAs create user-defined filegroups.
Used to separate large tables, indexes, or archive data.

Filestream Filegroup:

Stores unstructured data (e.g., documents, images).

Memory Optimized Data:

Used for In-Memory OLTP tables.
To create memory-optimized tables, you must first create a memory-optimized filegroup.

Demo: The following T-SQL script shows how to create primary File on PRIMARY Filegroups and Secondary Files on User-Defined Filegroup

T-SQL Script


USE master;
GO

CREATE DATABASE FileGroupDemo
ON PRIMARY -- Primary Filegroup
(
    NAME = N'FileGroupDemo_Primary', -- Logical Filename of Primary File
    FILENAME = N'C:\SQLData\FileGroupDemo_Primary.mdf',  -- Primary Physical Filename 
    SIZE = 100MB,
    MAXSIZE = 200MB,
    FILEGROWTH = 10MB
),
FILEGROUP FG_Sales  --User-Defined Filegroup with name FG_Sales 
(
    NAME = N'FileGroupDemo_Sales_01',  --Logical Filename of Secondary File 
    FILENAME = N'C:\SQLData\FileGroupDemo_Sales_01.ndf',  --Physical filename of Secondary File 
    SIZE = 20MB,
    MAXSIZE = 200MB,
    FILEGROWTH = 10MB
),
FILEGROUP FG_Archive  --User-Defined Filegroup with name FG_Archive 
(
    NAME = N'FileGroupDemo_Archive_01', --Logical Filename of Secondary File 
    FILENAME = N'C:\SQLData\FileGroupDemo_Archive_01.ndf',  --Physical filename of Secondary File 
    SIZE = 20MB,
    MAXSIZE = 200MB,
    FILEGROWTH = 10MB
)
LOG ON
(
    NAME = N'FileGroupDemo_Log', --Logical filename of Transaction Log File 
    FILENAME = N'C:\SQLData\FileGroupDemo_Log.ldf', --Physical filename of Transaction Log File 
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);

Now let’s see the database properties:

SQL Server Files and Filegroups

Hope you got a fair idea about SQL Server File and Filegroups. We’ll discuss about Filestream and Memory Optimized Filegroup in next article. Please feel free to leave a comment.

PowerShell Script – How many Times SQL Service Restarted

by Dev on

Hi, being SQL DBA there may be situations when you need to find out How many times SQL Service restarted. E.g. Let’s say there is an issue going on with a SQL Server and services got restarted. Your boss has asked you “Is it the first time it has happened? Can you tell me quickly the SQL Service restart history.

This is just one example, whatever may be the scenario, it is always good to have a PowerShell script handy.

The script counts the no. of SQL Server error logs and scan them for the text “SQL Server is starting”.

The output will have the complete message in the error log containing the text along with date and time of occurrence in server local time zone.

Script To Find How Many Times SQL Service Restarted:

####################################################################
# Objective: To Check how many times SQL Services got restarted 
# Author: Tridib Dev
# Source:https://sqlgeekspro.com/
# Parameter: You need to provide SQL Instance Name
#################################################################### 

# Define Input Parameter for SQL Instance Name
param([parameter(Mandatory = $true)] $SQLInstanceName)

Write-host 'SQL Service restart Details for: ' $SQLInstanceName
Write-host '-----------------------------------------------------------'
Write-host " "

# Using SMO for Server Object creation
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQLInstanceName
# Loading sqlserver module
Import-Module sqlserver -DisableNameCheck

# Defining the connection string which helps in establishing connection from remote server
$connection = "Data Source=$SQLInstanceName; Initial Catalog=master; Integrated Security=True; trustservercertificate=True;"

# Get error log list
$logs = Invoke-Sqlcmd -ConnectionString $connection -Query "EXEC xp_enumerrorlogs"

# Count no. of Error logs
$logCount = $logs.Count

Write-Host "Number of SQL Server error logs: $logCount"

foreach ($i in 0..$logCount) {
     
    # Call the ReadErrorLog method
    $logEntries = $srv.ReadErrorLog($i)
    
    # Filter and display results
    $logEntries | 
        Where-Object { ($_.Text -match 'SQL Server is starting') } |
        Format-Table LogDate, ProcessInfo, Text -AutoSize
}

Once you execute the script in PowerShell ISE, it will ask for the SQL Instance name for which you want to find the SQL Service restart history:

PowerShell Script How Many Times SQL Service Restarted

Example Result Set:

The output shows there are 100 SQL Error log files and the script has scanned them all. The SQL Service has restarted 3 times.

Hope it is is helpful. Please feel free to customize the script as per your requirements. Also you can use the same script to scan any other messages/errors by replacing the text.

Please leave a comment about your experience. You may also like to check the following interesting scripts.

Powershell Script to Validate SQL Servers

Powershell Script to Create Folders

List all permissions for a user in all or selective databases

Powershell Script to Create Folders

Please spread the word:

Tagged as: PowerShell, SQL Service Restart

{ Add a Comment }

What Is SQL Server Transaction: Complete Beginner’s Guide

by Dev on

Hi There! This article is to explain What Is SQL Server Transaction and this is part of the SQL Server DBA Tutorial: Beginners To Pro category. This will help in your journey to become a better DBA.

What is SQL Server Transaction?

A SQL Server transaction is a sequence of one or more SQL statements that are executed together. The transaction ensures that either all operations succeed or none of them are applied to the database.

In other words, batch of SQL Statement(s) performing a single or multiple activities on a database. Transactions ensures data integrity by either of the below:

Committing all changes together
Rolls back changes if any operation fails.

Example 1:

What is SQL Transaction

Example 2:

What is SQL Transaction

Practical Example: Bank Money Transfer:

Suppose you are transferring money from Account A to Account B.
Both operations must succeed together or fail. I.e. Either money should get credited to Account B or the money should not debit from Account A.

Different Modes Of SQL Server Transactions:

Auto-commit transactions
Implicit transactions
Explicit transactions
Batch-scoped transactions

Auto-commit transactions:

Each individual statement is a transaction. The auto-commit transaction mode is the default SQL Server transaction mode. When an SQL statement executes successfully, the system commits the transaction and permanently saves the data modifications in the database. If any statement encounters an error during execution, the system rolls back the transaction and reverts all changes made during that transaction.

Example:

 INSERT INTO Products (ProductID, ProductName, Price)
 VALUES (1,'Laptop',1200)

 UPDATE Products SET Price = 1300 WHERE ProductID = 1

 DELETE FROM Products WHERE ProductID = 1

As you must have observed that there is no BEGIN TRAN statement mentioned in the above example and hence each statement (INSERT, UPDATE, DELETE) runs as its own transaction.

Imagine a scenario when INSERT and UPDATE completes successfully but the DELETE statement fails, only the DELETE is rollback, the changes for INSERT and UPDATE remains saved.

Implicit transactions

In SQL Server, an Implicit Transaction starts automatically when you execute certain SQL statements. The transaction does not commit automatically; you must explicitly end it using COMMIT or ROLLBACK.

Let’s understand Implicit transaction by coding example:

Create a Table:

CREATE TABLE Products ( 
    ProductID INT PRIMARY KEY, 
    ProductName VARCHAR(50),
    Price DECIMAL(10,2)
)

Enable Implicit Transaction Mode:

SET IMPLICIT_TRANSACTIONS ON;

Commit an Implicit Transaction:

INSERT INTO Products (ProductID, ProductName, Price)
VALUES (1,'Laptop',1200)
COMMIT;

Rollback an Implicit Transaction:

UPDATE Products SET Price = 1300 WHERE ProductID = 1
ROLLBACK;

Do nothing and it will keep an open transaction:

DELETE FROM Products WHERE ProductID = 1

Since you have not COMMIT or ROLLBACK this statement, it will keep this as open transaction.
Reference Screen Shot:
What is SQL Server Transaction

Explicit transactions:

In SQL Server, an Explicit Transaction is a transaction that you start manually using BEGIN TRANSACTION and end using COMMIT or ROLLBACK. This gives you full control over when the transaction starts and ends.

Explicit Transaction with COMMIT:

BEGIN TRANSACTION

INSERT INTO Products (ProductID, ProductName, Price)
VALUES (1,'Laptop',1200)

UPDATE Products SET Price = 1300 WHERE ProductID = 1

DELETE FROM Products WHERE ProductID = 1

COMMIT;

This will save all the results of INSERT, UPDATE and DELETE. If you replace COMMIT with ROLLBACK command, it will save nothing.

Batch-scoped transactions:

Applicable only to Multiple Active Result Sets (MARS), when a Transact-SQL explicit or implicit transaction starts in a MARS session, SQL Server treats it as a batch-scoped transaction. If the transaction is not committed or rolled back before the batch finishes, SQL Server automatically rolls it back.

You may also like to go through the below articles:

What is SQL Server

Understanding SQL Server Versions and Editions

The Complete Guide to SQL Server System Databases

Please spread the word:

{ Add a Comment }

Why Instant File Initialization Is Not Enabled By Default: SQL DBA Interview Q&A

by Dev on

Hi There! If you are preparing for SQL DBA Interview, you are in right place. This article describes how to answer to the question Why Instant File Initialization (IFI) is not enabled by default in SQL Server.

Why Instant File Initialization Is Not Enabled By Default

I have already explained What is IFI and few possible interview questions in What is Instant File Initialization? SQL DBA Interview Q & A . Please read and come back to this for better understanding.

Now you may think that since IFI significantly improves performance, the system should enable it by default. Let’s understand why it is not so.

One Liner Answer:

By default, SQL Server does not enable IFI mainly to prevent security issues and the risk of exposing deleted/wrong data.

Next obvious question would be “Could you please explain the risk?”

Answer with Explanation:

IFI is not enabled by default because when users delete data, the Free Space Management system in the operating system does not physically delete the data. Rather it marks the space(blocks) holding the data as reusable.

Technically marking the storage blocks as “unallocated” in a file system table or bitmap. The actual data remains until overwritten, but the space is immediately available for new files, often using techniques like linked list or bitmaps.

When IFI is not enabled, the system zeros out the page before writing any data. However, when IFI is enabled, the system skips the zero-initialization step and overwrites the deleted disk content only when it writes new data to the files.

As a result, the newly allocated pages may contain previously deleted data, and a slight risk exists that a third-party tool could access the deleted content until another process writes new data to that specific area of the data file.

Additional Information:

This section is only for your knowledge and generally you don’t have to explain in Interview. But helps in case the interviewer asks further details.

While the database file remains attached to the SQL Server instance, the file’s discretionary access control list (DACL) reduces the risk of information disclosure. The DACL allows access only to the SQL Server service account, its service SID, and the local administrator. However, when someone detaches the file, a user or service without the SE_MANAGE_VOLUME_NAME privilege may still be able to access it.

Hope this helps in better understanding and you’ll be able to explain to the Interviewer Why IFI is not enabled by default. You may also like to go through the following for your SQL DBA Interview preparation.

Junior SQL Server DBA Interview Questions and Answers

Junior SQL DBA Interview Question Answers -Part2

How To Identify SQL Server Service Account: SQL DBA Interview Q&A

Junior SQL DBA Interview Questions Answers -Part3

Junior SQL DBA Interview Question Answers: Part 4

What is Instant File Initialization? SQL DBA Interview Q & A

Please spread the word:

{ Add a Comment }

How To Identify SQL Server Service Account: SQL DBA Interview Q&A

by Dev on

Hi There! Today we’ll discuss different ways to Identify SQL Server Service Account. This is another Interview Question for beginner to Intermediate level SQL DBAs.

The SQL Server service account is the Windows account under which the SQL Server Service runs.

Ways to identify SQL Server Service Account:

SQL Server Configuration Manager
Services.msc
Using T-SQL query
Using PowerShell Script
SQL Error Log

SQL Server Configuration Manager:

This is the most commonly used method to check the Service Account. However you need to RDP to the SQL Server.

Steps are as follows:

RDP/Login to the SQL Server
Got to Windows Start Button > Click on SQL Server Configuration Manager or do a windows search for Configuration Manager.

Identify SQL Server Service Account

Click on the SQL Server Services and check the “Log On As” column as marked in the following screen shot.

SQL Server Service Account

Services.msc or Windows Services applet:

You have to login (RDP) to the SQL Server. Hence remotely not possible to use this method.

RDP/Login to the SQL Server
Go to Start > Run > Services.msc Or just search Services.msc in windows search

Identify SQL Server Service Account

Using T-SQL query:

For T-SQL queries you can connect to the SQL Instance using SSMS (SQL Server Management Studio) from a jump server/centralized management server or a client machine without needing to RDP to the server.

Go to Start > Expand Microsoft SQL Server Tools > Click on SQL Server Management Studio. The associated version no. (in this case 20 may be different depending upon the client tool version installed on the server). You can also just do a windows search by typing SSMS.

Identify SQL Server Service Account

Now connect to the SQL Instance for which you want to find the Service Account information.

Open a new query Window by pressing ctrl + N on your keyboard or by clicking New Query

Identify SQL Server Service Account

Execute the below Query

 SELECT servicename, service_account, status_desc, startup_type_desc
 FROM sys.dm_server_services 
 GO

Example Result Set:

Identify SQL Server Service Account

Note: The DMV sys.dm_server_services had been introduced way back in SQL Server 2008R2 SP1. Hence this query will not work on SQL Server versions prior to 2008R2 SP1.

Using PowerShell Script:

Go to Start > Either right click on Windows PowerShell or Windows PowerShell ISE > Run As Admin. Or you can just type PowerShell in the Windows search box

Identify SQL Server Service Account

Execute the PowerShell Script:

 Get-WmiObject -Class Win32_Service -ComputerName ServerName | 
 Where-Object {$_.Name -Like "*SQL*"} | Format-List -Property Displayname, state, startName

Note: Replace ServerName by the hostname on which SQL Service is running

Example result of PowerShell PowerShell ISE:

Identify SQL Server Service Account

SQL Error Log:

Launch SSMS and connect to the SQL Instance
Expand Management > Expand SQL Server Logs > Right Click on the error log file > View SQL Server Log

Identify SQL Server Service Account

Click on Filter > Type in “Service Account” for Message contains text > Check the checkbox for Apply Filter > Click on Ok

Identify SQL Server Service Account

Example Result set:

Identify SQL Server Service Account

You may also want to explore:

What is Instant File Initialization? SQL DBA Interview Q & A

Junior SQL Server DBA Interview Questions and Answers

SQL DBA Interview Question Answers -Part2

Junior SQL DBA Interview Questions Answers -Part3

Junior SQL DBA Interview Question Answers: Part 4

Please spread the word:

Tagged as: SQL DBA Interview, SQL Server Service Account

{ Add a Comment }

What is Instant File Initialization? SQL DBA Interview Q & A

by Dev on

Hi There! We are going to discuss What is Instant File Initialization. The idea here is to explain you what does Instant File Initialization (IFI) mean and how it works. How far you should explain to an Interviewer and what has changed in SQL 2022.

What is Instant File Initialization

What is Instant File Initialization?

It is basically a Windows Operating System (OS) feature. Whenever SQL Server creates or expands a data or log file, OS validates the disk by writing zeros to the files. This process is known as Zeroing process.

The internal steps are as follows:

Allocate the space on disk
Writes zeros to the entire allocated portion
Then make it available for use

It is a single threaded process and hence for large files the delay is quite visible. Instant File Initialization allows SQL Server to allocate space for data files without zeroing out the disk space first and hence reduces the wait time. Basically it skips the zeroing process.

When you are answering in an interview this much should suffice. Continue to read for more knowledge and to be able to answer any follow up questions.

Does IFI applies to Log File?

In the past, transaction log files could not use instant file initialization. However, starting with SQL Server 2022 (16.x) — across all editions — and in Azure SQL Database and Azure SQL Managed Instance, transaction log autogrowth can now use instant file initialization for growth events up to 64 MB.

By default, new databases are configured with a 64 MB autogrowth size, which means they automatically benefit from this improvement.

If the transaction log grows by more than 64 MB at a time, instant file initialization will not apply, and the growth will take longer as the space must be zero-initialized.

What are the database Activities benefited by IFI?

Creating a New Database
Adding data or log file to an existing database
Increasing the existing file size manually or due to auto growth option
Restoring a database from backup file

How IFI works if Transparent Data Encryption (TDE) is Enabled?

If TDE is enabled for a database, IFI stops working for datafile however it is allowed for transaction log growth, because of how the transaction log file grows, and the fact that the transaction log is written into in a serial fashion.

How IFI works in SQL Azure and Managed Instance?

In Azure SQL Database and Azure SQL Managed Instance, transaction log autogrowth events up to 64 MB can benefit from instant file initialization.
Instant file initialization is automatically used in the General Purpose and Business Critical tiers of Azure SQL Database and Azure SQL Managed Instance. It helps improve the growth of transaction log files.
Instant file initialization isn’t configurable in Azure SQL Database and Azure SQL Managed Instance.

What kind of Environments will be benefited by IFI?

High-availability systems
Large databases
Systems with frequent restores
Systems experiencing auto-growth delays

There are very few reasons not to enable it. Ideally it should be part of SQL server build process.

You may also like to check the following:

Junior SQL Server DBA Interview Questions and Answers

SQL DBA Interview Question Answers -Part2

Junior SQL DBA Interview Questions Answers -Part3

Junior SQL DBA Interview Question Answers: Part 4

Please spread the word:

Tagged as: Instant File Initialization, SQL Interview

{ Add a Comment }

Fix XTP Configuration Is Invalid Error In Always On

by Dev on

Hello, this article is going to be part2 of How To Fix: XTP Configuration Is Invalid where we have learnt about the SCOM alert, what is XTP in SQL Server and how to fix the same in stand alone SQL Instance.

Here we’ll learn about the steps to fix XTP Configuration is invalid error in Always On SQL Instance. Hence I would like you to first go through the first article.

Steps to Fix Configuration Is Invalid Error In Always On

Calculate Resource Pool Memory
Create a resource pool
Bind the database to the pool
Bring the database offline and then online
Confirm the Binding

Now you may be thinking, well these are exactly the same steps for stand alone SQL Instance. Yes, you’re right, however in case of Always On you need to perform few extra steps for the point no. 4.

I have already explained how to calculate resource pool memory in the first article Link. Here I’ll start from point no.2

2. Create a resource pool

In case of Always On you have to create the resource pool in all the replicas.
Execute the below query on each replica.

 CREATE RESOURCE POOL PoolName  --Provide the pool name maintaining Organization's naming convention
  WITH
   (
     MIN_MEMORY_PERCENT = 63,  --Adjust as needed/calculated 
     MAX_MEMORY_PERCENT = 63   --Often set to the same value as MIN_MEMORY_PERCENT for Predictability
   )
 GO
 ALTER RESOURCE GOVERNOR RECONFIGURE;

e.g. XTP Configuration is Invalid

3. Bind the database to the pool:

Execute the below query on all the Replicas

EXEC  sys.sp_xtp_bind_db_resource_pool YourDatabaseName, 'PoolName';

e.g. XTP Configuration is Invalid

4. Bring the database offline and then online:

This is a bit tricky in case of Always On setup. You need to remove the db from Availability Group before you can take the db offline.

Following are the steps:

Remove the database from Availability Group
Take the database Offline and then Online on Primary Replica
Readd the database back to Availability Group
Failover the Availability Group to each of the replicas and Failback to the original Primary Replica.

5. Confirm the Binding:

This also needs to be executed on all the Replicas

SELECT d.database_id, d.name, d.resource_pool_id   
FROM sys.databases d WHERE name='SQLGeeksPro' --Change database name
GO

Example Result Set:

XTP Configuration is Invalid in Always On

As you can observe there is an associated resource_pool_id for the example database “SQLGeeksPro”.

Hope this helps. Please feel free to leave a comment.

Please spread the word:

Tagged as: Resource Pool, XTP Configuration

{ Add a Comment }

What Does SQL DBA Actually Do?

by Dev on

Hi There! Whether you are a student, aspiring data professional, or thinking to transform to a DBA role and wondering “What Does SQL DBA Actually Do”, you are not alone. The title Database Administrator (DBA) might sound mysterious, but it’s one of the most critical roles in IT .

What Does SQL DBA Actually Do

This guide breaks down what a SQL Server DBA really does, the tools they use and additional responsibilities as they grow in their carrier.

There are basically two type of DBA jobs in the IT industry.

Application DBA/Logical DBA — Works closely with Application Developers. Designs schema, tables, indexes, and relationships according to application needs. Require Strong SQL coding skills.

Production/Infrastructure DBA — Focuses on database operations, uptime, performance, and recovery in the production environment. Demands Strong system administration and database configuration skills.

To give you one real time scenario to understand a basic difference between the two:

When a Query is running slow, Production DBA will identify the exact slow running query and provide the query(execution) plan to App DBA. App DBA will analyze the plan and fix the query.

In some organizations the responsibilities may overlap depending upon the job requirement.

Key Responsibilities of a SQL Server DBA:

Area	Application DBA	Production DBA
Database Backup & Recovery	Is not Responsible	Ensure all database servers are backed up in alignment with Recovery Point Objective (RPO) and regularly tests restores of the backup files to make sure Recovery Time Objective (RTO) can be met.
Database Design	Designs schema, tables, indexes, and relationships according to application needs.	Ensures design aligns with operational standards and performance requirements.
SQL & Performance	Tunes SQL queries, stored procedures, and application-side performance.	Tunes system-level performance—memory, I/O, CPU, and indexing strategies.
Development Support	Works with developers during development and testing phases.	Supports deployments, migrations, and production rollouts.
Maintenance	Less involved in backups and system maintenance.	Handles patching, upgrades, Index maintenance and monitoring.
Security & Access	Defines roles and privileges for applications.	Manages overall database security, auditing, and compliance.
Troubleshooting	Fixes app-specific data or query issues.	Diagnoses system-level issues, server crashes, corruption, and performance bottlenecks.

Essential Tools/Functionalities Every SQL Server DBA Should Know:

Tool	Description
SQL Server Management Studio (SSMS)	Core interface for managing SQL databases.
Azure Data Studio	Modern, cross-platform alternative with notebooks.
SQL Server Extended Events	For tracing and debugging query performance.
Dynamic Management Views (DMVs)	Provides insight into SQL Server to assist with troubleshooting and monitoring.
SQL Agent Service	Automates scheduled jobs and maintenance tasks.
PowerShell / dbatools	For automating DBA workflows.
Query Store	Simplifies the process of identifying and resolving Query performance issues by providing insights into query plan.
Monitoring Tool like Redgate SQL Toolbelt	Professional suite for monitoring, comparison, and deployments.

Now as you grow in your DBA carrier, your role will not be limited to only technical work. I have tried to put few more critical responsibilities based on my experience.

Automation: Analyze repetitive tasks and check what and how far a task can be automated. Work with automation team, provide suggestions etc.

Incident reduction: Senior DBA/leads will work on analyzing ticket dumps and work on reducing repetitive incidents on same SQL Instance.

RCA (Root Cause Analysis) : On P1/P2 incidents, Leads/SME (Subject Matter Specialist) will work with Problem Management Team to document the RCA.

SOP (Standard Operating Procedure): You’ll be responsible for preparing SOPs for junior resources or on particular process to be followed for repetitive activities. e.g. Steps to Follow from database end during OS patching, Database Decommission, SQL Instance Provision/Decommission etc.

Customer Escalations: Handling customer escalations will be one of your prime responsibilities.

Mentoring Junior DBAs: Guiding Junior DBAs and providing necessary technical/process related trainings. Making proper plan for newly onboarded team member to bring him/her up to the speed etc.

Planning Major Activities: You will need to prepare Runbooks for major activities like DR (Disaster Recovery Drill), SQL Server upgrades etc.

Hope now you have got an overall idea about what Does a SQL DBA Actually Do. Please feel free to comment in case you want me to add any further responsivities.

You can also refer the following links:

What is SQL Server

Understanding SQL Server Versions and Editions

The Complete Guide to SQL Server System Databases

Please spread the word:

{ Add a Comment }

How To Fix: XTP Configuration Is Invalid

by Dev on

Hi There! In this article we are going to discuss about the SCOM Alert “MSSQL On Windows: XTP Configuration Is Invalid”. At first let’s see the alert description:

“XTP configuration for database “DatabaseName” on SQL Server instance “SQLInstance Name”, computer “ServerName” is not set according to the best practices. Database with memory-optimized tables should be bound to a dedicated resource pool.”

This alert points to issues with the configuration of In-Memory OLTP (XTP) features in SQL Server, which can impact database performance and reliability. As per the alert description, the database has memory-optimized tables and there is no dedicated resource pool. That is why you are not going to find out any error in SQL Error log related to this.

What is XTP in SQL Server?

XTP stands for Extreme Transaction Processing, an informal name to describe the In-Memory OLTP feature that powers memory-optimized tables. It is to boost transaction throughput by minimizing disk I/O and maximizing data access speed.

When you enable and create memory-optimized tables, SQL Server requires specific settings and prerequisites to ensure the XTP engine runs smoothly.

What is the Impact on SQL Server?

SQL Engine will allocate memory to memory-optimized tables from the default pool. This leads to uncontrolled memory consumption, which can cause overall memory pressure.
Without proper resource pools, SQL Server cannot isolate memory for In-Memory OLTP.
Result: potential server performance degradation, query slowdowns, or even crashes causing downtime.

How to Fix the XTP Configuration is Invalid Error?

Calculate Resource Pool Memory
Create a resource pool
Bind the database to the pool
Bring the database offline and then online

Well it is not that simple as it sounds. You need to calculate the memory percentage to allocate to the resource pool.

First of all you need to know the amount of memory needed for the memory optimized tables in the database. You can go through the Microsoft documentation Estimate Memory Requirements for Memory-Optimized Tables for the same.

Ideally this number should be provided by the app team to production DBA to do the rest of the configuration in SQL Server. However in some cases your app team may not be able to help with the information. Then you need to proceed as per the size in pre prod environment by monitoring the growth for couple of weeks.

Calculate Resource Pool Memory:

percentNeeded = memoryNeeded / memoryCommitted * availablePercent

memoryNeeded: Amount of memory needed for memory optimized tables
memoryCommitted: SQL Server Max Memory
availablePercent: Refer the below table:

Target Committed Memory	Percent available for in-memory tables
<= 8 GB	70%
<= 16 GB	75%
<= 32 GB	80%
<= 96 GB	85%
>96 GB	90%

For example, If

memoryNeeded = 16 GB
memoryCommitted = 32 GB
then availablePercent (considering the table) = 80%

Hence by plugging real numbers

percentNeeded = 16 / (32 * .8) = 16/25.6 =.625

Converting the result to percentage .625 * 100 = 62.5

percentNeeded = 62.5% rounding of to 63%

Create a resource pool:

Execute the following script to create a resource pool:

 CREATE RESOURCE POOL PoolName  --Provide the pool name maintaining Organization's naming convention
  WITH
   (
     MIN_MEMORY_PERCENT = 63,  --Adjust as needed/calculated 
     MAX_MEMORY_PERCENT = 63   --Often set to the same value as MIN_MEMORY_PERCENT for Predictability
   )
 GO
 ALTER RESOURCE GOVERNOR RECONFIGURE;

e.g. XTP Configuration is Invalid

Just to clarify, resource pool is created at SQL Instance level and not on database.

Bind the database to the pool:

EXEC  sys.sp_xtp_bind_db_resource_pool YourDatabaseName, 'PoolName';

e.g. XTP Configuration is Invalid

Next and last step is to take the database offline and than bring it online as can be seen in the messages once you bind the database to the resource pool.

Hope this helps. For Always ON SQL Instance there are few extra steps needs to be followed which we’ll discuss in the next blog Fix XTP Configuration Is Invalid Error In Always On

You may also like to go through the below:

Query to List SQL Database Role Owner Across All Databases

Query To List SQL Database Role Owner

SQL Server FORMAT Function Quick Guide

Please spread the word:

Tagged as: XTP Configuration

{ Add a Comment }

Query to List SQL Database Role Owner Across All Databases

by Dev on

Hello, Today we I am going to provide you with a query to List SQL Database Role Owner Across All Databases. In the article Query To List SQL Database Role Owner we have seen how to List The Database Role Owner for a single Database.

This query will be nice to have in the repository in case you want a audit report for all databases on a SQL Instance.

DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(MAX)
CREATE TABLE #databaseRoleOwner
(
        [DatabaseName] sysname,
	[RoleName] varchar(100),
	[RoleOwner] varchar(100)
);

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM master.sys.databases
WHERE  state_desc='online' 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN
SELECT @statement = 'use ['+@dbname +'];'+ 'SELECT dbname=db_name(db_id()),
    name AS RoleName,
    USER_NAME(owning_principal_id) AS RoleOwner
FROM
    sys.database_principals
WHERE
    type = ''R'' and name NOT IN (''public'', ''db_owner'', ''db_accessadmin'',''db_securityadmin'', ''db_ddladmin'',''db_backupoperator'',''db_datareader'',
	''db_datawriter'',''db_denydatareader'',''db_denydatawriter'')'

INSERT INTO #databaseRoleOwner ([DatabaseName],[RoleName],[RoleOwner])
EXEC  sp_executesql @statement
 
FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

SELECT * FROM #databaseRoleOwner
GO
 -- DROP TABLE #databaseRoleOwner