SQLDEBACLE DOT COM SQL, Business Intelligence, NoSQL

CREATE proc dbo.dba_startup
AS
SET NOCOUNT ON;
DECLARE @hostName VARCHAR(100)
DECLARE @tbl TABLE
(
      hostName VARCHAR(100)
)
 
INSERT INTO @tbl
EXEC xp_cmdshell 'hostname'
 
DELETE FROM @tbl
WHERE hostName IS NULL
 
 
 
 
SELECT @hostname = hostname FROM @tbl
 
--SELECT @hostName = HOST_NAME()
 
IF @hostName = 'Node1'
BEGIN
 
            EXEC sys.sp_configure 'show advanced options', 1;
            RECONFIGURE
            EXEC sys.sp_configure 'max server memory', 20000;
            RECONFIGURE
            EXEC sys.sp_configure 'min server memory', 20000;
            RECONFIGURE
            EXEC sys.sp_configure 'show advanced options', 0;
            RECONFIGURE
 
 
END
IF @hostName = 'Node2'
BEGIN
      EXEC sys.sp_configure 'show advanced options', 1;
      RECONFIGURE
      EXEC sys.sp_configure 'max server memory', 2147483647;
      RECONFIGURE
      EXEC sys.sp_configure 'min server memory', 0;
      RECONFIGURE
      EXEC sys.sp_configure 'show advanced options', 0;
      RECONFIGURE
END
 
 
SET NOCOUNT OFF;
 
 
--Configuring this as startup stored proc
EXEC dbo.sp_procoption N'dba_startup', 'startup', 'on'

USE [DATABASE]
GO
 
CREATE TABLE [dbo].[DatabaseSpace](
	[DatabaseSpaceID] [INT] IDENTITY(1,1) NOT NULL,
	[ServerName] [VARCHAR](100) NULL,
	[DatabaseName] [VARCHAR](100) NULL,
	[FileSizeMB] [INT] NULL,
	[LogicalFileName] [sysname] NOT NULL,
	[PhysicalFileName] [nvarchar](520) NULL,
	[STATUS] [sysname] NOT NULL,
	[Updateability] [sysname] NOT NULL,
	[RecoveryMode] [sysname] NOT NULL,
	[FreeSpaceMB] [INT] NULL,
	[FreeSpacePct] [VARCHAR](7) NULL,
	[FreeSpacePages] [INT] NULL,
	[PollDate] [datetime] NULL,
 CONSTRAINT [PK_DatabaseSpace] PRIMARY KEY CLUSTERED 
(
	[DatabaseSpaceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--Stored proc creation. This stored prod runs everyday via SQL Server Agent Job
USE [DATABASE]
GO
/****** Object:  StoredProcedure [dbo].[dba_DatabaseSpace]    Script Date: 05/16/2011 23:18:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[dba_DatabaseSpace]
AS
SET NOCOUNT ON;
 
DECLARE @command VARCHAR(5000)  
 
SELECT @command = 'Use [' + '?' + '] SELECT  
@@servername as ServerName,  
' + '''' + '?' + '''' + ' AS DatabaseName,  
CAST(sysfiles.size/128.0 AS int) AS FileSize,  
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,  
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,  
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,  
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,  
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +  
       'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,  
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,  
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))  
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,  
GETDATE() as PollDate FROM dbo.sysfiles'  
INSERT INTO dbo.DatabaseSpace  
   (ServerName,  
   DatabaseName,  
   FileSizeMB,  
   LogicalFileName,  
   PhysicalFileName,  
   STATUS,  
   Updateability,  
   RecoveryMode,  
   FreeSpaceMB,  
   FreeSpacePct,  
   PollDate)  
EXEC sp_MSForEachDB @command  
SET NOCOUNT OFF;

--Sample Query to get the database growth between two consecutive days.
--Author: Subhash Pant
SELECT  
        a.DatabaseName ,
        a.FileSizeMB AS previousSize,
        a.LogicalFileName ,
        a.PhysicalFileName ,
        a.FreeSpaceMB ,
        a.FreeSpacePct ,
        a.PollDate,
        c.FileSizeMB AS newSize,
        c.PollDate,
        isGrowth = CASE WHEN a.FileSizeMB < c.FileSizeMB THEN 1 ELSE 0 END
FROM dbo.DatabaseSpace a 
CROSS APPLY
(
SELECT TOP 1 *
FROM dbo.DatabaseSpace b
WHERE a.PollDate < b.PollDate
AND a.DatabaseName = b.DatabaseName
AND a.LogicalFileName = b.LogicalFileName
ORDER BY b.PollDate
) c