September 25, 2009 at 8:06 am
I use SQL Server 2005, i noticed a low space on one of the drives. now we have different drives to store mdf and ldf files. as soon as i noticed low disk space on the drive i looked out for some old tables in the database that were useless for sure and i deleted some tables from the database. now even after deleting those tables the space in the drive is same. why is that so. any idea?
and one more thing the tables that i deleted were total of 10 GB.
September 25, 2009 at 8:33 am
Deleting tables will not change the size of the data files. Once you make the delete you will have to shrink the database to reclaim the space the tables were using.
September 25, 2009 at 8:49 am
But that will create lot of fragmentation. and i think after that rebiulding idexes is not a good idea since that will take space, so any idea how do i remove fragmentaiton.
September 25, 2009 at 10:00 am
any comment on this friends?
September 25, 2009 at 2:17 pm
Only way to release disk space to OS from SQL server is by 'Shrinking' as pointed above by LeeM.
**
Don't think it will increase index fragmentation, you can check that by using index_physical_stats DMV or by right clicking the database - Reports - Index Physical Stats, before & after you shrink databases.
**
I suggest you employ a DBA if you work in a Company. Pls donot just delete tables for simple stuff like getting OS space, don't know what else you are doing to the database when you don't know the product.
September 25, 2009 at 2:46 pm
dude i know the product i am a DBA but i am a newbie and i was told to delete certain tables by my manager because those tables were already there, and these table were a backup of those tables, and they were there for more than a year. did you get it. and now they were of no use. I exactly know i should not delete tables now n then to gain space. Atleast i know what might be the consequences if i just blindly delete it. and i am not doing any harm to the database befor doing anything i always make sure that i am doing the right think. So Mr. SQLRocker do not worry about me.
does that make clear bro.......
September 25, 2009 at 2:46 pm
so once again can someone answer please........
But that will create lot of fragmentation. and i think after that rebiulding idexes is not a good idea since that will take space, so any idea how do i remove fragmentaiton.?????????????????
September 25, 2009 at 3:31 pm
espanolanthony (9/25/2009)
so once again can someone answer please........But that will create lot of fragmentation. and i think after that rebiulding idexes is not a good idea since that will take space, so any idea how do i remove fragmentaiton.?????????????????
If you need to recover that disk space for use by the O/S, the only choice you have is to perform a shrink (DBCC SHRINKFILE). Once that is done, then you are going to have to perform an index rebuild/reorganize because the shrink WILL cause index fragmentation.
Okay, now that we have that out of the way - when you shrink (if you decide to, that is), make sure you shrink the file to a specific size that leaves plenty of space available for the index rebuild operation. If you don't leave space available, then the data file is just going to grow again and use up the space that you just released back to the O/S.
Personally, I would not shrink the file unless you know for sure that the database is not going to grow again - ever. If you anticipate that someone, at some time in the near future - is going to add data to the database, then you need to have space in the data file for that data.
I would recommend that you add storage to the system instead of trying to manage the space by shrinking the data file and letting it grow again. Also realize that shrinking/growing the data file (or log file for that matter) is going to cause file fragmentation which could affect performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 25, 2009 at 4:23 pm
Aside from Jeffrey's nice explanation, if you shrink, shrink to the level (target) that holds your data AND has space for a reindex of your largest table, AND leaves some free space for data growth over the next month or 3.
If that means you aren't gaining must space from the shrink, then you just need more disk space.
September 25, 2009 at 4:49 pm
Here's an option that might not work but if it does will avoid causing fragmentation.
use the truncateonly option in the shrink command. If the last used extent is at the end of the database nothing will happen but no harm done. you may be lucky and enough space will be returned to the OS to satisfy your requirements. Alternatively the database will shrink right down so you have no free space, in this case do a manual file growth afterwards to a suitable overall size in one chunk.
This works because truncateonly does not attempt to move data about, just chop the end of the file off.
use dbcc shrinkfile not shrinkdatabase.
Afterthought: do a reindex first, it should increase the chances of the end of the file being unused.
---------------------------------------------------------------------
September 28, 2009 at 1:28 pm
Yo espanolanthony, you made it clear that "you know the product but you are a newbie".. kinda explains it all..
September 28, 2009 at 1:46 pm
If you reindex, and then shrink without just truncating off the end, you may re-fragment the indexes.
I would recommend you shrink to a level that will let you work with your data, handle a reindex without growth, and normal data growth.
September 29, 2009 at 4:28 pm
since you NOW have different drives for Data and Log files, move the Database files to where they should be now...you did not state that you have moved the MDB and LDF files or not..I will assume not.
if you're going to shrink do it before, move your database file and re mount and then I would do a capacity plan on prospective growth and set the file sizes according to your findings, that will mitigate any (for now anyway) unnecessary fragmentation. of course re index after shrinking.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply