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).

Perfmon helpful couters

Buffer cache hit ratio :

It is the percentage of data server had in the cache and did not have to read it from the disk.
Buffer cache hit ratio should be more than 90% for a SQL Server

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio

FROM sys.dm_os_performance_counters a

JOIN (SELECT cntr_value,OBJECT_NAME

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio base'

AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME

WHERE a.counter_name = 'Buffer cache hit ratio'

AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'




Page Life Expectancy (PLE) :

It is the number of seconds the data will stay in the  cache. The ideal value is 300 if less than 300 or between 300-400 the sever will require more memory.


SELECT *,cntr_value as [PLE in secs],cntr_value / 60 as [PLE in mins],

cntr_value / 3600 as [PLE in hours],

cntr_value / 86400 as [PLE in days]

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Page life expectancy'

AND OBJECT_NAME = 'SQLServer:Buffer Manager'

Reasons for database to go to SUSPECT mode

Possibility 1 : Someone deleted or misplaced a log/data file when SQL server was offline so the database could not start as it is missing a file.
Solution : Put the missing file back on the location. SQL error log will give the  exact  location of the missing file.

Possibility 2 : SQL server could not access to put an exclusive lock on the data/log file while the database was starting up . This could be caused when the data SQL server is shared with some other tool ( e.g. antivirus)
Solution: Find  the file handler that placed an exclusive lock on the data/log file and kill it. Then run the following query.

RESTORE DATABASE WITH RECOVERY

Possibility 3 : Due to a corrupted transaction. This could likely be that the SQL server went down in the middle of the transaction and while coming back it could not complete ( COMMIT/ROLLBACK) the transaction.

Solution: Switch to emergency mode and run the dbcc checkdb command

Possibility 4: Some OS/hardware failure

Batch Update : using TOP n

---do not use set rowcount.
--Use top n
WHILE (1=1)
  BEGIN
     BEGIN TRANSACTION
    UPDATE r
    SET col1= col2
    from Table1 r
    where r.col3 in (select top 10000 col3 from Table1 where col1 is null)

   -- Update 10000 nonupdated rows

   IF @@ROWCOUNT = 0
     BEGIN
       COMMIT TRANSACTION
       BREAK
    END

    COMMIT TRANSACTION
END

suspect mode: database

---once the database goes to suspect mode
--follow the steps to recover the database

--1. reset the database from suspect mode
--sp_resetstatus turns off the suspect flag on a database.
--This procedure updates the mode and status columns of the named database in sys.databases.
--Also note that only logins having sysadmin priveleges can perform this :


use master
GO
exec sp_resetstatus 'db1';
--2.set the database to emergency mode
--Once the database is set to EMERGENCY mode it becomes a READ_ONLY copy
--only members of sysadmin fixed server roles have privileges to access it.
--The basic purpose for this is to facilitate troubleshooting

alter database db1 set emergency;

--3.run the consistency test
dbcc checkdb(db1)

--4. Roll the databse to single user mode
use master
GO
ALTER DATABASE db1 SET SINGLE_USER
WITH ROLLBACK IMMEDIATE


--5. this query will attempt to repir all the reported loss. This query can cause some data loss
dbcc checkdb('db1',REPAIR_ALLOW_DATA_LOSS)

--6.finally bring the database ONLINE and set to multiuser mode
ALTER DATABASE db1 SET MULTI_USER
WITH ROLLBACK IMMEDIATE

Stored procedure with an OUTPUT parameter

CREATE procName1 (@intClientId int, @account varchar(50), @intCount int OUTPUT)
AS

SELECT @intCount = COUNT (*) FROM Clients where clientId = @intClientId

SELECT @intCount

GO


--procedure call
Declare @out int
EXECUTE procName1 999, 'ABCSDRF',@out OUTPUT
SELECT @out

Dynamic sql: executing stored procedure using a sp_executesql

I had a situation where the logic on finding the duplicate records( based on the account number match) was different on each client. I built a logic where each client had it's own stored procedure called proc_check_dup_logic and the stored procedure name used by each client was stored in a table in a central admin database. I had a wrapper stored procedure in the admin database that would find the duplicate logic stored procedure name from the table and execute that using sp_executesql.

I try to stay away from dynamic sql but this was pretty fun writing and I unerstood the parameters scope as well.



@procname is a storedrocedure variable where the name is assigned and it's a storeprocedure with an OUTPUT value.

SELECT @procname = procedurename from table1 WHERE clientId = 10

BEGIN
--call special dup check logic

DECLARE @sqlStr NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

--the follwing are the parameters in this scope

DECLARE @parmINAccount VARCHAR(30),@parmINHospId int , @ParmINpatType varchar(1),@paramINPtno int
DECLARE @parmRET1 int
--assigning the values to be passed to the storeprocedure on sp_executesql call
SET @parmINAccount = @strAccount
SET @parmINHospId = @intHospital
SET @ParmINpatType = @patType
SET @paramINPtno = @intPtno

SET @sqlStr = N'Exec ' + @procName + ' @intPtno,@strAccount,@intHospital,@patType, @outDupCheck OUTPUT'

--the following parameters name and datatype should be exactly the same as the parameters in the called storedprocedure (e.g. @procname in this case)

SET @ParmDefinition=N'@IntPtno int, @strAccount varchar(50),@intHospital int,@patType varchar(1), @outDupCheck int OUTPUT';

EXECUTE sp_executesql @sqlStr, @ParmDefinition, @IntPtno = @paramINPtno, @strAccount = @parmINAccount, @intHospital=@parmINHospId,@patType=@ParmINpatType,@outDupCheck = @parmRET1 OUTPUT
Select @outDupCheck = @parmRET1

END