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

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 DBREINDEX Status

I have inherited a SQL SERVER 2000 project from a colleague and we have a monthly SQL SERVER job that reindexes all the indexes in a reporting table (it contains archived records up to 1 year old, averaging close to 15 million rows). This table receives fresh data from another table in a different database every night (close to 30000 rows).

Since there is a monthly job that rebuilds all the indexes, there will be a single day every month when the rebuilding of indexes will clash with insertion of those new rows. I haven't been able to find a specific TSQL construct that I can use in my INSERT stored procedure to see if the table is currently locked for rebuilding indexes before trying to insert the rows. Specifically, I wouldn't insert the rows that night because the table is being reindexed.

Please let me know if there is such a facility in Server 2000 to find out the current indexing status. Also, I wouldn't mind alternate solutions to the above problem.

Thanks in advance!
Your ETL Job could check to see if the re-indexing Job is running, and if so, have the ETL job take a pass for an hour [ WAITFOR DELAY '001:00:00' ], then check again, etc.|||

Sorry to ask the obvious, but how to check if the job is running? Specifically, from within SQL Server and outside SQL Server? I guess at this point in time I am just curious to find out both alternatives.

Thanks for the reply!

|||

With SQL 2005, you could use sp_helpjobactivity.

However, with SQL 2000, it is a bit more trouble. You can check the msdb.dbo.sysjobhistory, column run_status, looking for run_status = 4 (in progress).

|||

Great tip!

OK, here is a more reliability-related question... I have never dealt with the SQL Server Job Agent (believe me, I have complete faith in SQL Server 2005, but this is 2000's job agent), but I am hoping to find out how reliable the Agent is in 2000? I only bring up this question because you mentioned in your previous post that "with SQL Server 2000, it is a bit more trouble." Of course looking at the schema of that table (sysjobhistory), this doesn't seem to be too hard!

Thanks again for the quick replies, Arnie!

|||

My experience with SQL 2000 SQL Agent is that it is very reliable.

However, sometimes the JobHistory table seems to have some latency. If a Job 'should' be running, and the run_status <> 4, I will also cross check a the start_time against the end_time. Keep in mind that an entry is added to the JobHistory table when the Job starts, and if the end_time is NULL, then it has not finished.

|||

Alternatively you could use the stored procedure 'sp_help_job' to determine the job's status.

See here for more info:

http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2

...or here (note that this link refers to SQL Server 7 but the syntax should still apply to SQL Server 2000):

http://doc.ddart.net/mssql/sql70/sp_help_27.htm

Chris

|||Thanks, Chris!