SQLSVR 2005 SP2 / Shrink data file will not work

[Major Drake]

Windows 2003 64 bit sp2 + SQL Server std 32 bit sp2
compability mode 90, recovery model simple.

I have about 40 Gb database where is about 98% free space (I deleted data
from tables).

dbcc checkdb -OK.
dbcc reindex all tables -OK

Shrink database (with reorganize and release unused pace) -no any affets,
data file size will be same still, 98% free space.
Log size 1 Mb.

Shrink datafile with log works fine, but not with datafile (No any errors
occured).

I tried without any helps:
- Database full backup->restore to different SQL Server -same problem in
other server.
- Change recovery model to full and back to simple.
- compability level 80 and 90.


I know that lastest "option" is that I should script the database and copy
data from old to new one,
but do you have any other ideas?`

Br, Major

[Russell Fields]

Re: SQLSVR 2005 SP2 / Shrink data file will not work

Major,

I don't know if this fits, of course, but have you run DBCC CLEANTABLE?

When you run DBCC SHRINKFILE ('Datafile', targetsize) I trust that you are
not using TRUNCATEONLY. (Because, a page of data could still exist out at
the 40GB part of the file.)

Something I have never personally done, but which is a possibility, is to
add another file to the filegroup, then run:
DBCC SHRINKFILE ('Datafile1',EM PTYFILE)
This will move all of the data to the other file(s) in the filegroup, after
which the first file can be dropped. (But, as I said, this is Books Online
information, not personal experience.)

RLF


"Major Drake" <major.drake@pp .inet.fi.HALOOO OOOO.fiwrote in message
news:%gKBk.36$u %6.17@read4.ine t.fi...

Windows 2003 64 bit sp2 + SQL Server std 32 bit sp2
compability mode 90, recovery model simple.
>
I have about 40 Gb database where is about 98% free space (I deleted data
from tables).
>
dbcc checkdb -OK.
dbcc reindex all tables -OK
>
Shrink database (with reorganize and release unused pace) -no any
affets, data file size will be same still, 98% free space.
Log size 1 Mb.
>
Shrink datafile with log works fine, but not with datafile (No any errors
occured).
>
I tried without any helps:
- Database full backup->restore to different SQL Server -same problem in
other server.
- Change recovery model to full and back to simple.
- compability level 80 and 90.
>
>
I know that lastest "option" is that I should script the database and copy
data from old to new one,
but do you have any other ideas?`
>
Br, Major
>
>
>
>
>
>

[Erland Sommarskog]

Re: SQLSVR 2005 SP2 / Shrink data file will not work

Major Drake (major.drake@pp .inet.fi.HALOOO OOOO.fi) writes:

Windows 2003 64 bit sp2 + SQL Server std 32 bit sp2
compability mode 90, recovery model simple.
>
I have about 40 Gb database where is about 98% free space (I deleted data
from tables).
>
dbcc checkdb -OK.
dbcc reindex all tables -OK

And all tables have clustered indexes?

Shrink database (with reorganize and release unused pace) -no any
affets, data file size will be same still, 98% free space.

Try using DBCC SHRINKFILE instead. Books Online says about DBCC
SHRINKDATABASE:

The database cannot be made smaller than the minimum size of the
database. The minimum size is the size specified when the database is
originally created, or the last size explicitly set by using a file
size changing operation such as DBCC SHIRNKFILE or ALTER DATABASE.

But about DBCC SHRINKFILE:

You can shrink a file to a size that is less than the size specified when
it was created. This resets the minimum file size to the new value.

A tip is to specify a target size to DBCC SHRINKFILE. I have found that
it completes faster that way.

Don't forget to reindex your tables when you are done. Shrinking induces
a lot of fragmentation.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Aaron Bertrand [SQL Server MVP]]

Re: SQLSVR 2005 SP2 / Shrink data file will not work

In addition to the other posts, please read:

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

If you ran the size up to 40GB as a test, and are going to do so again, you
are much better off leaving it as is than shrinking it to the bare minimum
and then running the size up again.




On 9/22/08 6:17 AM, in article %gKBk.36$u%6.17 @read4.inet.fi, "Major Drake"
<major.drake@pp .inet.fi.HALOOO OOOO.fiwrote:

