MSDE and DDL error

I have recently set up MSDE through a SharePoint services
install. I added a database, but when I go to add a table
through Visual Studio or osql I get the error "ADO error:
DDL statement is not allowed." I have tried to modify the
DDL settings in my database through >> exec sp_dboption
mydatabase, "ddl in tran", true <<, but "ddl in tran" is
not a recognized option any longer. Anyone know a
workaround on this? THANKS>Check your MDAC version
Try to update it.
Regards
---
All information provided above AS IS.
"Stephen Haiman" <shaiman@.ctt-inc.com> wrote in message
news:115501c3a2ea$88b9a2a0$a301280a@.phx.gbl...
> I have recently set up MSDE through a SharePoint services
> install. I added a database, but when I go to add a table
> through Visual Studio or osql I get the error "ADO error:
> DDL statement is not allowed." I have tried to modify the
> DDL settings in my database through >> exec sp_dboption
> mydatabase, "ddl in tran", true <<, but "ddl in tran" is
> not a recognized option any longer. Anyone know a
> workaround on this? THANKS>

MSDE and Cyrillic

I have a table content in my database. This table has 5 fields(id, lang1, lang2 ...) type text. The problem is that one of the language fields has to contain some text in Cyrillic but after insert it always converts the text into "?? ????? ????". Can anyone help?

What you are getting is called character conversion but you are lucky I have helped someone at another site with Cyrillic character conversion. You enable column level collation for all the langauges your site support and the problem will be resolved. You need Enterprise manager to do it manually so use the link below to download the Eval version of SQL Server install it as a named instance and register your MSDE and in Query Analyzer right click on the tables in question and generate the create table statement and modify it to include the collation of all the languages used in the database. Run a search for column level collation in the BOL (books online). If you need more help post again. Hope this helps.

http://www.microsoft.com/sql/evaluation/trial/default.mspx

MSDE 2000A inner join problems

Hi All
I am using VB6 and MSDE 2000
I previously used Access 2000 and used the following ado sql to create a
temp table in access
(Invoice table contains Invoice data such as Invoice number, date etc.
Idetail table contains details of the invoice individual items e.g product
code, Invoice Number etc) The link is the Invoice Number field in each table
(Master/Detail)
sql = "SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
idetail.price INTO idetailtemp IN '" & App.path & "\data\tramcarstemp.mdb'
FROM idetail inner join [invoice] on idetail.inv_num = invoice.inv_num where
invoice.inv_date between #" & Format(DT1.Value, "m-d-yyyy") & "# and #" &
Format(DT2.Value, "m-d-yyyy") & "#"
cn.execute sql
This worked fine in Access 2000
I am upgrading to MSDE2000 and using the following code in TSql
Private Sub maketable()
Dim a, fso As New FileSystemObject
Dim x As Integer
Set a = fso.CreateTextFile(App.path & "\sql\BackupTramcars.sql", True)
a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy', 'true'")
a.WriteLine ("GO")
a.WriteLine ("USE " & "Tramcars")
a.WriteLine ("SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
idetail.price INTO idetailtemp")
a.WriteLine ("FROM idetail inner join [invoice] on idetail.inv_num =
invoice.inv_num")
a.WriteLine ("where invoice.inv_date between '" &
Format(frmorderhistory.DT1.Value, "m-d-yyyy") & "' and '" &
Format(frmorderhistory.DT2.Value, "m-d-yyyy") & "'")
a.WriteLine ("GO")
a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy', 'false'")
a.WriteLine ("GO")
a.Close
Set a = Nothing
End Sub
The table is created but the inserted invoice dates are only ever 1 of 2
dates
Also when I read from the table to graph the data with
sql = "Select inv_date, sum(qty) as amount from [idetailtemp] where
[prod_code] = '" &
frmorderhistory.maingrid.TextMatrix(frmorderhistor y.maingrid.Row, 0) & "'
group by [inv_date]"
rs.Open sql, cn, adOpenDynamic, adLockReadOnly
There are several records in the recordset but calling rs.recordcount = -1,
which didn't happen in Access 2K
Any ideas
Steve
hi Steve,
steve wrote:
> Hi All
> ...
> Private Sub maketable()
> Dim a, fso As New FileSystemObject
> Dim x As Integer
> Set a = fso.CreateTextFile(App.path & "\sql\BackupTramcars.sql", True)
> a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy',
> 'true'") a.WriteLine ("GO")
> a.WriteLine ("USE " & "Tramcars")
> a.WriteLine ("SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
> idetail.price INTO idetailtemp")
> a.WriteLine ("FROM idetail inner join [invoice] on idetail.inv_num =
> invoice.inv_num")
> a.WriteLine ("where invoice.inv_date between '" &
> Format(frmorderhistory.DT1.Value, "m-d-yyyy") & "' and '" &
> Format(frmorderhistory.DT2.Value, "m-d-yyyy") & "'")
> a.WriteLine ("GO")
> a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy',
> 'false'") a.WriteLine ("GO")
> a.Close
>
> The table is created but the inserted invoice dates are only ever 1
> of 2 dates
please use the ISO format when referencing dates... dates can be confusing,
but if you refer to them in the 'YYYYMMDD' or 'YYYY-MM-DD' format you will
be out of troubles.. please have a look at
http://www.windowsitpro.com/SQLServe...9147/9147.html ,
http://www.karaszi.com/SQLServer/info_datetime.asp for further info..

> Also when I read from the table to graph the data with
> sql = "Select inv_date, sum(qty) as amount from [idetailtemp] where
> [prod_code] = '" &
> frmorderhistory.maingrid.TextMatrix(frmorderhistor y.maingrid.Row, 0)
> & "' group by [inv_date]"
> rs.Open sql, cn, adOpenDynamic, adLockReadOnly
> There are several records in the recordset but calling rs.recordcount
> = -1, which didn't happen in Access 2K
this depends on the MDAC recordset type... the recordcount value will be
available when the recordset has been fully populated, that's to say as soon
as you perform a .MoveLast operation.. (BTW... .Movelast can be time
consuming as all rows must be fetched and transferred)..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea
Thanks. Worked a treat
Steve
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3k76maFt35b8U1@.individual.net...
> hi Steve,
> steve wrote:
> please use the ISO format when referencing dates... dates can be
> confusing, but if you refer to them in the 'YYYYMMDD' or 'YYYY-MM-DD'
> format you will be out of troubles.. please have a look at
> http://www.windowsitpro.com/SQLServe...9147/9147.html ,
> http://www.karaszi.com/SQLServer/info_datetime.asp for further info..
>
> this depends on the MDAC recordset type... the recordcount value will be
> available when the recordset has been fully populated, that's to say as
> soon as you perform a .MoveLast operation.. (BTW... .Movelast can be time
> consuming as all rows must be fetched and transferred)..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

MSDE 2000 Replication Conflict

I am using MSDE 2000 for my replication. the problem is that althought at time creating publication it inserts GUID into every table but if one table in 2 nodes has same primary key, it inserts only one row ( accorrding to prority ). there are some Conflict Reslover methods that can be used for this purpose. i wanted to ask that is there any other way for me to resolve this conflict. i am asking for a new way because my database schema has been created and a lot of coding behalf of that schema has been done.

I'd be thankful if you guide me.
Regards,

Are you trying to insert the same primary key at both nodes? In your case, conflict resolver won't work for you because although you have inserted at both nodes, they are treated as different rows with different rowguid. What are you trying to achieve in this scenario? Do rows with same primary key stand for the same thing in your business scenario?

You can also refer to this post to see if you have the same problem: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=509453&SiteID=1

Hope it helps

Wanwen

|||Dear Wanwen.

i have a scenario as follows.

I have one publisher and two subscribers. a table called "Companies" is my article. That "Companies" table has a primary key column called CompanyId which is an Identity Row. Now when i insert data into subscribers i will have two rows with same Primary key. For Example two rows with CompanyId 1. Now at time of merging data, i want both companyIds ( both rows with Pk 1 ) be present in publisher. But at reality it does not happen.

What should i do?! because i need both rows at publisher .

Note: i have to mention that at time of creating Subscription GUID is included by SQL Server. So why is it not working?!

Regards,|||

In your case, you need to use identity range management. Please refer to BOL topic: Replicating Identity Columns. Basically, to use identity columns in a replication topology that has updates at more than one node, each node in the replication topology must use a different range of identity values, so that duplicates do not occur. You could use auto for Identity Range Management Option and specify @.pub_identity_range, @.identity_range and @.threshold.

Rowguid column added by replication is for change tracking, conflict detecting and resolving, not for failure to apply changes due to constraint.

Hope it helps.

Wanwen

sql

MSDE : find out what triggers are created

Hi
may i know is there anyway for me to find out from any system table in
MSDE what are the triggers that have been created ?
I wish to delete all and log in a new set of triggers, just in case
some old triggers cause some problems to the new ones.
Thank you
Best regards
Boon Yiang
Hi,
From OSQL Query the SYSOBJECTS table.
use <dbname>
go
Select name from sysobjects where xtype = 'tr'
Thanks
Hari
<chuaby@.hotmail.com> wrote in message
news:1174143091.658347.48080@.e65g2000hsc.googlegro ups.com...
> Hi
> may i know is there anyway for me to find out from any system table in
> MSDE what are the triggers that have been created ?
> I wish to delete all and log in a new set of triggers, just in case
> some old triggers cause some problems to the new ones.
> Thank you
> Best regards
> Boon Yiang
>

MSDE (SQL Server 2000) does not return @@Identity

Hi All,
I'm creating an appplication with some stored procedures. The stored
procedure needs to return the @.@.Identity of the table's new record. The
function has no errors and compiles without any warnings. The function
does work fine some times (returning value). But most of the calls are
failing and the function does not return proper value.
CREATE PROCEDURE CreateNewCourse
@.CourseName NVARCHAR(256),
@.Duration Int,
@.Contents NVarChar(500),
AS
DECLARE @.Identity int
INSERT into CourseMaster
(
CourseName,Contents,
Duration
)
VALUES
(
@.CourseName,
@.Contents,
@.Duration
)
SET @.Identity = SCOPE_IDENTITY()
return @.Identity
Any suggestions?
TIA.Hi
Can you show us how you call the SP ?
To reproduce your problem please post DDL+ sample data?
"Anbu" <t_anbazhagan2001@.yahoo.co.in> wrote in message
news:1143606553.333820.230400@.t31g2000cwb.googlegroups.com...
> Hi All,
> I'm creating an appplication with some stored procedures. The stored
> procedure needs to return the @.@.Identity of the table's new record. The
> function has no errors and compiles without any warnings. The function
> does work fine some times (returning value). But most of the calls are
> failing and the function does not return proper value.
> CREATE PROCEDURE CreateNewCourse
> @.CourseName NVARCHAR(256),
> @.Duration Int,
> @.Contents NVarChar(500),
> AS
> DECLARE @.Identity int
> INSERT into CourseMaster
> (
> CourseName,Contents,
> Duration
> )
> VALUES
> (
> @.CourseName,
> @.Contents,
> @.Duration
> )
>
> SET @.Identity = SCOPE_IDENTITY()
> return @.Identity
> Any suggestions?
> TIA.
>|||I'm trying to execute the function from Visual Studio .NET IDE (through
Database Explorer). Normally it will return the output in the "output"
window of VS .NET IDE.|||"Anbu" <t_anbazhagan2001@.yahoo.co.in> wrote in message
news:1143613779.111998.71180@.g10g2000cwb.googlegroups.com...
> I'm trying to execute the function from Visual Studio .NET IDE (through
> Database Explorer). Normally it will return the output in the "output"
> window of VS .NET IDE.
>
Don't use RETURN to ouptut results. Use OUTPUT parameters and keep RETURN
for what it is intended, i.e. error status. Make sure you set NOCOUNT ON.
Try:
CREATE PROCEDURE CreateNewCourse
@.CourseName NVARCHAR(256),
@.Duration Int,
@.Contents NVarChar(500),
@.Identity INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @.result INT
INSERT into CourseMaster
(
CourseName,Contents,
Duration
)
VALUES
(
@.CourseName,
@.Contents,
@.Duration
)
SET @.result =@.@.ERROR
IF @.result > 0
BEGIN
/* Error handling? */
EXEC usp_error_handler @.result, ... ;
END
SET @.Identity = SCOPE_IDENTITY()
RETURN @.result
GO
DECLARE @.i INT
EXEC CreateNewCourse
@.CourseName = '',
@.Duration = 0,
@.Contents = '',
@.Identity = @.i OUTPUT
SELECT @.i
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||It seem's it's a problem with the IDE and MSDE connection, or the way
the IDE returns the data from MSDE. I could run the same Stored Proc
from an application and retrieve the results in the return value.
Thanks,|||Does the table have an identity column?
"Anbu" <t_anbazhagan2001@.yahoo.co.in> wrote in message
news:1143606553.333820.230400@.t31g2000cwb.googlegroups.com...
> Hi All,
> I'm creating an appplication with some stored procedures. The stored
> procedure needs to return the @.@.Identity of the table's new record. The
> function has no errors and compiles without any warnings. The function
> does work fine some times (returning value). But most of the calls are
> failing and the function does not return proper value.
> CREATE PROCEDURE CreateNewCourse
> @.CourseName NVARCHAR(256),
> @.Duration Int,
> @.Contents NVarChar(500),
> AS
> DECLARE @.Identity int
> INSERT into CourseMaster
> (
> CourseName,Contents,
> Duration
> )
> VALUES
> (
> @.CourseName,
> @.Contents,
> @.Duration
> )
>
> SET @.Identity = SCOPE_IDENTITY()
> return @.Identity
> Any suggestions?
> TIA.
>

msde & indexing service

I have written an SQL Server 2000 Application using the
indexing service for full-text indexing a table. Try to
use MSDE instead of Sql Server
(SQLDMO) oSQLServer.FullTextService.isFullTextInstalled
returns always false
Has anyone an idea what to do?
Thanks Alex
Hi
It is not installed on MSDE. You need to use a full edition of SQL Server
From BOL:
"The Microsoft Search service itself is not installed during an installation
of SQL Server 2000 Desktop Engine (MSDE 2000). While this means that the
Microsoft Search service is not installed on Microsoft Windows 95, Windows
98, Windows NT Workstation, or Windows 2000 Professional clients, these
clients can make use of the service when connected to an instance of SQL
Server 2000 Standard Edition, SQL Server 2000 Developer Edition, or SQL
Server 2000 Enterprise Edition."
(Index: Keyword: MSDE 2000, Full-text Search)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"alex" <anonymous@.discussions.microsoft.com> wrote in message
news:03ce01c4e38a$e7dda0b0$a301280a@.phx.gbl...
> I have written an SQL Server 2000 Application using the
> indexing service for full-text indexing a table. Try to
> use MSDE instead of Sql Server
> (SQLDMO) oSQLServer.FullTextService.isFullTextInstalled
> returns always false
> Has anyone an idea what to do?
> Thanks Alex
>
|||
>--Original Message--
>Hi
>It is not installed on MSDE. You need to use a full
edition of SQL Server
>From BOL:
>"The Microsoft Search service itself is not installed
during an installation
>of SQL Server 2000 Desktop Engine (MSDE 2000). While this
means that the
>Microsoft Search service is not installed on Microsoft
Windows 95, Windows
>98, Windows NT Workstation, or Windows 2000 Professional
clients, these
>clients can make use of the service when connected to an
instance of SQL
>Server 2000 Standard Edition, SQL Server 2000 Developer
Edition, or SQL
>Server 2000 Enterprise Edition."
>(Index: Keyword: MSDE 2000, Full-text Search)
>Regards
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"alex" <anonymous@.discussions.microsoft.com> wrote in
message
>news:03ce01c4e38a$e7dda0b0$a301280a@.phx.gbl...
>
>.
>
|||Dear Mike,
thanks a lot for your reply. I havn't found any
information about this topic.
Alex
>--Original Message--
>Hi
>It is not installed on MSDE. You need to use a full
edition of SQL Server
>From BOL:
>"The Microsoft Search service itself is not installed
during an installation
>of SQL Server 2000 Desktop Engine (MSDE 2000). While this
means that the
>Microsoft Search service is not installed on Microsoft
Windows 95, Windows
>98, Windows NT Workstation, or Windows 2000 Professional
clients, these
>clients can make use of the service when connected to an
instance of SQL
>Server 2000 Standard Edition, SQL Server 2000 Developer
Edition, or SQL
>Server 2000 Enterprise Edition."
>(Index: Keyword: MSDE 2000, Full-text Search)
>Regards
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"alex" <anonymous@.discussions.microsoft.com> wrote in
message
>news:03ce01c4e38a$e7dda0b0$a301280a@.phx.gbl...
>
>.
>

msdb's sysjobs tables

Please list the cases when the contents of sysjobhistory table would be
deleted.
thanks
--
Cathy BOne way
from SQL server agent--> Properties-->Job System click on Clear Log
http://sqlservercode.blogspot.com/|||Yes,
But I am looking as to why there's no history on a particular job.
Any other ways?
--
Cathy B
"SQL" wrote:

> One way
> from SQL server agent--> Properties-->Job System click on Clear Log
> http://sqlservercode.blogspot.com/
>|||yes
right click on a job-->view job history-->clear all
http://sqlservercode.blogspot.com/|||Thank you.
I am not looking to clear the table.
I am looking for job history that I can't find.
The job was scheduled twice a week to run for a year.
There appears to be no history on it.
And I don't believe anybody here would go and clear the log.
Although anything is possible.
--
Cathy B
"SQL" wrote:

> yes
> right click on a job-->view job history-->clear all
> http://sqlservercode.blogspot.com/
>|||Is your maximum job history log size (rows) set to 1000 and you have
reached that number a year ago?|||when somebody does "delete from sysjobhistory"
Cathy Boehm wrote:
> Please list the cases when the contents of sysjobhistory table would be
> deleted.
> thanks
> --
> Cathy B

msdb's sysjobs tables

Please list the cases when the contents of sysjobhistory table would be
deleted.
thanks
Cathy B
One way
from SQL server agent--> Properties-->Job System click on Clear Log
http://sqlservercode.blogspot.com/
|||Yes,
But I am looking as to why there's no history on a particular job.
Any other ways?
Cathy B
"SQL" wrote:

> One way
> from SQL server agent--> Properties-->Job System click on Clear Log
> http://sqlservercode.blogspot.com/
>
|||yes
right click on a job-->view job history-->clear all
http://sqlservercode.blogspot.com/
|||Thank you.
I am not looking to clear the table.
I am looking for job history that I can't find.
The job was scheduled twice a week to run for a year.
There appears to be no history on it.
And I don't believe anybody here would go and clear the log.
Although anything is possible.
Cathy B
"SQL" wrote:

> yes
> right click on a job-->view job history-->clear all
> http://sqlservercode.blogspot.com/
>
|||Is your maximum job history log size (rows) set to 1000 and you have
reached that number a year ago?
|||when somebody does "delete from sysjobhistory"
Cathy Boehm wrote:
> Please list the cases when the contents of sysjobhistory table would be
> deleted.
> thanks
> --
> Cathy B

msdb's sysjobs tables

Please list the cases when the contents of sysjobhistory table would be
deleted.
thanks
--
Cathy BOne way
from SQL server agent--> Properties-->Job System click on Clear Log
http://sqlservercode.blogspot.com/|||Yes,
But I am looking as to why there's no history on a particular job.
Any other ways?
--
Cathy B
"SQL" wrote:
> One way
> from SQL server agent--> Properties-->Job System click on Clear Log
> http://sqlservercode.blogspot.com/
>|||yes
right click on a job-->view job history-->clear all
http://sqlservercode.blogspot.com/|||Thank you.
I am not looking to clear the table.
I am looking for job history that I can't find.
The job was scheduled twice a week to run for a year.
There appears to be no history on it.
And I don't believe anybody here would go and clear the log.
Although anything is possible.
--
Cathy B
"SQL" wrote:
> yes
> right click on a job-->view job history-->clear all
> http://sqlservercode.blogspot.com/
>|||Is your maximum job history log size (rows) set to 1000 and you have
reached that number a year ago?|||when somebody does "delete from sysjobhistory"
Cathy Boehm wrote:
> Please list the cases when the contents of sysjobhistory table would be
> deleted.
> thanks
> --
> Cathy B

msdb.dbo.sysdbmaintplan_history table question

Hi,
I am trying to check that a backup integrity check has verified a log
backup because it is being done over a network drive.
There is an entry verify backup for each backup performed in the
msdb.dbo.sysdbmaintplan_history table. Is the verify backup entry the
correct one I should be looking at to see if RESTORE VERIFYONLY
succeeded ?
My question is how do I tell that a verification found an error?
Thanks
--SamMaint plan executed RESTORE VERIFYONLY. It will produce errors of such are f
ound. Make sure you
handle the job so your are notified if errors occurs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<wildturtle@.gmail.com> wrote in message news:1147179633.997410.37830@.j73g2000cwa.googlegroup
s.com...
> Hi,
> I am trying to check that a backup integrity check has verified a log
> backup because it is being done over a network drive.
> There is an entry verify backup for each backup performed in the
> msdb.dbo.sysdbmaintplan_history table. Is the verify backup entry the
> correct one I should be looking at to see if RESTORE VERIFYONLY
> succeeded ?
> My question is how do I tell that a verification found an error?
> Thanks
> --Sam
>|||Thanks for your reply. My problem is the following:
the msdb.dbo.sysdbmaintplan_histor=ADy has two records: "Backup
transaction log" and "Verify Backup". They both have fields
called succeeded, error, and message. my question is, if a RESTORE
VERIFYONLY were to detect corruption, how would that be reflected
in the sysdbmaintplan_histor=ADy table ?
Many Thanks
--Sam|||I don't know, I'm afraid. I'd guess that Maint plans are smart enough to mar
k the execution as
failed and log the errors returned by RESTORE VERIFYONLY. I don't use maint
plans myself, as I
prefer more control over these things. Perhaps you can try, for instance doi
ng the backup to the
"nul" file name?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<wildturtle@.gmail.com> wrote in message
news:1147180762.992024.182010@.u72g2000cwu.googlegroups.com...
Thanks for your reply. My problem is the following:
the msdb.dbo.sysdbmaintplan_histor_y has two records: "Backup
transaction log" and "Verify Backup". They both have fields
called succeeded, error, and message. my question is, if a RESTORE
VERIFYONLY were to detect corruption, how would that be reflected
in the sysdbmaintplan_histor_y table ?
Many Thanks
--Sam

msdb.dbo.sysdbmaintplan_history table question

Hi,
I am trying to check that a backup integrity check has verified a log
backup because it is being done over a network drive.
There is an entry verify backup for each backup performed in the
msdb.dbo.sysdbmaintplan_history table. Is the verify backup entry the
correct one I should be looking at to see if RESTORE VERIFYONLY
succeeded ?
My question is how do I tell that a verification found an error?
Thanks
--SamMaint plan executed RESTORE VERIFYONLY. It will produce errors of such are found. Make sure you
handle the job so your are notified if errors occurs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<wildturtle@.gmail.com> wrote in message news:1147179633.997410.37830@.j73g2000cwa.googlegroups.com...
> Hi,
> I am trying to check that a backup integrity check has verified a log
> backup because it is being done over a network drive.
> There is an entry verify backup for each backup performed in the
> msdb.dbo.sysdbmaintplan_history table. Is the verify backup entry the
> correct one I should be looking at to see if RESTORE VERIFYONLY
> succeeded ?
> My question is how do I tell that a verification found an error?
> Thanks
> --Sam
>|||Thanks for your reply. My problem is the following:
the msdb.dbo.sysdbmaintplan_histor=ADy has two records: "Backup
transaction log" and "Verify Backup". They both have fields
called succeeded, error, and message. my question is, if a RESTORE
VERIFYONLY were to detect corruption, how would that be reflected
in the sysdbmaintplan_histor=ADy table ?
Many Thanks
--Sam|||I don't know, I'm afraid. I'd guess that Maint plans are smart enough to mark the execution as
failed and log the errors returned by RESTORE VERIFYONLY. I don't use maint plans myself, as I
prefer more control over these things. Perhaps you can try, for instance doing the backup to the
"nul" file name?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<wildturtle@.gmail.com> wrote in message
news:1147180762.992024.182010@.u72g2000cwu.googlegroups.com...
Thanks for your reply. My problem is the following:
the msdb.dbo.sysdbmaintplan_histor­y has two records: "Backup
transaction log" and "Verify Backup". They both have fields
called succeeded, error, and message. my question is, if a RESTORE
VERIFYONLY were to detect corruption, how would that be reflected
in the sysdbmaintplan_histor­y table ?
Many Thanks
--Sam

msdb..backupset.backup_finish_date -- changes from SQL2000?

It appears that the behavior has changed for the backupset table in the MSDB database between versions.

In SQL2000 backup_start_date & backup_finish_date were populated correctly. In 2005, backup_finish_date is the same as the backup_start_date value. Is this a bug or should I be looking for my backup timing elsewhere in 2005?

Thank you.

I can't repro the problem. It works fine for me.

I use this query to get a quick look at timings.

select top 100 database_name, backup_set_id, type, backup_finish_date, backup_start_date,
datediff (second,backup_start_date, backup_finish_date) secondsToComplete
,convert (bigint, backup_size / 1048576 ) sizeInMB

from msdb..backupset
where type = 'D'
order by backup_finish_date desc

If you can describe a repro, please let us know.

|||

It appears to work fine for database backups, I see timing differences in them. However for log backups (type = 'L') it appears that it's not changing, but I'm still investigating.

I was originally pursuing a long running t-log backup time and that's how I noticed this behavior. I've since discovered that if a backup device has a large # of backups appended to it, it dramaticly effects the time it takes to perform the backup. I was suprised to find that the finish time was being reported as the same as the start time when it clearly was taking much longer to perform the backup...

Currently (and with a small # of appends), my backups are averaging around 1/10th second so I'll have to try to dummy up some logspace and see if it's just that my backups are running quicker than the process can account for, or if it is indeed a bug.

I'll have more in a day or so once I can get some testing done. Thank you for looking into this.

|||

Your info helps.

Our start/finish times do NOT include the time it takes to open the media set and prepare it for writing.

The time only includes the time that is actually spent transferring data. This is the same logic as exists in sql2000.

For example, it may take many minutes to open a tape drive and seek to the end when appending data. That time is not included.

So if you want to also include that time, you'll need to add your own start/stop time.

This might already work if you use sqlagent jobs, and look up the start/top time in the job history.

Are you backing up to tape? That will always be relatively slow, and gets worse when appending backups.

If you have a lot of backups to append in a batch, such as a nightly job, you can use BACKUP WITH NOREWIND which avoids all the time to REWIND/SEEK to end that happens in such a scenario.

If you are backing up to disk, then having a lot of backups in the file shouldn't really matter...to a point.

1000's of backups WILL result in significant delay. The reason is that when setting up for the backup, we perform a synchronous read of all the marks before/after each backup set.

For disk backups, my recommendation is to prefer the use of a single filesystem directory, then write each backup into a separate disk file. That gives you better control over retention and space management. But I'd agree that it might be slightly more work to wrap this in your backup jobs. If you are using our management tools, they already do this by naming the log backups as ***.TRN where the *** includes the database name and timestamp of the backup.

Hope that helps.

MSDB Table User Permissions

Just out of curiosity, could someone point me towards a listing of the user permissions for the MSDB table? I have looked through BOL and on the internet and cannot find a good listing. An example would be something like...
dts_admin: <dts_admin description>

Thanks in advance.
-Kyle

Unfortunately msdb is being used by a number of different SQL Server services (built on top of SQL Server Engine, but shipped as part of SQL Server itself), and each type of service documents their own set of roles and usage (i.e. http://msdn2.microsoft.com/en-us/library/ms141053.aspx can help you with dts* principals), but there is no centralized documentation.

My recommendation right now would be to use msdn to search for any out of the box principal you want to know more about, and typically you will find the documentation for all related principals as well.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

msdb sysmail_attachments_transfer - very large, but 0 records

Our SQL server 2005 has a system table in the MSDB database called
sysmail_attachments_transfer. The management console summary report
shows almost 5 GB of data for the table with 0 records.
Table Name # Records Reserved Data
Indexes Unused
dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
KB 3840 KB
dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
KB 944 KB
This is puzzling. I would like to know more about this system table,
and to get back some of the space used, if possible. Does anyone know
anything about this? There doesn't seem to be any documentation
anywhere on it.
Rob Fisch
Kaz, Inc.
Hi
This looks like it holds the results of queries that are attached see
http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htm but I have not
found much more than this.
What does sp_spaceused give for this table?
Although this information should be correct you may want to try DBCC
UPDATEUSAGE to see if anything changes.
John
"rfisch@.gmail.com" wrote:

> Our SQL server 2005 has a system table in the MSDB database called
> sysmail_attachments_transfer. The management console summary report
> shows almost 5 GB of data for the table with 0 records.
> Table Name # Records Reserved Data
> Indexes Unused
> ----
> dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
> KB 3840 KB
> dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
> KB 944 KB
> This is puzzling. I would like to know more about this system table,
> and to get back some of the space used, if possible. Does anyone know
> anything about this? There doesn't seem to be any documentation
> anywhere on it.
> Rob Fisch
> Kaz, Inc.
>
|||Hi John,
sp_spaceused gave the same reading as the summary report.
DBCC UPDATEUSAGE didn't change anything to speak of.
Thanks for the link. It was interesting. There may be some clues in
there, but nothing jumps out at me.
Thanks for giving it a stab.
Rob
John Bell wrote:[vbcol=seagreen]
> Hi
> This looks like it holds the results of queries that are attached see
> http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htm but I have not
> found much more than this.
> What does sp_spaceused give for this table?
> Although this information should be correct you may want to try DBCC
> UPDATEUSAGE to see if anything changes.
> John
>
> "rfisch@.gmail.com" wrote:
|||Hi
I am not sure what has caused this. Have you tried DBCC CHECKDB?
John
On Nov 4, 1:55 am, "Rob Fisch" <rfi...@.gmail.com> wrote:[vbcol=seagreen]
> Hi John,
> sp_spaceused gave the same reading as the summary report.
> DBCC UPDATEUSAGE didn't change anything to speak of.
> Thanks for the link. It was interesting. There may be some clues in
> there, but nothing jumps out at me.
> Thanks for giving it a stab.
> Rob
>
> John Bell wrote:
>
>
>
>
|||Well I guess the good news is that DBCC CHECKDB reports "CHECKDB found
0 allocation errors and 0 consistency errors ".
John Bell wrote:[vbcol=seagreen]
> Hi
> I am not sure what has caused this. Have you tried DBCC CHECKDB?
> John
> On Nov 4, 1:55 am, "Rob Fisch" <rfi...@.gmail.com> wrote:
|||Hi ROb
What version are you using (SELECT @.@.VERSION) ?
I guess you could try manually deleting from/truncating the table even
though it is reporting no rows.
"Rob Fisch" wrote:

> Well I guess the good news is that DBCC CHECKDB reports "CHECKDB found
> 0 allocation errors and 0 consistency errors ".
>
>
> John Bell wrote:
>

msdb sysmail_attachments_transfer - very large, but 0 records

Our SQL server 2005 has a system table in the MSDB database called
sysmail_attachments_transfer. The management console summary report
shows almost 5 GB of data for the table with 0 records.
Table Name # Records Reserved Data
Indexes Unused
----
dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
KB 3840 KB
dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
KB 944 KB
This is puzzling. I would like to know more about this system table,
and to get back some of the space used, if possible. Does anyone know
anything about this? There doesn't seem to be any documentation
anywhere on it.
Rob Fisch
Kaz, Inc.Hi
This looks like it holds the results of queries that are attached see
http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htm but I have not
found much more than this.
What does sp_spaceused give for this table?
Although this information should be correct you may want to try DBCC
UPDATEUSAGE to see if anything changes.
John
"rfisch@.gmail.com" wrote:
> Our SQL server 2005 has a system table in the MSDB database called
> sysmail_attachments_transfer. The management console summary report
> shows almost 5 GB of data for the table with 0 records.
> Table Name # Records Reserved Data
> Indexes Unused
> ----
> dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
> KB 3840 KB
> dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
> KB 944 KB
> This is puzzling. I would like to know more about this system table,
> and to get back some of the space used, if possible. Does anyone know
> anything about this? There doesn't seem to be any documentation
> anywhere on it.
> Rob Fisch
> Kaz, Inc.
>|||Hi John,
sp_spaceused gave the same reading as the summary report.
DBCC UPDATEUSAGE didn't change anything to speak of.
Thanks for the link. It was interesting. There may be some clues in
there, but nothing jumps out at me.
Thanks for giving it a stab.
Rob
John Bell wrote:
> Hi
> This looks like it holds the results of queries that are attached see
> http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htm but I have not
> found much more than this.
> What does sp_spaceused give for this table?
> Although this information should be correct you may want to try DBCC
> UPDATEUSAGE to see if anything changes.
> John
>
> "rfisch@.gmail.com" wrote:
> > Our SQL server 2005 has a system table in the MSDB database called
> > sysmail_attachments_transfer. The management console summary report
> > shows almost 5 GB of data for the table with 0 records.
> >
> > Table Name # Records Reserved Data
> > Indexes Unused
> > ----
> > dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
> > KB 3840 KB
> > dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
> > KB 944 KB
> >
> > This is puzzling. I would like to know more about this system table,
> > and to get back some of the space used, if possible. Does anyone know
> > anything about this? There doesn't seem to be any documentation
> > anywhere on it.
> >
> > Rob Fisch
> > Kaz, Inc.
> >
> >|||Hi
I am not sure what has caused this. Have you tried DBCC CHECKDB?
John
On Nov 4, 1:55 am, "Rob Fisch" <rfi...@.gmail.com> wrote:
> Hi John,
> sp_spaceused gave the same reading as the summary report.
> DBCC UPDATEUSAGE didn't change anything to speak of.
> Thanks for the link. It was interesting. There may be some clues in
> there, but nothing jumps out at me.
> Thanks for giving it a stab.
> Rob
>
> John Bell wrote:
> > Hi
> > This looks like it holds the results of queries that are attached see
> >http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htmbut I have not
> > found much more than this.
> > What does sp_spaceused give for this table?
> > Although this information should be correct you may want to try DBCC
> > UPDATEUSAGE to see if anything changes.
> > John
> > "rfi...@.gmail.com" wrote:
> > > Our SQL server 2005 has a system table in the MSDB database called
> > > sysmail_attachments_transfer. The management console summary report
> > > shows almost 5 GB of data for the table with 0 records.
> > > Table Name # Records Reserved Data
> > > Indexes Unused
> > > ----
> > > dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
> > > KB 3840 KB
> > > dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
> > > KB 944 KB
> > > This is puzzling. I would like to know more about this system table,
> > > and to get back some of the space used, if possible. Does anyone know
> > > anything about this? There doesn't seem to be any documentation
> > > anywhere on it.
> > > Rob Fisch
> > > Kaz, Inc.- Hide quoted text -- Show quoted text -|||Well I guess the good news is that DBCC CHECKDB reports "CHECKDB found
0 allocation errors and 0 consistency errors ".
John Bell wrote:
> Hi
> I am not sure what has caused this. Have you tried DBCC CHECKDB?
> John
> On Nov 4, 1:55 am, "Rob Fisch" <rfi...@.gmail.com> wrote:
> > Hi John,
> >
> > sp_spaceused gave the same reading as the summary report.
> > DBCC UPDATEUSAGE didn't change anything to speak of.
> >
> > Thanks for the link. It was interesting. There may be some clues in
> > there, but nothing jumps out at me.
> >
> > Thanks for giving it a stab.
> > Rob
> >
> >
> >
> > John Bell wrote:
> > > Hi
> >
> > > This looks like it holds the results of queries that are attached see
> > >http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htmbut I have not
> > > found much more than this.
> >
> > > What does sp_spaceused give for this table?
> > > Although this information should be correct you may want to try DBCC
> > > UPDATEUSAGE to see if anything changes.
> >
> > > John
> >
> > > "rfi...@.gmail.com" wrote:
> >
> > > > Our SQL server 2005 has a system table in the MSDB database called
> > > > sysmail_attachments_transfer. The management console summary report
> > > > shows almost 5 GB of data for the table with 0 records.
> >
> > > > Table Name # Records Reserved Data
> > > > Indexes Unused
> > > > ----
> > > > dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
> > > > KB 3840 KB
> > > > dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
> > > > KB 944 KB
> >
> > > > This is puzzling. I would like to know more about this system table,
> > > > and to get back some of the space used, if possible. Does anyone know
> > > > anything about this? There doesn't seem to be any documentation
> > > > anywhere on it.
> >
> > > > Rob Fisch
> > > > Kaz, Inc.- Hide quoted text -- Show quoted text -|||Hi ROb
What version are you using (SELECT @.@.VERSION) ?
I guess you could try manually deleting from/truncating the table even
though it is reporting no rows.
"Rob Fisch" wrote:
> Well I guess the good news is that DBCC CHECKDB reports "CHECKDB found
> 0 allocation errors and 0 consistency errors ".
>
>
> John Bell wrote:
> > Hi
> >
> > I am not sure what has caused this. Have you tried DBCC CHECKDB?
> >
> > John
> >
> > On Nov 4, 1:55 am, "Rob Fisch" <rfi...@.gmail.com> wrote:
> > > Hi John,
> > >
> > > sp_spaceused gave the same reading as the summary report.
> > > DBCC UPDATEUSAGE didn't change anything to speak of.
> > >
> > > Thanks for the link. It was interesting. There may be some clues in
> > > there, but nothing jumps out at me.
> > >
> > > Thanks for giving it a stab.
> > > Rob
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > >
> > > > This looks like it holds the results of queries that are attached see
> > > >http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htmbut I have not
> > > > found much more than this.
> > >
> > > > What does sp_spaceused give for this table?
> > > > Although this information should be correct you may want to try DBCC
> > > > UPDATEUSAGE to see if anything changes.
> > >
> > > > John
> > >
> > > > "rfi...@.gmail.com" wrote:
> > >
> > > > > Our SQL server 2005 has a system table in the MSDB database called
> > > > > sysmail_attachments_transfer. The management console summary report
> > > > > shows almost 5 GB of data for the table with 0 records.
> > >
> > > > > Table Name # Records Reserved Data
> > > > > Indexes Unused
> > > > > ----
> > > > > dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
> > > > > KB 3840 KB
> > > > > dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
> > > > > KB 944 KB
> > >
> > > > > This is puzzling. I would like to know more about this system table,
> > > > > and to get back some of the space used, if possible. Does anyone know
> > > > > anything about this? There doesn't seem to be any documentation
> > > > > anywhere on it.
> > >
> > > > > Rob Fisch
> > > > > Kaz, Inc.- Hide quoted text -- Show quoted text -
>|||Or perhaps ALTER INDEX with REORGANIZE and LOB_COMPACTION? I don't deal that much with blobs, and
I'd guess that compaction shouldn't be necessary in this case, but it might be worth a try?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9406A927-93E5-4E82-B52F-745E6AE4CD6E@.microsoft.com...
> Hi ROb
> What version are you using (SELECT @.@.VERSION) ?
> I guess you could try manually deleting from/truncating the table even
> though it is reporting no rows.
> "Rob Fisch" wrote:
>> Well I guess the good news is that DBCC CHECKDB reports "CHECKDB found
>> 0 allocation errors and 0 consistency errors ".
>>
>>
>> John Bell wrote:
>> > Hi
>> >
>> > I am not sure what has caused this. Have you tried DBCC CHECKDB?
>> >
>> > John
>> >
>> > On Nov 4, 1:55 am, "Rob Fisch" <rfi...@.gmail.com> wrote:
>> > > Hi John,
>> > >
>> > > sp_spaceused gave the same reading as the summary report.
>> > > DBCC UPDATEUSAGE didn't change anything to speak of.
>> > >
>> > > Thanks for the link. It was interesting. There may be some clues in
>> > > there, but nothing jumps out at me.
>> > >
>> > > Thanks for giving it a stab.
>> > > Rob
>> > >
>> > >
>> > >
>> > > John Bell wrote:
>> > > > Hi
>> > >
>> > > > This looks like it holds the results of queries that are attached see
>> > > >http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htmbut I have not
>> > > > found much more than this.
>> > >
>> > > > What does sp_spaceused give for this table?
>> > > > Although this information should be correct you may want to try DBCC
>> > > > UPDATEUSAGE to see if anything changes.
>> > >
>> > > > John
>> > >
>> > > > "rfi...@.gmail.com" wrote:
>> > >
>> > > > > Our SQL server 2005 has a system table in the MSDB database called
>> > > > > sysmail_attachments_transfer. The management console summary report
>> > > > > shows almost 5 GB of data for the table with 0 records.
>> > >
>> > > > > Table Name # Records Reserved Data
>> > > > > Indexes Unused
>> > > > > ----
>> > > > > dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
>> > > > > KB 3840 KB
>> > > > > dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
>> > > > > KB 944 KB
>> > >
>> > > > > This is puzzling. I would like to know more about this system table,
>> > > > > and to get back some of the space used, if possible. Does anyone know
>> > > > > anything about this? There doesn't seem to be any documentation
>> > > > > anywhere on it.
>> > >
>> > > > > Rob Fisch
>> > > > > Kaz, Inc.- Hide quoted text -- Show quoted text -
>>

msdb sysmail_attachments_transfer - very large, but 0 records

Our SQL server 2005 has a system table in the MSDB database called
sysmail_attachments_transfer. The management console summary report
shows almost 5 GB of data for the table with 0 records.
Table Name # Records Reserved Data
Indexes Unused
----
dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
KB 3840 KB
dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
KB 944 KB
This is puzzling. I would like to know more about this system table,
and to get back some of the space used, if possible. Does anyone know
anything about this? There doesn't seem to be any documentation
anywhere on it.
Rob Fisch
Kaz, Inc.Hi
This looks like it holds the results of queries that are attached see
http://www.elsasoft.org/SUMMER.msdb...psenddbmail.htm but I have not
found much more than this.
What does sp_spaceused give for this table?
Although this information should be correct you may want to try DBCC
UPDATEUSAGE to see if anything changes.
John
"rfisch@.gmail.com" wrote:

> Our SQL server 2005 has a system table in the MSDB database called
> sysmail_attachments_transfer. The management console summary report
> shows almost 5 GB of data for the table with 0 records.
> Table Name # Records Reserved Data
> Indexes Unused
> ----
> dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
> KB 3840 KB
> dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
> KB 944 KB
> This is puzzling. I would like to know more about this system table,
> and to get back some of the space used, if possible. Does anyone know
> anything about this? There doesn't seem to be any documentation
> anywhere on it.
> Rob Fisch
> Kaz, Inc.
>|||Hi John,
sp_spaceused gave the same reading as the summary report.
DBCC UPDATEUSAGE didn't change anything to speak of.
Thanks for the link. It was interesting. There may be some clues in
there, but nothing jumps out at me.
Thanks for giving it a stab.
Rob
John Bell wrote:[vbcol=seagreen]
> Hi
> This looks like it holds the results of queries that are attached see
> http://www.elsasoft.org/SUMMER.msdb...psenddbmail.htm but I have not
> found much more than this.
> What does sp_spaceused give for this table?
> Although this information should be correct you may want to try DBCC
> UPDATEUSAGE to see if anything changes.
> John
>
> "rfisch@.gmail.com" wrote:
>|||Hi
I am not sure what has caused this. Have you tried DBCC CHECKDB?
John
On Nov 4, 1:55 am, "Rob Fisch" <rfi...@.gmail.com> wrote:[vbcol=seagreen]
> Hi John,
> sp_spaceused gave the same reading as the summary report.
> DBCC UPDATEUSAGE didn't change anything to speak of.
> Thanks for the link. It was interesting. There may be some clues in
> there, but nothing jumps out at me.
> Thanks for giving it a stab.
> Rob
>
> John Bell wrote:
>
>
>
>
>
>
>
>|||Well I guess the good news is that DBCC CHECKDB reports "CHECKDB found
0 allocation errors and 0 consistency errors ".
John Bell wrote:[vbcol=seagreen]
> Hi
> I am not sure what has caused this. Have you tried DBCC CHECKDB?
> John
> On Nov 4, 1:55 am, "Rob Fisch" <rfi...@.gmail.com> wrote:|||Hi ROb
What version are you using (SELECT @.@.VERSION) ?
I guess you could try manually deleting from/truncating the table even
though it is reporting no rows.
"Rob Fisch" wrote:

> Well I guess the good news is that DBCC CHECKDB reports "CHECKDB found
> 0 allocation errors and 0 consistency errors ".
>
>
> John Bell wrote:
>|||Or perhaps ALTER INDEX with REORGANIZE and LOB_COMPACTION? I don't deal that
much with blobs, and
I'd guess that compaction shouldn't be necessary in this case, but it might
be worth a try?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9406A927-93E5-4E82-B52F-745E6AE4CD6E@.microsoft.com...[vbcol=seagreen]
> Hi ROb
> What version are you using (SELECT @.@.VERSION) ?
> I guess you could try manually deleting from/truncating the table even
> though it is reporting no rows.
> "Rob Fisch" wrote:
>

msdb is corrupt

I have a user that corrupted msdb by trying to rebuild the indexes on the
backupfile table. I have removed the suspect flag so that the database is
available. And I have the scripts to completely rebuild msdb.
In the hopes of this problem being an allocation problem, I've tried
DBCC CHECKDB
DBCC CHECKALLOC
DBCC CHECKTABLE
DBCC REINDEX
all with the same result:
Server: Msg 823, Level 24, State 1, Line 1
I/O error (bad page ID) detected during read of BUF pointer = 0x1427de00,
page ptr = 0x448b0000, pageid = (0x1:0xf5f8), dbid = 4, status = 0x801, file
= E:\MSSQL7\DATA\msdbdata.mdf.
I also tried using bcp to get at the sysjobs table.
Besides restoring the database from a backup, which they don't have, is
there another way to get at the jobs and dts packages?I'd say its stuffed - maybe next time they'll back it up!
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:610925D1-F51F-486B-A056-F54CF4F088C8@.microsoft.com...
> I have a user that corrupted msdb by trying to rebuild the indexes on the
> backupfile table. I have removed the suspect flag so that the database is
> available. And I have the scripts to completely rebuild msdb.
> In the hopes of this problem being an allocation problem, I've tried
> DBCC CHECKDB
> DBCC CHECKALLOC
> DBCC CHECKTABLE
> DBCC REINDEX
> all with the same result:
> Server: Msg 823, Level 24, State 1, Line 1
> I/O error (bad page ID) detected during read of BUF pointer = 0x1427de00,
> page ptr = 0x448b0000, pageid = (0x1:0xf5f8), dbid = 4, status = 0x801,
file
> = E:\MSSQL7\DATA\msdbdata.mdf.
> I also tried using bcp to get at the sysjobs table.
> Besides restoring the database from a backup, which they don't have, is
> there another way to get at the jobs and dts packages?
>|||I tend to aggree. But before I give up, I was just trying to see if there wa
s
a solution. I remember in 6.5, the contents of pages could be changed with a
dbcc command. I understand that there's huge differences between 6.5 and 7.0
.
But since the database is basicly toast, I thought it would be worth a try.
"Mary Bray" wrote:

> I'd say its stuffed - maybe next time they'll back it up!
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:610925D1-F51F-486B-A056-F54CF4F088C8@.microsoft.com...
> file
>
>

msdb is corrupt

I have a user that corrupted msdb by trying to rebuild the indexes on the
backupfile table. I have removed the suspect flag so that the database is
available. And I have the scripts to completely rebuild msdb.
In the hopes of this problem being an allocation problem, I've tried
DBCC CHECKDB
DBCC CHECKALLOC
DBCC CHECKTABLE
DBCC REINDEX
all with the same result:
Server: Msg 823, Level 24, State 1, Line 1
I/O error (bad page ID) detected during read of BUF pointer = 0x1427de00,
page ptr = 0x448b0000, pageid = (0x1:0xf5f8), dbid = 4, status = 0x801, file
= E:\MSSQL7\DATA\msdbdata.mdf.
I also tried using bcp to get at the sysjobs table.
Besides restoring the database from a backup, which they don't have, is
there another way to get at the jobs and dts packages?
I'd say its stuffed - maybe next time they'll back it up!
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:610925D1-F51F-486B-A056-F54CF4F088C8@.microsoft.com...
> I have a user that corrupted msdb by trying to rebuild the indexes on the
> backupfile table. I have removed the suspect flag so that the database is
> available. And I have the scripts to completely rebuild msdb.
> In the hopes of this problem being an allocation problem, I've tried
> DBCC CHECKDB
> DBCC CHECKALLOC
> DBCC CHECKTABLE
> DBCC REINDEX
> all with the same result:
> Server: Msg 823, Level 24, State 1, Line 1
> I/O error (bad page ID) detected during read of BUF pointer = 0x1427de00,
> page ptr = 0x448b0000, pageid = (0x1:0xf5f8), dbid = 4, status = 0x801,
file
> = E:\MSSQL7\DATA\msdbdata.mdf.
> I also tried using bcp to get at the sysjobs table.
> Besides restoring the database from a backup, which they don't have, is
> there another way to get at the jobs and dts packages?
>
|||I tend to aggree. But before I give up, I was just trying to see if there was
a solution. I remember in 6.5, the contents of pages could be changed with a
dbcc command. I understand that there's huge differences between 6.5 and 7.0.
But since the database is basicly toast, I thought it would be worth a try.
"Mary Bray" wrote:

> I'd say its stuffed - maybe next time they'll back it up!
> "Jo" <Jo@.discussions.microsoft.com> wrote in message
> news:610925D1-F51F-486B-A056-F54CF4F088C8@.microsoft.com...
> file
>
>