Error 207: dbcc

Showing posts with label dbcc. Show all posts

Thursday, March 29, 2012

Error 925 DB already open when i refresh current activity

I have 20 DB's on my server.
When i set a DB1 to single_user and Run DBCC ShrinkDatabase command.
Then start EM and refresh current activity I get an
Error 925: Database DB1 already open and can only have one user at a time.
I cannot see anything except (no items).
On other servers i set single user and i can view and refresh the current
activity
I don't get it why can't i view current activity on other databases?
Thanks for your helpAlex,
Try closing the connection via Query Analyzer and try again.
HTH
Jerry
"alex sadykov" <alexsadykov@.gmail.com> wrote in message
news:%239FX%23tMgGHA.5088@.TK2MSFTNGP02.phx.gbl...
>I have 20 DB's on my server.
> When i set a DB1 to single_user and Run DBCC ShrinkDatabase command.
> Then start EM and refresh current activity I get an
> Error 925: Database DB1 already open and can only have one user at a time.
>
> I cannot see anything except (no items).
> On other servers i set single user and i can view and refresh the current
> activity
> I don't get it why can't i view current activity on other databases?
> Thanks for your help
>|||Also you could try running sp_who to check the current users and processes
accessing the database.
Hope this helps
Vishal Gandhi

Error 925 DB already open when i refresh current activity

Tuesday, March 27, 2012

Error 8965

I'm getting this error message ONLY SOMETIMES after a DBCC (I'd feel better
if it showed up on a regular basis). I haven't found much dealing with this
. The table is used to store small videos. Any useful suggestions (somethi
ng more than "restore from
a backup" or "call Microsoft") would be greatly appreciated.
I'm on Windows 2000 SP 4 and SQL 2000 SP 3.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8965: [Microsoft]&#
91;ODBC SQL Server Driver][SQL Server]Table error: Object ID %. The text
, ntext, or image node at page (1:8544241), slot 0, text ID 17459904512 is r
eferenced by page (1:8544242), slot 0,
but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Obje
ct ID %. The text, ntext, or image node at page (1:8544243), slot 0, text ID
17459904512 is referenced by page (1:8544242), slot 0, but was not seen in
the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Obje
ct ID %. The text, ntext, or image node at page (1:8544244), slot 0, text ID
17459904512 is referenced by page (1:8544242), slot 0, but was not seen in
the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 a
llocation errors and 3 consistency errors in table 'Table_Name' (object ID %
).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 a
llocation errors and 3 consistency errors in database 'Database_Name'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data
_loss is the minimum repair level for the errors found by DBCC CHECKDB (Data
base_Name ).The error message is indicating a corruption in the table. The root cause
of the corruption is unknown at this stage and will require more
information.
One explanation as to why the error only appears sometimes and not always
is if you run the dbcc checkdb command with any of the repair options then
it's likely that the corruption is repaired and reappears sometime later
again.
HTH
Agnes Panosian
SQL Server Supportsql

Error 8965

I'm getting this error message ONLY SOMETIMES after a DBCC (I'd feel better if it showed up on a regular basis). I haven't found much dealing with this. The table is used to store small videos. Any useful suggestions (something more than "restore from
a backup" or "call Microsoft") would be greatly appreciated.
I'm on Windows 2000 SP 4 and SQL 2000 SP 3.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8965: [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID %. The text, ntext, or image node at page (1:8544241), slot 0, text ID 17459904512 is referenced by page (1:8544242), slot 0,
but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID %. The text, ntext, or image node at page (1:8544243), slot 0, text ID 17459904512 is referenced by page (1:8544242), slot 0, but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID %. The text, ntext, or image node at page (1:8544244), slot 0, text ID 17459904512 is referenced by page (1:8544242), slot 0, but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in table 'Table_Name' (object ID %).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in database 'Database_Name'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Database_Name ).
The error message is indicating a corruption in the table. The root cause
of the corruption is unknown at this stage and will require more
information.
One explanation as to why the error only appears sometimes and not always
is if you run the dbcc checkdb command with any of the repair options then
it's likely that the corruption is repaired and reappears sometime later
again.
HTH
Agnes Panosian
SQL Server Support

Error 8965

I'm getting this error message ONLY SOMETIMES after a DBCC (I'd feel better if it showed up on a regular basis). I haven't found much dealing with this. The table is used to store small videos. Any useful suggestions (something more than "restore from a backup" or "call Microsoft") would be greatly appreciated
I'm on Windows 2000 SP 4 and SQL 2000 SP 3
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8965: [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID %. The text, ntext, or image node at page (1:8544241), slot 0, text ID 17459904512 is referenced by page (1:8544242), slot 0, but was not seen in the scan
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID %. The text, ntext, or image node at page (1:8544243), slot 0, text ID 17459904512 is referenced by page (1:8544242), slot 0, but was not seen in the scan
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID %. The text, ntext, or image node at page (1:8544244), slot 0, text ID 17459904512 is referenced by page (1:8544242), slot 0, but was not seen in the scan
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in table 'Table_Name' (object ID %)
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in database 'Database_Name'
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Database_Name )The error message is indicating a corruption in the table. The root cause
of the corruption is unknown at this stage and will require more
information.
One explanation as to why the error only appears sometimes and not always
is if you run the dbcc checkdb command with any of the repair options then
it's likely that the corruption is repaired and reappears sometime later
again.
HTH
Agnes Panosian
SQL Server Support

error 8906

Hi,

I have sql 7.o server and lately after running dbcc checkdb I saw these errors. These errors have not come in the way of functionality of application and database. I have run repair_allow_data_loss option couple of times with no sucess. Since these allocation errors are not associated with any single object can we go on with these errors in DB or how do I resolve these errors.

Thx,

Bxmakin

Server: Msg 8906, Level 16, State 1, Line 0
Page (1:656) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 100_PCT_FULL'.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:2464) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:12720) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:17144) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:223008) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:310776) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8906, Level 16, State 1, Line 0
Page (1:322715) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:315432), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 50_PCT_FULL'.
DBCC results for 'onbase'.
CHECKDB found 7 allocation errors and 0 consistency errors not associated with any single object.Do you have any text or image columns in your database? If so, what is the biggest BLOB file size for a particular row out of all the tables in your db?

I'm wondering this because those errors are related to extents and heaps.

...just a thought.

Kael|||Kael,

Thanks for reponding to my dilemma. I am not a DBA by any strech of imagination where I am caught between our pplication people and Microsoft. They have suggested that I restore from a backup and I stand to loose lot of work. I did restore a recent backup on a test ma chine with same allocation errors. I got suspicious about this one table which stores the trancations logs i.e. "Person X looked at a Y document at Certain time etc.." . I know these tables tend to grow really large and cause problems. I ran checktable etc. with no luck. Finally I dropped the table and ran checkdb and it cleared all the allocation errors. I donot know how to answer to your question. If you donot mind sending me a command or two to find out what you pointed out. It will be immensely helpful.

I did try running dbreindex on the table and it didnot help. Do you think if I run dbreindex with new fill factor it may help.

Thanks in advance.

Bxmakin

Friday, February 24, 2012

Error 2571 - not permission to run DBCC TRACEON

Hi,
I am trying to connect to SQL Server 2005 via Excel/ODBC. (ODBC Test IS
successfull) and get the following error after entering Login ID and Password:
Connection failed:
SQLState: '37000'
SQL Server Error: 2571
.....does not have permission to run DBCC TRACEON
Security setup on SQL Server seems to be fine (I can connect to SQL Server
from Reporting Services on same Login/Password).
Any idears?
Best regards,
Soren
To run DBCC Traceon you requires membership in the sysadmin fixed
server role.
Regards
Amish
|||That did it! Thanks !!!
"amish" wrote:

> To run DBCC Traceon you requires membership in the sysadmin fixed
> server role.
>
> Regards
> Amish
>
|||I had the same problem, but there are multiple users who use that
spreadsheet using Windows authentication. I did not want any of them to
be sysadmins. They only had SLECT permissions to certain tables. In my
case, deleting the Application name (from the SQL Server Login dialog)
fixed it. You need to click the Options button on the dialog to see
this. The application name seems to be an issue if it has certain
characters (like =AE in Microsoft=AE Query). Depending on the MS Office
version, the default value may be Microsoft=AE Query/MS Office XP/?.
You can also programmatically change the connection string (and even
the command text if necessary). Here is a sample.
'ChangeConnection
Sub ChangeConnection()
Dim sh As Worksheet
Dim qt As QueryTable
Dim sConnection As String
For Each sh In ActiveWorkbook.Sheets
For Each qt In sh.QueryTables
'Show current connectionstring
MsgBox ("Tab: " & sh.Name & vbCr & " Current Connection: "
& vbCr & qt.Connection)
'Show current query
MsgBox ("Tab: " & sh.Name & vbCr & "Current Query: " & vbCr
& qt.CommandText)
'Change Connection
qt.Connection =3D "ODBC;DRIVER=3DSQL
Server;SERVER=3Dmyserver;DATABASE=3DmyDB;Trusted_C onnection=3DYes;APP=3DExc=
el_TopCustomers;"
'Change Qry text (the owner for instance)
qt.CommandText =3D Replace(qt.CommandText, "DB.dbo.",
"DB.Me")
qt.SavePassword =3D False
'Show new connectionstring
MsgBox ("Tab: " & sh.Name & vbCr & "Connection: " & vbCr &
qt.Connection)
'Show new query
MsgBox ("Tab: " & sh.Name & vbCr & "Query: " & vbCr &
qt.CommandText)
Next qt
Next sh
End Sub
|||I have the exact same problem using Excel 97/MS-Query8. I tried your
suggestion, it did not work. I am still getting the error.
Ade
"preddy" wrote:

> I had the same problem, but there are multiple users who use that
> spreadsheet using Windows authentication. I did not want any of them to
> be sysadmins. They only had SLECT permissions to certain tables. In my
> case, deleting the Application name (from the SQL Server Login dialog)
> fixed it. You need to click the Options button on the dialog to see
> this. The application name seems to be an issue if it has certain
> characters (like ? in Microsoft? Query). Depending on the MS Office
> version, the default value may be Microsoft? Query/MS Office XP/?.
> You can also programmatically change the connection string (and even
> the command text if necessary). Here is a sample.
> 'ChangeConnection
> Sub ChangeConnection()
> Dim sh As Worksheet
> Dim qt As QueryTable
> Dim sConnection As String
> For Each sh In ActiveWorkbook.Sheets
> For Each qt In sh.QueryTables
> 'Show current connectionstring
> MsgBox ("Tab: " & sh.Name & vbCr & " Current Connection: "
> & vbCr & qt.Connection)
> 'Show current query
> MsgBox ("Tab: " & sh.Name & vbCr & "Current Query: " & vbCr
> & qt.CommandText)
> 'Change Connection
> qt.Connection = "ODBC;DRIVER=SQL
> Server;SERVER=myserver;DATABASE=myDB;Trusted_Conne ction=Yes;APP=Excel_TopCustomers;"
> 'Change Qry text (the owner for instance)
> qt.CommandText = Replace(qt.CommandText, "DB.dbo.",
> "DB.Me")
> qt.SavePassword = False
> 'Show new connectionstring
> MsgBox ("Tab: " & sh.Name & vbCr & "Connection: " & vbCr &
> qt.Connection)
> 'Show new query
> MsgBox ("Tab: " & sh.Name & vbCr & "Query: " & vbCr &
> qt.CommandText)
> Next qt
> Next sh
> End Sub
>
|||Sorry my mistake, it does work. You have to replace the string "Microsoft?
Query" with something else.
Ade
"Ade" wrote:
[vbcol=seagreen]
> I have the exact same problem using Excel 97/MS-Query8. I tried your
> suggestion, it did not work. I am still getting the error.
> --
> Ade
>
> "preddy" wrote:

Sunday, February 19, 2012

Error 2571 - not permission to run DBCC TRACEON

Hi,
I am trying to connect to SQL Server 2005 via Excel/ODBC. (ODBC Test IS
successfull) and get the following error after entering Login ID and Passwor
d:
Connection failed:
SQLState: '37000'
SQL Server Error: 2571
....does not have permission to run DBCC TRACEON
Security setup on SQL Server seems to be fine (I can connect to SQL Server
from Reporting Services on same Login/Password).
Any idears?
Best regards,
SorenTo run DBCC Traceon you requires membership in the sysadmin fixed
server role.
Regards
Amish|||That did it! Thanks !!!
"amish" wrote:

> To run DBCC Traceon you requires membership in the sysadmin fixed
> server role.
>
> Regards
> Amish
>|||I had the same problem, but there are multiple users who use that
spreadsheet using Windows authentication. I did not want any of them to
be sysadmins. They only had SLECT permissions to certain tables. In my
case, deleting the Application name (from the SQL Server Login dialog)
fixed it. You need to click the Options button on the dialog to see
this. The application name seems to be an issue if it has certain
characters (like =AE in Microsoft=AE Query). Depending on the MS Office
version, the default value may be Microsoft=AE Query/MS Office XP/'.
You can also programmatically change the connection string (and even
the command text if necessary). Here is a sample.
'ChangeConnection
Sub ChangeConnection()
Dim sh As Worksheet
Dim qt As QueryTable
Dim sConnection As String
For Each sh In ActiveWorkbook.Sheets
For Each qt In sh.QueryTables
'Show current connectionstring
MsgBox ("Tab: " & sh.Name & vbCr & " Current Connection: "
& vbCr & qt.Connection)
'Show current query
MsgBox ("Tab: " & sh.Name & vbCr & "Current Query: " & vbCr
& qt.CommandText)
'Change Connection
qt.Connection =3D "ODBC;DRIVER=3DSQL
Server;SERVER=3Dmyserver;DATABASE=3DmyDB
;Trusted_Connection=3DYes;APP=3DExc=
el_TopCustomers;"
'Change Qry text (the owner for instance)
qt.CommandText =3D Replace(qt.CommandText, "DB.dbo.",
"DB.Me")
qt.SavePassword =3D False
'Show new connectionstring
MsgBox ("Tab: " & sh.Name & vbCr & "Connection: " & vbCr &
qt.Connection)
'Show new query
MsgBox ("Tab: " & sh.Name & vbCr & "Query: " & vbCr &
qt.CommandText)
Next qt
Next sh
End Sub|||I have the exact same problem using Excel 97/MS-Query8. I tried your
suggestion, it did not work. I am still getting the error.
--
Ade
"preddy" wrote:

> I had the same problem, but there are multiple users who use that
> spreadsheet using Windows authentication. I did not want any of them to
> be sysadmins. They only had SLECT permissions to certain tables. In my
> case, deleting the Application name (from the SQL Server Login dialog)
> fixed it. You need to click the Options button on the dialog to see
> this. The application name seems to be an issue if it has certain
> characters (like ? in Microsoft? Query). Depending on the MS Office
> version, the default value may be Microsoft? Query/MS Office XP/'.
> You can also programmatically change the connection string (and even
> the command text if necessary). Here is a sample.
> 'ChangeConnection
> Sub ChangeConnection()
> Dim sh As Worksheet
> Dim qt As QueryTable
> Dim sConnection As String
> For Each sh In ActiveWorkbook.Sheets
> For Each qt In sh.QueryTables
> 'Show current connectionstring
> MsgBox ("Tab: " & sh.Name & vbCr & " Current Connection: "
> & vbCr & qt.Connection)
> 'Show current query
> MsgBox ("Tab: " & sh.Name & vbCr & "Current Query: " & vbCr
> & qt.CommandText)
> 'Change Connection
> qt.Connection = "ODBC;DRIVER=SQL
> Server;SERVER=myserver;DATABASE=myDB;Tru
sted_Connection=Yes;APP=Excel_TopC
ustomers;"
> 'Change Qry text (the owner for instance)
> qt.CommandText = Replace(qt.CommandText, "DB.dbo.",
> "DB.Me")
> qt.SavePassword = False
> 'Show new connectionstring
> MsgBox ("Tab: " & sh.Name & vbCr & "Connection: " & vbCr &
> qt.Connection)
> 'Show new query
> MsgBox ("Tab: " & sh.Name & vbCr & "Query: " & vbCr &
> qt.CommandText)
> Next qt
> Next sh
> End Sub
>|||Sorry my mistake, it does work. You have to replace the string "Microsoft?
Query" with something else.
--
Ade
"Ade" wrote:
[vbcol=seagreen]
> I have the exact same problem using Excel 97/MS-Query8. I tried your
> suggestion, it did not work. I am still getting the error.
> --
> Ade
>
> "preddy" wrote:
>

Error 2571 - not permission to run DBCC TRACEON

Hi,
I am trying to connect to SQL Server 2005 via Excel/ODBC. (ODBC Test IS
successfull) and get the following error after entering Login ID and Password:
Connection failed:
SQLState: '37000'
SQL Server Error: 2571
....does not have permission to run DBCC TRACEON
Security setup on SQL Server seems to be fine (I can connect to SQL Server
from Reporting Services on same Login/Password).
Any idears?
Best regards,
SorenTo run DBCC Traceon you requires membership in the sysadmin fixed
server role.
Regards
Amish|||That did it! Thanks !!!
"amish" wrote:
> To run DBCC Traceon you requires membership in the sysadmin fixed
> server role.
>
> Regards
> Amish
>|||I had the same problem, but there are multiple users who use that
spreadsheet using Windows authentication. I did not want any of them to
be sysadmins. They only had SLECT permissions to certain tables. In my
case, deleting the Application name (from the SQL Server Login dialog)
fixed it. You need to click the Options button on the dialog to see
this. The application name seems to be an issue if it has certain
characters (like =AE in Microsoft=AE Query). Depending on the MS Office
version, the default value may be Microsoft=AE Query/MS Office XP/'.
You can also programmatically change the connection string (and even
the command text if necessary). Here is a sample.
'ChangeConnection
Sub ChangeConnection()
Dim sh As Worksheet
Dim qt As QueryTable
Dim sConnection As String
For Each sh In ActiveWorkbook.Sheets
For Each qt In sh.QueryTables
'Show current connectionstring
MsgBox ("Tab: " & sh.Name & vbCr & " Current Connection: "
& vbCr & qt.Connection)
'Show current query
MsgBox ("Tab: " & sh.Name & vbCr & "Current Query: " & vbCr
& qt.CommandText)
'Change Connection
qt.Connection =3D "ODBC;DRIVER=3DSQL
Server;SERVER=3Dmyserver;DATABASE=3DmyDB;Trusted_Connection=3DYes;APP=3DExc=el_TopCustomers;"
'Change Qry text (the owner for instance)
qt.CommandText =3D Replace(qt.CommandText, "DB.dbo.",
"DB.Me")
qt.SavePassword =3D False
'Show new connectionstring
MsgBox ("Tab: " & sh.Name & vbCr & "Connection: " & vbCr &
qt.Connection)
'Show new query
MsgBox ("Tab: " & sh.Name & vbCr & "Query: " & vbCr &
qt.CommandText)
Next qt
Next sh
End Sub|||I have the exact same problem using Excel 97/MS-Query8. I tried your
suggestion, it did not work. I am still getting the error.
--
Ade
"preddy" wrote:
> I had the same problem, but there are multiple users who use that
> spreadsheet using Windows authentication. I did not want any of them to
> be sysadmins. They only had SLECT permissions to certain tables. In my
> case, deleting the Application name (from the SQL Server Login dialog)
> fixed it. You need to click the Options button on the dialog to see
> this. The application name seems to be an issue if it has certain
> characters (like Â® in MicrosoftÂ® Query). Depending on the MS Office
> version, the default value may be MicrosoftÂ® Query/MS Office XP/'.
> You can also programmatically change the connection string (and even
> the command text if necessary). Here is a sample.
> 'ChangeConnection
> Sub ChangeConnection()
> Dim sh As Worksheet
> Dim qt As QueryTable
> Dim sConnection As String
> For Each sh In ActiveWorkbook.Sheets
> For Each qt In sh.QueryTables
> 'Show current connectionstring
> MsgBox ("Tab: " & sh.Name & vbCr & " Current Connection: "
> & vbCr & qt.Connection)
> 'Show current query
> MsgBox ("Tab: " & sh.Name & vbCr & "Current Query: " & vbCr
> & qt.CommandText)
> 'Change Connection
> qt.Connection = "ODBC;DRIVER=SQL
> Server;SERVER=myserver;DATABASE=myDB;Trusted_Connection=Yes;APP=Excel_TopCustomers;"
> 'Change Qry text (the owner for instance)
> qt.CommandText = Replace(qt.CommandText, "DB.dbo.",
> "DB.Me")
> qt.SavePassword = False
> 'Show new connectionstring
> MsgBox ("Tab: " & sh.Name & vbCr & "Connection: " & vbCr &
> qt.Connection)
> 'Show new query
> MsgBox ("Tab: " & sh.Name & vbCr & "Query: " & vbCr &
> qt.CommandText)
> Next qt
> Next sh
> End Sub
>|||Sorry my mistake, it does work. You have to replace the string "MicrosoftÂ®
Query" with something else.
--
Ade
"Ade" wrote:
> I have the exact same problem using Excel 97/MS-Query8. I tried your
> suggestion, it did not work. I am still getting the error.
> --
> Ade
>
> "preddy" wrote:
> > I had the same problem, but there are multiple users who use that
> > spreadsheet using Windows authentication. I did not want any of them to
> > be sysadmins. They only had SLECT permissions to certain tables. In my
> > case, deleting the Application name (from the SQL Server Login dialog)
> > fixed it. You need to click the Options button on the dialog to see
> > this. The application name seems to be an issue if it has certain
> > characters (like Â® in MicrosoftÂ® Query). Depending on the MS Office
> > version, the default value may be MicrosoftÂ® Query/MS Office XP/'.
> >
> > You can also programmatically change the connection string (and even
> > the command text if necessary). Here is a sample.
> >
> > 'ChangeConnection
> > Sub ChangeConnection()
> >
> > Dim sh As Worksheet
> > Dim qt As QueryTable
> >
> > Dim sConnection As String
> >
> > For Each sh In ActiveWorkbook.Sheets
> > For Each qt In sh.QueryTables
> >
> > 'Show current connectionstring
> > MsgBox ("Tab: " & sh.Name & vbCr & " Current Connection: "
> > & vbCr & qt.Connection)
> > 'Show current query
> > MsgBox ("Tab: " & sh.Name & vbCr & "Current Query: " & vbCr
> > & qt.CommandText)
> >
> > 'Change Connection
> > qt.Connection = "ODBC;DRIVER=SQL
> > Server;SERVER=myserver;DATABASE=myDB;Trusted_Connection=Yes;APP=Excel_TopCustomers;"
> >
> > 'Change Qry text (the owner for instance)
> > qt.CommandText = Replace(qt.CommandText, "DB.dbo.",
> > "DB.Me")
> >
> > qt.SavePassword = False
> >
> > 'Show new connectionstring
> > MsgBox ("Tab: " & sh.Name & vbCr & "Connection: " & vbCr &
> > qt.Connection)
> > 'Show new query
> > MsgBox ("Tab: " & sh.Name & vbCr & "Query: " & vbCr &
> > qt.CommandText)
> >
> > Next qt
> > Next sh
> >
> > End Sub
> >
> >

Error 2535

Hi All
For the pass two weeks, the DBCC CHECKTABLE with REPAIR_REBUILD option is coming out with error 2535 on certain tables. We are using SQL7 with Serive Pack 4. Please help
Thanks
MaryDear Mary
Explanatio
The DBCC CHECKALLOC statement detected a mismatch in the object ID between an allocation structure (extent) and sysindexes
Note Occasionally, DBCC CHECKALLOC reports this error when no real error condition exists. Execute DBCC CHECKALLOC in single-user mode if you suspect the 2535 error is incorrect
Actio
Follow these steps:
Examine the index ID associated with the page number in the message to determine whether the error occurred on the table data or on an index.
Restore the database:
If the object ID is less than or equal to 100, the error is on a system table. Restore the database from a clean backup
If the object ID is greater than 100, the error is on a user table
If this error occurs on table data (the index ID = 1), restore the database from a clean backup
If the error occurs on an index, you can usually correct it by dropping and re-creating the index. If dropping and re-creating the index is not feasible, or if you cannot drop the index, contact your primary support provider for assistance.
If the problem persists, contact your primary support provider for assistance. Have the output of the appropriate DBCC statements available for review
Faheem latif
NETSOL|||Dear Mary
Explanatio
The DBCC CHECKALLOC statement detected a mismatch in the object ID between an allocation structure (extent) and sysindexes
Note Occasionally, DBCC CHECKALLOC reports this error when no real error condition exists. Execute DBCC CHECKALLOC in single-user mode if you suspect the 2535 error is incorrect
Actio
Follow these steps:
Examine the index ID associated with the page number in the message to determine whether the error occurred on the table data or on an index.
Restore the database:
If the object ID is less than or equal to 100, the error is on a system table. Restore the database from a clean backup
If the object ID is greater than 100, the error is on a user table
If this error occurs on table data (the index ID = 1), restore the database from a clean backup
If the error occurs on an index, you can usually correct it by dropping and re-creating the index. If dropping and re-creating the index is not feasible, or if you cannot drop the index, contact your primary support provider for assistance.
If the problem persists, contact your primary support provider for assistance. Have the output of the appropriate DBCC statements available for review
Faheem Lati
NETSOL

Error 2535

Hi All,
For the pass two weeks, the DBCC CHECKTABLE with REPAIR_REBUILD option is coming out with error 2535 on certain tables. We are using SQL7 with Serive Pack 4. Please help.
Thanks,
Mary
Dear Mary,
Explanation
The DBCC CHECKALLOC statement detected a mismatch in the object ID between an allocation structure (extent) and sysindexes.
Note Occasionally, DBCC CHECKALLOC reports this error when no real error condition exists. Execute DBCC CHECKALLOC in single-user mode if you suspect the 2535 error is incorrect.
Action
Follow these steps:
Examine the index ID associated with the page number in the message to determine whether the error occurred on the table data or on an index.
Restore the database:
If the object ID is less than or equal to 100, the error is on a system table. Restore the database from a clean backup.
If the object ID is greater than 100, the error is on a user table.
If this error occurs on table data (the index ID = 1), restore the database from a clean backup.
If the error occurs on an index, you can usually correct it by dropping and re-creating the index. If dropping and re-creating the index is not feasible, or if you cannot drop the index, contact your primary support provider for assistance.
If the problem persists, contact your primary support provider for assistance. Have the output of the appropriate DBCC statements available for review.
Faheem latif
NETSOL
|||Dear Mary,
Explanation
The DBCC CHECKALLOC statement detected a mismatch in the object ID between an allocation structure (extent) and sysindexes.
Note Occasionally, DBCC CHECKALLOC reports this error when no real error condition exists. Execute DBCC CHECKALLOC in single-user mode if you suspect the 2535 error is incorrect.
Action
Follow these steps:
Examine the index ID associated with the page number in the message to determine whether the error occurred on the table data or on an index.
Restore the database:
If the object ID is less than or equal to 100, the error is on a system table. Restore the database from a clean backup.
If the object ID is greater than 100, the error is on a user table.
If this error occurs on table data (the index ID = 1), restore the database from a clean backup.
If the error occurs on an index, you can usually correct it by dropping and re-creating the index. If dropping and re-creating the index is not feasible, or if you cannot drop the index, contact your primary support provider for assistance.
If the problem persists, contact your primary support provider for assistance. Have the output of the appropriate DBCC statements available for review.
Faheem Latif
NETSOL
|||Faheem,
The object ID is greater than 100, see below, but what are the steps to fix the user table? Do I use bcp to outload the data, drop table, then load the data back?
Table Corrupt: Page (1:566656) is allocated to object ID 1189579276, index ID 255, not to object ID 798625888, index ID 5 found in page header.
Mary

Error 2535

Hi All,
For the pass two weeks, the DBCC CHECKTABLE with REPAIR_REBUILD option is co
ming out with error 2535 on certain tables. We are using SQL7 with Serive Pa
ck 4. Please help.
Thanks,
MaryDear Mary,
Explanation
The DBCC CHECKALLOC statement detected a mismatch in the object ID between a
n allocation structure (extent) and sysindexes.
Note Occasionally, DBCC CHECKALLOC reports this error when no real error co
ndition exists. Execute DBCC CHECKALLOC in single-user mode if you suspect t
he 2535 error is incorrect.
Action
Follow these steps:
Examine the index ID associated with the page number in the message to deter
mine whether the error occurred on the table data or on an index.
Restore the database:
If the object ID is less than or equal to 100, the error is on a system tabl
e. Restore the database from a clean backup.
If the object ID is greater than 100, the error is on a user table.
If this error occurs on table data (the index ID = 1), restore the database
from a clean backup.
If the error occurs on an index, you can usually correct it by dropping and
re-creating the index. If dropping and re-creating the index is not feasible
, or if you cannot drop the index, contact your primary support provider for
assistance.
If the problem persists, contact your primary support provider for assistanc
e. Have the output of the appropriate DBCC statements available for review.
Faheem latif
NETSOL|||Dear Mary,
Explanation
The DBCC CHECKALLOC statement detected a mismatch in the object ID between a
n allocation structure (extent) and sysindexes.
Note Occasionally, DBCC CHECKALLOC reports this error when no real error co
ndition exists. Execute DBCC CHECKALLOC in single-user mode if you suspect t
he 2535 error is incorrect.
Action
Follow these steps:
Examine the index ID associated with the page number in the message to deter
mine whether the error occurred on the table data or on an index.
Restore the database:
If the object ID is less than or equal to 100, the error is on a system tabl
e. Restore the database from a clean backup.
If the object ID is greater than 100, the error is on a user table.
If this error occurs on table data (the index ID = 1), restore the database
from a clean backup.
If the error occurs on an index, you can usually correct it by dropping and
re-creating the index. If dropping and re-creating the index is not feasible
, or if you cannot drop the index, contact your primary support provider for
assistance.
If the problem persists, contact your primary support provider for assistanc
e. Have the output of the appropriate DBCC statements available for review.
Faheem Latif
NETSOL|||Faheem,
The object ID is greater than 100, see below, but what are the steps to fix
the user table? Do I use bcp to outload the data, drop table, then load the
data back?
Table Corrupt: Page (1:566656) is allocated to object ID 1189579276, index I
D 255, not to object ID 798625888, index ID 5 found in page header.
Mary

error 2501 performing dbcc reindex

To answer you questions, I'm able to select the data in the AssignGOP table
just fine. It all looks accurate. I did a DBCC checkcatalog and checkdb and
there are no errors.
I do know there are no indexes on this table to defrag, so could this be
causing my issue?
I read through that article awhile back. If I recall, to reindex, you have
to be in single-user mode, which we can't do so in our enviroment, we've
chosen defragging indexes over reindexing. I'm sorry for the confusion in my
wording.
"Paul S Randal [MS]" wrote:

> I recognize that code - I wrote that BOL example :-)
> One thing to be aware of - you're not using doing a reindex using this
> script, you're doing a defrag. They're different operations. You should read
> the whitepaper
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> to see if you really need to be doing this.
> It looks like a call to DBCC SHOWCONTIG is failing for some reason - I
> suspect there may be something wrong with your system catalogs. Can you run
> DBCC CHECKCATALOG and DBCC CHECKDB on the Hopping database? What happens if
> you try a select from the AssignGOP table?
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Josephine" <Josephine@.discussions.microsoft.com> wrote in message
> news:D896B2C3-2834-4C60-A7D5-5F9045E5F753@.microsoft.com...
> the
> rights.
> and
> exist
> other
>
>
|||No need to apologize for being unclear - I'm happy to help.
You don't have to be in single-user mode to reindex. I think you're getting
confused with the concurrency possible while rebuilding an index. When
rebuilding a non-clustered index, the table is read-only and when rebuilding
a clustered index, the table is not even readable.
The lack of indexes does mean it won't be defragged but the cursor to select
indexes should skip the heap:
[vbcol=seagreen]
- the IndexDepth property for a heap is always zero.
Can you post the results from the following query? I believe you that you
have no indexes but I'd like to see what data is there for the heap.
select * from sysindexes where id=object_id('AssignGOP')
Thanks.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Josephine" <Josephine@.discussions.microsoft.com> wrote in message
news:30B5C3B8-58E2-4CF4-B9D6-A4D82A01C9F7@.microsoft.com...
> Ha,ha! Yes, the BOL examples have helped me a bunch!

> To answer you questions, I'm able to select the data in the AssignGOP
table
> just fine. It all looks accurate. I did a DBCC checkcatalog and checkdb
and
> there are no errors.
> I do know there are no indexes on this table to defrag, so could this be
> causing my issue?
> I read through that article awhile back. If I recall, to reindex, you have
> to be in single-user mode, which we can't do so in our enviroment, we've
> chosen defragging indexes over reindexing. I'm sorry for the confusion in
my[vbcol=seagreen]
> wording.
> "Paul S Randal [MS]" wrote:
read[vbcol=seagreen]
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
run[vbcol=seagreen]
if[vbcol=seagreen]
rights.[vbcol=seagreen]
run[vbcol=seagreen]
named[vbcol=seagreen]
step[vbcol=seagreen]
the[vbcol=seagreen]
checktable,[vbcol=seagreen]
to[vbcol=seagreen]

error 2501 performing dbcc reindex

To answer you questions, I'm able to select the data in the AssignGOP table
just fine. It all looks accurate. I did a DBCC checkcatalog and checkdb and
there are no errors.
I do know there are no indexes on this table to defrag, so could this be
causing my issue?
I read through that article awhile back. If I recall, to reindex, you have
to be in single-user mode, which we can't do so in our enviroment, we've
chosen defragging indexes over reindexing. I'm sorry for the confusion in my
wording.
"Paul S Randal [MS]" wrote:

