SQL Resource

T-SQL to Get Drive Space Info

2012-01-18T17:02:00.000Z

To create a new script to fetch drive space info from SQL Server. Used powershell to create this script.

Pre-requisite: Enable XP_CMDSHELL and Powershell 1.0

DECLARE @psinfo TABLE(data NVARCHAR(100)) ;
INSERT INTO @psinfo
EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID, FreeSpace, Size"' ;
DELETE FROM @psinfo WHERE data is null or data like '%DeviceID%' or data like '%----%';
update @psinfo set data = REPLACE(data,' ',',');

;With DriveSpace as (

select SUBSTRING(data,1,2) as [Drive],
replace((left((substring(data,(patindex('%[0-9]%',data)) , len(data))),CHARINDEX(',',
(substring(data,(patindex('%[0-9]%',data)) , len(data))))-1)),',','')
as [FreeSpace]
,
replace(right((substring(data,(patindex('%[0-9]%',data)) , len(data))),PATINDEX('%,%',
(substring(data,(patindex('%[0-9]%',data)) , len(data))))) ,',','')
as [Size]
from @psinfo
)
SELECT Drive, convert(dec( 6,2),CONVERT(dec(17,2),FreeSpace)/(1024*1024*1024)) as FreeSpaceGB, convert(dec( 6,2),CONVERT(dec(17,2), size)/(1024*1024*1024)) as SizeGB
FROM DriveSpace;

I have tested this code on SQL Server 2008 R2 , SQL Server 2008 and SQL Server 2005.
Leave you feedback for encouragement.

SQL Server Database Size Info

2012-01-17T23:55:00.000Z

This code is to get Database size info in one recordset.

Declare @FileSpace table (
[database_id] int,
[db_name] nvarchar(100),
[file_id] int,
[file_name] nvarchar(100),
[space_used] int
)
INSERT INTO @FileSpace EXEC sp_MSforeachdb 'USE ? SELECT db_id(''?''),''?'' as DBname,fileid, name ,FILEPROPERTY(name, ''SpaceUsed'') as spaceused from sys.sysfiles';

