Tag Archives: case

SQL Version

All_The_Basics

Basic Server Info to obtain SQL Version and OS Version. 


/*
Basic Info
*/

SELECT
SERVERPROPERTY('ServerName')	 'SQLServerName',
COALESCE(SERVERPROPERTY('InstanceName'),'DEFAULT') 'InstanceName',
SERVERPROPERTY('ProductVersion') 'ProductVersion',
SERVERPROPERTY('ProductLevel')   'ProductLevel',
SERVERPROPERTY('Edition')		 'Edition',
CASE 
WHEN RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.2 then 'Windows 2012'
WHEN RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.1 then 'Windows 2008R2'
WHEN RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.0 then 'Windows 2008'
WHEN RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.2 then 'Windows 2003'
WHEN RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.1 then 'Windows XP'
WHEN RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.0 then 'Windows 2000'

ELSE RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)
END	'Windows Version'

Example

SQLVersion

Data File Size and Free Space – One Database

InsideDatabases

This script lists file size and how full the data files are for a single Database.

  

/*
DESCRIPTION    List space on a single database and location of its files

CONFIGURATION    Top. Modify the "use" statement
 
Compatibility list:
MSSQL2005
MSSQL2008
 
 
*/


USE Adventure_Works_2012

SELECT  
b.name
, b.type_desc
, CAST(b.size/128.0 AS int) 'SizeMB'  
, CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, 'SpaceUsed' ) AS int)/128.0 AS int) 'SpaceUsedMB'  
, CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,'SpaceUsed' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + '%'  'FreeSpace'

,  CASE
	WHEN b.is_percent_growth = 0 THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + ' MB, '  
    ELSE 'By ' + CAST(b.growth AS VARCHAR) + ' percent, '
 END
 + CASE  
    WHEN b.max_size = -1 THEN 'unrestricted growth'
	ELSE 'restricted growth to ' + LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + ' MB'  
    END AS Autogrow
, cast(b.max_size/128.0 as int) 'MaxSize'
, physical_name
 
FROM sys.database_files b

Database Size Totals

All_The_Basics, Reports, Space-Issues

Very simple script that list the total size of a database and its transaction log.

If you need for SQL 2000 click here

  



/*
DESCRIPTION    List space used on databases
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2012
MSSQL2008
MSSQL2005

DOES NOT WORK
MSSQL2000

 
*/


SELECT
    SERVERPROPERTY('SERVERNAME') ServerName,
    SERVERPROPERTY('IsClustered') IsCluster,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    case 
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.3 then 'Windows 2012R2'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.2 then 'Windows 2012'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.1 then 'Windows 2008R2'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.0 then 'Windows 2008'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.2 then 'Windows 2003'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.1 then 'Windows XP'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.0 then 'Windows 2000'

else RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)
end,
    DB_NAME(db.database_id) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB
FROM sys.databases db
    LEFT JOIN (
    SELECT database_id, SUM(size) RowSize 
    FROM sys.master_files 
    WHERE type = 0 
    GROUP BY database_id, type
				) mfrows ON mfrows.database_id = db.database_id
    
    LEFT JOIN (
    SELECT database_id, SUM(size) LogSize 
    FROM sys.master_files 
    WHERE type = 1 
    GROUP BY database_id, type
				) mflog ON mflog.database_id = db.database_id
WHERE
	db.database_id > 4

 

  For SQL 2000 


/*
DESCRIPTION    List space used on databases
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2000

 
*/

SELECT
    SERVERPROPERTY('SERVERNAME') ServerName,
    SERVERPROPERTY('IsClustered') IsCluster,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    case 
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.3 then 'Windows 2012R2'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.2 then 'Windows 2012'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.1 then 'Windows 2008R2'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.0 then 'Windows 2008'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.2 then 'Windows 2003'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.1 then 'Windows XP'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.0 then 'Windows 2000'

else RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)
end,
    DB_NAME(db.dbid) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB
FROM sysdatabases db
    LEFT JOIN (
    SELECT dbid, SUM(size) RowSize 
    FROM master..sysaltfiles
    WHERE groupID = 1 
    GROUP BY dbid, groupID
				) mfrows ON mfrows.dbid = db.dbid
    
    LEFT JOIN (
    SELECT dbid, SUM(size) LogSize 
    FROM master..sysaltfiles
    WHERE groupID <> 1
    GROUP BY dbid, groupID
				) mflog ON mflog.dbid = db.dbid
WHERE
	db.dbid > 4

Find If Mix Backup Types are Present

Backups, Reports, ,

There are mainly two types of backups:

  • Native SQL backups (either stored to local disk or a network location)
  • API backups, where software such as TSM/TDP or NetBackup, or CommVault, SQLLite, etc

There could be problems recovering a database if a server has different types of backups configured, unbeknown to the DBA. Per example, if TDP is used, and another DBA takes a local disk backup (out of band backup), it could pose a problem when we are restoring Tlog backups. This script identifies this situation, and lists those databases where different types of backups have been performed. If none are found, the result is blank

  


/*
DESCRIPTION
Queries the location of the last X days of databases backups
and reviews its location: API, DiskBackup or NetworkLocation

If backup location has changed in the last X days,
it returns those databases
If no change in location n the last X days,
it returns empty.

CONFIGURATION Set @days for the number of days to search for backups
Set @dbname for the specific database; leave NULL for all
 
Compatibility list:
MSSQL2012 MSSQL2005 MSSQL2008 MSSQL2000  
*/

 
DECLARE @days INT
DECLARE @dbname sysname
 
SET @days = 7			-- Number of days. Limited by the history stored on MSDB
SET @dbname = NULL		-- Specify database like 'database1' . If Null it will do all
 
 
 
 ---- End of configuration
 
declare @BackupLocation table
(i int identity(1,1) ,db varchar(1000) , bk_type varchar(10), bk_Loc varchar(3))

insert into @BackupLocation 

SELECT
 bs.database_name
, CASE bs.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'TLog'
END AS backup_type
, left(bmf.physical_device_name,3)
 
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE 1=1
and bmf.family_sequence_number = '1'
and (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - @days)
--and bs.type = 'D'
and bs.database_name like coalesce(@dbname,'%')


ORDER BY
bs.database_name

--------------------------
declare @summary table

(i int identity(1,1) ,db varchar(1000), db2 varchar(1000) )

insert into @summary

select db, db+'_'+bk_type
 from @BackupLocation 

group by bk_loc, db, bk_type
order by db



 select 
 case
 when count(db2) > 1 then 'Mix Backups Found'
 else 'Only one Type of Backups'
 end as 'backups'
 , db2 from @Summary 
 
 group by db2
 having count(db2)>1 


select db,  bk_type,
case substring(bk_loc,2,1)
WHEN ':' THEN 'LOCAL DISK'
WHEN '\' THEN 'NETWORK LOCATION'  --'
ELSE 'API BACKUPS'
end as 'Type of Backups'

 from @BackupLocation 

where db in
(select db from @Summary 
 group by db2, db
 having count(db2)>1 
 )
 
group by bk_loc, db, bk_type
order by db

Thank you Andrey for the idea to create this script