> I recognize that code - I wrote that BOL example :-)
> One thing to be aware of - you're not using doing a reindex using this
> script, you're doing a defrag. They're different operations. You should re
ad
> the whitepaper
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> to see if you really need to be doing this.
> It looks like a call to DBCC SHOWCONTIG is failing for some reason - I
> suspect there may be something wrong with your system catalogs. Can you ru
n
> DBCC CHECKCATALOG and DBCC CHECKDB on the Hopping database? What happens i
f
> you try a select from the AssignGOP table?
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Josephine" <Josephine@.discussions.microsoft.com> wrote in message
> news:D896B2C3-2834-4C60-A7D5-5F9045E5F753@.microsoft.com...
> the
> rights.
> and
> exist
> other
>
>|||No need to apologize for being unclear - I'm happy to help.
You don't have to be in single-user mode to reindex. I think you're getting
confused with the concurrency possible while rebuilding an index. When
rebuilding a non-clustered index, the table is read-only and when rebuilding
a clustered index, the table is not even readable.
The lack of indexes does mean it won't be defragged but the cursor to select
indexes should skip the heap:

- the IndexDepth property for a heap is always zero.
Can you post the results from the following query? I believe you that you
have no indexes but I'd like to see what data is there for the heap.
select * from sysindexes where id=object_id('AssignGOP')
Thanks.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Josephine" <Josephine@.discussions.microsoft.com> wrote in message
news:30B5C3B8-58E2-4CF4-B9D6-A4D82A01C9F7@.microsoft.com...[vbcol=seagreen]
> Ha,ha! Yes, the BOL examples have helped me a bunch!

> To answer you questions, I'm able to select the data in the AssignGOP
table
> just fine. It all looks accurate. I did a DBCC checkcatalog and checkdb
and
> there are no errors.
> I do know there are no indexes on this table to defrag, so could this be
> causing my issue?
> I read through that article awhile back. If I recall, to reindex, you have
> to be in single-user mode, which we can't do so in our enviroment, we've
> chosen defragging indexes over reindexing. I'm sorry for the confusion in
my[vbcol=seagreen]
> wording.
> "Paul S Randal [MS]" wrote:
>
read[vbcol=seagreen]
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx[vbcol=seagreen]
run[vbcol=seagreen]
if[vbcol=seagreen]
rights.[vbcol=seagreen]
run[vbcol=seagreen]
named[vbcol=seagreen]
step[vbcol=seagreen]
the[vbcol=seagreen]
checktable,[vbcol=seagreen]
to[vbcol=seagreen]

error 2501 performing dbcc reindex

