Hi All,
I have a SQL 2000 database that I had created a seperate file group for to
hold indexes. I have since deleted the indexes and want to delete the file
groups. When I execute the ALTER DATABASE <dbname> REMOVE FILEGROUP
<filegroupname> I receive an error indicating that 'The filegroup <filegroup>
cannot be removed because it is not empty'
If have verified that there are no tables or indexes in this filegroup and
have also run the following below queries to verify that the given filegroups
are empty:
select name,groupid
from sysindexes
where groupid=filegroup_id(<groupname>)
and indid > 0
Although there are no objects residing in the actual devices, they are still
listed in sysfiles. As such, even if I try to detach and reattach the DB,
the files are still linked and loaded with the database.
The tables to which the indexes existed are very large, so I also
stopped/restarted the sql services (thinking that possibly something may have
been stuck in memory) however that did not resolve the problem.
Has aonyone encountered this before? Is anyone aware of a fix for this?
Thanks,You can try using DBCC SHRINKFILE with the EmptyFile
option first. Then use ALTER DATABASE to remove the filegroup.
"DBADave" <DBADave@.discussions.microsoft.com> wrote in message
news:703563A7-BF30-445B-B563-2E0C323A9957@.microsoft.com...
> Hi All,
> I have a SQL 2000 database that I had created a seperate file group for to
> hold indexes. I have since deleted the indexes and want to delete the
file
> groups. When I execute the ALTER DATABASE <dbname> REMOVE FILEGROUP
> <filegroupname> I receive an error indicating that 'The filegroup
<filegroup>
> cannot be removed because it is not empty'
> If have verified that there are no tables or indexes in this filegroup and
> have also run the following below queries to verify that the given
filegroups
> are empty:
> select name,groupid
> from sysindexes
> where groupid=filegroup_id(<groupname>)
> and indid > 0
> Although there are no objects residing in the actual devices, they are
still
> listed in sysfiles. As such, even if I try to detach and reattach the DB,
> the files are still linked and loaded with the database.
> The tables to which the indexes existed are very large, so I also
> stopped/restarted the sql services (thinking that possibly something may
have
> been stuck in memory) however that did not resolve the problem.
> Has aonyone encountered this before? Is anyone aware of a fix for this?
> Thanks,|||I have tried this. The DBCC shows no output other then the standard DBCC
execution statement. When I attempt to issue an ALTER DATABASE to drop the
filegroups I get the error indicating that the filesgroups are not empty.
There are no objects in these filegroups, sysfilesgroups also does not show
the filegroups although sysfiles and sysfiles1 still show the filegroups that
I am trying to remove.
Any help would be greatly appreciated.
Thanks,
"Armando Prato" wrote:
> You can try using DBCC SHRINKFILE with the EmptyFile
> option first. Then use ALTER DATABASE to remove the filegroup.
> "DBADave" <DBADave@.discussions.microsoft.com> wrote in message
> news:703563A7-BF30-445B-B563-2E0C323A9957@.microsoft.com...
> > Hi All,
> >
> > I have a SQL 2000 database that I had created a seperate file group for to
> > hold indexes. I have since deleted the indexes and want to delete the
> file
> > groups. When I execute the ALTER DATABASE <dbname> REMOVE FILEGROUP
> > <filegroupname> I receive an error indicating that 'The filegroup
> <filegroup>
> > cannot be removed because it is not empty'
> >
> > If have verified that there are no tables or indexes in this filegroup and
> > have also run the following below queries to verify that the given
> filegroups
> > are empty:
> >
> > select name,groupid
> > from sysindexes
> > where groupid=filegroup_id(<groupname>)
> > and indid > 0
> >
> > Although there are no objects residing in the actual devices, they are
> still
> > listed in sysfiles. As such, even if I try to detach and reattach the DB,
> > the files are still linked and loaded with the database.
> >
> > The tables to which the indexes existed are very large, so I also
> > stopped/restarted the sql services (thinking that possibly something may
> have
> > been stuck in memory) however that did not resolve the problem.
> >
> > Has aonyone encountered this before? Is anyone aware of a fix for this?
> >
> > Thanks,
>
>|||This is kind of weird, but if i manually add the filegroup records back to
sysfilesgroups I can then see that there is an empty file in each (also
verified in EM by then looking at the filegroups properties to see the number
of files within). I was then able to ALTER DATABASE with a REMOVE FILE
refernecing what the original file name was, then issue an ALTER DATABASE
with a REMOVE FILEGROUP.
I am all set now.
Thanks All,
Dave
"DBADave" wrote:
> I have tried this. The DBCC shows no output other then the standard DBCC
> execution statement. When I attempt to issue an ALTER DATABASE to drop the
> filegroups I get the error indicating that the filesgroups are not empty.
> There are no objects in these filegroups, sysfilesgroups also does not show
> the filegroups although sysfiles and sysfiles1 still show the filegroups that
> I am trying to remove.
> Any help would be greatly appreciated.
> Thanks,
>
> "Armando Prato" wrote:
> > You can try using DBCC SHRINKFILE with the EmptyFile
> > option first. Then use ALTER DATABASE to remove the filegroup.
> >
> > "DBADave" <DBADave@.discussions.microsoft.com> wrote in message
> > news:703563A7-BF30-445B-B563-2E0C323A9957@.microsoft.com...
> > > Hi All,
> > >
> > > I have a SQL 2000 database that I had created a seperate file group for to
> > > hold indexes. I have since deleted the indexes and want to delete the
> > file
> > > groups. When I execute the ALTER DATABASE <dbname> REMOVE FILEGROUP
> > > <filegroupname> I receive an error indicating that 'The filegroup
> > <filegroup>
> > > cannot be removed because it is not empty'
> > >
> > > If have verified that there are no tables or indexes in this filegroup and
> > > have also run the following below queries to verify that the given
> > filegroups
> > > are empty:
> > >
> > > select name,groupid
> > > from sysindexes
> > > where groupid=filegroup_id(<groupname>)
> > > and indid > 0
> > >
> > > Although there are no objects residing in the actual devices, they are
> > still
> > > listed in sysfiles. As such, even if I try to detach and reattach the DB,
> > > the files are still linked and loaded with the database.
> > >
> > > The tables to which the indexes existed are very large, so I also
> > > stopped/restarted the sql services (thinking that possibly something may
> > have
> > > been stuck in memory) however that did not resolve the problem.
> > >
> > > Has aonyone encountered this before? Is anyone aware of a fix for this?
> > >
> > > Thanks,
> >
> >
> >|||Have you tried a DBCC SHRINKFILE with EMPTYFILE on the member files and then
dropping the files first?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"DBADave" <DBADave@.discussions.microsoft.com> wrote in message
news:95372080-4621-4B1B-9F3E-907D7503D8B9@.microsoft.com...
I have tried this. The DBCC shows no output other then the standard DBCC
execution statement. When I attempt to issue an ALTER DATABASE to drop the
filegroups I get the error indicating that the filesgroups are not empty.
There are no objects in these filegroups, sysfilesgroups also does not show
the filegroups although sysfiles and sysfiles1 still show the filegroups
that
I am trying to remove.
Any help would be greatly appreciated.
Thanks,
"Armando Prato" wrote:
> You can try using DBCC SHRINKFILE with the EmptyFile
> option first. Then use ALTER DATABASE to remove the filegroup.
> "DBADave" <DBADave@.discussions.microsoft.com> wrote in message
> news:703563A7-BF30-445B-B563-2E0C323A9957@.microsoft.com...
> > Hi All,
> >
> > I have a SQL 2000 database that I had created a seperate file group for
> > to
> > hold indexes. I have since deleted the indexes and want to delete the
> file
> > groups. When I execute the ALTER DATABASE <dbname> REMOVE FILEGROUP
> > <filegroupname> I receive an error indicating that 'The filegroup
> <filegroup>
> > cannot be removed because it is not empty'
> >
> > If have verified that there are no tables or indexes in this filegroup
> > and
> > have also run the following below queries to verify that the given
> filegroups
> > are empty:
> >
> > select name,groupid
> > from sysindexes
> > where groupid=filegroup_id(<groupname>)
> > and indid > 0
> >
> > Although there are no objects residing in the actual devices, they are
> still
> > listed in sysfiles. As such, even if I try to detach and reattach the
> > DB,
> > the files are still linked and loaded with the database.
> >
> > The tables to which the indexes existed are very large, so I also
> > stopped/restarted the sql services (thinking that possibly something may
> have
> > been stuck in memory) however that did not resolve the problem.
> >
> > Has aonyone encountered this before? Is anyone aware of a fix for this?
> >
> > Thanks,
>
>
Showing posts with label hold. Show all posts
Showing posts with label hold. Show all posts
Sunday, March 11, 2012
Wednesday, March 7, 2012
Cant create database with > 4 GB log
Hi,
I got a backup file from my friend which needs 11 GB to hold the
transaction log. I tried to restore this database but an error
message:
"MODIFY FILE encountered operating system error 112(error not found)
while attempting to expand the physical file."
appeared. The free space is more than enough. I then tried another way
to create a database with a 11 gb log file but it fails with the same
error message whenever I tried to create any database with a file
larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
this the cause of this limitation?
p.s. I'm very new to the SQLServer.
Pls Help,
Will.pongthai@.msn.com (Pongthai) wrote in message news:<4bf99b6f.0405042008.9cc4e14@.posting.google.com>...
> Hi,
> I got a backup file from my friend which needs 11 GB to hold the
> transaction log. I tried to restore this database but an error
> message:
> "MODIFY FILE encountered operating system error 112(error not found)
> while attempting to expand the physical file."
> appeared. The free space is more than enough. I then tried another way
> to create a database with a 11 gb log file but it fails with the same
> error message whenever I tried to create any database with a file
> larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
> this the cause of this limitation?
> p.s. I'm very new to the SQLServer.
>
> Pls Help,
> Will.
I believe that 4GB is the maximum file size on a FAT32 filesystem, so
I would guess your hard drive is formatted with FAT32, not NTFS. You
should be able to convert it, as described here (make sure you back up
your data first):
http://support.microsoft.com/defaul...kb;en-us;307881
NTFS is the recommended filesystem for both Windows XP and SQL Server.
Simon|||Dear Will,
Umm yeh interesting, I am not sure but if the problem is "4GB", it may
be due to 4GB Memory constraint... However, the error number you
report belongs to operating system not SQL Server and it means disk is
full, no space for any more 0101010 on the disk!
:-)
Exact description of Error
"112 there is not enough space on the disk. ERROR_DISK_FULL"
http://msdn.microsoft.com/library/d...des__0-499_.asp
Please describe things a lil more
Thanks!
Me,
Saghir Taj
MCDBA
pongthai@.msn.com (Pongthai) wrote in message news:<4bf99b6f.0405042008.9cc4e14@.posting.google.com>...
> Hi,
> I got a backup file from my friend which needs 11 GB to hold the
> transaction log. I tried to restore this database but an error
> message:
> "MODIFY FILE encountered operating system error 112(error not found)
> while attempting to expand the physical file."
> appeared. The free space is more than enough. I then tried another way
> to create a database with a 11 gb log file but it fails with the same
> error message whenever I tried to create any database with a file
> larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
> this the cause of this limitation?
> p.s. I'm very new to the SQLServer.
>
> Pls Help,
> Will.|||Thanks for both answers. I'll have one of my hdd partition changed to
NTFS and try again.
Thanks,
Will.|||Simon may be right this may be one of cause!.. i would like to know
the exact cause of problem.. if you get some fix or exact descripton
of problem please let me know!
Me,
Saghir Taj
MCDBA
sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0405050216.555cf13c@.posting.google.com>...
> pongthai@.msn.com (Pongthai) wrote in message news:<4bf99b6f.0405042008.9cc4e14@.posting.google.com>...
> > Hi,
> > I got a backup file from my friend which needs 11 GB to hold the
> > transaction log. I tried to restore this database but an error
> > message:
> > "MODIFY FILE encountered operating system error 112(error not found)
> > while attempting to expand the physical file."
> > appeared. The free space is more than enough. I then tried another way
> > to create a database with a 11 gb log file but it fails with the same
> > error message whenever I tried to create any database with a file
> > larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
> > this the cause of this limitation?
> > p.s. I'm very new to the SQLServer.
> > Pls Help,
> > Will.
> I believe that 4GB is the maximum file size on a FAT32 filesystem, so
> I would guess your hard drive is formatted with FAT32, not NTFS. You
> should be able to convert it, as described here (make sure you back up
> your data first):
> http://support.microsoft.com/defaul...kb;en-us;307881
> NTFS is the recommended filesystem for both Windows XP and SQL Server.
> Simon|||hi
has the problem solved
i am also facing the same problem
sara
Cant create database with > 4 GB log
Hi,
I got a backup file from my friend which needs 11 GB to hold the
transaction log. I tried to restore this database but an error
message:
"MODIFY FILE encountered operating system error 112(error not found)
while attempting to expand the physical file."
appeared. The free space is more than enough. I then tried another way
to create a database with a 11 gb log file but it fails with the same
error message whenever I tried to create any database with a file
larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
this the cause of this limitation?
p.s. I'm very new to the SQLServer.
Pls Help,
Will.pongthai@.msn.com (Pongthai) wrote in message news:<4bf99b6f.0405042008.9cc4e14@.posting.google.com>...
> Hi,
> I got a backup file from my friend which needs 11 GB to hold the
> transaction log. I tried to restore this database but an error
> message:
> "MODIFY FILE encountered operating system error 112(error not found)
> while attempting to expand the physical file."
> appeared. The free space is more than enough. I then tried another way
> to create a database with a 11 gb log file but it fails with the same
> error message whenever I tried to create any database with a file
> larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
> this the cause of this limitation?
> p.s. I'm very new to the SQLServer.
>
> Pls Help,
> Will.
I believe that 4GB is the maximum file size on a FAT32 filesystem, so
I would guess your hard drive is formatted with FAT32, not NTFS. You
should be able to convert it, as described here (make sure you back up
your data first):
http://support.microsoft.com/defaul...kb;en-us;307881
NTFS is the recommended filesystem for both Windows XP and SQL Server.
Simon|||Dear Will,
Umm yeh interesting, I am not sure but if the problem is "4GB", it may
be due to 4GB Memory constraint... However, the error number you
report belongs to operating system not SQL Server and it means disk is
full, no space for any more 0101010 on the disk!
:-)
Exact description of Error
"112 there is not enough space on the disk. ERROR_DISK_FULL"
http://msdn.microsoft.com/library/d...des__0-499_.asp
Please describe things a lil more
Thanks!
Me,
Saghir Taj
MCDBA
pongthai@.msn.com (Pongthai) wrote in message news:<4bf99b6f.0405042008.9cc4e14@.posting.google.com>...
> Hi,
> I got a backup file from my friend which needs 11 GB to hold the
> transaction log. I tried to restore this database but an error
> message:
> "MODIFY FILE encountered operating system error 112(error not found)
> while attempting to expand the physical file."
> appeared. The free space is more than enough. I then tried another way
> to create a database with a 11 gb log file but it fails with the same
> error message whenever I tried to create any database with a file
> larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
> this the cause of this limitation?
> p.s. I'm very new to the SQLServer.
>
> Pls Help,
> Will.|||Thanks for both answers. I'll have one of my hdd partition changed to
NTFS and try again.
Thanks,
Will.|||Simon may be right this may be one of cause!.. i would like to know
the exact cause of problem.. if you get some fix or exact descripton
of problem please let me know!
Me,
Saghir Taj
MCDBA
sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0405050216.555cf13c@.posting.google.com>...
> pongthai@.msn.com (Pongthai) wrote in message news:<4bf99b6f.0405042008.9cc4e14@.posting.google.com>...
> > Hi,
> > I got a backup file from my friend which needs 11 GB to hold the
> > transaction log. I tried to restore this database but an error
> > message:
> > "MODIFY FILE encountered operating system error 112(error not found)
> > while attempting to expand the physical file."
> > appeared. The free space is more than enough. I then tried another way
> > to create a database with a 11 gb log file but it fails with the same
> > error message whenever I tried to create any database with a file
> > larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
> > this the cause of this limitation?
> > p.s. I'm very new to the SQLServer.
> > Pls Help,
> > Will.
> I believe that 4GB is the maximum file size on a FAT32 filesystem, so
> I would guess your hard drive is formatted with FAT32, not NTFS. You
> should be able to convert it, as described here (make sure you back up
> your data first):
> http://support.microsoft.com/defaul...kb;en-us;307881
> NTFS is the recommended filesystem for both Windows XP and SQL Server.
> Simon
Subscribe to:
Posts (Atom)