logshipping setup with two standby db servers.

Hi,
Currently we have logshipping setup with one production db server
sending the logs to one standby database server.

we would like to have setup where there will be two standby db
servers. Second server will be located in different country.
Kindly let me know if we can have the setup where
there is one primary server and two standby db servers.

When I tried to configure the same I am not able to complete the
maintenance plan.
It gives error that logshipping monitor already exists.
Please help me. tks in advance.
Regards
Kamalkarora@.melstar.com (Kamal) wrote in message news:<a4d6a1fb.0408230107.abdaf08@.posting.google.com>...
> Hi,
> Currently we have logshipping setup with one production db server
> sending the logs to one standby database server.
> we would like to have setup where there will be two standby db
> servers. Second server will be located in different country.
> Kindly let me know if we can have the setup where
> there is one primary server and two standby db servers.
> When I tried to configure the same I am not able to complete the
> maintenance plan.
> It gives error that logshipping monitor already exists.
> Please help me. tks in advance.
> Regards
> Kamal

Yes, this is possible:

http://support.microsoft.com/defaul...inal.asp#modify
http://www.microsoft.com/resources/...art4/c1361.mspx

You might also want to check this article, which describes one
situation where you might get that error:

http://support.microsoft.com/defaul...q298743&sd=tech

Also check out "Modifying Log Shipping" in Books Online.

Simon

Logshipping Help Needed

I am setting up log shipping between a primary server and two secondaries.

The first (secondary ) is applying the logs fine but the second is generating the following error.

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3154: [Microsoft][ODBC SQL Server Driver][SQL Server]The backup set holds a backup of a database other than the existing 'CDE2' database.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.

the logshipping monitor says it failes to apply first_file_0000000000...Trn

I have seen this listed on MS KB Base Article - 311801 .

and have followed all the suggestions to resolve this issue including seperate folders for each log ship but to no avail.

Any help would be greatly appreciated.

Thanks in advanceDIsable the LS and apply full backup on secondary server, then enable LS again.sql

Logs/Restore Logs

Whats contained the the Backup Log Files ? What goes on when a restore of
the log takes place on say a standby server ?
If I do some inserts/updates and deletes against DB A ... And when I backup
the log after executing those statements, will the log contain those DML
statements or will it contain all the data/index pages that were being
affected by those DML ?
And if it does contain the DML , does that mean that when a restore log
takes place, the DML gets fired on the standby ?
And if the backup logs do not contain DML statements, what would it contain
and how does it do the restore ?
Please let me know the technical details...Using SQL 2000.
Also curious to know what happens when some DBCC commands are run such as
dbreindex or indexdefrag ...Hassan,
The log files contain changes made to data in the database. If the DML is
changed it updates (for example) sysobjects, syscolumns, syscomments, etc.
depending on the DML. If you ALTER TABLE and add a column with a default,
the log will contain (1) changes to the system objects, (2) every row in the
table with its new default value, (3) any indexes that are updated in the
process, (4) and so forth.
The commands that you issued are not recorded in the log, the results that
you obtained are recorded there.
Ditto on DBCC, any changes go into the logs.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Whats contained the the Backup Log Files ? What goes on when a restore of
> the log takes place on say a standby server ?
> If I do some inserts/updates and deletes against DB A ... And when I
backup
> the log after executing those statements, will the log contain those DML
> statements or will it contain all the data/index pages that were being
> affected by those DML ?
> And if it does contain the DML , does that mean that when a restore log
> takes place, the DML gets fired on the standby ?
> And if the backup logs do not contain DML statements, what would it
contain
> and how does it do the restore ?
> Please let me know the technical details...Using SQL 2000.
> Also curious to know what happens when some DBCC commands are run such as
> dbreindex or indexdefrag ...
>|||Ok.. thats informational.. How does the restore take place then ? So ive got
all the changes which im assuming that the data and index pages are captured
in the log.. Does it just about and replacing the data and index pages with
the new ones on the standby server.. Is that how the restore logs work
Thanks
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
> Hassan,
> The log files contain changes made to data in the database. If the DML is
> changed it updates (for example) sysobjects, syscolumns, syscomments, etc.
> depending on the DML. If you ALTER TABLE and add a column with a default,
> the log will contain (1) changes to the system objects, (2) every row in
the
> table with its new default value, (3) any indexes that are updated in the
> process, (4) and so forth.
> The commands that you issued are not recorded in the log, the results that
> you obtained are recorded there.
> Ditto on DBCC, any changes go into the logs.
> Russell Fields
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
of
> backup
> contain
as
>|||Row changes are in the log as well as page allocations, splits, etc.
So, no it does not just replace the pages since that would greatly increase
the log size. (For example, in that case if I updated 10 200-byte rows on
10 pages I would log about 160,000 bytes instead of about 4000 bytes.
Before and after images.)
The transactions are rolled forward one at a time.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#3UnFiy9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Ok.. thats informational.. How does the restore take place then ? So ive
got
> all the changes which im assuming that the data and index pages are
captured
> in the log.. Does it just about and replacing the data and index pages
with
> the new ones on the standby server.. Is that how the restore logs work
> Thanks
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
is
etc.
default,
> the
the
that
> of
DML
log
> as
>|||Hassan,
If you really want to know how most products implement transaction logging,
I recommend the books "Transaction Processing Concepts and Techniques" by
Reuter and Gray. Not light reading, though. But it goes through what type of
information that need to be logged in order for a product to use the log for
roll forward and roll back. Of course, vendors are probably tweaking these
algorithms a bit, but my guess is that most products uses these base
techniques in one form or another.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%233UnFiy9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Ok.. thats informational.. How does the restore take place then ? So ive
got
> all the changes which im assuming that the data and index pages are
captured
> in the log.. Does it just about and replacing the data and index pages
with
> the new ones on the standby server.. Is that how the restore logs work
> Thanks
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
is
etc.
default,
> the
the
that
> of
DML
log
> as
>sql

Logs/Restore Logs

Whats contained the the Backup Log Files ? What goes on when a restore of
the log takes place on say a standby server ?
If I do some inserts/updates and deletes against DB A ... And when I backup
the log after executing those statements, will the log contain those DML
statements or will it contain all the data/index pages that were being
affected by those DML ?
And if it does contain the DML , does that mean that when a restore log
takes place, the DML gets fired on the standby ?
And if the backup logs do not contain DML statements, what would it contain
and how does it do the restore ?
Please let me know the technical details...Using SQL 2000.
Also curious to know what happens when some DBCC commands are run such as
dbreindex or indexdefrag ...Hassan,
The log files contain changes made to data in the database. If the DML is
changed it updates (for example) sysobjects, syscolumns, syscomments, etc.
depending on the DML. If you ALTER TABLE and add a column with a default,
the log will contain (1) changes to the system objects, (2) every row in the
table with its new default value, (3) any indexes that are updated in the
process, (4) and so forth.
The commands that you issued are not recorded in the log, the results that
you obtained are recorded there.
Ditto on DBCC, any changes go into the logs.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Whats contained the the Backup Log Files ? What goes on when a restore of
> the log takes place on say a standby server ?
> If I do some inserts/updates and deletes against DB A ... And when I
backup
> the log after executing those statements, will the log contain those DML
> statements or will it contain all the data/index pages that were being
> affected by those DML ?
> And if it does contain the DML , does that mean that when a restore log
> takes place, the DML gets fired on the standby ?
> And if the backup logs do not contain DML statements, what would it
contain
> and how does it do the restore ?
> Please let me know the technical details...Using SQL 2000.
> Also curious to know what happens when some DBCC commands are run such as
> dbreindex or indexdefrag ...
>|||Ok.. thats informational.. How does the restore take place then ? So ive got
all the changes which im assuming that the data and index pages are captured
in the log.. Does it just about and replacing the data and index pages with
the new ones on the standby server.. Is that how the restore logs work
Thanks
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
> Hassan,
> The log files contain changes made to data in the database. If the DML is
> changed it updates (for example) sysobjects, syscolumns, syscomments, etc.
> depending on the DML. If you ALTER TABLE and add a column with a default,
> the log will contain (1) changes to the system objects, (2) every row in
the
> table with its new default value, (3) any indexes that are updated in the
> process, (4) and so forth.
> The commands that you issued are not recorded in the log, the results that
> you obtained are recorded there.
> Ditto on DBCC, any changes go into the logs.
> Russell Fields
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > Whats contained the the Backup Log Files ? What goes on when a restore
of
> > the log takes place on say a standby server ?
> >
> > If I do some inserts/updates and deletes against DB A ... And when I
> backup
> > the log after executing those statements, will the log contain those DML
> > statements or will it contain all the data/index pages that were being
> > affected by those DML ?
> >
> > And if it does contain the DML , does that mean that when a restore log
> > takes place, the DML gets fired on the standby ?
> > And if the backup logs do not contain DML statements, what would it
> contain
> > and how does it do the restore ?
> >
> > Please let me know the technical details...Using SQL 2000.
> >
> > Also curious to know what happens when some DBCC commands are run such
as
> > dbreindex or indexdefrag ...
> >
> >
>|||Row changes are in the log as well as page allocations, splits, etc.
So, no it does not just replace the pages since that would greatly increase
the log size. (For example, in that case if I updated 10 200-byte rows on
10 pages I would log about 160,000 bytes instead of about 4000 bytes.
Before and after images.)
The transactions are rolled forward one at a time.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#3UnFiy9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Ok.. thats informational.. How does the restore take place then ? So ive
got
> all the changes which im assuming that the data and index pages are
captured
> in the log.. Does it just about and replacing the data and index pages
with
> the new ones on the standby server.. Is that how the restore logs work
> Thanks
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
> > Hassan,
> >
> > The log files contain changes made to data in the database. If the DML
is
> > changed it updates (for example) sysobjects, syscolumns, syscomments,
etc.
> > depending on the DML. If you ALTER TABLE and add a column with a
default,
> > the log will contain (1) changes to the system objects, (2) every row in
> the
> > table with its new default value, (3) any indexes that are updated in
the
> > process, (4) and so forth.
> >
> > The commands that you issued are not recorded in the log, the results
that
> > you obtained are recorded there.
> >
> > Ditto on DBCC, any changes go into the logs.
> >
> > Russell Fields
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > Whats contained the the Backup Log Files ? What goes on when a restore
> of
> > > the log takes place on say a standby server ?
> > >
> > > If I do some inserts/updates and deletes against DB A ... And when I
> > backup
> > > the log after executing those statements, will the log contain those
DML
> > > statements or will it contain all the data/index pages that were being
> > > affected by those DML ?
> > >
> > > And if it does contain the DML , does that mean that when a restore
log
> > > takes place, the DML gets fired on the standby ?
> > > And if the backup logs do not contain DML statements, what would it
> > contain
> > > and how does it do the restore ?
> > >
> > > Please let me know the technical details...Using SQL 2000.
> > >
> > > Also curious to know what happens when some DBCC commands are run such
> as
> > > dbreindex or indexdefrag ...
> > >
> > >
> >
> >
>|||Hassan,
If you really want to know how most products implement transaction logging,
I recommend the books "Transaction Processing Concepts and Techniques" by
Reuter and Gray. Not light reading, though. But it goes through what type of
information that need to be logged in order for a product to use the log for
roll forward and roll back. Of course, vendors are probably tweaking these
algorithms a bit, but my guess is that most products uses these base
techniques in one form or another.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%233UnFiy9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Ok.. thats informational.. How does the restore take place then ? So ive
got
> all the changes which im assuming that the data and index pages are
captured
> in the log.. Does it just about and replacing the data and index pages
with
> the new ones on the standby server.. Is that how the restore logs work
> Thanks
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
> > Hassan,
> >
> > The log files contain changes made to data in the database. If the DML
is
> > changed it updates (for example) sysobjects, syscolumns, syscomments,
etc.
> > depending on the DML. If you ALTER TABLE and add a column with a
default,
> > the log will contain (1) changes to the system objects, (2) every row in
> the
> > table with its new default value, (3) any indexes that are updated in
the
> > process, (4) and so forth.
> >
> > The commands that you issued are not recorded in the log, the results
that
> > you obtained are recorded there.
> >
> > Ditto on DBCC, any changes go into the logs.
> >
> > Russell Fields
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > Whats contained the the Backup Log Files ? What goes on when a restore
> of
> > > the log takes place on say a standby server ?
> > >
> > > If I do some inserts/updates and deletes against DB A ... And when I
> > backup
> > > the log after executing those statements, will the log contain those
DML
> > > statements or will it contain all the data/index pages that were being
> > > affected by those DML ?
> > >
> > > And if it does contain the DML , does that mean that when a restore
log
> > > takes place, the DML gets fired on the standby ?
> > > And if the backup logs do not contain DML statements, what would it
> > contain
> > > and how does it do the restore ?
> > >
> > > Please let me know the technical details...Using SQL 2000.
> > >
> > > Also curious to know what happens when some DBCC commands are run such
> as
> > > dbreindex or indexdefrag ...
> > >
> > >
> >
> >
>

Logs with timestamp

Hi,

I'm using the SSIS log feature. However, I want a timestamp on the file everytime the IS package runs so as to keep track also that this particular log file is for this specific execution of IS.

How do i do that?

thanks a lot

cherriesh

Are you logging to SQL Server? If so, you can add a column to the dbo.sysdtslog90 table with a datetime datatype and set its default to GetDate().

I recommend that you read Jamie Thomson's blog about custom logging: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

|||

Here's a post from Jamie that shows how to set the log file name dynamically through an expression:

http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx

Logs Shipping ReInit - how to?

We have setup Log Shipping in SQL 2005. Working flawlessly. It appears that
only tran log backup will occur forever unless we intervene. I do want a full
backup to happen, you know, eventually...
What is best way to automate this? Ideally, a full backup nightly would be
great. How to do in script, or is there anything in gui?
tia,
chrisYou want a full backup of the primary? Just do Backup database...
You cannot backup the secondary as far as I know.
If you want to re-init log shipping and start clean on a system that LS is
working flawlessly on, you aer simply wasting bandwidth and cpu. :)
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:2E07F579-4A8A-47B6-B457-D7B543F00086@.microsoft.com...
> We have setup Log Shipping in SQL 2005. Working flawlessly. It appears
> that
> only tran log backup will occur forever unless we intervene. I do want a
> full
> backup to happen, you know, eventually...
> What is best way to automate this? Ideally, a full backup nightly would be
> great. How to do in script, or is there anything in gui?
> tia,
> chris|||I can't "just do backup database..." this will break log shipping. Correct?
I'm concerned w/ primary - to start log shipping you do a full backup to
init, then tran log backups thereafter w/ each log shipped to secondary. I DO
NOT want to do tran log backups forever... I eventually would like to do a
full backup.
Am I missing somehting...?
cb
"Kevin3NF" wrote:
> You want a full backup of the primary? Just do Backup database...
> You cannot backup the secondary as far as I know.
> If you want to re-init log shipping and start clean on a system that LS is
> working flawlessly on, you aer simply wasting bandwidth and cpu. :)
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:2E07F579-4A8A-47B6-B457-D7B543F00086@.microsoft.com...
> >
> > We have setup Log Shipping in SQL 2005. Working flawlessly. It appears
> > that
> > only tran log backup will occur forever unless we intervene. I do want a
> > full
> > backup to happen, you know, eventually...
> >
> > What is best way to automate this? Ideally, a full backup nightly would be
> > great. How to do in script, or is there anything in gui?
> >
> > tia,
> > chris
>
>|||Full backups do NOT break the log chain sequence so you can do a FULL backup
at any time. The next log backup after a FULL backup will still capture any
transactions since the previous Log backup, regardless of if there was a
FULL in between or not.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:D69D6BFF-3674-4894-92DF-35EB8261E9DA@.microsoft.com...
> I can't "just do backup database..." this will break log shipping.
> Correct?
> I'm concerned w/ primary - to start log shipping you do a full backup to
> init, then tran log backups thereafter w/ each log shipped to secondary. I
> DO
> NOT want to do tran log backups forever... I eventually would like to do a
> full backup.
> Am I missing somehting...?
> cb
> "Kevin3NF" wrote:
>> You want a full backup of the primary? Just do Backup database...
>> You cannot backup the secondary as far as I know.
>> If you want to re-init log shipping and start clean on a system that LS
>> is
>> working flawlessly on, you aer simply wasting bandwidth and cpu. :)
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Chris" <Chris@.discussions.microsoft.com> wrote in message
>> news:2E07F579-4A8A-47B6-B457-D7B543F00086@.microsoft.com...
>> >
>> > We have setup Log Shipping in SQL 2005. Working flawlessly. It appears
>> > that
>> > only tran log backup will occur forever unless we intervene. I do want
>> > a
>> > full
>> > backup to happen, you know, eventually...
>> >
>> > What is best way to automate this? Ideally, a full backup nightly would
>> > be
>> > great. How to do in script, or is there anything in gui?
>> >
>> > tia,
>> > chris
>>|||Also, taking DIFFERENTIAL BACKUP would NOT break the log sequence.
However taking TRANSACTION LOG backup (other than the automated jobs of Log
Shipping itself) would break the chain.
--
Ekrem Ã?nsoy
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:D69D6BFF-3674-4894-92DF-35EB8261E9DA@.microsoft.com...
> I can't "just do backup database..." this will break log shipping.
> Correct?
> I'm concerned w/ primary - to start log shipping you do a full backup to
> init, then tran log backups thereafter w/ each log shipped to secondary. I
> DO
> NOT want to do tran log backups forever... I eventually would like to do a
> full backup.
> Am I missing somehting...?
> cb
> "Kevin3NF" wrote:
>> You want a full backup of the primary? Just do Backup database...
>> You cannot backup the secondary as far as I know.
>> If you want to re-init log shipping and start clean on a system that LS
>> is
>> working flawlessly on, you aer simply wasting bandwidth and cpu. :)
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Chris" <Chris@.discussions.microsoft.com> wrote in message
>> news:2E07F579-4A8A-47B6-B457-D7B543F00086@.microsoft.com...
>> >
>> > We have setup Log Shipping in SQL 2005. Working flawlessly. It appears
>> > that
>> > only tran log backup will occur forever unless we intervene. I do want
>> > a
>> > full
>> > backup to happen, you know, eventually...
>> >
>> > What is best way to automate this? Ideally, a full backup nightly would
>> > be
>> > great. How to do in script, or is there anything in gui?
>> >
>> > tia,
>> > chris
>>

logs of creating and deleteing jobs

Hello
Does anybody know where/if MSSQL (especially 2005) keeps logs of
creating and deleteing jobs?

Thanks in advance
BartoloNo, it does not. Jobs are simple entries in the MSDB database, if you
want to keep track of that you will either have to tweak the appropiate
procedure that access these tables or write a trigger on the tables if
you are not sure if someone directly manipulates the tables.

HTH, Jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||Thanks Jens,
I wrote that because, I realized that one of my db haven't got backup
job since few days. Job had been written before and I dont know who
could do that or why it disappeard.

Regards,
Bartolosql

Logs NOT truncating with Arcserve v9?

Anyone using Brightstor Arcserve v9 + the SQL agent to backup their SQL
databases?
We are using it to backup all our SQL2000 databases (15) and it works just
fine *except* even though it is configured to do a 'complete backup' of each
database it doesnt truncate the transaction log at the end...with the
consequence that the logs bloat out the many Gigabytes.
I understood that the default behaviour for a 'complete backup' was to
backup the database+logs then truncate?
The arcserve agent give the option to run a 'transaction log' backup...(ie
logs only) but that is not what we want.
Any ideas on how to get it to truncate those logs once the backup is
completed sucessfully (other than use another product ;)
NB the CA support site for Arcserve is *REALLY* crap - every query returns
hundreds of product releases and adverts for their bloody software but no
technical information. Every time they change it, it gets worse
Al Blake, Canberra, AustraliaAl,

> I understood that the default behaviour for a 'complete backup' was to
> backup the database+logs then truncate?
You would have to ask Arcserve about this. In SQL Server, BACKUP DATABASE
does not empty the log. You either do regular log backups or put the
database in simple recovery mode. A tip is to run Profiler while the
Arcserve job is executed to catch what is *really* going on.
Also, information about shrinking files etc (as I guess you might want to do
this) is found at below side, see the links in the text:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Al Blake" <al@.blakes.net> wrote in message
news:eBeoGjGEEHA.3804@.TK2MSFTNGP09.phx.gbl...
> Anyone using Brightstor Arcserve v9 + the SQL agent to backup their SQL
> databases?
> We are using it to backup all our SQL2000 databases (15) and it works just
> fine *except* even though it is configured to do a 'complete backup' of
each
> database it doesnt truncate the transaction log at the end...with the
> consequence that the logs bloat out the many Gigabytes.
> I understood that the default behaviour for a 'complete backup' was to
> backup the database+logs then truncate?
> The arcserve agent give the option to run a 'transaction log' backup...(ie
> logs only) but that is not what we want.
> Any ideas on how to get it to truncate those logs once the backup is
> completed sucessfully (other than use another product ;)
> NB the CA support site for Arcserve is *REALLY* crap - every query returns
> hundreds of product releases and adverts for their bloody software but no
> technical information. Every time they change it, it gets worse
> Al Blake, Canberra, Australia
>

Logs not truncating

Hi,
we have a problem on one of our databases.. mainly the log is not truncating
after a backup.. By now it has grown to 3Gb, with the data file at 156Mb.
Any Ideas?
Thanks
NicholasHave a look here :-
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
--
HTH
Ryan Waight, MCDBA, MCSE
"Nicholas Aquilina" <naquilina@.gfi.com> wrote in message
news:e$cXQaKnDHA.372@.TK2MSFTNGP11.phx.gbl...
> Hi,
> we have a problem on one of our databases.. mainly the log is not
truncating
> after a backup.. By now it has grown to 3Gb, with the data file at 156Mb.
> Any Ideas?
> Thanks
> Nicholas
>|||Hi
The article helped..I had to use DBCC SHRINKFILE(Database_log,6). I shrunk
the log to 700Mb first from 3Gb, and then the rest with a backup & log
truncate worked normally. Now could the shrinkfile possibly affect any
replication? We have a snapshot replication from this same database to other
servers
Thanks Aagain
Nicholas
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eWDTxdKnDHA.3024@.tk2msftngp13.phx.gbl...
> Have a look here :-
> http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Nicholas Aquilina" <naquilina@.gfi.com> wrote in message
> news:e$cXQaKnDHA.372@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > we have a problem on one of our databases.. mainly the log is not
> truncating
> > after a backup.. By now it has grown to 3Gb, with the data file at
156Mb.
> >
> > Any Ideas?
> >
> > Thanks
> >
> > Nicholas
> >
> >
>

logs getting full

Hi all. If we have a long running process for a database and the log
is threatening to hit 100%, whats the best way to resolve it?"sumGirl" <emebohw@.netscape.net> wrote in message
news:a5e13cff.0410160524.3a7087c8@.posting.google.c om...
> Hi all. If we have a long running process for a database and the log
> is threatening to hit 100%, whats the best way to resolve it?

Expand the log or turn the long running processes into smaller chunks.

LOGS Files

Hi All,
SQL 2000 sp4 under Windows 2000 server sp4.
I have problem with my logs files, they are growing so much. I have
optimization job schedule once a week but looks like it's no helping with
logs files.
To fix a problem this morning on my server we have to detach the data,
delete the log file and attach the data again to be able to work.
Should I create a script to detach, delete the log files and attach the data
again everyday at midnight?
I have a low budget so I can't use third party software
It's a better way to fix this?
Tks in advance.
JFBJFB,
What is your recovery model set to. Is it set to full? if yes I am
assuming this is a production database.
Have you tried using DBCC SHRINKFILE as a nightly job, rather than detaching
and reattaching data everyday.
Thanks,
Rocky A
"JFB" wrote:

> Hi All,
> SQL 2000 sp4 under Windows 2000 server sp4.
> I have problem with my logs files, they are growing so much. I have
> optimization job schedule once a week but looks like it's no helping with
> logs files.
> To fix a problem this morning on my server we have to detach the data,
> delete the log file and attach the data again to be able to work.
> Should I create a script to detach, delete the log files and attach the da
ta
> again everyday at midnight?
> I have a low budget so I can't use third party software
> It's a better way to fix this?
> Tks in advance.
> JFB
>
>|||Run regular transaction log backups to address the issues
with the size of the log files.
-Sue
On Mon, 1 May 2006 17:43:35 -0400, "JFB" <help@.jfb.com>
wrote:

>Hi All,
>SQL 2000 sp4 under Windows 2000 server sp4.
>I have problem with my logs files, they are growing so much. I have
>optimization job schedule once a week but looks like it's no helping with
>logs files.
>To fix a problem this morning on my server we have to detach the data,
>delete the log file and attach the data again to be able to work.
>Should I create a script to detach, delete the log files and attach the dat
a
>again everyday at midnight?
>I have a low budget so I can't use third party software
>It's a better way to fix this?
>Tks in advance.
>JFB
>|||If you not require transaction log backup set recovery model to simple.
else if recovery model is not simple
Take transaction log backup regularaly , it will truncate commited log.
or in critical condition you can use
backup log <dbname> with no_log.
This will truncate log without backup , but its not recommended , since
it will break chain of transaction log.
Dont play with database by detaching backup and deleting log files.
Regards
Amish shah|||Tks for reply.
I have daily log backups as part of my maintenance schedule jobs using this
EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIfC
lean
-CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
2WEEKS -CrBkSubDir -BkExt "TRN"'
But looks like it's not truncated commited log.
It's a way to include the no_log in this command line?
Rgds
JFB
"amish" <shahamishm@.gmail.com> wrote in message
news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
> If you not require transaction log backup set recovery model to simple.
> else if recovery model is not simple
> Take transaction log backup regularaly , it will truncate commited log.
> or in critical condition you can use
> backup log <dbname> with no_log.
> This will truncate log without backup , but its not recommended , since
> it will break chain of transaction log.
> Dont play with database by detaching backup and deleting log files.
> Regards
> Amish shah
>|||Did you verify that transaction log backups are actually
going to the BackupLogs directory?
You may also need to increase the frequency of your log
backups.
With maintenance plans, you don't have as much flexibility
on the various commands. There is no option to do a no_log
or truncate_only through maintenance plans.
-Sue
On Tue, 2 May 2006 10:37:43 -0400, "JFB" <help@.jfb.com>
wrote:

>Tks for reply.
>I have daily log backups as part of my maintenance schedule jobs using this
>EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
>581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIf
Clean
> -CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
>2WEEKS -CrBkSubDir -BkExt "TRN"'
>But looks like it's not truncated commited log.
>It's a way to include the no_log in this command line?
>Rgds
>JFB
>
>"amish" <shahamishm@.gmail.com> wrote in message
>news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
>|||Tks for you reply Sue,
So how can I do it on a store procedure?
Any link or example... I'm going to install probably another HD to my raid
to fix this problem temporaly but I need to cut this logs..
Please help
Rgds
JFB
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:j44i52d1v8ta0lch1tm8od9di8vdsd911t@.
4ax.com...
> Did you verify that transaction log backups are actually
> going to the BackupLogs directory?
> You may also need to increase the frequency of your log
> backups.
> With maintenance plans, you don't have as much flexibility
> on the various commands. There is no option to do a no_log
> or truncate_only through maintenance plans.
> -Sue
> On Tue, 2 May 2006 10:37:43 -0400, "JFB" <help@.jfb.com>
> wrote:
>
>|||The SQL statement would be:
Backup Log YourDB
with truncate_only
-Sue
On Fri, 5 May 2006 21:30:00 -0400, "JFB" <help@.jfb.com>
wrote:

>Tks for you reply Sue,
>So how can I do it on a store procedure?
>Any link or example... I'm going to install probably another HD to my raid
>to fix this problem temporaly but I need to cut this logs..
>Please help
>Rgds
>JFB
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:j44i52d1v8ta0lch1tm8od9di8vdsd911t@.
4ax.com...
>

LOGS Files

Hi All,
SQL 2000 sp4 under windows 2000 server sp4.
I have problem with my logs files, they are growing so much. I have
optimization job schedule once a week but looks like it's no helping with
logs files.
To fix a problem this morning on my server we have to detach the data,
delete the log file and attach the data again to be able to work.
Should I create a script to detach, delete the log files and attach the data
again everyday at midnight?
I have a low budget so I can't use third party software :(
It's a better way to fix this?
Tks in advance.
JFBJFB,
What is your recovery model set to. Is it set to full? if yes I am
assuming this is a production database.
Have you tried using DBCC SHRINKFILE as a nightly job, rather than detaching
and reattaching data everyday.
Thanks,
Rocky A
"JFB" wrote:
> Hi All,
> SQL 2000 sp4 under windows 2000 server sp4.
> I have problem with my logs files, they are growing so much. I have
> optimization job schedule once a week but looks like it's no helping with
> logs files.
> To fix a problem this morning on my server we have to detach the data,
> delete the log file and attach the data again to be able to work.
> Should I create a script to detach, delete the log files and attach the data
> again everyday at midnight?
> I have a low budget so I can't use third party software :(
> It's a better way to fix this?
> Tks in advance.
> JFB
>
>|||Run regular transaction log backups to address the issues
with the size of the log files.
-Sue
On Mon, 1 May 2006 17:43:35 -0400, "JFB" <help@.jfb.com>
wrote:
>Hi All,
>SQL 2000 sp4 under windows 2000 server sp4.
>I have problem with my logs files, they are growing so much. I have
>optimization job schedule once a week but looks like it's no helping with
>logs files.
>To fix a problem this morning on my server we have to detach the data,
>delete the log file and attach the data again to be able to work.
>Should I create a script to detach, delete the log files and attach the data
>again everyday at midnight?
>I have a low budget so I can't use third party software :(
>It's a better way to fix this?
>Tks in advance.
>JFB
>|||If you not require transaction log backup set recovery model to simple.
else if recovery model is not simple
Take transaction log backup regularaly , it will truncate commited log.
or in critical condition you can use
backup log <dbname> with no_log.
This will truncate log without backup , but its not recommended , since
it will break chain of transaction log.
Dont play with database by detaching backup and deleting log files.
Regards
Amish shah|||Tks for reply.
I have daily log backups as part of my maintenance schedule jobs using this
EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIfClean
-CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
2WEEKS -CrBkSubDir -BkExt "TRN"'
But looks like it's not truncated commited log.
It's a way to include the no_log in this command line?
Rgds
JFB
"amish" <shahamishm@.gmail.com> wrote in message
news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
> If you not require transaction log backup set recovery model to simple.
> else if recovery model is not simple
> Take transaction log backup regularaly , it will truncate commited log.
> or in critical condition you can use
> backup log <dbname> with no_log.
> This will truncate log without backup , but its not recommended , since
> it will break chain of transaction log.
> Dont play with database by detaching backup and deleting log files.
> Regards
> Amish shah
>|||Did you verify that transaction log backups are actually
going to the BackupLogs directory?
You may also need to increase the frequency of your log
backups.
With maintenance plans, you don't have as much flexibility
on the various commands. There is no option to do a no_log
or truncate_only through maintenance plans.
-Sue
On Tue, 2 May 2006 10:37:43 -0400, "JFB" <help@.jfb.com>
wrote:
>Tks for reply.
>I have daily log backups as part of my maintenance schedule jobs using this
>EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
>581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIfClean
> -CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
>2WEEKS -CrBkSubDir -BkExt "TRN"'
>But looks like it's not truncated commited log.
>It's a way to include the no_log in this command line?
>Rgds
>JFB
>
>"amish" <shahamishm@.gmail.com> wrote in message
>news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
>> If you not require transaction log backup set recovery model to simple.
>> else if recovery model is not simple
>> Take transaction log backup regularaly , it will truncate commited log.
>> or in critical condition you can use
>> backup log <dbname> with no_log.
>> This will truncate log without backup , but its not recommended , since
>> it will break chain of transaction log.
>> Dont play with database by detaching backup and deleting log files.
>> Regards
>> Amish shah
>|||Tks for you reply Sue,
So how can I do it on a store procedure?
Any link or example... I'm going to install probably another HD to my raid
to fix this problem temporaly but I need to cut this logs..
Please help
Rgds
JFB
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:j44i52d1v8ta0lch1tm8od9di8vdsd911t@.4ax.com...
> Did you verify that transaction log backups are actually
> going to the BackupLogs directory?
> You may also need to increase the frequency of your log
> backups.
> With maintenance plans, you don't have as much flexibility
> on the various commands. There is no option to do a no_log
> or truncate_only through maintenance plans.
> -Sue
> On Tue, 2 May 2006 10:37:43 -0400, "JFB" <help@.jfb.com>
> wrote:
>>Tks for reply.
>>I have daily log backups as part of my maintenance schedule jobs using
>>this
>>EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
>>581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIfClean
>> -CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
>>2WEEKS -CrBkSubDir -BkExt "TRN"'
>>But looks like it's not truncated commited log.
>>It's a way to include the no_log in this command line?
>>Rgds
>>JFB
>>
>>"amish" <shahamishm@.gmail.com> wrote in message
>>news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
>> If you not require transaction log backup set recovery model to simple.
>> else if recovery model is not simple
>> Take transaction log backup regularaly , it will truncate commited log.
>> or in critical condition you can use
>> backup log <dbname> with no_log.
>> This will truncate log without backup , but its not recommended , since
>> it will break chain of transaction log.
>> Dont play with database by detaching backup and deleting log files.
>> Regards
>> Amish shah
>>
>|||The SQL statement would be:
Backup Log YourDB
with truncate_only
-Sue
On Fri, 5 May 2006 21:30:00 -0400, "JFB" <help@.jfb.com>
wrote:
>Tks for you reply Sue,
>So how can I do it on a store procedure?
>Any link or example... I'm going to install probably another HD to my raid
>to fix this problem temporaly but I need to cut this logs..
>Please help
>Rgds
>JFB
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:j44i52d1v8ta0lch1tm8od9di8vdsd911t@.4ax.com...
>> Did you verify that transaction log backups are actually
>> going to the BackupLogs directory?
>> You may also need to increase the frequency of your log
>> backups.
>> With maintenance plans, you don't have as much flexibility
>> on the various commands. There is no option to do a no_log
>> or truncate_only through maintenance plans.
>> -Sue
>> On Tue, 2 May 2006 10:37:43 -0400, "JFB" <help@.jfb.com>
>> wrote:
>>Tks for reply.
>>I have daily log backups as part of my maintenance schedule jobs using
>>this
>>EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
>>581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIfClean
>> -CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
>>2WEEKS -CrBkSubDir -BkExt "TRN"'
>>But looks like it's not truncated commited log.
>>It's a way to include the no_log in this command line?
>>Rgds
>>JFB
>>
>>"amish" <shahamishm@.gmail.com> wrote in message
>>news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
>> If you not require transaction log backup set recovery model to simple.
>> else if recovery model is not simple
>> Take transaction log backup regularaly , it will truncate commited log.
>> or in critical condition you can use
>> backup log <dbname> with no_log.
>> This will truncate log without backup , but its not recommended , since
>> it will break chain of transaction log.
>> Dont play with database by detaching backup and deleting log files.
>> Regards
>> Amish shah
>>
>sql

logs - configuration option changed

Using SS2000. I often see a line in the logs that says "configuration opton 'allow updates' changed from 0 to 1. Run the RECONFIGURE s Error 15457, serverity 0, State 1"
Another entry will change the option from 1 to 0. What's going on? Is a dts package doing this as part of a normal run?
Thanks,
Dan D.
That is usually associated with someone using EM to view certain options.
It is nothing to be concerned with.
Andrew J. Kelly SQL MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FAD761D0-4D1B-4EB4-BF9E-18155CC0ED9C@.microsoft.com...
> Using SS2000. I often see a line in the logs that says "configuration
opton 'allow updates' changed from 0 to 1. Run the RECONFIGURE s Error
15457, serverity 0, State 1"
> Another entry will change the option from 1 to 0. What's going on? Is a
dts package doing this as part of a normal run?
> Thanks,
> --
> Dan D.
|||No one claims to have been on the system at that time (1 AM local time). Could some system process make the changes?
Thanks,
Dan D.
"Andrew J. Kelly" wrote:

