September 9, 2010 at 12:04 am
When you run the shrinkfile
Run sp_lock <spid which is running the shrink process>
Form there take the object id and find out the table name by running.
select object_name(<object id from previous step>)
It is quite possible that the table may be quite big without any indexes defined on it. Since there are no indexes the data is stored in a heap and shrink will take a lot of time. It may even take an hour to release just 2 MB. If this is the case then let your shrink run. It will release the space.
This used to be a bug in SQL 2000 where indexes had to be created on such tables. Then they had to be rebuilt before shrinking files successfully. But in SQL 2005 this bug was fixed.
September 10, 2010 at 7:00 am
If this is a fairly large database, you will want to check the large tables for clustered indexes. Tables on heap shrink row-by-row which is painfully slow. The shrink will go faster by an order of magnitude if the large (>50 MB) tables are clustered. Once shrunk, you will want to REORG indexes (NOT rebuild) to correct fragmentation issues.
September 10, 2010 at 7:14 am
isheej (9/10/2010)
you will want to REORG indexes (NOT rebuild) to correct fragmentation issues.
It depends on the fragmentation percentage , What says isheej :-)?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 10, 2010 at 7:37 am
isheej (9/10/2010)
Tables on heap shrink row-by-row which is painfully slow.
No it does not. Shrink works on pages and extents. It does not change the fullness of pages. It does not move rows from one page to another. It simply moves free pages to the end of the file and then releases that space to the OS.
It may be slightly faster to shrink when heaps are involved, as the upper levels of the index tree don't have to be scanned and updated when the leaf pages move.
What does cause shrink to run really slow is LOB pages, because of the lack of a back pointer in the LOB page chain. (for details see Paul Randal's blog)
Once shrunk, you will want to REORG indexes (NOT rebuild) to correct fragmentation issues.
[/quote]
Shrink usually causes high fragmentation (90%+). At that level, rebuild is indicated, not reorg.
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
September 10, 2010 at 7:40 am
If the objective is to regain space, you must not rebuild after shrink, no matter what percentage fragmentation. Rebuilding a large cluster can re-extend the data file with significant, if not total, loss of recovered space. A REORG only adds a single 8K page for sorting. Search for Paul Randall's excellent documentation on this topic.
September 10, 2010 at 7:42 am
isheej (9/10/2010)
If the objective is to regain space, you must not rebuild after shrink, no matter what percentage fragmentation.
But then you're stuck with very poor performance for range scans. The indexes will need rebuilding at some point.
Rebuilding a large cluster can re-extend the data file with significant, if not total, loss of recovered space.
Yes it can, which is one reason why shrinking is usually such a poor idea.
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
September 12, 2010 at 8:34 pm
I know shrinking is not so good idea. I have no option except shrinking to gain space and that too is on dev server.
Now what's happening is shrink opertaion is getting completed but with no result i.e. it's not shrinking the file at all inspite of having 80% free space available in the datafile.
September 12, 2010 at 9:04 pm
How many datafiles are in this database (not including log files)?
How much free space do you have on drive?
How space is the entire database taking ?
How much space is allocated for the entire database?
How much free is in the entire database?
Maybe you can list your datafiles allocation/actual/free sizes and pinpoint which one is giving you trouble?
Though I might suggest the following option, it would be helpful if you answered the above for better troubleshooting.
If you have enough free space, try creating another new datafile, then move data from your troubled datafile to the new one. You might be able to delete/shrink the old one then.
I still think if you have multiple datafiles, that you need to increase the allocated size of the FIRST one for needed overhead for the shrink.
September 12, 2010 at 9:52 pm
Lets see what's ur analysis is with below situation of mine.
-One datafile and one logfile
-Less that 10% free space on drive
-124 GB total db size
-Growth unrestricted
-80% free space available in datafile
-No scope of adding a new datafile
September 12, 2010 at 9:55 pm
How much is the free space on drive in GB/MB's?
September 12, 2010 at 10:09 pm
It sounds as if will need 125GB's * .20 = 25GB's for a new datafile.
If this is too tight of space available. Then I would recommend you add a drive (at least temporarily --would a 500GB USB drive work?).
--HIGHLY RECOMMEND you detach and copy your db to is for backup. or use SQL backup utility.
ReAttach. Then try adding file(s) to db, referencing the new drive. (I'd recommend you add 15 files allocated as 10GB's each on new drive--much easier to work with when you start getting this big....)
and re-distribute data to the other files by emptying the troubled data file--then delete/shrink as needed.
Hope this helps! and Good Luck!
September 12, 2010 at 10:22 pm
Adding a new drive is not reasonable in DEV box coz of shrinking not happening properly.
I was thinking of attach/detach earlier, but how its going to help?
September 12, 2010 at 10:30 pm
Detach/ReAttach won't help without a new drive to work with.
But here's some reasons why detach/reattach can help with new drive:
1 reason: With the file manipulation recommended, you should at least make a backup... Not possible with your limited space on one drive.
2 reason: Detach then copy to bigger drive and reattach from bigger drive, can help by giving you enough space for overhead to perform shrink.
September 13, 2010 at 2:17 am
Adding a new drive and then attach\detach is fine. But what if no new drive is added.
September 13, 2010 at 2:23 am
Vivek29 (9/13/2010)
Adding a new drive and then attach\detach is fine. But what if no new drive is added.
From c_c_ post
2 reason: Detach then copy to bigger drive and reattach from bigger drive, can help by giving you enough space for overhead to perform shrink.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply