May 16, 2011 at 6:33 am
HEY, RTFM.
If you don't want our help stop asking for it.
May 16, 2011 at 6:39 am
thanks for everyone reply on this post
May 16, 2011 at 6:42 am
naresh.talla (5/16/2011)
in my jobstep 1-- re indexing
EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")'
DBCC DBREINDEX is deprecated, should not be used for new development, will be removed in a future version of SQL.
DECLARE @table_name varchar(1000),@sql nvarchar(4000) declare c1 cursor for SELECT name FROM sysobjects WHERE xtype = 'U' and name not in ('tbl_check')
sysobjects is deprecated, should not be used for new development, will be removed in a future version of SQL.
open c1
fetch next from c1 into @table_name
while @@Fetch_Status = 0
begin
Select @sql = 'UPDATE STATISTICS '+ '[' + @table_name + ']' +' WITH FULLSCAN'
--print @sql
exec sp_executesql @sql
fetch next from c1 into @table_name
end
close c1
deallocate c1
GO
Waste of time as you're updating stats that the reindex just updated.
step 3-- update usage
DBCC UpdateUsage (DatbaseName)
Does not need to be run on a regular basis on SQL 2005 and above. There were bugs in the page space algorithms in SQL 2000, they were fixed in 2005.
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
May 16, 2011 at 7:04 am
thanks for your post.....
May 16, 2011 at 7:36 am
Ninja's_RGR'us (5/16/2011)
#1 culprit is reindex job
I had the impression that reindex jobs increase the size of log file (ldf) not the data file(mdf)
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
May 16, 2011 at 7:38 am
I take transaction log backups very frequently inbetween reindexing steps to prevent big .ldf growth.
May 16, 2011 at 7:38 am
SKYBVI (5/16/2011)
Ninja's_RGR'us (5/16/2011)
#1 culprit is reindex jobI had the impression that reindex jobs increase the size of log file (ldf) not the data file(mdf)
Regards,
Sushant
I was the reindex job that was doubling the data size... it's that 99.99% of the time.
May 16, 2011 at 7:40 am
SKYBVI (5/16/2011)
Ninja's_RGR'us (5/16/2011)
#1 culprit is reindex jobI had the impression that reindex jobs increase the size of log file (ldf) not the data file(mdf)
Regards,
Sushant
Both actually. During the reindex a new index is created during the sorting. It takes up to 1.5 X the size of the original index.
If you happen to have 1 very large table then the db data files need to grow to accomodate it.
May 16, 2011 at 7:40 am
SKYBVI (5/16/2011)
Ninja's_RGR'us (5/16/2011)
#1 culprit is reindex jobI had the impression that reindex jobs increase the size of log file (ldf) not the data file(mdf)
Rebuild can increase both. The log because the rebuild is logged (fully in full recovery, minimally in bulk-logged and simple). The data because SQL needs somewhere to put the new index, preferably somewhere contiguous.
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
May 16, 2011 at 7:45 am
homebrew01 (5/16/2011)
I take transaction log backups very frequently inbetween reindexing steps to prevent big .ldf growth.
Can full backups, transaction log backups and rebuilding index go at the same time?
Isnt there any interference between them.
Regards,
SKYBVI
Regards
Sushant Kumar
MCTS,MCP
May 16, 2011 at 7:51 am
Probably could go at the same time, but why ?
I run in series, not parallel:
Rebuild index
Rebuild index
backup t-log
Rebuild index
Rebuild index
backup t-log
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply