SQL Server 2012 Internal Note_Databases and database file

SQL Server 2012 Internal Note_Databases and database file

A Microsoft SQL Server database is a collection of objects that hold and manipulate data.

  • It’s a collection of many objects (tables, views, stored procedures, functions, and constraints)
  • It’s owned by a single SQL Server login account
  • It maintains a set of user accounts, roles, schemas, and security
  • It has a set of system tables
  • It’s the primary unit of recovery and maintains logical consistency among objects within it
  • It has and manages its transaction logs
  • It can span multiple disk drives and operating system files
  • It can range in size from 2 MB to a technical limit of 1524,272 TB.
  • It can grow and shrink automatically or manually
  • It can have specific properties enabled or disabled

Understanding database files

A database file is an operating system file

A database spans at least two (one for data one for the transaction log) or more database files, and the files are specified when a database is created or altered.

Three types of database files:

  1. Primary data files

    1. One for each database

    2. Keeps track of all the rest of the files and stores data

    3. .mdf

    4. Has pointers into a table in the master database that contains information about all the files belonging to the database

      1. Use SELECT * FROM sys.database_files to see the table

        Files belong to Archive database
  2. Secondary data files
    1. Zero or more for each data files
    2. .ndf
  3. Log files
    1. At least one for each database
    2. Contains the information necessary to recover all transactions in a database
    3. .ldf

Five properties of each database file:

  1. a logical filename
  2. a physical filename
  3. an initial size
  4. a maximum size
  5. a growth increment

Information on database files can be seen in sys.database_files view.

1
SELECT * FROM sys.database_files

Creating a database

Use Object Explorer in Management Studio or CREATE DATABASE.

Create Database

CREATE DATABASE newdb

  1. Create a newdb database with default values, with default size, on two files with logical names newdb and newdb_log and corresponding physical files, newdb.mdf and newdb_log.ldf are created in the default data directory
  2. Permission (who can create a database):
    1. Anyone in the sysadmin role
    2. Anyone has CONTROL or ALTER permission on the server
    3. Anyone has CREATE DATABASE permission
  3. This command is case-insensitive
  4. Default size
    1. Data file: 3 MB (size of the primary data file of the model database)
    2. Log file: 0.75 MB
  5. Database owner
    1. The login account that created the database
    2. A database can have only one actual owner
    3. When the owner uses the database, the user name would be dbo

The model database

  1. SQL Server copies the model database when creating a new database
  2. Objects and database options are copied in all subsequently created databases
  3. Use SELECT * FROM sys.objects to see objects in the model database

Using CREATE DATABASE: an example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE DATABASE Archive
ON
PRIMARY
( NAME = Arch1,
FILENAME =
    'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200MB,
FILEGROWTH = 20MB),
( NAME = Arch2,
FILENAME =
    'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\archdat2.ndf',
SIZE = 10GB,
MAXSIZE = 50GB,
FILEGROWTH = 250MB) # file expansion size
LOG ON
( NAME = Archlog1,
FILENAME =
    'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\archlog1.ldf',
SIZE = 2GB,
MAXSIZE = 10GB,
FILEGROWTH = 100MB);

If you find that by executing the query there is error message:

Could not obtain exclusive lock on database 'model'. Retry the operation later.

Use this to see which session is occupying the model database:

1
2
3
4
5
6
select request_session_id
from sys.dm_tran_locks
where resource_type = 'database' and
      resource_database_id = 3 and
      request_type = 'LOCK' and
      request_status = 'GRANT';

Then just KILL the session.

Expanding or shrinking a database

Automatic file expansion

FILEGROWTH property determines how the file expands with suffix TB, GB, MB, KB, or %.

MAXSIZE property sets an upper limit on the file size

Enabling autogrow might prevent some failures due to unexpected increases in data volume.

Shortcomings:

  1. Time-consuming if fast file initialization is not activated
  2. Result in physical fragmentation
