Galaxy Server SQL Tips

Galaxy Information

Get total number of active users on a repository

SELECT * FROM SKVirtualActor WHERE IsActiveUser = 1;

Troubleshooting

Cleaning up the change log

DELETE FROM gobject_change_log
WHERE change_date <= DATEADD(dd, -15, GetDate())

Clean up unused galaxy objects

DECLARE @return_value int, @all_finished int
EXEC internal_delete_unused_packages @all_finished = @all_finished OUTPUT
SELECT @all_finished as N'@all_finished'
SELECT 'Return Value' = @return_value

Rebuild/Reorganize Indexes (Long Version - Based on Fragmentation)

DECLARE @reIndexRequest VARCHAR(1000)

DECLARE reIndexList CURSOR
FOR
SELECT INDEX_PROCESS
FROM (
    SELECT CASE 
            WHEN avg_fragmentation_in_percent BETWEEN 5
                    AND 30
                THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REORGANIZE;'
            WHEN avg_fragmentation_in_percent > 30
                THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REBUILD WITH (FILLFACTOR=80, SORT_IN_TEMPDB=ON, ONLINE=ON);'
            END AS INDEX_PROCESS
        ,avg_fragmentation_in_percent
        ,t.NAME
    FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS a
    INNER JOIN sys.indexes AS i ON a.object_id = i.object_id
        AND a.index_id = i.index_id
    INNER JOIN sys.tables t ON t.object_id = i.object_id
    WHERE i.NAME IS NOT NULL
    ) PROCESS
WHERE PROCESS.INDEX_PROCESS IS NOT NULL
ORDER BY avg_fragmentation_in_percent DESC

OPEN reIndexList

FETCH NEXT
FROM reIndexList
INTO @reIndexRequest

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY

        PRINT @reIndexRequest;

        EXEC (@reIndexRequest);

    END TRY

    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = 'UNABLE TO CLEAN UP INDEX WITH: ' + @reIndexRequest + ': MESSAGE GIVEN: ' + ERROR_MESSAGE()
            ,@ErrorSeverity = 9 
            ,@ErrorState = ERROR_STATE();

    END CATCH;

    FETCH NEXT
    FROM reIndexList
    INTO @reIndexRequest
END

CLOSE reIndexList;

DEALLOCATE reIndexList;

Rebuild Indexes (Short Version - Rebuild All)

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=80, SORT_IN_TEMPDB=ON, ONLINE=ON)'

Reorganize Indexes (Short Version - Reorganize All)

This isn't necessary if you do the rebuild as a REBUILD is more thorough than a REORGANIZE operation.

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REORGANIZE'

Truncate the database log file

DECLARE @DBNAME nvarchar(max)
SET @DBNAME= 'Test' /* Set the correct galaxy name here*/

DECLARE @RecoveryModel nvarchar(max)
SELECT @RecoveryModel = recovery_model_desc
FROM sys.databases
WHERE name = @DBNAME;

IF (@RecoveryModel is not null)
  BEGIN
    DECLARE @LOGNAME varchar(100)
    DECLARE @sql nvarchar(max)
    SET @sql = 'ALTER DATABASE '+ @DBNAME +' SET RECOVERY SIMPLE'
    IF (upper(@RecoveryModel)<> 'SIMPLE')
       EXEC (@sql)
    IF (coalesce(object_id(N'tempdb..##tt'),0)>0)
       DROP TABLE ##tt;
    SET @sql = 'select name into ##tt from sys.database_files where type =0'
      EXEC sp_executesql @sql

    DECLARE users_cursor
    CURSOR FOR SELECT name FROM ##tt
    OPEN users_cursor
    FETCH NEXT FROM users_cursor
    INTO @LOGNAME
    WHILE @@FETCH_STATUS = 0
    BEGIN
      --Print @LOGNAME
      DBCC SHRINKFILE (@LOGNAME , 1)
      FETCH NEXT FROM users_cursor --have to fetch again within loop
      INTO @LOGNAME
    END
    CLOSE users_cursor
    DEALLOCATE users_cursor

    SET @sql = 'DBCC SHRINKDATABASE (''' + @DBNAME +''' , TRUNCATEONLY)'
      EXEC(@sql)
    SET @sql ='ALTER DATABASE '+ @DBNAME +' SET RECOVERY FULL;'
    IF (upper(@RecoveryModel)<> 'SIMPLE')
       EXEC(@sql)
  END

Rebuild/Reorganize Indexes (Long Version - Based on Fragmentation)

DECLARE @reIndexRequest VARCHAR(1000)

DECLARE reIndexList CURSOR
FOR
SELECT INDEX_PROCESS
FROM (
    SELECT CASE 
            WHEN avg_fragmentation_in_percent BETWEEN 5
                    AND 30
                THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REORGANIZE;'
            WHEN avg_fragmentation_in_percent > 30
                THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REBUILD with(ONLINE=ON);'
            END AS INDEX_PROCESS
        ,avg_fragmentation_in_percent
        ,t.NAME
    FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS a
    INNER JOIN sys.indexes AS i ON a.object_id = i.object_id
        AND a.index_id = i.index_id
    INNER JOIN sys.tables t ON t.object_id = i.object_id
    WHERE i.NAME IS NOT NULL
    ) PROCESS
WHERE PROCESS.INDEX_PROCESS IS NOT NULL
ORDER BY avg_fragmentation_in_percent DESC

OPEN reIndexList

FETCH NEXT
FROM reIndexList
INTO @reIndexRequest

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY

        PRINT @reIndexRequest;

        EXEC (@reIndexRequest);

    END TRY

    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = 'UNABLE TO CLEAN UP INDEX WITH: ' + @reIndexRequest + ': MESSAGE GIVEN: ' + ERROR_MESSAGE()
            ,@ErrorSeverity = 9 
            ,@ErrorState = ERROR_STATE();

    END CATCH;

    FETCH NEXT
    FROM reIndexList
    INTO @reIndexRequest
END

CLOSE reIndexList;

DEALLOCATE reIndexList;

Rebuild Indexes (Short Version - Rebuild All)

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (ONLINE = ON)'

Reorganize Indexes (Short Version - Reorganize All)

This isn't necessary if you do the rebuild as a REBUILD is more thorough than a REORGANIZE operation.

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REORGANIZE'

Testing database integrity

EXEC internal_validate_integrity

Templates

Find template gobject ID

SELECT gobject_ID FROM GalaxyName.gobject WHERE tag_name LIKE 'TemplateName';

Unprotect template

DELETE FROM GalaxyName.gobject_protected WHERE gobject_id = #

Protect template

INSERT INTO GalaxyName.gobject_protected (gobject_id) VALUES (#);

Get list of protected templates

SELECT g.gobject_id, g.tag_name
FROM gobject as g
INNER JOIN gobject_protected as p on p.gobject_id = g.gobject_id
ORDER BY g.gobject_id

Get list of all objects and their areas derived from a specific template

DECLARE @tempName VARCHAR(50);
SET @tempName = '$TemplateName';

SELECT g.tag_name as TagName,
		(SELECT tag_name FROM gobject WHERE (gobject_id = g.hosted_by_gobject_id)) AS Area
FROM gobject AS g
WHERE g.is_template = 0
	AND g.derived_from_gobject_id = (
	SELECT gobject_id FROM gobject WHERE tag_name = @tempName
	)
ORDER BY Area, g.tag_name

Get count of objects derived from a specific template

DECLARE @tempName VARCHAR(50);
SET @tempName = '$TemplateName';

SELECT COUNT(gobject_id)
FROM gobject 
WHERE is_template = 0
	AND derived_from_gobject_id = (
	SELECT gobject_id FROM gobject WHERE tag_name = @tempName
	)

Alarms

Get list of all Alarmed attributes

SELECT g.tag_name + '.' + p.primitive_name AS name, p.gobject_id, p.package_id, p.mx_primitive_id, g.hosted_by_gobject_id,
(SELECT tag_name FROM gobject WHERE (gobject_id = g.hosted_by_gobject_id)) AS Area
FROM gobject AS g INNER JOIN
primitive_instance AS p ON p.gobject_id = g.gobject_id AND p.execution_group = 19 INNER JOIN
primitive_definition AS pd ON p.primitive_definition_id = pd.primitive_definition_id AND pd.primitive_name <> '' INNER JOIN
package AS pkg ON p.gobject_id = pkg.gobject_id AND p.package_id = pkg.package_id AND pkg.package_id = g.checked_in_package_id
WHERE (g.namespace_id = 1) AND (g.is_template = 0)
ORDER BY name

Get list of all alarmed attributes defined in templates

SELECT g.tag_name AS Template, p.primitive_name AS Attribute
FROM gobject AS g INNER JOIN
primitive_instance AS p ON p.gobject_id = g.gobject_id AND p.execution_group = 19 INNER JOIN
primitive_definition AS pd ON p.primitive_definition_id = pd.primitive_definition_id AND pd.primitive_name <> '' INNER JOIN
package AS pkg ON p.gobject_id = pkg.gobject_id AND p.package_id = pkg.package_id AND pkg.package_id = g.checked_in_package_id
WHERE (g.namespace_id = 1) AND (g.is_template = 1)
ORDER BY Template, Attribute

Get alarms from wwALMDB

SELECT EventStamp, AlarmState, TagName, Description, Operator, Priority, UserFullName
FROM v_AlarmHistory
where EventStamp > '1/1/2013' and EventStamp < '1/2/2013';

Historization

Get list of all historized attributes defined in templates

SELECT g.tag_name AS Template, p.primitive_name AS Attribute
FROM gobject AS g INNER JOIN
primitive_instance AS p ON p.gobject_id = g.gobject_id AND p.execution_group = 18 INNER JOIN
primitive_definition AS pd ON p.primitive_definition_id = pd.primitive_definition_id AND pd.primitive_name <> '' INNER JOIN
package AS pkg ON p.gobject_id = pkg.gobject_id AND p.package_id = pkg.package_id AND pkg.package_id = g.checked_in_package_id
WHERE (g.namespace_id = 1) AND (g.is_template = 1)
ORDER BY Template, Attribute

Scripts

Get list of all scripts defined in templates

SELECT g.tag_name AS Template, p.primitive_name AS Script
FROM gobject AS g INNER JOIN
primitive_instance AS p ON p.gobject_id = g.gobject_id AND p.execution_group = 16 INNER JOIN
primitive_definition AS pd ON p.primitive_definition_id = pd.primitive_definition_id AND pd.primitive_name <> '' INNER JOIN
package AS pkg ON p.gobject_id = pkg.gobject_id AND p.package_id = pkg.package_id AND pkg.package_id = g.checked_in_package_id
WHERE (g.namespace_id = 1) AND (g.is_template = 1)
ORDER BY Template, Script

Log Change Events

Get list of all log change attributes defined in templates

SELECT g.tag_name AS Template, p.primitive_name AS Attribute
FROM gobject AS g INNER JOIN
primitive_instance AS p ON p.gobject_id = g.gobject_id AND p.execution_group = 6 INNER JOIN
primitive_definition AS pd ON p.primitive_definition_id = pd.primitive_definition_id AND pd.primitive_name <> '' INNER JOIN
package AS pkg ON p.gobject_id = pkg.gobject_id AND p.package_id = pkg.package_id AND pkg.package_id = g.checked_in_package_id
WHERE (g.namespace_id = 1) AND (g.is_template = 1)
ORDER BY Template, Attribute