I can't start Sql server 2000, in the event viewer is the folowing message.
Database 'model' cannot be opened. It is in the middle of a restore.
Anyone can help me ?
Pedro MetelloYou can possibly use traceflags 3607, 3608 or 3609 to start model without recovery. However, you
need to search the net for articles describing this. Seems like MS removed these from KB... :-(
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"FRISSUL" <frissul@.frissul.pt> wrote in message news:uw6AaiTnDHA.3504@.TK2MSFTNGP11.phx.gbl...
> I can't start Sql server 2000, in the event viewer is the folowing message.
> Database 'model' cannot be opened. It is in the middle of a restore.
>
> Anyone can help me ?
>
> Pedro Metello
>|||thanks, but when I start with
SQLSERVR -T3609
it says:
"BYPASSING RECOVERY FOR DATABASE 'MODEL' BECAUSE IT IS MARKED IN LOAD"
and then
"Database 'model' cannot be opened. It is in the middle of a restore."
Pedro Metello
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:uQ7fBuTnDHA.3504@.TK2MSFTNGP11.phx.gbl...
> You can possibly use traceflags 3607, 3608 or 3609 to start model without
recovery. However, you
> need to search the net for articles describing this. Seems like MS removed
these from KB... :-(
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "FRISSUL" <frissul@.frissul.pt> wrote in message
news:uw6AaiTnDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > I can't start Sql server 2000, in the event viewer is the folowing
message.
> >
> > Database 'model' cannot be opened. It is in the middle of a restore.
> >
> >
> >
> > Anyone can help me ?
> >
> >
> >
> > Pedro Metello
> >
> >
>|||Too bad. There goes that option. I'm out of ideas. I assume you have searched KB and the archives.
So unless someone else comes up with any ideas, it might be worth opening a case with MS support.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"FRISSUL" <frissul@.frissul.pt> wrote in message news:eZHqd5UnDHA.1740@.TK2MSFTNGP12.phx.gbl...
> thanks, but when I start with
> SQLSERVR -T3609
> it says:
> "BYPASSING RECOVERY FOR DATABASE 'MODEL' BECAUSE IT IS MARKED IN LOAD"
> and then
> "Database 'model' cannot be opened. It is in the middle of a restore."
> Pedro Metello
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:uQ7fBuTnDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > You can possibly use traceflags 3607, 3608 or 3609 to start model without
> recovery. However, you
> > need to search the net for articles describing this. Seems like MS removed
> these from KB... :-(
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "FRISSUL" <frissul@.frissul.pt> wrote in message
> news:uw6AaiTnDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > > I can't start Sql server 2000, in the event viewer is the folowing
> message.
> > >
> > > Database 'model' cannot be opened. It is in the middle of a restore.
> > >
> > >
> > >
> > > Anyone can help me ?
> > >
> > >
> > >
> > > Pedro Metello
> > >
> > >
> >
> >
>
Showing posts with label model. Show all posts
Showing posts with label model. Show all posts
Tuesday, March 27, 2012
Can't start SQL Server 2000
We are trying to move msdb, model and master. We added the -c -m and -T3608
to the startup parameters. When we try to restart SQL fails to start. The
error is that it cannot find master database and will not start. We cannot
get back into Enterprise Manager to reset the startup parameters. How can
we reset the parameters and, more importantly, restart SQL Server?
Thanks very much in advance.First step is to look in SQL Servers errorlog file for the reason why SQL Server won't start.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Moishe Much" <MoisheMuch@.discussions.microsoft.com> wrote in message
news:C69EDED1-03A5-4B92-B5D4-DFE80C2B1A19@.microsoft.com...
> We are trying to move msdb, model and master. We added the -c -m and -T3608
> to the startup parameters. When we try to restart SQL fails to start. The
> error is that it cannot find master database and will not start. We cannot
> get back into Enterprise Manager to reset the startup parameters. How can
> we reset the parameters and, more importantly, restart SQL Server?
> Thanks very much in advance.
>|||> We are trying to move msdb, model and master. We added the -c -m
> and -T3608
> to the startup parameters. When we try to restart SQL fails to start. The
> error is that it cannot find master database and will not start. We
> cannot
> get back into Enterprise Manager to reset the startup parameters. How
> can
> we reset the parameters and, more importantly, restart SQL Server?
The parameters for a default SQL 2000 instance are in the registry under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters
If it SQL Server can't find master at startup, you probably didn't specify
the correct master database path parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Moishe Much" <MoisheMuch@.discussions.microsoft.com> wrote in message
news:C69EDED1-03A5-4B92-B5D4-DFE80C2B1A19@.microsoft.com...
> We are trying to move msdb, model and master. We added the -c -m
> and -T3608
> to the startup parameters. When we try to restart SQL fails to start. The
> error is that it cannot find master database and will not start. We
> cannot
> get back into Enterprise Manager to reset the startup parameters. How
> can
> we reset the parameters and, more importantly, restart SQL Server?
> Thanks very much in advance.
>
to the startup parameters. When we try to restart SQL fails to start. The
error is that it cannot find master database and will not start. We cannot
get back into Enterprise Manager to reset the startup parameters. How can
we reset the parameters and, more importantly, restart SQL Server?
Thanks very much in advance.First step is to look in SQL Servers errorlog file for the reason why SQL Server won't start.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Moishe Much" <MoisheMuch@.discussions.microsoft.com> wrote in message
news:C69EDED1-03A5-4B92-B5D4-DFE80C2B1A19@.microsoft.com...
> We are trying to move msdb, model and master. We added the -c -m and -T3608
> to the startup parameters. When we try to restart SQL fails to start. The
> error is that it cannot find master database and will not start. We cannot
> get back into Enterprise Manager to reset the startup parameters. How can
> we reset the parameters and, more importantly, restart SQL Server?
> Thanks very much in advance.
>|||> We are trying to move msdb, model and master. We added the -c -m
> and -T3608
> to the startup parameters. When we try to restart SQL fails to start. The
> error is that it cannot find master database and will not start. We
> cannot
> get back into Enterprise Manager to reset the startup parameters. How
> can
> we reset the parameters and, more importantly, restart SQL Server?
The parameters for a default SQL 2000 instance are in the registry under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters
If it SQL Server can't find master at startup, you probably didn't specify
the correct master database path parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Moishe Much" <MoisheMuch@.discussions.microsoft.com> wrote in message
news:C69EDED1-03A5-4B92-B5D4-DFE80C2B1A19@.microsoft.com...
> We are trying to move msdb, model and master. We added the -c -m
> and -T3608
> to the startup parameters. When we try to restart SQL fails to start. The
> error is that it cannot find master database and will not start. We
> cannot
> get back into Enterprise Manager to reset the startup parameters. How
> can
> we reset the parameters and, more importantly, restart SQL Server?
> Thanks very much in advance.
>
Can't start SQL Server 2000
We are trying to move msdb, model and master. We added the -c -m and -T3608
to the startup parameters. When we try to restart SQL fails to start. The
error is that it cannot find master database and will not start. We cannot
get back into Enterprise Manager to reset the startup parameters. How can
we reset the parameters and, more importantly, restart SQL Server?
Thanks very much in advance.
> We are trying to move msdb, model and master. We added the -c -m
> and -T3608
> to the startup parameters. When we try to restart SQL fails to start. The
> error is that it cannot find master database and will not start. We
> cannot
> get back into Enterprise Manager to reset the startup parameters. How
> can
> we reset the parameters and, more importantly, restart SQL Server?
The parameters for a default SQL 2000 instance are in the registry under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\ MSSQLServer\Parameters
If it SQL Server can't find master at startup, you probably didn't specify
the correct master database path parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Moishe Much" <MoisheMuch@.discussions.microsoft.com> wrote in message
news:C69EDED1-03A5-4B92-B5D4-DFE80C2B1A19@.microsoft.com...
> We are trying to move msdb, model and master. We added the -c -m
> and -T3608
> to the startup parameters. When we try to restart SQL fails to start. The
> error is that it cannot find master database and will not start. We
> cannot
> get back into Enterprise Manager to reset the startup parameters. How
> can
> we reset the parameters and, more importantly, restart SQL Server?
> Thanks very much in advance.
>
sql
to the startup parameters. When we try to restart SQL fails to start. The
error is that it cannot find master database and will not start. We cannot
get back into Enterprise Manager to reset the startup parameters. How can
we reset the parameters and, more importantly, restart SQL Server?
Thanks very much in advance.
> We are trying to move msdb, model and master. We added the -c -m
> and -T3608
> to the startup parameters. When we try to restart SQL fails to start. The
> error is that it cannot find master database and will not start. We
> cannot
> get back into Enterprise Manager to reset the startup parameters. How
> can
> we reset the parameters and, more importantly, restart SQL Server?
The parameters for a default SQL 2000 instance are in the registry under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\ MSSQLServer\Parameters
If it SQL Server can't find master at startup, you probably didn't specify
the correct master database path parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Moishe Much" <MoisheMuch@.discussions.microsoft.com> wrote in message
news:C69EDED1-03A5-4B92-B5D4-DFE80C2B1A19@.microsoft.com...
> We are trying to move msdb, model and master. We added the -c -m
> and -T3608
> to the startup parameters. When we try to restart SQL fails to start. The
> error is that it cannot find master database and will not start. We
> cannot
> get back into Enterprise Manager to reset the startup parameters. How
> can
> we reset the parameters and, more importantly, restart SQL Server?
> Thanks very much in advance.
>
sql
Sunday, March 25, 2012
Can't start engine
SQL Server 2000 cluster
While trying to move the master, model, etc db's I put trace flags into the
startup according to the instruction in:
http://support.microsoft.com/kb/224071
When that didn't work, I tried splitting the 3 args into 3 seperate options.
Now, I can't get in to change it back, the engine refuses to let me in:
Error:
A connection could not be established to _SERVER_
Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
in single user mode. Only one administrator can connect at a time.
I did get into the configuration settings once, though I'm not sure how, and
I thought I removed the options.How about removing "-m" from the startup parameters of your SQL Server
service?
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
"Jay" <nospan@.nospam.org> wrote in message
news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 cluster
> While trying to move the master, model, etc db's I put trace flags into
> the startup according to the instruction in:
> http://support.microsoft.com/kb/224071
> When that didn't work, I tried splitting the 3 args into 3 seperate
> options. Now, I can't get in to change it back, the engine refuses to let
> me in:
> Error:
> A connection could not be established to _SERVER_
> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
> in single user mode. Only one administrator can connect at a time.
>
> I did get into the configuration settings once, though I'm not sure how,
> and I thought I removed the options.
>|||I can't get in!
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:FEC71DAB-8E4D-49B8-97B8-386038797D8A@.microsoft.com...
> How about removing "-m" from the startup parameters of your SQL Server
> service?
> --
> Ekrem Önsoy
> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
> MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into
>> the startup according to the instruction in:
>> http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate
>> options. Now, I can't get in to change it back, the engine refuses to let
>> me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how,
>> and I thought I removed the options.
>|||I think I found the startup parameters in the registry (both nodes of the
cluster) in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\parameters
But I know better than to do more than look in regedit.
"Jay" <nospan@.nospam.org> wrote in message
news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 cluster
> While trying to move the master, model, etc db's I put trace flags into
> the startup according to the instruction in:
> http://support.microsoft.com/kb/224071
> When that didn't work, I tried splitting the 3 args into 3 seperate
> options. Now, I can't get in to change it back, the engine refuses to let
> me in:
> Error:
> A connection could not be established to _SERVER_
> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
> in single user mode. Only one administrator can connect at a time.
>
> I did get into the configuration settings once, though I'm not sure how,
> and I thought I removed the options.
>|||With the Windows admin, I tried to remove the -c -m -T3608 from the
registry. The primary node in the cluster refused to let me remove the
parameter and the secondary allowed the delete. When we restarted the
server, it was still in single user and a refresh in regedit on the
secondary showed the deleted options were back.
"Jay" <nospan@.nospam.org> wrote in message
news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 cluster
> While trying to move the master, model, etc db's I put trace flags into
> the startup according to the instruction in:
> http://support.microsoft.com/kb/224071
> When that didn't work, I tried splitting the 3 args into 3 seperate
> options. Now, I can't get in to change it back, the engine refuses to let
> me in:
> Error:
> A connection could not be established to _SERVER_
> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
> in single user mode. Only one administrator can connect at a time.
>
> I did get into the configuration settings once, though I'm not sure how,
> and I thought I removed the options.
>|||Stop other services which connect to SQL Server. SQL Server Agent for
example...
Then you'd be the one who connects to SQL Server and then you'd be able to
change the setting?
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
"Jay" <nospan@.nospam.org> wrote in message
news:OowRKaaHIHA.4808@.TK2MSFTNGP05.phx.gbl...
>I can't get in!
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:FEC71DAB-8E4D-49B8-97B8-386038797D8A@.microsoft.com...
>> How about removing "-m" from the startup parameters of your SQL Server
>> service?
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into
>> the startup according to the instruction in:
>> http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate
>> options. Now, I can't get in to change it back, the engine refuses to
>> let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server
>> is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how,
>> and I thought I removed the options.
>>
>|||I did stop everything else. listed in the SQL Server Service Manager: SQL
agent, DTC, Full Text search and sonething else I don't recall right now.
Unless it's got something to do with the Cluster manager? I know that
sub-system has some control over SQL Server, but I'm really unfamilar with
it, the Windows admin set it up.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:E4CA655D-3777-4AAA-BB8F-F305A064816B@.microsoft.com...
> Stop other services which connect to SQL Server. SQL Server Agent for
> example...
> Then you'd be the one who connects to SQL Server and then you'd be able to
> change the setting?
> --
> Ekrem Önsoy
> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
> MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:OowRKaaHIHA.4808@.TK2MSFTNGP05.phx.gbl...
>>I can't get in!
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:FEC71DAB-8E4D-49B8-97B8-386038797D8A@.microsoft.com...
>> How about removing "-m" from the startup parameters of your SQL Server
>> service?
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into
>> the startup according to the instruction in:
>> http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate
>> options. Now, I can't get in to change it back, the engine refuses to
>> let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server
>> is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure
>> how, and I thought I removed the options.
>>
>>
>|||Still in need of help.
"Jay" <nospan@.nospam.org> wrote in message
news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 cluster
> While trying to move the master, model, etc db's I put trace flags into
> the startup according to the instruction in:
> http://support.microsoft.com/kb/224071
> When that didn't work, I tried splitting the 3 args into 3 seperate
> options. Now, I can't get in to change it back, the engine refuses to let
> me in:
> Error:
> A connection could not be established to _SERVER_
> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
> in single user mode. Only one administrator can connect at a time.
>
> I did get into the configuration settings once, though I'm not sure how,
> and I thought I removed the options.
>|||Single user mode is the -m startup switch. Perhaps you removed that option in the registry from one
of the nodes but no the other. I would connect using regedit and verify the setting for all nodes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
> Still in need of help.
> "Jay" <nospan@.nospam.org> wrote in message news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into the startup according to
>> the instruction in: http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate options. Now, I can't get in
>> to change it back, the engine refuses to let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how, and I thought I removed
>> the options.
>|||If regedit allows me to change it, am I changing the registry in the correct
localtion? Sounds like it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:3F379085-1621-4BE3-BC32-93A3A921566D@.microsoft.com...
> Single user mode is the -m startup switch. Perhaps you removed that option
> in the registry from one of the nodes but no the other. I would connect
> using regedit and verify the setting for all nodes...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Still in need of help.
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into
>> the startup according to the instruction in:
>> http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate
>> options. Now, I can't get in to change it back, the engine refuses to
>> let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server
>> is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how,
>> and I thought I removed the options.
>>
>|||I'm not sure you want to *change* it using regedit. My gut feeling was to check using regedit to
verify if this was the issue. I would assume that Enterprise Manager would change it for you on all
nodes. Or possibly the cluster support in Windows will take care of that for you (regardless of what
tool you use to make that change). I'd try EM first, and if that doesn't help, use regedit, and
verify all nodes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:OqUUt58HIHA.4712@.TK2MSFTNGP04.phx.gbl...
> If regedit allows me to change it, am I changing the registry in the correct localtion? Sounds
> like it.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:3F379085-1621-4BE3-BC32-93A3A921566D@.microsoft.com...
>> Single user mode is the -m startup switch. Perhaps you removed that option in the registry from
>> one of the nodes but no the other. I would connect using regedit and verify the setting for all
>> nodes...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Still in need of help.
>> "Jay" <nospan@.nospam.org> wrote in message news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into the startup according
>> to the instruction in: http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate options. Now, I can't get
>> in to change it back, the engine refuses to let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how, and I thought I
>> removed the options.
>>
>>
>|||OK Tibor, regedit did allow me to remove just the -m flag. I was then able
to get into properties and remove the other two. The genesis of my issue was
that I could not get into the properties in the first place. I would guess
that this is VERY unusual as both you and Ekrem continue to suggest I change
the options via properties\startup options, despite my continuing to repeat
that I couldn't get in.
I'm OK now, thanks Tibor and Ekrem. I suppose I can be a bit of a pill when
stressed.
The part that is very disturbing is that upon researching the command line
options provided and the experience applying them, there are three mistakes
in the Microsoft kb article.
1) The arguments are presented as if you put them all in, one entry when you
need to do each one separately.
2) -T3608 should be /T3608
3) -m is not necessary in SQL Server 2000 (and is what caused so much of the
problem)
The other two Admins (and our manager, who used to be an Admin) got a good
laugh at me for the faith I had in the Microsoft kb article. It seems that
they have gotten bad instructions many times before.
Last, kicking and screaming, I've now learned a little about the Windows
registry.
Thanks,
Jay
PS. I have now moved the model database and will do the others shortly.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:3F379085-1621-4BE3-BC32-93A3A921566D@.microsoft.com...
> Single user mode is the -m startup switch. Perhaps you removed that option
> in the registry from one of the nodes but no the other. I would connect
> using regedit and verify the setting for all nodes...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Still in need of help.
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into
>> the startup according to the instruction in:
>> http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate
>> options. Now, I can't get in to change it back, the engine refuses to
>> let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server
>> is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how,
>> and I thought I removed the options.
>>
>|||> I would guess that this is VERY unusual as both you and Ekrem continue to suggest I change the
> options via properties\startup options, despite my continuing to repeat that I couldn't get in.
Sorry, I missed that part...
> The part that is very disturbing is that upon researching the command line options provided and
> the experience applying them, there are three mistakes in the Microsoft kb article.
Was it KB 224071?
> 1) The arguments are presented as if you put them all in, one entry when you need to do each one
> separately.
Perhaps the article assumes that you use EM and EM represents the arguments as one on each line,
while you modified the registry directly which represents the information differently? I guess the
KB could have a few words about the difference between EM's representation and how it looks like in
regedit...
> 2) -T3608 should be /T3608
My experience is that dash or slash makes no difference for the various SQL Server exe files that
reads command line options...
A tip: Make sure that you managed to get the proper dbid for model (check sysdatabases). I believe
it need to be 3. As I remember, SQL Server uses the dbid for the model database, not the name of the
database. Which means that you will get whacky behavior when you create new databases if model isn't
3...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:eulAKH9HIHA.4296@.TK2MSFTNGP04.phx.gbl...
> OK Tibor, regedit did allow me to remove just the -m flag. I was then able to get into properties
> and remove the other two. The genesis of my issue was that I could not get into the properties in
> the first place. I would guess that this is VERY unusual as both you and Ekrem continue to suggest
> I change the options via properties\startup options, despite my continuing to repeat that I
> couldn't get in.
> I'm OK now, thanks Tibor and Ekrem. I suppose I can be a bit of a pill when stressed.
> The part that is very disturbing is that upon researching the command line options provided and
> the experience applying them, there are three mistakes in the Microsoft kb article.
> 1) The arguments are presented as if you put them all in, one entry when you need to do each one
> separately.
> 2) -T3608 should be /T3608
> 3) -m is not necessary in SQL Server 2000 (and is what caused so much of the problem)
> The other two Admins (and our manager, who used to be an Admin) got a good laugh at me for the
> faith I had in the Microsoft kb article. It seems that they have gotten bad instructions many
> times before.
> Last, kicking and screaming, I've now learned a little about the Windows registry.
> Thanks,
> Jay
> PS. I have now moved the model database and will do the others shortly.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:3F379085-1621-4BE3-BC32-93A3A921566D@.microsoft.com...
>> Single user mode is the -m startup switch. Perhaps you removed that option in the registry from
>> one of the nodes but no the other. I would connect using regedit and verify the setting for all
>> nodes...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Still in need of help.
>> "Jay" <nospan@.nospam.org> wrote in message news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into the startup according
>> to the instruction in: http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate options. Now, I can't get
>> in to change it back, the engine refuses to let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how, and I thought I
>> removed the options.
>>
>>
>|||> Was it KB 224071?
yes.
...
> A tip: Make sure that you managed to get the proper dbid for model (check
> sysdatabases). I believe it need to be 3. As I remember, SQL Server uses
> the dbid for the model database, not the name of the database. Which means
> that you will get whacky behavior when you create new databases if model
> isn't 3...
Yes, model is dbid 3.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:eulAKH9HIHA.4296@.TK2MSFTNGP04.phx.gbl...
>> OK Tibor, regedit did allow me to remove just the -m flag. I was then
>> able to get into properties and remove the other two. The genesis of my
>> issue was that I could not get into the properties in the first place. I
>> would guess that this is VERY unusual as both you and Ekrem continue to
>> suggest I change the options via properties\startup options, despite my
>> continuing to repeat that I couldn't get in.
>> I'm OK now, thanks Tibor and Ekrem. I suppose I can be a bit of a pill
>> when stressed.
>> The part that is very disturbing is that upon researching the command
>> line options provided and the experience applying them, there are three
>> mistakes in the Microsoft kb article.
>> 1) The arguments are presented as if you put them all in, one entry when
>> you need to do each one separately.
>> 2) -T3608 should be /T3608
>> 3) -m is not necessary in SQL Server 2000 (and is what caused so much of
>> the problem)
>> The other two Admins (and our manager, who used to be an Admin) got a
>> good laugh at me for the faith I had in the Microsoft kb article. It
>> seems that they have gotten bad instructions many times before.
>> Last, kicking and screaming, I've now learned a little about the Windows
>> registry.
>> Thanks,
>> Jay
>> PS. I have now moved the model database and will do the others shortly.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:3F379085-1621-4BE3-BC32-93A3A921566D@.microsoft.com...
>> Single user mode is the -m startup switch. Perhaps you removed that
>> option in the registry from one of the nodes but no the other. I would
>> connect using regedit and verify the setting for all nodes...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Still in need of help.
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags
>> into the startup according to the instruction in:
>> http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate
>> options. Now, I can't get in to change it back, the engine refuses to
>> let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server
>> is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure
>> how, and I thought I removed the options.
>>
>>
>>
>|||Just wanted to say thanks to all who contributed to this thread. I ran into
this exact same issue today and was able to fix it the same way Jay
ultimately did. You've saved me potentially countless hours of trial and
error. Many thanks!
"Jay" wrote:
> > Was it KB 224071?
> yes.
> ...
> >
> > A tip: Make sure that you managed to get the proper dbid for model (check
> > sysdatabases). I believe it need to be 3. As I remember, SQL Server uses
> > the dbid for the model database, not the name of the database. Which means
> > that you will get whacky behavior when you create new databases if model
> > isn't 3...
> Yes, model is dbid 3.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Jay" <nospan@.nospam.org> wrote in message
> > news:eulAKH9HIHA.4296@.TK2MSFTNGP04.phx.gbl...
> >> OK Tibor, regedit did allow me to remove just the -m flag. I was then
> >> able to get into properties and remove the other two. The genesis of my
> >> issue was that I could not get into the properties in the first place. I
> >> would guess that this is VERY unusual as both you and Ekrem continue to
> >> suggest I change the options via properties\startup options, despite my
> >> continuing to repeat that I couldn't get in.
> >>
> >> I'm OK now, thanks Tibor and Ekrem. I suppose I can be a bit of a pill
> >> when stressed.
> >>
> >> The part that is very disturbing is that upon researching the command
> >> line options provided and the experience applying them, there are three
> >> mistakes in the Microsoft kb article.
> >>
> >> 1) The arguments are presented as if you put them all in, one entry when
> >> you need to do each one separately.
> >> 2) -T3608 should be /T3608
> >> 3) -m is not necessary in SQL Server 2000 (and is what caused so much of
> >> the problem)
> >>
> >> The other two Admins (and our manager, who used to be an Admin) got a
> >> good laugh at me for the faith I had in the Microsoft kb article. It
> >> seems that they have gotten bad instructions many times before.
> >>
> >> Last, kicking and screaming, I've now learned a little about the Windows
> >> registry.
> >>
> >> Thanks,
> >> Jay
> >>
> >> PS. I have now moved the model database and will do the others shortly.
> >>
> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> >> in message news:3F379085-1621-4BE3-BC32-93A3A921566D@.microsoft.com...
> >> Single user mode is the -m startup switch. Perhaps you removed that
> >> option in the registry from one of the nodes but no the other. I would
> >> connect using regedit and verify the setting for all nodes...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Jay" <nospan@.nospam.org> wrote in message
> >> news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
> >> Still in need of help.
> >>
> >> "Jay" <nospan@.nospam.org> wrote in message
> >> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
> >> SQL Server 2000 cluster
> >>
> >> While trying to move the master, model, etc db's I put trace flags
> >> into the startup according to the instruction in:
> >> http://support.microsoft.com/kb/224071
> >>
> >> When that didn't work, I tried splitting the 3 args into 3 seperate
> >> options. Now, I can't get in to change it back, the engine refuses to
> >> let me in:
> >>
> >> Error:
> >>
> >> A connection could not be established to _SERVER_
> >> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server
> >> is
> >> in single user mode. Only one administrator can connect at a time.
> >>
> >>
> >> I did get into the configuration settings once, though I'm not sure
> >> how, and I thought I removed the options.
> >>
> >>
> >>
> >>
> >>
> >>
> >
>
>
While trying to move the master, model, etc db's I put trace flags into the
startup according to the instruction in:
http://support.microsoft.com/kb/224071
When that didn't work, I tried splitting the 3 args into 3 seperate options.
Now, I can't get in to change it back, the engine refuses to let me in:
Error:
A connection could not be established to _SERVER_
Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
in single user mode. Only one administrator can connect at a time.
I did get into the configuration settings once, though I'm not sure how, and
I thought I removed the options.How about removing "-m" from the startup parameters of your SQL Server
service?
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
"Jay" <nospan@.nospam.org> wrote in message
news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 cluster
> While trying to move the master, model, etc db's I put trace flags into
> the startup according to the instruction in:
> http://support.microsoft.com/kb/224071
> When that didn't work, I tried splitting the 3 args into 3 seperate
> options. Now, I can't get in to change it back, the engine refuses to let
> me in:
> Error:
> A connection could not be established to _SERVER_
> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
> in single user mode. Only one administrator can connect at a time.
>
> I did get into the configuration settings once, though I'm not sure how,
> and I thought I removed the options.
>|||I can't get in!
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:FEC71DAB-8E4D-49B8-97B8-386038797D8A@.microsoft.com...
> How about removing "-m" from the startup parameters of your SQL Server
> service?
> --
> Ekrem Önsoy
> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
> MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into
>> the startup according to the instruction in:
>> http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate
>> options. Now, I can't get in to change it back, the engine refuses to let
>> me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how,
>> and I thought I removed the options.
>|||I think I found the startup parameters in the registry (both nodes of the
cluster) in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\parameters
But I know better than to do more than look in regedit.
"Jay" <nospan@.nospam.org> wrote in message
news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 cluster
> While trying to move the master, model, etc db's I put trace flags into
> the startup according to the instruction in:
> http://support.microsoft.com/kb/224071
> When that didn't work, I tried splitting the 3 args into 3 seperate
> options. Now, I can't get in to change it back, the engine refuses to let
> me in:
> Error:
> A connection could not be established to _SERVER_
> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
> in single user mode. Only one administrator can connect at a time.
>
> I did get into the configuration settings once, though I'm not sure how,
> and I thought I removed the options.
>|||With the Windows admin, I tried to remove the -c -m -T3608 from the
registry. The primary node in the cluster refused to let me remove the
parameter and the secondary allowed the delete. When we restarted the
server, it was still in single user and a refresh in regedit on the
secondary showed the deleted options were back.
"Jay" <nospan@.nospam.org> wrote in message
news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 cluster
> While trying to move the master, model, etc db's I put trace flags into
> the startup according to the instruction in:
> http://support.microsoft.com/kb/224071
> When that didn't work, I tried splitting the 3 args into 3 seperate
> options. Now, I can't get in to change it back, the engine refuses to let
> me in:
> Error:
> A connection could not be established to _SERVER_
> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
> in single user mode. Only one administrator can connect at a time.
>
> I did get into the configuration settings once, though I'm not sure how,
> and I thought I removed the options.
>|||Stop other services which connect to SQL Server. SQL Server Agent for
example...
Then you'd be the one who connects to SQL Server and then you'd be able to
change the setting?
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
"Jay" <nospan@.nospam.org> wrote in message
news:OowRKaaHIHA.4808@.TK2MSFTNGP05.phx.gbl...
>I can't get in!
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:FEC71DAB-8E4D-49B8-97B8-386038797D8A@.microsoft.com...
>> How about removing "-m" from the startup parameters of your SQL Server
>> service?
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into
>> the startup according to the instruction in:
>> http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate
>> options. Now, I can't get in to change it back, the engine refuses to
>> let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server
>> is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how,
>> and I thought I removed the options.
>>
>|||I did stop everything else. listed in the SQL Server Service Manager: SQL
agent, DTC, Full Text search and sonething else I don't recall right now.
Unless it's got something to do with the Cluster manager? I know that
sub-system has some control over SQL Server, but I'm really unfamilar with
it, the Windows admin set it up.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:E4CA655D-3777-4AAA-BB8F-F305A064816B@.microsoft.com...
> Stop other services which connect to SQL Server. SQL Server Agent for
> example...
> Then you'd be the one who connects to SQL Server and then you'd be able to
> change the setting?
> --
> Ekrem Önsoy
> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
> MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:OowRKaaHIHA.4808@.TK2MSFTNGP05.phx.gbl...
>>I can't get in!
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:FEC71DAB-8E4D-49B8-97B8-386038797D8A@.microsoft.com...
>> How about removing "-m" from the startup parameters of your SQL Server
>> service?
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into
>> the startup according to the instruction in:
>> http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate
>> options. Now, I can't get in to change it back, the engine refuses to
>> let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server
>> is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure
>> how, and I thought I removed the options.
>>
>>
>|||Still in need of help.
"Jay" <nospan@.nospam.org> wrote in message
news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 cluster
> While trying to move the master, model, etc db's I put trace flags into
> the startup according to the instruction in:
> http://support.microsoft.com/kb/224071
> When that didn't work, I tried splitting the 3 args into 3 seperate
> options. Now, I can't get in to change it back, the engine refuses to let
> me in:
> Error:
> A connection could not be established to _SERVER_
> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
> in single user mode. Only one administrator can connect at a time.
>
> I did get into the configuration settings once, though I'm not sure how,
> and I thought I removed the options.
>|||Single user mode is the -m startup switch. Perhaps you removed that option in the registry from one
of the nodes but no the other. I would connect using regedit and verify the setting for all nodes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
> Still in need of help.
> "Jay" <nospan@.nospam.org> wrote in message news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into the startup according to
>> the instruction in: http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate options. Now, I can't get in
>> to change it back, the engine refuses to let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how, and I thought I removed
>> the options.
>|||If regedit allows me to change it, am I changing the registry in the correct
localtion? Sounds like it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:3F379085-1621-4BE3-BC32-93A3A921566D@.microsoft.com...
> Single user mode is the -m startup switch. Perhaps you removed that option
> in the registry from one of the nodes but no the other. I would connect
> using regedit and verify the setting for all nodes...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Still in need of help.
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into
>> the startup according to the instruction in:
>> http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate
>> options. Now, I can't get in to change it back, the engine refuses to
>> let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server
>> is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how,
>> and I thought I removed the options.
>>
>|||I'm not sure you want to *change* it using regedit. My gut feeling was to check using regedit to
verify if this was the issue. I would assume that Enterprise Manager would change it for you on all
nodes. Or possibly the cluster support in Windows will take care of that for you (regardless of what
tool you use to make that change). I'd try EM first, and if that doesn't help, use regedit, and
verify all nodes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:OqUUt58HIHA.4712@.TK2MSFTNGP04.phx.gbl...
> If regedit allows me to change it, am I changing the registry in the correct localtion? Sounds
> like it.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:3F379085-1621-4BE3-BC32-93A3A921566D@.microsoft.com...
>> Single user mode is the -m startup switch. Perhaps you removed that option in the registry from
>> one of the nodes but no the other. I would connect using regedit and verify the setting for all
>> nodes...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Still in need of help.
>> "Jay" <nospan@.nospam.org> wrote in message news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into the startup according
>> to the instruction in: http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate options. Now, I can't get
>> in to change it back, the engine refuses to let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how, and I thought I
>> removed the options.
>>
>>
>|||OK Tibor, regedit did allow me to remove just the -m flag. I was then able
to get into properties and remove the other two. The genesis of my issue was
that I could not get into the properties in the first place. I would guess
that this is VERY unusual as both you and Ekrem continue to suggest I change
the options via properties\startup options, despite my continuing to repeat
that I couldn't get in.
I'm OK now, thanks Tibor and Ekrem. I suppose I can be a bit of a pill when
stressed.
The part that is very disturbing is that upon researching the command line
options provided and the experience applying them, there are three mistakes
in the Microsoft kb article.
1) The arguments are presented as if you put them all in, one entry when you
need to do each one separately.
2) -T3608 should be /T3608
3) -m is not necessary in SQL Server 2000 (and is what caused so much of the
problem)
The other two Admins (and our manager, who used to be an Admin) got a good
laugh at me for the faith I had in the Microsoft kb article. It seems that
they have gotten bad instructions many times before.
Last, kicking and screaming, I've now learned a little about the Windows
registry.
Thanks,
Jay
PS. I have now moved the model database and will do the others shortly.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:3F379085-1621-4BE3-BC32-93A3A921566D@.microsoft.com...
> Single user mode is the -m startup switch. Perhaps you removed that option
> in the registry from one of the nodes but no the other. I would connect
> using regedit and verify the setting for all nodes...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Still in need of help.
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into
>> the startup according to the instruction in:
>> http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate
>> options. Now, I can't get in to change it back, the engine refuses to
>> let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server
>> is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how,
>> and I thought I removed the options.
>>
>|||> I would guess that this is VERY unusual as both you and Ekrem continue to suggest I change the
> options via properties\startup options, despite my continuing to repeat that I couldn't get in.
Sorry, I missed that part...
> The part that is very disturbing is that upon researching the command line options provided and
> the experience applying them, there are three mistakes in the Microsoft kb article.
Was it KB 224071?
> 1) The arguments are presented as if you put them all in, one entry when you need to do each one
> separately.
Perhaps the article assumes that you use EM and EM represents the arguments as one on each line,
while you modified the registry directly which represents the information differently? I guess the
KB could have a few words about the difference between EM's representation and how it looks like in
regedit...
> 2) -T3608 should be /T3608
My experience is that dash or slash makes no difference for the various SQL Server exe files that
reads command line options...
A tip: Make sure that you managed to get the proper dbid for model (check sysdatabases). I believe
it need to be 3. As I remember, SQL Server uses the dbid for the model database, not the name of the
database. Which means that you will get whacky behavior when you create new databases if model isn't
3...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:eulAKH9HIHA.4296@.TK2MSFTNGP04.phx.gbl...
> OK Tibor, regedit did allow me to remove just the -m flag. I was then able to get into properties
> and remove the other two. The genesis of my issue was that I could not get into the properties in
> the first place. I would guess that this is VERY unusual as both you and Ekrem continue to suggest
> I change the options via properties\startup options, despite my continuing to repeat that I
> couldn't get in.
> I'm OK now, thanks Tibor and Ekrem. I suppose I can be a bit of a pill when stressed.
> The part that is very disturbing is that upon researching the command line options provided and
> the experience applying them, there are three mistakes in the Microsoft kb article.
> 1) The arguments are presented as if you put them all in, one entry when you need to do each one
> separately.
> 2) -T3608 should be /T3608
> 3) -m is not necessary in SQL Server 2000 (and is what caused so much of the problem)
> The other two Admins (and our manager, who used to be an Admin) got a good laugh at me for the
> faith I had in the Microsoft kb article. It seems that they have gotten bad instructions many
> times before.
> Last, kicking and screaming, I've now learned a little about the Windows registry.
> Thanks,
> Jay
> PS. I have now moved the model database and will do the others shortly.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:3F379085-1621-4BE3-BC32-93A3A921566D@.microsoft.com...
>> Single user mode is the -m startup switch. Perhaps you removed that option in the registry from
>> one of the nodes but no the other. I would connect using regedit and verify the setting for all
>> nodes...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Still in need of help.
>> "Jay" <nospan@.nospam.org> wrote in message news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags into the startup according
>> to the instruction in: http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate options. Now, I can't get
>> in to change it back, the engine refuses to let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure how, and I thought I
>> removed the options.
>>
>>
>|||> Was it KB 224071?
yes.
...
> A tip: Make sure that you managed to get the proper dbid for model (check
> sysdatabases). I believe it need to be 3. As I remember, SQL Server uses
> the dbid for the model database, not the name of the database. Which means
> that you will get whacky behavior when you create new databases if model
> isn't 3...
Yes, model is dbid 3.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:eulAKH9HIHA.4296@.TK2MSFTNGP04.phx.gbl...
>> OK Tibor, regedit did allow me to remove just the -m flag. I was then
>> able to get into properties and remove the other two. The genesis of my
>> issue was that I could not get into the properties in the first place. I
>> would guess that this is VERY unusual as both you and Ekrem continue to
>> suggest I change the options via properties\startup options, despite my
>> continuing to repeat that I couldn't get in.
>> I'm OK now, thanks Tibor and Ekrem. I suppose I can be a bit of a pill
>> when stressed.
>> The part that is very disturbing is that upon researching the command
>> line options provided and the experience applying them, there are three
>> mistakes in the Microsoft kb article.
>> 1) The arguments are presented as if you put them all in, one entry when
>> you need to do each one separately.
>> 2) -T3608 should be /T3608
>> 3) -m is not necessary in SQL Server 2000 (and is what caused so much of
>> the problem)
>> The other two Admins (and our manager, who used to be an Admin) got a
>> good laugh at me for the faith I had in the Microsoft kb article. It
>> seems that they have gotten bad instructions many times before.
>> Last, kicking and screaming, I've now learned a little about the Windows
>> registry.
>> Thanks,
>> Jay
>> PS. I have now moved the model database and will do the others shortly.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:3F379085-1621-4BE3-BC32-93A3A921566D@.microsoft.com...
>> Single user mode is the -m startup switch. Perhaps you removed that
>> option in the registry from one of the nodes but no the other. I would
>> connect using regedit and verify the setting for all nodes...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Still in need of help.
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 cluster
>> While trying to move the master, model, etc db's I put trace flags
>> into the startup according to the instruction in:
>> http://support.microsoft.com/kb/224071
>> When that didn't work, I tried splitting the 3 args into 3 seperate
>> options. Now, I can't get in to change it back, the engine refuses to
>> let me in:
>> Error:
>> A connection could not be established to _SERVER_
>> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server
>> is
>> in single user mode. Only one administrator can connect at a time.
>>
>> I did get into the configuration settings once, though I'm not sure
>> how, and I thought I removed the options.
>>
>>
>>
>|||Just wanted to say thanks to all who contributed to this thread. I ran into
this exact same issue today and was able to fix it the same way Jay
ultimately did. You've saved me potentially countless hours of trial and
error. Many thanks!
"Jay" wrote:
> > Was it KB 224071?
> yes.
> ...
> >
> > A tip: Make sure that you managed to get the proper dbid for model (check
> > sysdatabases). I believe it need to be 3. As I remember, SQL Server uses
> > the dbid for the model database, not the name of the database. Which means
> > that you will get whacky behavior when you create new databases if model
> > isn't 3...
> Yes, model is dbid 3.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Jay" <nospan@.nospam.org> wrote in message
> > news:eulAKH9HIHA.4296@.TK2MSFTNGP04.phx.gbl...
> >> OK Tibor, regedit did allow me to remove just the -m flag. I was then
> >> able to get into properties and remove the other two. The genesis of my
> >> issue was that I could not get into the properties in the first place. I
> >> would guess that this is VERY unusual as both you and Ekrem continue to
> >> suggest I change the options via properties\startup options, despite my
> >> continuing to repeat that I couldn't get in.
> >>
> >> I'm OK now, thanks Tibor and Ekrem. I suppose I can be a bit of a pill
> >> when stressed.
> >>
> >> The part that is very disturbing is that upon researching the command
> >> line options provided and the experience applying them, there are three
> >> mistakes in the Microsoft kb article.
> >>
> >> 1) The arguments are presented as if you put them all in, one entry when
> >> you need to do each one separately.
> >> 2) -T3608 should be /T3608
> >> 3) -m is not necessary in SQL Server 2000 (and is what caused so much of
> >> the problem)
> >>
> >> The other two Admins (and our manager, who used to be an Admin) got a
> >> good laugh at me for the faith I had in the Microsoft kb article. It
> >> seems that they have gotten bad instructions many times before.
> >>
> >> Last, kicking and screaming, I've now learned a little about the Windows
> >> registry.
> >>
> >> Thanks,
> >> Jay
> >>
> >> PS. I have now moved the model database and will do the others shortly.
> >>
> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> >> in message news:3F379085-1621-4BE3-BC32-93A3A921566D@.microsoft.com...
> >> Single user mode is the -m startup switch. Perhaps you removed that
> >> option in the registry from one of the nodes but no the other. I would
> >> connect using regedit and verify the setting for all nodes...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Jay" <nospan@.nospam.org> wrote in message
> >> news:%23ZIDnu8HIHA.3956@.TK2MSFTNGP04.phx.gbl...
> >> Still in need of help.
> >>
> >> "Jay" <nospan@.nospam.org> wrote in message
> >> news:uvHnERaHIHA.4956@.TK2MSFTNGP06.phx.gbl...
> >> SQL Server 2000 cluster
> >>
> >> While trying to move the master, model, etc db's I put trace flags
> >> into the startup according to the instruction in:
> >> http://support.microsoft.com/kb/224071
> >>
> >> When that didn't work, I tried splitting the 3 args into 3 seperate
> >> options. Now, I can't get in to change it back, the engine refuses to
> >> let me in:
> >>
> >> Error:
> >>
> >> A connection could not be established to _SERVER_
> >> Reason: Login failed for user 'DOMAIN\USERNAME'. Reason: Server
> >> is
> >> in single user mode. Only one administrator can connect at a time.
> >>
> >>
> >> I did get into the configuration settings once, though I'm not sure
> >> how, and I thought I removed the options.
> >>
> >>
> >>
> >>
> >>
> >>
> >
>
>
Thursday, March 8, 2012
Can't remove inactive entries from transaction log
My database is set to Full Recovery model. I can shrink my transaction log
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -
Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>
|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>
|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential[vbcol=seagreen]
> backup however.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
log[vbcol=seagreen]
> execution
> event
> So
operation?[vbcol=seagreen]
don't
>
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -
Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>
|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>
|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential[vbcol=seagreen]
> backup however.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
log[vbcol=seagreen]
> execution
> event
> So
operation?[vbcol=seagreen]
don't
>
Can't remove inactive entries from transaction log
My database is set to Full Recovery model. I can shrink my transaction log
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential
> backup however.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > My database is set to Full Recovery model. I can shrink my transaction
log
> > by executing a logfile backup, but when I execute a Full or Incremental
> > backup, this option is disabled. I don't understand why. With the
> execution
> > of a successful backup, entries in the log prior to the point of backup
> > become irrelevant! I might need subsequent logfile transactions in the
> event
> > of a failure, but I won't need the logfile prior to the point of backup.
> So
> > why can't I shrink the logfile as part and parcel of the backup
operation?
> > Must I always execute a separate logfile backup prior to my Full /
> > Incremental backup for the sole purpose of shrinking the logfile? I
don't
> > get it.
> >
> > Thanks for your advice!
> >
> > - Joe Geretz -
> >
> >
>
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential
> backup however.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > My database is set to Full Recovery model. I can shrink my transaction
log
> > by executing a logfile backup, but when I execute a Full or Incremental
> > backup, this option is disabled. I don't understand why. With the
> execution
> > of a successful backup, entries in the log prior to the point of backup
> > become irrelevant! I might need subsequent logfile transactions in the
> event
> > of a failure, but I won't need the logfile prior to the point of backup.
> So
> > why can't I shrink the logfile as part and parcel of the backup
operation?
> > Must I always execute a separate logfile backup prior to my Full /
> > Incremental backup for the sole purpose of shrinking the logfile? I
don't
> > get it.
> >
> > Thanks for your advice!
> >
> > - Joe Geretz -
> >
> >
>
Can't remove inactive entries from transaction log
My database is set to Full Recovery model. I can shrink my transaction log
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential
> backup however.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
log[vbcol=seagreen]
> execution
> event
> So
operation?[vbcol=seagreen]
don't[vbcol=seagreen]
>
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential
> backup however.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
log[vbcol=seagreen]
> execution
> event
> So
operation?[vbcol=seagreen]
don't[vbcol=seagreen]
>
Friday, February 10, 2012
Can't Get Security Filters to Work
I've added in a Security Filter to my Report Model as documented by Bob
here:
http://blogs.msdn.com/bobmeyers/pages/Implementing-Data-Security-in-a-Report-Model.aspx
But I'm still seeing all data in report runs - what am I doing wrong?
Any other switches or settings I need? Do I need to turn on 'individual
model item' security in Management Studio? I did try that but didn't see
any difference.
ThanksI figured out my problem: I forgot to add the filter to the Security Filters
collection.
Works now.
Thanks.
here:
http://blogs.msdn.com/bobmeyers/pages/Implementing-Data-Security-in-a-Report-Model.aspx
But I'm still seeing all data in report runs - what am I doing wrong?
Any other switches or settings I need? Do I need to turn on 'individual
model item' security in Management Studio? I did try that but didn't see
any difference.
ThanksI figured out my problem: I forgot to add the filter to the Security Filters
collection.
Works now.
Thanks.
Subscribe to:
Posts (Atom)