> That is usually associated with someone using EM to view certain options.
> It is nothing to be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:FAD761D0-4D1B-4EB4-BF9E-18155CC0ED9C@.microsoft.com...
> opton 'allow updates' changed from 0 to 1. Run the RECONFIGURE s Error
> 15457, serverity 0, State 1"
> dts package doing this as part of a normal run?
>
>
|||Do you have a maintenance plan set up and if so does it have the option to
fix minor errors? This may cause an entry like this but I am not 100 %
sure. Are there any scheduled jobs that collect information from the server
with say sp_dboption?
Andrew J. Kelly SQL MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:95E34CD1-BDFA-423B-B05B-C319F7E8E3E3@.microsoft.com...
> No one claims to have been on the system at that time (1 AM local time).
Could some system process make the changes?[vbcol=seagreen]
> Thanks,
> --
> Dan D.
>
> "Andrew J. Kelly" wrote:
options.[vbcol=seagreen]
a[vbcol=seagreen]
|||There are no maintenance plans. There was one dts package that was running at the time of the message. I'll look through it and see what I can find.
Thanks,
Dan D.
"Andrew J. Kelly" wrote:

> Do you have a maintenance plan set up and if so does it have the option to
> fix minor errors? This may cause an entry like this but I am not 100 %
> sure. Are there any scheduled jobs that collect information from the server
> with say sp_dboption?
> --
> Andrew J. Kelly SQL MVP
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:95E34CD1-BDFA-423B-B05B-C319F7E8E3E3@.microsoft.com...
> Could some system process make the changes?
> options.
> a
>
>

logs - configuration option changed

Using SS2000. I often see a line in the logs that says "configuration opton
'allow updates' changed from 0 to 1. Run the RECONFIGURE s Error 15457, serv
erity 0, State 1"
Another entry will change the option from 1 to 0. What's going on? Is a dts
package doing this as part of a normal run?
Thanks,
--
Dan D.That is usually associated with someone using EM to view certain options.
It is nothing to be concerned with.
Andrew J. Kelly SQL MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FAD761D0-4D1B-4EB4-BF9E-18155CC0ED9C@.microsoft.com...
> Using SS2000. I often see a line in the logs that says "configuration
opton 'allow updates' changed from 0 to 1. Run the RECONFIGURE s Error
15457, serverity 0, State 1"
> Another entry will change the option from 1 to 0. What's going on? Is a
dts package doing this as part of a normal run?
> Thanks,
> --
> Dan D.|||No one claims to have been on the system at that time (1 AM local time). Cou
ld some system process make the changes?
Thanks,
--
Dan D.
"Andrew J. Kelly" wrote:

> That is usually associated with someone using EM to view certain options.
> It is nothing to be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:FAD761D0-4D1B-4EB4-BF9E-18155CC0ED9C@.microsoft.com...
> opton 'allow updates' changed from 0 to 1. Run the RECONFIGURE s Error
> 15457, serverity 0, State 1"
> dts package doing this as part of a normal run?
>
>|||Do you have a maintenance plan set up and if so does it have the option to
fix minor errors? This may cause an entry like this but I am not 100 %
sure. Are there any scheduled jobs that collect information from the server
with say sp_dboption?
Andrew J. Kelly SQL MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:95E34CD1-BDFA-423B-B05B-C319F7E8E3E3@.microsoft.com...
> No one claims to have been on the system at that time (1 AM local time).
Could some system process make the changes?[vbcol=seagreen]
> Thanks,
> --
> Dan D.
>
> "Andrew J. Kelly" wrote:
>
options.[vbcol=seagreen]
a[vbcol=seagreen]|||There are no maintenance plans. There was one dts package that was running a
t the time of the message. I'll look through it and see what I can find.
Thanks,
--
Dan D.
"Andrew J. Kelly" wrote:

> Do you have a maintenance plan set up and if so does it have the option to
> fix minor errors? This may cause an entry like this but I am not 100 %
> sure. Are there any scheduled jobs that collect information from the serv
er
> with say sp_dboption?
> --
> Andrew J. Kelly SQL MVP
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:95E34CD1-BDFA-423B-B05B-C319F7E8E3E3@.microsoft.com...
> Could some system process make the changes?
> options.
> a
>
>

Logs

Hi
I have a database In SQL server and I want to keep a log of all the
activity’s that that the end user did I want to know who did and what he
did and when he did what is the bet way to keep track on that. In my previews
application I made a logs table with fields tableid, tablename, userid
,action, datetime and then always when insert update or delete in table we
save it also to logs, is this idea good for SQL server or there is a better
way to do it with SQL server archiving
Read about blackbox in Books Online and see if thats what you were looking
for.
Or use profiler.
Thanks,
Sree
"hershel" wrote:

> Hi
> I have a database In SQL server and I want to keep a log of all the
> activity’s that that the end user did I want to know who did and what he
> did and when he did what is the bet way to keep track on that. In my previews
> application I made a logs table with fields tableid, tablename, userid
> ,action, datetime and then always when insert update or delete in table we
> save it also to logs, is this idea good for SQL server or there is a better
> way to do it with SQL server archiving
>

Logs

I can't get a 7.0 log to shrink. Can't I get it to shrink
if I do the following?
BACKUP LOG DBNAME WITH TRUNCATE_ONLY
Also: where does this backup go - into computer oblivion?That backup doesn't go anywhere. The log records that will be removed goes
into the "trash can". Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"CLM" <anonymous@.discussions.microsoft.com> wrote in message
news:ea7501c3f0c7$ccc226e0$a401280a@.phx.gbl...
> I can't get a 7.0 log to shrink. Can't I get it to shrink
> if I do the following?
> BACKUP LOG DBNAME WITH TRUNCATE_ONLY
> Also: where does this backup go - into computer oblivion?|||That's what I thought but wanted to confirm.
And thx for the articles...

>--Original Message--
>That backup doesn't go anywhere. The log records that
will be removed goes
>into the "trash can". Check out below KB articles:
>INF: How to Shrink the SQL Server 7.0 Transaction Log
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;256650
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>Log File Grows too big
>http://www.support.microsoft.com/?id=317375
>Log file filling up
>http://www.support.microsoft.com/?id=110139
>Considerations for Autogrow and AutoShrink
>http://www.support.microsoft.com/?id=315512
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
>
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"CLM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ea7501c3f0c7$ccc226e0$a401280a@.phx.gbl...
shrink
oblivion?
>
>.
>

Logs