With SpaceInfo (DBName, FileName, FileType,FilePath,CurrentSizeMB,SpaceUsedMB)
as
(

SELECT
DB_NAME(df.[database_id]) as [DBName],
df.[name] as [FileName] ,
df.type_desc as [FileType],
df.physical_name as [FilePath],
(df.[size]*8.0)/1024.0 as [CurrentSizeMB],
(fs.[space_used]*8.0)/1024.0 as [SpaceUsedMB]
FROM
SYS.MASTER_FILES AS df
INNER JOIN @FileSpace as fs on (df.[database_id]= FS.[database_id] and df.[file_id]=fs.[file_id])
)
SELECT
SI.DBName,
convert(dec(17,2),SUM(SI.CurrentSizeMB)) as [DatabaseSizeMB],
convert(dec(4,2),(1-(SUM(SI.SpaceUsedMB)/SUM(SI.CurrentSizeMB)))*100 )as [%age Free],
convert(dec(17,2),D.DataFileSizeMB) as DataFileSizeMB,
convert(dec(4,2) ,(1-(D.DataFileSpaceUsedMB/D.DataFileSizeMB))*100) as [%age Free],
convert(dec(17,2),L.LogFileSizeMB) as LogFileSizeMB,
convert(dec(4,2),(1-(L.LogFileSpaceUsedMB/L.LogFileSizeMB))*100) as [%age Free]
FROM
SpaceInfo SI
CROSS APPLY (SELECT sum(CurrentSizeMB) as [DataFileSizeMB], SUM(SpaceUsedMB) as [DataFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype='ROWS' and DBName=SI.DBName group by DBName) as D
CROSS APPLY (SELECT sum(CurrentSizeMB) as [LogFileSizeMB], SUM(SpaceUsedMB) as [LogFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype ='LOG' and DBName=SI.DBName group by DBName) as L
GROUP BY
SI.DBName, D.DataFileSizeMB,D.DataFileSpaceUsedMB,L.LogFileSizeMB,L.LogFileSpaceUsedMB

SQL Server Database Backup Info

2012-01-17T12:06:00.000Z

There are two scripts one for getting only backup dates of each database and other one also fetch backup size.

First ,

Use [Master]

select

S.name as [DatabaseName],

S.recovery_model_desc as [RecoveryModel],

S.state_desc [DatabaseState],

S.create_date as [DatabaseCreatedDate],

b.[Last_Full_Backup_Date] as [LastFullBackupDate],

L.Last_Log_Backup_Date as [LastLogBackupDate],

I.[Last_Diff_Backup_Date] as [LastDiffBackupDate]

from

sys.databases S

left outer join

(select database_name, max(backup_finish_date) as [Last_Full_Backup_Date] from msdb.dbo.backupset

Where type='D'

group by database_name) b on ( s.name =b.database_name)

Left outer join

(select database_name, max(backup_finish_date) as [Last_Log_Backup_Date] from msdb.dbo.backupset

Where type='L'

group by database_name) L on ( s.name =L.database_name)

Left outer join

(select database_name, max(backup_finish_date) as [Last_Diff_Backup_Date] from msdb.dbo.backupset

Where type='I'

group by database_name) I on ( s.name =I.database_name)

order by

s.name

Second,

Use [Master]

select

S.name as [DatabaseName],

S.recovery_model_desc as [RecoveryModel],

S.state_desc [DatabaseState],

S.create_date as [DatabaseCreatedDate],

F.[Last_Full_Backup_Date] as [LastFullBackupDate],

F.backup_size,

L.Last_Log_Backup_Date as [LastLogBackupDate],

L.backup_size as [LogBackupSize],

I.[Last_Diff_Backup_Date] as [LastDiffBackupDate],

I.backup_size as [DiffBackupSize]

from

sys.databases S

outer apply

(select top 1 database_name, backup_finish_date as [Last_Full_Backup_Date] , backup_size from msdb.dbo.backupset

Where type='D' and database_name =s.name order by backup_finish_date desc ) F

outer apply

(select top 1 database_name, backup_finish_date as [Last_Log_Backup_Date] , backup_size from msdb.dbo.backupset

Where type='L' and database_name =s.name order by backup_finish_date desc ) L

outer apply

(select top 1 database_name, backup_finish_date as [Last_Diff_Backup_Date] , backup_size from msdb.dbo.backupset

Where type='I' and database_name =s.name order by backup_finish_date desc ) I

order by

s.name

Information on Backups

2011-10-19T20:58:00.001+01:00

To check last backup details of all the databases use the following script. This script works on both SQL Server 2005 and SQL Server 2008

Select

db.name as 'Database_Name',

db.recovery_model_desc as 'Recovery_Model',

db.state_desc as 'State',

FB.LastBackupDate as 'LastFullBackup',

lB.LastBackupDate as 'LastLogBackup' ,

DIF.LastBackupDate as 'LastDiffBackup'

From sys.databases db

Cross Apply (Select max(backup_finish_date) as LastBackupDate from msdb.dbo.backupset where type= 'D' and database_name= db.name) FB

Cross Apply (Select max(backup_finish_date) as LastBackupDate from msdb.dbo.backupset where type= 'L' and database_name= db.name) LB

Cross Apply (Select max(backup_finish_date) as LastBackupDate from msdb.dbo.backupset where type= 'I' and database_name= db.name) dif

Order by

db.name

Checking For Fragmented Indexes

2010-06-29T16:07:00.001+01:00

The most common issue related to SQL Server performance is bad maintenance of indexes. Indexes are the most powerful object in SQL server to speed up data retrieval. Below is script that identifies all the indexes in a particular database, where index fragmentation level is above 29%. Microsoft recommends rebuilding indexes which are fragmented over 30%.

SELECT

a.index_id, object_name(a.object_id) as Table_name, name, index_type_desc,

avg_fragmentation_in_percent

FROM

sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a

JOIN sys.indexes AS b ON a.object_id = b.object_id AND

a.index_id = b.index_id

Where

avg_fragmentation_in_percent> 29 and

object_name(a.object_id) not like 'sys%'

Order by

avg_fragmentation_in_percent desc ;

This query will fetch all the indexes on the user database that needs rebuilding.

Feel free to provide feedback as I am new to blogging, but I would like to contribute to the community as much as possible.