May 16, 2011 at 4:14 am
Hi all,
My Production Database is around 280 GB.
my primary data file is around 130 GB on every sunday it primary file suddenly grows upto 220 GB.
I checked it with sp_spaceused In that 70 GB is unallocated.I have compact the database,but this unexpected data file grows on every sunday i am not understanding why it is happening.Even there is no process running on sunday except full backup.
May 16, 2011 at 4:31 am
I guess some hidden scheduled task is working on the database during the weekend.
I would suggest capturing statements with a server-side trace and look at the results on monday.
-- Gianluca Sartori
May 16, 2011 at 4:37 am
i have checked it but there is no hidden tasks are running on sunday
i have run profiler also i couldn't find anything on that...
May 16, 2011 at 5:04 am
Files don't grow for no reason. Put some logging in place to record the file sizes, that'll narrow down the time range for you to profile/trace.
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 5:24 am
It is because full backup occur..
May 16, 2011 at 5:27 am
#1 culprit is reindex job
#2 is big dataloads
So which one is it?
Keep in mind that you can have something like backupexec or any 3rd party app connect to the server and start those for you.
I actually had a situation where the full backups and checkdb were ran twice daily... for no good reason!
May 16, 2011 at 5:29 am
naresh.talla (5/16/2011)
It is because full backup occur..
No way... full backup doesn,t write anything to the data files... it makes a copy in another file completely.
I'd check your maintenance plan for reindex job... it's almost always the culprit for something like this.
May 16, 2011 at 5:53 am
naresh.talla (5/16/2011)
It is because full backup occur..
Nope. Backups don't grow the DB. They write a couple history rows to MSDB, but that's all.
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 5:53 am
i have find one job re indexing running on sunday...
that might be the issues...
But how to maintain that data file not to grow
May 16, 2011 at 5:57 am
naresh.talla (5/16/2011)
i have find one job re indexing running on sunday...that might be the issues...
But how to maintain that data file not to grow
Don't reindex the whole DB, only what needs to be reindexed.
May 16, 2011 at 5:58 am
GilaMonster (5/16/2011)
naresh.talla (5/16/2011)
It is because full backup occur..Nope. Backups grow the DB. They write a couple history rows to MSDB, but that's all.
Might want to re-edit that one!
Backups DON'T grow the db.
May 16, 2011 at 6:08 am
But i want to re index all tables in database...
all tables are heavily fragmented
May 16, 2011 at 6:11 am
naresh.talla (5/16/2011)
But i want to re index all tables in database...all tables are heavily fragmented
That script WILL work all the indexes that need to be reworked.
If you want to control growth then you need to find a way to run that job daily... even multiple times daily.
Moreover if indexes get fragmented right after reindex then maybe you need to stop worrying about those or consider altering the indexes or their fill factor.
May 16, 2011 at 6:12 am
naresh.talla (5/16/2011)
But i want to re index all tables in database...all tables are heavily fragmented
you should have a look at the script that was linked earlier, this will only re-index the tables that need it.
It may be that since all your tables are fragemented it may have to do every table but after that you shouldn't have to reindex every table every week.
May 16, 2011 at 6:31 am
in my job
step 1-- re indexing
EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")'
step 2--- update statistics
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')
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
step 3-- update usage
DBCC UpdateUsage (DatbaseName)
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply