Distribution database:-
The distribution database is another system database in SQL
Server.
It is very useful in the replication of objects.
The utilization of this database is to store metadata of
replication & information of distribution agents & subscribers.
This database contains details of the article, meta-data of
replication, and data.
Resource database:-
The 5th system database in SQL Server is the resource database. This database is hidden from users and is read-only.
Resource databases, which are used to offer a collection of system objects that are consistent across all editions of SQL Server, were first introduced with SQL Server 2005.
Here are some important points about it:
Purpose: The read-only Resource database serves as a storage space for the system objects and metadata that the SQL Server instance needs. It keeps track of system objects like functions, stored procedures, and system catalog views.
Hidden Database: Resource databases are concealed and not immediately accessible by users or administrators, in contrast to user databases. The SQL Server engine is intended to be used internally.
Single Instance: Each SQL Server instance only has one Resource database, which is shared by all the databases that are hosted by that instance.
Patches and Upgrades: The Resource database is modified to update system objects during the installation of a service pack or a SQL Server upgrade. This aids in maintaining compatibility between the installed version and the system catalog.
Version-specific: The Resource database only supports that particular version. A new Resource database is built to correspond with the system objects of the new version whenever an SQL Server instance is updated.
Recovery Model: Because the Resource database is designed to be read-only and does not require transaction log backups, it employs the Simple recovery model.
Security: The Resource database includes stringent security controls since it is necessary for the SQL Server instance to run. It is only accessible to the SQL Server service account and highly privileged administrators.
Avoid Modifications: Playing around with the Resource database might cause serious problems and possibly ruin the database. Any direct modifications to this database must be avoided.
System Object Access: When resolving system object references in queries, the SQL Server engine checks the Resource database. It aids in effectively retrieving data about system items.
File Location: The Resource database files (MDF and LDF) are kept in the 'MSSQLBinn' folder of the SQL Server installation directory.
By default, the location of resource database files are <drive>:\Program Files\Microsoft SQL Server\MSSQL1.<instance_name>\Binn\
The resource database's ID is 32767.
The T-SQL command cannot be used to back up and restore this database.
MSDB Database:-
A key and important system database in Microsoft SQL Server is the MSDB database. In addition to other crucial functions, it is crucial for handling different administrative activities, scheduling jobs, and keeping historical data about SQL Server Agent. The MSDB database assists in streamlining and automating routine maintenance, backups, and data transfer activities by serving as a store for crucial metadata and configurations.
1. MSDB database is the 4th system database which is used
to store details of SQL Server Agent like job history, database backup & restore
history, job schedules, alerts, and operators.
2. This database contains information on backups, jobs, alerts,
operators, and other system-related tasks.
3. We can't drop the MSDB database.
4. We can't remove the primary filegroup, primary data file,
or log file.
5. We can't rename the database or primary filegroup.
6. We can't set the database to OFFLINE.
7. We can't set the primary filegroup to READ_ONLY.
8. The Recovery mode of the MSDB database is SIMPLE. It means we can take T-Log backup.
9. The database ID of MSDB is 4.
Advantages of MSDB database:
Centralized Storage for SQL Server Jobs: SQL Server Agent tasks, including their schedules, histories, and step-by-step descriptions, are stored in a single location, the MSDB database. By streamlining job administration and monitoring, this centralized storage makes it simpler to schedule and keep track of diverse jobs and procedures.
Maintenance Plan Storage: Backups, index upkeep, and integrity checks are just a few of the maintenance operations that database administrators may schedule and manage thanks to the storage of maintenance plan data by MSDB. This assists in minimizing downtime, guaranteeing database health, and automating normal maintenance tasks.
Model Database:-
1. Model database is a third-system database.
2. This database is used as a template to create a new user
database.
3. When we fire a command to create a new user database, the system just
creates the replica of the Model database with a specified name which includes predefined
objects, settings, and configurations.
4. Changes made in the Model database, will be inherited by all user databases. It means, that if we create an object in the Model database, it'll be included automatically in all newly created user databases.
5. We can't add any file or filegroup in the Model database.
6. We can't change the owner of the Model database from SA to any other.
7. We can't set the Model database to OFFLINE.
8. The recovery model of this database is Full. Hence, we can take full, differential, and transactional log backups.
9. The ID of the Model database is 3.
Components of the
Model Database:
The model database also contains lots of components like system tables, system views, system functions system stored procedures, etc.
Details of a few important components are given below:
System Tables: This database contains a few system tables like sys. objects, sys. columns, sys. indexes, and sys. views etc.
System Views: The model database also contains a few system views which provide easy access to system tables. For example, sys.database_files,
sys.database_permissions, and sys.database_role_members.
System Stored Procedures: A few system-stored procedures are also available in this database which helps DBA perform their administrative tasks. For example, sp_adduser, sp_helpdb and sp_addrole etc.
Functions of the Model Database:
Here are a few essential functions
of the Model database for your reference:
1. Providing a Template
2. Defining Database Properties
3. Managing Database Security
4. Supporting Customization
Conclusion:
The model database is critical so always keep good db backup in a safe location for any unprecedented issue.
1. TempDB database is also an important & system database.
It is used to store temporary data like temporary tables, temporary stored
procedures, internal objects, and other temporary objects.
2. When we restart the SQL service, the system automatically re-creates the TempDB database.
3. The Recovery Model of TempDB is also Simple.
4. TempDB database id of TempDB is 2.
5. In TempDB, not possible to add a filegroup.
6. We can’t take TempDB backup or restore.
7. We can’t change the database owner of TempDB. It should be sa always.
8. We can’t create a snapshot of the TempDB database.
9. We can’t drop the TempDB database.
10. We can’t set the TempDB database as OFFLINE.
Components of the
Tempdb Database:
As we specified above, the TempDB database is also a system
database that contains lots of components like system tables, system views,
and system stored procedures. Sometimes, the database engine creates some Internal objects like temporary
large objects (LOB) and work files for hash operations, to store Intermediate
sort results and Version stores, etc.
A few essential components of the master database are given:
System Tables: This
database also contains a lot of system tables that store details of temporary
objects like temp tables, table variables, cursor tables, etc.
System Views:
This database contains system views that provide easy access to system tables.
System Stored
Procedures: The Tempdb database also has system stored procedures that help
DBAs perform their tasks like sp_help, sp_rename, sp_helpindex, etc.
Functions of the
Tempdb Database:
TempDB is a critical database that helps to manage temporary objects in SQL Server.
A few critical functions
of the Tempdb database are given below:
1. Storing Temporary
Objects
2. Supporting Snapshot Isolation
3. Managing Transactions
4. Supporting Online Index Operations
5. Supporting Sorting
and Hashing
Conclusion:
As we are aware, Tempdb is a very important database in SQL Server, so always configure TempDB properly to get good performance.
FAQs
Q. What is tempdb in SQL Server?
Ans. TempDB database is also an important & system database.
Q. Why tempdb is used in SQL Server?
Ans. TempDB is used to store temporary data like temporary tables, temporary stored procedures, internal objects, and other temporary objects.
Q. When is TempDB used in SQL Server?
Ans. The system uses TempDB to store temporary data like temporary tables, temporary stored procedures, internal objects, and other temporary objects.
Q. Can we take a backup of tempdb in SQL Server?
Ans. We can’t take TempDB backup or restore.
Q. How can you increase the size of tempdb in SQL Server?
Ans. Right-click on the TempDB, Select Properties, go to Files & change the size (MS) as per requirement.
We can use the below command also to increase the size of TempDB.
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE(NAME=N'tempdev',SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE(NAME = N'templog', SIZE = 10240KB )
GO
Q. How do I restore tempdb in an SQL Server?
Ans. We can’t take TempDB backup or restore the TempDB database.
Q. What is the use of TempDB in an SQL Server?
Ans. The system needs some space to store data/results during the execution of user query/operation. Generally, the system stores temporary data like temp. tables, temp. SP, internal objects, and other temp. objects etc.
Q. How do you delete TempDB files in SQL Server?
Ans. If the SQL service is running, the system will not allow deleting any file of the TempDB database. To delete any TempDB file, need to stop SQL service & change the system configuration for TempDB.
Q. How do you free up TempDB space in SQL Server? / Tempdb drive is full in SQL Server? / If TempDB is full what to do in SQL Server? / How to clear TempDB in SQL Server? / How to shrink TempDB in SQL Server? / What to do when TempDB is full in SQL Server?
Ans. To Free up the TempDB space, follow the below steps(Before running on the Prod environment, check at the Test or Dev environment or discuss with Sr. DBA):-
1. Check whether any bulky query is running or not. If running, check with the application team to stop or restart that process.
2. Still the issue is available: If not, then connect with the Windows Team & try to get more free space on the drive which has TempDB data & log files.
3. Still the issue is available:-If not possible to add more space, try to shrink TempDB after running CHECKPOINT in TempDB.
CHECKPOINT
go
DBCC FREEPROCCACHE & then
go
USE [tempdb]
GO
--To Shrink Data File
DBCC SHRINKFILE (N'tempdev', 0, TRUNCATEONLY)
GO
--To Shrink Log File
DBCC SHRINKFILE (N'templog', 0, TRUNCATEONLY)
GO
GO
4. Still the issue is still available, get approval from the application owner/server owner to reboot the server. Schedule the change. Perform a Pre-health check, take the help of the Windows Team to reboot the server, and then Post-health check
Q. What is the purpose of TempDB in SQL Server?
A. TempDB provides the space to store temporary data like temporary tables, temporary stored procedures, internal objects, and other temporary objects.
Q. What should be the initial size of TempDB in SQL Server?
A. The number of TempDB files & the size depend on multiple factors like server configuration, load from the application, etc.
Q. Where to find TempDB in SQL Server? / Where is TempDB located in SQL Server?
A. To find TempDB, Just follow the below steps:-
1. Connect with your SQL Server Instance
2. Expand Instance
3. Expand Database
4. Expand System Databases
5. Here we'll get TempDB
To get the physical location of TempDB files, use the below query to get more details
Use Master
go
SELECT * FROM sys.master_files WHERE database_id=2
Q. How to shrink TempDB in SQL Server without restarting the server?
A. Just refer above question which has all the steps. For this, refer only to steps 1, 2, and 3.
Q. How to change the initial size of TempDB in SQL Server?
A. To change the Initial size of TempDB, we have two options:
1. Specify at the SQL server installation
2. Specify in the property of TempDB (If the SQL server is already installed).
Q. How to rebuild tempdb in SQL Server?
A. Just reboot the server. The system will automatically rebuild/recreate the TempDB database. But before restarting the SQL service, check with your Sr. DBA & application owner.
Q. How to check the size of tempdb in SQL Server?
A. Use the below command to check the size of TempDB or any other database
Use TempDB
go
sp_spaceused
Q. How to restore tempdb in SQL Server?
A. Restore TempDB is not allowed.
Q. When tempdb is full in SQL Server? / Why does tempdb size increase in SQL Server?
A. TempDB is generally full when the bulk query is running or the Index maintenance job is running etc.
Q. Can we shrink tempdb in SQL Server?
A. Yes, we can shrink tempdb.
Q. Can we take a backup of tempdb in SQL Server?
A. No, We can't.
Q. Can we shrink the tempdb data file in SQL Server?
A. Yes, we can shrink tempdb.
Q. How can I tell what is consuming tempdb space in SQL Server? /
How to check what is consuming tempdb space in the SQL Server?
A. Use the below query to get the details of TempDB usage:
SELECT @@ServerName as ServerName, DB_Name() AS DBName,
(SUM(fsu.unallocated_extent_page_count)*1.0/128) AS [AvailableFreeSpace(MB)]
,(SUM(fsu.version_store_reserved_page_count)*1.0/128) AS [UsedByVersionStore(MB)]
,(SUM(fsu.internal_object_reserved_page_count)*1.0/128) AS [UsedByInternalObjects(MB)]
,(SUM(fsu.user_object_reserved_page_count)*1.0/128) AS [UsedUserObjects(MB)]
FROM tempdb. sys.dm_db_file_space_usage fsu;
Q. Why TempDB is growing in SQL Server?
A. TempDB is used to store temporary objects & query results.
TempDB is the shared resource that is heavily used by all user databases.
Below are a few reasons for tempdb growth in SQL Server:-
1. Version Store: In Snapshot or Read Committed Snapshot Isolation level, the system uses TempDB to store older versions of rows. In the case of long-running transactions, TempDB grows very fast.
2. Multiple Active Transactions: In case of multiple active transactions, the system uses tempdb which also grows the size rapidly.
3. Sorting and Grouping: If the query is fired with ORDER BY (means result in sorted order) or GROUP BY (to get aggregated result), one of the causes is to grow TempDB size.
4. Temporary Tables: Temporary tables are stored in TempDB. If we use a lot of temporary tables in our queries, It also helps to grow the size of TempDB.
5. Index Operations: At the time of Index creation or rebuild online, SQL Server uses TempDB. It will grow the size of tempdb.
6. Query Spills: In case of large datasets or complex calculations, the system uses TempDB & this will also increase the size of tempdb.
Q. How to resize tempdb in SQL Server?
A. To resize TempDB, use the below command
ALTER DATABASE TempDB MODIFY FILE(NAME = tempted, SIZE = 50000MB);
Q. What is tempdb contention in SQL Server?
A. Tempdb contention is a performance issue that occurs when multiple sessions try to access the same tempdb resources simultaneously. It creates blocking which returns the result slowly & creates slow query performance, timeouts, deadlocks, and other issues.
Q. What should be the tempdb size in SQL Server?
A. TempDB size in any environment depends on multiple factors like current resources, load on the server, facing TempDB contention issues, etc etc.
To get better performance:-
keep data & log files on separate drives.
Keep the same size as all TempDB files.
The number of data files in TempDB should not be more than the number of logical processors. It means, if we have 8 logical processors, we can create 8 data files on TempDB.
Q. How to check if TempDB is full in SQL Server?
A. Use the below command to check the free space on TempDB
USE tempdb
EXEC sp_spaceused
In MS SQL Server, two types of databases are there:
1. System Databases and
2. User Databases.
System Databases:
System databases are important databases & they are automatically
created during MS SQL Server installation. System databases store server information.
There are four main system databases in MS SQL Server but 2 other DBs are also available:
Master Database:-
1. The master database is one of the system databases and keeps details of MS SQL Server instances, system-level information, metadata, logins, and configurations.
2. If the Maser database is not online or up. SQL server service will not start.
3. The Recovery Mode of the master database is Simple, hence we can’t take the T-Log backup of this database.
4. The master is the first database; hence the ID of this database is 1.
5. Never store the master database-related files on the C drive.
6. We can't add files or file groups.
7. We can't change the database owner of the master database from SA to any other.
8. We can't drop the master database.
9. We can't set the database to OFFLINE.
10. We can't set the database or primary file group to READ ONLY.
11. We can't enable CDC (Change Data Capture) on the master database.
12. The master database will not be a part of mirroring or log shipping.
