473,324 Members | 2,567 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

SQLSVR 2005 SP2 / Shrink data file will not work

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


Sep 22 '08 #1
6 8770
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',EMPTYFILE)
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" <ma*********@pp.inet.fi.HALOOOOOOO.fiwrote in message
news:%g*************@read4.inet.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


Sep 22 '08 #2
Major Drake (ma*********@pp.inet.fi.HALOOOOOOO.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, es****@sommarskog.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

Sep 22 '08 #3
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 %g*************@read4.inet.fi, "Major Drake"
<ma*********@pp.inet.fi.HALOOOOOOO.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

Sep 22 '08 #4
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 ;-)...

Sep 30 '08 #5
Major Drake (ma*********@pp.inet.fi.HALOOOOOOO.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, es****@sommarskog.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

Sep 30 '08 #6
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_Data
database_10_Log

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

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

DBCC SHRINKFILE('database_10_Data',7800) -- Only Shrink to 9800 MB
DBCC SHRINKFILE('database_10_Data',7500) -- Only Shrink to 9800 MB
DBCC SHRINKFILE('database_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.HALOOOOOOO.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
Oct 15 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: SP | last post by:
I've production sql server 7 sp3 on windows NT. I had a 8GB data file of which 5GB were used and 3GB were unused. I wanted to take back the unused 3GB. So I did the following with EM GUI: 1. I...
1
by: R Camarda | last post by:
Help, I have a database that has a data file of 2GB and a log file of 31GB. In enterprise manager, when I choose shrink it says there is 30GB of unused space. When I shrink the database, it does...
5
by: BashiraInTrouble | last post by:
Hi Friends, I have tried almost everything but I cant seem to shrink the transaction log. Executing DBCC SQLPERF(LOGSPACE) gives me this info: Database Log Size (MB) Log Space Used (%) ...
4
by: Tommy.Vincent | last post by:
hi all, This will be a easy question for all out here. I have a database of 28GB. having 3 Data Files 22 GB, 3.58 Gb and 2.70 GB respectively. and a Transaction Log file of 156 mb. When i...
6
by: sathyashrayan | last post by:
Following are the selected thread from the date:30-jan-2005 to 31-jan-2005. I did not use any name because of the subject is important. You can get the original thread by typing the subject...
5
by: Mark | last post by:
I'd like to automate the process of shrinking .jpg files using C#. I'd like to point at 1000 files, shrink them to 30%, and then resave the new files. I've seen the Graphics.DrawImage method, but...
0
by: dmlinliverpool | last post by:
I am running VS.net 2005 Express and Sql Server 2005 Express. The DB and VS are both on the same PC. I cannot connect to a database from within VS. In Database Explorer I click Connect To...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.