Huge size of unused space problem

Question

Huge size of unused space problem

ashkan sirous

SSCrazy

Points: 2733
More actions
August 17, 2010 at 4:51 am

#226170

Hi,
I have a Data base witch has 50 GB reserved size but only 10 GB data.(40 GB Unused Space)
I have many images and pictures in my tables because i have an I Filter to detect and index the documents, so i can't store address instead of storing original images in DB.
What can i do to reuse my Space?
Thanks and regards
Ashkan
Best Regards,
Ashkan
August 17, 2010 at 6:01 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

stewartc-708166 (8/17/2010)
Have you tried to run SHRINKDATABASE?
USE [i]myDb[/i]
GO
DBCC SHRINKDATABASE(N'myDb' )
GO

Ummm.... no. Absolutely NOT the correct thing to do and that's actually some pretty bad advice. First, you need to find out WHY the database grew so large and fix THAT problem first or you'll be caught in a never ending cycle of growing and shrinking.

Second, shrinking a database should ONLY be done after you find and fix the problem and ONLY if you don't have the space to just leave things alone because shrinking a database does a lot of bad things to the database and the underlying disk system.

Third, if you've done 1 and 2 above, then you need to follow the database shrink with the rebuild of ALL indexes because one of the very bad things a shrinkDB does is it reorganizes the indexes without regard to proper order within the indexes. Another name for that little problem is "Extreme Fragmentation of Indexes."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ashkan sirous SSCrazy Points: 2733 More actions · Answer 2

There is 4 tables with Irrational size of reserved size

2 of them are very huge

1- MiscFileVersion data size = 16 MB yes,Mega Byte:w00t:, Unused = 25GB!!!!! do you belive?

27000 rows of data , 48 KB index size

2-FileVersion data size= 8.5 GB , unused = 15GB....

23000 rows of data , 48 KB index

the table design of the MiscFileVersion is

IDint40

FileIDint40

Contentimage160

FileNamenvarchar2500

Extensionvarchar100

Titlenvarchar2551

StatusFileStatusType (tinyint)10

FileTypeDocTypeCodeType (char)21

FileGroupnvarchar41

FileRevisionchar21

Descriptionnvarchar5001

Keywordnvarchar1001

FileDatevarchar501

ModifiedAtPersianDateType (varchar)100

IsLastbit10

theUserUserNameType (nvarchar)500

IsDeletedbit10

StartSheetNosmallint21

SheetCountsmallint21

EndSheetNoint41

and the table design of FileVersion is

IDint40

FileIDint40

Contentimage160

FileNamenvarchar2500

Extensionvarchar101

Titlenvarchar2551

FileRevisionRevisionType (char)21

FileTypeDocTypeCodeType (char)21

FileGroupnvarchar41

StatusFileStatusType (tinyint)10

theUserUserNameType (nvarchar)500

Descriptionnvarchar5001

Keywordnvarchar2501

ModifiedAtPersianDateType (varchar)100

IsLastbit10

WorkFlowStepint41

IsDeletedbit10

FileDatePersianDateType (varchar)101

StartSheetNosmallint21

SheetCountsmallint21

EndSheetNoint41

[Size]varchar101

I also have a weekly job , to optimize the DB, (sqlserver2000)

i have reorganize data and index pages ticked , and sets to change free space per page percentage to 10%

and

Remove unused space from Data base files

Shrink data base when it grows beyond 50 MB

amount of free space to remain after shrink : 10% of the data space...

Now please tell me what to do?

Best Regards,
Ashkan

ashkan sirous SSCrazy Points: 2733 More actions · Answer 3

@jeff Moden

Thanks Jeff ,

Please tell me how can i determine why this 2 tables takes this amount of unused space?

Best Regards,
Ashkan

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

So you have a database at least one of whose tables is gigabytes in size, and you also have a maintenance plan that attempts to shrink the database when it grows above 50MB? Re-read Jeff's post, and then lose that task from your maintenance plan! When you've done that, rebuild the clustered index on each of the two tables you mentioned. If there is no clustered index, you probably need to create one, either choosing a new clustering key or converting one of your existing non-clustered indexes into a clustered one. If you don't have a clustered index on your tables, then you're going to keep having this problem, the more so if you keep shrinking your database.

John

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

John's pretty much spot on. You need to check if the tables have clustered indexes. We also need to check what the clustered indexes are on. It is possible to have had page/extent splits so bad as to have made the tables and their related indexes grow like crazy just because of poor choices as indexes or no real index maintenance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ashkan sirous SSCrazy Points: 2733 More actions · Answer 6

thanks john , i will remove the check of "remove unused space..."

Do i need to remove "reorganize data and index pages" check?

also what will happened to my i filter?

Is it safe for it? the i filter took a month to search my tables...

--

Best Regards,
Ashkan

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Ashkan

You need to rebuild and/or reorganise your indexes regularly, so if you remove it from this maintenance plan then you should make sure you're doing it somewhere else.

I'm sorry but I don't know what an i filter is.

John

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 8

If you shrink regularly, you're creating fragmentation in your tables. Remove that from your system. If it grows beyond 50MB, determine why. don't just shrink.

ashkan sirous SSCrazy Points: 2733 More actions · Answer 9

Dear John,

My data base is use for a document management system, and i filter is a plug-in which i use to index my images(something like OCR index for images).

Anyway, I'm a newbie and i need to know what exactly do you mean.

What should i do know?

Thanks,

Ashkan

Best Regards,
Ashkan

ashkan sirous SSCrazy Points: 2733 More actions · Answer 10

Dear Steve,

Thanks for reply. I have stopped shrinking my data base.

Know Please tell me what to do?

How can i understand why my database grows beyond 50MB...

Thanks,

Ashkan

Best Regards,
Ashkan

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

Everything you need to know has already been posted here - you just need to do a bit of reading. To summarise:

(1) Create a clustered index on all tables that do not already have one

(2) Make sure you do not have any jobs that automatically shrink your database

(3) Rebuild your indexes regularly. You can do this through a maintenance plan or you can implement something more sophisticated - just search for index maintenance automation or something like that

Why are you so worried that your database is growing over 50MB? Your database needs to be the size it needs to be. You can control how much space indexes take up by rebuilding them, as mentioned above, but you probably can't control how much data users put into the database. Get the three points above sorted and then size your database files according to how much data there is now and how much you expect it to grow.

John

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 12

You need the space for data, and you need some pad space. This is for data growth and maintenance operations.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 13

ashkan siroos (8/20/2010)
Dear Steve,
Thanks for reply. I have stopped shrinking my data base.
Know Please tell me what to do?
How can i understand why my database grows beyond 50MB...
Thanks,
Ashkan

Although it's not step-by-step, John summarized what needs to be done in his previous post. I agree that such instructions neither can be nor should be step-by-step for something like this because all the steps are pretty well documented in Books Online. If you're the one who will be working with the server in this manner, then you need to learn it and make Books Online your best friend.

The other thing that I would recommend is that you talk with your boss and get some training on how to maintain an SQL Server. I know you have the brains for it because you've already identified that there is a problem and you've done it at the table level.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)