on 06-09-2010 2:27 PM
Hi ,
We have ECC 6.0 system on Windows 2003 and SQL SERVER 2005 database. There were hugh number of spools request deleted by us.
Now DB02 is showing this :
Number of files 5
Total Size [MB] 402,900
Allocated [MB] 302,727
Free [MB] 100,173
We want 100GB free space in OS level that is inside the SQL server .The affected table was TST03 .
Is there any procedure / document to recover this space in OS level ?
Thanks
Mukul
Hi dudes!
I agree with Mushtaq_M that you can shrink your files, and also that this is not recommended; some DBAs become very nervous if they see some tens of Gb allocated by the database but free and which space cannot be returned to the file system.
Of course, you can shrink your database files. It is explained in note *363018* (points D and E). In fact, under certain circumstances it could be necessary to shrink your transaction log, as per note *625546* and note *421644*.
However, regarding your particular question, I would say that it could have a 'bad' effect: you would waste your resources or even hinder the performance during the time it takes the database shrinking and you wouldn't get a performance improvement in return. In fact, sooner or later your database will probably grow up to its current size or even more. Even more, as a rule of a thumb, it is recommended that each datafile has at least around 10-15% of free space.
At last, let me point out that you should not rely on SQL Server's "autogrow" function: this is recommended just to prevent the emergency situation that the database stops working because there is no free space left at all, but you are recommended to monitor and manually increase the datafiles whenever necessary.
You can also refer to the SAP Help Portal for further help on the +MS SQL Server DB administration in CCMS+ as well as on the +Database Management Tools+. You should always check the +Microsoft SQL Server 2005 *Books Online (BOL)*+
Cheers!!
-Jesú
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Mukul,
have a look at:
SAP KBA 1721843 -
SQL Server: Database-related post processing after freeing a significant amount of space
This KBA clarifies all related questions.
Regards,
Beate
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The only way i can think of is to shrink your data files using DBCC Shrink command, which is highly not recommended.
you need to consider consequences of shrinking database v/s the space to be reclaim. Please go through the link below.
[http://www.karaszi.com/SQLServer/info_dont_shrink.asp]
[http://sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
90 | |
10 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.