SQL Processes & Databases

1. Managing the SQL Process

SQL Server runs as a Windows service in the background — credentials can be set at install time and afterward. The service can be stopped, paused or restarted.

In production

Stopping or restarting drops all active connections — don't do this casually.

Checking Status

services.mscSQL Server Configuration ManagerT-SQL

Go to services.msc and look for:

Service	Description
SQL Server (MSSQLSERVER)	Main service — the database engine
SQL Server Agent (MSSQLSERVER)	Job management and alerts
SQL Server Browser	Listens for connection requests
SQL Server CEIP service	Telemetry
SQL Server VSS Writer	Volume Shadow Copy support

Gives an overview of all SQL Server services with their status (Running / Stopped) and startup mode (Automatic / Manual).

DECLARE @agent NVARCHAR(512);

SELECT @agent = COALESCE(N'SQLAgent$' + CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')),
    N'SQLServerAgent');

EXEC master.dbo.xp_servicecontrol 'QueryState', @agent;

Result: Running or Stopped

2. Managing Databases

A single DBMS holds multiple databases:

Type	Description
System databases	Necessary for the SQL engine to function
Database snapshots	A read-only copy of a database at a given point in time
User databases	Databases you've created yourself

System Databases

Database	Function
`master`	Holds all system-level information for the SQL Server instance
`msdb`	Used by SQL Server Agent to schedule alerts and jobs
`model`	Template for every new database — changes here apply to every new database
`Resource` (hidden)	Read-only database with system objects; visible via the `sys` schema
`tempdb`	Workspace for temporary objects and intermediate results — recreated on every restart

Querying an Overview of Existing Databases

Via SSMSVia T-SQL

Object Explorer → Databases → expand

SELECT name, database_id, create_date
FROM sys.databases;
GO

3. Planning a New Database

A database consists, at minimum, of:

Database name
Owner
≥1 data file — logical name + physical location (.mdf / .ndf)
≥1 log file — logical name + physical location (.ldf)
≥1 filegroup
Options: collation, recovery model, compatibility level, containment type, ...

Storage Best Practices

Use RAID 1+0 or RAID 5 for performance and fault tolerance
Use filegroups to place objects on specific disks
Put the log file on a separate physical disk (separates read and write operations)

DB.mdf      → Raid 1      (primary data file)
DB.ldf      → Raid 10     (transaction log)
Sales1.ndf  → Raid 5      (extra data files)
Sales2.ndf  → Raid 10
TempDB      → Raid 5/10

4. Creating a Database

Via SSMST-SQL — simpleT-SQL — extended

Object Explorer → right-click Databases → New Database...

Set: name, owner, data file (size, autogrowth), log file, filegroups.

USE master;
GO
CREATE DATABASE sample;
GO

Uses the default configuration set during installation.

USE master;
GO
CREATE DATABASE projects
ON (
    name       = projects_dat,
    filename   = 'd:\projects.mdf',
    size       = 10,
    maxsize    = 100,
    filegrowth = 5
)
LOG ON (
    name       = projects_log,
    filename   = 'd:\projects.ldf',
    size       = 40,
    maxsize    = 100,
    filegrowth = 10
);
GO

5. Modifying a Database

Changes the physical structure of an existing database:

Add/remove data files, log files or filegroups
Change database options
Rename

Via SSMST-SQL — add a fileT-SQL — change options

Object Explorer → right-click the database → Properties

ALTER DATABASE projects
ADD FILE (
    name       = projects_dat1,
    filename   = 'C:\projects1.ndf',
    size       = 10,
    maxsize    = 100,
    filegrowth = 5
);
GO

-- Set recovery model
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

-- Single-user mode (for maintenance)
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Set to read-only
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO

-- Back to multi-user
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

6. Recovery Models

The recovery model determines how SQL Server maintains the transaction log — and therefore what's possible during recovery after a failure.

Model	Log behavior	Point-in-time recovery	Log backup required
Simple	The log is automatically truncated after each checkpoint	No — recovery up to the last full or differential backup	No
Full	The log is fully retained until explicitly backed up	Yes — down to the second	Yes
Bulk-Logged	Bulk operations are minimally logged	Limited — not during a bulk period	Yes

Simple

The log file doesn't grow uncontrolled: SQL Server automatically reuses the space once a transaction completes. Easy to manage, but you can only recover up to the most recent full or differential backup. Everything after that is lost.

Choose Simple when:

The database isn't business-critical (test environment, development database)
Hours of data loss are acceptable
You don't want to manage log backups

Full

The entire transaction log is retained until you perform a log backup. This enables point-in-time recovery — recovery to the exact moment before a crash or error. The required trade-off: log backups must be run regularly, otherwise the log file fills up.

Choose Full when:

Data loss must be minimal (production databases, financial systems)
Point-in-time recovery is a requirement
There's a structured backup schedule (full + differential + log)

Forgetting a log backup = the log fills up

Without regular log backups, the .ldf file keeps growing until the disk fills up. Always set up a SQL Server Agent job for automatic log backups.

Bulk-Logged

A hybrid model: behaves like Full, but bulk operations (BULK INSERT, SELECT INTO, CREATE INDEX, ...) are minimally logged for better performance. The downside: during such a bulk operation, point-in-time recovery isn't possible — only recovery up to the end of the log backup that contains the bulk operation.

Choose Bulk-Logged when:

You're running large data imports or index rebuilds
You temporarily want less log overhead
You'll switch back to Full afterward

Switching temporarily

A common pattern: switch to Bulk-Logged before a large bulk load, run the operation, immediately take a log backup, and switch back to Full.

Setting the Recovery Model

Via SSMSVia T-SQL

Object Explorer → right-click the database → Properties → Options page → Recovery model

-- Query the current model
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'MyDatabase';
GO

-- Change the model
ALTER DATABASE MyDatabase SET RECOVERY FULL;
-- or: SIMPLE / BULK_LOGGED
GO

Decision Tree

Is the database production-critical?
├── No → SIMPLE
└── Yes
    ├── Large bulk operations planned?
    │   ├── Yes → Temporarily BULK_LOGGED, then back to FULL
    │   └── No  → FULL
    └── Point-in-time recovery required?
        └── Yes → FULL (with regular log backups)

7. Deleting a Database

Via SSMSVia T-SQL

Object Explorer → right-click the database → Delete

USE master;
GO
DROP DATABASE projects;
GO

Irreversible

A deleted database is irrevocably lost — including all data, tables and objects. Always make sure you have a recent backup before deleting a database.