September 8, 2004 at 10:48 am
Hi everyone,
Can someone help me troubleshoot the following problem.
Error: 1105, Severity: 17, State: 2
Could not allocate space for object 'backupfile' in database 'msdb' because the 'PRIMARY' filegroup is full.
I have been using this forum for quite sometime and it helped me a lot in preparing my logshipping server.
It all started right after the power outage.
Thanks
September 8, 2004 at 11:26 am
Your MSDB database file is full. Right click on MSDB, select "Properties", click on "Data Files", and increase the size given in "Space Allocated (MB)" .
Steve
September 8, 2004 at 11:46 am
Thanx steve for your response to my question. I appreciate that...however now the problem is my msdb database is showing suspected...and I cannot select the properties...is there a way I can recover my msdb database....
Thanx again...
Muneeb.
September 8, 2004 at 11:58 am
Not good.
Is your disk out of space?
If so, do you have another disk that you can use or can you free up space on that disk?
According to books online, this occurs when the disk is full. Your best bet is to free up space on the current disk, then follow the directions in books online. Note: if you free up space on the existing disk, you won't need to create a new file, you can just expand the current one, like I showed you before.
Bring up Books Online, select index, and type in "resetting suspect status" for the directions.
Steve
September 9, 2004 at 4:55 am
Hi,
as soon as you get the database to online, you couls use the undocumented procedures
sp_delete_backuphistory
or
sp_delete_backup_and_restore_history
to truncate your backup history...
regards
karl
Best regards
karl
September 9, 2004 at 3:35 pm
Thanks for all the replies to my question. My SQL Server is finally up and running, and so does my log shipping...I am a happy person once again... Well it's been like more than 8 months that I used this forum to create my log shipping server and for the past 8 months my log shipping had been synchronized with the production server we have. I could have never done this without everyones help in this forum. But the recent experience showed me that it's always better to take the backups of the 2 most important databases which are master and msdb. Its just because my msdb went into suspected mode that my 8 months work would have been gone...but thanks to all of you that I'm safe now. Speaking about the msdb, I would like to share what I did to restore my msdb.
Like, I did got the advice in this forum to reset the suspected mode of my msdb database. I reset it and then I restored the msdb from the previous backup. This helped me to atleast restart my SQL Server which I wasn't able to. However, I need my most recent msdb to be running with this SQL Server so what I did I stopped the SQL Server and overwrite the most recent .mdf and ldf file on the restored backup of msdb database. Then I restarted the SQL Server and it worked...Later I went to the msdb properties and changed the space allocated from 2MB to 64MB so that I won't get any problems in the future...Later I restored all my database from the recent backups and enabled the log shipping routines.
I would appreciate comments on what I did.
Thanks
September 9, 2004 at 4:43 pm
Karl made a very good point. One of the major things that will cause your msdb database to grow is backup history, especially in a log shipping scenario. I have implemented the following code into my backup job (full, not log), to delete backup history older than 3 months -
Declare
@his_cut_date char(10),
@command varchar(128),
-- delete backup history older than 3 months.
-- set @his_cut_date = 3 months ago
select @his_cut_date = convert(char(10), dateadd(month, -3, getdate()), 101)
-- set @his_cut_date to 1st day of the month
select @his_cut_date = substring(@his_cut_date, 1, 3) + '01' + substring(@his_cut_date, 6, 5)
select @command = 'USE msdb EXEC sp_delete_backuphistory ' + char(39) + @his_cut_date + char(39)
print @command
exec (@command)
By the way, I put this in the full backup because it executes once per day rather than the every 15 minutes of the log backups. You could even put it into a seperate job to execute it less often.
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply