[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server Backups

Posted on 2011-10-18
16
Medium Priority
?
822 Views
Last Modified: 2012-05-12
Hello!

I have been called about a SQL Server whose disk space is being eaten at roughly 6GB/day from SQL Server backups.  They go back about 80 days.  I would assume them to be full backups, as they are each around 800MB and getting steadily a little larger every day, so 80 days worth would not be necessary.  However, I need to know:

How do I view backup settings in SQL Server 2005?
Is there a way to tell it to use only so much space or only keep so many days' worth of backups?

Any other suggestions I don't know to ask?

Thanks!
0
Comment
Question by:PowerEdgeTech
  • 7
  • 6
  • 3
16 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 36988519
It depends on how you are doing your backups.  You could be getting backups from maintenance plans or just TSQL tasks in SQL Agent.
0
 
LVL 33

Author Comment

by:PowerEdgeTech
ID: 36988572
Ok ... how do I tell which?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 36988600
Look in SQL Agent Jobs and maintenance plans.  Hopefully (probably) they are being executed from the local server.  People usually do staged deletion of backups.  I do full backups only, not transaction logs.  I keep 2 weeks of full backups.  One month of weekly a sunday backup.  And 3 months of a monthly backup.  
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Author Comment

by:PowerEdgeTech
ID: 36988977
Ok ... I'm not exactly sure what I'm looking for or at.

Under SQL Server Agent, there is a Job Activity monitor that only shows two jobs running every night at 12AM (that is the timestamp for all the BAK files).  One is "DataBase Maintenance.Subplan_1", category "Database Maintenance".  When I check the Properties for it, under Schedules, it shows the Name as DSI "DataBase Backup", so I'm assuming this is the one I'm looking for, but I cannot see any properties for this.  If I Edit it, all I can do is change its schedule.  So, is there a way that I can confirm it is doing "full" backups so I can safely delete the older BAK files?  or is that the only type of built-in backup that SQL can do?  Where do I see this scheduled backup job's configuration?  You are also saying that SQL Server cannot/does not auto-delete BAK files ... I would just need to delete them manually (or automate it)?

Thanks.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 36989645
You will find it under maintenance plans which is located under management.  I don't believe you will have access to it if you are using SQL Server Management Studio Express though, but I could be mistaken.  SQL Server can delete the backups, but it's a separate step that it takes outside of doing the backup.  It is configurable within the maintenance plan.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36997456
>>I don't believe you will have access to it if you are using SQL Server Management Studio Express though, but I could be mistaken.<<
It cannot be SQL Server Express as they are using SQL Server Agent.

Rather than going into the Maintenance Plans, which will be confusing, they probably should just check the text file generated that logs the activity.  If there is one present the path should be listed in the Job Step towards the end of the command in the SQL Server Agent job.
0
 
LVL 33

Author Comment

by:PowerEdgeTech
ID: 36997507
Thanks guys ... will check this tomorrow.  Been busy ... they had a power outage last night and two of their servers (including this one) didn't come back up :)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 36997708
AC.... I was just talking about the management studio they we reusing to using to attach to the instance, not suggesting the instance itself was Express.  I use SSMS from the Enterprise media so I can't test the experience of SSMSE attaching to standard or higher SQL Server.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36999241
>>I was just talking about the management studio they we reusing to using to attach to the instance<<
Got it.  Sorry I misunderstood.
0
 
LVL 33

Author Comment

by:PowerEdgeTech
ID: 37001916
AC ... where will I find this text file that is generated that logs the activity?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37002189
Jobs that executed Maintenance Plans used to have a path to a report that logged all activity.  Since going to SQL Server 2005 and SSIS, that may not be true (I have never used Maintenance Plans, so I am not sure), but it is worth checking:
1. Look in SQL Server Agent
2. Find the Job.
3. Select Properties.
4. Select  the Steps tab.
5. Edit
6. See if the command line contains a parameter for a log file.
0
 
LVL 33

Author Comment

by:PowerEdgeTech
ID: 37002561
It does not appear to:

/SQL "Maintenance Plans\DSI DataBase maintenance" /SERVER DSISERVER  /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package\Subplan_1.Disable";false /REPORTING E

Other ideas?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 37002576
Look at the maintenance plan.  Your question is:

How do I view backup settings in SQL Server 2005?
Is there a way to tell it to use only so much space or only keep so many days' worth of backups?

If you look at the plan you will find the backups settings and cleanup options.
0
 
LVL 33

Author Comment

by:PowerEdgeTech
ID: 37002592
Ok, help me out here ... where exactly do I find the "Maintenance Plans"?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 37002626
0
 
LVL 33

Author Closing Comment

by:PowerEdgeTech
ID: 37002924
Sorry, I missed the "under [M]anagement" part, assuming we were talking about SQL Server Agent or SQL Server Agent\Jobs.

I setup a cleanup to delete the BAK files after 7 days, which should free up a lot of space.

Thanks.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Loops Section Overview
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question