Drive space for db and log backups

Hello all,
Can anyone tell me what is the ideal free space on a disk to allow for db
and log backups on a Full Recovery Model? My db is 4GB, and my log is 2.5GB.
How much free space should I keep to safely run regular backups?
Sincerely,
Gerald
None.
Backups should not be stored on the same machine, much less the same disks,
as your primary data store. Decide on your disaster recovery plan,
calculate how many backups you need to make it bulletproof, and then buy
disk space accordingly. Backup storage space doesn't have to be enterprise
grade stuff, but it should be reasonable reliable and fault tolerant.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:351EC7E4-1142-48C8-BEB0-4677D22FA78E@.microsoft.com...
> Hello all,
> Can anyone tell me what is the ideal free space on a disk to allow for db
> and log backups on a Full Recovery Model? My db is 4GB, and my log is
> 2.5GB.
> How much free space should I keep to safely run regular backups?
> --
> Sincerely,
> Gerald
|||Geoff,
Thanks for your response. I do have backup files on a different partition.
What I'm asking is how much disk space should I have to ensure that the db
and log backup processes will take place safely?
Sincerely,
Gerald
"Geoff N. Hiten" wrote:

> None.
> Backups should not be stored on the same machine, much less the same disks,
> as your primary data store. Decide on your disaster recovery plan,
> calculate how many backups you need to make it bulletproof, and then buy
> disk space accordingly. Backup storage space doesn't have to be enterprise
> grade stuff, but it should be reasonable reliable and fault tolerant.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
> news:351EC7E4-1142-48C8-BEB0-4677D22FA78E@.microsoft.com...
>
|||I am unclear. If you are asking how much database and log space are
consumed during a backup operation, the answer is only enough log to hopd
the transaction history recorded during a full backup. Backups take space
equal to the allocated portion of the database in question plus the segment
of the log modified during the backup operation. Backups, in and of
themselves, do not force expansion of the log or the data files. As for how
much space, that depends on how many iterations of your backup you intend to
keep online at any given moment.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for your response. I do have backup files on a different partition.
> What I'm asking is how much disk space should I have to ensure that the db
> and log backup processes will take place safely?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
|||Geoff,
Thanks for the response. What I'm asking is does the backup process use any
space on the same partition as the mdf or the ldf during the backup process
itself, which it would then clean up afterwards? In other words, can the
partition be full or close to full and allow SQL Server to successfully
backup the db and the log?
Sincerely,
Gerald
"Geoff N. Hiten" wrote:

> I am unclear. If you are asking how much database and log space are
> consumed during a backup operation, the answer is only enough log to hopd
> the transaction history recorded during a full backup. Backups take space
> equal to the allocated portion of the database in question plus the segment
> of the log modified during the backup operation. Backups, in and of
> themselves, do not force expansion of the log or the data files. As for how
> much space, that depends on how many iterations of your backup you intend to
> keep online at any given moment.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
> news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...
>
|||Ahh. It becomes clearer.
SQL does not use any file resources except the MDF, LDF, and .BAK files
specified to accomplish a backup.
One exception may be if you use the WITH STANDBY option to take the database
into standby mode. There must be room for the standby file, but you can
specify any valid local storage location for that file.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:F679B7FB-F8D3-48B2-9EB9-30460D9A8401@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for the response. What I'm asking is does the backup process use
> any
> space on the same partition as the mdf or the ldf during the backup
> process
> itself, which it would then clean up afterwards? In other words, can the
> partition be full or close to full and allow SQL Server to successfully
> backup the db and the log?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
|||A different partition on the same physical drive or array does nothing to
alleviate the issue Geoff mentioned. You need to backup to a different
physical drive or drive array to get the real benefits.
Andrew J. Kelly SQL MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for your response. I do have backup files on a different partition.
> What I'm asking is how much disk space should I have to ensure that the db
> and log backup processes will take place safely?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
|||Geoff,
Yes!! That's the question I wanted answered ... and my answer. Thanks for
hanging in there with me.
Sincerely,
Gerald
"Geoff N. Hiten" wrote:

> Ahh. It becomes clearer.
> SQL does not use any file resources except the MDF, LDF, and .BAK files
> specified to accomplish a backup.
> One exception may be if you use the WITH STANDBY option to take the database
> into standby mode. There must be room for the standby file, but you can
> specify any valid local storage location for that file.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
> news:F679B7FB-F8D3-48B2-9EB9-30460D9A8401@.microsoft.com...
>
|||Andrew,
Thanks for your response. Actually, the question was the one that Geoff
answered finally.
Sincerely,
Gerald
"Andrew J. Kelly" wrote:

> A different partition on the same physical drive or array does nothing to
> alleviate the issue Geoff mentioned. You need to backup to a different
> physical drive or drive array to get the real benefits.
> --
> Andrew J. Kelly SQL MVP
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
> news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...
>
>

Drive space for db and log backups

Hello all,
Can anyone tell me what is the ideal free space on a disk to allow for db
and log backups on a Full Recovery Model? My db is 4GB, and my log is 2.5GB.
How much free space should I keep to safely run regular backups?
Sincerely,
GeraldNone.
Backups should not be stored on the same machine, much less the same disks,
as your primary data store. Decide on your disaster recovery plan,
calculate how many backups you need to make it bulletproof, and then buy
disk space accordingly. Backup storage space doesn't have to be enterprise
grade stuff, but it should be reasonable reliable and fault tolerant.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:351EC7E4-1142-48C8-BEB0-4677D22FA78E@.microsoft.com...
> Hello all,
> Can anyone tell me what is the ideal free space on a disk to allow for db
> and log backups on a Full Recovery Model? My db is 4GB, and my log is
> 2.5GB.
> How much free space should I keep to safely run regular backups?
> --
> Sincerely,
> Gerald|||Geoff,
Thanks for your response. I do have backup files on a different partition.
What I'm asking is how much disk space should I have to ensure that the db
and log backup processes will take place safely?
Sincerely,
Gerald
"Geoff N. Hiten" wrote:

> None.
> Backups should not be stored on the same machine, much less the same disks
,
> as your primary data store. Decide on your disaster recovery plan,
> calculate how many backups you need to make it bulletproof, and then buy
> disk space accordingly. Backup storage space doesn't have to be enterpris
e
> grade stuff, but it should be reasonable reliable and fault tolerant.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in messag
e
> news:351EC7E4-1142-48C8-BEB0-4677D22FA78E@.microsoft.com...
>|||I am unclear. If you are asking how much database and log space are
consumed during a backup operation, the answer is only enough log to hopd
the transaction history recorded during a full backup. Backups take space
equal to the allocated portion of the database in question plus the segment
of the log modified during the backup operation. Backups, in and of
themselves, do not force expansion of the log or the data files. As for how
much space, that depends on how many iterations of your backup you intend to
keep online at any given moment.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for your response. I do have backup files on a different partition.
> What I'm asking is how much disk space should I have to ensure that the db
> and log backup processes will take place safely?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
>|||Geoff,
Thanks for the response. What I'm asking is does the backup process use any
space on the same partition as the mdf or the ldf during the backup process
itself, which it would then clean up afterwards? In other words, can the
partition be full or close to full and allow SQL Server to successfully
backup the db and the log?
--
Sincerely,
Gerald
"Geoff N. Hiten" wrote:

> I am unclear. If you are asking how much database and log space are
> consumed during a backup operation, the answer is only enough log to hopd
> the transaction history recorded during a full backup. Backups take space
> equal to the allocated portion of the database in question plus the segmen
t
> of the log modified during the backup operation. Backups, in and of
> themselves, do not force expansion of the log or the data files. As for h
ow
> much space, that depends on how many iterations of your backup you intend
to
> keep online at any given moment.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in messag
e
> news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...
>|||Ahh. It becomes clearer.
SQL does not use any file resources except the MDF, LDF, and .BAK files
specified to accomplish a backup.
One exception may be if you use the WITH STANDBY option to take the database
into standby mode. There must be room for the standby file, but you can
specify any valid local storage location for that file.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:F679B7FB-F8D3-48B2-9EB9-30460D9A8401@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for the response. What I'm asking is does the backup process use
> any
> space on the same partition as the mdf or the ldf during the backup
> process
> itself, which it would then clean up afterwards? In other words, can the
> partition be full or close to full and allow SQL Server to successfully
> backup the db and the log?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
>|||A different partition on the same physical drive or array does nothing to
alleviate the issue Geoff mentioned. You need to backup to a different
physical drive or drive array to get the real benefits.
Andrew J. Kelly SQL MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for your response. I do have backup files on a different partition.
> What I'm asking is how much disk space should I have to ensure that the db
> and log backup processes will take place safely?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
>|||Geoff,
Yes!! That's the question I wanted answered ... and my answer. Thanks for
hanging in there with me.
Sincerely,
Gerald
"Geoff N. Hiten" wrote:

