-- shell for SYSLOG7 upgrade
IF EXISTS(SELECT index_id FROM sys.indexes WHERE object_id = OBJECT_ID(N'Event') AND [name] = N'IX_Event_Target')
BEGIN
	DROP INDEX IX_Event_Target ON [dbo].[Event]
END
	CREATE NONCLUSTERED INDEX [IX_Event_Target] ON [dbo].[Event] 
	(
		[Target] ASC
	)
GO

if exists (select * from sysobjects where name='ChkTempSpace' and  xtype = 'P')
DROP PROCEDURE ChkTempSpace
GO

--*****************************************************************************
-- PROCEDURE: ChkTempSpace
--
-- NOTES:
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Common/StoredProcedures/02.ChkTempSpace.sql#2 $
--
--*****************************************************************************

/*************************************************************************************************
Determines if sufficient drive space remains for tempdb to load the largest
table within an ION_Data, ION_Network or ION_SystemLog database. Use DataLog2 if ION_Data, 
TS_Triples if ION_Network, or Event if ION_SystemLog

Any task that requires loading database tables into SQL system database tempdb
will fail if tempdb cannot get about 10-20% larger than the largest table
tempdb is set to unrestricted size, so the real limit is determined by how large it is
and how much space remains on the hard drive.
*************************************************************************************************/

CREATE PROCEDURE ChkTempSpace
AS
SET NOCOUNT ON

DECLARE @TableSizeInMB NVARCHAR(255)
DECLARE @Drive NVARCHAR(1024)
DECLARE @tempSize NVARCHAR(255)
DECLARE @DriveSpace INT
DECLARE @DrvSp FLOAT

--get largest table size in MB + 15% buffer
CREATE TABLE #T1
(
	tablename varchar (255) COLLATE database_default,
 	numRows varchar(255) COLLATE database_default,
	reservedKB varchar(255) COLLATE database_default,
	dataKB varchar(255) COLLATE database_default,
	indexKB varchar(255) COLLATE database_default,
	unused varchar(255) COLLATE database_default
)
-- use check for largest table to make appropriate choice given database
IF EXISTS (SELECT name from sysobjects WHERE name = 'DataLog2')
BEGIN
INSERT INTO #T1 EXEC('sp_spaceused Datalog2')
END
IF EXISTS (SELECT name from sysobjects WHERE name = 'TS_Triples')
BEGIN
INSERT INTO #T1 EXEC('sp_spaceused TS_Triples')
END
IF EXISTS (SELECT name from sysobjects WHERE name = 'Event')
BEGIN
INSERT INTO #T1 EXEC('sp_spaceused Event')
END

SELECT @TableSizeInMB= reservedKB FROM #T1
Set @TableSizeInMB = (REPLACE(@TableSizeInMB,'KB','')/1024)*1.15
DROP TABLE #T1
--PRINT 'tablesize '+@TableSizeInMB

--get drive tempdb resides on and tempdev size
CREATE TABLE #T2
(
	_name varchar (255) COLLATE database_default,
 	file_location varchar(255) COLLATE database_default,
	_group varchar(255) COLLATE database_default,
	_size varchar(255) COLLATE database_default,
	_maxSize varchar(255) COLLATE database_default,
	_growth varchar(255) COLLATE database_default,
	usage varchar(255) COLLATE database_default
)
INSERT INTO #T2 EXEC('tempdb..sp_helpfile tempdev')
SELECT @Drive = file_location from #T2
Set @Drive = Left(@Drive,1)
SELECT @tempSize = _size from #T2
Set @tempSize = (REPLACE(@tempSize,'KB','')/1024)
DROP TABLE #T2
--PRINT 'tempdb drive '+@Drive
--PRINT 'tempdb size '+@tempSize

-- Use xp_fixeddrives extended procedure to determine the remaining space on drive, in MB
CREATE TABLE #T3(DrvLetter NVARCHAR(5) COLLATE database_default, DrvSpace INT)
INSERT INTO #T3 EXEC master.dbo.xp_fixeddrives
SELECT @DriveSpace = DrvSpace from #T3 where DrvLetter = @Drive
DROP TABLE #T3
Set @DrvSp = @DriveSpace
--PRINT @DrvSp
--if largest table > than space remaining +tempdb size then indexing and updating statistics will likely fill drive
If (@TableSizeInMB > @DrvSp+@tempSize)
	BEGIN
		DECLARE @msg NVARCHAR(255)
		Set @msg = 'Insufficient space is available on '+ @Drive +': drive to execute the requested task. The system database tempdb must be able to grow to ' +@TableSizeInMB+ ' MB to complete the requested task.'
		RAISERROR (@msg,16, 1)
	END

RETURN 0
GO
