Shrink Large DB File Size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Shrink Large DB File Size

Author

Topic

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-07-30 : 17:01:39

I have shrink the Database which is 192 GB and it gave back with this results.

Before it was rose to this level it was 41 GB but I could able to shrink to 162 GB still it is occupying around 120 GB.

Results
DBID FILEID CURSIZE MIN SIZE USED PAGES EST PAGES
5 1 21085208 5760000 21082984 21082984
5 2 5574424 239985 5574424 239984

I shrink the second time but no evidence of shrinking further.

Can any one tell what could be the reason, and what further it needs to be done.

Thanks all for the much needed guidance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-07-30 : 19:13:54

Why are you shrinking the database? How much free space do you have inside each file (this can be seen if you run the "Disk usage" report in SSMS)?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-07-30 : 20:36:33

Tkizer Thanks for responding, hope you will advise rightly.

I have consumed 95 percent through the Disk Usage, now the Organisation picking me to Shrink as much as possible but could able to shrink to 20 Percent back. but still It has around 120 GB occupied.

It all started when some databases have been added and last sunday schedued Reindex Automated job failed. from that day onwards Database space has almost 5 times to its original size.

Do you suspect there is fragmentation to indexes.

Please do suggest me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-07-31 : 00:16:42

If you are at 95% on the disk usage report, then a shrink isn't going to buy you much. You'll either need to delete data or add more disk space.

The reindex is to fix fragmentation. You can query a DMV to see what fragmentation each index has.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-07-31 : 02:07:19

there is heavy fragmentation occured and AVERAGE FRAGEMENT PERCENT for main tables were 99 %.

I have Defragemnted the Indexes

1. I have shrunk LDF but could not shrink the MDF, Can you help in this regard with the situation so far.

2. Is this could cause to GROW MDF and LDF file sizes,for the scenario above on last sunday which failed the REINDEX process.

Please help me.

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-07-31 : 02:13:12

I would like to add one more thing,

From Last Sunday when the Automated REINDEX failed ever since Database has grown to 5 times.

Later found fragementation and subsequently defragmented and later shrink but LDF shrunk to the desire limit but not MDF.

Thanks to you abundantly.

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-07-31 : 17:02:44

I have used like this for the database id as 8, object_id 1113627606 and index_id as 5 with the following command:

I use this example as I individually did the defrag.

DBCC INDEXDEFRAG (8, 1113627606, 5)

But IT DIDN'T DEFRAGMENTED.

I have serveral indexes like above which have not Defragmented

Can anyone please help me its serious with me.

need your valuable suggestion.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-31 : 21:38:34

how large are the new databases that were added?

sounds like you need to purchase more disk space, and in a hurry.

find a particularly large table with a highly fragmented index and rebuild it (not defrag it), see if it gains you some space. know that a fillfactor will take up some space, and that if the disk is 95% full, you may not have room anymore to rebuild index at all.

as Tara said, your options are really either delete data or get more disks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-31 : 21:39:13

this would also be a very good time to make sure your backups are good

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-08-02 : 18:38:35

Many thanks for your kind suggestions.

I have done the Reindex on all the tables.

It came to normal.

Pleae clarify me this which indicates frustration for me.

This when I ran the using sys.dm_db_index_physical_stats

NAME INDEXNAME AVG FRAG % ROWS
CHART IE_CHART_PK 99.99721456 9298276

What Shall I do?

Thanks Tara, Russel, your suggestions were working.......

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-08-04 : 00:07:06

Can you please advise me why the Fragmentation is still visible for me?

Its not working. Awaiting your guidance.

Thanks all

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-04 : 13:47:36

Well fragmentation can appear very quickly, so you'll need to analyze the indexes to see why fragmentation is occuring so fast after the ALTER INDEX.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-08-04 : 20:55:55

Thanks Tara

I have used sys.dm_db_index_usage_stats DMV and later analysed the Indexes there is bulk of indexes were still highly fragmented.

I have again reindexed but no use.

Can You educate me as to how I can defragment those indexes.

Thanks abundantly for your guidance as I exactly do them.

Looking forward to this solution as you might tell.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-05 : 15:06:50

You shouldn't bother rebuilding them unless the fragmentation is at least 50%. This is coming from a Microsoft engineer. He actually said Microsoft says 75% but said to be on the safe side just use 50%.

So what fragmentation levels are you seeing after ALTER REINDEX runs?

Also how many rows are in the tables that you are concerned about? Some can not be defragmented if they are small.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-08-05 : 18:26:22

Please suggest me based on this data

I am just giving some examples.

Like this there are many indexes are having more than 10000 rows.

I have around 2000 indexes on some specific tables were more than 80 %.

TABLENAME INDEXNAME AVG FRAG IN % ROWS

CHART SOE_CHART_PK 99.99721456 9298276

GENCMT SOE_GENCMT_PK 100 1958894

Thanks for the advise.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-06 : 13:40:43

Show me the ALTER INDEX command that you ran on these examples.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-08-09 : 18:18:33

Thanks Tara for continuous response and getting me towards the solution.

However, I have this command in the Stored Procedure which includes this part.

The Fill Factor is 80.

SET @comm = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@comm)

Please help me.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-09 : 23:41:00

what is your question at this point?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-10 : 13:36:51

Try changing the fillfactor to 100 since that is the recommendation. It is rarely advised to go below 100 these days.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-10 : 14:37:19

Not for Nothing.....

BUT

Doing this with Dynamic SQL sounds like you doing automated things for many different db's which may or may not need you to do this

MOO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-08-12 : 00:13:08

Thanks to all.

I have changed the fillfactor to 90 percent but no change,
and I have waited to execute and get the result and took this long and getting back to you all, Experts.

I am still unable to understand even after running DEFRAGMENTATION thereafter the Reindex worked fine, then I successfully shrinked the Data files. Still the Fragementation is showing up. This is really uanable to understand as I have mentioned above in one of the examples with TABLENAME, INDEXNAME, AVERGE FRAG% ROWS....

Can anyone please explain to me as this became notorius.

Thanks once again.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources