search
Japanese Chinese Nederlands Espanol Italiano Deutsch Francais Twitter Rss Feeds
SQL Server GroupsView
SQL Server Ce
SQL Server Clients
SQL Server Clustering
SQL Server Connect
SQL Server Dts
SQL Server Fulltext
SQL Server Integrationsvcs
SQL Server Msde
SQL Server Newusers
SQL Server Olap
SQL Server Programming
SQL Server Replication
SQL Server Reportingsvcs
SQL Server Security
SQL Server Server
SQL Server Setup
SQL Server Tools
SQL Server Xml

Group SummariesView
.NET Framework
Access
BizTalk
Certifications
CRM
DDK
Exchange Server
FoxPro
French
French .NET
Games
German
German .NET
Graphic Design
IIS
Internet
ISA Server
Italian
Italian .NET
Maps
MCIS
Miscellaneous
Mobile Application Development
Money
MSN
Networking
Office
Ops Mgr
Publisher
Security
SharePoint
Small Business
Spanish
Spanish .NET
SQL Server
Systems Management Server
Transaction Server
Virtual PC / Virtual Server
Visual Studio
Win32
Windows 2000
Windows 2003 Server
Windows 7
Windows Live
Windows Media
Windows Update
Windows Vista
Windows XP
 

View All Microsoft SQL Server Server Posts  Ask A New Question 

DBCC LOGINFO question - Michael MacGregor

Thursday, September 28, 2006 11:16 AM

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
reply
 

This column is also defined as create_lsn in the (documented) catalog view sys. - Gail Erickson [MS]

Thursday, September 28, 2006 2:02 PM

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/prodtechnol/sql/2005/downloads/books.mspx
reply

Thanks Gail.MTM - Michael MacGregor

Thursday, September 28, 2006 2:09 PM

Thanks Gail.

MTM
reply

Ok, read that but unfortunately it doesn't really help me much. - Michael MacGregor

Thursday, September 28, 2006 2:16 PM

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
reply

The CreateLSN is really only interesting for backup/restore purposes. - Gail Erickson [MS]

Thursday, September 28, 2006 4:01 PM

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
reply

I have a feeling that is it difficult to get the time of the grow operations, - Tibor Karaszi

Thursday, September 28, 2006 4:28 PM

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/
reply

Previous Microsoft SQL Server Server conversation.