Error when shrinking data file in SQL 2005

I've just upgraded my production server from 2K to 2K5, and I've got one
database that is way too big (almost 5 GB with 88% free space). I have no
problems shrinking the transaction log, but when I try to shrink the data
file, I get the following message:
"A severe error occurred on the current command. The results, if any,
should be discarded. File ID 1 of database ID 8 cannot be shrunk as it is
either being shrunk by another process or is empty. (Microsoft SQL Server,
Error: 0)"
At one point, I had autoshrink turned on for the database to handle the
transaction log, but this has never been a problem for the data file. Durin
g
my upgrade process, I detatched this database and copied it over to my test
server, where I am still having no problems. Help!!Hi
I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC CHECKDB?
John
"Gary" wrote:

> I've just upgraded my production server from 2K to 2K5, and I've got one
> database that is way too big (almost 5 GB with 88% free space). I have no
> problems shrinking the transaction log, but when I try to shrink the data
> file, I get the following message:
> "A severe error occurred on the current command. The results, if any,
> should be discarded. File ID 1 of database ID 8 cannot be shrunk as it is
> either being shrunk by another process or is empty. (Microsoft SQL Server,
> Error: 0)"
> At one point, I had autoshrink turned on for the database to handle the
> transaction log, but this has never been a problem for the data file. Dur
ing
> my upgrade process, I detatched this database and copied it over to my tes
t
> server, where I am still having no problems. Help!!
>|||"John Bell" wrote:
[vbcol=seagreen]
> I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC >CHECKDB?[/vbc
ol]
John,
I ended up getting that problem solved (by detaching and reattaching the
database and then taking it offline and rebooting the server), but I've got
another somewhat related problem. In a maintenance plan I was doing a
rebuild on the indexes for the database. That maintenance plan never
completed (it hung up), and now my data file size is about 4 GB (it was abou
t
500 MB) with basically no free space. I've run DBCC CHECKDB along with abou
t
everything else I can think of, and I can't find any way to get that file
size back down.
Thanks!|||Hi Gary
Is this the data file or log file? If it is the log file you can use BACKUP
LOG ... WITH TRUNCATE_ONLY and then DBCC SHRINKFILE.
John
"Gary" wrote:

> "John Bell" wrote:
>
> John,
> I ended up getting that problem solved (by detaching and reattaching the
> database and then taking it offline and rebooting the server), but I've go
t
> another somewhat related problem. In a maintenance plan I was doing a
> rebuild on the indexes for the database. That maintenance plan never
> completed (it hung up), and now my data file size is about 4 GB (it was ab
out
> 500 MB) with basically no free space. I've run DBCC CHECKDB along with ab
out
> everything else I can think of, and I can't find any way to get that file
> size back down.
> Thanks!
>|||John,
No, it's the data file. I've tried to manually rebuild/reorganize
individual indexes, and that's not really working, either.
Gary
[vbcol=seagreen]
> Hi Gary
> Is this the data file or log file? If it is the log file you can use BACKU
P
> LOG ... WITH TRUNCATE_ONLY and then DBCC SHRINKFILE.
> John
>
> "Gary" wrote:
>|||Hi
You can use sp_spaceused to see what is being used by the file, and DBCC
SHRINKFILE will shrink it. In general it is not a good idea to contunually
expand/shrink the files.
John
"Gary" wrote:
[vbcol=seagreen]
> John,
> No, it's the data file. I've tried to manually rebuild/reorganize
> individual indexes, and that's not really working, either.
> Gary
>|||i came accross this problem too. what did was to restart the service
and executed dbcc shrinkfile again. that time it succeeded.|||Hi,
I got the same error,
When i moved all of user tables and indexes out of primary FG and to a new
FG, my Primary is left with around 17GB of which less than a 1GB is actually
used, and when I ran shrink file on the only file on that FG, it took ages.
then I stoped it (in 2000 it would have taken max 2-3 hours, our SAN is not
that bad :-) ). when I ran it again I get the error described.
when I back up the db and restore it, then I can shrink the file.
this is a db in simle mode, dev environment. worried what happens when we go
to production with this.
error:
File ID 1 of database ID X cannot be shrunk as it is either being shrunk by
another process or is empty.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
CTS DBA
"XNMB" wrote:

> i came accross this problem too. what did was to restart the service
> and executed dbcc shrinkfile again. that time it succeeded.
>|||I tried detach and re-attach the database fie and run DBCC SHRINKFILE
But I wouldn't recommend shrinking. Here's an article by the great Tibor
Karaszi on database shrinking
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:630320F3-22A4-4D33-A245-54D14D4784FD@.microsoft.com...[vbcol=seagreen]
> Hi,
> I got the same error,
> When i moved all of user tables and indexes out of primary FG and to a new
> FG, my Primary is left with around 17GB of which less than a 1GB is
> actually
> used, and when I ran shrink file on the only file on that FG, it took
> ages.
> then I stoped it (in 2000 it would have taken max 2-3 hours, our SAN is
> not
> that bad :-) ). when I ran it again I get the error described.
> when I back up the db and restore it, then I can shrink the file.
> this is a db in simle mode, dev environment. worried what happens when we
> go
> to production with this.
> error:
> File ID 1 of database ID X cannot be shrunk as it is either being shrunk
> by
> another process or is empty.
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
> should
> be discarded.
> --
> CTS DBA
>
> "XNMB" wrote:
>|||hi,
thanks for the reply.
I know what you mean, but what I don't understand is why shrinkfile wouldn't
work when there's so much free space in the file.
over 8GB
if the original database shrunk in SQL 2000 it works perfectly.
Thanks
--
CTS DBA
"bass_player [SBS-MVP]" wrote:

> I tried detach and re-attach the database fie and run DBCC SHRINKFILE
> But I wouldn't recommend shrinking. Here's an article by the great Tibor
> Karaszi on database shrinking
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:630320F3-22A4-4D33-A245-54D14D4784FD@.microsoft.com...
>
>