> Ahh. It becomes clearer.
> SQL does not use any file resources except the MDF, LDF, and .BAK files
> specified to accomplish a backup.
> One exception may be if you use the WITH STANDBY option to take the databa
se
> into standby mode. There must be room for the standby file, but you can
> specify any valid local storage location for that file.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in messag
e
> news:F679B7FB-F8D3-48B2-9EB9-30460D9A8401@.microsoft.com...
>|||Andrew,
Thanks for your response. Actually, the question was the one that Geoff
answered finally.
Sincerely,
Gerald
"Andrew J. Kelly" wrote:

> A different partition on the same physical drive or array does nothing to
> alleviate the issue Geoff mentioned. You need to backup to a different
> physical drive or drive array to get the real benefits.
> --
> Andrew J. Kelly SQL MVP
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in messag
e
> news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...
>
>

Drive out of space

Hello,
The drive my DB is on only has 10 % space available. Would the Shrink
option free up some space for this DB?
Thanks
Hi,
Check the usage of database using
use dbname
go
sp_spaceused @.updateusage='true'
For LDF file see
dbcc sqlperf(logspace)
If either of MDF or LDF have space you can backup the database and shrink
the file
If LDF can be shrunk then:-
1. Backup the transaction log or truncate the transaction log
2. dbcc shrinkfile
Thanks
Hari
MCDBA
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407090906.4da4e71e@.posting.google.c om...
> Hello,
> The drive my DB is on only has 10 % space available. Would the Shrink
> option free up some space for this DB?
>
> Thanks
|||Beware that using the updateusage=true option will run DBCC UPDATEUSAGE on
all the tables using table S locks, effectively making them read-only during
the operation.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVYPLfdZEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Check the usage of database using
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For LDF file see
> dbcc sqlperf(logspace)
> If either of MDF or LDF have space you can backup the database and shrink
> the file
> If LDF can be shrunk then:-
> 1. Backup the transaction log or truncate the transaction log
> 2. dbcc shrinkfile
> Thanks
> Hari
> MCDBA
> "Craig Alexander" <craig@.itas.net> wrote in message
> news:2582929c.0407090906.4da4e71e@.posting.google.c om...
>

Drive out of space

Hello,
The drive my DB is on only has 10 % space available. Would the Shrink
option free up some space for this DB?
ThanksHi,
Check the usage of database using
use dbname
go
sp_spaceused @.updateusage='true'
For LDF file see
dbcc sqlperf(logspace)
If either of MDF or LDF have space you can backup the database and shrink
the file
If LDF can be shrunk then:-
1. Backup the transaction log or truncate the transaction log
2. dbcc shrinkfile
Thanks
Hari
MCDBA
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407090906.4da4e71e@.posting.google.com...
> Hello,
> The drive my DB is on only has 10 % space available. Would the Shrink
> option free up some space for this DB?
>
> Thanks|||Beware that using the updateusage=true option will run DBCC UPDATEUSAGE on
all the tables using table S locks, effectively making them read-only during
the operation.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVYPLfdZEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Check the usage of database using
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For LDF file see
> dbcc sqlperf(logspace)
> If either of MDF or LDF have space you can backup the database and shrink
> the file
> If LDF can be shrunk then:-
> 1. Backup the transaction log or truncate the transaction log
> 2. dbcc shrinkfile
> Thanks
> Hari
> MCDBA
> "Craig Alexander" <craig@.itas.net> wrote in message
> news:2582929c.0407090906.4da4e71e@.posting.google.com...
>

Drive out of space

Hello,
The drive my DB is on only has 10 % space available. Would the Shrink
option free up some space for this DB?
ThanksHi,
Check the usage of database using
use dbname
go
sp_spaceused @.updateusage='true'
For LDF file see
dbcc sqlperf(logspace)
If either of MDF or LDF have space you can backup the database and shrink
the file
If LDF can be shrunk then:-
1. Backup the transaction log or truncate the transaction log
2. dbcc shrinkfile
Thanks
Hari
MCDBA
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407090906.4da4e71e@.posting.google.com...
> Hello,
> The drive my DB is on only has 10 % space available. Would the Shrink
> option free up some space for this DB?
>
> Thanks|||Beware that using the updateusage=true option will run DBCC UPDATEUSAGE on
all the tables using table S locks, effectively making them read-only during
the operation.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVYPLfdZEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Check the usage of database using
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For LDF file see
> dbcc sqlperf(logspace)
> If either of MDF or LDF have space you can backup the database and shrink
> the file
> If LDF can be shrunk then:-
> 1. Backup the transaction log or truncate the transaction log
> 2. dbcc shrinkfile
> Thanks
> Hari
> MCDBA
> "Craig Alexander" <craig@.itas.net> wrote in message
> news:2582929c.0407090906.4da4e71e@.posting.google.com...
> > Hello,
> >
> > The drive my DB is on only has 10 % space available. Would the Shrink
> > option free up some space for this DB?
> >
> >
> > Thanks
>

