SQL Server Query for Capacity Planning – SQL 2005 & 2008

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 26-12-2010

14

As a proactive DBA its one of responsibility to keep an eye on database’s growth and other file system changes for the databases hosted on SQL instance. However being busy on daily DBA tasks, its not always possible to prepare scripts or manually extract data from system for documentations & trend reporting purposes. I faced the similar challenge while working with large DB systems and came up with a T-SQL script that can do the needful without worries..

Setup a low disk space alert for SQL Server drives

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev, Uncategorized | Posted on 21-12-2010

2

This is the pretty easy and clean script that you may use to check periodically your database server drive space. You can create a job to run on hourly basis to execute this code, along with a database mail code to send you alert if  any of drive  has less than 15% or 20% threshold value.

-- Create a global temp table
CREATE TABLE ##space( dletter varchar(3), tspace BIGINT, fspace int, percentfree numeric(5,2))
-- Insert drive details
INSERT INTO ##space (dletter, fspace) EXEC master.dbo.xp_fixeddrives
-- Declare variables
DECLARE   @oFSO   INT, @oDrive INT, @drsize VARCHAR(255), @ret   INT
-- invoke OACreate
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT
DECLARE @dletter VARCHAR(3), @fspace INT, @tspace BIGINT
while (select count(*) from  ##space where tspace is null)>0
begin
   select top 1 @dletter = dletter  + ':\',@fspace = fspace from ##space where tspace is null
   EXEC   @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
   EXEC   @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
   UPDATE   ##space SET   tspace = CAST(@drsize AS BIGINT) WHERE   lower(dletter) + ':\'   = lower(@dletter)
   EXEC master.dbo.sp_OADestroy @oDrive
end
EXEC master.dbo.sp_OADestroy @oFSO
update   ##space set   percentfree = fspace/((tspace/1024.0)/1024.0)*100 
-- Select your data
select [Drive] = dletter ,
         [Total Space GB]= convert(numeric(10,3), (tspace/1024.0)/1024.0/1024) ,
         [Free Space GB]=convert(numeric(10,3),fspace/1024.0) ,
         [% Free]= percentfree 
         from   ##space
-- Drop temporary table
drop table ##space

Script to find used and free space in Database files

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 02-08-2010

2

In an Enterprise world, you may be responsible to monitor 1000+ databases hosted on hundreds of SQL Server instances. On a lazy afternoon, suddenly low disk space alarm hit your inbox. What will you do, storage can not be added or expended on the fly. Here is the tested script to find used and free space from SQL Server database’s data files that you can shrink to easy the current situation.

-- Find Log file information and save in temp file using dbcc sqlperf logspace
CREATE TABLE #LogSpace (DatabaseName varchar(40), LogSize int, LogSpace int, status bit)
INSERT #LogSpace exec ('dbcc sqlperf(logspace) WITH NO_INFOMSGS') 
 
-- Find data file information using cursor and dbcc showfilestats
Declare @DatabaseName varchar(500)
create table #DBSpace(FielId tinyint, Filegroup tinyint, TotalSpace int, 
Used_Space int, Name1 varchar(25),NameofFile Varchar(900) )
Declare curDB cursor for select name from master..sysdatabases 
open curDB
fetch curDB into @DatabaseName
while @@fetch_status = 0
begin
    if databasepropertyex(@DatabaseName,'Status') = 'ONLINE'
    begin
    insert into #DBSpace exec ('USE [' + @DatabaseName + ']  DBCC SHOWFILESTATS WITH NO_INFOMSGS')
    end
    fetch curDB into @DatabaseName
end
close curDB
deallocate curDB 
 
-- Select data in tabular format with proper headings & order by
select left(sd.name,30) AS 'DatabaseName',
(ff.[DataFileSpace(MB)])+ls.LogSize as 'DatabaseSize(MB)',ff.[DataFileSpace(MB)],
ff.[DataFileUsedSpace(MB)], ff.[DataFileFreeSpace(MB)],
ls.LogSize as 'LogFileSize(MB)', ls.LogSpace as'LogFileSpaceUsedIn(%)'
from #DBSpace dbs join master..sysdatabases sd on sd.filename=dbs.NameofFile
join #LogSpace ls on sd.name=ls.DatabaseName
join
(select  sf.dbid, sum(dbss.TotalSpace/16) as 'DataFileSpace(MB)', 
sum(dbss.Used_Space/16) as 'DataFileUsedSpace(MB)', 
(sum(dbss.TotalSpace/16)- sum(dbss.Used_Space/16)) 
as 'DataFileFreeSpace(MB)' from #DBSpace dbss
join master.sys.sysaltfiles sf on rtrim(sf.filename)= rtrim(dbss.NameofFile)
group by sf.dbid) ff on ff.dbid=sd.dbid
order by 'DataFileFreeSpace(MB)' desc 
 
-- Drop temporary tables manually
Drop table #DBSpace
DROP TABLE #LogSpace 
© 2010 SQLKnowledge.com Increase your website traffic with Attracta.com