question part 2

sorry.. should have added this to help...
Oldest active transaction:
> SPID (server process ID) : 144
> UID (user ID) : 6
> Name : implicit_transaction
> LSN : (106228:47115:1)
> Start time : Jan 28 2004 11:55:08:840AM
> DBCC execution completed. If DBCC printed error
messages, contact your system administrator.>--Original Message--
>sorry.. should have added this to help...
>Oldest active transaction:
>> SPID (server process ID) : 144
>> UID (user ID) : 6
>> Name : implicit_transaction
>> LSN : (106228:47115:1)
>> Start time : Jan 28 2004 11:55:08:840AM
>> DBCC execution completed. If DBCC printed error
>messages, contact your system administrator.
>.
>
P.S can anyone tell me how to see if its turn on or not...
sql server wiese... i now "set" will turn it off and on
but how can i check its current status.. if i turn it off
will it be for just THAT database or all of the server

question on Transactions and Commit

If you start a transaction with BEGIN TRAN and then, for some reason this is
interrupted and not recovered from (in other words, no ROLLBACK or no
COMMIT), what happens? Is there a time after which this will timeout? If
so, where is it set and what is the deafault?
I have a situation where i believe this happened, and now queries to the
table simply hang, do not return and do not give any error message. More
specifically if the query contains a WHERE clause. For instance, the query
SELECT * from Table returns quickly (it is a small table). But SELECT *
from Table WHERE column='xxx' is the one that hangs.
Any advice is appreciated. Thank you.I would expect it to actually be the other way around. But in any case an
open tran will stay that way for as long as the connection that started it
is valid. Your application that begins the tran should always handle
situations sucha s that. You can check to see if there is an open tran in
any db by running DBCC OPENTRAN(). If you find one that is hanging around
long after it should be you can kill that SPID and the tran will be rolled
back automatically. You can also use sp_who2 to see if you are begin blocked
and by who. sp_lock will also show any currently held locks.
Andrew J. Kelly SQL MVP
"THOMAS CONLON" <someone@.verizon.net> wrote in message
news:bJlgg.2906$td6.2754@.trnddc02...
> If you start a transaction with BEGIN TRAN and then, for some reason this
> is interrupted and not recovered from (in other words, no ROLLBACK or no
> COMMIT), what happens? Is there a time after which this will timeout? If
> so, where is it set and what is the deafault?
> I have a situation where i believe this happened, and now queries to the
> table simply hang, do not return and do not give any error message. More
> specifically if the query contains a WHERE clause. For instance, the
> query SELECT * from Table returns quickly (it is a small table). But
> SELECT * from Table WHERE column='xxx' is the one that hangs.
> Any advice is appreciated. Thank you.
>|||THOMAS CONLON (someone@.verizon.net) writes:
> If you start a transaction with BEGIN TRAN and then, for some reason
> this is interrupted and not recovered from (in other words, no ROLLBACK
> or no COMMIT), what happens? Is there a time after which this will
> timeout? If so, where is it set and what is the deafault?
There is no timeout. If there is never any COMMIT or ROLLBACK explicitly,
there will be a ROLLBACK when the connection is physically disconnected.
Physical disconnection happens when the application exits. It can also
happen if the application code issues a disconnect. However, usually
connection is in force in the client API. This causes the physical
connection to be retained for 60 seconds, unless it can be reused in this
time frame. If a connection is reused from the pool, the rollback will
occur on reuse.

> I have a situation where i believe this happened, and now queries to the
> table simply hang, do not return and do not give any error message.
> More specifically if the query contains a WHERE clause. For instance,
> the query SELECT * from Table returns quickly (it is a small table).
> But SELECT * from Table WHERE column='xxx' is the one that hangs.
Note that if you get a "Timeout expired" in your application, you must
submit a IF @.@.trancount > 0 ROLLBACK TRANSACTION". The timeout is
something the client API raises, and SQL Server does not know what it
means, and will keep the transaction alive.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Question on Transaction Log Backups

New to Sql Server, running SQL Server 2000.

Our transaction log file backups occasionally fail as the size of the
transaction log gets really huge. We'd like to schedule additional
transaction log backups. Does that require an exclusive on the database
or can the db be used during a transaction log backup?

Also, does switching to a bulk mode recovery model before a bulk
operation then switching back to full recovery mode after present any
issues?

Thanks in advance.DataPro wrote:
> New to Sql Server, running SQL Server 2000.
> Our transaction log file backups occasionally fail as the size of the
> transaction log gets really huge. We'd like to schedule additional
> transaction log backups. Does that require an exclusive on the database
> or can the db be used during a transaction log backup?
> Also, does switching to a bulk mode recovery model before a bulk
> operation then switching back to full recovery mode after present any
> issues?

It doesn't make sense to switch back and forth. You can permanently
switch to bulk logged because different logging for bulk operations is
all this recovery model is about.

See Microsoft's documentation for more details:

http://technet2.microsoft.com/Windo...3.mspx?mfr=true

Kind regards

robert|||DataPro (datapro01@.yahoo.com) writes:
> Our transaction log file backups occasionally fail as the size of the
> transaction log gets really huge. We'd like to schedule additional
> transaction log backups. Does that require an exclusive on the database
> or can the db be used during a transaction log backup?

Translog backups can be taken without conflict for other users. Else
it would be a quite meaningless operations. Some sites back up their
transaction log every 15 minutes or so.

> Also, does switching to a bulk mode recovery model before a bulk
> operation then switching back to full recovery mode after present any
> issues?

In difference to Robert, I think this makes sense. I believe we do this
in the maintenance procedure that we ship with our product. We switch
to bulk-logged before starting reindexing of tables, and then switch
back to full when it's done.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
>> Also, does switching to a bulk mode recovery model before a bulk
>> operation then switching back to full recovery mode after present any
>> issues?
> In difference to Robert, I think this makes sense. I believe we do this
> in the maintenance procedure that we ship with our product. We switch
> to bulk-logged before starting reindexing of tables, and then switch
> back to full when it's done.

Hm, I can see that it makes sense if you do this for *some* bulk
operations only. My understanding of the question was whether it makes
sense to do it always for bulk operations. And in that case I don't
think it does: if I switch from "full" to "bulk logged" before any bulk
op and switch back afterwards I get the same behavior as if "bulk
logged" was used all the time.

Kind regards

robert|||Many thanks for the thoughts. Being brand new to SQL Server (coming
over from DB2) have much to read and learn.

Here is my situation. An application upgrade occurred over the weekend
which made several database changes. Now the active log is at 12 Gigs.
Taking a full database backup and a log backup is not truncating the
log. Issuing a backup log truncate only has no effect.

I ussed a dbcc loginfo against the database and it returned 308 rows. I
am assuming that these 'virtual logs' represent active non-committed
transactions.

Is that right?

We are talking to the application vendor asking him why this occured.
In the meantime,,,,

Any thoughts on how I can reduce the size of this log? From what I've
read about shrinkfile it doesn't appear to be relevant here.

Any thoughts or suggestions would be appreciated.|||DataPro (datapro01@.yahoo.com) writes:
> Here is my situation. An application upgrade occurred over the weekend
> which made several database changes. Now the active log is at 12 Gigs.
> Taking a full database backup and a log backup is not truncating the
> log. Issuing a backup log truncate only has no effect.

It has, but that will not shrink the log file. (Unless autoshrink is in
effect, but you don't want that.) Only give you a lot of free space in
the file.

Note that TRUNCATE_ONLY invalidates the transaction log, so you need to
take a full backup after this operation.

> Any thoughts on how I can reduce the size of this log? From what I've
> read about shrinkfile it doesn't appear to be relevant here.

DBCC SHRINKFILE is indeed your guy. Be sure to set a target size when
you run it, so you don't make the log too small. In that case, it will
have to grow again, and that will only take performance from the system.
And the file may become fragmented on disk.

For things like an application upgrade, the best strategy may be to set
the recovery mode to simple, and set back to full recovery when it the
upgrade is done. Again, you need to take a full backup in this situation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Here is what the vendor is recommending:

1. Back up db and log via Windows Explorer or normal db backups
2. Stop services (application...EAS)
3. Disconnect/detach database
4. Delete log via Windows Explorer
5. Reconnect/reattach database....log will dynamically be allocated

Does this make sense?
They also want autoshrink turned on but its my understanding some SQL
DBA's don't want that. Your thoughts?

Thanks in advance.|||Well SQL Server definetely seems to be a different animal.

Here are some questions a newguy like myself would have about the
logging.

The documentation says that once a backup log truncate only is done, a
full backup should be taken as the recovery ability of the system has
been degraded. Why is that, if that command merely archives committed
transactions?

A SELECT @.@.TRANCOUNT yields a count of zero.
dbcc opentran(eas) says zero also.
dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
that they are open transactions. Why don't they show up in trancount
then?

If a log backup supposedly makes the log 'reusable' then why would a 12
gig log grow another Gig right within a few hours of the backup with
minimal database use?
Wouldn't most of that space be reusable.

Please straighten me out on my thinking :)

Thanks|||We shrink the transaction log every two hours without any noticeable
performance hit, but our total database size is less than 500 MB. I
have played with the simple logging one time and had problems with it,
so we have been sticking with our two hour shrink process (added to our
two hour backup process) which works fine for now.

DBCC SHRINKFILE(DBName_log, 1)
BACKUP LOG DBName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBName_log, 1)
go|||pb648174 (google@.webpaul.net) writes:
> We shrink the transaction log every two hours without any noticeable
> performance hit, but our total database size is less than 500 MB. I
> have played with the simple logging one time and had problems with it,
> so we have been sticking with our two hour shrink process (added to our
> two hour backup process) which works fine for now.
> DBCC SHRINKFILE(DBName_log, 1)
> BACKUP LOG DBName WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(DBName_log, 1)

This appears completely pointless to me. If you have full recovery,
you are losing all the benefits by truncating the transaction log
every two hours. If you have simple recovery, there should not be
any need to shrink the file, unless there has been any extraordinary
actions going on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||DataPro (datapro01@.yahoo.com) writes:
> Here is what the vendor is recommending:
> 1. Back up db and log via Windows Explorer or normal db backups
> 2. Stop services (application...EAS)
> 3. Disconnect/detach database
> 4. Delete log via Windows Explorer
> 5. Reconnect/reattach database....log will dynamically be allocated
> Does this make sense?

No! Never delete a log file, unless you are told so by a Microsoft
support professional. There are so many people who have lost their
database, because they deleted the log. I guess it's safe if you
detach first, but I would like to take a gamble with a production
database.

> They also want autoshrink turned on but its my understanding some SQL
> DBA's don't want that. Your thoughts?

Shrinking a database file only make sense if there has been some
exceptional activity. Running a schema upgrade certainly counts as one.
But doing it as a matter of routine, or with autoshrink, only serves
to degrade the performance of the application in three different
ways:

1) The shrinking takes resources.
2) The autogrow takes resources.
3) The log file becomes fragmented on disk.

Autogrow is a mixed blessing. Up to SQL Server 6.5 there was no autogrow,
which meant that if the data or log segment was filled up, it was a
hard stop. Autogrow that was added in SQL 7 avoids these situations,
as long as the disk has space. However, for the DBA there is not
really any difference: he should should still monitor database size
amd increase the size in due time. A 10% autogrow in a 200 GB database
is very painfully noticeable fot the users.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||DataPro (datapro01@.yahoo.com) writes:
> Well SQL Server definetely seems to be a different animal.
> Here are some questions a newguy like myself would have about the
> logging.
> The documentation says that once a backup log truncate only is done, a
> full backup should be taken as the recovery ability of the system has
> been degraded. Why is that, if that command merely archives committed
> transactions?

Up-to-the point recovery. You take a full backup at midnight, then
you backup the transaction log once an hour. at 10:23 you run an
UPDATE statement, but forget the WHERE clause. At this point you
can back up the transaction log.

You can the restore the full backup and reapply the transaction log
dumps but stop at 10:22, so you can recover the lost data. If you
somewhere along the line has backed up the transaction log with
NO_LOG or TRUNCATE_ONLY, the log chain is broken, and it's not
possible to continue to apply logs.

> A SELECT @.@.TRANCOUNT yields a count of zero.
> dbcc opentran(eas) says zero also.
> dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
> that they are open transactions. Why don't they show up in trancount
> then?
> If a log backup supposedly makes the log 'reusable' then why would a 12
> gig log grow another Gig right within a few hours of the backup with
> minimal database use?
> Wouldn't most of that space be reusable.

I'm not really sure what you are talking about here. If the log file
grew, apparently there was some action going on in the database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your replies.
In regards to:

"> A SELECT @.@.TRANCOUNT yields a count of zero.
> dbcc opentran(eas) says zero also.
> dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
> that they are open transactions. Why don't they show up in trancount
> then?

> If a log backup supposedly makes the log 'reusable' then why would a 12
> gig log grow another Gig right within a few hours of the backup with
> minimal database use?
> Wouldn't most of that space be reusable.

I'm not really sure what you are talking about here. If the log file
grew, apparently there was some action going on in the database. "

1.) What I'm not clear on is this...a dbcc loginfo yeilds 308 rows
marked with a 2.
Are those 'active' uncommitted transactions? What do those rows
represent?

2.) If backing up a log makes the space within a log file 'reusable'
why did the log grow in size if the space in the log was made reusable?
Wouldn't the minimal activity I referenced use that reclaimed space
within the log instead of making it bigger?

3) And finally what is the relationship between the results of a dbcc
loginfo command, a dbcc opentran and a A SELECT @.@.TRANCOUNT|||DataPro (datapro01@.yahoo.com) writes:
> 1.) What I'm not clear on is this...a dbcc loginfo yeilds 308 rows
> marked with a 2.
> Are those 'active' uncommitted transactions? What do those rows
> represent?

DBCC LOGINFO is not documented, but I learn a little from SQL Server
MVP Tibor Karasiz's article on shrinking:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp. (I recommend
that you read this article in whole, although I summarise some details
below.)

Each row represents a VLF, a virtual log file, and 2 indicates that
that VLF is in use. You cannot shrink the log past the last active
VLF. And if all VLF's have a 2, then all VLFs are in use, and obviously
nothing can be shrunk.

You should backup the transaction log, either to disk or just toss it
with TRUNCATE_ONLY.

> 2.) If backing up a log makes the space within a log file 'reusable'
> why did the log grow in size if the space in the log was made reusable?

As I discussed above, the log is apparently not reusable.

> 3) And finally what is the relationship between the results of a dbcc
> loginfo command, a dbcc opentran and a A SELECT @.@.TRANCOUNT

@.@.trancount only reflects the state of the current process.

DBCC OPENTRAN reveals if there are any open transactions. The log
cannot be truncated past the oldest active transaction.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks much|||This has been a really usefull thread. I have the same problem, and
debated about starting new thread or continue this one:
DBCC LOGINFO shows my last log entry is in use (as indicated by STATUS
of 2)
DBCC OPENTRAN indicates no active open transactions
My file is 89% empty, Ive backed up the log files repeatedly (first one
was large 40+ GB, now they are < 500K)
I've tried taking the DB offline and back online. The last log entry
still shows in use and I cant shrink.
I read the article, which helps identify why I cant shrink (log file in
use) but now I'm stumped.
TIA
Rob|||rcamarda (robc390@.hotmail.com) writes:
> This has been a really usefull thread. I have the same problem, and
> debated about starting new thread or continue this one:
> DBCC LOGINFO shows my last log entry is in use (as indicated by STATUS
> of 2)
> DBCC OPENTRAN indicates no active open transactions
> My file is 89% empty, Ive backed up the log files repeatedly (first one
> was large 40+ GB, now they are < 500K)
> I've tried taking the DB offline and back online. The last log entry
> still shows in use and I cant shrink.
> I read the article, which helps identify why I cant shrink (log file in
> use) but now I'm stumped.

Is the last VLF the only that has status = 2? Or are there several?

Did you try running BACKUP TRANSACTION WITH NO_LOG? (Since you lose the
log chain, you need a full backup as well.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Question on shrinkingTransaction Log file

Hi All,
For some unknown reason one of our SQL Server 2000
database transaction log file grew to 25 GB before we
noticed it.
Thank god we noticed it before we ran out of space
completely.
Currently, we are left with 1 GB of free space and
the Data file size of the database is about 1.5 GB.
I looked over the help documentation on Books Online
trying to figure out how to reduce the size of the
transaction log file.
I read that i need to truncate the transaction log file
before I could shrink the size of the transaction log
file.
I read that the "active" portion of the transaction log
file can never be truncated.
How would I figure out what portion of this 25 GB
transaction log file is active?
What if all of it is, then how can I shrink the size of
the file?
At this point I MUST shrink the transaction log file,
and later try to trace what caused it to grow so big.
Please advise the best way I can shrink the size of the
transaction log file to the initial size we originally
had allocated, 100 MB.
Thank you,
Mitra
First, make sure that you are either backing up your database logs, or have the database set to simple mode.
You can most easily check to see the status of the database logs in enterprise manager by highlighting the database name and then selecting taskpad under the view menu. This will provide you with a graphical view of the size and of how much of the log is
full.
If it is full, switch to simple recovery under Database Properties in SQL Enterprise manager, and then choose All Tasks -> shrink database checking the shrink file option with the correct file chosen from the drop down menu on the top. (you may have to d
o this twice)
|||Hi,
After Transaction log backup the physical file will not shrink automatically
if the "Auto shrink" option is enabled. Please do not
enable this option because this will affect your OLTP processng because the
Auto shrink will take more I/O.
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
MCDBA
"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
news:130cf01c4437a$fc073b60$a001280a@.phx.gbl...
> Hi All,
> For some unknown reason one of our SQL Server 2000
> database transaction log file grew to 25 GB before we
> noticed it.
> Thank god we noticed it before we ran out of space
> completely.
> Currently, we are left with 1 GB of free space and
> the Data file size of the database is about 1.5 GB.
> I looked over the help documentation on Books Online
> trying to figure out how to reduce the size of the
> transaction log file.
> I read that i need to truncate the transaction log file
> before I could shrink the size of the transaction log
> file.
> I read that the "active" portion of the transaction log
> file can never be truncated.
> How would I figure out what portion of this 25 GB
> transaction log file is active?
> What if all of it is, then how can I shrink the size of
> the file?
> At this point I MUST shrink the transaction log file,
> and later try to trace what caused it to grow so big.
> Please advise the best way I can shrink the size of the
> transaction log file to the initial size we originally
> had allocated, 100 MB.
> Thank you,
> Mitra
>
|||Hi,
I am not clear about what would happen when i try to back
up a transaction log that is 25GB and I've got only 1 GB
free disk space.
Are you saying that if i run the statement you wrote:
backup log <dbname> with truncate_only
I should be okay and don't need to worry about how much
free space is available?
Thank you,
Mitra

>--Original Message--
>Hi,
>After Transaction log backup the physical file will not
shrink automatically
>if the "Auto shrink" option is enabled. Please do not
>enable this option because this will affect your OLTP
processng because the
>Auto shrink will take more I/O.
>How to reduce the Transaction log size
>1. Backup the transaction log (Use BACKUP Log dbname to
>disk='c:\backup\dbname.tr1' or use enterprise manager)
>or (Since you done have the hard disk space truncate the
trasaction log)
>backup log <dbname> with truncate_only
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>Have a look into the below link.
>Shrinking the Transaction Log in SQL Server 2000 with
DBCC
>http://www.support.microsoft.com/?id=272318
>
>Thanks
>Hari
>MCDBA
>
>
>
>"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
>news:130cf01c4437a$fc073b60$a001280a@.phx.gbl...
>
>.
>

Question on shrinkingTransaction Log file

Hi All,
For some unknown reason one of our SQL Server 2000
database transaction log file grew to 25 GB before we
noticed it.
Thank god we noticed it before we ran out of space
completely.
Currently, we are left with 1 GB of free space and
the Data file size of the database is about 1.5 GB.
I looked over the help documentation on Books Online
trying to figure out how to reduce the size of the
transaction log file.
I read that i need to truncate the transaction log file
before I could shrink the size of the transaction log
file.
I read that the "active" portion of the transaction log
file can never be truncated.
How would I figure out what portion of this 25 GB
transaction log file is active?
What if all of it is, then how can I shrink the size of
the file?
At this point I MUST shrink the transaction log file,
and later try to trace what caused it to grow so big.
Please advise the best way I can shrink the size of the
transaction log file to the initial size we originally
had allocated, 100 MB.
Thank you,
MitraFirst, make sure that you are either backing up your database logs, or have
the database set to simple mode.
You can most easily check to see the status of the database logs in enterpri
se manager by highlighting the database name and then selecting taskpad unde
r the view menu. This will provide you with a graphical view of the size an
d of how much of the log is
full.
If it is full, switch to simple recovery under Database Properties in SQL En
terprise manager, and then choose All Tasks -> shrink database checking the
shrink file option with the correct file chosen from the drop down menu on t
he top. (you may have to d
o this twice)|||Hi,
After Transaction log backup the physical file will not shrink automatically
if the "Auto shrink" option is enabled. Please do not
enable this option because this will affect your OLTP processng because the
Auto shrink will take more I/O.
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
MCDBA
"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
news:130cf01c4437a$fc073b60$a001280a@.phx
.gbl...
> Hi All,
> For some unknown reason one of our SQL Server 2000
> database transaction log file grew to 25 GB before we
> noticed it.
> Thank god we noticed it before we ran out of space
> completely.
> Currently, we are left with 1 GB of free space and
> the Data file size of the database is about 1.5 GB.
> I looked over the help documentation on Books Online
> trying to figure out how to reduce the size of the
> transaction log file.
> I read that i need to truncate the transaction log file
> before I could shrink the size of the transaction log
> file.
> I read that the "active" portion of the transaction log
> file can never be truncated.
> How would I figure out what portion of this 25 GB
> transaction log file is active?
> What if all of it is, then how can I shrink the size of
> the file?
> At this point I MUST shrink the transaction log file,
> and later try to trace what caused it to grow so big.
> Please advise the best way I can shrink the size of the
> transaction log file to the initial size we originally
> had allocated, 100 MB.
> Thank you,
> Mitra
>|||Hi,
I am not clear about what would happen when i try to back
up a transaction log that is 25GB and I've got only 1 GB
free disk space.
Are you saying that if i run the statement you wrote:
backup log <dbname> with truncate_only
I should be okay and don't need to worry about how much
free space is available?
Thank you,
Mitra

>--Original Message--
>Hi,
>After Transaction log backup the physical file will not
shrink automatically
>if the "Auto shrink" option is enabled. Please do not
>enable this option because this will affect your OLTP
processng because the
>Auto shrink will take more I/O.
>How to reduce the Transaction log size
>1. Backup the transaction log (Use BACKUP Log dbname to
>disk='c:\backup\dbname.tr1' or use enterprise manager)
>or (Since you done have the hard disk space truncate the
trasaction log)
>backup log <dbname> with truncate_only
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>Have a look into the below link.
>Shrinking the Transaction Log in SQL Server 2000 with
DBCC
>http://www.support.microsoft.com/?id=272318
>
>Thanks
>Hari
>MCDBA
>
>
>
>"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
> news:130cf01c4437a$fc073b60$a001280a@.phx
.gbl...
>
>.
>

Question on shrinkingTransaction Log file

Hi All,
For some unknown reason one of our SQL Server 2000
database transaction log file grew to 25 GB before we
noticed it.
Thank god we noticed it before we ran out of space
completely.
Currently, we are left with 1 GB of free space and
the Data file size of the database is about 1.5 GB.
I looked over the help documentation on Books Online
trying to figure out how to reduce the size of the
transaction log file.
I read that i need to truncate the transaction log file
before I could shrink the size of the transaction log
file.
I read that the "active" portion of the transaction log
file can never be truncated.
How would I figure out what portion of this 25 GB
transaction log file is active?
What if all of it is, then how can I shrink the size of
the file?
At this point I MUST shrink the transaction log file,
and later try to trace what caused it to grow so big.
Please advise the best way I can shrink the size of the
transaction log file to the initial size we originally
had allocated, 100 MB.
Thank you,
MitraFirst, make sure that you are either backing up your database logs, or have the database set to simple mode
You can most easily check to see the status of the database logs in enterprise manager by highlighting the database name and then selecting taskpad under the view menu. This will provide you with a graphical view of the size and of how much of the log is full
If it is full, switch to simple recovery under Database Properties in SQL Enterprise manager, and then choose All Tasks -> shrink database checking the shrink file option with the correct file chosen from the drop down menu on the top. (you may have to do this twice)|||Hi,
After Transaction log backup the physical file will not shrink automatically
if the "Auto shrink" option is enabled. Please do not
enable this option because this will affect your OLTP processng because the
Auto shrink will take more I/O.
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
MCDBA
"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
news:130cf01c4437a$fc073b60$a001280a@.phx.gbl...
> Hi All,
> For some unknown reason one of our SQL Server 2000
> database transaction log file grew to 25 GB before we
> noticed it.
> Thank god we noticed it before we ran out of space
> completely.
> Currently, we are left with 1 GB of free space and
> the Data file size of the database is about 1.5 GB.
> I looked over the help documentation on Books Online
> trying to figure out how to reduce the size of the
> transaction log file.
> I read that i need to truncate the transaction log file
> before I could shrink the size of the transaction log
> file.
> I read that the "active" portion of the transaction log
> file can never be truncated.
> How would I figure out what portion of this 25 GB
> transaction log file is active?
> What if all of it is, then how can I shrink the size of
> the file?
> At this point I MUST shrink the transaction log file,
> and later try to trace what caused it to grow so big.
> Please advise the best way I can shrink the size of the
> transaction log file to the initial size we originally
> had allocated, 100 MB.
> Thank you,
> Mitra
>|||Hi,
I am not clear about what would happen when i try to back
up a transaction log that is 25GB and I've got only 1 GB
free disk space.
Are you saying that if i run the statement you wrote:
backup log <dbname> with truncate_only
I should be okay and don't need to worry about how much
free space is available?
Thank you,
Mitra
>--Original Message--
>Hi,
>After Transaction log backup the physical file will not
shrink automatically
>if the "Auto shrink" option is enabled. Please do not
>enable this option because this will affect your OLTP
processng because the
>Auto shrink will take more I/O.
>How to reduce the Transaction log size
>1. Backup the transaction log (Use BACKUP Log dbname to
>disk='c:\backup\dbname.tr1' or use enterprise manager)
>or (Since you done have the hard disk space truncate the
trasaction log)
>backup log <dbname> with truncate_only
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>Have a look into the below link.
>Shrinking the Transaction Log in SQL Server 2000 with
DBCC
>http://www.support.microsoft.com/?id=272318
>
>Thanks
>Hari
>MCDBA
>
>
>
>"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
>news:130cf01c4437a$fc073b60$a001280a@.phx.gbl...
>> Hi All,
>> For some unknown reason one of our SQL Server 2000
>> database transaction log file grew to 25 GB before we
>> noticed it.
>> Thank god we noticed it before we ran out of space
>> completely.
>> Currently, we are left with 1 GB of free space and
>> the Data file size of the database is about 1.5 GB.
>> I looked over the help documentation on Books Online
>> trying to figure out how to reduce the size of the
>> transaction log file.
>> I read that i need to truncate the transaction log file
>> before I could shrink the size of the transaction log
>> file.
>> I read that the "active" portion of the transaction log
>> file can never be truncated.
>> How would I figure out what portion of this 25 GB
>> transaction log file is active?
>> What if all of it is, then how can I shrink the size of
>> the file?
>> At this point I MUST shrink the transaction log file,
>> and later try to trace what caused it to grow so big.
>> Please advise the best way I can shrink the size of the
>> transaction log file to the initial size we originally
>> had allocated, 100 MB.
>> Thank you,
>> Mitra
>>
>
>.
>

Question on moving logs

Running SQL Server 2000. I have been asked to move the transaction logs
to another drive. I was able to do that for the user databases using
detach/attach.

However I don't see that as an option for moving the logs for the
system databases.
Is there any way I can move the system database logs? I'm guessing it
might be possible through a restore.

Any thoughts ?

Thanks in advance.

GerryShutdown the service, and change thew appropiate registry settings for
the temp and the master database. The other databases can be moved with
attach / detach.

HTH, Jens K. Suessmeyer.

--
http://www.sqlserver2005.de
--|||Jens wrote:

Quote:

Originally Posted by

Shutdown the service, and change thew appropiate registry settings for
the temp and the master database. The other databases can be moved with
attach / detach.
>
HTH, Jens K. Suessmeyer.
>
--
http://www.sqlserver2005.de
--

Thanks Jens

Also came across this

http://support.microsoft.com/kb/224071/

Question on moving logs

Running SQL Server 2000. I have been asked to move the transaction logs to another drive. I was able to do that for the user databases using detach/attach.

However I don't see that as an option for moving the logs for the system databases.
Is there any way I can move the system database logs? I'm guessing it might be possible through a restore.

Any thoughts ?

Thanks in advance.

GerryGiven the number of transactions that happen in master, model and msdb on a daily basis, I have never found it necessary to move the logs of these three databases. Tempdb on the other hand can be moved with a dirty trick. Leave the existing log with a maxsize limit of say 5MB, and create a second logfile on the new device at the actual size you need. Logfiles are used in sequence, as opposed to datafiles which are used in a more distributed way. You will still have the original disk used for 5MB of logs every once in a while, but the vast majority of the usage should be on the other drive.|||Thanks

Also came across this article

http://support.microsoft.com/kb/224071/

question on failure inside a transaction

I want to issue a transaction where whole transaction should roll back
properly. I want to be sure that anything that stops these statements
from working will rollback the transaction. Example, the table2 no
longer exists below so I get error
Invalid object name 'table2'.
How do I get sql to gracefully rollback.. It seems to ignore my if
@.@.trancount > 0 rollback statement.
begin tran
insert into table1 (x,y,z) values(1,2,3)
insert into table2 (x,y,z) values(1,2,3)
if @.@.trancount > 0 rollback
commit tranAny severe error will stop the batch and any further code will not be run.
You might be able to use SET XACT_ABORT to do what you want or you can
handle it where you made the call in the first place.You might want to have
a look at this:
http://www.sommarskog.se/error-handling-II.html
Andrew J. Kelly SQL MVP
"David" <daveygf@.yahoo.com> wrote in message
news:1113009248.856813.214350@.o13g2000cwo.googlegroups.com...
>I want to issue a transaction where whole transaction should roll back
> properly. I want to be sure that anything that stops these statements
> from working will rollback the transaction. Example, the table2 no
> longer exists below so I get error
> Invalid object name 'table2'.
> How do I get sql to gracefully rollback.. It seems to ignore my if
> @.@.trancount > 0 rollback statement.
> begin tran
> insert into table1 (x,y,z) values(1,2,3)
> insert into table2 (x,y,z) values(1,2,3)
> if @.@.trancount > 0 rollback
> commit tran
>

Question on Different Types of Backups for SQL Server 2000

Does it matter which backup method you use with SQL Server
2000, as far as SQL Server recognizing the backup, and
keeping the transaction log file at a reasonable size?
You can backup your database in Enterprise Manager using the
Backup Wizard, the Backup Database Menu Option, or through a
Maintenance Plan.
What my documentation doesn't tell me, is this: Does it
matter which backup method you use, in order for the
transaction log to remain at a reasonable size (based on the
last full backup)?
In other words, will SQL Server 2000 recognize any of the
backups as full backups, and allow the transaction log to be
shrunk, back to the point of the last full backup?
Appreciate any help/advice.
jj
p.s. If you put the DB Backup on a SCHEDULE, using either
the Wizard, or the Backup Database menu option in EM, does
this require SQL Server Agent to be running? Or do you only
need Agent to run, when backing up via a Maintenance Plan?
Just wondering if ANY scheduled backups require Agent to be
running?Hello,
To answer your question, no it does not matter.
Personally I do it though Database Maintenance Plan,
purely cos i'm a GUI kinda guy
:D
Peter
>--Original Message--
>Does it matter which backup method you use with SQL
Server
>2000, as far as SQL Server recognizing the backup, and
>keeping the transaction log file at a reasonable size?
>You can backup your database in Enterprise Manager using
the
>Backup Wizard, the Backup Database Menu Option, or
through a
> Maintenance Plan.
>What my documentation doesn't tell me, is this: Does it
>matter which backup method you use, in order for the
>transaction log to remain at a reasonable size (based on
the
>last full backup)?
>In other words, will SQL Server 2000 recognize any of the
>backups as full backups, and allow the transaction log to
be
>shrunk, back to the point of the last full backup?
>Appreciate any help/advice.
>jj
>p.s. If you put the DB Backup on a SCHEDULE, using
either
>the Wizard, or the Backup Database menu option in EM,
does
>this require SQL Server Agent to be running? Or do you
only
>need Agent to run, when backing up via a Maintenance
Plan?
>Just wondering if ANY scheduled backups require Agent to
be
>running?
>.
>|||Thanks for the info. Very helpful. The reason I asked
this, is because each backup type uses a different naming
convention. I just need to make sure that, if I do a full
DB back, using either of the 3 methods, I'll be able to do a
DB RESTORE, using any of the backup files (regardless of
their naming convention, or which method I used to backup
the DB).
Is this correct?
Sorry for this one last question. I just need to make sure,
and my documentation doesn't provide answer to this
question. Many thanks.
jj
=============Kevin Brooks wrote:
> Do FULL DATABASE BACKUPS and LOG BACKUPS. We do a full backup once a day
> and backup the logs every 5min to 1 hour, depending on which database. For
> safety we also have a job that manually checks % log used(DBCC
> SQLPERF(LOGSPACE) not agent counters) every 30mins across all databases and
> will fire off the appropiate log backup and email us if they are getting
> full. A full backup will not clear the log and log size will always vary
> depending on usage. This can all be done through the GUI, but the GUI
> stinks. It is all about coding for best performance and most control, then
> again we had to build customer log shipping because SQL logshipping is not
> stable. Remember - if your logfile grows and you need to shrink the
> physical file, you have to use DBCC SHRINKFILE. This is all assuming you
> are running in FULL RECOVERY mode. HTH.
>
> "JJay" <jjj.nospam@.mindspring.com> wrote in message
> news:D4q_a.10456$M6.888848@.newsread1.prod.itd.earthlink.net...
>>Does it matter which backup method you use with SQL Server
>>2000, as far as SQL Server recognizing the backup, and
>>keeping the transaction log file at a reasonable size?
>>You can backup your database in Enterprise Manager using the
>>Backup Wizard, the Backup Database Menu Option, or through a
>> Maintenance Plan.
>>What my documentation doesn't tell me, is this: Does it
>>matter which backup method you use, in order for the
>>transaction log to remain at a reasonable size (based on the
>>last full backup)?
>>In other words, will SQL Server 2000 recognize any of the
>>backups as full backups, and allow the transaction log to be
>>shrunk, back to the point of the last full backup?
>>Appreciate any help/advice.
>>jj
>>p.s. If you put the DB Backup on a SCHEDULE, using either
>>the Wizard, or the Backup Database menu option in EM, does
>>this require SQL Server Agent to be running? Or do you only
>>need Agent to run, when backing up via a Maintenance Plan?
>>Just wondering if ANY scheduled backups require Agent to be
>>running?
>
>

Question on client side and server side transactions

Hi All,
Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside
stored procedure, if a client has already started a transaction? The reason,
I am asking this question is that, though an error occured in a stored
procedure which was called within a transaction (on the client side), the
rest of the statements in the stored procedure were executed, which was not
desired.
I am have opened a connection and started a transaction in vb.net and called
a stored procedure. The stored procedure calls several other stored
procedures and adds/updates records in linked tables. It so happened that,
one temporary table did not exist and that particular statement failed when
I
tried to select records from the temporary table. But the following
statements were executed. Why was the transacton not being honoured? Ideally
,
when a statement failed, the transaction should be aborted; this is what I
understand.
Any suggestions?
Thanks
kdThen you have to check for error on your own with the variable @.@.Error.
BEGIN TRANSACTION
<Dosomething>
IF @.@.Error = 0
COMMIT TRANSACTION
ELSE ROLLBACK TRANSACTION
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"kd" <kd@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0056D0F3-9A2D-46D5-9667-C01FD7D671B2@.microsoft.com...
> Hi All,
> Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside
> stored procedure, if a client has already started a transaction? The
> reason,
> I am asking this question is that, though an error occured in a stored
> procedure which was called within a transaction (on the client side), the
> rest of the statements in the stored procedure were executed, which was
> not
> desired.
> I am have opened a connection and started a transaction in vb.net and
> called
> a stored procedure. The stored procedure calls several other stored
> procedures and adds/updates records in linked tables. It so happened that,
> one temporary table did not exist and that particular statement failed
> when I
> tried to select records from the temporary table. But the following
> statements were executed. Why was the transacton not being honoured?
> Ideally,
> when a statement failed, the transaction should be aborted; this is what I
> understand.
> Any suggestions?
> Thanks
> kd
>|||The piece you're missing, is that not all errors cause a Stored Proc to
terminate and return... Most, in fact, just set the value of @.@.Error and
continue processing the next statement. If you want the stored Proc t ostop
and return upon encountering an error from a specific statement, you have to
code that yourself, for each statement, by testing the value of @.@.error
immediately after teh statement executes...
Declare @.Err Integer -- At Beginning of SP
/* ******
Other stuff
*********/
<Statement>
Set @.Err = @.@.Error
If @.Err <> 0 Begin
If @.@.TranCount > 0 Rollback
Raiserror('This is message', 16,1)
Return (@.Err)
End
<rest of Stored Proc>
"kd" wrote:

> Hi All,
> Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside
> stored procedure, if a client has already started a transaction? The reaso
n,
> I am asking this question is that, though an error occured in a stored
> procedure which was called within a transaction (on the client side), the
> rest of the statements in the stored procedure were executed, which was no
t
> desired.
> I am have opened a connection and started a transaction in vb.net and call
ed
> a stored procedure. The stored procedure calls several other stored
> procedures and adds/updates records in linked tables. It so happened that,
> one temporary table did not exist and that particular statement failed whe
n I
> tried to select records from the temporary table. But the following
> statements were executed. Why was the transacton not being honoured? Ideal
ly,
> when a statement failed, the transaction should be aborted; this is what I
> understand.
> Any suggestions?
> Thanks
> kd
>|||Hi,
I don't have BEGIN TRANSACTION..END TRANSACTION block in the stored
procedure. I have begun a transcation in the client side in vb.net.
try
connection.Open()
trans = connection.BeginTransaction()
... 'called stored procedure
trans.Commit()
command.Connection.Close()
Catch ex As Exception
trans.Rollback()
End Try
My first question is this. Does the stored procedure and all the other
stored procedures, UDFs, etc called by the stored procedure be part of the
transaction started on the client side?
My second question is, what are the errors that cause a stored procedure to
terminate and return? I could may be dummy code for the errors and check
whether the transaction started at the client is honoured at the server end.
Regards,
kd
"CBretana" wrote:
> The piece you're missing, is that not all errors cause a Stored Proc to
> terminate and return... Most, in fact, just set the value of @.@.Error and
> continue processing the next statement. If you want the stored Proc t ost
op
> and return upon encountering an error from a specific statement, you have
to
> code that yourself, for each statement, by testing the value of @.@.error
> immediately after teh statement executes...
> Declare @.Err Integer -- At Beginning of SP
> /* ******
> Other stuff
> *********/
> <Statement>
> Set @.Err = @.@.Error
> If @.Err <> 0 Begin
> If @.@.TranCount > 0 Rollback
> Raiserror('This is message', 16,1)
> Return (@.Err)
> End
> <rest of Stored Proc>
> "kd" wrote:
>

question on backup

Hi,
currently I have set up the complete backup at midnight
and transaction backup every hour. I would like to
schedule some differential backup too.
If the complete backup doesn't finish by the time the
transaction backup needs to kick off, how the server deal
with it?
Same thing if differential backup doesn't finish by the
time transaction backup needs to kick off, how the server
deal with it?
I don't know how the server order the backups if I
schedule complete, differential and transcation backup
all at 12:00:00am?
I need to restore from the backup to recover the primary
server, how can I make sure they have exactly the same
footprint? I am imaging there always some time gap between
I load the backup and bring it online.Jen,
Backup operations to the same database must be serialized.
If a backup job kicks off during another backup of the same database, the
job will blow off.
James Hokes
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:057001c3ce6f$e528bd90$a101280a@.phx.gbl...
> Hi,
> currently I have set up the complete backup at midnight
> and transaction backup every hour. I would like to
> schedule some differential backup too.
> If the complete backup doesn't finish by the time the
> transaction backup needs to kick off, how the server deal
> with it?
> Same thing if differential backup doesn't finish by the
> time transaction backup needs to kick off, how the server
> deal with it?
> I don't know how the server order the backups if I
> schedule complete, differential and transcation backup
> all at 12:00:00am?
> I need to restore from the backup to recover the primary
> server, how can I make sure they have exactly the same
> footprint? I am imaging there always some time gap between
> I load the backup and bring it online.|||So it is your respsonsibility to know how long backups take, and schedule
them so they do NOT overlap...
--
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
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:057001c3ce6f$e528bd90$a101280a@.phx.gbl...
> Hi,
> currently I have set up the complete backup at midnight
> and transaction backup every hour. I would like to
> schedule some differential backup too.
> If the complete backup doesn't finish by the time the
> transaction backup needs to kick off, how the server deal
> with it?
> Same thing if differential backup doesn't finish by the
> time transaction backup needs to kick off, how the server
> deal with it?
> I don't know how the server order the backups if I
> schedule complete, differential and transcation backup
> all at 12:00:00am?
> I need to restore from the backup to recover the primary
> server, how can I make sure they have exactly the same
> footprint? I am imaging there always some time gap between
> I load the backup and bring it online.