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


Backup and Housekeeping with Maintenance Plans


Backup and Housekeeping with Maintenance Plans

Author
Message
Gethyn Ellis
Gethyn Ellis
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1145 Visits: 2867
Comments posted to this topic are about the item Backup and Housekeeping with Maintenance Plans

Gethyn Ellisgethynellis.com
Taggs
Taggs
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 134
I usually perform the clean up before the backups, that way, it will make the most efficient use of disk space and backups are less likely to fail due to lack of space.
Gethyn Ellis
Gethyn Ellis
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1145 Visits: 2867
Taggs (1/26/2011)
I usually perform the clean up before the backups, that way, it will make the most efficient use of disk space and backups are less likely to fail due to lack of space.


That's a fair point the only thing I would say to that is make sure you don't leave yourself in a situation where you deleted the backup and don't have a new one. (If you only keep one backup)

Gethyn Ellisgethynellis.com
Martin Hawley
Martin Hawley
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 144
Good article and led me to check our backup plans set up by a contractor. Discovered that there is no clearing of the backup and restore history and msdb has grown from 12Mb to over 3Gb!
Thanks for the heads up.
Taggs
Taggs
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 134
Yes, We store 2 days SQL backups locally but also copy these backups on to our 3rd party corporate backup system.
Gethyn Ellis
Gethyn Ellis
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1145 Visits: 2867
Martin Hawley (1/26/2011)
Good article and led me to check our backup plans set up by a contractor. Discovered that there is no clearing of the backup and restore history and msdb has grown from 12Mb to over 3Gb!
Thanks for the heads up.


I'm pleased you found it useful.

Gethyn Ellisgethynellis.com
cy-dba
cy-dba
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 963
Nice article!
jasona.work
jasona.work
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2321 Visits: 11204
Thank you! I'd not looked at the History Cleanup task, so I didn't know what it might do (yes, I'm rather new to managing an SQL Server, and don't have a mentor)

So, I took a look at a maintenance plan I had set up to backup several DBs on a server, and checked the msdb size. 108GB!! So I added in a History cleanup at the end, and will see if that shrinks it down tonight when the plan executes!

Thank you from a novice!
Jason A.
phani31
phani31
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 204
Thanq for your article.
Gethyn Ellis
Gethyn Ellis
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1145 Visits: 2867
jasona.work (1/26/2011)
Thank you! I'd not looked at the History Cleanup task, so I didn't know what it might do (yes, I'm rather new to managing an SQL Server, and don't have a mentor)

So, I took a look at a maintenance plan I had set up to backup several DBs on a server, and checked the msdb size. 108GB!! So I added in a History cleanup at the end, and will see if that shrinks it down tonight when the plan executes!

Thank you from a novice!
Jason A.


Jason, just a word of warning make sure that the cleanup task when deleting 108GB of data does not block other scheduled backups occuring, it maybe an idea to cleanup in smaller chunks instead of one large hit.

Gethyn Ellisgethynellis.com
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