Msg 3702, Level 16, State 3, Line 2 Cannot drop database “DataBaseName” because it is currently in use.

Try to drop a SQL database, but get following errors:

USE Master;
GO
DROP DATABASE dbTEST;
GO
Msg 3702, Level 16, State 4, Line 1
Cannot drop database "dbTEST" because it is currently in use.

SOLUTION

Kill the blocking sessions, or set database in SINGER_USER mode with “Rollback Immediate” option to kill  and rollback the transactions.

USE [master]
GO
ALTER DATABASE dbTEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE [master]
GO
DROP DATABASE dbTEST;
GO

After this, check both data files and log files are cleaned up as well.

How to Set SQL or Oracle Database to Single-User Mode and How to Check Single User or Multi User Mode

In Oracle database, place the instance in restricted mode.  The users already connected to the database will not get disconnected. You need to manually kill these sessions.

SQL> alter system enable restricted session;

OR

Startup in restricted mode.

SQL> startup restrict;

Check the instance mode

SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED

In SQL database,  Below are the two most popular ways to set up database to single-user mode in SQL Server:

Using SQL Server Management Studio

Right-click the database to change, and then click Properties -> click the Options page->From the Restrict Access option, select Single.
If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

Using Transact-SQL

USE master;
GO
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Check SQL Database Single User or Multi User Mode

a)  Select ServerProperty(‘IsSingleUser’)

It should return 1, 0, or Null.

1 = Single user.
0 = Not single user
NULL = Input is not valid, or an error.

use MyDatabase
go
Select ServerProperty('IsSingleUser')

b) Select from sys.database,  it should retuen:

  • SINGLE_USER
  • MULTI_USER
SELECT user_access_desc 
FROM sys.databases 
WHERE name = 'MyDatabase'

3) From SSMS, Right-click the database , and then click Properties -> click the Options page->From the Restrict Access option.

SQL Server Backup History Details

Handy script to list backup history details

Here is a script to list SQL server backup details for the last two weeks  or 14 days.

SELECT
     bs.database_name                                               AS DatabaseName
     ,CASE
        WHEN bs.database_name IN ('model','master','tempdb','msdb')
        THEN 1
        ELSE 0
     END                                                            AS IsSystemDatabase
    ,bs.backup_start_date                                           AS StartTime
    ,bs.backup_finish_date                                          AS FinishTime
    ,bs.expiration_date												AS ExpiryTime
    ,DATEDIFF(MI, bs.backup_start_date, bs.backup_finish_date)      AS DurationMin
    ,DATEDIFF(SS, bs.backup_start_date, bs.backup_finish_date)      AS DurationSec
    ,CASE bs.type
        WHEN 'D' THEN 'Full Backup'
        WHEN 'I' THEN 'Differential Database'
        WHEN 'L' THEN 'Log'
        WHEN 'F' THEN 'File/Filegroup'
        WHEN 'G' THEN 'Differential File'
        WHEN 'P' THEN 'Partial' 
        WHEN 'Q' THEN 'Differential partial'
     END                                                            AS BackupType
    ,CAST(ROUND(((bs.backup_size/1024)/1024),2) AS decimal(10,2))   AS BackupSizeMb
    ,CAST(
        ROUND(((bs.compressed_backup_size/1024)/1024),2) 
                                                AS decimal(18,2))   AS CompressedSizeMb
    , ' '+ISNULL(
      CASE WHEN has_bulk_logged_data = 1 THEN '| Bulk Logged Data' END
    + CASE WHEN is_snapshot = 1 THEN '| Snapshot' END
    + CASE WHEN is_readonly = 1 THEN '| Read-Only' END
    + CASE WHEN is_single_user = 1 THEN '| Single User' END
    + CASE WHEN has_backup_checksums = 1 THEN '| Backup Checksums' END
    + CASE WHEN is_damaged = 1 THEN '| Damaged' END
    + CASE WHEN begins_log_chain = 1 THEN '| Begins Log Chain' END
    + CASE WHEN has_incomplete_metadata = 1 THEN '| Incomplete Metadata' END
    + CASE WHEN is_force_offline = 1 THEN '| Force Offline' END
    + CASE WHEN is_copy_only = 1 THEN '| Copy Only' END
    ,'')                                            AS BackupOptions    
    ,bmf.physical_device_name                       AS BackupFile
    ,bs.user_name                                   AS ExecUser
FROM msdb.dbo.backupmediafamily AS bmf
    INNER JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id
WHERE DATEDIFF(dd, bs.backup_finish_date, GETDATE()) <= 14
ORDER by bs.backup_start_date  DESC;

List All Database Files on SQL Server

SELECT 
    db.name AS             [Database Name], 
    mf.name AS             [Logical Name], 
    mf.type_desc AS        [File Type], 
    mf.name AS	           [Logical Name],
    mf.state AS	           [ State ],
    mf.state_desc AS       [ State Desc],
    mf.physical_name AS    [Path],
    CAST((mf.Size*8)/1024/1024 AS DECIMAL(18,1)) AS [Current Size(GB)],
    CASE 
	WHEN mf.is_percent_growth >0 THEN
	     'TRUE'  
	ELSE
	     'FALSE' 
	END AS [Autogrowth],
    CASE 
	WHEN  mf.max_size = 0  THEN  'No growth is allowed'
	WHEN  mf.max_size = -1 THEN  'Unlimited'
	ELSE
             CAST(CAST(mf.max_size as BIGINT)*8/1024/1024 AS VARCHAR(30)) +' MB'  
	END AS [MaximumSize] 
FROM 
     sys.master_files AS mf
INNER JOIN sys.databases AS db 
  ON  db.database_id = mf.database_id
Order by db.name,mf.type_desc;

How to Find Interval Partition Name by Date Column Value

Since  HIGH_VALUE  in DBA_TAB_PARTITION view is LONG type, so it is difficult to get the system generated interval partition name straight away. Here is a small function to achieve this purpose.

SQL> desc dba_tab_partitions
Name              Null?    Type
----------------- -------- ------------
TABLE_OWNER                VARCHAR2(30)
TABLE_NAME                 VARCHAR2(30)
COMPOSITE                  VARCHAR2(3)
PARTITION_NAME             VARCHAR2(30)
SUBPARTITION_COUNT         NUMBER
HIGH_VALUE                 LONG
...
.

1)first, create a function.

set serveroutput on
CREATE OR REPLACE FUNCTION GET_INTERVAL_PARTITION_NAME(
tableowner in VARCHAR2, tablename VARCHAR2, partitiondate VARCHAR2 )
return VARCHAR2
IS
  dt date;
begin
for x in (select table_name,partition_name, high_value 
          from dba_tab_partitions 
          where INTERVAL='YES' and TABLE_OWNER=tableowner 
          and TABLE_NAME=tablename )
loop
execute immediate 'select '||x.high_value||' from dual' into dt;
        if to_char(dt - 1/24,'YYYYMMDD') = partitiondate 
        then
          --dbms_output.put_line(' partition: '||x.table_name||'.'||
                               x.partition_name||' '||x.high_value);
          return x.partition_name;
       end if;
end loop;
end;

2) Get the interval partition name in SQLPLUS by calling the previous function.

SQL> var pat_name varchar2(30);
SQL> exec :pat_name:=GET_INTERVAL_PARTITION_NAME('TESTUSER','CDR_TBL','20181225');

PL/SQL procedure successfully completed.

SQL> print :pat_name;

PAT_NAME
--------------------------------------
SYS_P122658

3) Get the partition name in shell environment.

$ export PARTITIONNAME=`sqlplus -s / as sysdba <<EOF
set head off feedback off serverout off
var pat_name varchar2(30);
exec :pat_name:=GET_INTERVAL_PARTITION_NAME('TESTUSER','CDR_TBL','20181225');
print pat_name
exit
EOF`

$ echo $PARTITIONNAME
SYS_P122658