drive not visible in the SQL server

Hi all,
Here is my problem:
I've having trouble to make my second logical drive available for database
under SQL server 2005 (Other words: I cannot move database to my second
database drive because I cannot see it in SQL server 2005)
Config:
- Cluster Acitve/passive
- 2 nodes (Win 2003 server SE SP2)
- 4 drives configured:
* drive 1 for quorum
* drive 2 for fro tempdb
* drive 3 for my database (old) and logs
* drive 4 for my database (where I want to move to)
- SQL server 2005 SP1
This new drive is properly configured (or so I think) in the cluster admin,
as a physical drive part of my SQLserver Data group resource (as my old drive
is) and is showing up active in my resource list.
I can also browse it as normal from Windows
When trying to move the database in SQL server 2005, I can only browse my
current database drive and I cannot see the new one.
Any ideas what am I missing ?
Thanks a lot for your help.
Julien
You have to make the SQL service dependant on the new drive resource. This
will require taking SQL offline when making the change.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Julien" <Julien@.discussions.microsoft.com> wrote in message
news:84B592EF-FD04-447B-8456-25ABE59E6FF1@.microsoft.com...
> Hi all,
> Here is my problem:
> I've having trouble to make my second logical drive available for database
> under SQL server 2005 (Other words: I cannot move database to my second
> database drive because I cannot see it in SQL server 2005)
> Config:
> - Cluster Acitve/passive
> - 2 nodes (Win 2003 server SE SP2)
> - 4 drives configured:
> * drive 1 for quorum
> * drive 2 for fro tempdb
> * drive 3 for my database (old) and logs
> * drive 4 for my database (where I want to move to)
> - SQL server 2005 SP1
>
> This new drive is properly configured (or so I think) in the cluster
> admin,
> as a physical drive part of my SQLserver Data group resource (as my old
> drive
> is) and is showing up active in my resource list.
> I can also browse it as normal from Windows
> When trying to move the database in SQL server 2005, I can only browse my
> current database drive and I cannot see the new one.
> Any ideas what am I missing ?
> Thanks a lot for your help.
> Julien
>
|||Hi Geoff,
Thanks a lot for your reply.
It's definitely making sens to me but I'm not sure how to do this.
Could you please l explain a bit further?
Thanks,
Julien
"Geoff N. Hiten" wrote:

> You have to make the SQL service dependant on the new drive resource. This
> will require taking SQL offline when making the change.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Julien" <Julien@.discussions.microsoft.com> wrote in message
> news:84B592EF-FD04-447B-8456-25ABE59E6FF1@.microsoft.com...
>
|||After a bit of diggin, I found and corrected the options.
Located in the SQLserver ressource of the cluster, once put offline, just
added the correct dependencies.
Thanks a lot for the Geoff.
Julien
"Julien" wrote:
[vbcol=seagreen]
> Hi Geoff,
> Thanks a lot for your reply.
> It's definitely making sens to me but I'm not sure how to do this.
> Could you please l explain a bit further?
> Thanks,
> Julien
> "Geoff N. Hiten" wrote:

Drive missing from Enterprise Manager

Hi,

I have an SQL cluster on Windows 2003 with 2 drives (1 for dbs and 1 for logs). Both of these drives are accessible from Windows explorer.

In Enterprise Manager only the db drive appears as a location to house dbs and logs.

I can't work out how to get the second drive to appear and adding the path manual doesn't work as I end up with the other drive letter appending to the front eg "y:\z:\logs"

Has anyone seen this or have any ideas how to fix it?

Help much appreciated.What's your cluster like? Active/Passive or Active/Active?|||The cluster is active/passive.

Just figured it out, I forgot to add the log drive to the dependennce list for the SQL service.