DBCC srhinkfile didn't shrink the file but expand the database instead!!

[Phyllis]

I have a 2.8G SQL 7 database with 1.6G used space. I want to shrink it to 1.8G, but after I issued the 'DBCC srhinkfile(datafile, 1800)', it didn't shrink the file, instead it expanded to 2.7G used space? I tried on a test server, the command worked every time although the time to complete the task tended to increase every time.

I did the DBCC DBREINDEX (fill factor 90) and updated usage, the size didn't change. DBCC CHECKDB, CATELOG AND SHOWCONTIG were all performed and there wasn't any error or fragmentation.

Now I'm wondering what's the actual size of my database, 1.6G or 2.7G? If there's a way that I can get back close to my original used sized, then I can try to shink the file again. Any comment is welcome. Thanks advance.

[mitch]

This burned me once too!!
If you set up a maint plan that backs up the db & transaction log as well as the other stuff in the maint plan, you will get the disk space back.



------------
Phyllis at 4/6/01 11:39:13 AM

I have a 2.8G SQL 7 database with 1.6G used space. I want to shrink it to 1.8G, but after I issued the 'DBCC srhinkfile(datafile, 1800)', it didn't shrink the file, instead it expanded to 2.7G used space? I tried on a test server, the command worked every time although the time to complete the task tended to increase every time.

I did the DBCC DBREINDEX (fill factor 90) and updated usage, the size didn't change. DBCC CHECKDB, CATELOG AND SHOWCONTIG were all performed and there wasn't any error or fragmentation.

Now I'm wondering what's the actual size of my database, 1.6G or 2.7G? If there's a way that I can get back close to my original used sized, then I can try to shink the file again. Any comment is welcome. Thanks advance.

[Phyllis]

Thanks Mitch. I'm glad somebody already encountered a similar problem. I'll give it a try. Thanks again.


------------
mitch at 4/6/01 2:07:07 PM

This burned me once too!!
If you set up a maint plan that backs up the db & transaction log as well as the other stuff in the maint plan, you will get the disk space back.



------------
Phyllis at 4/6/01 11:39:13 AM

I have a 2.8G SQL 7 database with 1.6G used space. I want to shrink it to 1.8G, but after I issued the 'DBCC srhinkfile(datafile, 1800)', it didn't shrink the file, instead it expanded to 2.7G used space? I tried on a test server, the command worked every time although the time to complete the task tended to increase every time.

I did the DBCC DBREINDEX (fill factor 90) and updated usage, the size didn't change. DBCC CHECKDB, CATELOG AND SHOWCONTIG were all performed and there wasn't any error or fragmentation.

Now I'm wondering what's the actual size of my database, 1.6G or 2.7G? If there's a way that I can get back close to my original used sized, then I can try to shink the file again. Any comment is welcome. Thanks advance.

[Andrew Beaumont]

------------
Phyllis at 4/6/01 2:23:41 PM

Thanks Mitch. I'm glad somebody already encountered a similar problem. I'll give it a try. Thanks again.


------------
mitch at 4/6/01 2:07:07 PM

This burned me once too!!
If you set up a maint plan that backs up the db & transaction log as well as the other stuff in the maint plan, you will get the disk space back.



------------
Phyllis at 4/6/01 11:39:13 AM

I have a 2.8G SQL 7 database with 1.6G used space. I want to shrink it to 1.8G, but after I issued the 'DBCC srhinkfile(datafile, 1800)', it didn't shrink the file, instead it expanded to 2.7G used space? I tried on a test server, the command worked every time although the time to complete the task tended to increase every time.

I did the DBCC DBREINDEX (fill factor 90) and updated usage, the size didn't change. DBCC CHECKDB, CATELOG AND SHOWCONTIG were all performed and there wasn't any error or fragmentation.

Now I'm wondering what's the actual size of my database, 1.6G or 2.7G? If there's a way that I can get back close to my original used sized, then I can try to shink the file again. Any comment is welcome. Thanks advance.

Phyllis,

Many people including myself have problems with large transaction
files & much has been written about it in discussion groups & in
microsoft articles. I am not 100% happy with anything I have tried
to date - perhaps SQL Server 2000 is better - I updated one of our
servers last week & only time will tell.

