May 23, 2016 at 12:53 am
Hello Friends,
I am looking for advice/suggestion on how to reduce the amount of space on the database.
Right now the database size is 200GB(Mdf file is 196Gb and ldf file size is 6GB only)
Now i need to reduce the overall size of the database size with 20Gb available space.we have enough space on the drive but still we need a your suggestion to implement for this.
Currently
File Name CurrentSizeMBFreeSpaceMb
MDf file 194GB 100Gb
LDF file 6.8GB 6.5GB
I have run the below command for above results in database to analyze the amount of space allocated and unallocated space.
select
f.type_desc as [Type]
,f.name as [FileName]
,fg.name as [FileGroup]
,f.physical_name as [Path]
,f.size / 128.0 as [CurrentSizeMB]
,f.size / 128.0 - convert(int,fileproperty(f.name,'SpaceUsed')) /
128.0 as [FreeSpaceMb]
from
sys.database_files f with (nolock) left outer join
sys.filegroups fg with (nolock) on
f.data_space_id = fg.data_space_id
option (recompile)
For me Options like:
1.Should i go for Shrink datafile option to clear the space like batch process(500MB each shrink operation)
2.Should i check unused index or index fragmenetation on the database or should i inform the customer to delete the unwanted data from database?
its quite urgent for me ,Please help.Any help would be much appreciated!!
thanks in advance for your help on this matter
SQL server DBA
May 23, 2016 at 12:59 am
DBA - why do you need to do this? If you shrink it, over time it will grow again.
May 23, 2016 at 1:04 am
The one reason I can think of to shrink database files is so that you could restore to a smaller machine in the case of an emergency or your copying to a smaller Dev box or some such. Other than that, I agree... there's no reason to shrink database files because the free space doesn't get stored on backups and it's just going to continue to grow anyway.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2016 at 1:59 am
It's always a good idea to archive/delete unused/duplicate data. An easy way of freeing up some space.
May 23, 2016 at 2:53 am
The main point is that after a shrink operation ( other than truncate only ), you need to perform FULL maintenance on your database !
This may cause your LDF to need way more space than currently allocated to it, depending on the table size of the largest table;
Best is to have a good maintenance attitude, so your objects are optimal for your applications.
If there is no need to actually free space on disk, don't shrink ( except for the reason Jeff mentioned ).
If you decide to shrink, try to plan op front how much time it will need to complete and inform consumers up front!
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
May 23, 2016 at 3:09 am
Thank you all your suggestions\help
But still some questions about this.
Yes I understood about Fragmentation part But My intention is how to reduce space from Data file which is free space available around 100GB.if so what should i inform to customer? they needs reduce the database size of the database.
I have only below options like
1)need to inform the customer delete the unwanted data(i belive Customer wont accept)
2)Need to check fragmentation on the database but not sure it release the space.
3)Need to change the auto growth of the Data file but right now autogrowth enabled ( By2GB,unlimited)(But Customer wont accept) he clear stated that need to reduce the database size of the database.
File Name CurrentSizeMB
FreeSpaceMb
MDf file 194GB 100Gb
LDF file 6.8GB 6.5GB
When i checked the database properties shows Space available around 98GB and he need to reduce that to 20GB.
Please let us know your suggestions\concerns about this issue.
Any help would be much appreciated!!
SQL server DBA
May 23, 2016 at 4:04 am
If they want to reduce the size of the database permanently, then they need to archive/delete data.
Don't change the autogrow settings, they're fine as they are.
Shrinking might get you some free space back temporarily, but if there's something that needs that 100GB of free space in the DB (staging tables during loads or index rebuilds), then the DB's just going to grow again back to the same size.
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 23, 2016 at 6:53 am
Database admin(DBA) (5/23/2016)
Thank you all your suggestions\helpBut still some questions about this.
Yes I understood about Fragmentation part But My intention is how to reduce space from Data file which is free space available around 100GB.if so what should i inform to customer? they needs reduce the database size of the database.
I have only below options like
1)need to inform the customer delete the unwanted data(i belive Customer wont accept)
2)Need to check fragmentation on the database but not sure it release the space.
3)Need to change the auto growth of the Data file but right now autogrowth enabled ( By2GB,unlimited)(But Customer wont accept) he clear stated that need to reduce the database size of the database.
File Name CurrentSizeMB
FreeSpaceMb
MDf file 194GB 100Gb
LDF file 6.8GB 6.5GB
When i checked the database properties shows Space available around 98GB and he need to reduce that to 20GB.
Please let us know your suggestions\concerns about this issue.
Any help would be much appreciated!!
It would appear that you only have a total of 13.3GB of log file size. Find out what your largest index (both clustered and non-clustered, please) is and let us know so that we can help you make an intelligent decision as to how to proceed. There are ways to greatly reduce how much log file space you will need for the reindexing effort after the shrink.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2016 at 7:13 am
Jeff Moden (5/23/2016)
It would appear that you only have a total of 13.3GB of log file size.
Less. The results were Total Space, Free space, not used space and free space.
CurrentSizeMB 6.8GB
The MDF is almost half empty (Total 194, free 100). Unless there's been some recent archiving or that's a little odd. Curious if there's large loads happening through staging tables.
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 23, 2016 at 8:55 am
Thank you all for your information/suggestions.
But for your information,The requirement is need to reduce size of the database and here Data file free space available around 98.5 out 194GB.I need to reclaim the space from available space.(under database properties)
When i checked the database properties shows Space available around 98GB and he need to reduce that to 20GB.
If i suggest to customer to reclaim the space from available space to shrink operation in small batch process(like 500MB) until space released from database. the impact is fragmentation but i don't have any other solution for this since backup didn't work and log file size is very less(just 6Gb only)
I understood database fragmentation part,what is the best solution to reduce overall size from the Database or data file.
Please note my Intention is to avoid shrink operation against data file.
If any other alternatives is there to reduce the database size of the database.Please share with me,as same i will follow the steps in future.
Please let us know your suggestions\concerns about this issue.
Thanks in advance for your help on this matter
SQL server DBA
May 23, 2016 at 9:10 am
Database admin(DBA) (5/23/2016)
Thank you all for your information/suggestions.But for your information,The requirement is need to reduce size of the database and here Data file free space available around 98.5 out 194GB.I need to reclaim the space from available space.(under database properties)
When i checked the database properties shows Space available around 98GB and he need to reduce that to 20GB.
If i suggest to customer to reclaim the space from available space to shrink operation in small batch process(like 500MB) until space released from database. the impact is fragmentation but i don't have any other solution for this since backup didn't work and log file size is very less(just 6Gb only)
I understood database fragmentation part,what is the best solution to reduce overall size from the Database or data file.
Please note my Intention is to avoid shrink operation against data file.
If any other alternatives is there to reduce the database size of the database.Please share with me,as same i will follow the steps in future.
Please let us know your suggestions\concerns about this issue.
Thanks in advance for your help on this matter
Understood and we can help but I need you to answer the questions I asked about the sizes of indexes before I'll hazard a recommendation.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2016 at 10:59 am
Thank you jeff for your quick response.
Total size of the indexes is around 98GB.Waiting for your suggestions to proceed further.
SQL server DBA
May 23, 2016 at 12:43 pm
Hello Jeff,
Waiting for your suggestions on this. Kindly help me to process this issue further.
I look forward your positive response on this issue.
Thanks in advance your help on this matter.
SQL server DBA
May 23, 2016 at 4:26 pm
Database admin(DBA) (5/23/2016)
Thank you jeff for your quick response.Total size of the indexes is around 98GB.Waiting for your suggestions to proceed further.
Look at what I asked. I need to know just what the largest index is. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 24, 2016 at 8:53 am
Aside from Jeff's valid question about the largest index, I would like to know why: why does the database size need to be reduced. Yes, disk space is a little low. But as Gail pointed out: maybe the database needs to be the size that it is due to staging tables and the like. It might be a lot cheaper to add disk or put in a bigger disk. A 300 gig database is a respectable size, but it's not huge.
The unspoken part of what Gail said was that at the moment there may be 100 gig free, but at some point a process may run that will take a lot of that space, chew up some data, then clear itself out. If that's the case, there's no benefit to shrinking the database because the next time that process runs it'll just grow again. We don't know what the internal processes of your system are, or what its growth rate is.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply