T-SQL

Get the total size of the latest full backups on the SQL server

you can update –and backup_start_date > GETDATE()-1 or WHERE Position = 1 sections to get customized results.

WITH LastBackUp AS
(
SELECT bs.database_name,
bs.backup_size,
bs.backup_start_date,
bmf.physical_device_name,
Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE bs.[type] = ‘D’ –and backup_start_date > GETDATE()-1
AND bs.is_copy_only = 0
)
SELECT sum( CAST(backup_size / 1048576 /1024 AS DECIMAL(10, 2) )) AS [Total_BackupSizeGB] FROM LastBackUp
WHERE Position = 1

Getting Database Backup History / who took your database backup and when – SQL server?

SELECT    bs.database_name,    bs.backup_start_date,    bs.backup_finish_date,    bs.server_name,     bs.user_name,    bs.type,    bm.physical_device_nameFROMmsdb.dbo.backupset ASbsINNERJOINmsdb.dbo.backupmediafamily ASbm onbs.media_set_id = bm.media_set_id

for Detailed backup info use this T-SQL:

SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ‘ ‘ + ‘MB’ AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ‘ ‘ + ‘Seconds’ TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN ‘D’ THEN ‘Full’
WHEN ‘I’ THEN ‘Differential’
WHEN ‘L’ THEN ‘Transaction Log’
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
ORDER BY backup_start_date DESC, backup_finish_date
GO

Get Size of all individual databases and Total value – SQL server

SELECT d.NAME
,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs
,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs into #Totaldbsize
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 — Skip system databases
GROUP BY d.NAME
ORDER BY d.NAME

select * from #Totaldbsize

SELECT sum (size_gbs) as TotalDBSize
FROM #Totaldbsize

TSQL to setup custom drive space alerts on Solarwinds DPA (Database performance Analyzer)

— as shown above choose the custom SQL Alert – Multiple Numeric return while creating Alerts and use the following script to configure the custom alerts.

SET NOCOUNT ON

DECLARE @DBname nvarchar(128),

    @CMD1     nvarchar(200),

    @Drive    nvarchar(1),

    @MB_Free  int

