article SQL Server Database Structure

Each SQL Server database consists of at least two files:

  • A primary data file with an .mdf extension.
  • A log file with an .ldf extension.

There are also secondary data files with .ndf extensions. A database can have only one primary data file, zero or more secondary data files, and one or more log files. Each database file can only be used by one database.

In a database, data files store persistent data. For ease of management, you can group one or more data files into logical tablespaces. The filegroup is the SQL Server equivalent of an Oracle tablespace. SQL Server filegroups fall under, and are associated with, the individual databases. The SQL Server data hierarchy is:

Instance / Database / FileGroup / Data file

Each data file can be a member of only one filegroup, but the log files are managed separately from one another. There are three types of filegroups:

  • Primary
  • User defined
  • Default

When you configure your SQL Server databases, you can specify the maximum size of data files to prevent disk drives from running out of space. If you do not specify the size of data files, the database assumes that the size is unlimited.

NOTE: Unlimited data files can not initiate up.time alerts. Since up.time measures datafile and log file size as a percentage of their maximum size, if a datafile size has an infinite maximum size, the percent of maximum datafile size must be near zero. Therefore you should always set the maximum size of each datafile for effective monitoring.


The following diagram illustrates six data files in three filegroups in three databases across two instances of a system:

If you set a Critical threshold of 90% for SQL Server Instance_B and a Warning threshold of 70%, the SQL Server Tablespace Check monitor watches the size of all data files in that instance. The monitor will send an alert if any of the files reaches or exceeds the defined thresholds.

Related Articles


Running up.time with an MS SQL Server database

RatingViews
article

The up.time DataStore can run on a MS SQL Server database to leverage existing SQL Server installations and take advantage of SQL Server's advanced replication, recovery and archiving options.

By: uptime Support | Date Created: 7-10-2006 | Last Modified: 6-19-2014 | Index: 087

  7571

Configuring SQL Server Ports

RatingViews
article

This article explains how to configure static and dynamic SQL Server ports.

By: uptime Support | Date Created: 7-11-2007 | Last Modified: 6-28-2011 | Index: 193

  7496

Migrating Your DataStore To a New Database

RatingViews
article

This article outlines how to move your DataStore between different database types.

By: uptime Support | Date Created: 2-21-2007 | Last Modified: 6-30-2011 | Index: 142

  3701

SQL Server Advanced Metrics

RatingViews
article

This article outlines the performance metrics that the up.time SQL Server Advanced Metrics monitor collects from a SQL Server database.

By: uptime Support | Date Created: 1-23-2007 | Last Modified: 1-3-2012 | Index: 122

  4819

Does the database need to reside on the up.time server?

RatingViews
article

No, the up.time DataStore (database) connection is designed to allow the DB to reside on a different server than the monitoring station. This applies to Oracle and SQL Server databases, the default...

By: uptime Support | Date Created: 12-31-2002 | Last Modified: 5-2-2014 | Index: 259

  1639

User Comments



No comments have been posted.

Copyright © 2019 IDERA, Inc.   Legal   Privacy Statement