SQL Clone
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
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

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

Gethyn Ellisgethynellis.com
Taggs
Taggs
Mr or Mrs. 500
Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)

Group: General Forum Members
Points: 536 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
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4990 Visits: 2932
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-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 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
Mr or Mrs. 500
Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)

Group: General Forum Members
Points: 536 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
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4990 Visits: 2932
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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2451 Visits: 963
Nice article!
jasona.work
jasona.work
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: 21809 Visits: 13935
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
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 204
Thanq for your article.
Gethyn Ellis
Gethyn Ellis
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4990 Visits: 2932
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