Optimizing and shrinking large highly-transactional database

Hi,
I have a 50GB database on SQL Server 2000 Enterprise that is highly
transactional in a 24x7 environment. There's approximately 8 GB of
free space, so I want to shrink it. It has been online for over five
years and has never been defraged or reorganized. I've been given a
maximum downtime window of eight hours. A test defrag on a copy of the
database ran for almost two days (single user mode, quad-cpu server).
I can't risk deadlocks with this system (lots of foreign key
contraints, etc.), so I am unable to run the defrag while it is
online.
I'm wondering if moving all of the data to a new file in the same
filegroup would be faster and also eliminate the existing
fragmentation and possibly truncate the free space. Does anyone have
any suggestions?
Thanks!
First off I would highly recommend you DONT shrink the files. 8GB of free
space in a 50GB db is the minimum free space I would like to see. You need
free space to minimize fragmentation and for operations such as reindexing
to work properly. See here for more details:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
But two days to rebuild less than 50GB of indexes is pretty pathetic and I
suspect something else is wrong. Make sure the log files for the user and
tempdb databases are on a separate raid array than the data and preferably
on a Raid 1 or Raid 10. What is your current disk setup like? But if you
want to defrag everything you may get faster results by BCP'ing out all the
data in each table, truncating the tables and BCP or Bulk Inserting back in
again. I would drop all the non-clustered indexes before importing back in
and re add them afterwards. I would also use an Order by clause when
exporting to export them in the order of the clustered index to give the
best chance of a clean and fast import for the clustered index. If you
export the data to a disk other than the one the data or log files are on
this should be a relatively quick process. But again even if you simply
reindexed each table one at a time with 4 processors it should only take a
short while.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Curtis" <curtmorrison@.yahoo.com> wrote in message
news:1d585c72-be6c-4913-a18d-5e38c6aa1bcb@.w34g2000hsg.googlegroups.com...
> Hi,
> I have a 50GB database on SQL Server 2000 Enterprise that is highly
> transactional in a 24x7 environment. There's approximately 8 GB of
> free space, so I want to shrink it. It has been online for over five
> years and has never been defraged or reorganized. I've been given a
> maximum downtime window of eight hours. A test defrag on a copy of the
> database ran for almost two days (single user mode, quad-cpu server).
> I can't risk deadlocks with this system (lots of foreign key
> contraints, etc.), so I am unable to run the defrag while it is
> online.
> I'm wondering if moving all of the data to a new file in the same
> filegroup would be faster and also eliminate the existing
> fragmentation and possibly truncate the free space. Does anyone have
> any suggestions?
> Thanks!
|||On Nov 21, 3:50 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> First off I would highly recommend you DONT shrink the files.
After giving this some more consideration, I agree...

> What is your current disk setup like?
It looks like the data and logs are on different partitions on a SAN.
I don't have any additional seperate physical partitions to play with.

> But if you want to defrag everything you may get faster results by BCP'ing out all the
> data in each table, truncating the tables and BCP or Bulk Inserting back in
> again.
Exactly my thoughts in the first place, but the system is so complex
and so un-documented... this is a last resort... I think this may
Inevitably be the route I need to take.
|||Different logical partitions on the same physical array do not give any
performance benefits. When you do a resource intensive operation such as a
reindex the data and log activities will contend with each other on the same
physical array. One thing you can do to minimize this effect and to speed up
the overall operation is to put the database in Simple recovery mode so the
index rebuilds will be done with a minimally logged operation. That will
minimize the data going to the transaction logs. Make sure to have a valid
full backup before you start, change the recovery mode to Simple and then
rebuild all the indexes one table at a time with DBCC
DBREINDEX('TableName'). A 50GB db with a 4 processor system should only
take a few hours even with poor disks. Make sure the MAXDOP of the server is
set to 0 or 4 so you get maximum parallelism when rebuilding. I would test
this first on a copy of the db just to ensure you have it all down right.
Then change the recovery mode back to Full (if that's what you had before)
and make to take another FULL backup when done.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Curtis" <curtmorrison@.yahoo.com> wrote in message
news:313d59e5-33d9-4161-8d4c-1f7fa2b91c27@.v4g2000hsf.googlegroups.com...
> On Nov 21, 3:50 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
> After giving this some more consideration, I agree...
>
> It looks like the data and logs are on different partitions on a SAN.
> I don't have any additional seperate physical partitions to play with.
>
> Exactly my thoughts in the first place, but the system is so complex
> and so un-documented... this is a last resort... I think this may
> Inevitably be the route I need to take.
>
|||On Nov 22, 6:26 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
One thing you can do to minimize this effect and to speed up
> the overall operation is to put the database in Simple recovery mode so the
> index rebuilds will be done with a minimally logged operation. That will
> minimize the data going to the transaction logs.
Thanks Andrew - Your thought are exactly on track with what I have
already tried.
|||This is one of my favorite scenarios. We have the same problem almost except
we carry tables with 400mill rows and our window is smaller than yours.
What we end up doing is almost the same thing. We do a select into another
table, and create all the indexes on that new table. Then we just rename the
original table to OLD and then the new table gets the original name... did I
say that right?
1. select * into customerNEW from customer.
2. Build indexes on customerNEW.
3. rename customer to customerOLD.
4. rename customerNEW to customer.
5. Drop table customerOLD.
and you're done...
Of course, if you're lucky enough to be on Yukon, then you can partition and
reindex a single partition at a time and get much better results. That is if
you don't need the parallel reads that weren't added. However, you can get
concurrency back if you put a partitioned view on top of them. I know that's
a workaround for it, but it's all we've got until katmai.
"Curtis" wrote:

> Hi,
> I have a 50GB database on SQL Server 2000 Enterprise that is highly
> transactional in a 24x7 environment. There's approximately 8 GB of
> free space, so I want to shrink it. It has been online for over five
> years and has never been defraged or reorganized. I've been given a
> maximum downtime window of eight hours. A test defrag on a copy of the
> database ran for almost two days (single user mode, quad-cpu server).
> I can't risk deadlocks with this system (lots of foreign key
> contraints, etc.), so I am unable to run the defrag while it is
> online.
> I'm wondering if moving all of the data to a new file in the same
> filegroup would be faster and also eliminate the existing
> fragmentation and possibly truncate the free space. Does anyone have
> any suggestions?
> Thanks!
>
sql