Filegroup full

Hi - I am no exprt at setting up SQL Server and am in a situation where I am
the ONLY SQL Server resource around now. I have set up a Db where the
primary file group was set to the servers c:\ drive which only has about 5Gb
of space and only about 200mb left. I can't find anything on the c:\ drive
to delete to create some space.
The tables I am setting up are large - many millions of records. I have
managed to assign space on a much bigger disk (50Gb) but can't delete the
space allocated to the c:\ drive. I assume it is the default drive where
system files are kept.
The problem I am now getting is that I can no longer even save a DTS - I get
a message saying no more disk space.
Is there any way I can ensure that I can either get rid of the allocation to
the c:\ disk or force SQL Server to save system files to the bigger disk.
I did find an option to set new Db's to the bigger drive so I could delete
the Db and recreate in a new DB.
Any thoughts anyone?
TIA
Andreww
One idea would be to create a separate filegroup on a bigger drive, drop and
re-create all your clustered indexes and use the ON keyword to specify the
new filegroup.
In theory, you should be able to get all of the data out of the filegroup
that is only on C:\ and then you should be able to remove the allocation by
using ALTER DATABASE ... REMOVE FILE
http://www.aspfaq.com/
(Reverse address to reply.)
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.u k...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>
|||You could also detach the database, move the MDF/LDF files to the bigger
drive, and re-attach them. Or BACKUP the database and then RESTORE...WITH
MOVE.
Also see the following (thanks to Andrew Kelly):
http://support.microsoft.com/?id=314546 Moving DB's between Servers
http://support.microsoft.com/?id=224071 Moving SQL Server Databases to a
New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.aspfaq.com/
(Reverse address to reply.)
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.u k...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>
|||Aaron - I have created a filegroup on a much bigger drive (I may not have
used the correct terminology in my original post).
I don't have any clustered indexes so will have a go with the ALTER
DATABASE...REMOVE FILE command.
Cheers
Andrew
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uAfESLdbEHA.996@.TK2MSFTNGP12.phx.gbl...
> One idea would be to create a separate filegroup on a bigger drive, drop
and
> re-create all your clustered indexes and use the ON keyword to specify the
> new filegroup.
> In theory, you should be able to get all of the data out of the filegroup
> that is only on C:\ and then you should be able to remove the allocation
by[vbcol=seagreen]
> using ALTER DATABASE ... REMOVE FILE
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Andreww" <andrew@.fake.com> wrote in message
> news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.u k...
I[vbcol=seagreen]
> am
> 5Gb
> drive
the[vbcol=seagreen]
where[vbcol=seagreen]
> get
allocation[vbcol=seagreen]
> to
disk.[vbcol=seagreen]
delete
>
|||> I don't have any clustered indexes so will have a go with the ALTER
> DATABASE...REMOVE FILE command.
You don't have any primary keys (they are created as clustered by default)?
You could create a clustered index on each table. They certainly aren't
going to hurt.
However, it would likely be easier to BACKUP and then RESTORE ... WITH MOVE
or detach/re-attach. I prefer the latter because it is typically quicker.
BTW, I've never tried to remove a file that is non-empty.
http://www.aspfaq.com/
(Reverse address to reply.)
|||Sorted:
1. detached db
2. Moved data and log files to big drive
3. reattached db
... so far so good!
Thanks Aaron
Andrew
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.u k...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>

Filegroup full

Hi - I am no exprt at setting up SQL Server and am in a situation where I am
the ONLY SQL Server resource around now. I have set up a Db where the
primary file group was set to the servers c:\ drive which only has about 5Gb
of space and only about 200mb left. I can't find anything on the c:\ drive
to delete to create some space.
The tables I am setting up are large - many millions of records. I have
managed to assign space on a much bigger disk (50Gb) but can't delete the
space allocated to the c:\ drive. I assume it is the default drive where
system files are kept.
The problem I am now getting is that I can no longer even save a DTS - I get
a message saying no more disk space.
Is there any way I can ensure that I can either get rid of the allocation to
the c:\ disk or force SQL Server to save system files to the bigger disk.
I did find an option to set new Db's to the bigger drive so I could delete
the Db and recreate in a new DB.
Any thoughts anyone?
TIA
AndrewwOne idea would be to create a separate filegroup on a bigger drive, drop and
re-create all your clustered indexes and use the ON keyword to specify the
new filegroup.
In theory, you should be able to get all of the data out of the filegroup
that is only on C:\ and then you should be able to remove the allocation by
using ALTER DATABASE ... REMOVE FILE
http://www.aspfaq.com/
(Reverse address to reply.)
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.uk...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>|||You could also detach the database, move the MDF/LDF files to the bigger
drive, and re-attach them. Or BACKUP the database and then RESTORE...WITH
MOVE.
Also see the following (thanks to Andrew Kelly):
http://support.microsoft.com/?id=314546 Moving DB's between Servers
http://support.microsoft.com/?id=224071 Moving SQL Server Databases to a
New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.aspfaq.com/
(Reverse address to reply.)
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.uk...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>|||Aaron - I have created a filegroup on a much bigger drive (I may not have
used the correct terminology in my original post).
I don't have any clustered indexes so will have a go with the ALTER
DATABASE...REMOVE FILE command.
Cheers
Andrew
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uAfESLdbEHA.996@.TK2MSFTNGP12.phx.gbl...
> One idea would be to create a separate filegroup on a bigger drive, drop
and
> re-create all your clustered indexes and use the ON keyword to specify the
> new filegroup.
> In theory, you should be able to get all of the data out of the filegroup
> that is only on C:\ and then you should be able to remove the allocation
by
> using ALTER DATABASE ... REMOVE FILE
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Andreww" <andrew@.fake.com> wrote in message
> news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.uk...
I[vbcol=seagreen]
> am
> 5Gb
> drive
the[vbcol=seagreen]
where[vbcol=seagreen]
> get
allocation[vbcol=seagreen]
> to
disk.[vbcol=seagreen]
delete[vbcol=seagreen]
>|||> I don't have any clustered indexes so will have a go with the ALTER
> DATABASE...REMOVE FILE command.
You don't have any primary keys (they are created as clustered by default)?
You could create a clustered index on each table. They certainly aren't
going to hurt.
However, it would likely be easier to BACKUP and then RESTORE ... WITH MOVE
or detach/re-attach. I prefer the latter because it is typically quicker.
BTW, I've never tried to remove a file that is non-empty.
http://www.aspfaq.com/
(Reverse address to reply.)|||Sorted:
1. detached db
2. Moved data and log files to big drive
3. reattached db
... so far so good!
Thanks Aaron
Andrew
"Andreww" <andrew@.fake.com> wrote in message
news:LsWKc.76200$q8.26631@.fe1.news.blueyonder.co.uk...
> Hi - I am no exprt at setting up SQL Server and am in a situation where I
am
> the ONLY SQL Server resource around now. I have set up a Db where the
> primary file group was set to the servers c:\ drive which only has about
5Gb
> of space and only about 200mb left. I can't find anything on the c:\
drive
> to delete to create some space.
> The tables I am setting up are large - many millions of records. I have
> managed to assign space on a much bigger disk (50Gb) but can't delete the
> space allocated to the c:\ drive. I assume it is the default drive where
> system files are kept.
> The problem I am now getting is that I can no longer even save a DTS - I
get
> a message saying no more disk space.
> Is there any way I can ensure that I can either get rid of the allocation
to
> the c:\ disk or force SQL Server to save system files to the bigger disk.
> I did find an option to set new Db's to the bigger drive so I could delete
> the Db and recreate in a new DB.
> Any thoughts anyone?
> TIA
> Andreww
>