Author |
Topic |
magictech
Starting Member
44 Posts |
Posted - 2004-12-21 : 15:50:57
|
I'm having problem managing log and data file sizes while log shipping is configured and running properly. I have a re-indexing job that runs weekly and after this jobs runs, the log and data file sizes of the database that's being log-shipped increases by an additional 40Gig each. I would like to be able to shrink the data and log files of the primary database to their normal sizes without breaking log shipping. I realize the process of shrinking data and log files will automatically truncate the transaction log, which will break log shipping. Is there a way to shrink these files without breaking log shipping? Any information would be greatly appreciated.Regards |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-21 : 16:26:51
|
Shrinking the database does not truncate the tlog unless you specify it to. What command are you running?And you should NOT be shrinking them if the reindexing job is just going to need it again. Your database is taking a huge performance hit during the shrink and then again when it needs to expand. It is recommended to add disk space to support the needed file sizes for the reindexing job.Tara |
|
|
magictech
Starting Member
44 Posts |
Posted - 2004-12-21 : 17:22:58
|
Thanks for responding. I'm using the script below to shrink the data file, which I believe truncates the log file in the process. I would like to be able to shrink log and data files without breaking log shipping. Truncating the log will break log shipping.SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. *** USE [Staging] -- This is the name of the database -- for which the log will be shrunk. SELECT @LogicalFileName = 'Staging2_Log', -- Use sp_helpfile to -- identify the logical file -- name that you want to shrink. @MaxMinutes = 10, -- Limit on time allowed to wrap log. @NewSize = 200 -- in MB -- Setup / initialize DECLARE @OriginalSize int SELECT @OriginalSize = size -- in 8K pages FROM sysfiles WHERE name = @LogicalFileName SELECT 'Original Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' FROM sysfiles WHERE name = @LogicalFileName CREATE TABLE DummyTrans (DummyColumn char (8000) not null) -- Wrap log and truncate it. DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255) SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY' -- Try an initial shrink. DBCC SHRINKFILE (@LogicalFileName, @NewSize) EXEC (@TruncLog) -- Wrap the log if necessary. WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size. BEGIN -- Outer loop. SELECT @Counter = 0 WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) BEGIN -- update INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes. DELETE DummyTrans SELECT @Counter = @Counter + 1 END -- update EXEC (@TruncLog) -- See if a trunc of the log shrinks it. END -- outer loop SELECT 'Final Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),size) + ' 8K pages or ' + CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' FROM sysfiles WHERE name = @LogicalFileName DROP TABLE DummyTrans PRINT '*** Perform a full database backup ***' SET NOCOUNT OFF |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-21 : 17:25:36
|
I take it that you did not write this code. This part of the code is creating the query to do the truncate:@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'But why do you want to shrink it if you are just going to receive a performance penalty when it needs to expand? !!!Tara |
|
|
Hippi
Yak Posting Veteran
63 Posts |
Posted - 2004-12-21 : 23:01:42
|
[quote]Originally posted by tduggan I take it that you did write this code. This part of the code is creating the query to do the truncate:@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'But why do you want to shrink it if you are just going to receive a performance penalty when it needs to expand? !!!Tara[/quotte]Tara,So what will u do if the transaction log is too big?In my experience, I make a full backup, detach the database, and reattach after deleting the big log file.And I have a plan to do full backup everyday & transaction log every two hours.Do u think it's good enough?Thank a bunch. |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-12-22 : 01:36:44
|
If the transaction log is growing too big constantly, you simply increase the frequency of log backups (or log shipping). It's more than ok to do it even every 15 minutes, if required.Detaching, attaching etc is not a viable production procedure. It simply increases the downtime, disconnects all the users and could eventually ..... get you fired. On an occational, increase in log file size., simply shrink the log file after the next log backup.Hemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-22 : 04:16:22
|
I have a re-indexing job that runs weekly and after this jobs runs, the log ... increases by an additional 40GigHow about looking at re-indexeing less agressively?We fiddled around with a threshold for re-indexing - starting at 95% and working down until we saw a change in performance. This reduced the number of tables which needed reindexing. We also changed our maintenance routine to use DEFRAG, instead of REINDEX, on tables that had more than 10,000 pages [I think] to DEFRAG instead of REINDEX. (But I'm not sure if that made a difference to the Log size).Does reindexing a clustered index cause the other indexes on the table to be reindexed? If so then also reindexing those will boost the log file size too (i.e. if you are looping round all tables & indexes).What about REBUILD STATS - does that take much log space? Again, I think there is no point doing those after a REINDEX (although is a Reindex the equivalent of a FULL SCAN Rebuild Stats?)Kristen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-22 : 12:22:45
|
Completely agree with hgorijal. Kristen, no the reindex is not the equivalent of a full scan with update stats. MS has said that reindexing isn't necessary on small scale systems and that indexdefrag can be done on those systems instead. And indexdefrag doesn't use nearly as much tlog space as dbreindex. Oh and btw, MS said small scale systems are 10GB and under. I thought that was strange of them to say. But then again there test systems are a few terabytes.Tara |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-22 : 13:11:18
|
"[i]no the reindex is not the equivalent of a full scan with update stats[i]"So reindex does the equivalent of an UPDATE STATS with a partial/sample scan?, or NO Update Stats at all?Do I need to do REINDEX on indexes if I have already done ReIndex on a clustered index on that table? (does the reindex of the clustered index force recreation of all other indexes on that table?)ThanksKristen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-22 : 13:15:36
|
Reindexing doesn't do statistics. They are separate operations. IMO, update statistics should be run more often than dbreindex. Given the article that I read about dbreindex vs. indexdefrag, MS agrees too. I was doing dbreindex once a day, now I'm down to once a week. I do update stats once per day though. I also have the auto update stats option turned on the database as recommended by MS. BOL does not state that reindexing a clustered index forces recreation of the other indexes on the table. But I seem to recall someone making that claim here. I'm going to do some digging on this.Tara |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-22 : 13:18:33
|
Found it on Brad's site:quote: If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.
And Brad's a god when it comes to SQL Server performance. MS even referenced his web site once in a seminar I went to. Someone was asking about performance, and the MS guy referred him to Brad's site for the details.Tara |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-22 : 13:22:35
|
Hehe! Cheers Tara, that'll reduce Log space and wasted CPU cycles a fair bit.Enterprise / Platinum Web Maintenance Tools Version coming up - a Must Purchase option for our existing customers during this festive season!Krsiten |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-22 : 13:26:54
|
I think I'll be changing my dbreindex stored proc for this. It rebuilds all indexes currently. I need to adjust it a little so that on the tables that have a clustered index (which almost all should anyway), don't bother with the nonclustered ones. Not sure why I didn't consider this before.Tara |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-12-23 : 02:20:40
|
Kristen/Tara, Rebuilding all indexes in case of reindex on clustered was a bug in SQL 2000 which is fixed in SP2. All indexes are rebuilt only if the clustered index is NOT "unique".Here is a KB article on the bug....http://support.microsoft.com/default.aspx?scid=kb;en-us;304519So, unless your clustered indexes are not unique, you do not have to change your code. cheers...Hemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-23 : 12:17:00
|
I am so confused by that knowledge base article. So do the nonclustered indexes get rebuilt if you reindex a clustered index that is unique? And what about if it isn't unique?Tara |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-12-24 : 01:20:33
|
No; it's the other way around.NonClustered indexes get rebuilt if you reindex a clustered index that is NOT unique.Here is the deal... When a clustered index is initially created, all the non-clustered indexes are rebuilt to hold the "clustering key value" of the clustered index, instead of the RID(Row Identifier). Note that, in this case all the non-cluster indexes are not pointing to the phisical location of the data, but are holding only the corresponding clustering key value. This is the key factor for re-indexing behavior. When a "UNIQUE" clustered index is rebuilt, the "clustering key value" is not changing and so, even if the data pages have changed, it will not effect the non-clustered index as they are refering the datapages anymore.But, when a "NON-UNIQUE" clustered index is created, a 8-byte unique identifier is added to non-unique key for each duplicate row, making it a unique "clusturing key value". When this index is rebuilt, this 8-byte identifier is updated, if necessary. This causes the "clustering key value" to change, and there-by effecting the non-clustered indexes; resulting in their rebuild.In short...When you Rebuild a... Unique Clustered Index -- only that index is rebuilt. Non-Unique Clustered Index -- ALL indexes are rebuilt. Hemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-30 : 13:17:19
|
Thanks Hemanth! Here's my updated sproc:SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO------------------------------------------------------------------------------------------------------ OBJECT NAME : isp_DBCC_DBREINDEX---- AUTHOR : Tara Duggan-- DATE : May 11, 2004---- INPUTS : @dbName - name of the database-- OUTPUTS : None-- DEPENDENCIES : None---- DESCRIPTION : This stored procedure runs DBCC DBREINDEX for each of the indexes in the database.---- EXAMPLES (optional) : EXEC isp_DBCC_DBREINDEX @dbName = 'GT'---- MODIFICATION HISTORY :------------------------------------------------------------------------------------------------------ 12/22/2004 - Tara Duggan-- If table has a clustered index, just rebuild that index; otherwise rebuild all.---- 12/30/2004 - Tara Duggan-- If table has a non-unique clustered index, just rebuild that index; otherwise rebuild all.----------------------------------------------------------------------------------------------------CREATE PROC isp_DBCC_DBREINDEX(@dbName SYSNAME)ASSET NOCOUNT ONDECLARE @objName SYSNAMEDECLARE @idxName SYSNAMEDECLARE @SQL NVARCHAR(4000)DECLARE @ID INTDECLARE @RowCnt INTCREATE TABLE ##Indexes( Indexes_ID INT IDENTITY(1, 1) NOT NULL, IndexName SYSNAME NOT NULL, ObjectName SYSNAME NOT NULL)-- non-unique clustered indexesSET @SQL = ''SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName) 'SET @SQL = @SQL + 'SELECT i.[name], o1.[name] 'SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i ' SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o1 'SET @SQL = @SQL + 'ON i.[id] = o1.[id] 'SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o2 'SET @SQL = @SQL + 'ON i.[name] = o2.[name] 'SET @SQL = @SQL + 'WHERE i.indid = 1 AND 'SET @SQL = @SQL + 'o1.type = ''U'' AND 'SET @SQL = @SQL + 'OBJECTPROPERTY(o2.[id], ''IsUniqueCnst'') = 0 'EXEC sp_executesql @statement = @SQL-- nonclustered indexes but only tables that do not have non-unique clustered indexes; unique clustered indexesSET @SQL = ''SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName) 'SET @SQL = @SQL + 'SELECT i.[name], o.[name] 'SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i ' SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o 'SET @SQL = @SQL + 'ON i.[id] = o.[id] 'SET @SQL = @SQL + 'WHERE i.indid > 1 AND i.indid < 255 AND 'SET @SQL = @SQL + 'o.type = ''U'' AND 'SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0 AND 'SET @SQL = @SQL + 'o.[name] NOT IN (SELECT ObjectName FROM ##Indexes)'EXEC sp_executesql @statement = @SQLSELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectNameFROM ##IndexesORDER BY Indexes_IDSET @RowCnt = @@ROWCOUNTWHILE @RowCnt <> 0BEGIN SET @SQL = 'DBCC DBREINDEX(''' + @dbName + '.dbo.' + @objName + ''', ' + @idxName + ', 0) WITH NO_INFOMSGS' EXEC sp_executesql @statement = @SQL SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName FROM ##Indexes WHERE Indexes_ID > @ID ORDER BY Indexes_ID SET @RowCnt = @@ROWCOUNTENDDROP TABLE ##IndexesRETURN 0GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO I'll be updating my blog shortly.Tara |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-12-31 : 04:23:52
|
B-E-A-Utiful.It feels good to see such good, straight forward, no-nonsense code with NO CURSORS. I wish my developers would ever learn to do that. Hemanth GorijalaExchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each. |
|
|
dsweeney
Starting Member
3 Posts |
Posted - 2005-12-28 : 18:05:30
|
back to the original questionis the only solution to reduce log size not re-index as agressively?my problem is the same - we have a 70GB database trying to log ship to a remote site. everything works OK, until the weekly reindex - then the next log file is 35+ GB and takes over 3 days to transfer on a T1.Anyway around this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
dsweeney
Starting Member
3 Posts |
Posted - 2005-12-29 : 10:53:45
|
thanks for the link - I'll check the log fragmentation and see if we can use INDEXDEFRAG more often that DBREINDEX.the second comment brings up another question (about trans logs during the reindex)the trans logs fail during the reindex job. Is that by design or should they still be running (the databse takes several hours to reindex - like over 6 hours)?still, doesn't sql 2000 commit all the changes at once? that would still have a net result of a large log file. |
|
|
Next Page
|