1
2
# Returns a list of the amount of free disk space on each of your local volumes
exec xp_fixeddrives

Manual file expansion

Use ALTER DATABASE command with MODIFY FILE option to change the SIZE property of the files.

Note that the new file size must be larger than the current size.

Fast file initialization

Instant file initialization adds space to the data file (not log files) without filling the newly added space with zeros (If want files are zeroed out, use tracefalge 1806 or deny SE_MANAGE_VOLUME_NAME rights of your account), the disk is overwritten when the new data is written to the files.

Automatic shrinkage

Never recommended (resource-intensive)

Database property autoshrink controls the option, and has the same effect as running DBCC SHRINKDATABASE (dbname, 25).

Manual shrinkage

DBCC SHRINKFILE

1
2
3
# Attemps to shrink the file to target size in the current database
DBCC SHRINKFILE ( {file_name | file_id }
[, target_size][, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY} ] )

DBCC SHRINKDATABASE

1
2
3
4
# Shrinks all files in a database (larger than its minimum size)
# target_percent is a percentage of free space to leave in each file of the database
DBCC SHRINKDATABASE (database_name [, target_percent]
[, {NOTRUNCATE | TRUNCATEONLY} ] )

Using database filegroups

Filegroups are data files in a database grouped for allocation and administration purposes.

Primary filegroup

  1. The filegroup containing the primary data file
  2. Only one primary filegroup exists
  3. By default, the primary filegroup is the default filegroup

Create filegroups

Use FILEGROUP keyword in the CREATE DATABASE or ALTER DATABASE statement.

The default filegroup

One filegroup always has the property of DEFAULT.

One default filegroup for each database.

Change the default filegroup by using ALTER DATABASE

When an object (table or index) is created, it’s created in the default filegroup if no specific filegroup is specified.

Proportional fill

  1. Data is stored in a proportional fill manner in the file group
  2. Creating all your files to be the same size is recommended to avoid the issues of proportional fill

Why use multiple files

  1. Partial restore
  2. The flexibility of easily moving the database onto separate drives

Partial restore using filegroups

  1. Partial restore and backup (objects created on the restored filegroups are available)
  2. The database goes online as soon as the primary filegroup is restored
  3. For large databases and a failure of a subset of the disks

A FILEGROUP CREATION example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
CREATE DATABASE Sales 
ON PRIMARY 
( NAME = salesPrimary1, 
FILENAME = 
 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\salesPrimary1.
mdf', 
SIZE = 1000, MAXSIZE = 5000, 
FILEGROWTH = 1000 ), 
( NAME = salesPrimary2, 
FILENAME = 
 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\salesPrimary2.
ndf', 
SIZE = 1000, MAXSIZE = 5000, 
FILEGROWTH = 1000 ), 
FILEGROUP SalesGroup1 
( NAME = salesGrp1Fi1e1, 
FILENAME = 
 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\salesGrp1Fi1e1.
ndf', 
SIZE = 500, MAXSIZE = 3000, 
FILEGROWTH = 500 ), 
( NAME = salesGrp1Fi1e2, 
FILENAME = 
 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\salesGrp1Fi1e2.
ndf', 
SIZE = 500, MAXSIZE = 3000, 
FILEGROWTH = 500 ), 
FILEGROUP SalesGroup2 
( NAME = salesGrp2Fi1e1, 
FILENAME = 
 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\salesGrp2Fi1e1.
ndf', 
SIZE = 1000, MAXSIZE = 50000, 
FILEGROWTH = 5000 ), 
( NAME = salesGrp2Fi1e2, 
FILENAME = 
 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\salesGrp2Fi1e2.
ndf', 
SIZE = 1000, MAXSIZE = 50000, 
FILEGROWTH = 500 ) 
LOG ON 
( NAME = 'Sales_log', 
FILENAME = 
 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\saleslog.ldf', 
SIZE = 500MB, MAXSIZE = 2500MB, 
FILEGROWTH = 500MB );

Filestream filegroups

Filestream filegroups

  1. Contain one file reference, and the file is specified as an operating system folder
  2. The path up to the last folder must exist, and the last folder must not exist
  3. No space is preallocated to the filegroup
    1. The file and filegroup grow as data is added to tables that are created with filestream columns
  4. Note that you can’t move a file from one filegroup to another.

Create filestream filegroups

Before creating it, we need to enable the feature.

Enable filestream 
1
2
3
4
5
6
7
8
9
10
11
12
CREATE DATABASE MyMovieReviews # or ALTER DATABASE for existing databases
ON
PRIMARY 
 ( NAME = Reviews_data,
 FILENAME = 'c:\data\Reviews_data.mdf'),
FILEGROUP MovieReviewsFSGroup1 CONTAINS FILESTREAM
 ( NAME = Reviews_FS,
 FILENAME = 'c:\data\Reviews_FS') # the path C:\Data must exist, but 
								  # the Reviews_FS subfolder can’t exist
LOG ON ( NAME = Reviews_log,
 FILENAME = 'c:\data\Reviews_log.ldf');
GO
File structure
File structure

filestream.hdr file is a FILESTREAM container header file, which shouldn’t be modified or removed.

When filestream with a filestream filegroup is enabled, we can manage file data using Windows applications.

Altering a database

The ALTER DATABASE statement can only make changes each time it is executed.

ALTER DATABASE examples

1
2
3
4
5
6
7
# Increases the size of a database file
USE master 
GO 
ALTER DATABASE Test1
MODIFY FILE 
( NAME = 'test1dat3', 
SIZE = 2000MB);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# create a new filegroup in a database
# add two 500 MB files to the filegroup
# make the new filegroup the default
ALTER DATABASE Test1 
ADD FILEGROUP Test1FG1; 
GO 
ALTER DATABASE Test1 
ADD FILE 
( NAME = 'test1dat4', 
FILENAME = 
 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf', 
SIZE = 500MB, 
MAXSIZE = 1000MB, 
FILEGROWTH = 50MB), 
( NAME = 'test1dat5', 
FILENAME = 
 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\t1dat5.ndf', 
SIZE = 500MB, 
MAXSIZE = 1000MB, 
FILEGROWTH = 50MB) 
TO FILEGROUP Test1FG1; 
GO 
ALTER DATABASE Test1 
MODIFY FILEGROUP Test1FG1 DEFAULT; 
GO

Databases under the hood

A database consists of user-defined space (allocated in one or more operating system files) for the permanent storage of user objects such as tables and indexes.

Databases are divided into logical pages (of 8 KB each), numbered contiguously from 0 to N.

Enlarge/Shrink a file

  1. new space is added to the end of the file
  2. pages are removed starting at the end of the pages

New a database

  1. A unique database ID created

  2. View sys.databases shows basic information about each database

    sys.databases results

Space allocation

Extents

  1. eight logically contiguous pages (or 64 KB of space)
  2. Types
    1. Uniform extents
      1. All eight pages owned by a single object
    2. Mixed extents
      1. shared by up to eight objects
  3. Allocation
    1. For a new table or index, allocates mixed extents
    2. When the table or index grows to eight pages, all future allocations use uniform extents

GAM pages

  1. record which extents have been allocated for any type of use.
  2. On page 2, another GAM appears every 511,230 pages after the first GAM on page 2
  3. Each GAM covers about 64,000 extents, or almost 4 GB of data

SGAM pages

  1. record which extents are now used as mixed extents and have at least one unused page
  2. On page 3, another SGAM appears every 511,230 pages after the first SGAM on page 3
  3. each SGAM covers about 64,000 extents, or almost 4 GB of data
Bit settings in GAM and SGAM pages

Setting database options

By default, all the options that require ON or OFF have an initial value of OFF unless the option was set to ON in the model database.

All databases created after an option is changed in the model have the same values as the model.

sys.databases lists the current values of all the options.

State options

State options control who can use the database and for what operations:

  1. User access state determines which users can use the database.
  2. Status state determines whether the database is available to anybody for use
  3. Updateability state determines what operations can be performed on the database

SINGLE_USER | RESTRICTED_USER | MULTI_USER

1
ALTER DATABASE AdventureWorks SET SINGLE_USER;

  1. Describe the user access property of a database

  2. Mutually exclusive

  3. SINGLE_USER

    1. Allow only one connection at a time

  4. RESTRICTED_USER

    1. Allow qualified connections
    2. Qualified: dbcreator, sysadmin server role, db_owner role

  5. MULTI_USER (default)

    1. Allow connections with a valid user name

  6. Check current option

    1
    2
    SELECT USER_ACCESS_DESC FROM sys.databases
    WHERE name = '<name of database>';
    Current access option

OFFLINE | ONLINE | EMERGENCY

1
ALTER DATABASE AdventureWorks SET OFFLINE;

  1. Describe the status of a database

  2. Mutually exclusive

  3. ONLINE (default)

  4. OFFLINE

    1. it’s closed and shut down cleanly and marked as offline
    2. cannot be modified

  5. EMERGENCY

    1. allows you to access the data while you copy it to a new location.

  6. Check current status

    1
    2
    SELECT state_desc from sys.databases 
    WHERE name = 'AdventureWorks';

    Can have another status:

    1. RESTORING
    2. RECOVERING
    3. RECOVERY_PENDING

READ_ONLY | READ_WRITE

1
ALTER DATABASE AdventureWorks SET READ_ONLY;

  1. describe a database’s ability to be updated

  2. mutually exclusive

  3. READ_WRITE (default)

    1. any user with the appropriate permissions can carry out data modification operations

  4. READ_ONLY

    1. no INSERT, UPDATE, or DELETE operations can be executed
    2. automatic recovery will not run when the SQL server is restarted
    3. no locks are needed for SELECT
    4. Cannot shrink databases
    5. Cannot set to READ_ONLY if any connections are made to the database

  5. Check

    1
    2
    SELECT name, is_read_only FROM sys.databases
    WHERE name = 'AdventureWorks';
    Updatability status

    1 for READ_ONLY, 0 for READ_WRITE

Termination options

Termination options determine the behavior of the SQL Server when several state options cannot be set.

1
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH NO_WAIT;

Wait is the default option

ROLLBACK AFTER integer [SECONDS]

  1. wait for the specified number of seconds and then break unqualified connections.
  2. Incomplete transactions are rolled back.

ROLLBACK IMMEDIATE

  1. breaks unqualified connections immediately
  2. All incomplete transactions are rolled back

NO_WAIT

  1. check for connections before attempting to change the database state and causes the ALTER DATABASE statement to fail if certain connections exist (generates an error)

Cursor options

The cursor options control the behavior of server-side cursors defined using one of the following T-SQL commands for defining and manipulating cursors: DECLARE, OPEN, FETCH, CLOSE, and DEALLOCAT

CURSOR_CLOSE_ON_COMMIT {ON | OFF}

  1. ON
    1. any open cursors are closed when a transaction is committed or rolled back
  2. OFF (default)
    1. cursors remain open after a transaction is committed

CURSOR_DEFAULT {LOCAL | GLOBAL}

  1. LOCAL and cursors aren’t specified as GLOBAL when they are created
  2. The cursor name is valid only within this batch
  3. GLOBAL and cursors aren’t specified as LOCAL when they are created
    1. The cursor name can be referenced in any stored procedure or batch executed by the connection.

Auto options

Affect actions that SQL Server might take automatically

AUTO_CLOSE

  1. On
    1. the database is closed and shut down cleanly when the last user of the database exits

AUTO_SHRINK

  1. On
    1. all of a database’s files are candidates for periodic shrinking
    2. never recommended

AUTO_CREATE_STATISTICS

  1. On (default)
    1. the SQL Server Query Optimizer creates statistics on columns referenced in a query’s WHERE, ON, GROUP BY, or DISTINCT clauses.

AUTO_UPDATE_STATISTICS

  1. On (default)
    1. existing statistics are updated if the data in the tables has changed

SQL options

The SQL options control how various SQL statements are interpreted.

Default is OFF for all.

ANSI_NULL_DEFAULT

  1. ON
    1. if you don’t specifically indicate whether a column in a table allows NULL values, NULLs are allowed
  2. OFF
    1. newly created columns don’t allow NULLs if no nullability constraint is specified

ANSI_NULLS

  1. ON
    1. any comparisons with a NULL value result in UNKNOWN
  2. OFF
    1. comparisons of non-Unicode values to NULL result in a value of TRUE if both values being compared are NULL

ANSI_PADDING

  1. ON
    1. strings being compared with each other are set to the same length before the comparison takes place
  2. OFF
    1. No padding

ANSI_WARNINGS

  1. ON
    1. errors or warnings are issued when conditions such as division-by-zero or arithmetic overflow occur

ARITHABORT

  1. ON
    1. a query is terminated when an arithmetic overflow or division-by-zero error is encountered during the execution of a query
  2. OFF
    1. the query returns NULL as the result of the operation

CONCAT_NULL_YIELDS_NULL

  1. ON
    1. concatenating two strings results in a NULL string if either string is NULL
  2. OFF
    1. a NULL string is treated as an empty (zero-length) string for concatenation

NUMERIC_ROUNDABORT

  1. ON
    1. an error is generated if an expression results in a loss of precision
  2. OFF
    1. the result is simply rounded

QUOTED_IDENTIFIER

  1. ON
    1. identifiers such as table and column names can be delimited by double quotation marks, and literals must then be delimited by single quotation marks
  2. OFF
    1. identifiers can’t be in quotation marks and must follow all T-SQL rules for identifiers

RECURSIVE_TRIGGERS

  1. ON
    1. triggers can fire recursively, either directly or indirectly
  2. OFF
    1. triggers can’t be fired recursively.

Database recovery options

RECOVERY (FULL, BULK_LOGGED, or SIMPLE)

  1. determines how much recovery can be done on a SQL Server database
  2. controls how much information is logged and how much of the log is available for backups

PAGE_VERIFY

Discover damaged database pages caused by disk I/O path errors, which can cause database corruption problems.

  1. CHECKSUM
    1. SQL Server calculates a checksum over the contents of each page and stores the value in the page header when a page is written to the disk
    2. When reading the page, a checksum is calculated and compared with the stored one, if not match, error message 824
    3. run DBCC CHECKDB to determine the type of database page or pages affected
  2. TORN_PAGE_DETECTION
    1. allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages
  3. NONE

Understanding database security

Authentication

SQL Server validates and establishes the identity of an individual who wants to access a resource

Authorization

SQL Server decides whether a given identity is allowed to access a resource

Securable

  1. This is an entity on which permissions can be granted.
  2. Securable include databases, schemas, and objects.

Principal

  1. This entity can access securable
  2. A primary principal represents a single user
  3. A secondary principal represents multiple users

Database access

Authentication logins

  1. Windows authentication

    1. SQL Server login security is integrated directly with Windows security to authenticate SQL Server users
    2. The SID is the same as the OS

  2. SQL Server authentication

    1. An administrator creates SQL Server login accounts within SQL Server

  3. Login names

    1. Check login names in sys.server_principals view

      sys.server_principals view

Security modes

  1. Windows authentication mode

    1. Use only Windows authentication
    2. The default SQL Server sa login is disabled

  2. Mixed mode

    1. Use both Windows authentication and SQL Server authentication
    2. The sa login can be enabled using the ALTER LOGIN command

  3. Change mode

    Choosing an authentication mode for your SQL Server instance in the Server Properties sheet

