InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 12:31:55 GMT20http://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspx[quote][b]Mac1986 (1/2/2013)[/b][hr]1)[b] I see that Indexes have heavy fragmentation levels:[/b] This effects the DML Performance for sure, but will this allow the data file size to grow extra than actual consumed space?[/quote]NO there is no 'extra' or 'actual consumed' kind of thing in index fragmentation level. framentation happens due to page split whch is dependent on fill factor.[quote][b]Mac1986 (1/2/2013)[/b][hr](2) [b]Will adding few indexes at appropriate places[/b] reduce the Data file to not pre capture so much disk space than actual consumed space.?[/quote] NO. index addition always cost you space/disk and it helps you to retrieve the data faster but slow down your DML operation for more details see this link [url]http://blogs.msdn.com/b/pamitt/archive/2010/12/23/notes-sql-server-index-fragmentation-types-and-solutions.aspx[/url]Thu, 03 Jan 2013 02:54:34 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxI have 2 quick questions on Data file size usage behavior 1)[b] I see that Indexes have heavy fragmentation levels:[/b] This effects the DML Performance for sure, but will this allow the data file size to grow extra than actual consumed space?2) [b]Will adding few indexes at appropriate places[/b] reduce the Data file to not pre capture so much disk space than actual consumed space.?Wed, 02 Jan 2013 15:53:57 GMTMac1986http://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspx[quote]Not going to affect the size or usage of the data file in any way.[/quote]sorry for my Friday hangover, I thought the complain is about log file :)Fri, 21 Dec 2012 01:37:07 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspx[quote][b]crazy4sql (12/21/2012)[/b][hr]why dont you change your recovery model to "bulk logged" while your ETLs are scheduled?[/quote]Not going to affect the size or usage of the data file in any way.Fri, 21 Dec 2012 01:35:34 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspx[quote][b]Mac1986 (12/21/2012)[/b][hr]I have done the things that you have suggested but still in the next ETL run, again the Datafile size is boosted up and used space by the file is way less.Can you please suggest. What can be done now..[/quote]If the space in the data file is used up and the file grows during ETL, then leave the data file alone, it needs to be the size it is.Fri, 21 Dec 2012 01:34:47 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxwhy dont you change your recovery model to "bulk logged" while your ETLs are scheduled?Fri, 21 Dec 2012 01:33:31 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxI have done the things that you have suggested but still in the next ETL run, again the Datafile size is boosted up and used space by the file is way less.Can you please suggest. What can be done now..Fri, 21 Dec 2012 01:29:05 GMTMac1986http://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxThis is the second time? If you shrink a database at all, it should be one-off job following a one-off large data deletion. If it grows "too large" again, then the DB needs extra space for its operations.One thing to look at is if the autogrow settings are too large. If you have a 5GB DB set to grow 5GB at a time and it results in 48% free space, then you might want to shrink again (to a size that that includes a more reasonable amount of free space), then set the autogrowth smaller to a setting that allows for growth but makes growths unlikely to happen very often.Tue, 18 Dec 2012 09:50:41 GMTdan-572483http://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspx[quote][b]Mac1986 (12/18/2012)[/b][hr]Also, what is size that can be on a general note can be determined as LOB? Can you possibly give an approx number?[/quote]Huh?LOB = Large Object. Nvarchar(max), Varchar(max), varbinary(max), XML data types.[quote]what are the preferred Database settings for these huge Databases.[/quote]Defaults unless you have a good reason to change work most of the time.Tue, 18 Dec 2012 03:17:51 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxActually we took extra space while deploying the databases and implementing the application. And now we need that space for new deployments in that server. So, long story short we need the space which is not used.Also, what is size that can be on a general note can be determined as LOB? Can you possibly give an approx number?what are the preferred Database settings for these huge Databases.Tue, 18 Dec 2012 02:53:44 GMTMac1986http://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxWhy are you ending up with so much free space that's not going to be reused?Tue, 18 Dec 2012 02:40:57 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxI mean this is 2nd time I'm doing this. Its pain to check on the Shrinkfile SPIDS if there are any blockings or something. It takes 6-7 hours for 1 data file and I have 8 files to go on 1 database. I have 6 databases on different servers like that in same condition.In your experience, how can I identify to maintain these huge databases that gets fragmented too often. Tue, 18 Dec 2012 02:32:17 GMTMac1986http://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxNo, it's just that LOB columns massively slow down shrinks because of the way they pages link together.Tue, 18 Dec 2012 02:30:42 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxIts the LOBs we have a huge column on few tables, is there a better way to maintain LOBs? Tue, 18 Dec 2012 02:24:59 GMTMac1986http://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxjust modify the initial space and auto-growth option.If you right click on your database and select properties, then go to files. You will see the initial size details of your data-file. Just modify this by leaving "actual+20-30GB space" in the initial size of your data or log file. Do it one by one, not all file at same time (ideal time is when full backup is finished).Then, change the auto-growth in MB instead of %. I will advice to review your database growth and then give sufficient amount of mb in auto-growth as well. Because auto-growth also cause the IO and sometime impact performance when luck is not favoring you. So if you see too much auto-growth then configure with sufficient amount of space (1 gb or 2gb)Tue, 18 Dec 2012 02:23:35 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxDBCC Shrinkfile is the only way to release unused space back to the OS.Let me guess, either lots of heaps or lots of LOB columns?Tue, 18 Dec 2012 02:19:35 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxShrink in small chunks, but then also remember to perform index and statistic maintenance as index fragmentation will be shot to pieces.Tue, 18 Dec 2012 02:16:39 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1397610-1550-1.aspxHi, I have few databases that have 8 data files allocated with 300 GB each and used only 110 - 120 GB each. So, I wanted to release the unused space back to the disk so that other databases can use the space.I'm using DBCC Shrinkfile option and its taking 7-8 hours per data file. Is there any other faster way to release the space back to the disk?Please suggestTue, 18 Dec 2012 02:08:24 GMTMac1986