I run dbcc reindex at night and received error 2501 on several of the
databases.
Based on what Books Online recommends, I have ran a dbcc checktable, and
checkdb for that matter...no errors. I also verified the table(s) to exist in
the database and are also in the sysobjects. Does anyone have any other ideas
as to why this job is failing and how I fix it?
Thank youCan you post the error output, exact DBCC command, and output from
sysobjects?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Josephine" <Josephine@.discussions.microsoft.com> wrote in message
news:3582C3AB-FE6A-45C5-9536-918962706A50@.microsoft.com...
> I run dbcc reindex at night and received error 2501 on several of the
> databases.
> Based on what Books Online recommends, I have ran a dbcc checktable, and
> checkdb for that matter...no errors. I also verified the table(s) to exist
in
> the database and are also in the sysobjects. Does anyone have any other
ideas
> as to why this job is failing and how I fix it?
> Thank you|||Thank you Paul, below is the information I'm dealing with.
Josie.
DBCC Command
*Perform a 'USE <database name>' to select the database in which to run the
script.*/
-- Declare variables
USE Hopping
SET NOCOUNT ON
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 30.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @.tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%'
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
From Job History
Executed as user: DOMAIN\username. Could not find a table or object named
'AssignGOP'. Check sysobjects. [SQLSTATE 42S02] (Error 2501). The step
failed.
From Sysobjects
AssignGOP 1860201677 U 6 3 1619001347 592 0 0 2004-02-21
11:25:49.007 0 592 0 U 1 115 0 2004-02-21 11:25:49.007 0 0 0 0 0 0 0
"Paul S Randal [MS]" wrote:
> Can you post the error output, exact DBCC command, and output from
> sysobjects?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Josephine" <Josephine@.discussions.microsoft.com> wrote in message
> news:3582C3AB-FE6A-45C5-9536-918962706A50@.microsoft.com...
> > I run dbcc reindex at night and received error 2501 on several of the
> > databases.
> > Based on what Books Online recommends, I have ran a dbcc checktable, and
> > checkdb for that matter...no errors. I also verified the table(s) to exist
> in
> > the database and are also in the sysobjects. Does anyone have any other
> ideas
> > as to why this job is failing and how I fix it?
> > Thank you
>
>|||I recognize that code - I wrote that BOL example :-)
One thing to be aware of - you're not using doing a reindex using this
script, you're doing a defrag. They're different operations. You should read
the whitepaper
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
to see if you really need to be doing this.
It looks like a call to DBCC SHOWCONTIG is failing for some reason - I
suspect there may be something wrong with your system catalogs. Can you run
DBCC CHECKCATALOG and DBCC CHECKDB on the Hopping database? What happens if
you try a select from the AssignGOP table?
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Josephine" <Josephine@.discussions.microsoft.com> wrote in message
news:D896B2C3-2834-4C60-A7D5-5F9045E5F753@.microsoft.com...
> Thank you Paul, below is the information I'm dealing with.
> Josie.
> DBCC Command
> *Perform a 'USE <database name>' to select the database in which to run
the
> script.*/
> -- Declare variables
> USE Hopping
> SET NOCOUNT ON
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.maxfrag DECIMAL
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 30.0
> -- Declare cursor
> DECLARE tables CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> -- Create the table
> CREATE TABLE #fraglist (
> ObjectName CHAR (255),
> ObjectId INT,
> IndexName CHAR (255),
> IndexId INT,
> Lvl INT,
> CountPages INT,
> CountRows INT,
> MinRecSize INT,
> MaxRecSize INT,
> AvgRecSize INT,
> ForRecCount INT,
> Extents INT,
> ExtentSwitches INT,
> AvgFreeBytes INT,
> AvgPageDensity INT,
> ScanDensity DECIMAL,
> BestCount INT,
> ActualCount INT,
> LogicalFrag DECIMAL,
> ExtentFrag DECIMAL)
> -- Open the cursor
> OPEN tables
> -- Loop through all the tables in the database
> FETCH NEXT
> FROM tables
> INTO @.tablename
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- Do the showcontig of all indexes of the table
> INSERT INTO #fraglist
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> FETCH NEXT
> FROM tables
> INTO @.tablename
> END
> -- Close and deallocate the cursor
> CLOSE tables
> DEALLOCATE tables
> -- Declare cursor for list of indexes to be defragged
> DECLARE indexes CURSOR FOR
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
> ' + RTRIM(@.indexid) + ') - fragmentation currently '
> + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
> ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>
> From Job History
> Executed as user: DOMAIN\username. Could not find a table or object named
> 'AssignGOP'. Check sysobjects. [SQLSTATE 42S02] (Error 2501). The step
> failed.
> From Sysobjects
> AssignGOP 1860201677 U 6 3 1619001347 592 0 0 2004-02-21
> 11:25:49.007 0 592 0 U 1 115 0 2004-02-21 11:25:49.007 0 0 0 0 0 0 0
>
> "Paul S Randal [MS]" wrote:
> > Can you post the error output, exact DBCC command, and output from
> > sysobjects?
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Josephine" <Josephine@.discussions.microsoft.com> wrote in message
> > news:3582C3AB-FE6A-45C5-9536-918962706A50@.microsoft.com...
> > > I run dbcc reindex at night and received error 2501 on several of the
> > > databases.
> > > Based on what Books Online recommends, I have ran a dbcc checktable,
and
> > > checkdb for that matter...no errors. I also verified the table(s) to
exist
> > in
> > > the database and are also in the sysobjects. Does anyone have any
other
> > ideas
> > > as to why this job is failing and how I fix it?
> > > Thank you
> >
> >
> >|||Ha,ha! Yes, the BOL examples have helped me a bunch! :)
To answer you questions, I'm able to select the data in the AssignGOP table
just fine. It all looks accurate. I did a DBCC checkcatalog and checkdb and
there are no errors.
I do know there are no indexes on this table to defrag, so could this be
causing my issue?
I read through that article awhile back. If I recall, to reindex, you have
to be in single-user mode, which we can't do so in our enviroment, we've
chosen defragging indexes over reindexing. I'm sorry for the confusion in my
wording.
"Paul S Randal [MS]" wrote:
> I recognize that code - I wrote that BOL example :-)
> One thing to be aware of - you're not using doing a reindex using this
> script, you're doing a defrag. They're different operations. You should read
> the whitepaper
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> to see if you really need to be doing this.
> It looks like a call to DBCC SHOWCONTIG is failing for some reason - I
> suspect there may be something wrong with your system catalogs. Can you run
> DBCC CHECKCATALOG and DBCC CHECKDB on the Hopping database? What happens if
> you try a select from the AssignGOP table?
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Josephine" <Josephine@.discussions.microsoft.com> wrote in message
> news:D896B2C3-2834-4C60-A7D5-5F9045E5F753@.microsoft.com...
> > Thank you Paul, below is the information I'm dealing with.
> > Josie.
> >
> > DBCC Command
> >
> > *Perform a 'USE <database name>' to select the database in which to run
> the
> > script.*/
> > -- Declare variables
> > USE Hopping
> > SET NOCOUNT ON
> > DECLARE @.tablename VARCHAR (128)
> > DECLARE @.execstr VARCHAR (255)
> > DECLARE @.objectid INT
> > DECLARE @.indexid INT
> > DECLARE @.frag DECIMAL
> > DECLARE @.maxfrag DECIMAL
> >
> > -- Decide on the maximum fragmentation to allow
> > SELECT @.maxfrag = 30.0
> >
> > -- Declare cursor
> > DECLARE tables CURSOR FOR
> > SELECT TABLE_NAME
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE TABLE_TYPE = 'BASE TABLE'
> >
> > -- Create the table
> > CREATE TABLE #fraglist (
> > ObjectName CHAR (255),
> > ObjectId INT,
> > IndexName CHAR (255),
> > IndexId INT,
> > Lvl INT,
> > CountPages INT,
> > CountRows INT,
> > MinRecSize INT,
> > MaxRecSize INT,
> > AvgRecSize INT,
> > ForRecCount INT,
> > Extents INT,
> > ExtentSwitches INT,
> > AvgFreeBytes INT,
> > AvgPageDensity INT,
> > ScanDensity DECIMAL,
> > BestCount INT,
> > ActualCount INT,
> > LogicalFrag DECIMAL,
> > ExtentFrag DECIMAL)
> >
> > -- Open the cursor
> > OPEN tables
> >
> > -- Loop through all the tables in the database
> > FETCH NEXT
> > FROM tables
> > INTO @.tablename
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > -- Do the showcontig of all indexes of the table
> > INSERT INTO #fraglist
> > EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> > FETCH NEXT
> > FROM tables
> > INTO @.tablename
> > END
> >
> > -- Close and deallocate the cursor
> > CLOSE tables
> > DEALLOCATE tables
> >
> > -- Declare cursor for list of indexes to be defragged
> > DECLARE indexes CURSOR FOR
> > SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> > FROM #fraglist
> > WHERE LogicalFrag >= @.maxfrag
> > AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> >
> > -- Open the cursor
> > OPEN indexes
> >
> > -- loop through the indexes
> > FETCH NEXT
> > FROM indexes
> > INTO @.tablename, @.objectid, @.indexid, @.frag
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
> > ' + RTRIM(@.indexid) + ') - fragmentation currently '
> > + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> > SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
> > ' + RTRIM(@.indexid) + ')'
> > EXEC (@.execstr)
> >
> > FETCH NEXT
> > FROM indexes
> > INTO @.tablename, @.objectid, @.indexid, @.frag
> > END
> >
> > -- Close and deallocate the cursor
> > CLOSE indexes
> > DEALLOCATE indexes
> >
> > -- Delete the temporary table
> > DROP TABLE #fraglist
> > GO
> >
> >
> > From Job History
> > Executed as user: DOMAIN\username. Could not find a table or object named
> > 'AssignGOP'. Check sysobjects. [SQLSTATE 42S02] (Error 2501). The step
> > failed.
> >
> > From Sysobjects
> > AssignGOP 1860201677 U 6 3 1619001347 592 0 0 2004-02-21
> > 11:25:49.007 0 592 0 U 1 115 0 2004-02-21 11:25:49.007 0 0 0 0 0 0 0
> >
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > Can you post the error output, exact DBCC command, and output from
> > > sysobjects?
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Josephine" <Josephine@.discussions.microsoft.com> wrote in message
> > > news:3582C3AB-FE6A-45C5-9536-918962706A50@.microsoft.com...
> > > > I run dbcc reindex at night and received error 2501 on several of the
> > > > databases.
> > > > Based on what Books Online recommends, I have ran a dbcc checktable,
> and
> > > > checkdb for that matter...no errors. I also verified the table(s) to
> exist
> > > in
> > > > the database and are also in the sysobjects. Does anyone have any
> other
> > > ideas
> > > > as to why this job is failing and how I fix it?
> > > > Thank you
> > >
> > >
> > >
>
>|||No need to apologize for being unclear - I'm happy to help.
You don't have to be in single-user mode to reindex. I think you're getting
confused with the concurrency possible while rebuilding an index. When
rebuilding a non-clustered index, the table is read-only and when rebuilding
a clustered index, the table is not even readable.
The lack of indexes does mean it won't be defragged but the cursor to select
indexes should skip the heap:
> > > DECLARE indexes CURSOR FOR
> > > SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> > > FROM #fraglist
> > > WHERE LogicalFrag >= @.maxfrag
> > > AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
- the IndexDepth property for a heap is always zero.
Can you post the results from the following query? I believe you that you
have no indexes but I'd like to see what data is there for the heap.
select * from sysindexes where id=object_id('AssignGOP')
Thanks.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Josephine" <Josephine@.discussions.microsoft.com> wrote in message
news:30B5C3B8-58E2-4CF4-B9D6-A4D82A01C9F7@.microsoft.com...
> Ha,ha! Yes, the BOL examples have helped me a bunch! :)
> To answer you questions, I'm able to select the data in the AssignGOP
table
> just fine. It all looks accurate. I did a DBCC checkcatalog and checkdb
and
> there are no errors.
> I do know there are no indexes on this table to defrag, so could this be
> causing my issue?
> I read through that article awhile back. If I recall, to reindex, you have
> to be in single-user mode, which we can't do so in our enviroment, we've
> chosen defragging indexes over reindexing. I'm sorry for the confusion in
my
> wording.
> "Paul S Randal [MS]" wrote:
> > I recognize that code - I wrote that BOL example :-)
> >
> > One thing to be aware of - you're not using doing a reindex using this
> > script, you're doing a defrag. They're different operations. You should
read
> > the whitepaper
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> > to see if you really need to be doing this.
> >
> > It looks like a call to DBCC SHOWCONTIG is failing for some reason - I
> > suspect there may be something wrong with your system catalogs. Can you
run
> > DBCC CHECKCATALOG and DBCC CHECKDB on the Hopping database? What happens
if
> > you try a select from the AssignGOP table?
> >
> > Thanks
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Josephine" <Josephine@.discussions.microsoft.com> wrote in message
> > news:D896B2C3-2834-4C60-A7D5-5F9045E5F753@.microsoft.com...
> > > Thank you Paul, below is the information I'm dealing with.
> > > Josie.
> > >
> > > DBCC Command
> > >
> > > *Perform a 'USE <database name>' to select the database in which to
run
> > the
> > > script.*/
> > > -- Declare variables
> > > USE Hopping
> > > SET NOCOUNT ON
> > > DECLARE @.tablename VARCHAR (128)
> > > DECLARE @.execstr VARCHAR (255)
> > > DECLARE @.objectid INT
> > > DECLARE @.indexid INT
> > > DECLARE @.frag DECIMAL
> > > DECLARE @.maxfrag DECIMAL
> > >
> > > -- Decide on the maximum fragmentation to allow
> > > SELECT @.maxfrag = 30.0
> > >
> > > -- Declare cursor
> > > DECLARE tables CURSOR FOR
> > > SELECT TABLE_NAME
> > > FROM INFORMATION_SCHEMA.TABLES
> > > WHERE TABLE_TYPE = 'BASE TABLE'
> > >
> > > -- Create the table
> > > CREATE TABLE #fraglist (
> > > ObjectName CHAR (255),
> > > ObjectId INT,
> > > IndexName CHAR (255),
> > > IndexId INT,
> > > Lvl INT,
> > > CountPages INT,
> > > CountRows INT,
> > > MinRecSize INT,
> > > MaxRecSize INT,
> > > AvgRecSize INT,
> > > ForRecCount INT,
> > > Extents INT,
> > > ExtentSwitches INT,
> > > AvgFreeBytes INT,
> > > AvgPageDensity INT,
> > > ScanDensity DECIMAL,
> > > BestCount INT,
> > > ActualCount INT,
> > > LogicalFrag DECIMAL,
> > > ExtentFrag DECIMAL)
> > >
> > > -- Open the cursor
> > > OPEN tables
> > >
> > > -- Loop through all the tables in the database
> > > FETCH NEXT
> > > FROM tables
> > > INTO @.tablename
> > >
> > > WHILE @.@.FETCH_STATUS = 0
> > > BEGIN
> > > -- Do the showcontig of all indexes of the table
> > > INSERT INTO #fraglist
> > > EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> > > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> > > FETCH NEXT
> > > FROM tables
> > > INTO @.tablename
> > > END
> > >
> > > -- Close and deallocate the cursor
> > > CLOSE tables
> > > DEALLOCATE tables
> > >
> > > -- Declare cursor for list of indexes to be defragged
> > > DECLARE indexes CURSOR FOR
> > > SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> > > FROM #fraglist
> > > WHERE LogicalFrag >= @.maxfrag
> > > AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> > >
> > > -- Open the cursor
> > > OPEN indexes
> > >
> > > -- loop through the indexes
> > > FETCH NEXT
> > > FROM indexes
> > > INTO @.tablename, @.objectid, @.indexid, @.frag
> > >
> > > WHILE @.@.FETCH_STATUS = 0
> > > BEGIN
> > > PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
> > > ' + RTRIM(@.indexid) + ') - fragmentation currently '
> > > + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> > > SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
> > > ' + RTRIM(@.indexid) + ')'
> > > EXEC (@.execstr)
> > >
> > > FETCH NEXT
> > > FROM indexes
> > > INTO @.tablename, @.objectid, @.indexid, @.frag
> > > END
> > >
> > > -- Close and deallocate the cursor
> > > CLOSE indexes
> > > DEALLOCATE indexes
> > >
> > > -- Delete the temporary table
> > > DROP TABLE #fraglist
> > > GO
> > >
> > >
> > > From Job History
> > > Executed as user: DOMAIN\username. Could not find a table or object
named
> > > 'AssignGOP'. Check sysobjects. [SQLSTATE 42S02] (Error 2501). The
step
> > > failed.
> > >
> > > From Sysobjects
> > > AssignGOP 1860201677 U 6 3 1619001347 592 0 0 2004-02-21
> > > 11:25:49.007 0 592 0 U 1 115 0 2004-02-21 11:25:49.007 0 0 0 0 0 0 0
> > >
> > >
> > > "Paul S Randal [MS]" wrote:
> > >
> > > > Can you post the error output, exact DBCC command, and output from
> > > > sysobjects?
> > > >
> > > > --
> > > > Paul Randal
> > > > Dev Lead, Microsoft SQL Server Storage Engine
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "Josephine" <Josephine@.discussions.microsoft.com> wrote in message
> > > > news:3582C3AB-FE6A-45C5-9536-918962706A50@.microsoft.com...
> > > > > I run dbcc reindex at night and received error 2501 on several of
the
> > > > > databases.
> > > > > Based on what Books Online recommends, I have ran a dbcc
checktable,
> > and
> > > > > checkdb for that matter...no errors. I also verified the table(s)
to
> > exist
> > > > in
> > > > > the database and are also in the sysobjects. Does anyone have any
> > other
> > > > ideas
> > > > > as to why this job is failing and how I fix it?
> > > > > Thank you
> > > >
> > > >
> > > >
> >
> >
> >

Thursday, March 29, 2012

Error 925 DB already open when i refresh current activity

Error 925 DB already open when i refresh current activity

Tuesday, March 27, 2012

Error 8965

Error 8965

Error 8965

error 8906

Friday, February 24, 2012

Error 2571 - not permission to run DBCC TRACEON

Sunday, February 19, 2012

Error 2571 - not permission to run DBCC TRACEON

Error 2571 - not permission to run DBCC TRACEON

Error 2535

Error 2535

Error 2535

error 2501 performing dbcc reindex

error 2501 performing dbcc reindex

error 2501 performing dbcc reindex

Error 207

Blog Archive

About Me