How to force DB shrink?

I have DB 13GB in size. After we deleted some old tables
from it it's still using 13GB on hard drive. In properties
it was always set up "Auto shrink", but after a week it's
still 13GB... :(
How to force shrinking manual?Try running DBCC OPENTRANS to see if you have any long running open
transactions preventing the SHRINK.
Also have a look at DBCC SHRINKFILE in BOL
HTH
Ryan Waight, MCDBA, MCSE
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>|||Is it data or log file which is big? If log, check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>|||If you are trying to shrink the data file then you can use DBCC SHRINKFILE.
You will be unable to shrink the data file if there are allocated extents at
the end. You can move indexes and tables with clustered indexes by doing a
DBCC DBREINDEX. If the extents are from a table with non clustered index or
text/image data, then you have more of an issue. Unfortunately these do not
move with the DBCC DBREINDEX command. You will have to create a new table
and copy the data back in to physically move the extents. A better option
is to create a new file group for your non clustered tables and text/image
data and move it there.
To find the offending tables and indexes download the free tool below.
Select from the tool bar Table/Index View. This will show you a list of
tables and indexes and where they are allocated in the data file. Scroll to
the end of the data file click on an allocated extent (green). This will
highlight the index that is allocated to the extent. You can then run DBCC
DBREINDEX on that index.
Hope this helps
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>|||"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>
Autoshrink shrinks a database if it has 25% space in it. If the table took
up less space than that , then you have to manually shrink it
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003|||This is the main question! How manually shrink DB?!
>--Original Message--
>"Alexandre Rybalov" <alex@.covers.com> wrote in message
>news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
>> I have DB 13GB in size. After we deleted some old tables
>> from it it's still using 13GB on hard drive. In
properties
>> it was always set up "Auto shrink", but after a week
it's
>> still 13GB... :(
>> How to force shrinking manual?
>Autoshrink shrinks a database if it has 25% space in it.
If the table took
>up less space than that , then you have to manually
shrink it
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.518 / Virus Database: 316 - Release Date:
11/09/2003
>
>.
>|||Alexandre,
You can use DBCC SHRINKFILE for data files also.If it still doesnt shrink,
check for data fragmentation using DBCC SHOWCONTIG.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:52fe01c37d30$3ab07190$a501280a@.phx.gbl...
> This is the main question! How manually shrink DB?!
> >--Original Message--
> >"Alexandre Rybalov" <alex@.covers.com> wrote in message
> >news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> >> I have DB 13GB in size. After we deleted some old tables
> >> from it it's still using 13GB on hard drive. In
> properties
> >> it was always set up "Auto shrink", but after a week
> it's
> >> still 13GB... :(
> >>
> >> How to force shrinking manual?
> >>
> >Autoshrink shrinks a database if it has 25% space in it.
> If the table took
> >up less space than that , then you have to manually
> shrink it
> >
> >
> >--
> >Outgoing mail is certified Virus Free.
> >Checked by AVG anti-virus system (http://www.grisoft.com).
> >Version: 6.0.518 / Virus Database: 316 - Release Date:
> 11/09/2003
> >
> >
> >.
> >