Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


hoe to stop creating a daily database backup file


hoe to stop creating a daily database backup file

Author
Message
Sergei Zarembo
Sergei Zarembo
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 557
Hi All

I have a SQL Server 2008 Enterprise Ed sitting on 64-bit WinServer 2008. The instance is used strictly for development work, so taking care of a DR situation is not a priority. Apart for that, all the data is backed/replicated by SAN through our datacenter.

I don't have a ton of space on the hard drive and would like to stop creating a daily backup file for the biggest of my databases: 300 Gb in size. The daily back file for the database is ~ 50Gb,
and i'd like not to have it created every day in \Backup folder. How would i do this modification? I cannot find the answer in Books Online.

Thank you.

Sergei Z
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2725 Visits: 715
I worry a bit because you're asking this question, however, you must have a job to produce the daily backup so edit/cancel the job.
If you're using a maint plan ( which I never do ) you'll have to edit it I expect, can't help there as I don't use them. You canfind the plans through SSMS

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Bru Medishetty
Bru Medishetty
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 Visits: 1950
You need to find out, how is that backup being generated / created and disable or delete that.

As suggested earlier, you should be able to see a job and disable it. A Maintenance plan might have been created or a SQL Server Job is created which performs the backup.. So I would start looking in the Jobs..


Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13686
Thats a hell of a setup for a DEV server, can I have some of your budget? :-)

You have a 300GB database but a 50Gb backup. You have 2008 enterprise edt. so are you using the compression option in the backup? If not get it turned on. If its not turned on also shrink the database to get some space back.

don't rely on the SAN replication as your database backup, it doesn't work like that.

If you are not sure how to do the above speak to your DBA (with this setup you must have one?)

---------------------------------------------------------------------
Sergei Zarembo
Sergei Zarembo
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 557
Thank to All for your prompt answers!, they helped. I identified a couple of daily jobs that

1) shrink all databases
2) do daily backup for all databases.

the jobs are owned by SUNGARD [our datacenter people, we outsource our data solutions to this company], not me. The jobs are scheduled by SA account, I'm only a developer/user of the system [though I own ETL databases on the server], so i guess i cannot mess with the jobs, will have to call them to negotiate.

..yeah the setup is impressive, the Winxerver box has 16 processers and 128 GB of RAM, so it practicaly flies unimpeded.

Thanks again,

Sergei Z
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4461 Visits: 9825
Sergei Zarembo (2/16/2010)

1) shrink all databases


Really bad idea to schedule a shrink of the databases every night. Why do this, just so the database can grow the next day, causing file fragmentation and index fragmentation?

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Sergei Zarembo
Sergei Zarembo
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 557
Jeffrey Williams-493691 (2/16/2010)
Sergei Zarembo (2/16/2010)

1) shrink all databases


Really bad idea to schedule a shrink of the databases every night. Why do this, just so the database can grow the next day, causing file fragmentation and index fragmentation?



Jeffry,

can you tell me what might be the rationale behind the daily shrinking job? Do they try to save some disk space? if so, how much of disk space (in %) can be saved this way? I need to negotiate with our DBAs on canceling this job, because it does seem to defragment my indexes badly in an Informatica ETL database I owe.

Thank you,

Sergei Z
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21067 Visits: 18258
The amount of space you save depends. I would focus less on the disk space saved by the daily shrink and more on the reciprocal effects of that shrink. Increased IO while performing the shrink and growth. Locking and Blocking while the database file grows. Increased job run times to shrink the file after every backup.

All of these factors are determined by your environment. If they are running out of disk space, they might shrink the files. A better solution would be to get more disk space.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search