IF OBJECT_ID(‘tempdb..#rdFreeSpace’) IS NOT NULL

DROP TABLE #rdFreeSpace

IF OBJECT_ID(‘tempdb..#rdDataDrives’) IS NOT NULL

DROP TABLE #rdDataDrives

IF OBJECT_ID(‘tempdb..#rdTotalSpace’) IS NOT NULL

DROP TABLE #rdTotalSpace

CREATE TABLE #rdDataDrives (DB nvarchar(128), Drive nvarchar(1));

CREATE TABLE #rdFreeSpace (Drive char(1), MB_Free int);

CREATE TABLE #rdTotalSpace (Drive char(1), GB_Total int);

INSERT INTO #rdFreeSpace EXEC xp_fixeddrives;

INSERT INTO #rdDataDrives

EXEC sp_MSforeachdb

@command1=”use [?]

SELECT DB_NAME(), UPPER(LEFT(filename,1)) as DRIVE

FROM dbo.sysfiles”

— always check the C: drive

INSERT INTO #rdDataDrives (Drive) VALUES (‘C’)

insert into #rdTotalSpace
SELECT distinct(left(volume_mount_point, 1)),
total_bytes/1048576/1024 as Size_in_GB
FROM sys.master_files AS f CROSS APPLY
sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576,
available_bytes/1048576 order by 1

SELECT DISTINCT dd.Drive, fs.MB_Free/1024 FreeSpace_GB
FROM #rdDataDrives dd join #rdFreeSpace fs
on fs.Drive = dd.drive
join #rdTotalSpace ft on ft.Drive = dd.drive
where (fs.MB_Free < 10240 and ft.GB_Total < 500) or (fs.MB_Free < 102400 and ft.GB_Total > 500)

TSQL to change all database properties in SQL server

in below example I changed page_verify_option_desc value to checksum value for all the databases, we can replace that with any

if exists (
select 1
from sys.databases
where page_verify_option_desc != ‘CHECKSUM’
and state_desc = ‘ONLINE’
)
begin
select ‘ALTER DATABASE ‘ + QUOTENAME(name) + ‘ SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;’
from sys.databases
where page_verify_option_desc != ‘CHECKSUM’
and state_desc = ‘ONLINE’ and name not in (‘master’,’msdb’,’model’,’tempdb’)
end
else
select ‘ALL the databases has page_verify_option_desc as CHECKSUM’

TSQL to set all databases in full recovery mode in SQL Server

set nocount on
go

if exists (
select 1
from sys.databases
where recovery_model_desc = ‘SIMPLE’
and state_desc = ‘ONLINE’
)
begin
select ‘ALTER DATABASE ‘ + QUOTENAME(name) + ‘ SET RECOVERY FULL with ROLLBACK IMMEDIATE;’
from sys.databases
where recovery_model_desc = ‘SIMPLE’ — since you only want SIMPLE recovery model databases to get changed to FULL recovery.
and state_desc = ‘ONLINE’ and name not in (‘master’,’msdb’,’model’,’tempdb’)
end
else
select ‘ALL the databases are in FULL RECOVERY MODE – Make sure you take proper LOG BACKUPS !!’

Get the SSIS package name of the SQL Agent Job

;WITH CTE1 AS (
    SELECT 
        J.job_id
        ,JobName = J.name
        ,JS.step_id, JS.step_name, JS.command
        ,StartIndex = 
            CASE 
                WHEN JS.command LIKE '/DTS%' OR JS.command LIKE '/SQL%' OR JS.command LIKE '/ISSERVER%' THEN CHARINDEX('\',JS.command, CHARINDEX('\',JS.command) + 1) --'
                WHEN JS.command LIKE '/SERVER%' THEN CHARINDEX('"', JS.Command, CHARINDEX(' ',command, CHARINDEX(' ',command) + 1) + 1) + 1
                ELSE 0
            END
        ,EndIndex = 
            CASE 
                WHEN JS.command LIKE '/DTS%' OR JS.command LIKE '/SQL%'  OR JS.command LIKE '/ISSERVER%' 
                    THEN  CHARINDEX('"',JS.command, CHARINDEX('\',JS.command, CHARINDEX('\',JS.command) + 1)) --'
                        - CHARINDEX('\',JS.command, CHARINDEX('\',JS.command) + 1) - 1 --'
                WHEN JS.command LIKE '/SERVER%' 
                    THEN  CHARINDEX('"',command, CHARINDEX('"', JS.Command, CHARINDEX(' ',command, CHARINDEX(' ',command) + 1) + 1) + 1)
                        - CHARINDEX('"', JS.Command, CHARINDEX(' ',command, CHARINDEX(' ',command) + 1) + 1) - 1
                ELSE 0
            END
    FROM msdb.dbo.sysjobsteps JS
    INNER JOIN msdb.dbo.sysjobs J
        ON JS.job_id = J.job_id
    WHERE JS.subsystem = 'SSIS'
)    
SELECT 
    C1.job_id
    , C1.JobName
    , C1.step_id
    , C1.step_name
    , PackageFolderPath = 
        CASE 
            WHEN C1.command LIKE '/DTS%' OR C1.command LIKE '/ISSERVER%' THEN SUBSTRING(C1.command, C1.StartIndex, C1.EndIndex)
            WHEN C1.command LIKE '/SQL%' THEN '\MSDB' + SUBSTRING(C1.command, C1.StartIndex, C1.EndIndex)
            WHEN C1.command LIKE '/SERVER%' THEN '\MSDB\' + SUBSTRING(C1.command, C1.StartIndex, C1.EndIndex)
            ELSE NULL
        END
    , C1.command
FROM CTE1 C1
ORDER BY C1.job_id, C1.step_id

how to take log backup when the backup drive is full.

Recently I had a situation where the schedule net backups were failed for more than 24 hours on the AG configured servers, due to that the log drive was full and we do not have drive space to perform manual log backups to clear log file and shrink.

Then we tried below log command to take backup to NUL device, but it will break your log chain. so I recommend after performing your admin work with this trick take a full backup and run your regular log backup schedule.

BACKUP LOG yourdb_name TO DISK = ‘NUL’

The below script will help you to verify the log chain, how this backup disturbed the log chain.

/* To check Transactional log chain information===================== */
SELECT /* Columns for retrieving information */
       -- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME, 
       msdb.dbo.backupset.database_name,
       msdb.dbo.backupset.backup_start_date,
       msdb.dbo.backupset.backup_finish_date,
       -- msdb.dbo.backupset.expiration_date, 

       CASE msdb.dbo.backupset.type
            WHEN 'D' THEN 'Full'
            WHEN 'I' THEN 'Diff'
            WHEN 'L' THEN 'Log'
       END  AS backup_type,
       -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],  
       msdb.dbo.backupmediafamily.logical_device_name,
       msdb.dbo.backupmediafamily.physical_device_name,
       -- msdb.dbo.backupset.name AS backupset_name,
       -- msdb.dbo.backupset.description,
       msdb.dbo.backupset.is_copy_only,
       msdb.dbo.backupset.is_snapshot,
       msdb.dbo.backupset.checkpoint_lsn,
       msdb.dbo.backupset.database_backup_lsn,
       msdb.dbo.backupset.differential_base_lsn,
       msdb.dbo.backupset.first_lsn,
       msdb.dbo.backupset.fork_point_lsn,
       msdb.dbo.backupset.last_lsn
FROM   msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset
            ON  msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 

        /* ----------------------------------------------------------------------------
        Generic WHERE statement to simplify selection of more WHEREs    
        -------------------------------------------------------------------------------*/
WHERE  1 = 1

       /* ----------------------------------------------------------------------------
       WHERE statement to find Device Backups with '{' and date n days back
       ------------------------------------------------------------------------------- */
       -- AND     physical_device_name LIKE '{%'

       /* -------------------------------------------------------------------------------
       WHERE statement to find Backups saved in standard directories, msdb.dbo.backupfile AS b 
       ---------------------------------------------------------------------------------- */
       -- AND     physical_device_name  LIKE '[fF]:%'                          -- STANDARD F: Backup Directory
       -- AND     physical_device_name  NOT LIKE '[nN]:%'                      -- STANDARD N: Backup Directory

       -- AND     physical_device_name  NOT LIKE '{%'                          -- Outstanding Analysis
       -- AND     physical_device_name  NOT LIKE '%$\Sharepoint$\%' ESCAPE '$' -- Sharepoint Backs up to Share
       -- AND     backupset_name NOT LIKE '%Galaxy%'                           -- CommVault Sympana Backup


       /* -------------------------------------------------------------------------------
       WHERE Statement to find backup information for a certain period of time, msdb.dbo.backupset AS b 
       ---------------------------------------------------------------------------------- 
       AND    (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  -- 7 days old or younger
       AND    (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) <= GETDATE())  -- n days old or older

       */

       /* -------------------------------------------------------------------------------
       WHERE Statement to find backup information for (a) given database(s) 
       ---------------------------------------------------------------------------------- */
       AND database_name IN ('AdventureWorks2012') -- database names
       -- AND     database_name IN ('rtc')  -- database names

        /* -------------------------------------------------------------------------------
        ORDER Clause for other statements
        ---------------------------------------------------------------------------------- */
        --ORDER BY        msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date -- order clause

        ---WHERE msdb..backupset.type = 'I' OR  msdb..backupset.type = 'D'
ORDER BY
       --2,

       2       DESC,
       3       DESC