Hi
I have a database In SQL server and I want to keep a log of all the
activity’s that that the end user did I want to know who did and what he
did and when he did what is the bet way to keep track on that. In my preview
s
application I made a logs table with fields tableid, tablename, userid
,action, datetime and then always when insert update or delete in table we
save it also to logs, is this idea good for SQL server or there is a better
way to do it with SQL server archivingRead about blackbox in Books Online and see if thats what you were looking
for.
Or use profiler.
Thanks,
Sree
"hershel" wrote:

> Hi
> I have a database In SQL server and I want to keep a log of all the
> activity’s that that the end user did I want to know who did and what
he
> did and when he did what is the bet way to keep track on that. In my previ
ews
> application I made a logs table with fields tableid, tablename, userid
> ,action, datetime and then always when insert update or delete in table we
> save it also to logs, is this idea good for SQL server or there is a bette
r
> way to do it with SQL server archiving
>sql

Logs

Hi
I have a database In SQL server and I want to keep a log of all the
activityâ's that that the end user did I want to know who did and what he
did and when he did what is the bet way to keep track on that. In my previews
application I made a logs table with fields tableid, tablename, userid
,action, datetime and then always when insert update or delete in table we
save it also to logs, is this idea good for SQL server or there is a better
way to do it with SQL server archivingRead about blackbox in Books Online and see if thats what you were looking
for.
Or use profiler.
Thanks,
Sree
"hershel" wrote:
> Hi
> I have a database In SQL server and I want to keep a log of all the
> activityâ's that that the end user did I want to know who did and what he
> did and when he did what is the bet way to keep track on that. In my previews
> application I made a logs table with fields tableid, tablename, userid
> ,action, datetime and then always when insert update or delete in table we
> save it also to logs, is this idea good for SQL server or there is a better
> way to do it with SQL server archiving
>

Logs

I can't get a 7.0 log to shrink. Can't I get it to shrink
if I do the following?
BACKUP LOG DBNAME WITH TRUNCATE_ONLY
Also: where does this backup go - into computer oblivion?That backup doesn't go anywhere. The log records that will be removed goes
into the "trash can". Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"CLM" <anonymous@.discussions.microsoft.com> wrote in message
news:ea7501c3f0c7$ccc226e0$a401280a@.phx.gbl...
> I can't get a 7.0 log to shrink. Can't I get it to shrink
> if I do the following?
> BACKUP LOG DBNAME WITH TRUNCATE_ONLY
> Also: where does this backup go - into computer oblivion?|||That's what I thought but wanted to confirm.
And thx for the articles...
>--Original Message--
>That backup doesn't go anywhere. The log records that
will be removed goes
>into the "trash can". Check out below KB articles:
>INF: How to Shrink the SQL Server 7.0 Transaction Log
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;256650
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>Log File Grows too big
>http://www.support.microsoft.com/?id=317375
>Log file filling up
>http://www.support.microsoft.com/?id=110139
>Considerations for Autogrow and AutoShrink
>http://www.support.microsoft.com/?id=315512
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
>
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"CLM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ea7501c3f0c7$ccc226e0$a401280a@.phx.gbl...
>> I can't get a 7.0 log to shrink. Can't I get it to
shrink
>> if I do the following?
>> BACKUP LOG DBNAME WITH TRUNCATE_ONLY
>> Also: where does this backup go - into computer
oblivion?
>
>.
>

LOGS

hello, i get this error
Microsoft OLE DB Provider for SQL Server error '80040e14'
The log file for database 'autosmotos' is full. Back up the transaction log
for the database to free up some log space.
So i try to delete the LOF file with this command:
ALTER DATABASE [autosmotos]
REMOVE FILE [autosmotos_Log]
GO
But i get
The primary data or log file cannot be removed from a database
Q: how i can i set off this option or how can i empty the log file ?
Gerard.hi Gérard,
I'll do a shrink over that .LDF file or in the case that you would have
enough free space, you could make another LOG file in the same volume.
A question, what recovery model own your database? If your database contains
only 'development data' or 'test data' a good idea is have as simple model.
Current location: Alicante (ES)
"Gérard Leclercq" wrote:

> hello, i get this error
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> The log file for database 'autosmotos' is full. Back up the transaction lo
g
> for the database to free up some log space.
>
> So i try to delete the LOF file with this command:
> ALTER DATABASE [autosmotos]
> REMOVE FILE [autosmotos_Log]
> GO
>
> But i get
> The primary data or log file cannot be removed from a database
>
> Q: how i can i set off this option or how can i empty the log file ?
>
> Gerard.
>
>
>|||underprocessable|||BACKUP LOG autosmotos WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(autosmotos_LOG,1)
GO
You may then want to issue the following command :-
ALTER DATABASE autosmotos SET RECOVERY SIMPLE
But i would suggest reading up on this 1st
HTH. Ryan
"Grard Leclercq" <gl@.pasdemail.fr> wrote in message
news:MQ8Uf.330505$cT4.10333346@.phobos.telenet-ops.be...
>I work in hard conditions. The db is on a internetserver. I don't have
>access to the Enterprise so i have to ask, and ask, and ask my provider but
>i get no help. I found a trial version of a on-line manager. In attach some
>settings. Maybe this help.
> Is there an instruction to delete or schrink the log file or to turn it
> off ?
> "Enric" <vtam13@.terra.es.(donotspam)> schreef in bericht
> news:9033071E-3A41-42C8-85B2-A35FE6919F70@.microsoft.com...
>
>|||Thank you, the log is empty now(i suppose because the error is not longer
there)
gerard
"Ryan" <Ryan_Waight@.nospam.hotmail.com> schreef in bericht
news:uLJ7qSZTGHA.6084@.TK2MSFTNGP14.phx.gbl...
> BACKUP LOG autosmotos WITH TRUNCATE_ONLY
> GO
> DBCC SHRINKFILE(autosmotos_LOG,1)
> GO
> You may then want to issue the following command :-
> ALTER DATABASE autosmotos SET RECOVERY SIMPLE
> But i would suggest reading up on this 1st
> --
> HTH. Ryan
>
> "Grard Leclercq" <gl@.pasdemail.fr> wrote in message
> news:MQ8Uf.330505$cT4.10333346@.phobos.telenet-ops.be...
>