June 7, 2011 at 1:16 pm
Hi,
I have two large tables that get updated daily or (added to).
I want to run this TSQL daily.
ALTER INDEX ALL ON DIM_Data REORGANIZE
and
DBCC SHRINKDATABASE (MyDB,10);
Say I have 100 uses, and the tables in the REORGANIZE code are used frequently.
Questions:
1) Should I goto to single mode before either of the above?
2) (if no is to 1), when code runs and used run a select over it, is there any risk.
NOTE: NO chance of INSERTS, DELETEs, or UPDATES while REORGANIZE runs.
But anything good be going on during the SHRINK code.
How do I handle the above code with many users, using the database??
June 7, 2011 at 1:39 pm
If you're intent on shrinking, don't waste your time defragmenting the indexes beforehand, as the shrink will result in them being far more fragmented than ever.
Shrink is something that should be done only if some unusual operation has grown the data or log file far beyond normal, or you've archived data and don't expect to reuse the space soon. Otherwise all you're doing is forcing SQL to expend time and resources growing the data file again.
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
June 7, 2011 at 1:41 pm
Digs (6/7/2011)
Hi,I have two large tables that get updated daily or (added to).
I want to run this TSQL daily.
ALTER INDEX ALL ON DIM_Data REORGANIZE
and
DBCC SHRINKDATABASE (MyDB,10);
Say I have 100 uses, and the tables in the REORGANIZE code are used frequently.
Questions:
1) Should I goto to single mode before either of the above?
2) (if no is to 1), when code runs and used run a select over it, is there any risk.
NOTE: NO chance of INSERTS, DELETEs, or UPDATES while REORGANIZE runs.
But anything good be going on during the SHRINK code.
How do I handle the above code with many users, using the database??
I hope you don't get flamed on the shrink operation, as it is one of the top 5 most worst practices ever !
You can find a little intro about it at http://www.sqlservercentral.com/articles/SHRINKFILE/71414/
With very good refs to the hot stuff.
A reorganize not do the most optimal reorganization and your shrink database will destroy any optimization anyway !
If you really, really, really need the shrink and cannot do without, first perform your shrink and then do full rebuilds ( not reorganize ! )
Check books online and the article I referred to for more info on the options !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 7, 2011 at 1:54 pm
phew...lucky I asked:w00t:
so I should
DBCC SHRINKFILE (MyDB,10);
ALTER INDEX ALL ON DIM_Data REBUILD
****ONLY on DOWNTIME, not automatically via job
QUESTION : MyDB bloats up from 500Mb, to 1000MB quickly after a full days operations, so i shouldnt be worried about being bloated??
June 7, 2011 at 2:16 pm
Digs (6/7/2011)
phew...lucky I asked:w00t:so I should
DBCC SHRINKFILE (MyDB,10);
?
Why Shrink??????
Have you done some archiving operation that has massively reduced the size of the database, size you won't be reusing? If not, all you're doing with that shrink is forcing the DB to immediately grow.
Do Not Shrink on a regular basis. Only after unusual events (archiving, bad maintenance or similar)
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
June 8, 2011 at 10:50 am
ROFL. No, don't shrink and, IMHO, don't reorganize unless you know the condition of the indexes. I have DBs that get 1M+ rows inserted / updated daily so I run a job nightly that queries the DMV sys.dm_db_index_physical_stats using the fragmentation values returned to determine whether to reorganize or rebuild the indexes individually. You can find a similar script in the Scripts Library.
June 8, 2011 at 10:55 am
GreyBeard (6/8/2011)
ROFL. No, don't shrink and, IMHO, don't reorganize unless you know the condition of the indexes. I have DBs that get 1M+ rows inserted / updated daily so I run a job nightly that queries the DMV sys.dm_db_index_physical_stats using the fragmentation values returned to determine whether to reorganize or rebuild the indexes individually. You can find a similar script in the Scripts Library.
http://sqlfool.com/2010/04/index-defrag-script-v4-0/
P.S. If 1 GB of data is an issue for you then you need to buy a bigger drive.
June 9, 2011 at 8:50 am
Digs (6/7/2011)
Hi,I have two large tables that get updated daily or (added to).
I want to run this TSQL daily.
ALTER INDEX ALL ON DIM_Data REORGANIZE
and
DBCC SHRINKDATABASE (MyDB,10);
Say I have 100 uses, and the tables in the REORGANIZE code are used frequently.
Questions:
1) Should I goto to single mode before either of the above?
2) (if no is to 1), when code runs and used run a select over it, is there any risk.
NOTE: NO chance of INSERTS, DELETEs, or UPDATES while REORGANIZE runs.
But anything good be going on during the SHRINK code.
How do I handle the above code with many users, using the database??
First SHRINK and then REORGANIZE.
SINGLE MODE is good.
You are using the same database daily for loading, then why do you SHRINK it?
June 9, 2011 at 8:59 am
SQL_Athmi (6/9/2011)
First SHRINK and then REORGANIZE.SINGLE MODE is good.
Rather don't shrink, just reorganise (or rebuild). No need for single user mode, both can be done without any risk with the DB in normal operation (though the tables being reorganised may be unavailable, depending what you're doing and how)
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
June 9, 2011 at 9:06 am
GilaMonster (6/9/2011)
SQL_Athmi (6/9/2011)
First SHRINK and then REORGANIZE.SINGLE MODE is good.
Rather don't shrink, just reorganise (or rebuild). No need for single user mode, both can be done without any risk with the DB in normal operation (though the tables being reorganised may be unavailable, depending what you're doing and how)
As a side note I have an app (Ms Dynamics Navision) throw not very elegant errors when doing a rebuild during prod hours (now keep it mind that I was also changing the fill factor that time). Normally I'm not getting any errors of that kind.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply