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


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


Configuring SQL Server Ports


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


Migrating Your DataStore To a New Database


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


SQL Server Advanced Metrics


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


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


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


User Comments

No comments have been posted.

Copyright © 2019 IDERA, Inc.   Legal   Privacy Statement