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. During
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. During
> my upgrade process, I detatched this database and copied it over to my test
> server, where I am still having no problems. Help!!
>|||"John Bell" wrote:
> I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC >CHECKDB?
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 about
500 MB) with basically no free space. I've run DBCC CHECKDB along with about
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:
> > I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC >CHECKDB?
> 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 about
> 500 MB) with basically no free space. I've run DBCC CHECKDB along with about
> 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
> 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:
> >
> > > I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC >CHECKDB?
> >
> > 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 about
> > 500 MB) with basically no free space. I've run DBCC CHECKDB along with about
> > everything else I can think of, and I can't find any way to get that file
> > size back down.
> >
> > Thanks!
> >|||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:
> John,
> No, it's the data file. I've tried to manually rebuild/reorganize
> individual indexes, and that's not really working, either.
> Gary
>
> > 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:
> > >
> > > > I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC >CHECKDB?
> > >
> > > 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 about
> > > 500 MB) with basically no free space. I've run DBCC CHECKDB along with about
> > > everything else I can think of, and I can't find any way to get that file
> > > size back down.
> > >
> > > Thanks!
> > >|||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...
> 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.
>>|||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...
> > 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 had similar problem with the data file shrinking, but I think I solved the
problem safer way (without restoring or detaching the database and without
service restart):
1) Backup database log
2) Run sp_helpfile and note the size of the data file (6444160 KB, for
example)
3) Run ALTER DATABASE and use the file size number slightly bigger than
actual file size:
ALTER DATABASE TESTDB
MODIFY FILE
( NAME = TESTDB_DATA
, SIZE = 6444165KB )
4) Now shrink file command works:
USE TESTDB
GO
DBCC SHRINKFILE (N'TESTDB_DATA' , TRUNCATEONLY)
GO
Let me know if this worked for anybody else.
"CTS_DBA" 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...
> > > 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.
> > >>
> > >>
> >
> >
> >