Showing posts with label 200gb. Show all posts
Showing posts with label 200gb. Show all posts

Friday, February 24, 2012

Cannot shrink large database file

I have a server that is running SQL 2000. There is one large database 200
GB in size made of three files in the primary file group. We just freed up
40 GB of space in the db by archiving a table. The taskpad of SQL indicates
all this comes from the first file in the primary filegroup.
Our problem is that we've had a ton of trouble shrinking it down. We've
tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
haven't done empty file because we do not want to remove the file. Out of
all of our attempts, only one try has succeeded in removing 10 GB, and that
took 4 hours. DBCC CHECKDB shows no errors, we did a backup successfully.
It just looks like it is running slow with high CPU and DISK I/O. The data
files are all on a fiber-optic SAN. Any suggestions? Thanks.

*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************
The shrinking process usually is very painful and resource intensive. How
long it takes usually depends on the hardware and how much activity you have
in that file. In order to shrink the file SQL Server needs to move any data
pages near the end of the file towards the beginning of the file. This is a
fully logged operation and can be blocked just like any other process that
updates pages. 40GB in a 200GB db is not that much free space. You need
plenty of free space on a regular basis to handle things like reindexing
anyway. If you must shrink then use SHRINKFILE vs. SHRINKDATABASE and make
sure when you are done that all the files in the file group are the same
size. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:%23D6br50bFHA.3048@.TK2MSFTNGP12.phx.gbl...
>I have a server that is running SQL 2000. There is one large database 200
>GB in size made of three files in the primary file group. We just freed up
>40 GB of space in the db by archiving a table. The taskpad of SQL
>indicates all this comes from the first file in the primary filegroup.
> Our problem is that we've had a ton of trouble shrinking it down. We've
> tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
> haven't done empty file because we do not want to remove the file. Out of
> all of our attempts, only one try has succeeded in removing 10 GB, and
> that took 4 hours. DBCC CHECKDB shows no errors, we did a backup
> successfully. It just looks like it is running slow with high CPU and DISK
> I/O. The data files are all on a fiber-optic SAN. Any suggestions?
> Thanks.
>
> --
> *************************************************
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> *************************************************
>

Cannot shrink large database file

I have a server that is running SQL 2000. There is one large database 200
GB in size made of three files in the primary file group. We just freed up
40 GB of space in the db by archiving a table. The taskpad of SQL indicates
all this comes from the first file in the primary filegroup.
Our problem is that we've had a ton of trouble shrinking it down. We've
tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
haven't done empty file because we do not want to remove the file. Out of
all of our attempts, only one try has succeeded in removing 10 GB, and that
took 4 hours. DBCC CHECKDB shows no errors, we did a backup successfully.
It just looks like it is running slow with high CPU and DISK I/O. The data
files are all on a fiber-optic SAN. Any suggestions? Thanks.
****************************************
*********
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
****************************************
*********The shrinking process usually is very painful and resource intensive. How
long it takes usually depends on the hardware and how much activity you have
in that file. In order to shrink the file SQL Server needs to move any data
pages near the end of the file towards the beginning of the file. This is a
fully logged operation and can be blocked just like any other process that
updates pages. 40GB in a 200GB db is not that much free space. You need
plenty of free space on a regular basis to handle things like reindexing
anyway. If you must shrink then use SHRINKFILE vs. SHRINKDATABASE and make
sure when you are done that all the files in the file group are the same
size. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:%23D6br50bFHA.3048@.TK2MSFTNGP12.phx.gbl...
>I have a server that is running SQL 2000. There is one large database 200
>GB in size made of three files in the primary file group. We just freed up
>40 GB of space in the db by archiving a table. The taskpad of SQL
>indicates all this comes from the first file in the primary filegroup.
> Our problem is that we've had a ton of trouble shrinking it down. We've
> tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
> haven't done empty file because we do not want to remove the file. Out of
> all of our attempts, only one try has succeeded in removing 10 GB, and
> that took 4 hours. DBCC CHECKDB shows no errors, we did a backup
> successfully. It just looks like it is running slow with high CPU and DISK
> I/O. The data files are all on a fiber-optic SAN. Any suggestions?
> Thanks.
>
> --
> ****************************************
*********
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> ****************************************
*********
>

Cannot Shrink Database

All,
We have a data modeling group in here that can grow databases to 150Gb or
200Gb.
When they have run a scenario they will get rid of lots of the data and ask
me to shrink the database for them. This usually works no problem.
Today I have 2 databases they want to shrink, both have 85 - 90 Gb free, but
when I shrink with a 10% free space option nothing happens to the size.
I am a bit hazy about the details but is it something to do with where data
gets written to?
Any ideas on how to get around this problem would be very welcome.
Thanks In AdvanceVerify that the tranlog has been backed up recently and then run
CHECKPOINT against the database. This will force all of the dirty and
log pages that haven't been written to get written. Most likely there
is a dirty page preventing the database from shrinking any further than
it has.
HTH
Jason|||Is if the ldf file which is big? If so, I have some info in below article (approx middle of article)
regarding shrinking of tlog files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:DFC25D21-F281-4A2C-9C21-920500732936@.microsoft.com...
> All,
> We have a data modeling group in here that can grow databases to 150Gb or
> 200Gb.
> When they have run a scenario they will get rid of lots of the data and ask
> me to shrink the database for them. This usually works no problem.
> Today I have 2 databases they want to shrink, both have 85 - 90 Gb free, but
> when I shrink with a 10% free space option nothing happens to the size.
> I am a bit hazy about the details but is it something to do with where data
> gets written to?
> Any ideas on how to get around this problem would be very welcome.
> Thanks In Advance
>|||Jason,
Thanks for the info. Appreciate the help.
"j strate" wrote:
> Verify that the tranlog has been backed up recently and then run
> CHECKPOINT against the database. This will force all of the dirty and
> log pages that haven't been written to get written. Most likely there
> is a dirty page preventing the database from shrinking any further than
> it has.
> HTH
> Jason
>