July 9, 2009 at 3:46 pm
Hi partners..
Mi configuration is.
OS:Windows Server 2003 R2 x64 Edition
Database: Sql Server 2000 sp4
Database size: 116 Gigs.
Disk Free space:32 Gigs
After delete some information from tables, i have15 Gigabytes free.
But when i want to shrink database, dont work.
i send shrink database from enterpise manager and stay processing for hours (at less 3) and never finish. i send shrink database with commands by query analizer, and finish without any result.
i can´t find info. about this problem, be cause i dont have any error code.
i send shrink files too with the same result.
table database have ntext columns, but i dont know if this can be an issue.
Any help will be apreciated.
pd. I send database to
Thankx
..............
Gpe. Nava
July 9, 2009 at 5:11 pm
The first thing I'd do is to rebuild all the indices on all tables, or at a minimum the tables you've deleted data from. You may have many free pages but very few free extents. Once you've done that have another go at shrinking the database.
You may also find it necessary to put the database into single-user mode before kicking off the shrink, as the shrink process may be getting blocked, and will definitely be slowed down greatly, by any parallel use.
I'd also recommend doing the shrink through Query Analyser rather than through Enterprise Manager. It may execute the same things under the covers, but it doesn't always so I prefer to know exactly what is being run. You generally get better and/or more complete errors/messages through Query Analyser, and they're often easier to work with if they're presented as text rather than in a window.
July 9, 2009 at 5:40 pm
After you do the reindexes.
It say do a
DBCC SHRINKDATABASE
and if that didn't clean up the log file enough I'd do a
DBCC SHRINKFILE
CEWII
July 14, 2009 at 4:54 am
Try the shrinkfile with TRUNCATEONLY argument:
DBCC SHRINKFILE('DATAFILE', TRUNCATEONLY).
Try the DBCC SHRINKFILE without the argument during off business hours as it can result in lot of data\pages movement.
DBCC SHRINKFILE('DATAFILE')
July 14, 2009 at 1:43 pm
Briefly.
Why do you want to shrink your database file? Do you have a pressing reason such as a critical discspace shortage that can not be addressed any other way?
Assuming that you are going to continue to add data - then the database is only going to have to grow again. This is going to require resources when it does, impacting on prformance, an most likely increase fragmentation, impacting on performance.
Best practice would actually be - identify the amount of space you're going to need over a decent period of time - say a couple of years, or lifetime of the system, whichever is shorter, assign that amount of space in one go. Keep an eye on it in case you have miscalculated and are using more space than expected so you can add more, as and when required, in a controlled manner. But keep autogrow on in case you get caught with your pants down.
Don't be shrinking stuff without good reason.
Hope this helps - any further questions, get back and I'll do my best to assist
cheers
Andrew
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
July 14, 2009 at 1:48 pm
Mind - a good point earlier - some index maintenance (SQLFool has a very well regarded script for reorganising and rebuilding - and there's some very good stuff on here if you do a search) would be a very, very good idea. Also do a search on update statistics - this may also help performance wise.
🙂
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
July 15, 2009 at 1:36 am
Have you run dbcc checkdb lately ?
I always use shrinkfile in stead of shrinkdb.
(what are the sizes of your db files)
Keep in mind your shrink operation will perform heavy IO. That is the slowes part of your server !
So overhead is to be expected !
Also make sure regular users are out of your db to prevent locking issues.
BTW if you expect your data to consume the freed 15Gb within a forseable periode of time, the shrink may actually not be such a good idea.
Double check your db growth settings ! (use grow in MB in stead of % )
However, your rebuild and cleanup operation will materialize in less backup file space needed !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 16, 2009 at 1:16 pm
ALZDBA (7/15/2009)
Have you run dbcc checkdb lately ?I always use shrinkfile in stead of shrinkdb.
(what are the sizes of your db files)
Keep in mind your shrink operation will perform heavy IO. That is the slowes part of your server !
So overhead is to be expected !
Also make sure regular users are out of your db to prevent locking issues.
BTW if you expect your data to consume the freed 15Gb within a forseable periode of time, the shrink may actually not be such a good idea.
Double check your db growth settings ! (use grow in MB in stead of % )
However, your rebuild and cleanup operation will materialize in less backup file space needed !
And a shrink of the mdf file will adversely impact your indexes. When you recreate the indexes, it's going to expand your mdf file, leading to a vicious cycle of expand/contract, expand/contract. If there is not a pressing disk need, don't shrink the file.
-- You can't be late until you show up.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply