April 26, 2010 at 10:38 am
Hello,
I discovered that after this code runs, my transaction job only for 2 db fails (log chain is broken and I have to restore full back up to fix the problem) All others db are fine.Can someone please help.
Here is a code:
@ExcludeDBs VARCHAR(MAX) = '''tempdb''',
@Verbose BIT = 0,
@Fragmentation INT = 50,
@FreeSpace INT = 10
AS
SET NOCOUNT ON
DECLARE @SVVSDatabases TABLE ([name] sysname)
INSERT INTO @SVVSDatabases
EXEC('SELECT [name] FROM [master].[sys].[databases]
WHERE ([name] NOT IN (' + @ExcludeDBs + '))
AND ([state_desc] NOT IN (''OFFLINE'', ''RESTORING''))
AND ([is_in_standby] <> 1)
AND ([source_database_id] IS NULL)
AND ([is_read_only] <> 1)')
DECLARE @ErrorMessage VARCHAR(MAX)
DECLARE @SVVSDatabaseMaintenance_DBName SYSNAME
DECLARE SVVSDatabaseMaintenance_Cursor CURSOR FOR
SELECT [name] FROM @SVVSDatabases
OPEN SVVSDatabaseMaintenance_Cursor
FETCH NEXT FROM SVVSDatabaseMaintenance_Cursor
INTO @SVVSDatabaseMaintenance_DBName
WHILE @@FETCH_STATUS = 0
BEGIN
CheckDatabaseIntegrity:
BEGIN TRY
IF (@Verbose = 1) PRINT 'Checking Database Integrity on: ' + @SVVSDatabaseMaintenance_DBName
EXEC ('DBCC CHECKDB (N''' + @SVVSDatabaseMaintenance_DBName + ''') WITH NO_INFOMSGS')
END TRY
BEGIN CATCH
IF (@Verbose = 1)
BEGIN
PRINT 'ERROR -- Check Database Integrity Task: ' + @SVVSDatabaseMaintenance_DBName + ' - ' + ERROR_MESSAGE()
PRINT ('DBCC CHECKDB (N''' + @SVVSDatabaseMaintenance_DBName + ''') WITH NO_INFOMSGS')
END
SET @ErrorMessage = Replace(ERROR_MESSAGE(), '''', '')
EXEC('RAISERROR(''ERROR -- Check Database Integrity Task: ' + @SVVSDatabaseMaintenance_DBName + ' - ' + @ErrorMessage + ''', 16, 1) WITH LOG')
END CATCH
IF (@@ERROR = 0) GOTO ShrinkDatabase ELSE GOTO NextDatabase
ShrinkDatabase:
BEGIN TRY
IF (@Verbose = 1) PRINT 'Shrinking Database: ' + @SVVSDatabaseMaintenance_DBName
EXEC ('DBCC SHRINKDATABASE(N''' + @SVVSDatabaseMaintenance_DBName + ''', ' + @FreeSpace + ', NOTRUNCATE) WITH NO_INFOMSGS')
END TRY
BEGIN CATCH
IF (@Verbose = 1)
BEGIN
PRINT 'ERROR -- Shrink Database Task: ' + @SVVSDatabaseMaintenance_DBName + ' - ' + ERROR_MESSAGE()
PRINT ('DBCC SHRINKDATABASE(N''' + @SVVSDatabaseMaintenance_DBName + ''', 10, NOTRUNCATE) WITH NO_INFOMSGS')
END
SET @ErrorMessage = Replace(ERROR_MESSAGE(), '''', '')
EXEC('RAISERROR(''ERROR -- Shrink Database Task: ' + @SVVSDatabaseMaintenance_DBName + ' - ' + @ErrorMessage + ''', 16, 1) WITH LOG')
END CATCH
IF (@@ERROR = 0) GOTO ReorganizeIndex ELSE GOTO NextDatabase
ReorganizeIndex:
DECLARE @DatabaseID INT
SET @DatabaseID = db_id(@SVVSDatabaseMaintenance_DBName)
DECLARE @SVVSTablesIndexes TABLE([TableViewName] SYSNAME, [IndexName] SYSNAME)
INSERT INTO @SVVSTablesIndexes
EXEC('SELECT
QUOTENAME(OBJECT_SCHEMA_NAME([Frag].[object_id], [Frag].[database_id])) + ''.'' +
QUOTENAME(OBJECT_NAME([Frag].[object_id], [Frag].[database_id])) AS [TableViewName],
[Indx].[name] AS [IndexName]
FROM [master].[sys].[dm_db_index_physical_stats] ('+ @DatabaseID +', NULL, NULL, NULL, NULL) AS [Frag]
INNER JOIN [' + @SVVSDatabaseMaintenance_DBName + '].[sys].[indexes] AS [Indx] ON [Indx].[object_id] = [Frag].[object_id] AND [Indx].[index_id] = [Frag].[index_id]
WHERE
([Frag].[avg_fragmentation_in_percent] >= ' + @Fragmentation + ')
AND ([Frag].[index_id] > 0) ')
DECLARE @SVVSTableViewName SYSNAME
DECLARE SVVSTableViewName_Cursor CURSOR FOR
SELECT DISTINCT [TableViewName] FROM @SVVSTablesIndexes
OPEN SVVSTableViewName_Cursor
FETCH NEXT FROM SVVSTableViewName_Cursor
INTO @SVVSTableViewName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SVVSIndexName SYSNAME
DECLARE SVVSIndexName_Cursor CURSOR FOR
SELECT DISTINCT [IndexName] FROM @SVVSTablesIndexes
WHERE [TableViewName] = @SVVSTableViewName
OPEN SVVSIndexName_Cursor
FETCH NEXT FROM SVVSIndexName_Cursor
INTO @SVVSIndexName
WHILE @@FETCH_STATUS = 0
BEGIN
ReoragnizeIndexes:
BEGIN TRY
IF (@Verbose = 1) PRINT 'Reorganizing Index: ' + @SVVSIndexName + ' for Table: ' + @SVVSTableViewName + ' on Database: ' + @SVVSDatabaseMaintenance_DBName
EXEC ('ALTER INDEX [' + @SVVSIndexName + '] ON [' + @SVVSDatabaseMaintenance_DBName + '].' + @SVVSTableViewName + ' REORGANIZE WITH ( LOB_COMPACTION = ON )')
END TRY
BEGIN CATCH
IF (@Verbose = 1)
BEGIN
PRINT 'ERROR -- Reorganizing Index Task for Index: ' + @SVVSIndexName + ' on Table: ' + @SVVSTableViewName + ' in Database: ' + @SVVSDatabaseMaintenance_DBName + ': ' + ERROR_MESSAGE()
PRINT ('ALTER INDEX [' + @SVVSIndexName + '] ON [' + @SVVSDatabaseMaintenance_DBName + '].' + @SVVSTableViewName + ' REORGANIZE WITH ( LOB_COMPACTION = ON )')
END
SET @ErrorMessage = Replace(ERROR_MESSAGE(), '''', '')
EXEC('RAISERROR(''ERROR -- Reorganizing Index Task for Index: ' + @SVVSIndexName + ' on Table: ' + @SVVSTableViewName + ' in Database: ' + @SVVSDatabaseMaintenance_DBName + ' - ' + @ErrorMessage + ''', 16, 1) WITH LOG')
END CATCH
UpdateStatistics:
BEGIN TRY
IF (@Verbose = 1) PRINT 'Updating Statistics for Table: ' + @SVVSTableViewName + ' on Database: ' + @SVVSDatabaseMaintenance_DBName
EXEC('UPDATE STATISTICS [' + @SVVSDatabaseMaintenance_DBName + '].' + @SVVSTableViewName + ' [' + @SVVSIndexName + '] WITH FULLSCAN')
END TRY
BEGIN CATCH
IF (@Verbose = 1)
BEGIN
PRINT 'ERROR -- Update Statistics Task: ' + @SVVSDatabaseMaintenance_DBName + ' - ' + ERROR_MESSAGE()
PRINT ('UPDATE STATISTICS [' + @SVVSDatabaseMaintenance_DBName + '].' + @SVVSTableViewName + ' [' + @SVVSIndexName + '] WITH FULLSCAN')
END
SET @ErrorMessage = Replace(ERROR_MESSAGE(), '''', '')
EXEC('RAISERROR(''ERROR -- Update Statistics Task: ' + @SVVSDatabaseMaintenance_DBName + ' - ' + @ErrorMessage + ''', 16, 1) WITH LOG')
END CATCH
FETCH NEXT FROM SVVSIndexName_Cursor
INTO @SVVSIndexName
END
CLOSE SVVSIndexName_Cursor
DEALLOCATE SVVSIndexname_Cursor
FETCH NEXT FROM SVVSTableViewName_Cursor
INTO @SVVSTableViewName
END
CLOSE SVVSTableViewName_Cursor
DEALLOCATE SVVSTableViewName_Cursor
GOTO NextDatabase
NextDatabase:
DELETE FROM @SVVSTablesIndexes
FETCH NEXT FROM SVVSDatabaseMaintenance_Cursor
INTO @SVVSDatabaseMaintenance_DBName
END
CLOSE SVVSDatabaseMaintenance_Cursor
DEALLOCATE SVVSDatabaseMaintenance_Cursor
April 26, 2010 at 2:16 pm
There is nothing in that script that would cause the issue you are having. It has to be some other script that is breaking the log chain.
Two questions:
1) Why do you think you have to restore the database to re-establish the log chain? To re-establish the log chain you have to perform a full or diff backup, not restore.
2) Why are you shrinking your databases? And then, right after shrinking the database and log file you are performing an index rebuild/reorganize which is just going to grow the database? It is really a waste of time and resources.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 27, 2010 at 8:24 am
Two questions:
1) Why do you think you have to restore the database to re-establish the log chain? To re-establish the log chain you have to perform a full or diff backup, not restore.
I am sorry not to restore, to do a full backup, It is always happends after this job runs only for 2 databases, all others are ok.
2) Why are you shrinking your databases? And then, right after shrinking the database and log file you are performing an index rebuild/reorganize which is just going to grow the database? It is really a waste of time and resources.
This code was already there before I came, what is your recomendations, in what order I should do it and why?
Thank you very much
April 27, 2010 at 8:56 am
You were right I found other job:
which breaks a log chain which contains: BACKUP LOG @dbName WITH truncate_only, I am commenting this code out and I think that would fix it, here the code:
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- determine the name of the database we are currently connected to
DECLARE @dbName varchar(100)
SELECT @dbName = DB_NAME ()
-- dump the transaction log before we start...
BACKUP LOG @dbName WITH truncate_only
-- get the calls to delete and store the ids in a temp table
CREATE TABLE #t(server VARCHAR(50), unique_jobid VARCHAR(50))
INSERT INTO #t
SELECT server, unique_jobid FROM CALL_LOG
WHERE TRANSFERRED IS NOT NULL
CREATE INDEX [ix_temp_table] ON [#t]([server],[unique_jobid])
UPDATE STATISTICS #t
--delete the error log first
DELETE error_log
FROM error_log e, #t, peg_log p
WHEREp.server = #t.server
ANDp.unique_jobid = #t.unique_jobid
ANDe.server = p.server
ANDe.peg_log_id = p.peg_log_id
--then the speech log
DELETE speech_log
FROM speech_log s, #t, peg_log p
WHEREp.server = #t.server
ANDp.unique_jobid = #t.unique_jobid
ANDs.server = p.server
ANDs.peg_log_id = p.peg_log_id
--then the peg log
DELETE peg_log
FROM peg_log pl, #t
WHERE pl.server = #t.server
AND pl.unique_jobid = #t.unique_jobid
--and the ao log
DELETE ao_log
FROM ao_log al, #t
WHERE al.server = #t.server
AND al.unique_jobid = #t.unique_jobid
--and the call log
DELETE call_log
FROM call_log cl, #t
WHERE cl.server = #t.server
AND cl.unique_jobid = #t.unique_jobid
--drop the temp table
DROP TABLE #t
-- dump the transaction log at then end
BACKUP LOG @dbName WITH truncate_only
END
April 27, 2010 at 7:38 pm
That should fix the problem - unless there are other jobs doing the same thing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 28, 2010 at 6:34 am
Why are you shrinking your databases? And then, right after shrinking the database and log file you are performing an index rebuild/reorganize which is just going to grow the database? It is really a waste of time and resources.
This code was already there before I came, what is your recomendations, in what order I should do it and why?
April 28, 2010 at 8:50 am
My recommendation is to not shrink the databases - as I said before, it is just a waste of time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply