putting multiple xml files data into database in a single transaction

First of all i do not know whether this is the right form to ask the question

Let me describe the scenario iam using

Iam generating xml files at a particular place and sending them to a server

xml1|------->dataset1---------->adapter1.update(dataset1)

xml2|-------->dataset2---------->adapter2.update(dataset2)

xml3|-------->dataset3---------->adapter3.update(dataset3)

all thethree updates should happen in onlyone transaction if any one of the update fails then the transaction should rollback

can anyone tell me a way to do it

i am desperately in search of any ways to do it can anybody help please

If the 3 SqlDataAdapters are using the same connection, maybe you can try to embed the updates in a single transaction opened on the connection:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConn"].ToString()))
{

conn.open;

//build your SqlDataAdapters on the same conneciton

SqlTransaction st;
st=conn.BeginTransaction("tran1");
adapter1.update(dataset1);

adapter2.update(dataset2);

adapter3.update(dataset3);

st.Commit();
}

|||

ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized

This is the error iam getting bcoz iam using the command builder to update the table using dataset cant we use command builder and transaction at the same time

or we should use only sqlcommand to the method

can anybody tell me the solution cant we use transaction and adapter and commandbuilder at the same time

[:'(]

Putting DDL statemnets (CREATE SCHEMA), in a transaction

Are T-SQL DDL Statements allowed within a transaction in SQL Server 2005?

Hi,

I'm working with some folks on a VS add-in that maps conceptual data models to physical DBMS implementations. Currently, the only target that doesn't generate a schema creation script inside a transaction is SQL Server 2005. Being a development project, having the CREATE SCHEMA script in a transaction would be useful, to avoid the need to clean out the parts of the DDL script that did run, and just start with a new iteration. When I asked why, they thought that SQL Server didn't allow DDL statements to run inside a transaction. Searching this, I could only find references that suggested you shouldn't (for performance reasons - but these are not an issue in this case).

The rules for this are likely in Books online, etc..., but searches I tried gave gave too many references, or too few.

If you know the answer or the exact reference, please pass that along. Also, if you know of an alternative way of removing a schema from a DB, without having to delete all the individual items within that schema first, please post that as well.

Thanks BRN..

Hi,

Here is some good info concerning your issue: http://www.sybase.com/detail?id=42077.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi and thanks for the quick response - I'll check it out. BRN..

|||

Hi,

Took a look at the SYBASE ref. That talks about MS having determined table create, alter, drop, not being allowed in a transaction due to 1) not wanting the DB structure altered 2) Performance.

As Mentioned, performance not an issue in this case. Also, I was hoping to find a MS document that stated their restrictions, and if they could be over-ridden, even if not recommended.

Still, more info than I had before, so thanks again. BRN..|||

Brian,

Not sure why you are consulting sybase documentation for SQL2005 issue.

The create schema statement can be run in a user transaction. You should be able to find most of the information @. http://msdn2.microsoft.com/en-us/library/ms189462.aspx

On the second issue, I assume you are asking if there is a way to drop a schema along with all objects contained in it in one shot. Unfortunately, this is not possible right now. You need to drop or move objects out of the schema before dropping it. We are considering this feature for a future release.

|||

Sameer,

Thanks for the link and the answer - I'll check out the link, and pass that along to the development team. The SYBASE reference was from an earlier reply, and the content was generic RDBMS, pointing out some preferences by specific vendors.

The reasons requiring schema objects to be dropped before dropping the schema are pretty easy to figure out - when a DB is populated. If there was a way to allow dropping a schema in one shot during the development phase, that would be helpful.

There might be reasons to allow dropping complete schemas in a production DB too; but that would be very dependant on how schemas (in the 2005 namespace seperate from ownership form), were utilized in the DB design. I'm looking at some of this in the project I mentioned.

Thanks again for the replies. BRN..

Push vs. Pull Transaction Replication

Q: Push vs. Pull Transactional Replication
We are currently utilizing a push transactional
replication of a subset of the tables in a production
database involving 3 machines; the publisher which is our
production db machine with 4 hyperthreaded processors, 8
GB ram, a distribution db machine with 1 processor, 2 GB
ram, and a subscriber db machine with 2 hyperthreaded
processors, 6 GB ram to which this table subset is
replicated. All machines are SQL EE with SP3. The
subscriber is used for complex searches and has 6 indexed
views resident on it. All replication agents are set to
run continuously.
The Distribution Agent profiles have been left at the
defaults except for QueryTimeout, which has been set to
3600.
Because we cannot simulate this environment for testing,
I would appreciate hearing from anyone who has experience
with a push vs. pull subscription from a performance
standpoint. Thanks very much for your input.
pull offloads the distribution agent to the subscriber. Hence your
performance on your publisher is better.
The problem is that then you no longer have a central point of
administration - in other words you manage your publications and snapshot
agent on your publisher, but your distribution agent on your subscriber.
I can't quantify exactly what performance increase you will have on your
publisher as it is largely a function of load and hardware on your publisher
and subscriber.
You will have to test to see what performance increase you get. You can get
an idea of what it will be by stopping your log reader and distribution
agent on your publisher.
"fundster" <anonymous@.discussions.microsoft.com> wrote in message
news:98dc01c433b2$498477f0$a301280a@.phx.gbl...
> Q: Push vs. Pull Transactional Replication
> We are currently utilizing a push transactional
> replication of a subset of the tables in a production
> database involving 3 machines; the publisher which is our
> production db machine with 4 hyperthreaded processors, 8
> GB ram, a distribution db machine with 1 processor, 2 GB
> ram, and a subscriber db machine with 2 hyperthreaded
> processors, 6 GB ram to which this table subset is
> replicated. All machines are SQL EE with SP3. The
> subscriber is used for complex searches and has 6 indexed
> views resident on it. All replication agents are set to
> run continuously.
> The Distribution Agent profiles have been left at the
> defaults except for QueryTimeout, which has been set to
> 3600.
> Because we cannot simulate this environment for testing,
> I would appreciate hearing from anyone who has experience
> with a push vs. pull subscription from a performance
> standpoint. Thanks very much for your input.
>
|||I would think that you would not need to stop the log reader. Even with a
pull subscription the log reader is still running on the publisher.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||that's a very good point Rand, I missed that.
I was trying to get a baseline "without" replication, but to get a
representative baseline with pull subscriptions you really should have the
log reader agent running.
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:nxrqWlENEHA.3780@.cpmsftngxa10.phx.gbl...
> I would think that you would not need to stop the log reader. Even with a
> pull subscription the log reader is still running on the publisher.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
sql

Purging Transaction Log

I am trying to purge the logfile (ldf) for our database.
I thought I had done it in the past through Enterprise
Manager by clicking the database, selecting
AllTasks/Shrink Database and then reducing the size of the
transaction log to its minimum. The logfile is currently
150 MB. I specified to shrink it to the minimum (8 MB).
It told me that it succeeded but the size is not changing.
Is this the appropriate way to reduce the log size?
THanks,
-RobYou must back up the log if you are in Full Recovery Mode prior to
shrinking...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Rob C" <robnocspam@.ctnosoftwarespam.com> wrote in message
news:0a4701c3bf26$c8d635d0$a401280a@.phx.gbl...
> I am trying to purge the logfile (ldf) for our database.
> I thought I had done it in the past through Enterprise
> Manager by clicking the database, selecting
> AllTasks/Shrink Database and then reducing the size of the
> transaction log to its minimum. The logfile is currently
> 150 MB. I specified to shrink it to the minimum (8 MB).
> It told me that it succeeded but the size is not changing.
> Is this the appropriate way to reduce the log size?
> THanks,
> -Rob|||Hi Rob,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Our MVP, Wayne had provided a good explanation of how to shrink the log
file.
I just want to add some more information of this part.
When SQL Server finishes backing up the transaction log, it automatically
truncates the inactive portion of the transaction log. This inactive
portion contains completed transactions and so is no longer used during the
recovery process. Conversely, the active portion of the transaction log
contains transactions that are still running and have not yet completed.
SQL Server reuses this truncated, inactive space in the transaction log
instead of allowing the transaction log to continue to grow and use more
space. That is why although you use Enterprise Manager to set the log file
size, but it remains the same. You shoud backup it first to let it become
inactive.
Although the transaction log may be truncated manually, it is strongly
recommended that you do not do this, as it breaks the log backup chain.
Until a full database backup is created, the database is not protected from
media failure. Use manual log truncation only in very special
circumstances, and create a full database backup as soon as practical.
The ending point of the inactive portion of the transaction log, and hence
the truncation point, is the earliest of the following events:
1)The most recent checkpoint.
2)The start of the oldest active transaction, which is a transaction that
has not yet been committed or rolled back.
This represents the earliest point to which SQL Server would have to roll
back transactions during recovery.
3)The start of the oldest transaction that involves objects published for
replication whose changes have not been replicated yet. This represents the
earliest point that SQL Server still has to replicate.
You can refer to SQL Server Books on Line (BOL) for more information for
active portion and inactive portion of log files and some DBCC commands to
carry out log truncation by searching :
"Truncating the Transaction Log" in the BOL
I hope will be helpful to resolve your question. If you still have any
questions, please feel free to post any new message here, I am ready to
offer you further help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you both fo you replies.
I was able to successfully shrink the log once I ran the
backup process on the transaction logfile.
I also appreciated the explanation behind it.
Regards,
-Rob
>--Original Message--
>Hi Rob,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>Our MVP, Wayne had provided a good explanation of how to
shrink the log
>file.
>I just want to add some more information of this part.
>When SQL Server finishes backing up the transaction log,
it automatically
>truncates the inactive portion of the transaction log.
This inactive
>portion contains completed transactions and so is no
longer used during the
>recovery process. Conversely, the active portion of the
transaction log
>contains transactions that are still running and have not
yet completed.
>SQL Server reuses this truncated, inactive space in the
transaction log
>instead of allowing the transaction log to continue to
grow and use more
>space. That is why although you use Enterprise Manager to
set the log file
>size, but it remains the same. You shoud backup it first
to let it become
>inactive.
>Although the transaction log may be truncated manually,
it is strongly
>recommended that you do not do this, as it breaks the log
backup chain.
>Until a full database backup is created, the database is
not protected from
>media failure. Use manual log truncation only in very
special
>circumstances, and create a full database backup as soon
as practical.
>The ending point of the inactive portion of the
transaction log, and hence
>the truncation point, is the earliest of the following
events:
>1)The most recent checkpoint.
>2)The start of the oldest active transaction, which is a
transaction that
>has not yet been committed or rolled back.
>This represents the earliest point to which SQL Server
would have to roll
>back transactions during recovery.
>3)The start of the oldest transaction that involves
objects published for
>replication whose changes have not been replicated yet.
This represents the
>earliest point that SQL Server still has to replicate.
>You can refer to SQL Server Books on Line (BOL) for
more information for
>active portion and inactive portion of log files and some
DBCC commands to
>carry out log truncation by searching :
>"Truncating the Transaction Log" in the BOL
>I hope will be helpful to resolve your question. If you
still have any
>questions, please feel free to post any new message here,
I am ready to
>offer you further help.
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||How does one manually truncate a transaction log?|||Hi Gayle,
Thank you for using MSDN Newsgroup!
You can manually truncate the trasaction log. Please refer to the following
article for detailed information:
http://support.microsoft.com/?id=272318
If you still have question, please post new message here and I am ready to
help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for replying! The drive where the transaction log resides -
drive L - ran out of space (the transaction log is 97GB). This has,
naturally, made it to where we cannot access our databases through
Enterprise Manager. When we try to open Query Analyzer, it times out
but then still shows a query window with the Master database selected,
but we cannot change from the Master to any of our application's
databases. We tried to do the following via Query Analyzer anyway:
backup database (to the S drive, which had space)
backup log with truncate_only
dbcc shrinkfile on the log file
backup database
We let it run ("Processing query batch") for an hour before we stopped
it. We assume that it didn't work because it was trying to write to
the transaction log but couldn't. Is that correct? Does SQL write
administrative queries (like backup statements) to the transaction
log? We know now what we need to do to prevent the transaction log
from outgrowing its space and we're going to try to set up an alert so
that SQL will page us when the log gets too big.
I guess what I'd like to know is, should our preventive measures fail
and the log's drive fill up again, what is the best approach to take?
Thank you for any info you can share.sql

Purging SQL Server Log Files

I am currently maintaining a database at work that somehow has a
transaction log of 20 GB. Is there an easy way to purge this log
file?
Thanks,
JeffCertainly :-) Have a look at
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q256650
To understand why it has grown check out
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q110139
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317375
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff" <jeffpuro@.yahoo.com> wrote in message
news:7851a310.0401121127.2d279759@.posting.google.com...
> I am currently maintaining a database at work that somehow has a
> transaction log of 20 GB. Is there an easy way to purge this log
> file?
> Thanks,
> Jeff|||Hi,
This is because of the recovary model you selected, if the database is
defined as "FULL" recovary, you may need to perform a Transaction log backup
using (Backup Log) command, otherwise the Transaction log file records and
wont clear the Logs and it grows to higher limit incase you are not limiting
the transaction log size.
In your case do,
1. Change the recovary model to Simple
2. Run "Backup log dbname with no_log"
3. Use dbcc shrinkfile command to shrink the log file
4. Change the recovary model to "FULL"
5. Schedule a Backup log command atleast twice a day .
Thanks
Hari
MCDBA
"Jeff" <jeffpuro@.yahoo.com> wrote in message
news:7851a310.0401121127.2d279759@.posting.google.com...
> I am currently maintaining a database at work that somehow has a
> transaction log of 20 GB. Is there an easy way to purge this log
> file?
> Thanks,
> Jeff

Purge Transaction Log File

My database file is growing and growing and growing. This is good...
The bad news, at least for storage limitations, is the Transaction log file
is growing significantly faster. At the present time my data file is around
150 MB and the Transaction log file is just under 10GB......
Do I need to let the Transaction log file grow or is there a way to prevent
the file from getting to large? Any suggestions on solving this problem are
greatly appreciated.
WByou have to back up the transaction log to keep it from growing for ever.
OR Put your Database in "Simple Recovery Mode"
Greg Jackson
PDX, OR|||Ta add to Greg's response, the proper database recovery model depends on
your recovery requirements. If your plan is to simply restore from your
last backup, you should use the SIMPLE model. Committed data will be
removed from the log automatically to keep your log size reasonable.
If you need to further minimize potential data loss, you need to use the
FULL or BULK_LOGGED recovery model and schedule regular log backups between
database backups. The log backups will provide the means for forward
recovery following a database restore and also remove committed data from
the log.
You can read more about recovery models in the Books Online.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>|||I had the same problem.
I must have my database in FULL recovery model, and my log file growes
larger and larger.
Except normaly daily backup (and hourly incremantaly too )
Ones a week I make the following step:
1) Make backup of my database.
2) than do:
backup log mydatabasename with truncate_only
dbcc shrinkdatabase('mydatabasename')
3) Make again backup of my shrink database
On course, I put this steps in DTS package and put in Agent.
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>|||When you do a log backup, the log file(s) will be emptied. It is not normal to do BACKUP LOG WITH
TRUNCATE_ONLY in such scenario (this will break your chain of log backups!!!) nor to shrink file log
file (http://www.karaszi.com/SQLServer/info_dont_shrink.asp). You should investigate *why* the log
keep growing despite your regular log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
>I had the same problem.
> I must have my database in FULL recovery model, and my log file growes larger and larger.
> Except normaly daily backup (and hourly incremantaly too )
> Ones a week I make the following step:
> 1) Make backup of my database.
> 2) than do:
> backup log mydatabasename with truncate_only
> dbcc shrinkdatabase('mydatabasename')
> 3) Make again backup of my shrink database
> On course, I put this steps in DTS package and put in Agent.
>
> "WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>> My database file is growing and growing and growing. This is good...
>> The bad news, at least for storage limitations, is the Transaction log file
>> is growing significantly faster. At the present time my data file is around
>> 150 MB and the Transaction log file is just under 10GB......
>> Do I need to let the Transaction log file grow or is there a way to prevent
>> the file from getting to large? Any suggestions on solving this problem are
>> greatly appreciated.
>> WB
>>
>|||Thank you for all the input. I was able to backup the log file and then
shrink it down to under 100MB. The problem now is I can't set the max file
size to say 2GB because the file size is still 9GB. How can I prevent the
file from growing beyond 2GB'
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
file
> is growing significantly faster. At the present time my data file is
around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
prevent
> the file from getting to large? Any suggestions on solving this problem
are
> greatly appreciated.
> WB
>|||"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
>I had the same problem.
> I must have my database in FULL recovery model, and my log file growes
> larger and larger.
> Except normaly daily backup (and hourly incremantaly too )
> Ones a week I make the following step:
> 1) Make backup of my database.
> 2) than do:
> backup log mydatabasename with truncate_only
> dbcc shrinkdatabase('mydatabasename')
> 3) Make again backup of my shrink database
> On course, I put this steps in DTS package and put in Agent.
Why do you have Full recovery model if you're truncating the logs? It
doesn't make any sense. Put the recovery model to simple and you will gest
the same result, but the cheaper way.
Best regards
Wojtek|||I don't understand. You say you shrunk it down to 100MB. Then you say the file is still 9GB?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
> Thank you for all the input. I was able to backup the log file and then
> shrink it down to under 100MB. The problem now is I can't set the max file
> size to say 2GB because the file size is still 9GB. How can I prevent the
> file from growing beyond 2GB'
>
> "WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>> My database file is growing and growing and growing. This is good...
>> The bad news, at least for storage limitations, is the Transaction log
> file
>> is growing significantly faster. At the present time my data file is
> around
>> 150 MB and the Transaction log file is just under 10GB......
>> Do I need to let the Transaction log file grow or is there a way to
> prevent
>> the file from getting to large? Any suggestions on solving this problem
> are
>> greatly appreciated.
>> WB
>>
>|||Yes, There are two listings for the transaction file.
1. Current size = ~ 9GB
2. Space used = ~100MB
I am referencing the details listed under Shrink Database -> files
WB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> I don't understand. You say you shrunk it down to 100MB. Then you say the
file is still 9GB?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
> > Thank you for all the input. I was able to backup the log file and then
> > shrink it down to under 100MB. The problem now is I can't set the max
file
> > size to say 2GB because the file size is still 9GB. How can I prevent
the
> > file from growing beyond 2GB'
> >
> >
> > "WB" <none> wrote in message
news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> >> My database file is growing and growing and growing. This is good...
> >> The bad news, at least for storage limitations, is the Transaction log
> > file
> >> is growing significantly faster. At the present time my data file is
> > around
> >> 150 MB and the Transaction log file is just under 10GB......
> >>
> >> Do I need to let the Transaction log file grow or is there a way to
> > prevent
> >> the file from getting to large? Any suggestions on solving this
problem
> > are
> >> greatly appreciated.
> >>
> >> WB
> >>
> >>
> >
> >|||I see. I don't use the EM GUI much, but I assume it means that the file size is 9GB and it is only
used 100MB. You need to shrink the file using DBCC SHRINKFILE. It might require a few BACKUP,
SHRINK, BACKUO, SHRINK iteration where you monitor the status between using DBCC LOGINFO. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some more info.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"WB" <none> wrote in message news:ek1%23BsGbFHA.3144@.TK2MSFTNGP14.phx.gbl...
> Yes, There are two listings for the transaction file.
> 1. Current size = ~ 9GB
> 2. Space used = ~100MB
> I am referencing the details listed under Shrink Database -> files
> WB
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
>> I don't understand. You say you shrunk it down to 100MB. Then you say the
> file is still 9GB?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
>> > Thank you for all the input. I was able to backup the log file and then
>> > shrink it down to under 100MB. The problem now is I can't set the max
> file
>> > size to say 2GB because the file size is still 9GB. How can I prevent
> the
>> > file from growing beyond 2GB'
>> >
>> >
>> > "WB" <none> wrote in message
> news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>> >> My database file is growing and growing and growing. This is good...
>> >> The bad news, at least for storage limitations, is the Transaction log
>> > file
>> >> is growing significantly faster. At the present time my data file is
>> > around
>> >> 150 MB and the Transaction log file is just under 10GB......
>> >>
>> >> Do I need to let the Transaction log file grow or is there a way to
>> > prevent
>> >> the file from getting to large? Any suggestions on solving this
> problem
>> > are
>> >> greatly appreciated.
>> >>
>> >> WB
>> >>
>> >>
>> >
>> >
>|||ok, thanks for staying with the thread.
WB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23ONr3GbFHA.616@.TK2MSFTNGP12.phx.gbl...
> I see. I don't use the EM GUI much, but I assume it means that the file
size is 9GB and it is only
> used 100MB. You need to shrink the file using DBCC SHRINKFILE. It might
require a few BACKUP,
> SHRINK, BACKUO, SHRINK iteration where you monitor the status between
using DBCC LOGINFO. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some more info.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "WB" <none> wrote in message
news:ek1%23BsGbFHA.3144@.TK2MSFTNGP14.phx.gbl...
> > Yes, There are two listings for the transaction file.
> >
> > 1. Current size = ~ 9GB
> > 2. Space used = ~100MB
> >
> > I am referencing the details listed under Shrink Database -> files
> >
> > WB
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> >> I don't understand. You say you shrunk it down to 100MB. Then you say
the
> > file is still 9GB?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "WB" <none> wrote in message
news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
> >> > Thank you for all the input. I was able to backup the log file and
then
> >> > shrink it down to under 100MB. The problem now is I can't set the
max
> > file
> >> > size to say 2GB because the file size is still 9GB. How can I
prevent
> > the
> >> > file from growing beyond 2GB'
> >> >
> >> >
> >> > "WB" <none> wrote in message
> > news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> >> >> My database file is growing and growing and growing. This is
good...
> >> >> The bad news, at least for storage limitations, is the Transaction
log
> >> > file
> >> >> is growing significantly faster. At the present time my data file
is
> >> > around
> >> >> 150 MB and the Transaction log file is just under 10GB......
> >> >>
> >> >> Do I need to let the Transaction log file grow or is there a way to
> >> > prevent
> >> >> the file from getting to large? Any suggestions on solving this
> > problem
> >> > are
> >> >> greatly appreciated.
> >> >>
> >> >> WB
> >> >>
> >> >>
> >> >
> >> >
> >
> >
>|||And rather than setting a max size for the file, then remember to put it to
SIMPLE recovery - that will keep the file in a decent size but it have the
space to grow if it needs it for some reason.
/Steen
WB wrote:
> ok, thanks for staying with the thread.
> WB
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:e%23ONr3GbFHA.616@.TK2MSFTNGP12.phx.gbl...
>> I see. I don't use the EM GUI much, but I assume it means that the
>> file size is 9GB and it is only used 100MB. You need to shrink the
>> file using DBCC SHRINKFILE. It might require a few BACKUP, SHRINK,
>> BACKUO, SHRINK iteration where you monitor the status between using
>> DBCC LOGINFO. See
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some more
>> info.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "WB" <none> wrote in message
> news:ek1%23BsGbFHA.3144@.TK2MSFTNGP14.phx.gbl...
>> Yes, There are two listings for the transaction file.
>> 1. Current size = ~ 9GB
>> 2. Space used = ~100MB
>> I am referencing the details listed under Shrink Database -> files
>> WB
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
>> I don't understand. You say you shrunk it down to 100MB. Then you
>> say the file is still 9GB?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "WB" <none> wrote in message
> news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
>> Thank you for all the input. I was able to backup the log file
>> and then shrink it down to under 100MB. The problem now is I
>> can't set the max file size to say 2GB because the file size is
>> still 9GB. How can I prevent the file from growing beyond 2GB'
>>
>> "WB" <none> wrote in message
>> news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>> My database file is growing and growing and growing. This is
>> good... The bad news, at least for storage limitations, is the
>> Transaction log file is growing significantly faster. At the
>> present time my data file is around 150 MB and the Transaction
>> log file is just under 10GB......
>> Do I need to let the Transaction log file grow or is there a way
>> to prevent the file from getting to large? Any suggestions on
>> solving this problem are greatly appreciated.
>> WB|||A terrible solution and an indication that you have not yet discovered the
true purpose of the transaction log.
You have much left to learn, Grasshopper.
Anthony Thomas
"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
I had the same problem.
I must have my database in FULL recovery model, and my log file growes
larger and larger.
Except normaly daily backup (and hourly incremantaly too )
Ones a week I make the following step:
1) Make backup of my database.
2) than do:
backup log mydatabasename with truncate_only
dbcc shrinkdatabase('mydatabasename')
3) Make again backup of my shrink database
On course, I put this steps in DTS package and put in Agent.
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>

Purge Transaction Log File

My database file is growing and growing and growing. This is good...
The bad news, at least for storage limitations, is the Transaction log file
is growing significantly faster. At the present time my data file is around
150 MB and the Transaction log file is just under 10GB......
Do I need to let the Transaction log file grow or is there a way to prevent
the file from getting to large? Any suggestions on solving this problem are
greatly appreciated.
WByou have to back up the transaction log to keep it from growing for ever.
OR Put your Database in "Simple Recovery Mode"
Greg Jackson
PDX, OR|||Ta add to Greg's response, the proper database recovery model depends on
your recovery requirements. If your plan is to simply restore from your
last backup, you should use the SIMPLE model. Committed data will be
removed from the log automatically to keep your log size reasonable.
If you need to further minimize potential data loss, you need to use the
FULL or BULK_LOGGED recovery model and schedule regular log backups between
database backups. The log backups will provide the means for forward
recovery following a database restore and also remove committed data from
the log.
You can read more about recovery models in the Books Online.
Hope this helps.
Dan Guzman
SQL Server MVP
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>|||I had the same problem.
I must have my database in FULL recovery model, and my log file growes
larger and larger.
Except normaly daily backup (and hourly incremantaly too )
Ones a week I make the following step:
1) Make backup of my database.
2) than do:
backup log mydatabasename with truncate_only
dbcc shrinkdatabase('mydatabasename')
3) Make again backup of my shrink database
On course, I put this steps in DTS package and put in Agent.
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>|||When you do a log backup, the log file(s) will be emptied. It is not normal
to do BACKUP LOG WITH
TRUNCATE_ONLY in such scenario (this will break your chain of log backups!!!
) nor to shrink file log
file (http://www.karaszi.com/SQLServer/info_dont_shrink.asp). You should inv
estigate *why* the log
keep growing despite your regular log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
>I had the same problem.
> I must have my database in FULL recovery model, and my log file growes lar
ger and larger.
> Except normaly daily backup (and hourly incremantaly too )
> Ones a week I make the following step:
> 1) Make backup of my database.
> 2) than do:
> backup log mydatabasename with truncate_only
> dbcc shrinkdatabase('mydatabasename')
> 3) Make again backup of my shrink database
> On course, I put this steps in DTS package and put in Agent.
>
> "WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>|||Thank you for all the input. I was able to backup the log file and then
shrink it down to under 100MB. The problem now is I can't set the max file
size to say 2GB because the file size is still 9GB. How can I prevent the
file from growing beyond 2GB'
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
file
> is growing significantly faster. At the present time my data file is
around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
prevent
> the file from getting to large? Any suggestions on solving this problem
are
> greatly appreciated.
> WB
>|||"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
>I had the same problem.
> I must have my database in FULL recovery model, and my log file growes
> larger and larger.
> Except normaly daily backup (and hourly incremantaly too )
> Ones a week I make the following step:
> 1) Make backup of my database.
> 2) than do:
> backup log mydatabasename with truncate_only
> dbcc shrinkdatabase('mydatabasename')
> 3) Make again backup of my shrink database
> On course, I put this steps in DTS package and put in Agent.
Why do you have Full recovery model if you're truncating the logs? It
doesn't make any sense. Put the recovery model to simple and you will gest
the same result, but the cheaper way.
Best regards
Wojtek|||I don't understand. You say you shrunk it down to 100MB. Then you say the fi
le is still 9GB?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
> Thank you for all the input. I was able to backup the log file and then
> shrink it down to under 100MB. The problem now is I can't set the max fil
e
> size to say 2GB because the file size is still 9GB. How can I prevent the
> file from growing beyond 2GB'
>
> "WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> file
> around
> prevent
> are
>|||Yes, There are two listings for the transaction file.
1. Current size = ~ 9GB
2. Space used = ~100MB
I am referencing the details listed under Shrink Database -> files
WB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> I don't understand. You say you shrunk it down to 100MB. Then you say the
file is still 9GB?[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
file[vbcol=seagreen]
the[vbcol=seagreen]
news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
problem[vbcol=seagreen]|||I see. I don't use the EM GUI much, but I assume it means that the file size
is 9GB and it is only
used 100MB. You need to shrink the file using DBCC SHRINKFILE. It might requ
ire a few BACKUP,
SHRINK, BACKUO, SHRINK iteration where you monitor the status between using
DBCC LOGINFO. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some more info.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"WB" <none> wrote in message news:ek1%23BsGbFHA.3144@.TK2MSFTNGP14.phx.gbl...
> Yes, There are two listings for the transaction file.
> 1. Current size = ~ 9GB
> 2. Space used = ~100MB
> I am referencing the details listed under Shrink Database -> files
> WB
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> file is still 9GB?
> file
> the
> news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> problem
>

Purge Transaction Log File

My database file is growing and growing and growing. This is good...
The bad news, at least for storage limitations, is the Transaction log file
is growing significantly faster. At the present time my data file is around
150 MB and the Transaction log file is just under 10GB......
Do I need to let the Transaction log file grow or is there a way to prevent
the file from getting to large? Any suggestions on solving this problem are
greatly appreciated.
WB
you have to back up the transaction log to keep it from growing for ever.
OR Put your Database in "Simple Recovery Mode"
Greg Jackson
PDX, OR
|||Ta add to Greg's response, the proper database recovery model depends on
your recovery requirements. If your plan is to simply restore from your
last backup, you should use the SIMPLE model. Committed data will be
removed from the log automatically to keep your log size reasonable.
If you need to further minimize potential data loss, you need to use the
FULL or BULK_LOGGED recovery model and schedule regular log backups between
database backups. The log backups will provide the means for forward
recovery following a database restore and also remove committed data from
the log.
You can read more about recovery models in the Books Online.
Hope this helps.
Dan Guzman
SQL Server MVP
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>
|||I had the same problem.
I must have my database in FULL recovery model, and my log file growes
larger and larger.
Except normaly daily backup (and hourly incremantaly too )
Ones a week I make the following step:
1) Make backup of my database.
2) than do:
backup log mydatabasename with truncate_only
dbcc shrinkdatabase('mydatabasename')
3) Make again backup of my shrink database
On course, I put this steps in DTS package and put in Agent.
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>
|||When you do a log backup, the log file(s) will be emptied. It is not normal to do BACKUP LOG WITH
TRUNCATE_ONLY in such scenario (this will break your chain of log backups!!!) nor to shrink file log
file (http://www.karaszi.com/SQLServer/info_dont_shrink.asp). You should investigate *why* the log
keep growing despite your regular log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
>I had the same problem.
> I must have my database in FULL recovery model, and my log file growes larger and larger.
> Except normaly daily backup (and hourly incremantaly too )
> Ones a week I make the following step:
> 1) Make backup of my database.
> 2) than do:
> backup log mydatabasename with truncate_only
> dbcc shrinkdatabase('mydatabasename')
> 3) Make again backup of my shrink database
> On course, I put this steps in DTS package and put in Agent.
>
> "WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>
|||Thank you for all the input. I was able to backup the log file and then
shrink it down to under 100MB. The problem now is I can't set the max file
size to say 2GB because the file size is still 9GB. How can I prevent the
file from growing beyond 2GB?
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
file
> is growing significantly faster. At the present time my data file is
around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
prevent
> the file from getting to large? Any suggestions on solving this problem
are
> greatly appreciated.
> WB
>
|||"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
>I had the same problem.
> I must have my database in FULL recovery model, and my log file growes
> larger and larger.
> Except normaly daily backup (and hourly incremantaly too )
> Ones a week I make the following step:
> 1) Make backup of my database.
> 2) than do:
> backup log mydatabasename with truncate_only
> dbcc shrinkdatabase('mydatabasename')
> 3) Make again backup of my shrink database
> On course, I put this steps in DTS package and put in Agent.
Why do you have Full recovery model if you're truncating the logs? It
doesn't make any sense. Put the recovery model to simple and you will gest
the same result, but the cheaper way.
Best regards
Wojtek
|||I don't understand. You say you shrunk it down to 100MB. Then you say the file is still 9GB?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
> Thank you for all the input. I was able to backup the log file and then
> shrink it down to under 100MB. The problem now is I can't set the max file
> size to say 2GB because the file size is still 9GB. How can I prevent the
> file from growing beyond 2GB?
>
> "WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> file
> around
> prevent
> are
>
|||Yes, There are two listings for the transaction file.
1. Current size = ~ 9GB
2. Space used = ~100MB
I am referencing the details listed under Shrink Database -> files
WB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> I don't understand. You say you shrunk it down to 100MB. Then you say the
file is still 9GB?[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
file[vbcol=seagreen]
the[vbcol=seagreen]
news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
problem[vbcol=seagreen]
|||I see. I don't use the EM GUI much, but I assume it means that the file size is 9GB and it is only
used 100MB. You need to shrink the file using DBCC SHRINKFILE. It might require a few BACKUP,
SHRINK, BACKUO, SHRINK iteration where you monitor the status between using DBCC LOGINFO. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some more info.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"WB" <none> wrote in message news:ek1%23BsGbFHA.3144@.TK2MSFTNGP14.phx.gbl...
> Yes, There are two listings for the transaction file.
> 1. Current size = ~ 9GB
> 2. Space used = ~100MB
> I am referencing the details listed under Shrink Database -> files
> WB
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> file is still 9GB?
> file
> the
> news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> problem
>

Purge a Transaction Log?

Hi Guys,
I have MS SQL Server 2000 which, among others, has a database on it used for
McAffee protection pilot (A policy driven AV managment system). Over the las
t
month or so the Tlog ldf has exploded in size to over 10GB (the mdf is only
25MB). How can I purge this log with minimal disruption to the database.
I tried shrinking the database and restoring from a backup but this seems to
have had no effect on the ldf file. I sopke to someone who sugested exportin
g
the data, objects and procedures etc. but during a test run this came back
with errors and also it seems quite long winded.
I don't have that much background in SQLserver but I know in Exchange - the
backup API will purge the tlogs once a backup has been successful - can
something similar be configured?. I noticed the maximum log file size
settings - which I will set once I have purged the ldf file.
Any ideas?
Thanks,
Niall.Hi
Check out
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"Niall" wrote:

> Hi Guys,
> I have MS SQL Server 2000 which, among others, has a database on it used f
or
> McAffee protection pilot (A policy driven AV managment system). Over the l
ast
> month or so the Tlog ldf has exploded in size to over 10GB (the mdf is onl
y
> 25MB). How can I purge this log with minimal disruption to the database.
> I tried shrinking the database and restoring from a backup but this seems
to
> have had no effect on the ldf file. I sopke to someone who sugested export
ing
> the data, objects and procedures etc. but during a test run this came back
> with errors and also it seems quite long winded.
> I don't have that much background in SQLserver but I know in Exchange - th
e
> backup API will purge the tlogs once a backup has been successful - can
> something similar be configured?. I noticed the maximum log file size
> settings - which I will set once I have purged the ldf file.
> Any ideas?
> Thanks,
> Niall.sql

Purge a Transaction Log?

Hi Guys,
I have MS SQL Server 2000 which, among others, has a database on it used for
McAffee protection pilot (A policy driven AV managment system). Over the last
month or so the Tlog ldf has exploded in size to over 10GB (the mdf is only
25MB). How can I purge this log with minimal disruption to the database.
I tried shrinking the database and restoring from a backup but this seems to
have had no effect on the ldf file. I sopke to someone who sugested exporting
the data, objects and procedures etc. but during a test run this came back
with errors and also it seems quite long winded.
I don't have that much background in SQLserver but I know in Exchange - the
backup API will purge the tlogs once a backup has been successful - can
something similar be configured?. I noticed the maximum log file size
settings - which I will set once I have purged the ldf file.
Any ideas?
Thanks,
Niall.Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
John
"Niall" wrote:
> Hi Guys,
> I have MS SQL Server 2000 which, among others, has a database on it used for
> McAffee protection pilot (A policy driven AV managment system). Over the last
> month or so the Tlog ldf has exploded in size to over 10GB (the mdf is only
> 25MB). How can I purge this log with minimal disruption to the database.
> I tried shrinking the database and restoring from a backup but this seems to
> have had no effect on the ldf file. I sopke to someone who sugested exporting
> the data, objects and procedures etc. but during a test run this came back
> with errors and also it seems quite long winded.
> I don't have that much background in SQLserver but I know in Exchange - the
> backup API will purge the tlogs once a backup has been successful - can
> something similar be configured?. I noticed the maximum log file size
> settings - which I will set once I have purged the ldf file.
> Any ideas?
> Thanks,
> Niall.

Purge a Transaction Log?

Hi Guys,
I have MS SQL Server 2000 which, among others, has a database on it used for
McAffee protection pilot (A policy driven AV managment system). Over the last
month or so the Tlog ldf has exploded in size to over 10GB (the mdf is only
25MB). How can I purge this log with minimal disruption to the database.
I tried shrinking the database and restoring from a backup but this seems to
have had no effect on the ldf file. I sopke to someone who sugested exporting
the data, objects and procedures etc. but during a test run this came back
with errors and also it seems quite long winded.
I don't have that much background in SQLserver but I know in Exchange - the
backup API will purge the tlogs once a backup has been successful - can
something similar be configured?. I noticed the maximum log file size
settings - which I will set once I have purged the ldf file.
Any ideas?
Thanks,
Niall.
Hi
Check out
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
John
"Niall" wrote:

> Hi Guys,
> I have MS SQL Server 2000 which, among others, has a database on it used for
> McAffee protection pilot (A policy driven AV managment system). Over the last
> month or so the Tlog ldf has exploded in size to over 10GB (the mdf is only
> 25MB). How can I purge this log with minimal disruption to the database.
> I tried shrinking the database and restoring from a backup but this seems to
> have had no effect on the ldf file. I sopke to someone who sugested exporting
> the data, objects and procedures etc. but during a test run this came back
> with errors and also it seems quite long winded.
> I don't have that much background in SQLserver but I know in Exchange - the
> backup API will purge the tlogs once a backup has been successful - can
> something similar be configured?. I noticed the maximum log file size
> settings - which I will set once I have purged the ldf file.
> Any ideas?
> Thanks,
> Niall.

purge .ldf file

I'm assuming that the .ldf file houses all the transaction logs, well mine is getting quite large (like 6 times the size of my .mdf) and I'm wondering if there is a way to purge logs back to a specific date. Can anyone give me some direction? ThanksYou can't clear the log selectively... Its unfortunately an "all or nothing" proposition. Check out the How To (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_4915.asp) from BOL.

-PatP|||Your log files grows so large because there are action queries against your database that modify large volumes of data. Assuming your Recovery Mode is Simple, the smaller your transactions in terms of volume of data affected, the less chances are that your log would grow. If the recovery mode is Full or Bulk-Logged, the growth also depends on the frequency of transaction log or full database backups.

pull or push

which is better?
push or pull?
this is for transaction replication.
Kevin,
it depends - pull will offload some of the processing so using pull or
anonymous subscriptions can increase performance by moving Distribution
Agent processing from the Distributor to Subscribers.
On the other hand, push will centralize the agents for management purposes.
From BOL:
Use pull subscriptions when:
a.. Administration of the subscription will take place at the Subscriber.
b.. The publication has a large number of Subscribers (for example,
Subscribers using the Internet), and when it would be too resource-intensive
to run all the agents at one site or all at the Distributor.
c.. Subscribers are autonomous, disconnected, and/or mobile. Subscribers
will determine when they will connect to the Publisher/Distributor and
synchronize changes.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Pubs Transaction log

I can't backup the transaction log for the pubs database
on any systems I have. I even made some bogus transactions
to have something in the log. Is backing up the Pubs T/log
not allowed? Thanks in advance for your reply.--Denhi Den,
Its because recovery model of pubs database must be simple. change it to
bulk-logged/full and then try taking backup.
- Vishal

Pubs Transaction log

I can't backup the transaction log for the pubs database
on any systems I have. I even made some bogus transactions
to have something in the log. Is backing up the Pubs T/log
not allowed? Thanks in advance for your reply.--Denhi Den,
Its because recovery model of pubs database must be simple. change it to
bulk-logged/full and then try taking backup.
--
- Vishal

Publication has expired

I have setup transaction replication using seperate server for distribution
box.
Server A - Publisher (Win2003, SQL2000 SP818)
Server B - Distributor (Win2003, SQL2000 SP818)
Server C - Subscriber1 (Win2003 and SQL2000 SP818)
Server D - Subscriber2 (Win2003 and SQL2000 SP818)
Server E - Subscriber3 (Win2000, SQL2000 SP818)
Server A has 3 publishers and each publisher has more than 10 articles, one
of them we use it for monitoring replication by update a datetime column from
sql job.
The trans. replication are running fine execpt the subscriber3. This
subsciber always get sthe subscription expires errors, but the monitor
article does get updated. And the wired thing is the replication monitor on
the distribution server shows that the agent is in suspect and no reponse
within 10 min after each snapshot/initation.
I did query the MSDistribution_History and there is only one row which the
date I ran the snspshot. All the retention and history period are default.
Does anyone knows that it is Win2003 to Win2000 issue or something else.
Thanks in advance
This guy expired prematurely because your history retention is less than
your publication retention. Bump this setting up to your publication
retention, and be highly proactive about sensing when this guy has dropped
off the network for more than 1 day.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"kc" <kc@.discussions.microsoft.com> wrote in message
news:7B0A51D7-F9BD-4281-A7F3-E8144164F02A@.microsoft.com...
> I have setup transaction replication using seperate server for
distribution
> box.
> Server A - Publisher (Win2003, SQL2000 SP818)
> Server B - Distributor (Win2003, SQL2000 SP818)
> Server C - Subscriber1 (Win2003 and SQL2000 SP818)
> Server D - Subscriber2 (Win2003 and SQL2000 SP818)
> Server E - Subscriber3 (Win2000, SQL2000 SP818)
> Server A has 3 publishers and each publisher has more than 10 articles,
one
> of them we use it for monitoring replication by update a datetime column
from
> sql job.
> The trans. replication are running fine execpt the subscriber3. This
> subsciber always get sthe subscription expires errors, but the monitor
> article does get updated. And the wired thing is the replication monitor
on
> the distribution server shows that the agent is in suspect and no reponse
> within 10 min after each snapshot/initation.
> I did query the MSDistribution_History and there is only one row which the
> date I ran the snspshot. All the retention and history period are
default.
> Does anyone knows that it is Win2003 to Win2000 issue or something else.
> Thanks in advance
|||In my case, the monitor article in the subscriber had same time as the
publisher when it was mark for expiration. Therefore it is not a network
issue.
By default, the hsitory rentention is 48 hrs and transaction rentention is
72 hrs, so I don't understand what do you mean that I need to bump the
setting up?
Thanks
KC
"Hilary Cotter" wrote:

> This guy expired prematurely because your history retention is less than
> your publication retention. Bump this setting up to your publication
> retention, and be highly proactive about sensing when this guy has dropped
> off the network for more than 1 day.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "kc" <kc@.discussions.microsoft.com> wrote in message
> news:7B0A51D7-F9BD-4281-A7F3-E8144164F02A@.microsoft.com...
> distribution
> one
> from
> on
> default.
>
>