Hello:
I've implemented a stand-by server solution, where the
tran log backup from the primary server gets restored to
the secondary server at every 15-min interval.
I understand that there are some limitations with this
approach (could not implement MS SLS as our business unit
could not afford to purchase the Ent. Ed.), and was
wondering if anyone has encountered any other issues or
observations when implementing a similar manual log
shipping process, other than my own observations listed
below:
Log Shipping will fail if...
- ...there are any open connections to the database where
the transaction log files are restored to; though querying
tables using the fully qualified name is possible from
another database connection or via a linked server
connection.
I've also had one incident where my log shipping process
failed due to a LSN out of sync issue. This happened when
I ran a BCP IN operation. Other times, both BCP and BULK
INSERT operations ran successfully, funnelling changes to
the secondary server's database as expected.
Thank you for all your responses.
Regards,
- Rob.Your observations are correct. Log shipping will fail if there are =
users connected to the database. I am wondering if perhaps someone =
changed the dboptions when you had the log shipping fail after a BCP =
import. =20
And no, I have not experienced any other issues. The custom log =
shipping approach works very well.
You can find a script that will kill any connections to the specified =
database here:
http://sqlguy.home.comcast.net/logship.htm
--=20
Keith
"Rob" <anonymous@.discussions.microsoft.com> wrote in message =
news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
> Hello:
>=20
> I've implemented a stand-by server solution, where the=20
> tran log backup from the primary server gets restored to=20
> the secondary server at every 15-min interval.
>=20
> I understand that there are some limitations with this=20
> approach (could not implement MS SLS as our business unit=20
> could not afford to purchase the Ent. Ed.), and was=20
> wondering if anyone has encountered any other issues or=20
> observations when implementing a similar manual log=20
> shipping process, other than my own observations listed=20
> below:
>=20
> Log Shipping will fail if...
>=20
> - ...there are any open connections to the database where=20
> the transaction log files are restored to; though querying=20
> tables using the fully qualified name is possible from=20
> another database connection or via a linked server=20
> connection.
>=20
> I've also had one incident where my log shipping process=20
> failed due to a LSN out of sync issue. This happened when=20
> I ran a BCP IN operation. Other times, both BCP and BULK=20
> INSERT operations ran successfully, funnelling changes to=20
> the secondary server's database as expected.
>=20
> Thank you for all your responses.
>=20
> Regards,
>=20
> - Rob.|||Rather than doing a KILL command on each SPID, a cleaner
way to do it is to put the database in single user mode
with rollback immediate for the duration of the log
restore and then put it back in multi user mode. This
works very well. Here's an example:
alter database database_name set SINGLE_USER with rollback
immediate
restore log database_name from disk
= 'c:\database_name_log.bak' with standby
= 'c:\standby\database_name.bak'
alter database database_name set MULTI_USER
>--Original Message--
>Your observations are correct. Log shipping will fail if
there are users connected to the database. I am wondering
if perhaps someone changed the dboptions when you had the
log shipping fail after a BCP import.
>And no, I have not experienced any other issues. The
custom log shipping approach works very well.
>You can find a script that will kill any connections to
the specified database here:
>http://sqlguy.home.comcast.net/logship.htm
>--
>Keith
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
to
unit
where
querying
process
when
BULK
to
>.
>|||Agreed. I need to update the web page.
--=20
Keith
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message =
news:13bbf01c3f7d4$804fb2a0$a001280a@.phx
.gbl...
> Rather than doing a KILL command on each SPID, a cleaner=20
> way to do it is to put the database in single user mode=20
> with rollback immediate for the duration of the log=20
> restore and then put it back in multi user mode. This=20
> works very well. Here's an example:
>=20
> alter database database_name set SINGLE_USER with rollback=20
> immediate
>=20
> restore log database_name from disk=20
> =3D 'c:\database_name_log.bak' with standby=20
> =3D 'c:\standby\database_name.bak'
>=20
> alter database database_name set MULTI_USER
>=20
> there are users connected to the database. I am wondering=20
> if perhaps someone changed the dboptions when you had the=20
> log shipping fail after a BCP import. =20
> custom log shipping approach works very well.
> the specified database here:
> message news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
> to=20
> unit=20
> where=20
> querying=20
> process=20
> when=20
> BULK=20
> to=20|||But even in single user mode, there could be multiple
connections to the database, which can cause manual log
shipping failures. In this case, I find killing all user
connections more effective, to ensure no connections
exists prior to restoring either the full backup and/or
the tran log.
Thanks.
>--Original Message--
>Rather than doing a KILL command on each SPID, a cleaner
>way to do it is to put the database in single user mode
>with rollback immediate for the duration of the log
>restore and then put it back in multi user mode. This
>works very well. Here's an example:
>alter database database_name set SINGLE_USER with
rollback
>immediate
>restore log database_name from disk
>= 'c:\database_name_log.bak' with standby
>= 'c:\standby\database_name.bak'
>alter database database_name set MULTI_USER
>
if
>there are users connected to the database. I am
wondering
>if perhaps someone changed the dboptions when you had the
>log shipping fail after a BCP import.
>custom log shipping approach works very well.
>the specified database here:
>message news:1332101c3f7bf$64a958e0$a301280a@.phx
.gbl...
>to
>unit
or
listed
>where
>querying
>process
>when
>BULK
>to
>.
>|||Putting it in 'single user mode with rollback immediate'
will disconnect any currnet connections to the db and then
put it in single user mode for the process to restore the
log. Since it's in single user mode, only the process
that is restoring the log can connect. Once the restore
is done, just put it back into multi user mode.
>--Original Message--
>But even in single user mode, there could be multiple
>connections to the database, which can cause manual log
>shipping failures. In this case, I find killing all user
>connections more effective, to ensure no connections
>exists prior to restoring either the full backup and/or
>the tran log.
>Thanks.
>
>rollback
>if
>wondering
the
the
this
>or
>listed
from
changes
>.
>
Showing posts with label helloi. Show all posts
Showing posts with label helloi. Show all posts
Monday, March 19, 2012
Friday, March 9, 2012
managing the transaction log
Hello!
I'm working with an SQL database that someone else has set up and this is a learning experience for me.
I understand what the transaction log is and a little about it.
What i would like to do is shrink it because it is full. If i use the wizard to truncate or shrink data it never seems to work. I have created a second log file but the server doesn't seem to use it. Increasing the log size does nothing also. DARN!
What is the best way to dump the old data?
thanks in advance?
RIMQ1 [i would like to do is shrink it because it is full]?
A1 Note: one cannot shrink a 'Full' log beyond an active VLF; moreover, one must either dump / back up the contents of a transaction log to a transaction log backup *.trn file (or truncate it) before DBCC ShrinkFile can shrink the file to any smaller size.
Frequently dumping / backing up your (production database) transaction logs to transaction log backup *.trn files will provide the means of point in time recoverability; and also keep the overall DB log size managable. (Typically, production user DBs should be using the Full backup recovery model.)
General production guidelines include:
i The use of DBCC ShrinkFile, (and / or enable autoshrink if appropriate).
ii Identify any long running transactions that may be filling up your DB Log rewrite them to be efficient.
iii Dump the DB transaction log to transaction log backup *.trn files as appropriate for the production environmen.t
DBCC ShrinkFile advantages:
* it is safe
* it may be safely used even if your DB has multiple log files (add several additional log files to your DB, then rigorously test your method)
* ordinary users may work in the DB while its files are being shrunk
Use MyDB
Go
DBCC ShrinkFile ([MyDB_Log], 1, TruncateOnly)
Go|||This was address a couple of weeks ago - check out the link:
link (http://dbforums.com/showthread.php?s=&threadid=546372)
I'm working with an SQL database that someone else has set up and this is a learning experience for me.
I understand what the transaction log is and a little about it.
What i would like to do is shrink it because it is full. If i use the wizard to truncate or shrink data it never seems to work. I have created a second log file but the server doesn't seem to use it. Increasing the log size does nothing also. DARN!
What is the best way to dump the old data?
thanks in advance?
RIMQ1 [i would like to do is shrink it because it is full]?
A1 Note: one cannot shrink a 'Full' log beyond an active VLF; moreover, one must either dump / back up the contents of a transaction log to a transaction log backup *.trn file (or truncate it) before DBCC ShrinkFile can shrink the file to any smaller size.
Frequently dumping / backing up your (production database) transaction logs to transaction log backup *.trn files will provide the means of point in time recoverability; and also keep the overall DB log size managable. (Typically, production user DBs should be using the Full backup recovery model.)
General production guidelines include:
i The use of DBCC ShrinkFile, (and / or enable autoshrink if appropriate).
ii Identify any long running transactions that may be filling up your DB Log rewrite them to be efficient.
iii Dump the DB transaction log to transaction log backup *.trn files as appropriate for the production environmen.t
DBCC ShrinkFile advantages:
* it is safe
* it may be safely used even if your DB has multiple log files (add several additional log files to your DB, then rigorously test your method)
* ordinary users may work in the DB while its files are being shrunk
Use MyDB
Go
DBCC ShrinkFile ([MyDB_Log], 1, TruncateOnly)
Go|||This was address a couple of weeks ago - check out the link:
link (http://dbforums.com/showthread.php?s=&threadid=546372)
Wednesday, March 7, 2012
Managing permissions, user and group accounts in MSDE
Hello
I have little experience of MSDE but have worked with SQL Server at various
locations in the past.
Can anyone tell me the easiest way to manage permissions, user and group
accounts etc. in the absence of enterprise manager.
Does everything have to be scripted?
Many thanks
Mark
hi Mark,
Mark wrote:
> Hello
> I have little experience of MSDE but have worked with SQL Server at
> various locations in the past.
> Can anyone tell me the easiest way to manage permissions, user and
> group accounts etc. in the absence of enterprise manager.
> Does everything have to be scripted?
you can script out part of your management, but you probably have to
interactively perform some administration too... you can have a look at
http://support.microsoft.com/default...;EN-US;q325003 for some
hints on oSql.exe use for that... and/or you can resort on third party
management tools... some of them, both free and commercial, are listed at
http://www.microsoft.com/sql/msde/partners and
http://www.aspfaq.com/show.asp?id=2442
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea :@.)
Mark
I have little experience of MSDE but have worked with SQL Server at various
locations in the past.
Can anyone tell me the easiest way to manage permissions, user and group
accounts etc. in the absence of enterprise manager.
Does everything have to be scripted?
Many thanks
Mark
hi Mark,
Mark wrote:
> Hello
> I have little experience of MSDE but have worked with SQL Server at
> various locations in the past.
> Can anyone tell me the easiest way to manage permissions, user and
> group accounts etc. in the absence of enterprise manager.
> Does everything have to be scripted?
you can script out part of your management, but you probably have to
interactively perform some administration too... you can have a look at
http://support.microsoft.com/default...;EN-US;q325003 for some
hints on oSql.exe use for that... and/or you can resort on third party
management tools... some of them, both free and commercial, are listed at
http://www.microsoft.com/sql/msde/partners and
http://www.aspfaq.com/show.asp?id=2442
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea :@.)
Mark
Saturday, February 25, 2012
Management tools is installed but i cant find it...
Hello
I just installed Visual Studio 2005 Rc1, after that i installed Sql server
2005 september ctp.
Everything worked out fine and i installed everyprogram and all the tools
from sql server 2005 september ctp.
But when i try to find management studio i cant find it... its not under
Start Menu\Programs\Microsoft SQL Server 2005 CTP
the only thing thats under Microsoft SQL Server 2005 CTP is configuration
tools...
do i need to do some configuration to make management studio works?
On Fri, 7 Oct 2005 05:43:02 -0700, Diffen
<Diffen@.discussions.microsoft.com> wrote:
>Hello
>I just installed Visual Studio 2005 Rc1, after that i installed Sql server
>2005 september ctp.
>Everything worked out fine and i installed everyprogram and all the tools
>from sql server 2005 september ctp.
>But when i try to find management studio i cant find it... its not under
>Start Menu\Programs\Microsoft SQL Server 2005 CTP
>the only thing thats under Microsoft SQL Server 2005 CTP is configuration
>tools...
>do i need to do some configuration to make management studio works?
>
One common cause for not having the SQL Server Management Studio is
that you installed SQL Server Express Edition. It doesn't have SQL
Server Management Studio.
Another possibility is that you didn't choose to install SQL Server
Management Studio during Setup.
If you don't already have it, find a copy of SQL Server 2005 Developer
Edition (or above). Click the Advanced button during setup and study
the options for installation on the Advanced screen carefully.
Then you should be in good shape.
Andrew Watt
MVP - InfoPath
|||Hello Andrew.
When i installed the VS2005 RC1 i choosed to install sql 2005 express. but
after i finished that installation i installed sql server 2005 september ctp
(standard edition) in an new instance.
i have removed the sql 2005 september ctp and installed it again but its
still not there.
when im installing sql server 2005 september ctp im choosing all the
checkbox when i come to the point where i get the question what programs i
want to install.
when i read your answere i wonder if i should remove both vs2005 rc1 and sql
server 2005 september ctp. then install vs 2005 rc1 withour sql 2005 express
and then install sql server 2005 september ctp again.
Best regards
J?rgen
"Andrew Watt [MVP - InfoPath]" wrote:
> On Fri, 7 Oct 2005 05:43:02 -0700, Diffen
> <Diffen@.discussions.microsoft.com> wrote:
> One common cause for not having the SQL Server Management Studio is
> that you installed SQL Server Express Edition. It doesn't have SQL
> Server Management Studio.
> Another possibility is that you didn't choose to install SQL Server
> Management Studio during Setup.
> If you don't already have it, find a copy of SQL Server 2005 Developer
> Edition (or above). Click the Advanced button during setup and study
> the options for installation on the Advanced screen carefully.
> Then you should be in good shape.
> Andrew Watt
> MVP - InfoPath
>
|||Jorgen,
As a first step make sure you click the Advanced button during SQL
Server 2005 setup.
On the advanced screen make sure you notice the visual difference
between the install this component and the install this component and
all its subcomponent options.
It's easy to miss out some desired subcomponents.
If that works, then fine.
But if not ...
The recommended install order is SQL Server 2005 then Visual Studo
2005. So if the simpler approach doesn't work that looks like the way
to go.
Check after installing SQL Server that SQL Server Management Studio
has installed. It should be in Start|All Programs|Microsoft SQL Server
2005 CTP.
Andrew Watt
MVP - InfoPath
On Fri, 7 Oct 2005 14:01:33 -0700, Diffen
<Diffen@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hello Andrew.
>When i installed the VS2005 RC1 i choosed to install sql 2005 express. but
>after i finished that installation i installed sql server 2005 september ctp
>(standard edition) in an new instance.
>i have removed the sql 2005 september ctp and installed it again but its
>still not there.
>when im installing sql server 2005 september ctp im choosing all the
>checkbox when i come to the point where i get the question what programs i
>want to install.
>when i read your answere i wonder if i should remove both vs2005 rc1 and sql
>server 2005 september ctp. then install vs 2005 rc1 withour sql 2005 express
>and then install sql server 2005 september ctp again.
>Best regards
>Jrgen
>"Andrew Watt [MVP - InfoPath]" wrote:
|||Andrew,
Thank you very much for you quick support.
The problem was solved when i choosed advanced options in the setup just as
you suggested.
I reinstalled sql 2005 and vs 2005 rc1 and all worked out really fine.
Thanks allot!
"Andrew Watt [MVP - InfoPath]" wrote:
> Jorgen,
> As a first step make sure you click the Advanced button during SQL
> Server 2005 setup.
> On the advanced screen make sure you notice the visual difference
> between the install this component and the install this component and
> all its subcomponent options.
> It's easy to miss out some desired subcomponents.
> If that works, then fine.
> But if not ...
> The recommended install order is SQL Server 2005 then Visual Studo
> 2005. So if the simpler approach doesn't work that looks like the way
> to go.
> Check after installing SQL Server that SQL Server Management Studio
> has installed. It should be in Start|All Programs|Microsoft SQL Server
> 2005 CTP.
> Andrew Watt
> MVP - InfoPath
> On Fri, 7 Oct 2005 14:01:33 -0700, Diffen
> <Diffen@.discussions.microsoft.com> wrote:
>
>
|||You're welcome.
Andrew Watt
MVP - InfoPath
On Sat, 8 Oct 2005 04:31:02 -0700, Diffen
<Diffen@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Andrew,
>Thank you very much for you quick support.
>The problem was solved when i choosed advanced options in the setup just as
>you suggested.
>I reinstalled sql 2005 and vs 2005 rc1 and all worked out really fine.
>Thanks allot!
>"Andrew Watt [MVP - InfoPath]" wrote:
I just installed Visual Studio 2005 Rc1, after that i installed Sql server
2005 september ctp.
Everything worked out fine and i installed everyprogram and all the tools
from sql server 2005 september ctp.
But when i try to find management studio i cant find it... its not under
Start Menu\Programs\Microsoft SQL Server 2005 CTP
the only thing thats under Microsoft SQL Server 2005 CTP is configuration
tools...
do i need to do some configuration to make management studio works?
On Fri, 7 Oct 2005 05:43:02 -0700, Diffen
<Diffen@.discussions.microsoft.com> wrote:
>Hello
>I just installed Visual Studio 2005 Rc1, after that i installed Sql server
>2005 september ctp.
>Everything worked out fine and i installed everyprogram and all the tools
>from sql server 2005 september ctp.
>But when i try to find management studio i cant find it... its not under
>Start Menu\Programs\Microsoft SQL Server 2005 CTP
>the only thing thats under Microsoft SQL Server 2005 CTP is configuration
>tools...
>do i need to do some configuration to make management studio works?
>
One common cause for not having the SQL Server Management Studio is
that you installed SQL Server Express Edition. It doesn't have SQL
Server Management Studio.
Another possibility is that you didn't choose to install SQL Server
Management Studio during Setup.
If you don't already have it, find a copy of SQL Server 2005 Developer
Edition (or above). Click the Advanced button during setup and study
the options for installation on the Advanced screen carefully.
Then you should be in good shape.
Andrew Watt
MVP - InfoPath
|||Hello Andrew.
When i installed the VS2005 RC1 i choosed to install sql 2005 express. but
after i finished that installation i installed sql server 2005 september ctp
(standard edition) in an new instance.
i have removed the sql 2005 september ctp and installed it again but its
still not there.
when im installing sql server 2005 september ctp im choosing all the
checkbox when i come to the point where i get the question what programs i
want to install.
when i read your answere i wonder if i should remove both vs2005 rc1 and sql
server 2005 september ctp. then install vs 2005 rc1 withour sql 2005 express
and then install sql server 2005 september ctp again.
Best regards
J?rgen
"Andrew Watt [MVP - InfoPath]" wrote:
> On Fri, 7 Oct 2005 05:43:02 -0700, Diffen
> <Diffen@.discussions.microsoft.com> wrote:
> One common cause for not having the SQL Server Management Studio is
> that you installed SQL Server Express Edition. It doesn't have SQL
> Server Management Studio.
> Another possibility is that you didn't choose to install SQL Server
> Management Studio during Setup.
> If you don't already have it, find a copy of SQL Server 2005 Developer
> Edition (or above). Click the Advanced button during setup and study
> the options for installation on the Advanced screen carefully.
> Then you should be in good shape.
> Andrew Watt
> MVP - InfoPath
>
|||Jorgen,
As a first step make sure you click the Advanced button during SQL
Server 2005 setup.
On the advanced screen make sure you notice the visual difference
between the install this component and the install this component and
all its subcomponent options.
It's easy to miss out some desired subcomponents.
If that works, then fine.
But if not ...
The recommended install order is SQL Server 2005 then Visual Studo
2005. So if the simpler approach doesn't work that looks like the way
to go.
Check after installing SQL Server that SQL Server Management Studio
has installed. It should be in Start|All Programs|Microsoft SQL Server
2005 CTP.
Andrew Watt
MVP - InfoPath
On Fri, 7 Oct 2005 14:01:33 -0700, Diffen
<Diffen@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hello Andrew.
>When i installed the VS2005 RC1 i choosed to install sql 2005 express. but
>after i finished that installation i installed sql server 2005 september ctp
>(standard edition) in an new instance.
>i have removed the sql 2005 september ctp and installed it again but its
>still not there.
>when im installing sql server 2005 september ctp im choosing all the
>checkbox when i come to the point where i get the question what programs i
>want to install.
>when i read your answere i wonder if i should remove both vs2005 rc1 and sql
>server 2005 september ctp. then install vs 2005 rc1 withour sql 2005 express
>and then install sql server 2005 september ctp again.
>Best regards
>Jrgen
>"Andrew Watt [MVP - InfoPath]" wrote:
|||Andrew,
Thank you very much for you quick support.
The problem was solved when i choosed advanced options in the setup just as
you suggested.
I reinstalled sql 2005 and vs 2005 rc1 and all worked out really fine.
Thanks allot!
"Andrew Watt [MVP - InfoPath]" wrote:
> Jorgen,
> As a first step make sure you click the Advanced button during SQL
> Server 2005 setup.
> On the advanced screen make sure you notice the visual difference
> between the install this component and the install this component and
> all its subcomponent options.
> It's easy to miss out some desired subcomponents.
> If that works, then fine.
> But if not ...
> The recommended install order is SQL Server 2005 then Visual Studo
> 2005. So if the simpler approach doesn't work that looks like the way
> to go.
> Check after installing SQL Server that SQL Server Management Studio
> has installed. It should be in Start|All Programs|Microsoft SQL Server
> 2005 CTP.
> Andrew Watt
> MVP - InfoPath
> On Fri, 7 Oct 2005 14:01:33 -0700, Diffen
> <Diffen@.discussions.microsoft.com> wrote:
>
>
|||You're welcome.
Andrew Watt
MVP - InfoPath
On Sat, 8 Oct 2005 04:31:02 -0700, Diffen
<Diffen@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Andrew,
>Thank you very much for you quick support.
>The problem was solved when i choosed advanced options in the setup just as
>you suggested.
>I reinstalled sql 2005 and vs 2005 rc1 and all worked out really fine.
>Thanks allot!
>"Andrew Watt [MVP - InfoPath]" wrote:
Subscribe to:
Posts (Atom)