Hi all,
Occasionally, when I perform a RESTORE on a database,
The msdb tables are not updated properly.
The RESTORE functions normally, however when I run a query against the
BackupSet, BackupMediaFamily and RestoreHistory tables, there appear to be orphaned records in the RestoreHistory table. (eg. The RestoreHistory has a record of the restore as well as a BackupSetID value, however these values cannot be found in the BackupSet and BackupMediaFamily tables.
This is a recurring problem, however it does not happen all of the time.
Do you have any ideas?
TIA
Rick Sawtell
MCT, MCSD, MCDBA
Too many drinks I think Rick<g>. Seriously though I have not noticed this before. Is there any chance there is a job that tries to clean out the history without using sp_deletebackuphistory?
Andrew J. Kelly SQL MVP
"Rick Sawtell" <quickening@.msn.com> wrote in message news:e7q53A54EHA.1260@.TK2MSFTNGP12.phx.gbl...
Hi all,
Occasionally, when I perform a RESTORE on a database,
The msdb tables are not updated properly.
The RESTORE functions normally, however when I run a query against the
BackupSet, BackupMediaFamily and RestoreHistory tables, there appear to be orphaned records in the RestoreHistory table. (eg. The RestoreHistory has a record of the restore as well as a BackupSetID value, however these values cannot be found in the BackupSet and BackupMediaFamily tables.
This is a recurring problem, however it does not happen all of the time.
Do you have any ideas?
TIA
Rick Sawtell
MCT, MCSD, MCDBA
|||There is that possibility,
I'm still researching it however. This only happens occasionally, so it is a bit baffling.
I've got 50 or so servers in the hosted environment and only a few of them are experiencing this problem.
All the servers are the same H/W and S/W, service packs etc.
Win 2k Server, SP4, SQL 2k SP3a etc.
I'll keep you posted, meanwhile if anyone else can think of anything...
I've got jobs that run after a restore takes place which ensures that everything in these logs match up (just in case we run into problems like this. ;-))
Rick
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:uT3Lwr54EHA.4008@.TK2MSFTNGP15.phx.gbl...
Too many drinks I think Rick<g>. Seriously though I have not noticed this before. Is there any chance there is a job that tries to clean out the history without using sp_deletebackuphistory?
Andrew J. Kelly SQL MVP
"Rick Sawtell" <quickening@.msn.com> wrote in message news:e7q53A54EHA.1260@.TK2MSFTNGP12.phx.gbl...
Hi all,
Occasionally, when I perform a RESTORE on a database,
The msdb tables are not updated properly.
The RESTORE functions normally, however when I run a query against the
BackupSet, BackupMediaFamily and RestoreHistory tables, there appear to be orphaned records in the RestoreHistory table. (eg. The RestoreHistory has a record of the restore as well as a BackupSetID value, however these values cannot be found in the BackupSet and BackupMediaFamily tables.
This is a recurring problem, however it does not happen all of the time.
Do you have any ideas?
TIA
Rick Sawtell
MCT, MCSD, MCDBA
Showing posts with label updated. Show all posts
Showing posts with label updated. Show all posts
Sunday, February 19, 2012
Thursday, February 16, 2012
Backup...
Is there a way to find out when the data in a table was
last updated?
How does Sql Server keep track of it?
Thank you in advance,
-TinaSQL Server keeps track of updates to the data via the TRANSACTION LOG.
There are a number of third party tools available to read through the
TRANSACTION LOG. One such product is LOG EXPLORER by Lumigent
(http://www.lumigent.com/).
You might also try using the undocumented DBCC LOG command
Here is schetchy documentatrion.
The following undocumented command will do the trick.
DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )
PARAMETERS:
Dbid or dbname - Enter either the dbid or the name of the database
in question.
type - is the type of output:
0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length)
2 - very detailed information (plus object name, index name,
page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump
of the current transaction log''s row.
by default type = 0
Best way to easily identify updated records is of course to design a
LAST_UPDATE_DATE column into your table design, but of course I don't see to
many folks doing this these days.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tina" <anonymous@.discussions.microsoft.com> wrote in message
news:123cf01c44275$db2cf520$a501280a@.phx.gbl...
> Is there a way to find out when the data in a table was
> last updated?
> How does Sql Server keep track of it?
> Thank you in advance,
> -Tina
>|||Hi Gregory,
Thanks for the reply. I tried the following commands
I get the error "[Microsoft][ODBC SQL Server Driver]
Syntax error or access violation"
DBCC log ({DB1},{3})
DBCC log ({dbname=DB1},{type=3})
DBCC log ( {DB1}, type={3)) where DB1 is the name of my
database.
What am I doing wrong?
I am running it using Query Analyzer.
Thank you,
-Tina
>--Original Message--
>SQL Server keeps track of updates to the data via the
TRANSACTION LOG.
>There are a number of third party tools available to
read through the
>TRANSACTION LOG. One such product is LOG EXPLORER by
Lumigent
>(http://www.lumigent.com/).
>You might also try using the undocumented DBCC LOG
command
>Here is schetchy documentatrion.
>The following undocumented command will do the trick.
>
>DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )
>
>
>
>
>PARAMETERS:
>
> Dbid or dbname - Enter either the dbid or the name of
the database
> in question.
>
> type - is the type of output:
>
> 0 - minimum information (operation, context,
transaction id)
>
> 1 - more information (plus flags, tags, row length)
>
> 2 - very detailed information (plus object name,
index name,
> page id, slot id)
>
> 3 - full information about each operation
>
> 4 - full information about each operation plus
hexadecimal dump
> of the current transaction log''s row.
>
>by default type = 0
>
>
>Best way to easily identify updated records is of course
to design a
>LAST_UPDATE_DATE column into your table design, but of
course I don't see to
>many folks doing this these days.
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Tina" <anonymous@.discussions.microsoft.com> wrote in
message
>news:123cf01c44275$db2cf520$a501280a@.phx.gbl...
>> Is there a way to find out when the data in a table was
>> last updated?
>> How does Sql Server keep track of it?
>> Thank you in advance,
>> -Tina
>
>.
>|||You need to remove the curly braces. Try using:
dbcc log(DB1, 3)
-Sue
On Tue, 25 May 2004 16:10:19 -0700, "Tina"
<anonymous@.discussions.microsoft.com> wrote:
>Hi Gregory,
>Thanks for the reply. I tried the following commands
>I get the error "[Microsoft][ODBC SQL Server Driver]
>Syntax error or access violation"
>DBCC log ({DB1},{3})
>DBCC log ({dbname=DB1},{type=3})
>DBCC log ( {DB1}, type={3)) where DB1 is the name of my
>database.
>What am I doing wrong?
>I am running it using Query Analyzer.
>Thank you,
>-Tina
>
>>--Original Message--
>>SQL Server keeps track of updates to the data via the
>TRANSACTION LOG.
>>There are a number of third party tools available to
>read through the
>>TRANSACTION LOG. One such product is LOG EXPLORER by
>Lumigent
>>(http://www.lumigent.com/).
>>You might also try using the undocumented DBCC LOG
>command
>>Here is schetchy documentatrion.
>>The following undocumented command will do the trick.
>>
>>DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )
>>
>>
>>
>>
>>PARAMETERS:
>>
>> Dbid or dbname - Enter either the dbid or the name of
>the database
>> in question.
>>
>> type - is the type of output:
>>
>> 0 - minimum information (operation, context,
>transaction id)
>>
>> 1 - more information (plus flags, tags, row length)
>>
>> 2 - very detailed information (plus object name,
>index name,
>> page id, slot id)
>>
>> 3 - full information about each operation
>>
>> 4 - full information about each operation plus
>hexadecimal dump
>> of the current transaction log''s row.
>>
>>by default type = 0
>>
>>
>>Best way to easily identify updated records is of course
>to design a
>>LAST_UPDATE_DATE column into your table design, but of
>course I don't see to
>>many folks doing this these days.
>>--
>>----
>--
>>----
>--
>>--
>>Need SQL Server Examples check out my website at
>>http://www.geocities.com/sqlserverexamples
>>"Tina" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:123cf01c44275$db2cf520$a501280a@.phx.gbl...
>> Is there a way to find out when the data in a table was
>> last updated?
>> How does Sql Server keep track of it?
>> Thank you in advance,
>> -Tina
>>
>>.
last updated?
How does Sql Server keep track of it?
Thank you in advance,
-TinaSQL Server keeps track of updates to the data via the TRANSACTION LOG.
There are a number of third party tools available to read through the
TRANSACTION LOG. One such product is LOG EXPLORER by Lumigent
(http://www.lumigent.com/).
You might also try using the undocumented DBCC LOG command
Here is schetchy documentatrion.
The following undocumented command will do the trick.
DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )
PARAMETERS:
Dbid or dbname - Enter either the dbid or the name of the database
in question.
type - is the type of output:
0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length)
2 - very detailed information (plus object name, index name,
page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump
of the current transaction log''s row.
by default type = 0
Best way to easily identify updated records is of course to design a
LAST_UPDATE_DATE column into your table design, but of course I don't see to
many folks doing this these days.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tina" <anonymous@.discussions.microsoft.com> wrote in message
news:123cf01c44275$db2cf520$a501280a@.phx.gbl...
> Is there a way to find out when the data in a table was
> last updated?
> How does Sql Server keep track of it?
> Thank you in advance,
> -Tina
>|||Hi Gregory,
Thanks for the reply. I tried the following commands
I get the error "[Microsoft][ODBC SQL Server Driver]
Syntax error or access violation"
DBCC log ({DB1},{3})
DBCC log ({dbname=DB1},{type=3})
DBCC log ( {DB1}, type={3)) where DB1 is the name of my
database.
What am I doing wrong?
I am running it using Query Analyzer.
Thank you,
-Tina
>--Original Message--
>SQL Server keeps track of updates to the data via the
TRANSACTION LOG.
>There are a number of third party tools available to
read through the
>TRANSACTION LOG. One such product is LOG EXPLORER by
Lumigent
>(http://www.lumigent.com/).
>You might also try using the undocumented DBCC LOG
command
>Here is schetchy documentatrion.
>The following undocumented command will do the trick.
>
>DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )
>
>
>
>
>PARAMETERS:
>
> Dbid or dbname - Enter either the dbid or the name of
the database
> in question.
>
> type - is the type of output:
>
> 0 - minimum information (operation, context,
transaction id)
>
> 1 - more information (plus flags, tags, row length)
>
> 2 - very detailed information (plus object name,
index name,
> page id, slot id)
>
> 3 - full information about each operation
>
> 4 - full information about each operation plus
hexadecimal dump
> of the current transaction log''s row.
>
>by default type = 0
>
>
>Best way to easily identify updated records is of course
to design a
>LAST_UPDATE_DATE column into your table design, but of
course I don't see to
>many folks doing this these days.
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Tina" <anonymous@.discussions.microsoft.com> wrote in
message
>news:123cf01c44275$db2cf520$a501280a@.phx.gbl...
>> Is there a way to find out when the data in a table was
>> last updated?
>> How does Sql Server keep track of it?
>> Thank you in advance,
>> -Tina
>
>.
>|||You need to remove the curly braces. Try using:
dbcc log(DB1, 3)
-Sue
On Tue, 25 May 2004 16:10:19 -0700, "Tina"
<anonymous@.discussions.microsoft.com> wrote:
>Hi Gregory,
>Thanks for the reply. I tried the following commands
>I get the error "[Microsoft][ODBC SQL Server Driver]
>Syntax error or access violation"
>DBCC log ({DB1},{3})
>DBCC log ({dbname=DB1},{type=3})
>DBCC log ( {DB1}, type={3)) where DB1 is the name of my
>database.
>What am I doing wrong?
>I am running it using Query Analyzer.
>Thank you,
>-Tina
>
>>--Original Message--
>>SQL Server keeps track of updates to the data via the
>TRANSACTION LOG.
>>There are a number of third party tools available to
>read through the
>>TRANSACTION LOG. One such product is LOG EXPLORER by
>Lumigent
>>(http://www.lumigent.com/).
>>You might also try using the undocumented DBCC LOG
>command
>>Here is schetchy documentatrion.
>>The following undocumented command will do the trick.
>>
>>DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )
>>
>>
>>
>>
>>PARAMETERS:
>>
>> Dbid or dbname - Enter either the dbid or the name of
>the database
>> in question.
>>
>> type - is the type of output:
>>
>> 0 - minimum information (operation, context,
>transaction id)
>>
>> 1 - more information (plus flags, tags, row length)
>>
>> 2 - very detailed information (plus object name,
>index name,
>> page id, slot id)
>>
>> 3 - full information about each operation
>>
>> 4 - full information about each operation plus
>hexadecimal dump
>> of the current transaction log''s row.
>>
>>by default type = 0
>>
>>
>>Best way to easily identify updated records is of course
>to design a
>>LAST_UPDATE_DATE column into your table design, but of
>course I don't see to
>>many folks doing this these days.
>>--
>>----
>--
>>----
>--
>>--
>>Need SQL Server Examples check out my website at
>>http://www.geocities.com/sqlserverexamples
>>"Tina" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:123cf01c44275$db2cf520$a501280a@.phx.gbl...
>> Is there a way to find out when the data in a table was
>> last updated?
>> How does Sql Server keep track of it?
>> Thank you in advance,
>> -Tina
>>
>>.
Subscribe to:
Posts (Atom)