The problem seems most acute with infrequently back'd up data
warehouses that use large transactions. I once had a transaction log
greater than 1GB caused for this reason. I got an SQL script from
someone called Veronica that it was claimed would reduce the size of
transaction logs. It ran for over 12 hours on the server before I
aborted it. The transaction log was not made any smaller & the database
grew threefold. Over the next month or so, the database did shrink
considerably perhaps due to Maintenance plans or some other undocumented
process running, I don't know. I can email you this script if you like?

The morale of this story seems to be to avoid large transaction logs
growing in the first place:

a) Back up the log frequently.
b) Set limits to the max. size of the log
c) Set the initial log size small. You can't shrink any smaller than
this value. Strange isn't it?
d) Fragement any transactions in data warehouses into several small
transactions.
e) Buy plenty of disk space
f) SQL Server 2000??

Best Wishes,
Andrew
E:andrew.beaumont.qmcuh-tr.trent.nhs.uk@pmdf

[Phyllis]

Andrew,

I've done a) through d) as you mentioned. Since there are so many people have problems with the DBCC shrinkfile command, it is either buggy or not user-friendly. Another wierd thing is the time to shrink the file is unpredictable, it can vary from 10 minutes to 9 hours (maybe longer) for similar files.

By the way, I don't have any problem with the log size, I found the article from MS support site, it has a script to srhink the log file effectively. I used it once, it worked fine.

http://support.microsoft.com/support/kb/articles/Q256/6/50.ASP?LN=EN-US&SD=gn&FR=0&qry=@OriginalSize&rnk=1&src=DHCS_MSP SS_gn_SRCH&SPR=SQL

Phyllis

------------
Andrew Beaumont at 4/11/01 6:00:44 AM




------------
Phyllis at 4/6/01 2:23:41 PM

Thanks Mitch. I'm glad somebody already encountered a similar problem. I'll give it a try. Thanks again.


------------
mitch at 4/6/01 2:07:07 PM

This burned me once too!!
If you set up a maint plan that backs up the db & transaction log as well as the other stuff in the maint plan, you will get the disk space back.



------------
Phyllis at 4/6/01 11:39:13 AM

I have a 2.8G SQL 7 database with 1.6G used space. I want to shrink it to 1.8G, but after I issued the 'DBCC srhinkfile(datafile, 1800)', it didn't shrink the file, instead it expanded to 2.7G used space? I tried on a test server, the command worked every time although the time to complete the task tended to increase every time.

I did the DBCC DBREINDEX (fill factor 90) and updated usage, the size didn't change. DBCC CHECKDB, CATELOG AND SHOWCONTIG were all performed and there wasn't any error or fragmentation.

Now I'm wondering what's the actual size of my database, 1.6G or 2.7G? If there's a way that I can get back close to my original used sized, then I can try to shink the file again. Any comment is welcome. Thanks advance.

Phyllis,

Many people including myself have problems with large transaction
files & much has been written about it in discussion groups & in
microsoft articles. I am not 100% happy with anything I have tried
to date - perhaps SQL Server 2000 is better - I updated one of our
servers last week & only time will tell.

The problem seems most acute with infrequently back'd up data
warehouses that use large transactions. I once had a transaction log
greater than 1GB caused for this reason. I got an SQL script from
someone called Veronica that it was claimed would reduce the size of
transaction logs. It ran for over 12 hours on the server before I
aborted it. The transaction log was not made any smaller & the database
grew threefold. Over the next month or so, the database did shrink
considerably perhaps due to Maintenance plans or some other undocumented
process running, I don't know. I can email you this script if you like?

The morale of this story seems to be to avoid large transaction logs
growing in the first place:

a) Back up the log frequently.
b) Set limits to the max. size of the log
c) Set the initial log size small. You can't shrink any smaller than
this value. Strange isn't it?
d) Fragement any transactions in data warehouses into several small
transactions.
e) Buy plenty of disk space
f) SQL Server 2000??

Best Wishes,
Andrew
E:andrew.beaumont.qmcuh-tr.trent.nhs.uk@pmdf