January 16, 2009 at 8:20 am
Hi ,
I am facing issue while taking the snap manager backups as the backup drive I've is smaller in size and there are minimal chances of getting more space. Snap Manager backup is taking full backup of master databases. My biggest master database is msdb and its backup size is 336MB. is there anyway to shrink the msdb safely so that the backups can be accommodated on the backup drive?
MSDB database size is 355 MB and space available is 3.25 MB.
Thanks,
Sam
January 16, 2009 at 10:13 am
s
January 16, 2009 at 10:39 am
Before looking at deleting data, I would look else where to create space. Are you saying that you only have 3.? MB of free disk space?
I would look at trying to shrink the log files first.
If you have more free disk space and you can increase the space allocated to the database. Any data you delete from the database will be gone and not reported for any reports so deleting data usually isn't a first selection for gaining space.
January 16, 2009 at 10:51 am
I am with Ffalcon.Try shrinking database and log files.
http://msdn.microsoft.com/en-us/library/ms190488.aspx.
My question is that if u dont have enough space in backup disk then how will u take backup of user databases??
If u have any shared disk try taking backup to that location.
January 16, 2009 at 1:11 pm
free space 3 MB means when I right click and selected the properties of msdb database, it gave me the figure 3MB as space available, so from this I meant that there is no free space in msdb database and I can't just shrink that database..
Yes we have very small backup drive... and just tring to lower the size of the msdb database so that my backups can work.....user database is of only 10MB..
January 16, 2009 at 1:56 pm
I'm curious to know whether your data or t-log file is consuming all of this space (mdf or ldf respectively).
You can run "sp_helpdb msdb" and it should show you the file size of each.
If your log file is large, I suspect you'll find that MSDB is in FULL recovery mode and you haven't been doing any t-log backups. You can set the recovery to SIMPLE if you do not need point in time backups of the information in MSDB or start taking t-log backups if you do.
If your data files is the larger portion, run the script located here:
http://www.databasejournal.com/img/BigTables.sql and that will tell you what tables are consuming all the space.
If the top tables all start with "backup", your space is consumed with backup history. Look up the syntax for sp_delete_backuphistory and that will allow you to purge out all the history you don't want.
If it is neither, post the results of the large tables script here and someone should be able to point you in the right direction.
PS -- the sp_delete_backuphistory is a slow dog. If you are unhappy with the performance, two indexes can be created on the backupset table -- one on media_set_id and one on both backup_set_id and media_set_id.
Kyle
January 16, 2009 at 2:25 pm
340 MB taken by my data file and only 16 mb by the logfile
sysjobhistory1000 3528 KB2920 KB144 KB464 KB
backupset2964 2312 KB1624 KB408 KB280 KB
DTA_reports_querycolumn17741 1488 KB760 KB688 KB40 KB
backupmediafamily 2964 1104 KB480 KB336 KB288 KB
backupmediaset2964 720 KB312 KB312 KB96 KB
DTA_tuninglog6646 712 KB688 KB16 KB8 KB
DTA_reports_queryindex6715 592 KB296 KB280 KB16 KB
backupfilegroup2964 456 KB368 KB16 KB72 KB
sysmail_log1107 328 KB264 KB16 KB48 KB
DTA_reports_querytable2622 272 KB112 KB128 KB32 KB
DTA_reports_column1650 200 KB104 KB80 KB16 KB
January 16, 2009 at 2:35 pm
With what you indicated I would archive the Backup drive to DVD or CD and they delete the backups from the backup drive. How large is this backup drive? Please try to provide as much info regarding your system and how you do your backups.
January 16, 2009 at 10:28 pm
gagsam2001 (1/16/2009)
Hi ,I am facing issue while taking the snap manager backups as the backup drive I've is smaller in size and there are minimal chances of getting more space. Snap Manager backup is taking full backup of master databases. My biggest master database is msdb and its backup size is 336MB. is there anyway to shrink the msdb safely so that the backups can be accommodated on the backup drive?
MSDB database size is 355 MB and space available is 3.25 MB.
Thanks,
Sam
U can shrink msdb database when no any job is running...so first shrink at ideal time and then take backup..
January 17, 2009 at 3:25 pm
piece of advice, dont screw with MSDB if you must backup the database first.
apart from that, 335 MB isnt too bad but i noticed the job history table is largest. How often do you clear job history and what version sql server you using. there was a hotfix to cure a job history problem IIRC.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 19, 2009 at 8:56 am
The sql verssion is
Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86) (Build 3790: Service Pack 2)
and never cleared the job history, how can I clear the job history?
January 19, 2009 at 4:42 pm
You can do a backup to a shared folder through scripts in Management Studio.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply