DBfile usage: shrinkdatabase

Showing posts with label shrinkdatabase. Show all posts

Thursday, March 29, 2012

dbcc shrinkdatabase vs dbcc shrinkfile

Hello all,
I just chopped off millions of stale records from a table bringing the
row count from 85M to 5 and also freeing up around 30GB of data. What
is the safest way to shrink the database to reclaim this freed up space?Hello,
I recommend you to use DBCC SHRINKFILE to shrink the MDF and LDF file
seperately. Before executing the SHRINKFILE use the command SP_SPACEUSED
to identify the unallocated space.
Thanks
Hari
"AF" <af.at.work@.gmail.com> wrote in message
news:1168388869.505893.31950@.i56g2000hsf.googlegroups.com...
> Hello all,
> I just chopped off millions of stale records from a table bringing the
> row count from 85M to 5 and also freeing up around 30GB of data. What
> is the safest way to shrink the database to reclaim this freed up space?
>|||Hari Prasad wrote:
> Hello,
> I recommend you to use DBCC SHRINKFILE to shrink the MDF and LDF file
> seperately. Before executing the SHRINKFILE use the command SP_SPACEUSED
> to identify the unallocated space.
> Thanks
> Hari
>
> "AF" <af.at.work@.gmail.com> wrote in message
> news:1168388869.505893.31950@.i56g2000hsf.googlegroups.com...
> > Hello all,
> >
> > I just chopped off millions of stale records from a table bringing the
> > row count from 85M to 5 and also freeing up around 30GB of data. What
> > is the safest way to shrink the database to reclaim this freed up space?
> >
Thanks Hari, just curious -- why separately?
These are the results of sp_spaceused but how do I interpret these to
use dbcc shrinkfile?
name size unallocated space
mydb 39536.81 MB 504.16 MB
reserved data index size unused
8589984 KB 7921840 KB 647688 KB 20456 KB|||AF wrote:
> Hari Prasad wrote:
> > Hello,
> >
> > I recommend you to use DBCC SHRINKFILE to shrink the MDF and LDF file
> > seperately. Before executing the SHRINKFILE use the command SP_SPACEUSED
> > to identify the unallocated space.
> >
> > Thanks
> > Hari
> >
> >
> > "AF" <af.at.work@.gmail.com> wrote in message
> > news:1168388869.505893.31950@.i56g2000hsf.googlegroups.com...
> > > Hello all,
> > >
> > > I just chopped off millions of stale records from a table bringing the
> > > row count from 85M to 5 and also freeing up around 30GB of data. What
> > > is the safest way to shrink the database to reclaim this freed up space?
> > >
> Thanks Hari, just curious -- why separately?
> These are the results of sp_spaceused but how do I interpret these to
> use dbcc shrinkfile?
> name size unallocated space
> mydb 39536.81 MB 504.16 MB
> reserved data index size unused
> 8589984 KB 7921840 KB 647688 KB 20456 KB
This is the usage reported from SSEM:
mydb.mdf
7721MB USED
1172MB FREE
mydb.ldf
158MB USED
30486MB FREEsql

dbcc shrinkdatabase vs dbcc shrinkfile

Tuesday, March 27, 2012

dbcc shrinkdatabase vs dbcc shrinkfile

DBCC SHRINKDATABASE Question

We need to shrink one of our production databases - SQL Server 2000,
service pack 4. The size of the database is currently 26GB in size,
with about 40-50% free space.
We have concerns about the performance impact of running the DBCC
SHRINKDATABASE statement.
I would like to hear other people's experience with running this
command.
My understanding is that it will NOT take the database offline, that
is, users can still query the database - but is it going to create a
serious performance problem?
Also, approximately how long would a shrink of this size take?
In addition to these questions, I need general recommendations about
shrinking the database, caveats, etc.
Thank you!!
See:
http://support.microsoft.com/default...b;en-us;272318 --not sure if
this is data or log file
and
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
PS - You cannot shink a db smaller than it's orriginal creation size using
DBCC SHRINKDATABASE - must use DBCC SHRINKFILE.
HTH
Jerry
<tootsuite@.gmail.com> wrote in message
news:1129658373.884310.50430@.g14g2000cwa.googlegro ups.com...
> We need to shrink one of our production databases - SQL Server 2000,
> service pack 4. The size of the database is currently 26GB in size,
> with about 40-50% free space.
> We have concerns about the performance impact of running the DBCC
> SHRINKDATABASE statement.
> I would like to hear other people's experience with running this
> command.
> My understanding is that it will NOT take the database offline, that
> is, users can still query the database - but is it going to create a
> serious performance problem?
> Also, approximately how long would a shrink of this size take?
> In addition to these questions, I need general recommendations about
> shrinking the database, caveats, etc.
> Thank you!!
>

DBCC SHRINKDATABASE Question

We need to shrink one of our production databases - SQL Server 2000,
service pack 4. The size of the database is currently 26GB in size,
with about 40-50% free space.
We have concerns about the performance impact of running the DBCC
SHRINKDATABASE statement.
I would like to hear other people's experience with running this
command.
My understanding is that it will NOT take the database offline, that
is, users can still query the database - but is it going to create a
serious performance problem?
Also, approximately how long would a shrink of this size take?
In addition to these questions, I need general recommendations about
shrinking the database, caveats, etc.
Thank you!!See:
http://support.microsoft.com/defaul...kb;en-us;272318 --not sure if
this is data or log file
and
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
PS - You cannot shink a db smaller than it's orriginal creation size using
DBCC SHRINKDATABASE - must use DBCC SHRINKFILE.
HTH
Jerry
<tootsuite@.gmail.com> wrote in message
news:1129658373.884310.50430@.g14g2000cwa.googlegroups.com...
> We need to shrink one of our production databases - SQL Server 2000,
> service pack 4. The size of the database is currently 26GB in size,
> with about 40-50% free space.
> We have concerns about the performance impact of running the DBCC
> SHRINKDATABASE statement.
> I would like to hear other people's experience with running this
> command.
> My understanding is that it will NOT take the database offline, that
> is, users can still query the database - but is it going to create a
> serious performance problem?
> Also, approximately how long would a shrink of this size take?
> In addition to these questions, I need general recommendations about
> shrinking the database, caveats, etc.
> Thank you!!
>

DBCC SHRINKDATABASE Question

We need to shrink one of our production databases - SQL Server 2000,
service pack 4. The size of the database is currently 26GB in size,
with about 40-50% free space.
We have concerns about the performance impact of running the DBCC
SHRINKDATABASE statement.
I would like to hear other people's experience with running this
command.
My understanding is that it will NOT take the database offline, that
is, users can still query the database - but is it going to create a
serious performance problem?
Also, approximately how long would a shrink of this size take?
In addition to these questions, I need general recommendations about
shrinking the database, caveats, etc.
Thank you!!See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318 --not sure if
this is data or log file
and
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
PS - You cannot shink a db smaller than it's orriginal creation size using
DBCC SHRINKDATABASE - must use DBCC SHRINKFILE.
HTH
Jerry
<tootsuite@.gmail.com> wrote in message
news:1129658373.884310.50430@.g14g2000cwa.googlegroups.com...
> We need to shrink one of our production databases - SQL Server 2000,
> service pack 4. The size of the database is currently 26GB in size,
> with about 40-50% free space.
> We have concerns about the performance impact of running the DBCC
> SHRINKDATABASE statement.
> I would like to hear other people's experience with running this
> command.
> My understanding is that it will NOT take the database offline, that
> is, users can still query the database - but is it going to create a
> serious performance problem?
> Also, approximately how long would a shrink of this size take?
> In addition to these questions, I need general recommendations about
> shrinking the database, caveats, etc.
> Thank you!!
>sql

DBCC SHRINKDATABASE question

Can this be run on a database that's currently active (lots of people logged
on)?
Ideally, I'd run this out of office hours when no one's connected, but we've
got a serious disk space problem and I'd like to run this now. Any
suggestions/concerns?
Many thanks
GriffA second question if I may relating to truncation.
I have two machines each with SQLServer on it.
Machine 1 has the live databases A, B, C.
Machine 2 has the live databases D, E, F.
Everynight, the databases are backed up, copied onto the other machines and
restored. This is incase we lose a machine and can then switch everyone to
the remaining machine
So, in fact, I have the following
Machine 1 has A, B, C, D', E', F'
Machine 2 has A', B', C', D, E, F.
(where ' denotes the backup copy).
As the live databases are being written to (i.e. not read only), I presume
that the best strategy is to have the following shrink DB commands.
Machine 1
I run shrinkdatabase on A, B, C with the NOTRUNCATE option to prevent the
files from having to expand too much
I run shrinkdatabase on D', E', F' without the NOTRUNCATE option because
these won't be written to as they're not live.
Machine 2
I run shrinkdatabase on D, E, F with the NOTRUNCATE option to prevent the
files from having to expand too much
I run shrinkdatabase on A', B', C' without the NOTRUNCATE option because
these won't be written to as they're not live.
Does this make sense?
Thanks in advance
Griff|||Okay, think I found this answer now on MSDN:
The database being shrunk does not have to be in single user mode; other
users can be working in the database when it is shrunk. This includes system
databases.
Thanks for your time.
Griff

DBCC SHRINKDATABASE QUESTION

I am archiving about 25 gigs of images from our OLTP database to an archive database. After archiving these images(table which contains a column datatype image), there is about 25 gigs of unallocated space reported by EM. I run the normal Shrink database routine
DBCC SHRINKDATABASE
( database_name [ , target_percent ] )
--I set the target percent low(about 5%), current db size after archive is 75 gigs so it should shrink to about 79 gigs...
and instead of releasing the free space to the operating system the routine actually increases the used space of the db to fill almost all the newly freed space. Basically I remove 25 gigs of data, run the routine and now magically the database grows 25 gigs instead of freeing up 25 gigs.(now the db is back to the orginal size before archive process!!) I've only experienced this when dealing with image datatypes. I am wondering if the way images are stored has something to do with this unexpected behavior. If anybody has any insight please let me know. ThanksYes this is a know issue that it can happen sometimes but I don't know if
there is a KB regarding this or not. Try using DBCC CLEANTABLE and see if
that helps. If not then you might have to BCP out all the data from that
table, truncate it and bcp it back in.
--
Andrew J. Kelly
SQL Server MVP
"mike petanovitch" <mpetanovitch@.hotmail.com> wrote in message
news:5DB04B5D-64C6-4422-AEB4-A1E99BEE8574@.microsoft.com...
> I am archiving about 25 gigs of images from our OLTP database to an
archive database. After archiving these images(table which contains a
column datatype image), there is about 25 gigs of unallocated space reported
by EM. I run the normal Shrink database routine:
> DBCC SHRINKDATABASE
> ( database_name [ , target_percent ] )
> --I set the target percent low(about 5%), current db size after archive is
75 gigs so it should shrink to about 79 gigs...
> and instead of releasing the free space to the operating system the
routine actually increases the used space of the db to fill almost all the
newly freed space. Basically I remove 25 gigs of data, run the routine
and now magically the database grows 25 gigs instead of freeing up 25
gigs.(now the db is back to the orginal size before archive process!!)
I've only experienced this when dealing with image datatypes. I am
wondering if the way images are stored has something to do with this
unexpected behavior. If anybody has any insight please let me know. Thanks

DBCC ShrinkDatabase on large DB

Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian KingHave you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>

DBCC ShrinkDatabase on large DB

Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian KingHave you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateo
nly')
go
dbcc shrinkfile('logical_ldf_name','truncateo
nly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>

DBCC ShrinkDatabase on large DB

Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian King
Have you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
sql

DBCC SHRINKDATABASE not completing

We're running SQL 2005 Enterprise with a very large database (198,893,696
KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
following deletion of about 4 million records, but found that starting late
last week it is no longer completing - even after 12 hours. (It would
normally take 30 minutes). Other operational steps are running ok. We're
not seeing any error entries. Ideas? Thank you.
Hi Jeffrey
First of all, you should seriously reconsider running DBCC SHRINKDATABASE on
a daily basis. It is an incredibly resource intensive operation, that can
end up hurting as much as help. Take a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx
If you have to use DBCC SHRINKDATABASE, you can look in the
sys.dm_exec_requests view, and look at the percent_complete column to verify
that the operation is making progress, and get a rough idea how much longer
it will take.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.
|||Why shrink today, only to have it grow again tomorrow with the 4M
insert/delete operations? Also it would seem that 4M records in a 200GB
database isn't that much anyway.
TheSQLGuru
President
Indicium Resources, Inc.
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.

DBCC SHRINKDATABASE not completing

We're running SQL 2005 Enterprise with a very large database (198,893,696
KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
following deletion of about 4 million records, but found that starting late
last week it is no longer completing - even after 12 hours. (It would
normally take 30 minutes). Other operational steps are running ok. We're
not seeing any error entries. Ideas? Thank you.Hi Jeffrey
First of all, you should seriously reconsider running DBCC SHRINKDATABASE on
a daily basis. It is an incredibly resource intensive operation, that can
end up hurting as much as help. Take a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://blogs.msdn.com/sqlserverstor...
ff.aspx
http://blogs.msdn.com/sqlserverstor...erver-2005.aspx
If you have to use DBCC SHRINKDATABASE, you can look in the
sys.dm_exec_requests view, and look at the percent_complete column to verify
that the operation is making progress, and get a rough idea how much longer
it will take.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.|||Why shrink today, only to have it grow again tomorrow with the 4M
insert/delete operations' Also it would seem that 4M records in a 200GB
database isn't that much anyway.
TheSQLGuru
President
Indicium Resources, Inc.
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.

DBCC SHRINKDATABASE not completing

We're running SQL 2005 Enterprise with a very large database (198,893,696
KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
following deletion of about 4 million records, but found that starting late
last week it is no longer completing - even after 12 hours. (It would
normally take 30 minutes). Other operational steps are running ok. We're
not seeing any error entries. Ideas? Thank you.Hi Jeffrey
First of all, you should seriously reconsider running DBCC SHRINKDATABASE on
a daily basis. It is an incredibly resource intensive operation, that can
end up hurting as much as help. Take a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx
If you have to use DBCC SHRINKDATABASE, you can look in the
sys.dm_exec_requests view, and look at the percent_complete column to verify
that the operation is making progress, and get a rough idea how much longer
it will take.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.|||Why shrink today, only to have it grow again tomorrow with the 4M
insert/delete operations' Also it would seem that 4M records in a 200GB
database isn't that much anyway.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.

DBCC SHRINKDATABASE Errors Running SQL Server 2000 , SP3a

I am running SQL Server 2000 SP3a on Windows 2003
I get the following error when I run DBCC SHRINKDATABASE
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
If I shrink the database using Enterprise Manager, I can shrink all the
files, including the log, except the MDF (PRIMARY) file.
I get the following error when I try to shrink this file:
Error 0 : This server has been connected.You must reconnect to perform this
operation.
I have re-booted the server , but get the same error messages.
I have run DBCC CHECKDB and DBCC CHECKFILEGROUP , and 0 errors are reported
Has anyone any ideas how to resolve this problem?
--
DuncanJTry setting single-user mode.
DuncanJ wrote:
> I am running SQL Server 2000 SP3a on Windows 2003
> I get the following error when I run DBCC SHRINKDATABASE
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> If I shrink the database using Enterprise Manager, I can shrink all the
> files, including the log, except the MDF (PRIMARY) file.
> I get the following error when I try to shrink this file:
> Error 0 : This server has been connected.You must reconnect to perform this
> operation.
> I have re-booted the server , but get the same error messages.
> I have run DBCC CHECKDB and DBCC CHECKFILEGROUP , and 0 errors are reported
> Has anyone any ideas how to resolve this problem?
> --
> DuncanJ

DBCC Shrinkdatabase errors

When DBCC ShrinkDatabase ('db-name') is run; the following error is returned.

File ID 1 of database ID 5 cannot be shrunk as it is either being shrunk by another process or is empty.

File ID 3 of database ID 5 cannot be shrunk as it is either being shrunk by another process or is empty.

Cannot shrink log file 2 (logmentusnet) because all logical log files are in use.

The files 1-3 are not empty so I'm assuming that another shrink is already running.

Questions:

1. If I restart the database will the errent database shrink stop?

2. How can I find and stop the errent database shrink process?

3. What's going on, and how can I recover the empty space in the database?

I am also facing the same issue.

Any workaround for this.

Regards

kokila

|||

Hy,

I am with the same problem… somebody can help?

File ID 9 of database ID 6 cannot be shrunk as it is either being shrunk by another process or is empty.

dbcc shrinkdatabase error

Hi ,
i was trying to shrink a database but i got the following error :
it's something like " logical file is in use" despite me logging in as a
single user
the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
i have also used the EM's shrink database , though it says it completed
successfully but it did not shrink the database
kindly advise
tks & rdgsHi
What is the Error Number and Severity?
You might be getting an informational message to tell you that the DB can't
be shunk any smaller than it already is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi,
Post the exact error.
I feel that Probably there will be some active transactions. Execute DBCC
OPENTRAN('DBNAME') to identify if there is any open active trans.
If yes, wait that to complete. After that you could execute a backup LOG to
clear the transaction log and then execute DBCC SHRINKFILE to
shrink MDF and LDF seperately.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi ,
before doing the shrink database , have already did a txn log bakup and it
says 4GB is free, though the log file is still occupying 5 GB
so thought of shrinking the database/log file and return the free space back
to OS
tks & rdgs
"Hari Prasad" wrote:
> Hi,
> Post the exact error.
> I feel that Probably there will be some active transactions. Execute DBCC
> OPENTRAN('DBNAME') to identify if there is any open active trans.
> If yes, wait that to complete. After that you could execute a backup LOG to
> clear the transaction log and then execute DBCC SHRINKFILE to
> shrink MDF and LDF seperately.
> Thanks
> Hari
> SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs
>
>|||Hi ,
I have done a txn log backup file earlier and it says that 4GB is free
tks & rdgs
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> What is the Error Number and Severity?
> You might be getting an informational message to tell you that the DB can't
> be shunk any smaller than it already is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "maxzsim" wrote:
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs|||Hi
It all depends on where the "Virtual Log"/"Active Portion of the Log" is in
relation to the end of the log file.
The Active Portion may be close to the end of the file at the moment, so the
log can not be shrunk. Once the Active Portion is at the beginning of the log
file, then the log can be truncated to where it is.
Look at "virtual log files" in BOL.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"maxzsim" wrote:
> Hi ,
> before doing the shrink database , have already did a txn log bakup and it
> says 4GB is free, though the log file is still occupying 5 GB
> so thought of shrinking the database/log file and return the free space back
> to OS
> tks & rdgs
> "Hari Prasad" wrote:
> > Hi,
> >
> > Post the exact error.
> >
> > I feel that Probably there will be some active transactions. Execute DBCC
> > OPENTRAN('DBNAME') to identify if there is any open active trans.
> > If yes, wait that to complete. After that you could execute a backup LOG to
> > clear the transaction log and then execute DBCC SHRINKFILE to
> > shrink MDF and LDF seperately.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> > news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > > Hi ,
> > >
> > > i was trying to shrink a database but i got the following error :
> > > it's something like " logical file is in use" despite me logging in as a
> > > single user
> > >
> > > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> > >
> > > i have also used the EM's shrink database , though it says it completed
> > > successfully but it did not shrink the database
> > >
> > > kindly advise
> > > tks & rdgs
> >
> >
> >|||Are you trying to shrink the db file or the log file? Sounds like the log
file. You should use DBCC SHRINKFILE not database as it allows you to
choose the specific file you want to shrink and how much.
--
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> Hi ,
> i was trying to shrink a database but i got the following error :
> it's something like " logical file is in use" despite me logging in as a
> single user
> the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> i have also used the EM's shrink database , though it says it completed
> successfully but it did not shrink the database
> kindly advise
> tks & rdgs|||Hi Andrew ,
i was trying to shrink the log file but it couldn't as per the error below
and i have also tried to shrink the database as well , hoping it'll shrink
the log file
tks & rdgs
"Andrew J. Kelly" wrote:
> Are you trying to shrink the db file or the log file? Sounds like the log
> file. You should use DBCC SHRINKFILE not database as it allows you to
> choose the specific file you want to shrink and how much.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> > Hi ,
> >
> > i was trying to shrink a database but i got the following error :
> > it's something like " logical file is in use" despite me logging in as a
> > single user
> >
> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 , truncate_only)
> >
> > i have also used the EM's shrink database , though it says it completed
> > successfully but it did not shrink the database
> >
> > kindly advise
> > tks & rdgs
>
>|||Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
be in single user mode to run this.
--
Andrew J. Kelly SQL MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
> Hi Andrew ,
> i was trying to shrink the log file but it couldn't as per the error
> below
> and i have also tried to shrink the database as well , hoping it'll shrink
> the log file
> tks & rdgs
> "Andrew J. Kelly" wrote:
>> Are you trying to shrink the db file or the log file? Sounds like the
>> log
>> file. You should use DBCC SHRINKFILE not database as it allows you to
>> choose the specific file you want to shrink and how much.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>> > Hi ,
>> >
>> > i was trying to shrink a database but i got the following error :
>> > it's something like " logical file is in use" despite me logging in as
>> > a
>> > single user
>> >
>> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
>> > truncate_only)
>> >
>> > i have also used the EM's shrink database , though it says it completed
>> > successfully but it did not shrink the database
>> >
>> > kindly advise
>> > tks & rdgs
>>|||Hi Andrew ,
i did tried with the DBCC shrink file statement but got the "logical log in
use" error as per the DBCC Shrinkdatabase. however, when i changed the mode
to "Simple" recovery mode , i could somehow shrink the db
tks & rdgs
"Andrew J. Kelly" wrote:
> Did you actually try DBCC SHRINKFILE? If not you should. You don't need to
> be in single user mode to run this.
> --
> Andrew J. Kelly SQL MVP
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
> > Hi Andrew ,
> >
> > i was trying to shrink the log file but it couldn't as per the error
> > below
> > and i have also tried to shrink the database as well , hoping it'll shrink
> > the log file
> >
> > tks & rdgs
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Are you trying to shrink the db file or the log file? Sounds like the
> >> log
> >> file. You should use DBCC SHRINKFILE not database as it allows you to
> >> choose the specific file you want to shrink and how much.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> >> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
> >> > Hi ,
> >> >
> >> > i was trying to shrink a database but i got the following error :
> >> > it's something like " logical file is in use" despite me logging in as
> >> > a
> >> > single user
> >> >
> >> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
> >> > truncate_only)
> >> >
> >> > i have also used the EM's shrink database , though it says it completed
> >> > successfully but it did not shrink the database
> >> >
> >> > kindly advise
> >> > tks & rdgs
> >>
> >>
> >>
>
>|||This is because the recovery mode you were in was preventing the log being
truncated until you'd backed it up. Switching to simple mode removes that
restriction. See BOL for more info ('Shrinking the Transaction Log' is a
good place to start)
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:65ADA556-3489-4620-844E-CC0DCB0BDF3A@.microsoft.com...
> Hi Andrew ,
> i did tried with the DBCC shrink file statement but got the "logical log
> in
> use" error as per the DBCC Shrinkdatabase. however, when i changed the
> mode
> to "Simple" recovery mode , i could somehow shrink the db
> tks & rdgs
> "Andrew J. Kelly" wrote:
>> Did you actually try DBCC SHRINKFILE? If not you should. You don't need
>> to
>> be in single user mode to run this.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:A84212A5-E2B0-4CF4-9F03-D4B84FCC4E59@.microsoft.com...
>> > Hi Andrew ,
>> >
>> > i was trying to shrink the log file but it couldn't as per the error
>> > below
>> > and i have also tried to shrink the database as well , hoping it'll
>> > shrink
>> > the log file
>> >
>> > tks & rdgs
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Are you trying to shrink the db file or the log file? Sounds like the
>> >> log
>> >> file. You should use DBCC SHRINKFILE not database as it allows you to
>> >> choose the specific file you want to shrink and how much.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> >> news:BFE5D0BA-D797-40CE-899B-246D6BA039D4@.microsoft.com...
>> >> > Hi ,
>> >> >
>> >> > i was trying to shrink a database but i got the following error :
>> >> > it's something like " logical file is in use" despite me logging in
>> >> > as
>> >> > a
>> >> > single user
>> >> >
>> >> > the statement i used : DBCC Shrinkdatabase ('dbname', 1 ,
>> >> > truncate_only)
>> >> >
>> >> > i have also used the EM's shrink database , though it says it
>> >> > completed
>> >> > successfully but it did not shrink the database
>> >> >
>> >> > kindly advise
>> >> > tks & rdgs
>> >>
>> >>
>> >>
>>

dbcc shrinkdatabase error

DBCC SHRINKDATABASE duration

Is there anyway to tell how long this will run for -- or how far it has got? I have a large database that has just had most of the data removed. The command has been running for 8 hours and I have just stopped it to let something else run quickly. Any way of telling how much longer it will take?Hi

Try this:

dbcc shrinkdatabase('DatabaseName', truncateonly)

This command just truncates unused space and do not move any data.
It should take few minutes on 100 GB database.
If you need to run full shrinkdatabase, maybe it is a good idea to run it over the weekend.

Regards
Kris Zywczyk|||Thanks, that gave me a few hundred gigs of free space to play with. Does it gradually move space from the end of the db to the beginning to compact it? What I mean is -- to be able to chop (Say) 100GB from the end of file it needs to be free space and randomly doing a defrag would not do that.|||DBCC SHRINKDATABASE
( 'database_name' | database_id | 0
[ ,target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]

Arguments
'database_name' | database_id | 0
Is the name or ID of the database to be shrunk. If 0 is specified, the current database is used. Database names must comply with the rules for identifiers.

target_percent
Is the percentage of free space that you want left in the database file after the database has been shrunk.

NOTRUNCATE
Causes the freed file space to be retained in the database files. If not specified, the freed file space is released to the operating system.

TRUNCATEONLY
Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.

WITH NO_INFOMSGS
Suppresses all informational messages that have severity levels from 0 through 10.

Regards
Kris Zywczyk|||This command just truncates unused space and do not move any data.

Regards
Kris Zywczyk

DBCC Shrinkdatabase will indeed reorganize datapages on a disk and will indeed cause fragmentation.|||DBCC Shrinkdatabase will indeed reorganize datapages on a disk and will indeed cause fragmentation.

TRUNCATEONLY
Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.

Regards
Kris Zywczyk|||if you use that arguement sure, but then you are not regaining all of your unused space caused by delete operations. you are only getting back the pages at the end of the files that have never been written too. our friend said he removed a bunch of data, so I am guess the space at the end of the file that has not been written to is not were stands to gain ... oh nevermind|||The truncateonly helped. I suspect the shrink preferentially takes pages from the end of the file and moves them towards the front. This means that the truncateonly does recover some space. If it did not preferentially do that then by chance space could only be recovered at the end (as statistically there would always be a block fairly near the end).sql

Thursday, March 29, 2012

dbcc shrinkdatabase vs dbcc shrinkfile

dbcc shrinkdatabase vs dbcc shrinkfile

Tuesday, March 27, 2012

dbcc shrinkdatabase vs dbcc shrinkfile

DBCC SHRINKDATABASE Question

DBCC SHRINKDATABASE Question

DBCC SHRINKDATABASE Question

DBCC SHRINKDATABASE question

DBCC SHRINKDATABASE QUESTION

DBCC ShrinkDatabase on large DB

DBCC ShrinkDatabase on large DB

DBCC ShrinkDatabase on large DB

DBCC SHRINKDATABASE not completing

DBCC SHRINKDATABASE not completing

DBCC SHRINKDATABASE not completing

DBCC SHRINKDATABASE Errors Running SQL Server 2000 , SP3a

DBCC Shrinkdatabase errors

dbcc shrinkdatabase error

dbcc shrinkdatabase error

dbcc shrinkdatabase error

DBCC SHRINKDATABASE duration

DBfile usage

Blog Archive

About Me