InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 19:47:30 GMT20http://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx[quote][b]TheSQLGuru (1/24/2013)[/b][hr][quote][b]Ninja's_RGR'us (6/17/2011)[/b][hr][quote][b]Dan.Humphries (6/17/2011)[/b][hr]You know I don't know that I really gave it much thought. My mind just said that made sense and I went with it. But now that I have read some of the replies I think I need to look into why it is taking so much longer.[/quote]My first instinct still stands. Heaps can have pages ALL OVER the place and cause this. I forgot who posted this but he had a table with a few 100 rows that had 15 GB space used (no blobs). Created clustered index and fixed the problem.[/quote]I too have seen multi-GB heap tables with a single row. :w00t:[/quote]I was the one with a table with a few 100 rows that had 15 GB space used.Just want to mention that you now have the option of doing a rebuild on a heap table without creating a clustered index:[code="sql"]alter table [MyTable] rebuild partition = all[/code]A clustered index would still be better, but at least it is another option to deal with it.Thu, 24 Jan 2013 14:49:05 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx[quote][b]deep.tkt (1/24/2013)[/b][hr]We have had a MSSQL database for data warehouse of size 1.5TB and recently with the purging and arching we were able to cleanup some data and now we have 900GB for free space in data files. We have two different data file sitting in different LUNs which are made from same disk array of EMC storage.Our goal is to shrink/resize database without hitting any snag with performance in most efficient manner. Should we shrink the database or data files or create new data files with smaller size and move objects to new files and drop bigger files when empty.If we use rebuilding clustered indexes to move objects between data file what is impact where there is 100GB clustered index ?[/quote]Personally I would build a new database (starting it with necessary size for all data to be migrated, their indexes AND 12-18 months of growth's worth of EMPTY SPACE) and migrate the data over. You can do that with a minimally-logged operations so it should be VERY fast. Build out your non-clustered indexes and you have everything just right: no internal OR external OR OS File fragmentation (assuming contiguous file space available on LUNs) AND 100% accurate/fresh index statistics too. Oh, don't forget to size the tlog properly from the get-go also. You may also want to manually create some column stats as well (or get SQL to do them automatically by stressing the system with expected reports) before turning it live.Thu, 24 Jan 2013 14:45:06 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx[quote][b]Ninja's_RGR'us (6/17/2011)[/b][hr][quote][b]Dan.Humphries (6/17/2011)[/b][hr]You know I don't know that I really gave it much thought. My mind just said that made sense and I went with it. But now that I have read some of the replies I think I need to look into why it is taking so much longer.[/quote]My first instinct still stands. Heaps can have pages ALL OVER the place and cause this. I forgot who posted this but he had a table with a few 100 rows that had 15 GB space used (no blobs). Created clustered index and fixed the problem.[/quote]I too have seen multi-GB heap tables with a single row. :w00t:Thu, 24 Jan 2013 14:39:26 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxWe have had a MSSQL database for data warehouse of size 1.5TB and recently with the purging and arching we were able to cleanup some data and now we have 900GB for free space in data files. We have two different data file sitting in different LUNs which are made from same disk array of EMC storage.Our goal is to shrink/resize database without hitting any snag with performance in most efficient manner. Should we shrink the database or data files or create new data files with smaller size and move objects to new files and drop bigger files when empty.If we use rebuilding clustered indexes to move objects between data file what is impact where there is 100GB clustered index ?Thu, 24 Jan 2013 12:08:04 GMTdeep.tkthttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx[quote][b]Dan.Humphries (6/17/2011)[/b][hr]You know I don't know that I really gave it much thought. My mind just said that made sense and I went with it. But now that I have read some of the replies I think I need to look into why it is taking so much longer.[/quote]My first instinct still stands. Heaps can have pages ALL OVER the place and cause this. I forgot who posted this but he had a table with a few 100 rows that had 15 GB space used (no blobs). Created clustered index and fixed the problem.Fri, 17 Jun 2011 15:08:36 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxYou know I don't know that I really gave it much thought. My mind just said that made sense and I went with it. But now that I have read some of the replies I think I need to look into why it is taking so much longer.Fri, 17 Jun 2011 12:16:05 GMTDan.Humphrieshttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxHere is a scenario where it makes sense.We have multiple Silos with many facilities assigned to different Silos. When we created the Silo3 databases, we basically just copied the Silo2 database to the Silo3 server, then activited the facilites for Silo3. On the prior Silo2 we deactivated those facilities that would now be maintained on Silo3.We had to do this because a data delete process did not exist yet. Now that we have a good data delete process, we can go back to Silo 2 and Silo 3 and remove the facility data that doesn't need to be on those systems. Delete Silo 3 data from Silo 2.Delete Silo 2 data from Silo 3.This will likely reduce the overall size requirements for both systems. After that shinking the .MDFs will be done.We are using Netapp storage, which allows us to also shrink the drives and give that space back to the SAN to be used elsewhere.We are talking about hundreds of GB worth of space that can be returned to the SAN.If we left all of that white space in the .MDF it would be a year or two before we fill it up again.Fri, 17 Jun 2011 12:06:07 GMTJamesMorrisonhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx[quote][b]Dan.Humphries (6/16/2011)[/b][hr]Not to beat the proverbial dead horse here but I thought I would point out one such situation where we do a regular shrink of database files. I am sure there will be more than a few disagreements to this but it works for us all the same. We have a database that normally runs in the range of about 100 GB and it stays that way most of the month. Once a month however they run a process that increases the size to nearly a TB. Now I know what you are thinking it must be a poorly designed process but this process is for a Power Substation and it pulls in data from something like 90 different servers and runs some calculations that when I tried to understand them frankly made me feel slightly insecure about myself. Now my belief with this situation is the DB is used 24/7/365 and I want to minimize impact. So after the process I reduce the size thus reducing the amount time it takes to run a full backup and other tasks. I am sure many will disagree but alas I do what works for the situation.[/quote]1) what happens to this 900GB of added data after the calculations? It MUST be deleted because you database goes back to 100GB after the fact (and you shrink it). Thus the deleted that 'chunks' up space could be 'recompacted' by doing index rebuilds. 2) Even if data is spread around extents throughout the 1TB file, you could apply the SP/CU that gets backup compression for SQL 2008 and your backups will drop back to 100GB worth of size (actually less) even without the shrink or index rebuild.3) Seems to me that (without many details here) the best idea BY FAR is to refactor this data import into a staging database and do processing with it back into the production database and then truncate the data in stage. You could leave that database at 1TB so it never has to grow.Fri, 17 Jun 2011 06:45:07 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxBy any chance, are the tables being hit really hard HEAPS?Thu, 16 Jun 2011 15:48:58 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx[quote][b]GilaMonster (6/16/2011)[/b][hr][quote][b]Ninja's_RGR'us (6/16/2011)[/b][hr]Ya but to pick only the used pages you must scan all pages (even if only the header). So that's an extra 200 - 900GB??? to extract from the san.[/quote]No you don't. You just have to read the GAM and SGAM pages every 2 or 4 GB (I forget which) of the file. They're allocation bitmaps that say what state the extents in their interval are in.One possible reason is lots of partially used extents. Shrink moves data page by page. Backup backs up extents where one of more pages have data. So if there's lots of places where an extent has only a couple used pages, the backup has to back the full extent up.If that is the case, index rebuilds will have the same effect as the shrink in 'compacting' pages and reducing backup time and size[/quote]Awesome... that's why we need mcms around here. So you can't stop now :hehe:.Thu, 16 Jun 2011 15:46:49 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx[quote][b]Ninja's_RGR'us (6/16/2011)[/b][hr]Ya but to pick only the used pages you must scan all pages (even if only the header). So that's an extra 200 - 900GB??? to extract from the san.[/quote]No you don't. You just have to read the GAM and SGAM pages every 2 or 4 GB (I forget which) of the file. They're allocation bitmaps that say what state the extents in their interval are in.One possible reason is lots of partially used extents. Shrink moves data page by page. Backup backs up extents where one of more pages have data. So if there's lots of places where an extent has only a couple used pages, the backup has to back the full extent up.If that is the case, index rebuilds will have the same effect as the shrink in 'compacting' pages and reducing backup time and sizeThu, 16 Jun 2011 15:42:58 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxYa but to pick only the used pages you must scan all pages (even if only the header). So that's an extra 200 - 900GB??? to extract from the san.That's the only way I see to have such a huge difference in the times... for the same backup file size.Thu, 16 Jun 2011 15:16:59 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx[quote][b]Dan.Humphries (6/16/2011)[/b][hr]The Full DB Backup runs about an hour when it is at a TB but less then 10 minutes when it is reduced to the 100GB size.[/quote]I don't see how - backup only backs up what is used. It doesn't matter how large the file actually is.So, if it only takes 10 minutes to backup 100GB - and an hour to back 1TB, it is probably backing up almost 1TB of data.Thu, 16 Jun 2011 15:02:14 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxThe Full DB Backup runs about an hour when it is at a TB but less then 10 minutes when it is reduced to the 100GB size.Thu, 16 Jun 2011 14:33:05 GMTDan.Humphrieshttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxHow does that reduce backup time?And by how much?Thu, 16 Jun 2011 14:18:49 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxNot to beat the proverbial dead horse here but I thought I would point out one such situation where we do a regular shrink of database files. I am sure there will be more than a few disagreements to this but it works for us all the same. We have a database that normally runs in the range of about 100 GB and it stays that way most of the month. Once a month however they run a process that increases the size to nearly a TB. Now I know what you are thinking it must be a poorly designed process but this process is for a Power Substation and it pulls in data from something like 90 different servers and runs some calculations that when I tried to understand them frankly made me feel slightly insecure about myself. Now my belief with this situation is the DB is used 24/7/365 and I want to minimize impact. So after the process I reduce the size thus reducing the amount time it takes to run a full backup and other tasks. I am sure many will disagree but alas I do what works for the situation.Thu, 16 Jun 2011 14:07:01 GMTDan.Humphrieshttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx[quote][b]TheSQLGuru (6/16/2011)[/b][hr]1) please tell the vendor that TheSQLGuru said they have their heads up their collective @sses. Also tell them that they need to get me or some other qualified consultant on board to review their database application for what is likely a HUGE range of suboptimal and out right bad issues. I just helped one vendor achieve a ~70% reduction in SQL Server waits and load from ONE SIMPLE CHANGE - and there is MUCH more to go with them!!2) NEVER EVER EVER leave a database file at 1MB growth factor!!!3) Always try to figure out how big a database will be 12-18 months down the road (data AND index sizes combined) and make all files appropriate for that number right now. Monitor at least quarterly. Autogrowth should be an EXCEPTIONAL event, not used to manaage your database size by standard.[/quote]I'm a little late to jump on the band wagon here. I'm totally with Kevin on this one. I deal with a vendor that I had to train on these points. The vendor was actually a dealer for a major Point Of Sale system.Their usual database setup was 1MB growth for both data and log files. Their usual maintenance plan had shrinking the database after re-building indexes at night. Ouch!!These guys knew the app very well and were very well intentioned, but didn't have a clue about database setup or maintenance. After a few customer complaints on performance and me fixing only those 2 points - database growth and NOT shrinking the database after the index re-build, I've found that their usual installations are exactly what I recommended and we're all a happy lot now.Todd FifieldThu, 16 Jun 2011 12:26:11 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxSometimes (often) you just need to ignore what a vendor says because you know more than them.I ignore vendors all the time when they start spouting nonsense like that.You are not alone; read through the thread below if it offers any comfort. You're on there!That Darn Vendor[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139011[/url]Thu, 16 Jun 2011 07:19:47 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx1) please tell the vendor that TheSQLGuru said they have their heads up their collective @sses. Also tell them that they need to get me or some other qualified consultant on board to review their database application for what is likely a HUGE range of suboptimal and out right bad issues. I just helped one vendor achieve a ~70% reduction in SQL Server waits and load from ONE SIMPLE CHANGE - and there is MUCH more to go with them!!2) NEVER EVER EVER leave a database file at 1MB growth factor!!!3) Always try to figure out how big a database will be 12-18 months down the road (data AND index sizes combined) and make all files appropriate for that number right now. Monitor at least quarterly. Autogrowth should be an EXCEPTIONAL event, not used to manaage your database size by standard.Thu, 16 Jun 2011 07:17:47 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx[quote][b]RichardDouglas (6/15/2011)[/b][hr]You seem to have a pretty good handle on this, but with all due respect I think you may be going about this the wrong way. Your time is precious and should be spent doing more productive things, step back a little and take a look at the bigger picture...Your company are paying them (probably a substantial amount) for their service and they have supplied this documentation to you. It is not unreasonable to ask them to supply the reasons why you should implement their suggestions and ask them to back it up with the relavant Microsoft technical articles and baselines that prove their statements are indeed correct. Of course they will not be able to do this, then you can step in with collated advice from this thread as your counter argument. Definitely list this MS technical article in your arguement - [url=http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx]http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx[/url]We've all been there and unfortunately sometime it becomes more of a political argument than a technical one at times. Companies tend to favour external knowledge rather than internal purely because they pay more for it.Good luck![/quote]You are absolutely right. It can be very frustrating when you are the one caught in between the vendor and the business. Especially when asking these questions, the business side will sometimes see it as you are being difficult and hard to work with even though they don't understand. But this can typically be cured with the proper explanation.Thank you all for your input. I very much appreciate it.Wed, 15 Jun 2011 12:05:19 GMTBoaz51http://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx[quote]Could you please elaborate on that a little more where you could introduce physical fragmentation here.[/quote]Just as you said, it's going to happen if you're releasing free space and especially if you're using small growth increments. I've seen a few places with shrinks as part of their "maintenance plans", and these three decisions often seem to go together. If you're not doing that then I don't think you'll be having much impact on disk fragmentation. You'll just be wasting time ;-)Wed, 15 Jun 2011 05:12:11 GMTDon Halloranhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxYou seem to have a pretty good handle on this, but with all due respect I think you may be going about this the wrong way. Your time is precious and should be spent doing more productive things, step back a little and take a look at the bigger picture...Your company are paying them (probably a substantial amount) for their service and they have supplied this documentation to you. It is not unreasonable to ask them to supply the reasons why you should implement their suggestions and ask them to back it up with the relavant Microsoft technical articles and baselines that prove their statements are indeed correct. Of course they will not be able to do this, then you can step in with collated advice from this thread as your counter argument. Definitely list this MS technical article in your arguement - [url=http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx]http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx[/url]We've all been there and unfortunately sometime it becomes more of a political argument than a technical one at times. Companies tend to favour external knowledge rather than internal purely because they pay more for it.Good luck!Wed, 15 Jun 2011 03:27:17 GMTRichardDouglashttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxI wonder if you should just partition the huge tables with index aligned to the partitions and using a high fill factor and then just forget about it.If your worried about fragmentation and the impact it has on I/O there are other factors that should be taken into account before your going into discussions of shrink db. E.g choosing the right raid to help reduce the impact of fragmentation to some extent , with read ahead optimization you would be able to get away with it.Also things like partitioned tables etc help make sure historical data doesn't require the same amount of management as new data. Capacity planning is another factor , if your discussing the shrink db option too early in your implementation your sizing needs to be re-looked. Drop indexes which are not used and basically monitor the perf mon counters to see the rate at which reads and writes are happening and check if the indexes get fragment too often. Shrink db is just a quick and dirty solution for when you dont want to tune your database. Its not always the right answer just a work around.Wed, 15 Jun 2011 00:08:24 GMTJAYANTH KURUPhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxI completely agree with you guys and did before I even posted. I was trying to gather as much technical detail as I can so that I am prepared to combat their recommendation. [quote][b]GilaMonster (6/13/2011)[/b][hr][quote]Regular rebuilds will sort out the partially empty pages problem (from deletes and inserts causing page splits). The shrink-reindex (grow) just wastes a hell of a lot of time and resources for no gain[/quote][quote][b]allmhuran (6/13/2011)[/b][hr]Each operation essentially "undoes" the other in terms of logical fragmentation.Shrinking the database takes pages from the end first, and moves them to free space in the front. Rebuilding an index will try to lay out the pages on disk in order. Doing both sequentially is worse than just wasting time, because what you can end up with is underlying disk fragmentation.[/quote]I understand how it could be redundant to move a page up to the front with the shrink then turn around and move it with the rebuild. I guess I need to get more info from them on exactly how much data is being inserted and deleted from these tables. If it helps at all, this is Enterprise Vault and the DBs can range from 20 gig to 180 gig.I dont see how you would get underlying disk fragmentation if you are not releasing any of the file free space back to the OS. I do understand that if the file were full and we were rebuilding large indexes while autogrowing 1MB at a time or we released all the free space and grew it again, we would be introducing physical fragmentation. In this case, there would be plenty of space in the data file. We also dont have any control over the physical disk fragmentation, i believe that is handled on the SAN by that group. We are pretty much presented a LUN and thats about as much say so as we have for disk. Could you please elaborate on that a little more where you could introduce physical fragmentation here.ThanksTue, 14 Jun 2011 15:19:16 GMTBoaz51http://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxThere are valid reasons to shrink databases (out of space is seldom a good one), but not on a regular basis.Tue, 14 Jun 2011 04:48:03 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxyou can do shrink the database in only 1 special circumstance when only 1 door is open ,you are completely lost with space and there is no any other option you haveTue, 14 Jun 2011 03:40:26 GMTSyed Jahanzaib Bin hassanhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxAs mentioned already Defrag over shrink db :D however i can think of one place where a shrink db could be a good idea. Again this might not be applicable in your case.Say your using a staging database for some kind of ETL process where every week your loading data from different datasource. There would be no harm and probably some good by shrinking this staging db as the space freed could be used by temp db for the next stage of data cleansing.Hypothetically speaking of course ...Tue, 14 Jun 2011 02:56:48 GMTJAYANTH KURUPhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspx[quote][b]Boaz51 (6/13/2011)[/b][hr]I am currently working with a vendor that highly recommends doing a shrink data file or "shrink database" in their documentation once a week. They recommend this because they believe it is beneficial to remove the "white space" in the data file because of high volumes of inserts and deletes. [/quote]They are wrong. There's nothing wrong with free space in the DB, SQL in fact needs it.It was MS Access where you had to compact and repair on a regular basis if you didn't want the file to become unusable. SQL Server != MS Access.Regular rebuilds will sort out the partially empty pages problem (from deletes and inserts causing page splits). The shrink-reindex (grow) just wastes a hell of a lot of time and resources for no gainMon, 13 Jun 2011 23:59:02 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxEach operation essentially "undoes" the other in terms of logical fragmentation.Shrinking the database takes pages from the end first, and moves them to free space in the front. Rebuilding an index will try to lay out the pages on disk in order. Doing both sequentially is worse than just wasting time, because what you can end up with is underlying disk fragmentation.In short, don't do shrink > rebuild or rebuild > shrink. You should only do a shrink if you're desperate for disk space and don't have time to wait for your infrastructure team to increase your allocation. Or, for the log files, if you're trying to clean up fragmented VLFs.Mon, 13 Jun 2011 20:35:31 GMTDon Halloranhttp://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxYes, I can think of ramifications - index fragmentation, file fragmentation - and basically, just a waste of time and resources.If you are constantly shrinking a file - and then it is growing again, that is going to cause file fragmentation. It also causes index fragmentation, which you then have to perform an index rebuild/reorganize to clear up - which will probably cause the file to grow again.There is absolutely no performance issues with have extra 'white' space available in a database. In fact, it is often desirable to have that space - not only for future growth and to avoid auto growing that file at the worst time (say 8am Monday morning), but also to manage your index rebuilds without having to suffer the wait for the file to grow.All in all - that is not very good advice.The only time you should consider shrinking a file is when there has been an extraordinary growth and you know you will never use that space again. For example, you copied the database to a test system - removed most (or all) of the data to build a blank copy. Or, someone ran a bad query that caused the database to double in size, etc...Mon, 13 Jun 2011 20:35:13 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic1124708-391-1.aspxHello All,Long time reader, first time poster.While I agree that shrinking a data file is a bad thing in most cases, I wonder if it could be a good thing in special circumstances.I am currently working with a vendor that highly recommends doing a shrink data file or "shrink database" in their documentation once a week. They recommend this because they believe it is beneficial to remove the "white space" in the data file because of high volumes of inserts and deletes. In their documentation for the shrink database task they specify to retain the freed file space in the file and to leave 10% free space(which could be altered). So I don’t see where you would get a lot of filesystem fragmentation here. After that they want us to rebuild indexes and update stats(which we already do) . This would take care of the fragmentation from the shrink.I am wondering if anyone could think of any ramifications of shrinking data files in this case.Any input would be appreciated.Thank you,JoshMon, 13 Jun 2011 20:00:39 GMTBoaz51