Monitor disk space

/** This script checks the current space of the disks and if the available space is lower that the threshold (GB in this case) sends out email**/


use MyAdminDB
GO

declare @from varchar(100),
@to varchar(200),
@subject varchar(100),
@threshold int -- number of MB under which to launch an alert
　
　
　
SET NOCOUNT ON

DECLARE @list nvarchar(2000) = '';

WITH core AS (
　
　
SELECT DISTINCT

　
　
s.volume_mount_point [Drive],
　
　
CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]
　
　
FROM
　
　
sys.master_files f
　
　
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
　
　
)
　
　
　
　
　
　
　
　
　
SELECT @list = @list + ' ' + Drive + ', current space available =' + cast(cast(round([AvailableMBs]*1.000/1024.000,2) as numeric(36,2)) as varchar) + 'GB'
　
　
FROM core
　
　
WHERE AvailableMBs < @threshold
　
　
IF LEN(@list) > 3 BEGIN
　
　
DECLARE @msg varchar(500) = 'Disk Space les than 8GB Notification. The following drives are currently reporting less than '
　
　
+ CAST(@threshold/1024 as varchar(12)) + ' GB free: ' + @list
　
　
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile@mydomain.com',
@recipients = @to,
@subject = @subject,
@body = @msg
END

RETURN 0
　
　
　
　
　
GO
　
　
　
　
　
　
　
　
　

Diskspace analysis, database growth (yearly)

I am using the following  query to forecast the yearly database growth


select cast(MAX(Space_Used_in_MB)as float) as max_size , min(Space_Used_in_MB) AS MIN_SIZE ,

cast((MAX(Space_Used_in_MB) - MIN(Space_Used_in_MB)) as decimal(18,2))/ MIN(Space_Used_in_MB) * 100 as [yearly percent growth] ,

Database_Name from SQLMonitor..SQLAdmin_Monitor_db_file_info

where FILE_ID = 1

and Time_collected >='01/13/2014'

group by Database_Name

order by database_name


--SQLMonitor is a user database
--SQLAdmin_Monitor_db_file_info  is a user table where a job ( code here) logs the database file sixe in every four hours

-- if the time frame you want to measure is two years change the time_collected to '01/13/2013'  and change the yearly percent growth calculation as follows

( cast((MAX(Space_Used_in_MB) - MIN(Space_Used_in_MB)) as decimal(18,2))/ MIN(Space_Used_in_MB) * 100 )/2

Growth of database ( http://www.sqlservercentral.com/articles/Administration/analyzingdiskcapacityforsqlserver/2467/)

Database growth can be of three types:

1. linear growth (grows at a constant amount over time)
2. compound growth (grows at a constant rate for a specific period of time)
3. geometric growth (grows periodic by some increment, which is in turn growing at a constant rate)

Below are the formulae for different type of growths:
Linear: Current disk space + (growth * No of periods)
Compound: Current disk space * (1 + Growth %) ^ No of periods
Geometric: Current growth + [initial increment * {1-Incrementgrowth rate^ (No of period+1)}]/ (1 – growth rate)
To illustrate the fact, let us take one example. Say for, you have a database and it is of 100 GB. If it grows at 10% per year, then after 2 years, it will be 100 + (10 * 2) = 120 GB (linear growth).
If it is so that the database grows at 5% rate/month for 2 years, then it will be
100 * (1 + 0.05) ^ 24 = 322.5 GB (compound growth).
Take another case. Say for you have already a database of 100GB. Now the growth rate is so that it starts at 10 GB/month and the increment itself increments at 5%/month, then in 24 months, it will be 100 + {(10 * (1- (1.05 ^ (24 + 1))) / (1-1.05)} = 100 + 477 = 577 GB (geometric growth).

Removal of unused database to free up space.

I am trying to free up some space in the server by detaching the unused database and deleting the ldf & mdf files. Here are the steps I took
1. Performed a full back up of the database
2. Moved the BAK file to a different location in another server
3. Ran the follwing on the database since it was a publisher in the replication
sp_replicationdboption 'dbcentral,'publish','false'
4.Detach the database
5. Restore the db from the bakupfile somewhere to make sure that the back up is good.
6. Delete the files since I have a full back up.