March 25, 2011 at 4:32 am
That's not what I meant. All I can see is a 'domain unregistered' error image.
Edit your post. Go to the section below the edit box. Click Edit Attachements. Click Browse. Find the image file (on your PC). Click Open. Click Upload.
No shrink will not be causing this. You should, however, not be shrinking your database on a regular basis. It's incredibly bad practice.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2011 at 7:49 am
here it is... sorry : the image was showing fine on my computer so i thought everybody could see it
March 25, 2011 at 7:56 am
Lots wrong with that maint plan, but nothing that should be causing this.
You don't need to rebuild and reorganise indexes. Rebuild does everything reorg does and more. Pick one, drop the other. Better yet, go hunt the net for an index rebuild script (try http://www.sqlfool.com) that only rebuilds what needs rebuilding and doesn't do everything.
The shrink right after the index rebuild is undoing everything that the rebuild did. Shrink causes massive fragmentation and you'll probably have higher fragmentation after running that than you did before rebuilding indexes. Lose that step, you should not be shrinking the DB on a regular basis.
Are you sure there's no other log backup job?
Check the sQL error log, all backups are logged, check that there are no unexpected logs. Query the msdb backup tables and check the same.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2011 at 10:27 am
no this is the only bakcup FULL that is performed, the one from the maintenance plan.
it used to work fine since many month but now i am getting this issue : i can rebuild the transaction log shipping but it only last for a day... after the full bakcup, it starts to fail again. I have done it three times already and same issue.
March 25, 2011 at 10:41 am
This won't help with the Restore issue you're experiencing but to aid what Gail mentioned, you could use it to replace those rebuild/reorg steps in your Maintenance plan
You can use the dba_ReBuildOrReorgIndexesByFragLevel for each database individually (and leave it at that) or you can use the second procedure to execute the first procedure automagically for all databases on your SQL server. Not sure the critique Gail may have with this script 😉 but it's been working very well for our organization since I implemented it a couple weeks back
USE [F1Settings]
GO
/****** Object: StoredProcedure [dbo].[dba_ExecuteServerIndexMaintenance] Script Date: 03/22/2011 14:44:15 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_ExecuteServerIndexMaintenance]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dba_ExecuteServerIndexMaintenance]
GO
USE [F1Settings]
GO
/****** Object: StoredProcedure [dbo].[dba_ExecuteServerIndexMaintenance] Script Date: 03/22/2011 14:44:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ############################################################################################################### */
--EXECUTE SERVER INDEX MAINTENANCE
/* ############################################################################################################### */
CREATE PROCEDURE [dbo].[dba_ExecuteServerIndexMaintenance](
@FillFactor varchar(2),
@MinPages varchar(2) = '50',
@Exec varchar(1)
)
AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose:Executes F1Settings.dbo.dba_ReBuildOrReorgIndexesByFragLevel to run against ALL DB's on the
EXECUTING SERVER; excluding master, tempdb, model, msdb, and distribution
Department:DBA
Created For:John Doe
----------------------------------------------------------------------------------------------------------------
NOTES:1)Passes in parameters to the primary stored-procedure
----------------------------------------------------------------------------------------------------------------
Created On:03/15/2011
Create By:MyDoggieJessie
----------------------------------------------------------------------------------------------------------------
Modified On:
Modified By:
Changes:
1.
----------------------------------------------------------------------------------------------------------------
exec dbo.dba_ExecuteServerIndexMaintenance '92', 2, 0
*/
SET NOCOUNT ON
DECLARE @iint
DECLARE @Recsint
DECLARE @Tablesysname
DECLARE @SQLnvarchar(500)
/* ######################################### START MAIN PROCEDURE HERE ########################################## */
/* Create Temp Table to store the results in */
CREATE TABLE #Results (
Idx int IDENTITY(1,1), TName sysname
)
/* Fetch All the DB's on the Server */
INSERT INTO #Results
EXEC sp_MSForEachDB 'Use [?]; SELECT DB_NAME()'
/* Get rid of the ones we don't want to index */
DELETE FROM #Results
WHERE TName IN ('MASTER', 'TEMPDB', 'MODEL', 'MSDB', 'DISTRIBUTION', 'AdventureWorks')
/* Loop through the DB's and kick off the magic */
SET @recs = (SELECT COUNT(1) FROM #Results)
WHILE @Recs <> 0
BEGIN
SET @TABLE = (SELECT Top 1 TName FROM #Results )
SET @SQL = ' exec F1Settings.dbo.dba_ReBuildOrReorgIndexesByFragLevel '''
+ @Table + ''', ''' + @FillFactor + ''', ' + @MinPages + ', ' + @Exec
EXEC sp_executesql @SQL
DELETE FROM #Results WHERE TName = @Table
SET @recs = (SELECT COUNT(1) FROM #Results)
END
/* ########################################## END MAIN PROCEDURE HERE ########################################### */
DROP TABLE #Results
/*
exec dbo.dba_ExecuteServerIndexMaintenance '92', 2, 0
*/
SET NOCOUNT OFF
GO
USE [F1Settings]
GO
/****** Object: StoredProcedure [dbo].[dba_ReBuildOrReorgIndexesByFragLevel] Script Date: 03/22/2011 14:43:55 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_ReBuildOrReorgIndexesByFragLevel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dba_ReBuildOrReorgIndexesByFragLevel]
GO
USE [F1Settings]
GO
/****** Object: StoredProcedure [dbo].[dba_ReBuildOrReorgIndexesByFragLevel] Script Date: 03/22/2011 14:43:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ############################################################################################################### */
--DYNAMICALLY REORG/REBUILD SERVER INDEXES BY FRAGMENTATION LEVEL
/* ############################################################################################################### */
CREATE PROCEDURE [dbo].[dba_ReBuildOrReorgIndexesByFragLevel] (
@DB varchar(50),
@FillFactor varchar(2),
@MinPages int = 50,
@Exec int
)
AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose:Written to dynamically perform maintenance on indexes if fragmentation gets too high
Department:DBA
Created For:John Doe----------------------------------------------------------------------------------------------------------------
NOTES:* Primary concept originally created by Microsoft *
* Error corrected by Pinal Dave (http://www.SQLAuthority.com) *
----------------------------------------------------------------------------------------------------------------
Created On:03/14/2011
Create By:MyDoggieJessie----------------------------------------------------------------------------------------------------------------
Modified On:03/18/2011
Modified By:MyDoggieJessie
Changes:
1.Changed datatypes from varchar to sysname for TName, @tablename, and @IdxName
----------------------------------------------------------------------------------------------------------------
exec dbo.dba_ReBuildOrReorgIndexesByFragLevel 'AdventureWorks', '92', 2, 0
*/
SET NOCOUNT ON
IF OBJECT_ID ('tempdb.dbo.#fraglist','u') IS NOT NULL
BEGIN
DROP TABLE #fraglist
END
IF OBJECT_ID ('tempdb.dbo.#tables','u') IS NOT NULL
BEGIN
DROP TABLE #tables
END
-- Declare variables
DECLARE @TableNamesysname,@SQLvarchar(1500)
DECLARE @objectidint,@XTypevarchar(3)
DECLARE @indexidint,@fragdecimal
DECLARE @IdxNamesysname,@Recsint
DECLARE @Errvarchar(500),@iint
DECLARE @Versionvarchar(3),@Schemavarchar(3)
DECLARE @Threshold1decimal,@Threshold2decimal
SET @i = 1
SET @Version = UPPER(CONVERT(varchar(3), SERVERPROPERTY('edition')))
/* ######################################### START MAIN PROCEDURE HERE ########################################## */
CREATE TABLE #Tables (
Idx int IDENTITY(1,1),
TName sysname,
TType varchar(3),
SSchema varchar(3)
)
SET @SQL = '
SELECT CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name, ''ON'' AS Table_Type, TABLE_SCHEMA
FROM ' + RTRIM(@DB) + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
AND [table_name] NOT IN (
SELECT DISTINCT a.name
FROM ' + RTRIM(@DB) + '.sys.sysobjects AS a WITH(READUNCOMMITTED)
JOIN ' + RTRIM(@DB) + '.sys.syscolumns AS b WITH(READUNCOMMITTED) ON
a.id=b.id
JOIN ' + RTRIM(@DB) + '.sys.syscolumns AS c WITH(READUNCOMMITTED) ON
c.xtype=b.xtype
WHERE b.xType IN (''34'',''35'',''99'',''241''))
AND TABLE_NAME NOT LIKE ''MS%''
UNION
SELECT CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name, ''OFF'' AS Table_Type, TABLE_SCHEMA
FROM ' + RTRIM(@DB) + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
AND [table_name] IN (
SELECT DISTINCT a.name
FROM ' + RTRIM(@DB) + '.sys.sysobjects AS a WITH(READUNCOMMITTED)
JOIN ' + RTRIM(@DB) + '.sys.syscolumns AS b WITH(READUNCOMMITTED) ON
a.id=b.id
JOIN ' + RTRIM(@DB) + '.sys.syscolumns AS c WITH(READUNCOMMITTED) ON
c.xtype=b.xtype
WHERE b.xType IN (''34'',''35'',''99'',''241''))
AND TABLE_NAME NOT LIKE ''MS%''
ORDER BY TABLE_NAME;'
INSERT INTO #Tables
EXEC(@SQL)
-- Create the temporary table.
CREATE TABLE #fraglist (
ObjectName sysname,ObjectId int,IndexName sysname,
IndexId int,Lvl int,CountPages int,
CountRows int,MinRecSize int,MaxRecSize int,
AvgRecSize int,ForRecCount int,Extents int,
ExtentSwitches int,AvgFreeBytes int,AvgPageDensity int,
ScanDensity decimal,BestCount int,ActualCount int,
LogicalFrag decimal,ExtentFrag decimal
)
SET @Recs = (SELECT COUNT(1) FROM #Tables)
WHILE @i <= @Recs
BEGIN
SET @TableName = (SELECT TOP 1 TName FROM #Tables WHERE Idx = @i)
SET @Schema = (SELECT TOP 1 SSchema FROM #Tables WHERE Idx = @i)
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @DB + '.' + @Schema + '.[' + @TableName + ']'')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
SET @i = @i + 1
END
/* Add column needed so we can perform ON/OFFLINE statements */
ALTER TABLE #fraglist
ADD idxType varchar(3) NULL
IF RTRIM(@Version) = 'STA'
BEGIN
UPDATE #fraglist
SET idxType = 'OFF' FROM #Tables WHERE ObjectName = TName
END
ELSE
BEGIN
UPDATE #fraglist
SET idxType = TType FROM #Tables WHERE ObjectName = TName
END
/* Remove records not needed for the Cursor */
DELETE FROM #fraglist
WHERE CountPages < @MinPages
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT
ObjectName, ObjectId, IndexId, LogicalFrag, IndexName,
CASE WHEN INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') IS NULL THEN 'OFF'
ELSE idxType END
FROM #fraglist
WHERE
CountPages > @MinPages --LogicalFrag >= @MaxFrag
AND ISNULL(INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth'), 1) > 0
ORDER BY LogicalFrag DESC
-- Open the cursor.
OPEN indexes
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @TableName, @objectid, @indexid, @frag, @IdxName, @XType
SET @i = 0
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Exec = 0)
BEGIN
SET @i = @i + 1
IF @frag BETWEEN 13 AND 20
BEGIN
PRINT RTRIM(CONVERT(varchar(10),@i))+'. [' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%] '
+ RTRIM(@IdxName) + ' - ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
END
IF @frag > 20
BEGIN
PRINT RTRIM(CONVERT(varchar(10),@i))+'. [' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%] '
+ RTRIM(@IdxName) + ' - ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REBUILD WITH ( SORT_IN_TEMPDB = OFF, FILLFACTOR = '
+ @FillFactor + ', MAXDOP = 0 , ONLINE = ' + @XType + ' ) '
END
END
ELSE
BEGIN
SET @i = @i + 1
IF @frag BETWEEN 13 AND 20
BEGIN
SELECT @SQL = 'ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
BEGIN TRY
PRINT(@SQL)
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT ('ERROR FOUND: ' + ERROR_MESSAGE())
END CATCH
END
IF @frag > 20 --> Rebuild Indexes
BEGIN
SELECT @SQL = 'ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REBUILD WITH ( SORT_IN_TEMPDB = OFF, FILLFACTOR = '
+ @FillFactor + ', MAXDOP = 0 , ONLINE = ' + @XType + ' ) '
BEGIN TRY
PRINT(@SQL)
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT ('ERROR FOUND: ' + ERROR_MESSAGE())
END CATCH
END
END
FETCH NEXT
FROM indexes
INTO @TableName, @objectid, @indexid, @frag, @IdxName, @XType
END
-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
/* ########################################## END MAIN PROCEDURE HERE ########################################### */
/*
exec dbo.dba_ReBuildOrReorgIndexesByFragLevel 'CORELIBRARY_STAGING', '96',50, 0
*/
-- Delete the temporary table.
DROP TABLE #fraglist
DROP TABLE #Tables
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 25, 2011 at 1:57 pm
djguana (3/25/2011)
no this is the only bakcup FULL that is performed, the one from the maintenance plan.
Not asking about full backups. Full backups don't break log shipping.
Can you check the logs and the msdb tables and make sure that no LOG backups are running at the same time as the full, no log backups other than what the log shipping runs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2011 at 1:59 pm
Any idea for the reason why the transaction log shipping is out of sync after my maintenance plan ?
March 25, 2011 at 2:06 pm
djguana (3/25/2011)
Any idea for the reason why the transaction log shipping is out of sync after my maintenance plan ?
Somewhere other than the log shipping you have a scheduled job that is performing a Transaction Log backup. That job/task/application/whatever needs to be stopped.
If you're shipping logs, you should not be doing any other t-log backups anywhere for the database(s) you are shipping.
Have you recently installed a backup agent such as avamar, veritas, or another client for backups?
Avamar calls a transaction log backup an incremental backup in its agent configuration, and that would need to be stopped. If you temporarily disable log shipping, do you still see transaction log backups in the SQL error log on the server? As stated above, they are coming from somewhere, but fortunately they are logged.
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply