Category: SQL Scripts for DBA

How to Write Errors into SQL Server Log

The following T-SQL writes errors into SQL log when there are databases have not been backed up in last 12 hours:

IF EXISTS (
    SELECT bs.database_name,  MAX(bs.backup_finish_date) AS LatestDatabaseBackupDate
    FROM  msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
    INNER JOIN master.sys.databases d ON d.name = bs.database_name
    WHERE bs.type = 'D' and d.name <> 'tempdb' and d.state = 0 and d.source_database_id is null
    GROUP BY bs.database_name HAVING DATEDIFF(HH, MAX(bs.backup_finish_date), GETDATE()) > 12
) RAISERROR(N'On TESTSERVER\TESTINST one or more databases have not been backed up in last 12 hours, please check..', 17, 1) with log

Check the log as below:

ALso we can check spid 55 details:

select * from sys.sysprocesses where spid =55;
Advertisement

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;