Database Marked suspect with Error 3314

Hi,
I have a database that is constantly marked suspect with Error: 3314,
Severity: 21, State: 4 and it says "Error while undoing logged operation in
database 'prod_db'. Error at log record ID (81:7137:38)..". This database is
on SQL Server 2000 with SP3 on WIN2k AS with SP2. After it prints this
error, I also get Error: 9001, Severity: 21, State: 1 that states " The log
for database 'prod_db' is not available.. After I get the error message, the
database recovers itself and comes back on line. I have run DBCC commands
including the CHECKFILEGROUP and they are all coming up clean.
If someone can throw some light, on this I will greatly appreciate it.
Database is less than 1 GB and for data and log, it has unlimited file
growth. Recovery model for the database is "Full."
Thanks,
Sanjay.Are those the only errormessages? Is autoclose on for the database?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Sanjay" <s_dhakhwa@.hotmail.com> wrote in message news:eF$UJlylDHA.684@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a database that is constantly marked suspect with Error: 3314,
> Severity: 21, State: 4 and it says "Error while undoing logged operation in
> database 'prod_db'. Error at log record ID (81:7137:38)..". This database is
> on SQL Server 2000 with SP3 on WIN2k AS with SP2. After it prints this
> error, I also get Error: 9001, Severity: 21, State: 1 that states " The log
> for database 'prod_db' is not available.. After I get the error message, the
> database recovers itself and comes back on line. I have run DBCC commands
> including the CHECKFILEGROUP and they are all coming up clean.
> If someone can throw some light, on this I will greatly appreciate it.
> Database is less than 1 GB and for data and log, it has unlimited file
> growth. Recovery model for the database is "Full."
> Thanks,
> Sanjay.
>|||Hi Sanjay,
According to my research, this issue is mostly like a database corruption
problem. Do you have any backup of this database? If so, I would like you
to try to restore the database from the backup.
For additional information regarding restoring the database, please refer
to the following article on SQL Server Books Online.
Topic:"RESTORE"
Topic:"How to restore a database backup (Transact-SQL)"
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology requests please take a look at the web site
listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Thank you for all your suggestion.
I can restore from the backups of the database on to a different server and
all the DBCCs come out clean. The database does not have "Auto close" option
turned on. We opened up the case with microsoft since this is a really
wiered case.
Again, thanks a lot for your help.
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:Y9gqoj6lDHA.1548@.cpmsftngxa06.phx.gbl...
> Hi Sanjay,
> According to my research, this issue is mostly like a database corruption
> problem. Do you have any backup of this database? If so, I would like you
> to try to restore the database from the backup.
> For additional information regarding restoring the database, please refer
> to the following article on SQL Server Books Online.
> Topic:"RESTORE"
> Topic:"How to restore a database backup (Transact-SQL)"
> Also, due to the complexity of this issue, it would be best to contact
> Microsoft Product Support Services via telephone so that a dedicated
> Support Professional can assist with your request. To obtain the phone
> numbers for specific technology requests please take a look at the web
site
> listed below.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>

Database log truncated: Database: MyDatabase.

Hi,

My Database recovery model is set to Bulk Logged. I recently got this error in the Application Event Log.

EVENT LOG Application
EVENT TYPE Error
SOURCE MSSQLSERVER
CATEGORY Backup
EVENT ID 17055
MESSAGE 18278 :
Database log truncated: Database: MyDatabase.

I've checked the MS Knowledgebase, however article 818202 relates to Full Recovery model not Bulk Logged. I would like to know why this error has occurred and what it really means.

Cheers

DamianThis is not an error, it's just information only message. It's no different than when you issue a Truncate_only log backup.

Database log truncated: Database: MyDatabase.

Hi,
My Database recovery model is set to Bulk Logged. I
recently got this error in the Application Event Log.
EVENT LOG Application
EVENT TYPE Error
SOURCE MSSQLSERVER
CATEGORY Backup
EVENT ID 17055
MESSAGE 18278 :
Database log truncated: Database: MyDatabase.
I've checked the MS Knowledgebase, however article 818202
relates to Full Recovery model not Bulk Logged. I would
like to know why this error has occurred and what it really
means.
Cheers
DamianIt means you (or someone) did a BACKUP LOG WITH TRUNCATE_ONLY or =similar statement. And the log files can not be used for recovery until =the next full db backup is taken.
Mike John
"Damian Jolly" <damian.jolly@.sageautomation.com> wrote in message =news:38fe01c3c8dc$ba792e10$7d02280a@.phx.gbl...
> Hi,
> > My Database recovery model is set to Bulk Logged. I
> recently got this error in the Application Event Log.
> > EVENT LOG Application > EVENT TYPE Error > SOURCE MSSQLSERVER > CATEGORY Backup > EVENT ID 17055 > MESSAGE 18278 :
> Database log truncated: Database: MyDatabase.
> > I've checked the MS Knowledgebase, however article 818202
> relates to Full Recovery model not Bulk Logged. I would
> like to know why this error has occurred and what it really
> means.
> > Cheers
> > Damian|||To add to Mike's response, I ran the repro script in the MSKB article but
with the recovery model set to BULK_LOGGED. The error also appeared in the
log with BULK_LOGGED recovery.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Damian Jolly" <damian.jolly@.sageautomation.com> wrote in message
news:38fe01c3c8dc$ba792e10$7d02280a@.phx.gbl...
> Hi,
> My Database recovery model is set to Bulk Logged. I
> recently got this error in the Application Event Log.
> EVENT LOG Application
> EVENT TYPE Error
> SOURCE MSSQLSERVER
> CATEGORY Backup
> EVENT ID 17055
> MESSAGE 18278 :
> Database log truncated: Database: MyDatabase.
> I've checked the MS Knowledgebase, however article 818202
> relates to Full Recovery model not Bulk Logged. I would
> like to know why this error has occurred and what it really
> means.
> Cheers
> Damian

Database List for the currently logged in user

I can get a list of all databases in SQL Server using

sqlText = "select name from sysdatabases order by name".

How can I get such a list for the currently logged in user only?

Thanks in advance

Harold HoffmanI found it.

select name from sysdatabases where HAS_DBACCESS(name) = 1 order by name

Harold Hoffman

Database Last Modified

Is it possible to find out when a database was last modified, or logged into?
Will looking at the data and log file to see when they where last modified
at OS level do this?
Thanks
AdamYou can write something on sysprocesses. that eyes the DBID.
or Run a Profiler all the time.
Create a Trigger for Login attempts or object changed and save these
values in a table.
Maninder
MCDBA|||Adam Sankey wrote:
> Is it possible to find out when a database was last modified, or logged into?
> Will looking at the data and log file to see when they where last modified
> at OS level do this?
> Thanks
> Adam
SQL 2000 or 2005? There's nothing built in to SQL 2000, you would have
to write some triggers or capture some traces. The file date/time
usually indicates the last time the file grew or was "recovered". Data
modifications don't update those.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Adam --
I have been searching for this answer for nearly a year now. The official
consensus is "NO", but I have reason to believe that this is not so. If work
didn't require 10+ hours a day and I didn't have a family who is ready to put
me on probation for still being at work when normal people are enjoying
relaxing evenings at home, I might have time to drill down to an answer.
I am also playing catch up since Oracle metadata tables were quite familiar
to me before my focus was switched to SQLServer and I had trouble finding
similar objects in SQLServer. However, since I like challenges and am
persistent, I have discovered stuff from people like --
Kalen Delaney (a legend in her own time) has published two books relevant to
this discussion: "Inside SQLServer" (v2000 and v2005) dealing with the
database storage engine internals. Very comprehensive and well written, it
details structures within the 8k pages that SQLServer is made of and extents
(8 logically contiguous pages) and how these are allocated using GAM (Global
Allocation Map), SGAM (Shared Global Allocation Map), and IAM (Index
Allocation Map) pages.
You can view this stuff using DBCC PAGE (yadda yadda). You need to set trace
flags, specify dbase, file, page, and output options and you DO get LOTS of
output. But I think the key to last modification date of a database might be
found by excavating through this stuff.
My purpose in identifying the last modified date of a database is to
determine my risk level for data loss. The time between last backup and last
modified is that risk. Without knowing the last modified date, I could get my
shorts in a knot because I don't have a backup on the PatientTracking
database since 2004.08.01. But if I know that it has not been modified since
2004.07.06 -- I can ignore it since it is probably an archive or discontinued
remnant.
Well, if you do find an answer, I sure would like to know. I need something
that would work with SQLServer 2000 and 2005 mix -- I use scheduled tasks
composed of DOS/vbs/WMI to scan all my SQLServers daily, gathering
information about event log errors, failed jobs, db file sizes (allocated and
used), etc. since I don't have time to go to each of my 70 some servers
hosting SQLServer databases and look this up manually.
But knowing all this stuff is secondary to knowing that I am covered on
backups.
Thanks,
-- Glenn Wiens
"Adam Sankey" wrote:
> Is it possible to find out when a database was last modified, or logged into?
> Will looking at the data and log file to see when they where last modified
> at OS level do this?
> Thanks
> Adam

Database Last Modified

Is it possible to find out when a database was last modified, or logged into?
Will looking at the data and log file to see when they where last modified
at OS level do this?
Thanks
Adam
You can write something on sysprocesses. that eyes the DBID.
or Run a Profiler all the time.
Create a Trigger for Login attempts or object changed and save these
values in a table.
Maninder
MCDBA
|||Adam Sankey wrote:
> Is it possible to find out when a database was last modified, or logged into?
> Will looking at the data and log file to see when they where last modified
> at OS level do this?
> Thanks
> Adam
SQL 2000 or 2005? There's nothing built in to SQL 2000, you would have
to write some triggers or capture some traces. The file date/time
usually indicates the last time the file grew or was "recovered". Data
modifications don't update those.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Adam --
I have been searching for this answer for nearly a year now. The official
consensus is "NO", but I have reason to believe that this is not so. If work
didn't require 10+ hours a day and I didn't have a family who is ready to put
me on probation for still being at work when normal people are enjoying
relaxing evenings at home, I might have time to drill down to an answer.
I am also playing catch up since Oracle metadata tables were quite familiar
to me before my focus was switched to SQLServer and I had trouble finding
similar objects in SQLServer. However, since I like challenges and am
persistent, I have discovered stuff from people like --
Kalen Delaney (a legend in her own time) has published two books relevant to
this discussion: "Inside SQLServer" (v2000 and v2005) dealing with the
database storage engine internals. Very comprehensive and well written, it
details structures within the 8k pages that SQLServer is made of and extents
(8 logically contiguous pages) and how these are allocated using GAM (Global
Allocation Map), SGAM (Shared Global Allocation Map), and IAM (Index
Allocation Map) pages.
You can view this stuff using DBCC PAGE (yadda yadda). You need to set trace
flags, specify dbase, file, page, and output options and you DO get LOTS of
output. But I think the key to last modification date of a database might be
found by excavating through this stuff.
My purpose in identifying the last modified date of a database is to
determine my risk level for data loss. The time between last backup and last
modified is that risk. Without knowing the last modified date, I could get my
shorts in a knot because I don't have a backup on the PatientTracking
database since 2004.08.01. But if I know that it has not been modified since
2004.07.06 -- I can ignore it since it is probably an archive or discontinued
remnant.
Well, if you do find an answer, I sure would like to know. I need something
that would work with SQLServer 2000 and 2005 mix -- I use scheduled tasks
composed of DOS/vbs/WMI to scan all my SQLServers daily, gathering
information about event log errors, failed jobs, db file sizes (allocated and
used), etc. since I don't have time to go to each of my 70 some servers
hosting SQLServer databases and look this up manually.
But knowing all this stuff is secondary to knowing that I am covered on
backups.
Thanks,
-- Glenn Wiens
"Adam Sankey" wrote:

> Is it possible to find out when a database was last modified, or logged into?
> Will looking at the data and log file to see when they where last modified
> at OS level do this?
> Thanks
> Adam

Database Last Modified

Is it possible to find out when a database was last modified, or logged into
?
Will looking at the data and log file to see when they where last modified
at OS level do this?
Thanks
AdamYou can write something on sysprocesses. that eyes the DBID.
or Run a Profiler all the time.
Create a Trigger for Login attempts or object changed and save these
values in a table.
Maninder
MCDBA|||Adam Sankey wrote:
> Is it possible to find out when a database was last modified, or logged in
to?
> Will looking at the data and log file to see when they where last modified
> at OS level do this?
> Thanks
> Adam
SQL 2000 or 2005? There's nothing built in to SQL 2000, you would have
to write some triggers or capture some traces. The file date/time
usually indicates the last time the file grew or was "recovered". Data
modifications don't update those.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Adam --
I have been searching for this answer for nearly a year now. The official
consensus is "NO", but I have reason to believe that this is not so. If work
didn't require 10+ hours a day and I didn't have a family who is ready to pu
t
me on probation for still being at work when normal people are enjoying
relaxing evenings at home, I might have time to drill down to an answer.
I am also playing catch up since Oracle metadata tables were quite familiar
to me before my focus was switched to SQLServer and I had trouble finding
similar objects in SQLServer. However, since I like challenges and am
persistent, I have discovered stuff from people like --
Kalen Delaney (a legend in her own time) has published two books relevant to
this discussion: "Inside SQLServer" (v2000 and v2005) dealing with the
database storage engine internals. Very comprehensive and well written, it
details structures within the 8k pages that SQLServer is made of and extents
(8 logically contiguous pages) and how these are allocated using GAM (Global
Allocation Map), SGAM (Shared Global Allocation Map), and IAM (Index
Allocation Map) pages.
You can view this stuff using DBCC PAGE (yadda yadda). You need to set trace
flags, specify dbase, file, page, and output options and you DO get LOTS of
output. But I think the key to last modification date of a database might be
found by excavating through this stuff.
My purpose in identifying the last modified date of a database is to
determine my risk level for data loss. The time between last backup and last
modified is that risk. Without knowing the last modified date, I could get m
y
shorts in a knot because I don't have a backup on the PatientTracking
database since 2004.08.01. But if I know that it has not been modified since
2004.07.06 -- I can ignore it since it is probably an archive or discontinue
d
remnant.
Well, if you do find an answer, I sure would like to know. I need something
that would work with SQLServer 2000 and 2005 mix -- I use scheduled tasks
composed of DOS/vbs/WMI to scan all my SQLServers daily, gathering
information about event log errors, failed jobs, db file sizes (allocated an
d
used), etc. since I don't have time to go to each of my 70 some servers
hosting SQLServer databases and look this up manually.
But knowing all this stuff is secondary to knowing that I am covered on
backups.
Thanks,
-- Glenn Wiens
"Adam Sankey" wrote:

> Is it possible to find out when a database was last modified, or logged in
to?
> Will looking at the data and log file to see when they where last modified
> at OS level do this?
> Thanks
> Adam