Working with Database of Database Server-SQL

A SQL database is a collection of highly structured tables, where in each row reflects a data entity, and column defines a specific information. A maximum of 32,767 databases can be specified on an instance of SQL Server.

Here in this post, we will perform some activities on the database in SQL that will be useful in our daily routine of development and maintenance.

Create a Database in Database Server

Creating a database in SQL is straightforward. Here’s a simple example of how to do it using SQL commands. You can use this in a SQL environment, such as MySQL, PostgreSQL, or SQL Server.

Basic syntax to create a database in SQL

 CREATE DATABASE database_name;  

Basic syntax to use database of database server

 USE database_name;  

Get Lists of Available Database of Database Server

To list all available databases in a SQL database server, you can use the following commands

 
SELECT name FROM sys.databases; 
//or
EXEC sp_databases

Monitoring Database CPU Usage

It seems like you're interested in understanding how databases interact with CPU resources. When you run a query, the database engine utilizes CPU resources to process the SQL commands, perform calculations, and retrieve data.

Here the below SQL Command list outs databases with their CPU usage.

 SELECT d.name, T.[CPUTimeAsPercentage]   
   FROM   
   (SELECT    
     [Database],   
     CONVERT (DECIMAL (6, 3), [CPUTimeInMiliSeconds] * 1.0 /    
     SUM ([CPUTimeInMiliSeconds]) OVER () * 100.0) AS [CPUTimeAsPercentage]   
    FROM    
    (SELECT    
      dm_execplanattr.DatabaseID,   
      DB_Name(dm_execplanattr.DatabaseID) AS [Database],   
      SUM (dm_execquerystats.total_worker_time) AS CPUTimeInMiliSeconds   
     FROM sys.dm_exec_query_stats dm_execquerystats   
     CROSS APPLY    
     (SELECT    
       CONVERT (INT, value) AS [DatabaseID]   
      FROM sys.dm_exec_plan_attributes(dm_execquerystats.plan_handle)   
      WHERE attribute = N'dbid'   
     ) dm_execplanattr   
     GROUP BY dm_execplanattr.DatabaseID   
    ) AS CPUPerDb   
   ) AS T join sysdatabases d on T.[Database]=d.name  
  -- WHERE T.[Database] = @dbName   

Monitoring Memory Usage of Database of Database Server

Monitoring and managing memory usage in a database is crucial for performance and stability. Here’s an overview of how to check memory usage in various database management systems

In SQL Server, you can use the following queries to monitor memory usage database of database server:

 SELECT   
   DB_NAME(database_id) AS DatabaseName,  
   CONVERT(DECIMAL(18,2), SUM(size * 8.0 / 1024)) AS SizeMB,  
      CONVERT(DECIMAL(18,2), SUM(size * 8.0 / (1024*1024))) AS SizeGB  
 FROM   
   sys.master_files  
 GROUP BY   
   database_id;  

Database Backup History in SQL Server

To view the backup history of a database in SQL, you can use specific queries based on the database management system (DBMS) you are using.

In SQL Server, you can retrieve backup history using the msdb database. Here’s a query to get backup information:

 SELECT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,   
   BKS.database_name,   
   BKS.backup_start_date,   
   BKS.backup_finish_date,   
   BKS.expiration_date,   
   CASE BKS.type   
    WHEN 'D' THEN 'Database'   
    WHEN 'L' THEN 'Log'   
    END AS backup_type,   
   BKS.backup_size,   
   DKMF.logical_device_name,   
   DKMF.physical_device_name,   
   BKS.[name] AS backupset_name,   
   BKS.[description]   
 FROM   
   msdb.dbo.backupmediafamily AS DKMF  
   INNER JOIN msdb.dbo.backupset BKS ON DKMF.media_set_id =BKS.media_set_id   
 ORDER BY   
   BKS.database_name,   
   BKS.backup_finish_date   

Database Restore History in SQL Server

In SQL Server, you can track the restore history of databases by querying the msdb system database, which stores information about backups and restores. Here are some useful queries to get details about restore operations.

You can use the following query to see the restore history of all databases:

 SELECT   
 [restore_date]  
   ,[destination_database_name]  
   ,[user_name]  
   ,[backup_set_id]  
   ,[restore_type]  
   ,[replace]  
   ,[recovery]  
   ,[restart]  
 FROM [msdb].[dbo].[restorehistory]  

DROP A Database of Database Server

Dropping a database in SQL will permanently delete the database and all its data, so be sure to back up anything important before proceeding.

To drop a database in SQL Server, you can use the following command:

 DROP DATABASE YourDatabaseName;  

Thanks