Is there any way to find out what is in the Database log
file without using a third party utility (Something from
SQL Server itself or Microsoft) ' Another words, track
the transactions in the log.....
Thanks.AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
thing...
-Argenis
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
> Is there any way to find out what is in the Database log
> file without using a third party utility (Something from
> SQL Server itself or Microsoft) ' Another words, track
> the transactions in the log.....
> Thanks.|||I've listed three such tools on the links page on my web site (see my signature).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:uGJ7qwyIFHA.2648@.TK2MSFTNGP14.phx.gbl...
> AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
> thing...
> -Argenis
> "Rick" <anonymous@.discussions.microsoft.com> wrote in message
> news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
>> Is there any way to find out what is in the Database log
>> file without using a third party utility (Something from
>> SQL Server itself or Microsoft) ' Another words, track
>> the transactions in the log.....
>> Thanks.
>|||And LogPi (www.logpi.com)
Regards
Mike
"Argenis Fernandez" wrote:
> AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
> thing...
> -Argenis
> "Rick" <anonymous@.discussions.microsoft.com> wrote in message
> news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
> > Is there any way to find out what is in the Database log
> > file without using a third party utility (Something from
> > SQL Server itself or Microsoft) ' Another words, track
> > the transactions in the log.....
> >
> > Thanks.
>
>|||Microsoft or SQL Server does not have such tool woul be
sufficient (Read the question) !!!!!!!!!!!!!
>--Original Message--
>Is there any way to find out what is in the Database log
>file without using a third party utility (Something from
>SQL Server itself or Microsoft) ' Another words, track
>the transactions in the log.....
>Thanks.
>.
>|||Relax. You ask a question and get some additional information. How can this make you angry?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:50ea01c52337$f3f09190$a601280a@.phx.gbl...
> Microsoft or SQL Server does not have such tool woul be
> sufficient (Read the question) !!!!!!!!!!!!!
>
>>--Original Message--
>>Is there any way to find out what is in the Database log
>>file without using a third party utility (Something from
>>SQL Server itself or Microsoft) ' Another words, track
>>the transactions in the log.....
>>Thanks.
>>.|||Your links don't give me anything...Are you just
advertising yourself !!!!!!!!!!!
>--Original Message--
>Relax. You ask a question and get some additional
information. How can this make you angry?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Rick" <anonymous@.discussions.microsoft.com> wrote in
message
>news:50ea01c52337$f3f09190$a601280a@.phx.gbl...
>> Microsoft or SQL Server does not have such tool woul be
>> sufficient (Read the question) !!!!!!!!!!!!!
>>
>>--Original Message--
>>Is there any way to find out what is in the Database log
>>file without using a third party utility (Something from
>>SQL Server itself or Microsoft) ' Another words, track
>>the transactions in the log.....
>>Thanks.
>>.
>
>.
>|||> Your links don't give me anything...
Since you asked about reading the transaction log, I thought that knowledge about the log reader
tools available could be interesting. I've listed three such tools in my website, on my links page:
http://www.karaszi.com/SQLServer/links.asp
I might have been wrong in that assumption...
> Are you just
> advertising yourself !!!!!!!!!!!
On my website, I have a links page, a number of technical articles with tips, gotchas and other
technical information, about newsgroups and other stuff. I also mention who I am. I leave it up to
the reader to determine whether the website has any value or only serve as a mean to advertise
myself.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:553c01c5233f$bb049a40$a401280a@.phx.gbl...
> Your links don't give me anything...Are you just
> advertising yourself !!!!!!!!!!!
>
>>--Original Message--
>>Relax. You ask a question and get some additional
> information. How can this make you angry?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>http://www.sqlug.se/
>>
>>"Rick" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:50ea01c52337$f3f09190$a601280a@.phx.gbl...
>> Microsoft or SQL Server does not have such tool woul be
>> sufficient (Read the question) !!!!!!!!!!!!!
>>
>>--Original Message--
>>Is there any way to find out what is in the Database log
>>file without using a third party utility (Something from
>>SQL Server itself or Microsoft) ' Another words, track
>>the transactions in the log.....
>>Thanks.
>>.
>>
>>.|||Did not want to be rude......Some people have the
obsession to send people to their web site for
anything.....I knew that there wasn't a clear-cut
answer......It is our management getting pressured from
the security companies and pushing it down to us. The
problem is that they are cheap enough not to get any third
party utilities and act like bunch of morons when you
explain them what is available in SQL Server and what is
available out there......did not mean to take out of
you....
Thanks.
>--Original Message--
>> Your links don't give me anything...
>Since you asked about reading the transaction log, I
thought that knowledge about the log reader
>tools available could be interesting. I've listed three
such tools in my website, on my links page:
>http://www.karaszi.com/SQLServer/links.asp
>I might have been wrong in that assumption...
>
>> Are you just
>> advertising yourself !!!!!!!!!!!
>On my website, I have a links page, a number of technical
articles with tips, gotchas and other
>technical information, about newsgroups and other stuff.
I also mention who I am. I leave it up to
>the reader to determine whether the website has any value
or only serve as a mean to advertise
>myself.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Rick" <anonymous@.discussions.microsoft.com> wrote in
message
>news:553c01c5233f$bb049a40$a401280a@.phx.gbl...
>> Your links don't give me anything...Are you just
>> advertising yourself !!!!!!!!!!!
>>
>>--Original Message--
>>Relax. You ask a question and get some additional
>> information. How can this make you angry?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>http://www.sqlug.se/
>>
>>"Rick" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:50ea01c52337$f3f09190$a601280a@.phx.gbl...
>> Microsoft or SQL Server does not have such tool woul
be
>> sufficient (Read the question) !!!!!!!!!!!!!
>>
>>--Original Message--
>>Is there any way to find out what is in the Database
log
>>file without using a third party utility (Something
from
>>SQL Server itself or Microsoft) ' Another words,
track
>>the transactions in the log.....
>>Thanks.
>>.
>>
>>.
>
>.
>|||No worries.
Short answer is that SQL Server doesn't come with a log reader tool. There are two commands to
output t-log entries, but they are more or less binary information with which you can't decipher
anything useful. So in other words, if you want to read the log, you need a 3;rd party tool.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:561701c5234d$d0bd5490$a401280a@.phx.gbl...
> Did not want to be rude......Some people have the
> obsession to send people to their web site for
> anything.....I knew that there wasn't a clear-cut
> answer......It is our management getting pressured from
> the security companies and pushing it down to us. The
> problem is that they are cheap enough not to get any third
> party utilities and act like bunch of morons when you
> explain them what is available in SQL Server and what is
> available out there......did not mean to take out of
> you....
> Thanks.
>
>>--Original Message--
>> Your links don't give me anything...
>>Since you asked about reading the transaction log, I
> thought that knowledge about the log reader
>>tools available could be interesting. I've listed three
> such tools in my website, on my links page:
>>http://www.karaszi.com/SQLServer/links.asp
>>I might have been wrong in that assumption...
>>
>> Are you just
>> advertising yourself !!!!!!!!!!!
>>On my website, I have a links page, a number of technical
> articles with tips, gotchas and other
>>technical information, about newsgroups and other stuff.
> I also mention who I am. I leave it up to
>>the reader to determine whether the website has any value
> or only serve as a mean to advertise
>>myself.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>http://www.sqlug.se/
>>
>>"Rick" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:553c01c5233f$bb049a40$a401280a@.phx.gbl...
>> Your links don't give me anything...Are you just
>> advertising yourself !!!!!!!!!!!
>>
>>--Original Message--
>>Relax. You ask a question and get some additional
>> information. How can this make you angry?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>http://www.sqlug.se/
>>
>>"Rick" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:50ea01c52337$f3f09190$a601280a@.phx.gbl...
>> Microsoft or SQL Server does not have such tool woul
> be
>> sufficient (Read the question) !!!!!!!!!!!!!
>>
>>--Original Message--
>>Is there any way to find out what is in the Database
> log
>>file without using a third party utility (Something
> from
>>SQL Server itself or Microsoft) ' Another words,
> track
>>the transactions in the log.....
>>Thanks.
>>.
>>
>>.
>>
>>.
Showing posts with label third. Show all posts
Showing posts with label third. Show all posts
Friday, March 9, 2012
Log Explorer
Is there any way to find out what is in the Database log
file without using a third party utility (Something from
SQL Server itself or Microsoft) ? Another words, track
the transactions in the log.....
Thanks.
AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
thing...
-Argenis
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
> Is there any way to find out what is in the Database log
> file without using a third party utility (Something from
> SQL Server itself or Microsoft) ? Another words, track
> the transactions in the log.....
> Thanks.
|||I've listed three such tools on the links page on my web site (see my signature).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:uGJ7qwyIFHA.2648@.TK2MSFTNGP14.phx.gbl...
> AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
> thing...
> -Argenis
> "Rick" <anonymous@.discussions.microsoft.com> wrote in message
> news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
>
|||And LogPi (www.logpi.com)
Regards
Mike
"Argenis Fernandez" wrote:
> AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
> thing...
> -Argenis
> "Rick" <anonymous@.discussions.microsoft.com> wrote in message
> news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
>
>
file without using a third party utility (Something from
SQL Server itself or Microsoft) ? Another words, track
the transactions in the log.....
Thanks.
AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
thing...
-Argenis
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
> Is there any way to find out what is in the Database log
> file without using a third party utility (Something from
> SQL Server itself or Microsoft) ? Another words, track
> the transactions in the log.....
> Thanks.
|||I've listed three such tools on the links page on my web site (see my signature).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:uGJ7qwyIFHA.2648@.TK2MSFTNGP14.phx.gbl...
> AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
> thing...
> -Argenis
> "Rick" <anonymous@.discussions.microsoft.com> wrote in message
> news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
>
|||And LogPi (www.logpi.com)
Regards
Mike
"Argenis Fernandez" wrote:
> AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
> thing...
> -Argenis
> "Rick" <anonymous@.discussions.microsoft.com> wrote in message
> news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
>
>
Log Explorer
Is there any way to find out what is in the Database log
file without using a third party utility (Something from
SQL Server itself or Microsoft) ' Another words, track
the transactions in the log.....
Thanks.AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
thing...
-Argenis
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
> Is there any way to find out what is in the Database log
> file without using a third party utility (Something from
> SQL Server itself or Microsoft) ' Another words, track
> the transactions in the log.....
> Thanks.|||I've listed three such tools on the links page on my web site (see my signat
ure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:uGJ7qwyIFHA.2648@.TK2MSFTNGP14.phx.gbl...
> AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
> thing...
> -Argenis
> "Rick" <anonymous@.discussions.microsoft.com> wrote in message
> news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
>|||And LogPi (www.logpi.com)
Regards
Mike
"Argenis Fernandez" wrote:
> AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
> thing...
> -Argenis
> "Rick" <anonymous@.discussions.microsoft.com> wrote in message
> news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
>
>
file without using a third party utility (Something from
SQL Server itself or Microsoft) ' Another words, track
the transactions in the log.....
Thanks.AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
thing...
-Argenis
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
> Is there any way to find out what is in the Database log
> file without using a third party utility (Something from
> SQL Server itself or Microsoft) ' Another words, track
> the transactions in the log.....
> Thanks.|||I've listed three such tools on the links page on my web site (see my signat
ure).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:uGJ7qwyIFHA.2648@.TK2MSFTNGP14.phx.gbl...
> AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
> thing...
> -Argenis
> "Rick" <anonymous@.discussions.microsoft.com> wrote in message
> news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
>|||And LogPi (www.logpi.com)
Regards
Mike
"Argenis Fernandez" wrote:
> AFAIK, Log Explorer from Lumigent is the only tool capable of doing such
> thing...
> -Argenis
> "Rick" <anonymous@.discussions.microsoft.com> wrote in message
> news:53f101c5232a$7c1d9800$a401280a@.phx.gbl...
>
>
Wednesday, March 7, 2012
Log backups not truncating log
I just took over administration of a SS 2000 database that is hosted
by a third party service. There was a full backup being run each
night, with no transaction log backups. The TL did not truncate after
I started log backups. There are no uncommitted transactions in the
log according to DBCC OPENTRAN. I don't have access to the machine
itself, only to SQL Server via Enterprise Manager. Please respond if
you have any suggestions. Thank you.<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
Not truncating, or not shrinking?
Log backups don't shrink the size of the data file.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
> >I just took over administration of a SS 2000 database that is hosted
> > by a third party service. There was a full backup being run each
> > night, with no transaction log backups. The TL did not truncate after
> > I started log backups. There are no uncommitted transactions in the
> > log according to DBCC OPENTRAN. I don't have access to the machine
> > itself, only to SQL Server via Enterprise Manager. Please respond if
> > you have any suggestions. Thank you.
> Not truncating, or not shrinking?
> Log backups don't shrink the size of the data file.
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
Not truncating. There is 450 MB of used space after the most recent
TL backup.|||What does you virtual log file layout say (see about mind section of
http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Also, check for old open transactions (DBCC
OPENTRAN).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jheasley@.salvagedirect.com> wrote in message
news:1174930061.164826.99350@.n76g2000hsh.googlegroups.com...
> On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>> <jheas...@.salvagedirect.com> wrote in message
>> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>> >I just took over administration of a SS 2000 database that is hosted
>> > by a third party service. There was a full backup being run each
>> > night, with no transaction log backups. The TL did not truncate after
>> > I started log backups. There are no uncommitted transactions in the
>> > log according to DBCC OPENTRAN. I don't have access to the machine
>> > itself, only to SQL Server via Enterprise Manager. Please respond if
>> > you have any suggestions. Thank you.
>> Not truncating, or not shrinking?
>> Log backups don't shrink the size of the data file.
>> --
>> Greg Moore
>> SQL Server DBA Consulting
>> Email: sql (at) greenms.com http://www.greenms.com
> Not truncating. There is 450 MB of used space after the most recent
> TL backup.
>|||What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
What percentage is used?
As said, a log backup does not shrink the file. To shrink the file,
you'll need to run a DBCC SHRINKFILE against the log.|||On Mar 26, 6:36 pm, "cstrong" <clive.str...@.googlemail.com> wrote:
> What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
> What percentage is used?
> As said, a log backup does not shrink the file. To shrink the file,
> you'll need to run a DBCC SHRINKFILE against the log.
I understand the backup does not shrink the file physically, but
should truncate the transactions that have been written to the data
file. My file is not truncating after the backup, so it keeps growing
physically. Here are the results of the DBCC commands:
DBCC OPENTRAN:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SQLPERF(LOGSPACE)
Database Name: MyDb
Log Size (MB): 474.4922
Log Space Used (%): 99.84307
Status: 0
DBCC LOGINFO
This returns 1878 rows. The status column has a value of 2 for every
row, which would seem to indicate every VLF is in use.
Thanks for the replies.|||Full backups do not remove transactions from the transaction log. If you
are not backing up the tlog, it will grow indefinitely. You can either
start backing it up (the tlog), or you can issue the BACKUP log mydatabase
with truncate_only command to flush out the committed transactions. ONLY do
this if you no longer care about using the tlog for recovery purposes!
--
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>|||On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Full backups do not remove transactions from the transaction log. If you
> are not backing up the tlog, it will grow indefinitely. You can either
> start backing it up (the tlog), or you can issue the BACKUP log mydatabase
> with truncate_only command to flush out the committed transactions. ONLY do
> this if you no longer care about using the tlog for recovery purposes!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
> >I just took over administration of a SS 2000 database that is hosted
> > by a third party service. There was a full backup being run each
> > night, with no transaction log backups. The TL did not truncate after
> > I started log backups. There are no uncommitted transactions in the
> > log according to DBCC OPENTRAN. I don't have access to the machine
> > itself, only to SQL Server via Enterprise Manager. Please respond if
> > you have any suggestions. Thank you.
I understand that full backups do not remove transactions from the
TL. My problem is the tlog file is not being truncated after the tlog
backup runs.|||On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
Is the database enabled for replication? Transactions won't truncate
from the log if they are awaiting replication.|||On Mar 27, 8:45 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> > I just took over administration of a SS 2000 database that is hosted
> > by a third party service. There was a full backup being run each
> > night, with no transaction log backups. The TL did not truncate after
> > I started log backups. There are no uncommitted transactions in the
> > log according to DBCC OPENTRAN. I don't have access to the machine
> > itself, only to SQL Server via Enterprise Manager. Please respond if
> > you have any suggestions. Thank you.
> Is the database enabled for replication? Transactions won't truncate
> from the log if they are awaiting replication.
The db is not enable for replication|||Ahh. Try the DBCC SHRINKFILE command. However, due to virtual log
segmentation internally, it may STILL not shrink. There is help available
on the web for forcing a tlog to be shrinkable. It involves writing dummy
records to the tlog to force it over to the next virtual log boundary.
An easier fix if you can do it is to simply detatch and reattach using
sp_detach_db and sp_attach_single_file_db (assuming you have single file
db). There is a sp_attach_db command too, but you need to record
appropriate information for each db file prior to detach. PLEASE study
this command pair and test first if you use it!!
--
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174966348.518536.144910@.y80g2000hsf.googlegroups.com...
> On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> Full backups do not remove transactions from the transaction log. If you
>> are not backing up the tlog, it will grow indefinitely. You can either
>> start backing it up (the tlog), or you can issue the BACKUP log
>> mydatabase
>> with truncate_only command to flush out the committed transactions. ONLY
>> do
>> this if you no longer care about using the tlog for recovery purposes!
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> <jheas...@.salvagedirect.com> wrote in message
>> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>> >I just took over administration of a SS 2000 database that is hosted
>> > by a third party service. There was a full backup being run each
>> > night, with no transaction log backups. The TL did not truncate after
>> > I started log backups. There are no uncommitted transactions in the
>> > log according to DBCC OPENTRAN. I don't have access to the machine
>> > itself, only to SQL Server via Enterprise Manager. Please respond if
>> > you have any suggestions. Thank you.
> I understand that full backups do not remove transactions from the
> TL. My problem is the tlog file is not being truncated after the tlog
> backup runs.
>|||Well, yesterday afternoon the tlog finally cleared on its own, five
days after beginning tlog backups. I don't know why it took so long,
perhaps someone has insight into that. As the day wore on, the file
also shrunk physically.
I much appreciate the replies. Tibor, the articled on shrinking db
files is very informative. Thanks again, all.|||On Mar 28, 8:32 am, jheas...@.salvagedirect.com wrote:
> Well, yesterday afternoon the tlog finally cleared on its own, five
> days after beginning tlog backups. I don't know why it took so long,
> perhaps someone has insight into that. As the day wore on, the file
> also shrunk physically.
> I much appreciate the replies. Tibor, the articled on shrinking db
> files is very informative. Thanks again, all.
The log file PHYSICALLY shrank as the day progressed? Do you have the
Autoshrink option enabled on that database? You should turn that
off. Shrinking the database file is a logged operation, so every time
autoshrink decides to shrink the database, you're going to get log
activity, which won't truncate until the shrink operation finishes.
That might explain what you were seeing before. Autoshrink should NOT
be used on a production database.|||On Mar 29, 9:18 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 28, 8:32 am, jheas...@.salvagedirect.com wrote:
> > Well, yesterday afternoon the tlog finally cleared on its own, five
> > days after beginning tlog backups. I don't know why it took so long,
> > perhaps someone has insight into that. As the day wore on, the file
> > also shrunk physically.
> > I much appreciate the replies. Tibor, the articled on shrinking db
> > files is very informative. Thanks again, all.
> The log file PHYSICALLY shrank as the day progressed? Do you have the
> Autoshrink option enabled on that database? You should turn that
> off. Shrinking the database file is a logged operation, so every time
> autoshrink decides to shrink the database, you're going to get log
> activity, which won't truncate until the shrink operation finishes.
> That might explain what you were seeing before. Autoshrink should NOT
> be used on a production database.
Yes, it is enabled. I called the hosting company to ask about it.
They said it's part of their standard procedure to enable Autoshrink
when they set up a SQL Server instance for a client. The tech I spoke
with did not know why, but said it was OK to disable it, which I did.
Thank you, Tracy.
by a third party service. There was a full backup being run each
night, with no transaction log backups. The TL did not truncate after
I started log backups. There are no uncommitted transactions in the
log according to DBCC OPENTRAN. I don't have access to the machine
itself, only to SQL Server via Enterprise Manager. Please respond if
you have any suggestions. Thank you.<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
Not truncating, or not shrinking?
Log backups don't shrink the size of the data file.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
> >I just took over administration of a SS 2000 database that is hosted
> > by a third party service. There was a full backup being run each
> > night, with no transaction log backups. The TL did not truncate after
> > I started log backups. There are no uncommitted transactions in the
> > log according to DBCC OPENTRAN. I don't have access to the machine
> > itself, only to SQL Server via Enterprise Manager. Please respond if
> > you have any suggestions. Thank you.
> Not truncating, or not shrinking?
> Log backups don't shrink the size of the data file.
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
Not truncating. There is 450 MB of used space after the most recent
TL backup.|||What does you virtual log file layout say (see about mind section of
http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Also, check for old open transactions (DBCC
OPENTRAN).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jheasley@.salvagedirect.com> wrote in message
news:1174930061.164826.99350@.n76g2000hsh.googlegroups.com...
> On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>> <jheas...@.salvagedirect.com> wrote in message
>> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>> >I just took over administration of a SS 2000 database that is hosted
>> > by a third party service. There was a full backup being run each
>> > night, with no transaction log backups. The TL did not truncate after
>> > I started log backups. There are no uncommitted transactions in the
>> > log according to DBCC OPENTRAN. I don't have access to the machine
>> > itself, only to SQL Server via Enterprise Manager. Please respond if
>> > you have any suggestions. Thank you.
>> Not truncating, or not shrinking?
>> Log backups don't shrink the size of the data file.
>> --
>> Greg Moore
>> SQL Server DBA Consulting
>> Email: sql (at) greenms.com http://www.greenms.com
> Not truncating. There is 450 MB of used space after the most recent
> TL backup.
>|||What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
What percentage is used?
As said, a log backup does not shrink the file. To shrink the file,
you'll need to run a DBCC SHRINKFILE against the log.|||On Mar 26, 6:36 pm, "cstrong" <clive.str...@.googlemail.com> wrote:
> What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
> What percentage is used?
> As said, a log backup does not shrink the file. To shrink the file,
> you'll need to run a DBCC SHRINKFILE against the log.
I understand the backup does not shrink the file physically, but
should truncate the transactions that have been written to the data
file. My file is not truncating after the backup, so it keeps growing
physically. Here are the results of the DBCC commands:
DBCC OPENTRAN:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SQLPERF(LOGSPACE)
Database Name: MyDb
Log Size (MB): 474.4922
Log Space Used (%): 99.84307
Status: 0
DBCC LOGINFO
This returns 1878 rows. The status column has a value of 2 for every
row, which would seem to indicate every VLF is in use.
Thanks for the replies.|||Full backups do not remove transactions from the transaction log. If you
are not backing up the tlog, it will grow indefinitely. You can either
start backing it up (the tlog), or you can issue the BACKUP log mydatabase
with truncate_only command to flush out the committed transactions. ONLY do
this if you no longer care about using the tlog for recovery purposes!
--
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>|||On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Full backups do not remove transactions from the transaction log. If you
> are not backing up the tlog, it will grow indefinitely. You can either
> start backing it up (the tlog), or you can issue the BACKUP log mydatabase
> with truncate_only command to flush out the committed transactions. ONLY do
> this if you no longer care about using the tlog for recovery purposes!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
> >I just took over administration of a SS 2000 database that is hosted
> > by a third party service. There was a full backup being run each
> > night, with no transaction log backups. The TL did not truncate after
> > I started log backups. There are no uncommitted transactions in the
> > log according to DBCC OPENTRAN. I don't have access to the machine
> > itself, only to SQL Server via Enterprise Manager. Please respond if
> > you have any suggestions. Thank you.
I understand that full backups do not remove transactions from the
TL. My problem is the tlog file is not being truncated after the tlog
backup runs.|||On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
Is the database enabled for replication? Transactions won't truncate
from the log if they are awaiting replication.|||On Mar 27, 8:45 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> > I just took over administration of a SS 2000 database that is hosted
> > by a third party service. There was a full backup being run each
> > night, with no transaction log backups. The TL did not truncate after
> > I started log backups. There are no uncommitted transactions in the
> > log according to DBCC OPENTRAN. I don't have access to the machine
> > itself, only to SQL Server via Enterprise Manager. Please respond if
> > you have any suggestions. Thank you.
> Is the database enabled for replication? Transactions won't truncate
> from the log if they are awaiting replication.
The db is not enable for replication|||Ahh. Try the DBCC SHRINKFILE command. However, due to virtual log
segmentation internally, it may STILL not shrink. There is help available
on the web for forcing a tlog to be shrinkable. It involves writing dummy
records to the tlog to force it over to the next virtual log boundary.
An easier fix if you can do it is to simply detatch and reattach using
sp_detach_db and sp_attach_single_file_db (assuming you have single file
db). There is a sp_attach_db command too, but you need to record
appropriate information for each db file prior to detach. PLEASE study
this command pair and test first if you use it!!
--
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174966348.518536.144910@.y80g2000hsf.googlegroups.com...
> On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> Full backups do not remove transactions from the transaction log. If you
>> are not backing up the tlog, it will grow indefinitely. You can either
>> start backing it up (the tlog), or you can issue the BACKUP log
>> mydatabase
>> with truncate_only command to flush out the committed transactions. ONLY
>> do
>> this if you no longer care about using the tlog for recovery purposes!
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> <jheas...@.salvagedirect.com> wrote in message
>> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>> >I just took over administration of a SS 2000 database that is hosted
>> > by a third party service. There was a full backup being run each
>> > night, with no transaction log backups. The TL did not truncate after
>> > I started log backups. There are no uncommitted transactions in the
>> > log according to DBCC OPENTRAN. I don't have access to the machine
>> > itself, only to SQL Server via Enterprise Manager. Please respond if
>> > you have any suggestions. Thank you.
> I understand that full backups do not remove transactions from the
> TL. My problem is the tlog file is not being truncated after the tlog
> backup runs.
>|||Well, yesterday afternoon the tlog finally cleared on its own, five
days after beginning tlog backups. I don't know why it took so long,
perhaps someone has insight into that. As the day wore on, the file
also shrunk physically.
I much appreciate the replies. Tibor, the articled on shrinking db
files is very informative. Thanks again, all.|||On Mar 28, 8:32 am, jheas...@.salvagedirect.com wrote:
> Well, yesterday afternoon the tlog finally cleared on its own, five
> days after beginning tlog backups. I don't know why it took so long,
> perhaps someone has insight into that. As the day wore on, the file
> also shrunk physically.
> I much appreciate the replies. Tibor, the articled on shrinking db
> files is very informative. Thanks again, all.
The log file PHYSICALLY shrank as the day progressed? Do you have the
Autoshrink option enabled on that database? You should turn that
off. Shrinking the database file is a logged operation, so every time
autoshrink decides to shrink the database, you're going to get log
activity, which won't truncate until the shrink operation finishes.
That might explain what you were seeing before. Autoshrink should NOT
be used on a production database.|||On Mar 29, 9:18 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 28, 8:32 am, jheas...@.salvagedirect.com wrote:
> > Well, yesterday afternoon the tlog finally cleared on its own, five
> > days after beginning tlog backups. I don't know why it took so long,
> > perhaps someone has insight into that. As the day wore on, the file
> > also shrunk physically.
> > I much appreciate the replies. Tibor, the articled on shrinking db
> > files is very informative. Thanks again, all.
> The log file PHYSICALLY shrank as the day progressed? Do you have the
> Autoshrink option enabled on that database? You should turn that
> off. Shrinking the database file is a logged operation, so every time
> autoshrink decides to shrink the database, you're going to get log
> activity, which won't truncate until the shrink operation finishes.
> That might explain what you were seeing before. Autoshrink should NOT
> be used on a production database.
Yes, it is enabled. I called the hosting company to ask about it.
They said it's part of their standard procedure to enable Autoshrink
when they set up a SQL Server instance for a client. The tech I spoke
with did not know why, but said it was OK to disable it, which I did.
Thank you, Tracy.
Log backups not truncating log
I just took over administration of a SS 2000 database that is hosted
by a third party service. There was a full backup being run each
night, with no transaction log backups. The TL did not truncate after
I started log backups. There are no uncommitted transactions in the
log according to DBCC OPENTRAN. I don't have access to the machine
itself, only to SQL Server via Enterprise Manager. Please respond if
you have any suggestions. Thank you.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegr oups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
Not truncating, or not shrinking?
Log backups don't shrink the size of the data file.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
|||On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegr oups.com...
>
> Not truncating, or not shrinking?
> Log backups don't shrink the size of the data file.
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
Not truncating. There is 450 MB of used space after the most recent
TL backup.
|||What does you virtual log file layout say (see about mind section of
http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Also, check for old open transactions (DBCC
OPENTRAN).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jheasley@.salvagedirect.com> wrote in message
news:1174930061.164826.99350@.n76g2000hsh.googlegro ups.com...
> On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
> Not truncating. There is 450 MB of used space after the most recent
> TL backup.
>
|||What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
What percentage is used?
As said, a log backup does not shrink the file. To shrink the file,
you'll need to run a DBCC SHRINKFILE against the log.
|||On Mar 26, 6:36 pm, "cstrong" <clive.str...@.googlemail.com> wrote:
> What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
> What percentage is used?
> As said, a log backup does not shrink the file. To shrink the file,
> you'll need to run a DBCC SHRINKFILE against the log.
I understand the backup does not shrink the file physically, but
should truncate the transactions that have been written to the data
file. My file is not truncating after the backup, so it keeps growing
physically. Here are the results of the DBCC commands:
DBCC OPENTRAN:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SQLPERF(LOGSPACE)
Database Name: MyDb
Log Size (MB): 474.4922
Log Space Used (%): 99.84307
Status: 0
DBCC LOGINFO
This returns 1878 rows. The status column has a value of 2 for every
row, which would seem to indicate every VLF is in use.
Thanks for the replies.
|||Full backups do not remove transactions from the transaction log. If you
are not backing up the tlog, it will grow indefinitely. You can either
start backing it up (the tlog), or you can issue the BACKUP log mydatabase
with truncate_only command to flush out the committed transactions. ONLY do
this if you no longer care about using the tlog for recovery purposes!
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegr oups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
|||On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Full backups do not remove transactions from the transaction log. If you
> are not backing up the tlog, it will grow indefinitely. You can either
> start backing it up (the tlog), or you can issue the BACKUP log mydatabase
> with truncate_only command to flush out the committed transactions. ONLY do
> this if you no longer care about using the tlog for recovery purposes!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegr oups.com...
I understand that full backups do not remove transactions from the
TL. My problem is the tlog file is not being truncated after the tlog
backup runs.
|||On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
Is the database enabled for replication? Transactions won't truncate
from the log if they are awaiting replication.
|||On Mar 27, 8:45 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
>
> Is the database enabled for replication? Transactions won't truncate
> from the log if they are awaiting replication.
The db is not enable for replication
by a third party service. There was a full backup being run each
night, with no transaction log backups. The TL did not truncate after
I started log backups. There are no uncommitted transactions in the
log according to DBCC OPENTRAN. I don't have access to the machine
itself, only to SQL Server via Enterprise Manager. Please respond if
you have any suggestions. Thank you.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegr oups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
Not truncating, or not shrinking?
Log backups don't shrink the size of the data file.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
|||On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegr oups.com...
>
> Not truncating, or not shrinking?
> Log backups don't shrink the size of the data file.
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
Not truncating. There is 450 MB of used space after the most recent
TL backup.
|||What does you virtual log file layout say (see about mind section of
http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Also, check for old open transactions (DBCC
OPENTRAN).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jheasley@.salvagedirect.com> wrote in message
news:1174930061.164826.99350@.n76g2000hsh.googlegro ups.com...
> On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
> Not truncating. There is 450 MB of used space after the most recent
> TL backup.
>
|||What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
What percentage is used?
As said, a log backup does not shrink the file. To shrink the file,
you'll need to run a DBCC SHRINKFILE against the log.
|||On Mar 26, 6:36 pm, "cstrong" <clive.str...@.googlemail.com> wrote:
> What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
> What percentage is used?
> As said, a log backup does not shrink the file. To shrink the file,
> you'll need to run a DBCC SHRINKFILE against the log.
I understand the backup does not shrink the file physically, but
should truncate the transactions that have been written to the data
file. My file is not truncating after the backup, so it keeps growing
physically. Here are the results of the DBCC commands:
DBCC OPENTRAN:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SQLPERF(LOGSPACE)
Database Name: MyDb
Log Size (MB): 474.4922
Log Space Used (%): 99.84307
Status: 0
DBCC LOGINFO
This returns 1878 rows. The status column has a value of 2 for every
row, which would seem to indicate every VLF is in use.
Thanks for the replies.
|||Full backups do not remove transactions from the transaction log. If you
are not backing up the tlog, it will grow indefinitely. You can either
start backing it up (the tlog), or you can issue the BACKUP log mydatabase
with truncate_only command to flush out the committed transactions. ONLY do
this if you no longer care about using the tlog for recovery purposes!
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegr oups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
|||On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Full backups do not remove transactions from the transaction log. If you
> are not backing up the tlog, it will grow indefinitely. You can either
> start backing it up (the tlog), or you can issue the BACKUP log mydatabase
> with truncate_only command to flush out the committed transactions. ONLY do
> this if you no longer care about using the tlog for recovery purposes!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegr oups.com...
I understand that full backups do not remove transactions from the
TL. My problem is the tlog file is not being truncated after the tlog
backup runs.
|||On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
Is the database enabled for replication? Transactions won't truncate
from the log if they are awaiting replication.
|||On Mar 27, 8:45 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
>
> Is the database enabled for replication? Transactions won't truncate
> from the log if they are awaiting replication.
The db is not enable for replication
Log backups not truncating log
I just took over administration of a SS 2000 database that is hosted
by a third party service. There was a full backup being run each
night, with no transaction log backups. The TL did not truncate after
I started log backups. There are no uncommitted transactions in the
log according to DBCC OPENTRAN. I don't have access to the machine
itself, only to SQL Server via Enterprise Manager. Please respond if
you have any suggestions. Thank you.<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
Not truncating, or not shrinking?
Log backups don't shrink the size of the data file.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>
> Not truncating, or not shrinking?
> Log backups don't shrink the size of the data file.
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
Not truncating. There is 450 MB of used space after the most recent
TL backup.|||What does you virtual log file layout say (see about mind section of
http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Also, check for old
open transactions (DBCC
OPENTRAN).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jheasley@.salvagedirect.com> wrote in message
news:1174930061.164826.99350@.n76g2000hsh.googlegroups.com...
> On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
> Not truncating. There is 450 MB of used space after the most recent
> TL backup.
>|||What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
What percentage is used?
As said, a log backup does not shrink the file. To shrink the file,
you'll need to run a DBCC SHRINKFILE against the log.|||On Mar 26, 6:36 pm, "cstrong" <clive.str...@.googlemail.com> wrote:
> What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
> What percentage is used?
> As said, a log backup does not shrink the file. To shrink the file,
> you'll need to run a DBCC SHRINKFILE against the log.
I understand the backup does not shrink the file physically, but
should truncate the transactions that have been written to the data
file. My file is not truncating after the backup, so it keeps growing
physically. Here are the results of the DBCC commands:
DBCC OPENTRAN:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SQLPERF(LOGSPACE)
Database Name: MyDb
Log Size (MB): 474.4922
Log Space Used (%): 99.84307
Status: 0
DBCC LOGINFO
This returns 1878 rows. The status column has a value of 2 for every
row, which would seem to indicate every VLF is in use.
Thanks for the replies.|||Full backups do not remove transactions from the transaction log. If you
are not backing up the tlog, it will grow indefinitely. You can either
start backing it up (the tlog), or you can issue the BACKUP log mydatabase
with truncate_only command to flush out the committed transactions. ONLY do
this if you no longer care about using the tlog for recovery purposes!
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>|||On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Full backups do not remove transactions from the transaction log. If you
> are not backing up the tlog, it will grow indefinitely. You can either
> start backing it up (the tlog), or you can issue the BACKUP log mydatabase
> with truncate_only command to flush out the committed transactions. ONLY
do
> this if you no longer care about using the tlog for recovery purposes!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>
I understand that full backups do not remove transactions from the
TL. My problem is the tlog file is not being truncated after the tlog
backup runs.|||On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
Is the database enabled for replication? Transactions won't truncate
from the log if they are awaiting replication.|||On Mar 27, 8:45 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
>
> Is the database enabled for replication? Transactions won't truncate
> from the log if they are awaiting replication.
The db is not enable for replication
by a third party service. There was a full backup being run each
night, with no transaction log backups. The TL did not truncate after
I started log backups. There are no uncommitted transactions in the
log according to DBCC OPENTRAN. I don't have access to the machine
itself, only to SQL Server via Enterprise Manager. Please respond if
you have any suggestions. Thank you.<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
Not truncating, or not shrinking?
Log backups don't shrink the size of the data file.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>
> Not truncating, or not shrinking?
> Log backups don't shrink the size of the data file.
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
Not truncating. There is 450 MB of used space after the most recent
TL backup.|||What does you virtual log file layout say (see about mind section of
http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Also, check for old
open transactions (DBCC
OPENTRAN).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jheasley@.salvagedirect.com> wrote in message
news:1174930061.164826.99350@.n76g2000hsh.googlegroups.com...
> On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
> Not truncating. There is 450 MB of used space after the most recent
> TL backup.
>|||What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
What percentage is used?
As said, a log backup does not shrink the file. To shrink the file,
you'll need to run a DBCC SHRINKFILE against the log.|||On Mar 26, 6:36 pm, "cstrong" <clive.str...@.googlemail.com> wrote:
> What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
> What percentage is used?
> As said, a log backup does not shrink the file. To shrink the file,
> you'll need to run a DBCC SHRINKFILE against the log.
I understand the backup does not shrink the file physically, but
should truncate the transactions that have been written to the data
file. My file is not truncating after the backup, so it keeps growing
physically. Here are the results of the DBCC commands:
DBCC OPENTRAN:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SQLPERF(LOGSPACE)
Database Name: MyDb
Log Size (MB): 474.4922
Log Space Used (%): 99.84307
Status: 0
DBCC LOGINFO
This returns 1878 rows. The status column has a value of 2 for every
row, which would seem to indicate every VLF is in use.
Thanks for the replies.|||Full backups do not remove transactions from the transaction log. If you
are not backing up the tlog, it will grow indefinitely. You can either
start backing it up (the tlog), or you can issue the BACKUP log mydatabase
with truncate_only command to flush out the committed transactions. ONLY do
this if you no longer care about using the tlog for recovery purposes!
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>|||On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Full backups do not remove transactions from the transaction log. If you
> are not backing up the tlog, it will grow indefinitely. You can either
> start backing it up (the tlog), or you can issue the BACKUP log mydatabase
> with truncate_only command to flush out the committed transactions. ONLY
do
> this if you no longer care about using the tlog for recovery purposes!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>
I understand that full backups do not remove transactions from the
TL. My problem is the tlog file is not being truncated after the tlog
backup runs.|||On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
Is the database enabled for replication? Transactions won't truncate
from the log if they are awaiting replication.|||On Mar 27, 8:45 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
>
> Is the database enabled for replication? Transactions won't truncate
> from the log if they are awaiting replication.
The db is not enable for replication
Friday, February 24, 2012
Log all updated tables
We have a third party process that runs and updated several SQL tables.
Is there any way to find out what tables are being updated and store it in another table?IF you are running MS-SQL 2000, then use SQL Profiler.
-PatP|||I thought of that but they want this to run all the time. And profiler can cause some overhead. Is there any way to view what's in the transaction log?|||My first thought would be Lumigent Log Explorer (http://www.lumigent.com/products/le_sql.html), but there are options involving triggers that could also do what you've described.
-PatP|||You can take help of server side trace, as the PROFILER is a resource intensive operation.
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm & KBA http://support.microsoft.com/?kbid=822853
Is there any way to find out what tables are being updated and store it in another table?IF you are running MS-SQL 2000, then use SQL Profiler.
-PatP|||I thought of that but they want this to run all the time. And profiler can cause some overhead. Is there any way to view what's in the transaction log?|||My first thought would be Lumigent Log Explorer (http://www.lumigent.com/products/le_sql.html), but there are options involving triggers that could also do what you've described.
-PatP|||You can take help of server side trace, as the PROFILER is a resource intensive operation.
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm & KBA http://support.microsoft.com/?kbid=822853
Subscribe to:
Posts (Atom)