Windows 2003 64 bit sp2 + SQL Server std 32 bit sp2
compability mode 90, recovery model simple.
>
I have about 40 Gb database where is about 98% free space (I deleted data
from tables).
>
dbcc checkdb -OK.
dbcc reindex all tables -OK
>
Shrink database (with reorganize and release unused pace) -no any affets,
data file size will be same still, 98% free space.
Log size 1 Mb.
>
Shrink datafile with log works fine, but not with datafile (No any errors
occured).
>
I tried without any helps:
- Database full backup->restore to different SQL Server -same problem in
other server.
- Change recovery model to full and back to simple.
- compability level 80 and 90.
>
>
I know that lastest "option" is that I should script the database and copy
data from old to new one,
but do you have any other ideas?`
>
Br, Major
>
>
>
>
>
>

[Major Drake]

Re: SQLSVR 2005 SP2 / Shrink data file will not work

I have about 40 Gb database where is about 98% free space (I deleted data

from tables).
>
dbcc checkdb -OK.
dbcc reindex all tables -OK
>
Shrink database (with reorganize and release unused pace) -no any
affets, data file size will be same still, 98% free space.
Log size 1 Mb.
>
Shrink datafile with log works fine, but not with datafile (No any errors
occured).
>
I tried without any helps:
- Database full backup->restore to different SQL Server -same problem in
other server.
- Change recovery model to full and back to simple.
- compability level 80 and 90.
>
>
I know that lastest "option" is that I should script the database and copy
data from old to new one,
but do you have any other ideas?`

This is now resolved:

I see that dbcc checktable shows that there were xx rows in xxxx pages,
but the table was empty (rows were deleted from table, count(*) was zero).

I should drop all foreign keys, truncate all empty tables and recreate
foreign keys.
After it shrink file will fork fine, now database size is 50 Mb ;-)...

[Erland Sommarskog]

Re: SQLSVR 2005 SP2 / Shrink data file will not work

Major Drake (major.drake@pp .inet.fi.HALOOO OOOO.fi) writes:

I see that dbcc checktable shows that there were xx rows in xxxx pages,
but the table was empty (rows were deleted from table, count(*) was zero).

Page and extent deallocation can be deferred for large deletes, so that they
are not deleted until later. Although I think I've seen situations where
it never came to the end of the road. Reindexing the table could have
helped and saved you from dropping the FKs.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[suarez.jesus.antonio@gmail.com]

Re: SQLSVR 2005 SP2 / Shrink data file will not work

hello Drake,

I wrote this post few moments ago, maybe it could help you.


---------------*---------------*---------------*---------------
*---------------*---------------*---------------*

You would need to use "dbcc shrinkfile" but once a time and using
small amount of size.

Example.

You have a database of 10 GB called database_10 and you know you can
shrink to 7 GB , run in the Query Analyzer this sentences.

Run once a time to see if it is working.

database_10
files:
database_10_Dat a
database_10_Log



DBCC SHRINKFILE('dat abase_10_Data', 9800) -- Only Shrink to 9800 MB
--Depends on time it takes you can shrink more space.
DBCC SHRINKFILE('dat abase_10_Data', 9500) -- Only Shrink to 9500 MB
DBCC SHRINKFILE('dat abase_10_Data', 9000) -- Only Shrink to 9000 MB

DBCC SHRINKFILE('dat abase_10_Data', 8800) -- Only Shrink to 8800 MB
DBCC SHRINKFILE('dat abase_10_Data', 8500) -- Only Shrink to 8500 MB
DBCC SHRINKFILE('dat abase_10_Data', 8000) -- Only Shrink to 8000 MB

DBCC SHRINKFILE('dat abase_10_Data', 7800) -- Only Shrink to 9800 MB
DBCC SHRINKFILE('dat abase_10_Data', 7500) -- Only Shrink to 9800 MB
DBCC SHRINKFILE('dat abase_10_Data', 7000) -- Only Shrink to 9800 MB


With this you can shrink the database.


Hope this work for you.



Antonio S.
DBA and Web Developer.

http://www.grupoKino.com

---------------*---------------*---------------*---------------
*---------------*---------------*---------------*
On Sep 22, 3:17 am, "Major Drake"
<major.dr...@pp .inet.fi.HALOOO OOOO.fiwrote:

Windows 2003 64 bit sp2 + SQL Server std 32 bit sp2
compability mode 90, recovery model simple.
>
I have about 40 Gb database where is about 98% free space (I deleted data
from tables).
>
dbcc checkdb -OK.
dbcc reindex all tables -OK
>
Shrink database (with reorganize and release unused pace) -no any affets,
data file size will be same still, 98% free space.
Log size 1 Mb.
>
Shrink datafile with log works fine, but not with datafile (No any errors
occured).
>
I tried without any helps:
- Database full backup->restore to different SQL Server -same problem in
other server.
- Change recovery model to full and back to simple.
- compability level 80 and 90.
>
I know that lastest "option" is that I should script the database and copy
data from old to new one,
but do you have any other ideas?`
>
Br, Major