SQL Server Backups

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!
LVL 33
PowerEdgeTechIT ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
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
PowerEdgeTechIT ConsultantAuthor Commented:
Ok ... how do I tell which?
0
BrandonGalderisiCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

PowerEdgeTechIT ConsultantAuthor Commented:
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
BrandonGalderisiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>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
PowerEdgeTechIT ConsultantAuthor Commented:
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
BrandonGalderisiCommented:
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
Anthony PerkinsCommented:
>>I was just talking about the management studio they we reusing to using to attach to the instance<<
Got it.  Sorry I misunderstood.
0
PowerEdgeTechIT ConsultantAuthor Commented:
AC ... where will I find this text file that is generated that logs the activity?
0
Anthony PerkinsCommented:
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
PowerEdgeTechIT ConsultantAuthor Commented:
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
BrandonGalderisiCommented:
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
PowerEdgeTechIT ConsultantAuthor Commented:
Ok, help me out here ... where exactly do I find the "Maintenance Plans"?
0
BrandonGalderisiCommented:
0
PowerEdgeTechIT ConsultantAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.