Database security

Databases are owned by login names.

Most objects within a database are owned by database principals.

The SID used by a principal determines which databases that principal has access to.

sys.database_principals is a mapping table that maps login names to users in that particular database

Database user types

  1. a user that maps to a SQL Server login
  2. a user that maps to a Windows login
  3. a user who doesn’t map to any login
    1. have a user to be the owner of objects, who never actually uses the database
  4. a user without a login
    1. have access to only that database.
  5. Within a database, users own objects and are granted permissions
1
2
3
4
5
6
7
# Shows the mapping of users in the database to login names
SELECT s.name as [Login Name], d.name as [User Name],
     default_schema_name as [Default Schema] 
   FROM sys.database_principals d  
      LEFT JOIN sys.server_principals s 
   ON s.sid = d.sid
 WHERE d.type_desc = 'SQL_USER';
Mapping of users

Databases vs. schemas

Schemas

A schema is defined as a collection of database objects that are owned by a single user and form a single namespace (a container of objects)

No two tables in the same schema can have the same name.

Namespace

A namespace is a set of objects that can’t have duplicate names

Principals and schemas

Users and schemas are two separate things

Permissions are granted to users, but objects are placed in schemas.

Schemas contain objects, not users

The statement GRANT CREATE TABLE TO sue refers to the user sue.

Then sue creates a table

1
CREATE TABLE mytable (col1 varchar(20));

Then the table is placed in sue's default schema, maybe the schema sue.

If another user wants to select from this table, then

1
SELECT col1 FROM sue.mytable;

Here, sue refers to the schema that contains the table

An object is referenced by the schema n which it is contained

In most cases, the owner of the schema is the same as the owner of all objects within the schema

The column pricipal_id in the view sys.objects contains the user_id of an object’s owner if it isn’t the same as the owner of the object’s schema.

sys.objects view

The default for the default schema is the dbo schema.

Default schemas

Every database also has a schema called sys, which provides a way to access all the system tables and views.

A user can have at most one default schema at any time

A user’s default schema is used for name resolution during object creation or object reference

You should always specify the schema name for all object access as well as object management

To create an object in a schema

  1. The schema must exist
  2. The user creating the object must have permission to create the object
  3. The user creating the object must have permission to alter the schema

Moving or copying a database

Detaching and reattaching a database

Detach

1
EXEC sp_detach_db <name of database>;
  1. Requires that no one is using the database.
    1. User ALTER DATABASE to SINGLE_USER mode to break connections
  2. Ensure no incomplete transactions and no dirty pages exist
  3. The database is removed from the sys.databases view and underlying system tables

DROP DATABASE

  1. Doesn’t check for dirty pages or open transactions
  2. Will remove the physical files

Reattach

1
2
3
4
CREATE DATABASE database_name  
    ON <filespec> [ ,...n ]  
    FOR { ATTACH  
        | ATTACH_REBUILD_LOG }
  1. The primary file is required to have a entry
  2. The FOR ATTACH_REBUILD_LOG option is limited to read/write databases
  3. When attaching a read-only database, you must specify the log file or files in the FOR ATTACH clause

Backing up and restoring a database

The database doesn’t need to come offline at all because the backup is a completely online operation

Understanding compatibility levels

Compatibility levels

110 (for SQL Server 2012), 100 (for SQL Server 2008), or 90 (for SQL Server 2005)

Check compatibility level

1
2
SELECT compatibility_level FROM sys.databases
WHERE name = '<database name>';
Compatibility levels

Change compatibility level

1
2
ALTER DATABASE <database name>
SET COMPATIBILITY_LEVEL = <compatibility-level>;
Reading
#SQL Server
SQL Server 2012 Internal Note_Databases and database file
http://example.com/2022/06/25/SQL Server 2012 Internal Note_Databases and database file/
Author
Jiacheng Xie
Posted on
June 25, 2022
Licensed under