December 6, 2001 at 3:40 pm
SQL 2000: I set msdb to "Full backup mode. Prior to the nightly backup job we stop SQL server (to grab the mdf/ldf files) and then restart SQL. This seems to reset msdb to "Simple" mode. Then our msdb trans dumps fail. And thoughts on how to make the setting "stick"?
December 6, 2001 at 6:58 pm
It does change each time. I just reset it.
Andy
December 6, 2001 at 8:14 pm
December 6, 2001 at 9:55 pm
Steve,
You say that one can write a stored proc to "do it and make it a startup proc". How do you make a "startup proc"?
December 6, 2001 at 9:58 pm
Andy,
You said that "It does change each time. I just reset it." Do you reset it in the morning and put up with a night of failed trans dumps notices? Or what?
Bill
December 7, 2001 at 10:56 am
You can designate any stored procedure to run at server startup. In sql 2000, you set the server to run these with:
sp_configure 'scan for startup procs', 1
Set the procedures with
sp_procoption
Sets procedure options.
Syntax
sp_procoption [ @ProcName = ] 'procedure'
, [ @OptionName = ] 'option'
, [ @OptionValue = ] 'value'
Arguments
[@ProcName =] 'procedure'
Is the name of the procedure for which to set or view an option. procedure is nvarchar(776), with no default.
[@OptionName =] 'option'
Is the name of the option to set. The only value for option is startup, which sets stored procedure for autoexecution. A stored procedure that is set to autoexection runs every time Microsoft® SQL Server™ is started.
[@OptionValue =] 'value'
Is whether to set the option on (true or on) or off (false or off). value is varchar(12), with no default.
Return Code Values
0 (success) or error number (failure)
Steve Jones
December 7, 2001 at 11:46 am
I actually fix it as part of my backup:
For Each mDatabase In mSQLServer.Databases
if instr(strExcludeList, ucase(mDatabase.Name)) = 0 then
Set mBackup = CreateObject("SQLDMO.Backup")
mBackup.Database = mDatabase.Name
strDB = ""
if ucase(mDatabase.Name)="MASTER" then
strDB=sBakPath & "\FULL_" & UCase(mDatabase.Name) & "_" & oSQLSupport.FileName & ".bak"
mBackup.Files = strDB
mBackup.Action = 0
else
if mDatabase.dboption.truncatelogoncheckpoint <> 0 then
mDatabase.dboption.truncatelogoncheckpoint = 0
strMessage = strMessage & mdatabase.name & chr(13) & chr(10)
end if
strDB=sBakPath & "\LOG_" & UCase(mDatabase.Name) & "_" & oSQLSupport.FileName & ".bak"
mBackup.Files = strDB
mBackup.Action = 3
end if
if strDB <> "" then
mBackup.SQLBackup mSQLServer
end if
Set mBackup = Nothing
End If
Next
Andy
December 8, 2001 at 8:00 pm
Is this a good way of taking backups?
Or is method being given priority over objective?
Cursors never.
DTS - only when needed and never to control.
December 9, 2001 at 4:47 am
Seems to be a pretty good method to me. Works every time. Could you do it using TSQL - sure. To me the methods are equal, it's like arguing that C# is better than VB.net.
Andy
December 10, 2001 at 3:10 pm
Andy,
I see your VB script but how do you run it from SQL? Do you schedule it at, say 10:30pm, assuming you stopped and started SQL server at 10pm?
Bill
P.S. I got a response from Microsoft saying that the flipping of Msdb from Full to Simple mode is by design. They said that many people didn't realize how large the Msdb was getting (in Full mode) and would fill up their hard disks.
December 10, 2001 at 3:18 pm
It just runs on whatever schedule the back up is - I havent gone the extra mile to reset it immediately upon a service restart. Probably should have, but have not so far! I think Steve mentioned earlier about a start up proc. Another option would be to set a job to run every 5 mins, then disable it once it runs once.
Andy
July 10, 2002 at 1:17 pm
The posted solutions sound good, but is the log still usable after the database recovery option is switched to SIMPLE upon reboot, then FULL via the startup proc? It seems to me that once the setting goes to SIMPLE, no forward recovery is possible.
Or are you suggesting that the startup proc also include the creation of a new, current full backup of msdb? Wouldn't that be the only way to ensure it is recoverable from that point forward?
Any feedback would be appreciated.
Thanks, Barry
Barry Spiegel
EDS New York Solution Centre
25 Northpointe Pkwy
Amherst, NY 14228
716-564-6614
Barry Spiegel
EDS New York Solution Centre
25 Northpointe Pkwy
Amherst, NY 14228
716-564-6614
barry.spiegel@eds.com
July 15, 2002 at 6:06 am
Also, the consensus here in our group of DBAs is that Microsoft reverts msdb to 'simple' because it keeps the log from growing, which would require "real" DBA support -- indicating that MS is targeting SQL Server, despite its robustness, towards the lower end of the DB market.
Thoughts? And I'd still appreciate feedback on whether msdb is recoverable if recovery option is 'Full', shutdown/startup makes it 'Simple', and a startup proc returns it to 'Full' again.
Thanks, Barry
Barry Spiegel
EDS New York Solution Centre
25 Northpointe Pkwy
Amherst, NY 14228
716-564-6614
Barry Spiegel
EDS New York Solution Centre
25 Northpointe Pkwy
Amherst, NY 14228
716-564-6614
barry.spiegel@eds.com
July 15, 2002 at 6:16 am
The same rules apply for changing recovery mode on msdb as on any other db. My guess on the msdb is that there is rarely a need to do a point in time restore there. Most transactions actually occur in a "real" db, so how often would you restore to fix a change you made to a job in error. DTS isn't even much of an issue since SQL stores versions for you, so you can just roll back if you err. Based on all that, I imagine they did go with simple because it is one less thing to break for installations with no DBA.
Irksome behavior though, should at least be configurable.
Andy
July 19, 2002 at 9:16 am
I really see no reason to have MSDB in "FULL" mode. It is not a transaction heavy database. And what sort of transactions are even there? It records when jobs execute and whether they were successful or not. It stores schedules. It stores alerts. It stores data about your operators. It stores DTS package data.
This is not data that has changes made to it every hour. A complete nightly backup is fine for MSDB. Transaction log backups are overkill.
Make your DBA life less complicated. Leave MSDB in simple mode.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply