DBCC LOGINFO('DBname')

When I run DBCC LOGINFO('DBname') I get 2 and 0 for Status column. How do I
make value 2 as zero before I can truncate the logfile?
ThanksTry to backup the transaction log. then look again.

>--Original Message--
>When I run DBCC LOGINFO('DBname') I get 2 and 0 for
Status column. How do I make value 2 as zero before I can
truncate the logfile?
>Thanks
>.
>

DBCC LOGINFO question

The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset that
includes a column entitled CreateLSN, does anybody know what this is and
what it means? I've tried to find what it is but met with no success so far.
It seems to have changed as well from earlier version of SQL where this
column used to be a date, and had a different name.
TIA
Michael MacGregor
Database Architect
This column is also defined as create_lsn in the (documented) catalog view
sys.database_files. It is defined as "Log sequence number (LSN) at which
the file was created."
For more information about LSNs, I recommend starting with the BOL topic
titled "Introduction to Log Sequence Numbers".
Regards,
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pro...ads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:ekkdPEx4GHA.4256@.TK2MSFTNGP03.phx.gbl...
> The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset
> that includes a column entitled CreateLSN, does anybody know what this is
> and what it means? I've tried to find what it is but met with no success
> so far. It seems to have changed as well from earlier version of SQL where
> this column used to be a date, and had a different name.
> TIA
> Michael MacGregor
> Database Architect
>
|||Thanks Gail.
MTM
|||Ok, read that but unfortunately it doesn't really help me much.
What I'm trying to find out is at what time, and by how much, the
transaction log grows? I was hoping that this CreateLSN might have helped,
but I can't see how it can. Is there any other way to find this out?
Michael MacGregor
Database Architect
|||The CreateLSN is really only interesting for backup/restore purposes. You
might consider using Profiler to monitor for events that cause the log file
to autogrow. See the topic "Log File Auto Grow Event Class."
Maybe one or more of the MVPs will jump in with some recommendations as
well.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pro...ads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
> Ok, read that but unfortunately it doesn't really help me much.
> What I'm trying to find out is at what time, and by how much, the
> transaction log grows? I was hoping that this CreateLSN might have helped,
> but I can't see how it can. Is there any other way to find this out?
> Michael MacGregor
> Database Architect
>
|||I have a feeling that is it difficult to get the time of the grow operations, at least from what is
exposed. Event though we have the LSN, and each LSN has a timestamp, we would need to grab the
original log records to map that LSN to a timestamp. fn_dblog() can probably show us the timestamp
for the log record based on the LSN, but that requires that the log record exists in the tlog.
Why not have a job that monitors the log at desired frequency? Or, use a server-side trace to
capture the autogrow operations...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:uZpFSjz4GHA.900@.TK2MSFTNGP04.phx.gbl...
> The CreateLSN is really only interesting for backup/restore purposes. You might consider using
> Profiler to monitor for events that cause the log file to autogrow. See the topic "Log File Auto
> Grow Event Class."
> Maybe one or more of the MVPs will jump in with some recommendations as well.
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pro...ads/books.mspx
> "Michael MacGregor" <nospam@.nospam.com> wrote in message
> news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
>

DBCC LOGINFO question

The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset that
includes a column entitled CreateLSN, does anybody know what this is and
what it means? I've tried to find what it is but met with no success so far.
It seems to have changed as well from earlier version of SQL where this
column used to be a date, and had a different name.
TIA
Michael MacGregor
Database ArchitectThis column is also defined as create_lsn in the (documented) catalog view
sys.database_files. It is defined as "Log sequence number (LSN) at which
the file was created."
For more information about LSNs, I recommend starting with the BOL topic
titled "Introduction to Log Sequence Numbers".
Regards,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:ekkdPEx4GHA.4256@.TK2MSFTNGP03.phx.gbl...
> The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset
> that includes a column entitled CreateLSN, does anybody know what this is
> and what it means? I've tried to find what it is but met with no success
> so far. It seems to have changed as well from earlier version of SQL where
> this column used to be a date, and had a different name.
> TIA
> Michael MacGregor
> Database Architect
>|||Thanks Gail.
MTM|||Ok, read that but unfortunately it doesn't really help me much.
What I'm trying to find out is at what time, and by how much, the
transaction log grows? I was hoping that this CreateLSN might have helped,
but I can't see how it can. Is there any other way to find this out?
Michael MacGregor
Database Architect|||The CreateLSN is really only interesting for backup/restore purposes. You
might consider using Profiler to monitor for events that cause the log file
to autogrow. See the topic "Log File Auto Grow Event Class."
Maybe one or more of the MVPs will jump in with some recommendations as
well.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
> Ok, read that but unfortunately it doesn't really help me much.
> What I'm trying to find out is at what time, and by how much, the
> transaction log grows? I was hoping that this CreateLSN might have helped,
> but I can't see how it can. Is there any other way to find this out?
> Michael MacGregor
> Database Architect
>|||I have a feeling that is it difficult to get the time of the grow operations
, at least from what is
exposed. Event though we have the LSN, and each LSN has a timestamp, we woul
d need to grab the
original log records to map that LSN to a timestamp. fn_dblog() can probably
show us the timestamp
for the log record based on the LSN, but that requires that the log record e
xists in the tlog.
Why not have a job that monitors the log at desired frequency? Or, use a ser
ver-side trace to
capture the autogrow operations...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:uZpFSjz4GHA.900@.TK2MSFTNGP04.phx.gbl...
> The CreateLSN is really only interesting for backup/restore purposes. You
might consider using
> Profiler to monitor for events that cause the log file to autogrow. See t
he topic "Log File Auto
> Grow Event Class."
> Maybe one or more of the MVPs will jump in with some recommendations as we
ll.
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "Michael MacGregor" <nospam@.nospam.com> wrote in message
> news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
>

DBCC LOGINFO question

The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset that
includes a column entitled CreateLSN, does anybody know what this is and
what it means? I've tried to find what it is but met with no success so far.
It seems to have changed as well from earlier version of SQL where this
column used to be a date, and had a different name.
TIA
Michael MacGregor
Database ArchitectThis column is also defined as create_lsn in the (documented) catalog view
sys.database_files. It is defined as "Log sequence number (LSN) at which
the file was created."
For more information about LSNs, I recommend starting with the BOL topic
titled "Introduction to Log Sequence Numbers".
Regards,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:ekkdPEx4GHA.4256@.TK2MSFTNGP03.phx.gbl...
> The undocumented command DBCC LOGINFO, on SQL 2005 returns a resultset
> that includes a column entitled CreateLSN, does anybody know what this is
> and what it means? I've tried to find what it is but met with no success
> so far. It seems to have changed as well from earlier version of SQL where
> this column used to be a date, and had a different name.
> TIA
> Michael MacGregor
> Database Architect
>|||Thanks Gail.
MTM|||Ok, read that but unfortunately it doesn't really help me much.
What I'm trying to find out is at what time, and by how much, the
transaction log grows? I was hoping that this CreateLSN might have helped,
but I can't see how it can. Is there any other way to find this out?
Michael MacGregor
Database Architect|||The CreateLSN is really only interesting for backup/restore purposes. You
might consider using Profiler to monitor for events that cause the log file
to autogrow. See the topic "Log File Auto Grow Event Class."
Maybe one or more of the MVPs will jump in with some recommendations as
well.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
> Ok, read that but unfortunately it doesn't really help me much.
> What I'm trying to find out is at what time, and by how much, the
> transaction log grows? I was hoping that this CreateLSN might have helped,
> but I can't see how it can. Is there any other way to find this out?
> Michael MacGregor
> Database Architect
>|||I have a feeling that is it difficult to get the time of the grow operations, at least from what is
exposed. Event though we have the LSN, and each LSN has a timestamp, we would need to grab the
original log records to map that LSN to a timestamp. fn_dblog() can probably show us the timestamp
for the log record based on the LSN, but that requires that the log record exists in the tlog.
Why not have a job that monitors the log at desired frequency? Or, use a server-side trace to
capture the autogrow operations...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:uZpFSjz4GHA.900@.TK2MSFTNGP04.phx.gbl...
> The CreateLSN is really only interesting for backup/restore purposes. You might consider using
> Profiler to monitor for events that cause the log file to autogrow. See the topic "Log File Auto
> Grow Event Class."
> Maybe one or more of the MVPs will jump in with some recommendations as well.
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "Michael MacGregor" <nospam@.nospam.com> wrote in message
> news:uFTW1oy4GHA.5012@.TK2MSFTNGP03.phx.gbl...
>> Ok, read that but unfortunately it doesn't really help me much.
>> What I'm trying to find out is at what time, and by how much, the transaction log grows? I was
>> hoping that this CreateLSN might have helped, but I can't see how it can. Is there any other way
>> to find this out?
>> Michael MacGregor
>> Database Architect
>

DBCC loginfo

I've been using this useful undocumented DBCC command
recently but the only information I can find on the Status
column it returns, is that above 0 means that the virtual
log is in use.
Now all the VLF's I examined had a status of 2 and these
we're not uncommitted or undistrubted, just not backed
up. These returned to a status of 0 when backed up.
So, does anyone know what the values in the Status field
mean? How is say 1 different to 2? What other values can
it take?
Graham Davies
DBAI think a value of 1 is the VL that SQL is currently writing to, 0 is the
inactive part of the log and 2 is the active part of the log... Only the
inactive part of the log can be shrunk away...
BTW, this is undocumented and these are just my observations ( which may be
incorrect.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Graham Davies" <anonymous@.discussions.microsoft.com> wrote in message
news:186401c43f48$a96c9fc0$3a01280a@.phx.gbl...
> I've been using this useful undocumented DBCC command
> recently but the only information I can find on the Status
> column it returns, is that above 0 means that the virtual
> log is in use.
> Now all the VLF's I examined had a status of 2 and these
> we're not uncommitted or undistrubted, just not backed
> up. These returned to a status of 0 when backed up.
> So, does anyone know what the values in the Status field
> mean? How is say 1 different to 2? What other values can
> it take?
> Graham Davies
> DBA
>

DBCC loginfo

I've been using this useful undocumented DBCC command
recently but the only information I can find on the Status
column it returns, is that above 0 means that the virtual
log is in use.
Now all the VLF's I examined had a status of 2 and these
we're not uncommitted or undistrubted, just not backed
up. These returned to a status of 0 when backed up.
So, does anyone know what the values in the Status field
mean? How is say 1 different to 2? What other values can
it take?
Graham Davies
DBAI think a value of 1 is the VL that SQL is currently writing to, 0 is the
inactive part of the log and 2 is the active part of the log... Only the
inactive part of the log can be shrunk away...
BTW, this is undocumented and these are just my observations ( which may be
incorrect.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Graham Davies" <anonymous@.discussions.microsoft.com> wrote in message
news:186401c43f48$a96c9fc0$3a01280a@.phx.gbl...
> I've been using this useful undocumented DBCC command
> recently but the only information I can find on the Status
> column it returns, is that above 0 means that the virtual
> log is in use.
> Now all the VLF's I examined had a status of 2 and these
> we're not uncommitted or undistrubted, just not backed
> up. These returned to a status of 0 when backed up.
> So, does anyone know what the values in the Status field
> mean? How is say 1 different to 2? What other values can
> it take?
> Graham Davies
> DBA
>

DBCC LOGINFO

I am trying to get my log files to reduce in size, but when I run DBCC
LOGINFO (MPlus) I get 491 rows with status =2 so when I go to shrink the
logfile after a backup (BACKUP LOG MPlus WITH TRUNCATE_ONLY) I get "Ensure
the Log Reader Agent is running or use sp_repldone to mark transactions as
distributed."
I would asume that when the synch runs (once an hour for me, merge) it
should update these rows as replicated, but I still have 491 no matter what.
I dont have a log reader agent, and I am hesitant to go and write off these
rows using sp_repldone.
Any suggestions?
Duncan
Duncan,
it looks as though at some time you have set up transactional replication on
articles from this database. If it was still there, then running the log
reader agent would sort things out, then you could backup and shrink. As the
publication is no longer there, then running sp_repldone will mark the
transactions as read with the same effect. This won't affect your merge
replication, as it uses a different mechanism to log the changes, namely
triggers.
Regards,
Paul Ibison
|||Can you run this in your publishing database.
sp_replicationdboption 'DatabaseName','sync with backup','false'
See if you still get this error message.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hilary: I got "The replication option 'sync with backup' of database 'Mplus'
has been set to false." What did this do? Will it help the db and backups
shrink again?
Paul, We have never had Transactional on it, not as far as I can remember. I
do have a snapshot replication running on the DB as well (as merge) to keep
a warm backup server going. Would this be why DBCC LOGINFO is returning all
these rows that have status=2?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:24DAD586-A5DF-4ECF-8834-50F8E1532E53@.microsoft.com...
> Can you run this in your publishing database.
> sp_replicationdboption 'DatabaseName','sync with backup','false'
> See if you still get this error message.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||Hilary, I ran this then BACKUP LOG MPlus WITH TRUNCATE_ONLY and still got
the same error.
What did this command do exactly?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:24DAD586-A5DF-4ECF-8834-50F8E1532E53@.microsoft.com...
> Can you run this in your publishing database.
> sp_replicationdboption 'DatabaseName','sync with backup','false'
> See if you still get this error message.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>

DBCC loginfo

I've been using this useful undocumented DBCC command
recently but the only information I can find on the Status
column it returns, is that above 0 means that the virtual
log is in use.
Now all the VLF's I examined had a status of 2 and these
we're not uncommitted or undistrubted, just not backed
up. These returned to a status of 0 when backed up.
So, does anyone know what the values in the Status field
mean? How is say 1 different to 2? What other values can
it take?
Graham Davies
DBA
I think a value of 1 is the VL that SQL is currently writing to, 0 is the
inactive part of the log and 2 is the active part of the log... Only the
inactive part of the log can be shrunk away...
BTW, this is undocumented and these are just my observations ( which may be
incorrect.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Graham Davies" <anonymous@.discussions.microsoft.com> wrote in message
news:186401c43f48$a96c9fc0$3a01280a@.phx.gbl...
> I've been using this useful undocumented DBCC command
> recently but the only information I can find on the Status
> column it returns, is that above 0 means that the virtual
> log is in use.
> Now all the VLF's I examined had a status of 2 and these
> we're not uncommitted or undistrubted, just not backed
> up. These returned to a status of 0 when backed up.
> So, does anyone know what the values in the Status field
> mean? How is say 1 different to 2? What other values can
> it take?
> Graham Davies
> DBA
>