DBA - Tips and Queries

There are more than one ways to create a database in MS SQL Server. You can do it by using GUI from SQL Server management studio or you can use T-SQL. In this blog I will demonstrate all possible options to create a database: TSQL:  CREATE DATABASE [DBName] CONTAINMENT = NONE ON PRIMARY (NAME = N'DBName', FILENAME = N'Path\DBName.mdf' , SIZE = 20480KB , FILEGROWTH = 51200KB) LOG ON (NAME = N'DBName_log', FILENAME = N'Path\DBName_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10240KB) Using GUI:    Using GUI is a simple method to create a Database in MS SQL Server, Below method is simple and basic for advanced option you will have the option to choose the option you need:       

 To check a user status in Oracle Database run below command:  SELECT USERNAME, ACCOUNT_STATUS from dba_users where username like 'User_Name'; Example query and its output: 

To Create a user in Oracle run below command: " CREATE USER User_Name IDENTIFIED BY MyPassword; " To allow a user to connect to a Database run below command: (Connect is a Role)  " GRANT CONNECT TO User_Name; " To allow a user to connect to a Database run below command: (Create Session is a privilege ) " GRANT CREATE SESSION TO   User_Name; " To grant Select on a Table or all tables simply run below SQL Statement:  " GRANT SELECT ON TableName TO User_Name; " " GRANT SELECT ON ANY TABLE To User_Name; " To grant Table Privileges a user simply run below SQL Statement:  " GRANT SELECT,INSERT,UPDATE,DELETE ON Schema.Table TO User_Name; " To grant DBA role to a user simply run below SQL Statement:  " GRANT DBA to User_Name "

To Extract Data of a Table or query into excel, text or CSV in MySQL: If you Don't have access or availability of  MySQL Workbench, phpMyAdmin, AQUA Data Studio or any other tool or if you want to extract data from shell you can simply use below query:  mysql -h IP/Hostname -P Port  -u Username -p DatabaseName -e"SELECT * from Table1"> /Path/Result1.csv ***Note: This script is for Linux OS, for Windows OS use appropriate path (C:\Folder\File.csv)

To Enable or Disable a User in MS SQL Server: There are two ways to Enable / Disable a user in MS SQL Server 1. Using TSQL To Enable a User:  use master go ALTER LOGIN [UserName] ENABLE go To Disable a User:  use master go ALTER LOGIN [UserName] DISABLE go 2. Using SQL Server Management Studio GUI: Open SQL Server Management Studio, go to object explorer and expend logins folder as in the below image:  Double click on desired user / login or right click and go to properties as in the below image: Click on Status and Enable / Disable user / login as in the below image:

To Find MS SQL Server Database files (mdf, ndf, ldf) path use following command: In this example Database Name is [Admin]  use [Admin] go select * from sysfiles go Output of the above Query is: 

There are different Ways to Import / Export Database, Tablespace, Table, Schema in Oracle Database:   To Import Table from one Database to another follow the following steps:  1. First check Data Pump directory:  select Directory_Name, Directory_Path from dba_directories; 2. If directory is not created or you would like to change the location of directory run this:  CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/disk1/folder1'; " 3. Additionally you can grant read/ write access to the user.  GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO User; " 4. Export Table command (Run this command on Shell not on SQL Console) expdp SYSTEM TABLES=Schema.Table DIRECTORY=DUMP_DIR DUMPFILE=TableDumpfileName.dmp LOGFILE=Table_dumplog.log 5. To Import the dump command (Run this command on Shell not on SQL Console). This command will import the Data in same Table. Table definition on destination must be same as it is in source:  ...

 To update user password and unlock user account use this query:  " ALTER USER "UserName" IDENTIFIED BY "Password" account unlock; "

  To assign read only rights to a user in Oracle use below command:  " grant select any table to "UserName"; "

  To Unlock a user account in Oracle use this query:  ALTER USER "UserName" account unlock;

To check long running operations in Oracle use below script:  " SELECT SID, SERIAL#, CONTEXT,OPNAME,TARGET,MESSAGE,SOFAR  ,TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK; " To check progress of RMAN operations like Backup or Restore: " SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK; "

 You can rename a Database in MS SQL Server by using below command:  sp_renamedb 'CurrentName', 'New_Name' If you or someone is already connected with Database this command may fail, In that situation , please use below command.  Alter Database 'CurrentName' Set Single_User with Rollback immediate go sp_renamedb 'CurrentName', 'New_Name' go Alter Database 'New_Name' Set Multi_user with Rollback immediate go

To get the size of SQL Server Databases on an instance run the below query. This query has three sections, you may run this query once or you can run each section one by one.  The result set will give you the name of Database, Size in MBs and Size in GBs.  /*Create a temp Table*/ USE MASTER GO CREATE TABLE #FileSize ( dbName NVARCHAR(128), FileName NVARCHAR(128), type_desc NVARCHAR(128), CurrentSizeMB DECIMAL(10, 2), FreeSpaceMB DECIMAL(10, 2) ); /*Insert into the table from system stored procedure*/ INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB) exec sp_msforeachdb 'use [?]; SELECT DB_NAME() AS DbName, name AS FileName, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files WHERE type IN (0,1);'; /*Select the result from the Table*/ SELECT DBName, sum(CurrentSizeMB) as Current...

To Check the size tables in a MS SQL Server Database run the below command, You can modify the query to change the output of  Size column to MB's, GB's and so on.    SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, CAST( ROUND( ( ( SUM(a.total_pages) * 8 ) / 1024.00 ), 2 ) AS NUMERIC(36, 2) ) AS TotalSpaceMB, SUM(a.used_pages) * 8 AS UsedSpaceKB, CAST( ROUND( ( ( SUM(a.used_pages) * 8 ) / 1024.00 ), 2 ) AS NUMERIC(36, 2) ) AS UsedSpaceMB, ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 AS UnusedSpaceKB, CAST( ROUND( ( ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 ) / 1024.00, 2 ) AS NUMERIC(36, 2) ) AS UnusedSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.par...

Below query will provide you with history of all Backups taken for a Database in SQL Server, Details include, Name, Type, Path of backup, Backup start time and end time, Backup size and more:  SELECT CONVERT( CHAR(100), SERVERPROPERTY('Servername') ) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb.dbo.backupset.database_name = 'master' --(...

  Below query will provide you the recovery model of databases on an instance:  SELECT Name, DATABASEPROPERTYEX(Name, 'RECOVERY') AS [Recovery Model] FROM master.dbo.sysdatabases where Name not in ( 'master', 'tempdb', 'model', 'msdb' )

Create a trigger to stop users from accessing Database from SQL Server Management Studio.  This is a simple method where you can stop someone from connecting to a Database using SQL Server management studio. This helps DBA,s to combat Application admins accessing database using application user name password which in some cases is  not encrypted in connection string.  CREATE TRIGGER [TR_LOGON_APP] ON ALL SERVER FOR LOGON AS BEGIN DECLARE @program_name nvarchar(128) DECLARE @host_name nvarchar(128) SELECT @program_name = program_name, @host_name = host_name FROM sys.dm_exec_sessions AS c WHERE c.session_id = @@spid IF ORIGINAL_LOGIN() IN('loginName') AND @program_name LIKE '%Management%Studio%' BEGIN RAISERROR( 'This login is for application use only.', 16, 1 ) ROLLBACK; END END; GO ENABLE TRIGGER [TR_LOGON_APP] ON ALL SERVER GO

To check which Backup file was used to restore a Database use this command:  SELECT [rs].[destination_database_name], [rs].[restore_date], [bs].[backup_start_date], [bs].[backup_finish_date], [bs].[database_name] as [source_database_name], [bmf].[physical_device_name] as [backup_file_used_for_restore] FROM msdb..restorehistory rs INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id] INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] where [rs].[destination_database_name] like 'DBNAME' ORDER BY [rs].[restore_date] DESC;

Below query will provide you with the stats of running operations (like Backups, Restore, DBCC operations e.t.c.), you can modify this query and add the SQL Operation you are trying to find the information about:    SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd( second, estimated_completion_time / 1000, getdate() ) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ( 'BACKUP DATABASE', 'RESTORE DATABASE', 'DbccFilesCompact', 'ROLLBACK', 'SELECT INTO', 'BULK INSERT', 'INSERT', 'MERGE' )

You can get all running processes on a SQL Server by running " SP_who2 " a system stored procedure, it will return all processes, however below query will give you options to fetch only user process and it gives you option to filter. You can tweak this query and get the desired result. The procedure " SP_who2 " and below query will help you identify long running queries, blocking queries, active or inactive sessions. DROP TABLE #sp_who2 CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255), Login VARCHAR(255),HostName VARCHAR(255), BlkBy VARCHAR(255),DBName VARCHAR(255), Command VARCHAR(255),CPUTime INT, DiskIO INT,LastBatch VARCHAR(255), ProgramName VARCHAR(255),SPID2 INT, REQUESTID INT) INSERT INTO #sp_who2 EXEC sp_who2 GO select * from #sp_who2 where spid>50;