Equal disk space needed to delete .bak file?

Kind of an obscure question, but here goes.
SQL2000 SP4
I have a E: drive on my SQL server that is ~400gb. On it you'll find
a ~100gb live DB file and last night's 100gb .bak file (maintenance
plan is set for one day retention). The vendor is telling me that I
am going to run out of space doing the SQL backups because 100gb is
used by the live DB, 100gb is used by last nights .bak file, 100gb
will be used to write today's .bak file, and the last 100gb will be
used in the deletion process of removing the old .bak file.
I can't say I know how SQL handles the removal of the old .bak file
but is this a true statement that SQL will need equal free space to
delete the old .bak file?SQL deletes old backups AFTER creating new ones, so you will need space for
at least two backups.
Look at it this way, what happens if your database blows up during a backup.
If you have an older backup, you are fine. If you are overwriting your only
backup, it is time to polish up the resume.
Personally, I would go out and buy a couple of USB drives and back up to
there. Last week my mother purchased a 500GB model for $99. If that is not
an option, you could look into one of the commercially available backup
compression tools. The three major ones are SQLSafe (www.idera.com),
LiteSpeed for SQL (www.quest.com), and SQL Backup (www.red-gate.com).
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<rockemhard@.gmail.com> wrote in message
news:04b97215-0ec7-4545-a43b-f072fb3c47a2@.e6g2000prf.googlegroups.com...
> Kind of an obscure question, but here goes.
> SQL2000 SP4
> I have a E: drive on my SQL server that is ~400gb. On it you'll find
> a ~100gb live DB file and last night's 100gb .bak file (maintenance
> plan is set for one day retention). The vendor is telling me that I
> am going to run out of space doing the SQL backups because 100gb is
> used by the live DB, 100gb is used by last nights .bak file, 100gb
> will be used to write today's .bak file, and the last 100gb will be
> used in the deletion process of removing the old .bak file.
> I can't say I know how SQL handles the removal of the old .bak file
> but is this a true statement that SQL will need equal free space to
> delete the old .bak file?|||Right, I aggree. In your scenario I need 300gb, not 400gb as the
vendor claims.
100gb - live DB
100gb - last nights backup
100gb - to create tonights backup
--
300gb Total to perform the whole operation
Vendor is saying:
100gb - live DB
100gb - last nights backup
100gb - to create tonights backup
100gb - extra needed to delete last nights backup after a successful
backup
--
400gb Total to perform the whole operation
I just don't understand why that extra 100gb is needed.
On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> SQL deletes old backups AFTER creating new ones, so you will need space fo
r
> at least two backups.
> Look at it this way, what happens if your database blows up during a backu
p.
> If you have an older backup, you are fine. If you are overwriting your on
ly
> backup, it is time to polish up the resume.|||Also, a backup on the same disk as the primary is pretty useless when you
think about it.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<rockemhard@.gmail.com> wrote in message
news:b1987afa-2fde-4bcc-9e25-4d2632f7c009@.j44g2000hsj.googlegroups.com...[vbcol=seagreen]
> Right, I aggree. In your scenario I need 300gb, not 400gb as the
> vendor claims.
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> --
> 300gb Total to perform the whole operation
>
> Vendor is saying:
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> 100gb - extra needed to delete last nights backup after a successful
> backup
> --
> 400gb Total to perform the whole operation
> I just don't understand why that extra 100gb is needed.
> On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:|||Only if it's not getting written to tape ;)
On Nov 28, 11:28 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Also, a backup on the same disk as the primary is pretty useless when you
> think about it.|||I've never seen Windows needing temp storage in order to delete a file. Perh
aps your vendor somehow
confuses the windows Recycle Bin functionality somehow...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<rockemhard@.gmail.com> wrote in message
news:b1987afa-2fde-4bcc-9e25-4d2632f7c009@.j44g2000hsj.googlegroups.com...[vbcol=seagreen]
> Right, I aggree. In your scenario I need 300gb, not 400gb as the
> vendor claims.
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> --
> 300gb Total to perform the whole operation
>
> Vendor is saying:
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> 100gb - extra needed to delete last nights backup after a successful
> backup
> --
> 400gb Total to perform the whole operation
> I just don't understand why that extra 100gb is needed.
> On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:

Equal disk space needed to delete .bak file?

Kind of an obscure question, but here goes.
SQL2000 SP4
I have a E: drive on my SQL server that is ~400gb. On it you'll find
a ~100gb live DB file and last night's 100gb .bak file (maintenance
plan is set for one day retention). The vendor is telling me that I
am going to run out of space doing the SQL backups because 100gb is
used by the live DB, 100gb is used by last nights .bak file, 100gb
will be used to write today's .bak file, and the last 100gb will be
used in the deletion process of removing the old .bak file.
I can't say I know how SQL handles the removal of the old .bak file
but is this a true statement that SQL will need equal free space to
delete the old .bak file?SQL deletes old backups AFTER creating new ones, so you will need space for
at least two backups.
Look at it this way, what happens if your database blows up during a backup.
If you have an older backup, you are fine. If you are overwriting your only
backup, it is time to polish up the resume.
Personally, I would go out and buy a couple of USB drives and back up to
there. Last week my mother purchased a 500GB model for $99. If that is not
an option, you could look into one of the commercially available backup
compression tools. The three major ones are SQLSafe (www.idera.com),
LiteSpeed for SQL (www.quest.com), and SQL Backup (www.red-gate.com).
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<rockemhard@.gmail.com> wrote in message
news:04b97215-0ec7-4545-a43b-f072fb3c47a2@.e6g2000prf.googlegroups.com...
> Kind of an obscure question, but here goes.
> SQL2000 SP4
> I have a E: drive on my SQL server that is ~400gb. On it you'll find
> a ~100gb live DB file and last night's 100gb .bak file (maintenance
> plan is set for one day retention). The vendor is telling me that I
> am going to run out of space doing the SQL backups because 100gb is
> used by the live DB, 100gb is used by last nights .bak file, 100gb
> will be used to write today's .bak file, and the last 100gb will be
> used in the deletion process of removing the old .bak file.
> I can't say I know how SQL handles the removal of the old .bak file
> but is this a true statement that SQL will need equal free space to
> delete the old .bak file?|||Right, I aggree. In your scenario I need 300gb, not 400gb as the
vendor claims.
100gb - live DB
100gb - last nights backup
100gb - to create tonights backup
--
300gb Total to perform the whole operation
Vendor is saying:
100gb - live DB
100gb - last nights backup
100gb - to create tonights backup
100gb - extra needed to delete last nights backup after a successful
backup
--
400gb Total to perform the whole operation
I just don't understand why that extra 100gb is needed.
On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> SQL deletes old backups AFTER creating new ones, so you will need space for
> at least two backups.
> Look at it this way, what happens if your database blows up during a backup.
> If you have an older backup, you are fine. If you are overwriting your only
> backup, it is time to polish up the resume.|||Also, a backup on the same disk as the primary is pretty useless when you
think about it.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<rockemhard@.gmail.com> wrote in message
news:b1987afa-2fde-4bcc-9e25-4d2632f7c009@.j44g2000hsj.googlegroups.com...
> Right, I aggree. In your scenario I need 300gb, not 400gb as the
> vendor claims.
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> --
> 300gb Total to perform the whole operation
>
> Vendor is saying:
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> 100gb - extra needed to delete last nights backup after a successful
> backup
> --
> 400gb Total to perform the whole operation
> I just don't understand why that extra 100gb is needed.
> On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>> SQL deletes old backups AFTER creating new ones, so you will need space
>> for
>> at least two backups.
>> Look at it this way, what happens if your database blows up during a
>> backup.
>> If you have an older backup, you are fine. If you are overwriting your
>> only
>> backup, it is time to polish up the resume.|||Only if it's not getting written to tape ;)
On Nov 28, 11:28 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Also, a backup on the same disk as the primary is pretty useless when you
> think about it.|||I've never seen Windows needing temp storage in order to delete a file. Perhaps your vendor somehow
confuses the windows Recycle Bin functionality somehow...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<rockemhard@.gmail.com> wrote in message
news:b1987afa-2fde-4bcc-9e25-4d2632f7c009@.j44g2000hsj.googlegroups.com...
> Right, I aggree. In your scenario I need 300gb, not 400gb as the
> vendor claims.
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> --
> 300gb Total to perform the whole operation
>
> Vendor is saying:
> 100gb - live DB
> 100gb - last nights backup
> 100gb - to create tonights backup
> 100gb - extra needed to delete last nights backup after a successful
> backup
> --
> 400gb Total to perform the whole operation
> I just don't understand why that extra 100gb is needed.
> On Nov 28, 10:46 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>> SQL deletes old backups AFTER creating new ones, so you will need space for
>> at least two backups.
>> Look at it this way, what happens if your database blows up during a backup.
>> If you have an older backup, you are fine. If you are overwriting your only
>> backup, it is time to polish up the resume.sql

entity relationships and performance

I have large, very heavily used database (SQL 2000). Performance is critical. I have a job that runs on a nightly basis to delete records no longer need from specific tables (as to keep them manageable).

My question, does have entity relationships on these tables adversely affect performance? The cleanup batch job is taking so long now that I'd like to delete table relationships to speed it up.

I understand the value for maintaining data integerity, but any insight as to how relationships affect performance?

Thanks.Well, I delete the relationships. The batch job that deleted records went from 6-10 hours to 22 minutes.

So, while I don't think there is a major performance hit by having relationships established and doing selects/inserts/updates, there obviously is when it comes to deletes.|||whether you are doing inserts, updates or deletes there wil lalways be a "HIT" due to RI. Insert or Update 10,000 records at one time, both with and without RI and you will see a diffrence. The question is does removal of RI justify the speed over potentialy corrupted data.|||Obviously, the performance gain was substantial. Are there any performance gains via relationships, i.e. does the query optimizer leverage relationships when creating execution plans?|||No. The optimizer looks at indexes, statistics and hints to decide the best query plan.
Referential integrity is there solely to enforce the quality of the data.

Enterprise Manager & User permitions

I installed Enterprise Manager on user PC and now want him to be able to
delete and restore databases. Where do I need go to do this? I'm new in SQL
server.
Thank you,
AlexMake this login a member of the dbcreator fixed server role. For example:
sp_addsrvrolemember 'loginname','dbcreator'
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Alex" <alazor@.poli-film.net> wrote in message
news:uOg8vQo5DHA.2404@.TK2MSFTNGP11.phx.gbl...
> I installed Enterprise Manager on user PC and now want him to be able to
> delete and restore databases. Where do I need go to do this? I'm new in
SQL
> server.
> Thank you,
> Alex
>|||I did add but it doesn't let him delete the database. It gives an error
message 3704 "User doesn't have permissions to delete database... SQL
Server-DMO ODBC SQL State 4300..."
I don't know if it has something to do with this but my SQL Server 2000 is
setup for "SQL Server and Windows Authentication" Any idea?
Thanks!
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:OPW6Vio5DHA.1632@.TK2MSFTNGP12.phx.gbl...
> Make this login a member of the dbcreator fixed server role. For example:
> sp_addsrvrolemember 'loginname','dbcreator'
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 Programming by Example
>
> "Alex" <alazor@.poli-film.net> wrote in message
> news:uOg8vQo5DHA.2404@.TK2MSFTNGP11.phx.gbl...
> > I installed Enterprise Manager on user PC and now want him to be able to
> > delete and restore databases. Where do I need go to do this? I'm new in
> SQL
> > server.
> >
> > Thank you,
> > Alex
> >
> >
>|||Then something weird is going on. Try this script to see if it works:
sp_addlogin 'testcreatedb','testcreatedb'
go
sp_addsrvrolemember 'testcreatedb','dbcreator'
go
-- now logon using the new login and run the following
create database test
go
drop database test
go
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Alex" <alazor@.poli-film.net> wrote in message
news:u%23SYrHp5DHA.2628@.TK2MSFTNGP10.phx.gbl...
> I did add but it doesn't let him delete the database. It gives an error
> message 3704 "User doesn't have permissions to delete database... SQL
> Server-DMO ODBC SQL State 4300..."
> I don't know if it has something to do with this but my SQL Server 2000 is
> setup for "SQL Server and Windows Authentication" Any idea?
> Thanks!
>
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:OPW6Vio5DHA.1632@.TK2MSFTNGP12.phx.gbl...
> > Make this login a member of the dbcreator fixed server role. For
example:
> > sp_addsrvrolemember 'loginname','dbcreator'
> >
> > --
> > Carlos E. Rojas
> > SQL Server MVP
> > Co-Author SQL Server 2000 Programming by Example
> >
> >
> > "Alex" <alazor@.poli-film.net> wrote in message
> > news:uOg8vQo5DHA.2404@.TK2MSFTNGP11.phx.gbl...
> > > I installed Enterprise Manager on user PC and now want him to be able
to
> > > delete and restore databases. Where do I need go to do this? I'm new
in
> > SQL
> > > server.
> > >
> > > Thank you,
> > > Alex
> > >
> > >
> >
> >
>