SQL Server Single User Mode enablement

The Single user mode could be enabled for the user defined database. Lets see what could be the available options for you as a DBA Administrator for enabling single user mode.

While enabling this feature ensure that multiple users are not CONNECTED as the time of change, else all user connections gets closed without warning.

You should have DATABASE ALTER Permission to perform this activity.

Ensure that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF . If enabled , you will NOT be able to access the Database in Single User mode.

SQL Server Single User Mode using SQL Server Management Studio

Please find below the steps for enabling Single User Mode using SQL Server Management Studio .

  1. Connect to Microsoft SQL Server Instance
  2. Expand the Object Explorer and select the Database oracleappshelpDB
  3. Right click on Database oracleappshelpDB and select Properties
  4. Select Properties -> Options
  5. Select Restrict Access ->Single
  6. In case of multiple users connected to the Database Open Connection message will be shown. Click “Yes” to close all connections.

SQL Server Single User Mode using Transact SQL

Please find below the steps for enabling Single User Mode using SQL

USE master;
GO
ALTER DATABASE oracleappshelpDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE oracleappshelpDB
SET READ_ONLY;
GO

SQL Server increase database size

There could be a requirement where as a DBA Adminitrator you have been asked to increase the size for the existing SQL Server Database. The below options can be used for increasing SQL Server Database size.

Increase SQL Server Database Size using SQL Server Management Studio

Please find below the steps for increasing SQL Server Database size using SQL Server Management Studio.

  1. Connect to Microsoft SQL Server Instance
  2. Expand the Object Explorer and select the Database oracleappshelpDB
  3. Right click on Database oracleappshelpDB and select Properties
  4. Select Properties -> File
  5. Update the value for the Column Initial Size (MB) to increase the existing Database File Size. Minimum 1 MB to update to get the change reflected.
  6. You can also create a new File for the existing Database to increase the size. Click Add and enter the File name and the value for Initial Size (MB)

Increase SQL Server Database Size using T-SQL

The below SQL Query can be executed for increasing SQL Server Database size.

USE master;
GO
ALTER DATABASE oracleappshelpDB 
MODIFY FILE
    (NAME = oracleappshelpDatFile,
    SIZE = 100MB);
GO

SQL Server Database Operations

The blog provides the how to Create SQL Server Database, how to Select Database, How to Drop created Database in the MS SQL Server.

SQL Server Create Database

MS SQL Server provides various Database operations for which a SQL Server Database creation is required as a pre-requisite. MS SQL Server Database can be divided into 2 categories

  1. System Database
  2. User Database

System Databases are provided by MS SQL Server. Below given are the list of System Databases automatically provided:

  • Master
  • Model
  • MSDB
  • Tempdb
  • Resource
  • Distribution

User Databases are created by Database Administrator as per the application need.

Let’s say we need to have a new Database OracleappshelpDB. Below are the possible options to do the same.

Option 1:  Executed command for creating database

Syntax: Create Database <DATABASE_NAME>

SQL: Create Database OracleappshelpDB

Option 2: Using SQL Server Management Studio for creating database

  1. Connect to MS SQL Server.
  2. Right click on Database Folder
  3. Click -> New Database
  4. Enter the Database Name:OracleappshelpDB
  5. Owner: <Default>

SQL Server Select Database Command

Let’s say we have the table – ms_oracleapps_users which provides the listing of oracleappshelp registered users in the DATABASE – OracleappshelpDB. Below given are the possible option of selecting the data from the ms_oracleapps_users  table.

Option 1: Using T-SQL script

The below command allows/ enables you to use the Database OracleappshelpDB for performing the select query.

Syntax:

Exec use <DATABASE_NAME>

SQL: Exec use OracleappshelpDB

The below SELECT Query can be performed now on the  ms_oracleapps_users  table

Option 2: Using SQL Server Management Studio

Syntax: Select * from <DATABASE_NAME>               

SQL: Select * from ms_oracleapps_users

SQL Server Drop Database Command

Now let’s consider that we might not require the above created Database OracleappshelpDB. In that case, below given are the possible option of selecting the data from the ms_oracleapps_users table

Option 1: Using T-SQL script

The below command allows/ enables you to use the Database OracleappshelpDB for performing the select query.

Syntax: Drop Database <DATABASE_NAME>

SQL: Drop Database OracleappshelpDB

Option 2: Using SQL Server Management Studio

  1. Connect to MS SQL Server.
  2. Right click on the available Database – OracleappshelpDB
  3. Click -> Delete
  4. Click -> OK

  The Database OracleappshelpDB is removed.


Top beginners guide to SQL Server Tutorial

The SQL Server also known as MS SQL Server is a Relational Database Management System, developed by Microsoft. The tutorial provides the conceptual level details from basic to advanced SQL Server.

Microsoft SQL Server is a software which can be executed through network or can be installed locally on your computer.

The SQL Server tutorial includes topics like SQL Server Architecture, Installing SQL Server, SQL Server Data Types, SQL Server Create Database, SQL Server Drop Database, SQL Server Table creation commands, SQL Server Table Update commands, SQL Server Table Delete Commands, SQL Server DISTINCT Clause, SQL Server Group By Clause ,SQL Server TOP SQL commands, SQL Server Database Operations, SQL Server Aggregate Functions ( MIN, MAX,SUM, AVG, COUNT) , SQL Server Operators ( IS NULL, IS NOT NULL,UNION , LIKE, EXIST INTERSECT, IN, NOT, BETWEEN) , SQL Server Primary Key, SQL Server Foreign Key, SQL Server Create View, SQL Server Drop View, SQL Server Create INDEX, SQL Server Drop Index, SQL Server Create Trigger , SQL Server Drop Trigger, SQL Server Database backup, SQL Server Database Restore, SQL Server Interview Questions, SQL Server Common issues.

MS SQL Server Features

  • MS SQL Server is developed based on the RDBMS Specifications
  • MS SQL Server provides both Graphical User Interface as well as command-based software.
  • MS SQL Server is platform independent and scalable
  • MS SQL Server can be installed locally or can be connected through network
  • MS SQL Server supports an IBM Product, SQL (SEQUEL) language which is non-procedural, common database and case insensitive language
  • MS SQL Server provides SQL Server Analysis Services (SSAS) for the Data Analysis
  • MS SQL Server provides SQL Server Reporting Services (SSRS) for generating Data Reports
  • MS SQL Server provides SQL Server Integration Services (SSIS) for performing ETL (Extract, Transform, Load) operations.