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
>
Showing posts with label situation. Show all posts
Showing posts with label situation. Show all posts
Monday, March 12, 2012
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...
> > 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
> >
> >
>|||> 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
>
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...
> > 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
> >
> >
>|||> 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
>
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
>
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
>
Sunday, February 19, 2012
File Group backups and restores
We have a situation where a large DB(150GB) has mostly static data in it. We were considering a filegroup solution that locates the static data in one(or more) files and the dynamic content is stored in another but it seems that any file/filegroup restore requires a transaction log restore to complete the database. We don't have any transactional requirements, in fact they weren't even dumping them till I arrived and restoring them could undo changes to the data. The app is a warehouse where any lost data would be reloaded. The other alternative is separate databases. Anyone have any ideas or suggestions?So what exactly is the question<g>. A lot of people don't bother backing
up DW's if they can easily redo the data from scratch.
--
Andrew J. Kelly
SQL Server MVP
"jeff ericson" <anonymous@.discussions.microsoft.com> wrote in message
news:8C5F4B6A-F8A3-404A-BD26-F9245E061D80@.microsoft.com...
> We have a situation where a large DB(150GB) has mostly static data in it.
We were considering a filegroup solution that locates the static data in
one(or more) files and the dynamic content is stored in another but it seems
that any file/filegroup restore requires a transaction log restore to
complete the database. We don't have any transactional requirements, in
fact they weren't even dumping them till I arrived and restoring them could
undo changes to the data. The app is a warehouse where any lost data would
be reloaded. The other alternative is separate databases. Anyone have any
ideas or suggestions?|||Transaction log backups are required to bring the Filegroup back into sync
with the other filegroups. IF there have been no transactions since the
filegroup was backed up , then logs are not required.
You could just do a transaction log backup after loading new data...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jeff ericson" <anonymous@.discussions.microsoft.com> wrote in message
news:8C5F4B6A-F8A3-404A-BD26-F9245E061D80@.microsoft.com...
> We have a situation where a large DB(150GB) has mostly static data in it.
We were considering a filegroup solution that locates the static data in
one(or more) files and the dynamic content is stored in another but it seems
that any file/filegroup restore requires a transaction log restore to
complete the database. We don't have any transactional requirements, in
fact they weren't even dumping them till I arrived and restoring them could
undo changes to the data. The app is a warehouse where any lost data would
be reloaded. The other alternative is separate databases. Anyone have any
ideas or suggestions?|||We have a similar situation where I work and I'm not sure I understand
the solution you propose. We have a 120GB database. Some of the data
has become static and we are going to move that data (approx 50GB) to
a new table on a seperate filegroup. Immediately following the move,
we will do a backup of that filegroup, and move it to tape. Now, there
will be no changes to the data on this filegroup. It is old data that
we are only keeping around for research. So, 10 months from now, if
something goes wrong on the database and we have to do a restore, are
you saying that I'll have to apply trans. logs for the past 10 months
in order to get this new filegroup back into sync with the rest of the
database? Surely there must be a better way...
Currently, with everything in one filegroup, we are using simple
recovery model. We run a weekly full backup, with nightly differential
backups. We have no need for up to the minute transaction recovery, so
we aren't currently doing a transaction log backup. We find that this
scenario allows us all the recovery we really need. We would simply
like to move some static data out to a new file, do a one-time backup
of that file, and then continue to backup the primary filegroup much
the same way we have been. Is this not possible? What is the simplest
way to accomplish what we need here?
It would be very helpful if someone would post the steps that should
be taken to maintain this type of backup / restore strategy for
filegroups with static data. I've scoured google groups to no avail.
We are running Windows 2000 Server SP4 and SQL Server 2000 SP3. Thanks
for your posts...
Brandon
bspfantasyleague@.dont.spam.me.hotmail.com
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message news:<OjffuA7zDHA.1688@.TK2MSFTNGP10.phx.gbl>...
> Transaction log backups are required to bring the Filegroup back into sync
> with the other filegroups. IF there have been no transactions since the
> filegroup was backed up , then logs are not required.
> You could just do a transaction log backup after loading new data...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "jeff ericson" <anonymous@.discussions.microsoft.com> wrote in message
> news:8C5F4B6A-F8A3-404A-BD26-F9245E061D80@.microsoft.com...
> > We have a situation where a large DB(150GB) has mostly static data in it.
> We were considering a filegroup solution that locates the static data in
> one(or more) files and the dynamic content is stored in another but it seems
> that any file/filegroup restore requires a transaction log restore to
> complete the database. We don't have any transactional requirements, in
> fact they weren't even dumping them till I arrived and restoring them could
> undo changes to the data. The app is a warehouse where any lost data would
> be reloaded. The other alternative is separate databases. Anyone have any
> ideas or suggestions?|||My problem was that after restoring the file group backup, the database was inaccessible until I applied a transaction log backup. Any attempts to access was met with the message indicating the database is in the middle of a restore. In my case, restoring the transaction logs applied transactions I did not want applied. My question is can you selctivily restore file grouops without having to do a transaction log restore afterwards?
up DW's if they can easily redo the data from scratch.
--
Andrew J. Kelly
SQL Server MVP
"jeff ericson" <anonymous@.discussions.microsoft.com> wrote in message
news:8C5F4B6A-F8A3-404A-BD26-F9245E061D80@.microsoft.com...
> We have a situation where a large DB(150GB) has mostly static data in it.
We were considering a filegroup solution that locates the static data in
one(or more) files and the dynamic content is stored in another but it seems
that any file/filegroup restore requires a transaction log restore to
complete the database. We don't have any transactional requirements, in
fact they weren't even dumping them till I arrived and restoring them could
undo changes to the data. The app is a warehouse where any lost data would
be reloaded. The other alternative is separate databases. Anyone have any
ideas or suggestions?|||Transaction log backups are required to bring the Filegroup back into sync
with the other filegroups. IF there have been no transactions since the
filegroup was backed up , then logs are not required.
You could just do a transaction log backup after loading new data...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jeff ericson" <anonymous@.discussions.microsoft.com> wrote in message
news:8C5F4B6A-F8A3-404A-BD26-F9245E061D80@.microsoft.com...
> We have a situation where a large DB(150GB) has mostly static data in it.
We were considering a filegroup solution that locates the static data in
one(or more) files and the dynamic content is stored in another but it seems
that any file/filegroup restore requires a transaction log restore to
complete the database. We don't have any transactional requirements, in
fact they weren't even dumping them till I arrived and restoring them could
undo changes to the data. The app is a warehouse where any lost data would
be reloaded. The other alternative is separate databases. Anyone have any
ideas or suggestions?|||We have a similar situation where I work and I'm not sure I understand
the solution you propose. We have a 120GB database. Some of the data
has become static and we are going to move that data (approx 50GB) to
a new table on a seperate filegroup. Immediately following the move,
we will do a backup of that filegroup, and move it to tape. Now, there
will be no changes to the data on this filegroup. It is old data that
we are only keeping around for research. So, 10 months from now, if
something goes wrong on the database and we have to do a restore, are
you saying that I'll have to apply trans. logs for the past 10 months
in order to get this new filegroup back into sync with the rest of the
database? Surely there must be a better way...
Currently, with everything in one filegroup, we are using simple
recovery model. We run a weekly full backup, with nightly differential
backups. We have no need for up to the minute transaction recovery, so
we aren't currently doing a transaction log backup. We find that this
scenario allows us all the recovery we really need. We would simply
like to move some static data out to a new file, do a one-time backup
of that file, and then continue to backup the primary filegroup much
the same way we have been. Is this not possible? What is the simplest
way to accomplish what we need here?
It would be very helpful if someone would post the steps that should
be taken to maintain this type of backup / restore strategy for
filegroups with static data. I've scoured google groups to no avail.
We are running Windows 2000 Server SP4 and SQL Server 2000 SP3. Thanks
for your posts...
Brandon
bspfantasyleague@.dont.spam.me.hotmail.com
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message news:<OjffuA7zDHA.1688@.TK2MSFTNGP10.phx.gbl>...
> Transaction log backups are required to bring the Filegroup back into sync
> with the other filegroups. IF there have been no transactions since the
> filegroup was backed up , then logs are not required.
> You could just do a transaction log backup after loading new data...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "jeff ericson" <anonymous@.discussions.microsoft.com> wrote in message
> news:8C5F4B6A-F8A3-404A-BD26-F9245E061D80@.microsoft.com...
> > We have a situation where a large DB(150GB) has mostly static data in it.
> We were considering a filegroup solution that locates the static data in
> one(or more) files and the dynamic content is stored in another but it seems
> that any file/filegroup restore requires a transaction log restore to
> complete the database. We don't have any transactional requirements, in
> fact they weren't even dumping them till I arrived and restoring them could
> undo changes to the data. The app is a warehouse where any lost data would
> be reloaded. The other alternative is separate databases. Anyone have any
> ideas or suggestions?|||My problem was that after restoring the file group backup, the database was inaccessible until I applied a transaction log backup. Any attempts to access was met with the message indicating the database is in the middle of a restore. In my case, restoring the transaction logs applied transactions I did not want applied. My question is can you selctivily restore file grouops without having to do a transaction log restore afterwards?
Subscribe to:
Posts (Atom)