Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Declare @TopSQLArg int,
- @NewSQLArg nvarchar(10)
- Declare @StartParms Table (ParmID int not null identity(1, 1) primary key,
- ParmValue nvarchar(10) not null,
- ParmData nvarchar(500) not null,
- SQLArg As Cast(Replace(ParmValue, 'SQLArg', '') As Int))
- Declare @DBCCStatus Table (TraceFlag int not null primary key,
- TFStatus int not null,
- TFGlobal int not null,
- TFSession int not null)
- Set Nocount On
- Insert Into @StartParms (ParmValue, ParmData)
- Exec xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE',
- N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
- Select @TopSQLArg = Max(SQLArg)
- From @StartParms
- If Not Exists (Select 1 From @StartParms
- Where ParmData = '-T1204')
- Begin
- Set @TopSQLArg = @TopSQLArg + 1
- Set @NewSQLArg = 'SQLArg' + Cast(@TopSQLArg as nvarchar)
- Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
- N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters',
- @NewSQLArg,
- Reg_Sz,
- N'-T1204'
- Delete From @StartParms
- Insert Into @StartParms (ParmValue, ParmData)
- Exec xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE',
- N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
- If Not Exists (Select 1 From @StartParms
- Where ParmData = '-T1204')
- Begin
- RaisError ('Startup parameter 1204 was not successfully added. Please perform manually.', 1 ,1)
- End
- Else
- Begin
- Print 'Startup parameter 1204 was successfully added.'
- End
- End
- Else
- Begin
- Print 'Startup parameter 1204 already assigned.'
- End
- If Not Exists (Select 1 From @StartParms
- Where ParmData = '-T1222')
- Begin
- Set @TopSQLArg = @TopSQLArg + 1
- Set @NewSQLArg = 'SQLArg' + Cast(@TopSQLArg as nvarchar)
- Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
- N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters',
- @NewSQLArg,
- Reg_Sz,
- N'-T1222'
- Delete From @StartParms
- Insert Into @StartParms (ParmValue, ParmData)
- Exec xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE',
- N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
- If Not Exists (Select 1 From @StartParms
- Where ParmData = '-T1222')
- Begin
- RaisError ('Startup parameter 1222 was not successfully added. Please perform manually.', 1 ,1)
- End
- Else
- Begin
- Print 'Startup parameter 1222 was successfully added.'
- End
- End
- Else
- Begin
- Print 'Startup parameter 1222 already assigned.'
- End
- Insert Into @DBCCStatus
- Exec sp_executesql N'DBCC TraceStatus With No_InfoMsgs'
- If Not Exists (Select 1 From @DBCCStatus
- Where TraceFlag = 1204
- And TFGlobal = 1
- And TFStatus = 1)
- Begin
- DBCC TraceOn (1204, -1)
- End
- If Not Exists (Select 1 From @DBCCStatus
- Where TraceFlag = 1222
- And TFGlobal = 1
- And TFStatus = 1)
- Begin
- DBCC TraceOn (1222, -1)
- End
- Delete From @DBCCStatus
- Insert Into @DBCCStatus
- Exec sp_executesql N'DBCC TraceStatus With No_InfoMsgs'
- If Not Exists (Select 1 From @DBCCStatus
- Where TraceFlag = 1204
- And TFGlobal = 1
- And TFStatus = 1)
- Begin
- RaisError ('Trace flag 1204 is not running globally.', 1 ,1)
- End
- Else
- Begin
- Print 'Trace flag 1204 is running globally.'
- End
- If Not Exists (Select 1 From @DBCCStatus
- Where TraceFlag = 1222
- And TFGlobal = 1
- And TFStatus = 1)
- Begin
- RaisError ('Trace flag 1222 is not running globally.', 1 ,1)
- End
- Else
- Begin
- Print 'Trace flag 1222 is running globally.'
- End
- Set Nocount Off
Add Comment
Please, Sign In to add comment