Month: May 2011

Script to return the SQL Server service-account

--service_accounts.sql

-- return sql-server-service-account used by the default-instance
DECLARE @serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEMCurrentControlSetServicesMSSQLSERVER',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'
SELECT @Serviceaccount ServiceAccount_DefaultInstance

-- ditto for named-instance
DECLARE @serviceaccount2 varchar(100)
exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SystemCurrentControlSetServicesMSSQL$sqlins01', -- edit
N'ObjectName',
@ServiceAccount2 OUTPUT,
'no_output'
SELECT @Serviceaccount2 ServiceAccount_NamedInstance

-- and for named-agent
DECLARE @agentaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEMCurrentControlSetServicessqlagent$sqlins01', --edit
N'ObjectName',
@agentaccount OUTPUT,
N'no_output'
select @agentaccount ServiceAccount_NamedAagent

Search All Tables – script

--dba_SearchAllTables.sql
--Searches all columns of all tables for a given search string (4.47 mins)
--EG: exec dba_searchalltables 'patriqu%'

alter proc dba_SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
begin

	create table #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	set nocount on

	declare @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	set  @TableName = ''
--	set @SearchStr2 = quotename('%' + @SearchStr + '%','''')
	SET @SearchStr2 = quotename('' + @SearchStr + '','''')

	while @TableName is not null
	begin
		set @ColumnName = ''
		set @TableName =
		(
			select min(quotename(table_schema) + '.' + quotename(table_name))
			from information_schema.tables
			where table_type = 'BASE TABLE'
				and	quotename(table_schema) + '.' + quotename(table_name) > @TableName
				and	objectproperty(
						object_id(
							quotename(table_schema) + '.' + quotename(table_name)
							 ), 'IsMSShipped'
						       ) = 0
		)

		while (@TableName is not null) and (@ColumnName is not null)
		begin
			set @ColumnName =
			(
				select min(quotename(column_name))
				from information_schema.columns
				where table_schema = parsename(@TableName, 2)
					and	table_name = parsename(@TableName, 1)
					and	data_type in ('char', 'varchar', 'nchar', 'nvarchar')
					and	quotename(column_name) > @ColumnName
			)

			if @ColumnName is not null
			begin
				insert into #Results
				exec
				(
					'select ''' + @TableName + '.' + @ColumnName + ''', left(' + @ColumnName + ', 3630)
					from ' + @TableName + ' (nolock) ' +
					' where ' + @ColumnName + ' like ' + @SearchStr2
				)
			end
		end
	end

	select ColumnName, ColumnValue from #Results
end

GO

Orphaned Indexes

Our ‘check indexes’ job was failing occasionally with an error like …

Msg 7999, Level 16, State 40, Line 1 Could not find any index named ‘index_name’ for table ‘table_name’.

I tried to just exclude the named index, but a few days later another one appeared. The fix was to dig into the SP and add a condition to the statement …

...
insert into #indexlist
            select name
	    from sysindexes
	    where name not like '_WA%'
	    and indid not in (0,255)
	    and [root] is not null
	    and id = @tab_id
...

The Oracle SCN issue

Oracle-database uses a large number (‘SCN’) as a time-stamp for referential-integrity, backup/restores, patch levels etc etc. When instances are linked the highest SCN number overwrites all others.

Trouble is … a ‘warning limit’ is being reached (three quarters of the actual limit). And when exceeded Oracle-database assumes its corrupt and becomes unstable.

But wait! There’s a ‘fix’, a patch which lets you manually set the SCN back below the soft-limit. BUT this needs to occur on all linked instances within a short period, or they are just updated from the one with the highest number (dowh!).

And Finally … the fix is only available for the latest version of Oracle. So an obscure, forgotten, out-of-date, instance in the back-of-beyond can bring down hundreds of shiny new machines (‘patched’ or not) until upgraded.

‘Oracle SCN management’ then, not a disaster just another ongoing DBA task 🙂