DBA,  Junior DBA

Drive space check SQL server steps and scripts

Following is the script used for drive space related issues.

--SQL 2000
-- select name,(size/128.0) as size_mb,* from master.dbo.sysaltfiles order by size_mb desc

-- To check the drive free space
xp_fixeddrives

------------------------------- get all the db file info & filter the drive, mdf/ldf
select db_name(a.database_id) as DBname,a.name as DBfile ,
size/128.0 AS CurrentSizeMB,
--size/128.0 - ((size/128.0) - CAST(FILEPROPERTY(a.name, 'SpaceUsed') AS INT)/128.0) AS UsedSpaceMB,
--size/128.0 - CAST(FILEPROPERTY(a.name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB,
b.recovery_model_desc,a.type_desc ,
CASE WHEN is_percent_growth = 0
THEN LTRIM(STR(a.growth * 8.0 / 1024,10,1)) + ' MB, '
ELSE 'By ' + CAST(a.growth AS VARCHAR) + ' percent, 'END +
CASE WHEN max_size = -1 THEN 'unrestricted growth'
ELSE 'restricted growth to ' +LTRIM(STR(max_size * 8.0 / 1024,10,1)) + ' MB'
END AS Autogrow,a.physical_name
from sys.master_files a join sys.databases b
on a.database_id =b.database_id
--where a.physical_name like 'e%'
--and a.type_desc ='LOG'
order by a.size/128.0 desc

------------------------------- get the total, used and free space for particular DB
use Dbname
go
SELECT DB_NAME() AS DBNAME,
NAME AS FILENAME,
SIZE/128.0 AS CURRENTSIZE_MB,
SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0) AS USEDSPACE_MB,
SIZE/128.0 - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0 AS FREESPACEMB,
PHYSICAL_NAME,DATABASEPROPERTYEX (DB_NAME(),'RECOVERY') AS RECOVERY_MODEL,TYPE_DESC,
CASE WHEN IS_PERCENT_GROWTH = 0
THEN LTRIM(STR(GROWTH * 8.0 / 1024,10,1)) + ' MB, '
ELSE 'BY ' + CAST(GROWTH AS VARCHAR) + ' PERCENT, 'END +
CASE WHEN MAX_SIZE = -1 THEN 'UNRESTRICTED GROWTH'
ELSE 'RESTRICTED GROWTH TO ' +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ' MB'
END AS AUTO_GROW
FROM SYS.MASTER_FILES
WHERE DATABASE_ID = DB_ID()
--AND type_desc ='LOG'
--AND PHYSICAL_NAME like 'i%'
------------------------------
-- check the wait for transaction log file growth
select log_reuse_wait_desc,* from sys.databases -- where name like '%%'

Dbcc shrinkfile (logical log filename)
-- Solution: Shrink log file, move any unwanted files/backups or ask windows to increase the space for drive.
------------------------------

-- olny for file groups
SELECT DB_NAME() AS DBNAME,
MF.NAME AS FILENAME, sf.name AS [File_Group_Name],
SIZE/128.0 AS CURRENTSIZE_MB,
SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(mf.NAME, 'SPACEUSED') AS INT)/128.0) AS USEDSPACE_MB,
SIZE/128.0 - CAST(FILEPROPERTY(mf.NAME, 'SPACEUSED') AS INT)/128.0 AS FREESPACEMB,
PHYSICAL_NAME,DATABASEPROPERTYEX (DB_NAME(),'RECOVERY') AS RECOVERY_MODEL,mf.TYPE_DESC,
CASE WHEN IS_PERCENT_GROWTH = 0
THEN LTRIM(STR(GROWTH * 8.0 / 1024,10,1)) + ' MB, '
ELSE 'BY ' + CAST(GROWTH AS VARCHAR) + ' PERCENT, 'END +
CASE WHEN MAX_SIZE = -1 THEN 'UNRESTRICTED GROWTH'
ELSE 'RESTRICTED GROWTH TO ' +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ' MB'
END AS AUTO_GROW
FROM SYS.MASTER_FILES MF join sys.filegroups SF on MF.data_space_id =SF.data_space_id
WHERE DATABASE_ID = DB_ID()
order by CURRENTSIZE_MB desc

-------------------------------
--Error log checking, For any ticket generated
------------------------------

sp_readerrorlog 0,1,'is full'
sp_readerrorlog 0,1,'out of space'
--------------------------

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: