I synch 4 databases everyday and on one of them I get the Error 823, Severity 24, State 2 error (Yes I know you have heard this before but I am a new, LOL). I have tried detaching the database and copying it, renaming that database and then copying back to the original name with the same error; therefore, I think the error is somewhere in the database structure. If it is the database structure would it still give the error above or is that simply a hardware error? I have been searching for solution and I am at a loss. Any help would be appreciated.
Thanks.
N. AdkinsAn 823 error usually indicates a disk i/o problem. You say that you are synchronizing databases. How? replication ... log shipping ... home grown method?
Have you run DBCC checkdb against the database that throws the error?
And ... what OS and SP. What version SQL Server and SP. RAID ... 5 ... 0 ... 1 ... 1+0?
Have you checked with HW vendor for possible reported problems with your array controller (if you have them)?
Lots of opportunity to troubleshoot and many things can cause an 823 error.
No rest for the weary on this one! :(|||Raid 0
I have ran dbcc checkdb and shows no errors.
It is running on Win 2K Server
I am running MS SQL Server 2000
It is a Gateway 980 Server with Adaptec SCSI card (dual channel)
The remote database connects with the main database within the software itself (I.E. SmartClinic) and does the actual synch. I would assume it is using log shipping as method.
N. Adkins|||Raid 0 ... flying without a net! Striped diskset with no redundancy! I don't see the service pack levels in your response, but based on the info you have provided here is what I would start with.
1. Contact HW vendors for known disk controller problems (i.e. read / write caching, delayed writes, etc ) ... see if they have any firmware / patches. This could be a delayed write situation.
2. Check with Berdy Medical Systems regarding Smart Clinic. It was developed for Access ... are they tring to port it to SQL Server? Do they have any records or reports of disk transfer errors, or are you charting unsailed waters?
3. Start checking the transmission hardware. How is your network performing. Are you using hubs (bad), routers (good), dial-up (bad), fractional T1, etc. Copy a small file from good server to server that throws 823 error. Compare files. Was it copied in a timely manner? Are the files the same. Copy a backup of the database (if size allows) and restore to a differently named database. Run checkdb. Is it clean.
5. Check the microsoft.public forums under google groups. Check other support sites (M$ KnowledgeBase, SQLServer Central, SQL Team, etc (google 'em)).
6. Time to get the big boys involved. Micro$oft Support - not just for breakfast anymore!
HTH
Tom
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Error 644 Severity level 21
Hi,
I got the following error message for one of our databases:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 3/25/2005
Time: 9:18:53 AM
User: XXXXXXXX\Administrator
Computer: XXXXXX
Description:
Error: 644, Severity: 21, State: 3
Could not find the index entry for RID '16c42dc515100' in index page
(1:1725), index ID 0, database 'XXXXXXXXX_XXXXX_XXX_MSCRM'.
I looked up the error in Books Online and it told me to run DBCC CHECKDB to
see the extent of the damage. It gave me the following result:
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'XXXXXXXXX_XXXXX_XXX_MSCRM', index
'sysobjects.ncsysobjects2' (ID 1) (index ID 3). Extra or invalid key for the
keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:155:91) with values (parent_obj = 1237579447 and id = 365243844) points to the data row identified by ().
DBCC results for 'XXXXXXXXX_XXXXX_XXX_MSCRM'.
DBCC results for 'sysobjects'.
There are 2315 rows in 49 pages for object 'sysobjects'.
CHECKDB found 0 allocation errors and 1 consistency errors in table
'sysobjects' (object ID 1).
These don't seem to match. Should they?
The next thing that Books Online says to do is to run DBCC CHECKDB with the
REPAIR_REBUILD clause to fix the damage. Is this still an appropriate
course of action given that the error reported by checkdb doesn't seem to be
the one from the event log?
One of the other dba's here said that there may be a better way to rebuild
the index than to run the repair_rebuild on the entire database. I just
wanted to double check and get some other opinions/feedback before I
continued. Any help would really be appreciated. Thank you
-JohnHi
You have corruption.
Do a full backup of the database, then run the DBCC. There is probably more
than one problem so the DBCC finds the other one first.
At the same time, look for your backups because if the corruption is bad
enough, you need to restore to you last good backup.
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/
"John Rosenlof" <greyseal96@.hotmail.com> wrote in message
news:O#0LBPYMFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I got the following error message for one of our databases:
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 3/25/2005
> Time: 9:18:53 AM
> User: XXXXXXXX\Administrator
> Computer: XXXXXX
> Description:
> Error: 644, Severity: 21, State: 3
> Could not find the index entry for RID '16c42dc515100' in index page
> (1:1725), index ID 0, database 'XXXXXXXXX_XXXXX_XXX_MSCRM'.
> I looked up the error in Books Online and it told me to run DBCC CHECKDB
to
> see the extent of the damage. It gave me the following result:
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'XXXXXXXXX_XXXXX_XXX_MSCRM', index
> 'sysobjects.ncsysobjects2' (ID 1) (index ID 3). Extra or invalid key for
the
> keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:155:91) with values (parent_obj = 1237579447 and id => 365243844) points to the data row identified by ().
> DBCC results for 'XXXXXXXXX_XXXXX_XXX_MSCRM'.
> DBCC results for 'sysobjects'.
> There are 2315 rows in 49 pages for object 'sysobjects'.
> CHECKDB found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> These don't seem to match. Should they?
> The next thing that Books Online says to do is to run DBCC CHECKDB with
the
> REPAIR_REBUILD clause to fix the damage. Is this still an appropriate
> course of action given that the error reported by checkdb doesn't seem to
be
> the one from the event log?
> One of the other dba's here said that there may be a better way to rebuild
> the index than to run the repair_rebuild on the entire database. I just
> wanted to double check and get some other opinions/feedback before I
> continued. Any help would really be appreciated. Thank you
> -John
>
I got the following error message for one of our databases:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 3/25/2005
Time: 9:18:53 AM
User: XXXXXXXX\Administrator
Computer: XXXXXX
Description:
Error: 644, Severity: 21, State: 3
Could not find the index entry for RID '16c42dc515100' in index page
(1:1725), index ID 0, database 'XXXXXXXXX_XXXXX_XXX_MSCRM'.
I looked up the error in Books Online and it told me to run DBCC CHECKDB to
see the extent of the damage. It gave me the following result:
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'XXXXXXXXX_XXXXX_XXX_MSCRM', index
'sysobjects.ncsysobjects2' (ID 1) (index ID 3). Extra or invalid key for the
keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:155:91) with values (parent_obj = 1237579447 and id = 365243844) points to the data row identified by ().
DBCC results for 'XXXXXXXXX_XXXXX_XXX_MSCRM'.
DBCC results for 'sysobjects'.
There are 2315 rows in 49 pages for object 'sysobjects'.
CHECKDB found 0 allocation errors and 1 consistency errors in table
'sysobjects' (object ID 1).
These don't seem to match. Should they?
The next thing that Books Online says to do is to run DBCC CHECKDB with the
REPAIR_REBUILD clause to fix the damage. Is this still an appropriate
course of action given that the error reported by checkdb doesn't seem to be
the one from the event log?
One of the other dba's here said that there may be a better way to rebuild
the index than to run the repair_rebuild on the entire database. I just
wanted to double check and get some other opinions/feedback before I
continued. Any help would really be appreciated. Thank you
-JohnHi
You have corruption.
Do a full backup of the database, then run the DBCC. There is probably more
than one problem so the DBCC finds the other one first.
At the same time, look for your backups because if the corruption is bad
enough, you need to restore to you last good backup.
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/
"John Rosenlof" <greyseal96@.hotmail.com> wrote in message
news:O#0LBPYMFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I got the following error message for one of our databases:
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 3/25/2005
> Time: 9:18:53 AM
> User: XXXXXXXX\Administrator
> Computer: XXXXXX
> Description:
> Error: 644, Severity: 21, State: 3
> Could not find the index entry for RID '16c42dc515100' in index page
> (1:1725), index ID 0, database 'XXXXXXXXX_XXXXX_XXX_MSCRM'.
> I looked up the error in Books Online and it told me to run DBCC CHECKDB
to
> see the extent of the damage. It gave me the following result:
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'XXXXXXXXX_XXXXX_XXX_MSCRM', index
> 'sysobjects.ncsysobjects2' (ID 1) (index ID 3). Extra or invalid key for
the
> keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:155:91) with values (parent_obj = 1237579447 and id => 365243844) points to the data row identified by ().
> DBCC results for 'XXXXXXXXX_XXXXX_XXX_MSCRM'.
> DBCC results for 'sysobjects'.
> There are 2315 rows in 49 pages for object 'sysobjects'.
> CHECKDB found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> These don't seem to match. Should they?
> The next thing that Books Online says to do is to run DBCC CHECKDB with
the
> REPAIR_REBUILD clause to fix the damage. Is this still an appropriate
> course of action given that the error reported by checkdb doesn't seem to
be
> the one from the event log?
> One of the other dba's here said that there may be a better way to rebuild
> the index than to run the repair_rebuild on the entire database. I just
> wanted to double check and get some other opinions/feedback before I
> continued. Any help would really be appreciated. Thank you
> -John
>
Sunday, March 11, 2012
Error 605 - stale read
We're running SQL Server 2K sp3a on 2003 Server. Our user databases are on
an HP SAN. During heavy data warehouse ETL DTS operations we occasionally
get I/O errors such as:
DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005)
Error string: I/O error (bad page ID) detected during read at offset
0x0000020a472000 in file 'W:\SQLServer\Data\ColleagueRaw_Data.MDF'.
Running DBCC CHECKDB after a processing failure sometimes shows consistency
errors with the table associated with the I/O failure. Running DBCC again
without the REPAIR_REBUILD option will then not show any errors.
Running SQLIOStress fails with stale read errors such as:
ERROR: Stale read check failure. Page image returned does not match
previous write.
Running SQLIOStress on different boxes against the SAN results in the same
errors. Write caching is not enabled on the SAN.
Does anyone have any ideas'
Thanks much!whit wrote:
> We're running SQL Server 2K sp3a on 2003 Server. Our user databases
> are on an HP SAN. During heavy data warehouse ETL DTS operations we
> occasionally get I/O errors such as:
> DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259
> (80004005) Error string: I/O error (bad page ID) detected during
> read at offset 0x0000020a472000 in file
> 'W:\SQLServer\Data\ColleagueRaw_Data.MDF'.
> Running DBCC CHECKDB after a processing failure sometimes shows
> consistency errors with the table associated with the I/O failure.
> Running DBCC again without the REPAIR_REBUILD option will then not
> show any errors.
> Running SQLIOStress fails with stale read errors such as:
> ERROR: Stale read check failure. Page image returned does not match
> previous write.
> Running SQLIOStress on different boxes against the SAN results in the
> same errors. Write caching is not enabled on the SAN.
> Does anyone have any ideas'
Looks like a MS PSS (http://support.microsoft.com) case, they should be able
to help you.
You may also want to read this:
PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
http://support.microsoft.com/defaul...kb;en-us;826433
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
an HP SAN. During heavy data warehouse ETL DTS operations we occasionally
get I/O errors such as:
DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005)
Error string: I/O error (bad page ID) detected during read at offset
0x0000020a472000 in file 'W:\SQLServer\Data\ColleagueRaw_Data.MDF'.
Running DBCC CHECKDB after a processing failure sometimes shows consistency
errors with the table associated with the I/O failure. Running DBCC again
without the REPAIR_REBUILD option will then not show any errors.
Running SQLIOStress fails with stale read errors such as:
ERROR: Stale read check failure. Page image returned does not match
previous write.
Running SQLIOStress on different boxes against the SAN results in the same
errors. Write caching is not enabled on the SAN.
Does anyone have any ideas'
Thanks much!whit wrote:
> We're running SQL Server 2K sp3a on 2003 Server. Our user databases
> are on an HP SAN. During heavy data warehouse ETL DTS operations we
> occasionally get I/O errors such as:
> DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259
> (80004005) Error string: I/O error (bad page ID) detected during
> read at offset 0x0000020a472000 in file
> 'W:\SQLServer\Data\ColleagueRaw_Data.MDF'.
> Running DBCC CHECKDB after a processing failure sometimes shows
> consistency errors with the table associated with the I/O failure.
> Running DBCC again without the REPAIR_REBUILD option will then not
> show any errors.
> Running SQLIOStress fails with stale read errors such as:
> ERROR: Stale read check failure. Page image returned does not match
> previous write.
> Running SQLIOStress on different boxes against the SAN results in the
> same errors. Write caching is not enabled on the SAN.
> Does anyone have any ideas'
Looks like a MS PSS (http://support.microsoft.com) case, they should be able
to help you.
You may also want to read this:
PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
http://support.microsoft.com/defaul...kb;en-us;826433
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Error 5173
I had to do a restore of my msdb. Before I did, I
detached all of my databases. The restore went fine, and
I was able to re-attach all of my databases, except for
one of them. When I try to attach it, I get an error that
says "Error 5173: Cannot associate files with different
databases." The ldf files are on a differend drive than
the mdf file, but they were like that before I detached
them. Nothing has changed on this database. How do I get
this DB attached?
Thanks.
Hi
At a guess you have either got the wrong file names are missing files or
they are in the wrong order!
You may also want to check the permissions on the files and directories.
If that fails you may want to try sp_attach_single_file_db and only specify
the data file (make sure you keep a copy of the original files)
John
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:1f03501c457ae$c2d08cf0$a601280a@.phx.gbl...
> I had to do a restore of my msdb. Before I did, I
> detached all of my databases. The restore went fine, and
> I was able to re-attach all of my databases, except for
> one of them. When I try to attach it, I get an error that
> says "Error 5173: Cannot associate files with different
> databases." The ldf files are on a differend drive than
> the mdf file, but they were like that before I detached
> them. Nothing has changed on this database. How do I get
> this DB attached?
> Thanks.
|||I've double and triple checked the file names and they are
correct. All permissions look correct. It's only
one .mdf and one .ldf that need to attach.
If I run "sp_attach_single_file_db", will that create a
new log file? If so, is there a way for me to point the
log file somewhere else. I have to keep the log file on a
seperate drive due to disk space (60GB log file).
Thanks again.
>--Original Message--
>Hi
>At a guess you have either got the wrong file names are
missing files or
>they are in the wrong order!
>You may also want to check the permissions on the files
and directories.
>If that fails you may want to try
sp_attach_single_file_db and only specify
>the data file (make sure you keep a copy of the original
files)
>John
>"Ken" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1f03501c457ae$c2d08cf0$a601280a@.phx.gbl...
and[vbcol=seagreen]
that[vbcol=seagreen]
get
>
>.
>
|||Hi
I would make sure that you keep safe copies of all the files!
I would then try to re-attach them in the original locations, if that works
then detach them again and move the files.
Alternatively if you backed up the database before trying this, you could
restore the backup using the MOVE option. See Books online about the RESTORE
command.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1f1a301c457b6$0dbf1ef0$a301280a@.phx.gbl...[vbcol=seagreen]
> I've double and triple checked the file names and they are
> correct. All permissions look correct. It's only
> one .mdf and one .ldf that need to attach.
> If I run "sp_attach_single_file_db", will that create a
> new log file? If so, is there a way for me to point the
> log file somewhere else. I have to keep the log file on a
> seperate drive due to disk space (60GB log file).
> Thanks again.
>
> missing files or
> and directories.
> sp_attach_single_file_db and only specify
> files)
> message
> and
> that
> get
detached all of my databases. The restore went fine, and
I was able to re-attach all of my databases, except for
one of them. When I try to attach it, I get an error that
says "Error 5173: Cannot associate files with different
databases." The ldf files are on a differend drive than
the mdf file, but they were like that before I detached
them. Nothing has changed on this database. How do I get
this DB attached?
Thanks.
Hi
At a guess you have either got the wrong file names are missing files or
they are in the wrong order!
You may also want to check the permissions on the files and directories.
If that fails you may want to try sp_attach_single_file_db and only specify
the data file (make sure you keep a copy of the original files)
John
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:1f03501c457ae$c2d08cf0$a601280a@.phx.gbl...
> I had to do a restore of my msdb. Before I did, I
> detached all of my databases. The restore went fine, and
> I was able to re-attach all of my databases, except for
> one of them. When I try to attach it, I get an error that
> says "Error 5173: Cannot associate files with different
> databases." The ldf files are on a differend drive than
> the mdf file, but they were like that before I detached
> them. Nothing has changed on this database. How do I get
> this DB attached?
> Thanks.
|||I've double and triple checked the file names and they are
correct. All permissions look correct. It's only
one .mdf and one .ldf that need to attach.
If I run "sp_attach_single_file_db", will that create a
new log file? If so, is there a way for me to point the
log file somewhere else. I have to keep the log file on a
seperate drive due to disk space (60GB log file).
Thanks again.
>--Original Message--
>Hi
>At a guess you have either got the wrong file names are
missing files or
>they are in the wrong order!
>You may also want to check the permissions on the files
and directories.
>If that fails you may want to try
sp_attach_single_file_db and only specify
>the data file (make sure you keep a copy of the original
files)
>John
>"Ken" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1f03501c457ae$c2d08cf0$a601280a@.phx.gbl...
and[vbcol=seagreen]
that[vbcol=seagreen]
get
>
>.
>
|||Hi
I would make sure that you keep safe copies of all the files!
I would then try to re-attach them in the original locations, if that works
then detach them again and move the files.
Alternatively if you backed up the database before trying this, you could
restore the backup using the MOVE option. See Books online about the RESTORE
command.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1f1a301c457b6$0dbf1ef0$a301280a@.phx.gbl...[vbcol=seagreen]
> I've double and triple checked the file names and they are
> correct. All permissions look correct. It's only
> one .mdf and one .ldf that need to attach.
> If I run "sp_attach_single_file_db", will that create a
> new log file? If so, is there a way for me to point the
> log file somewhere else. I have to keep the log file on a
> seperate drive due to disk space (60GB log file).
> Thanks again.
>
> missing files or
> and directories.
> sp_attach_single_file_db and only specify
> files)
> message
> and
> that
> get
Error 5173
I had to do a restore of my msdb. Before I did, I
detached all of my databases. The restore went fine, and
I was able to re-attach all of my databases, except for
one of them. When I try to attach it, I get an error that
says "Error 5173: Cannot associate files with different
databases." The ldf files are on a differend drive than
the mdf file, but they were like that before I detached
them. Nothing has changed on this database. How do I get
this DB attached?
Thanks.Hi
At a guess you have either got the wrong file names are missing files or
they are in the wrong order!
You may also want to check the permissions on the files and directories.
If that fails you may want to try sp_attach_single_file_db and only specify
the data file (make sure you keep a copy of the original files)
John
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:1f03501c457ae$c2d08cf0$a601280a@.phx
.gbl...
> I had to do a restore of my msdb. Before I did, I
> detached all of my databases. The restore went fine, and
> I was able to re-attach all of my databases, except for
> one of them. When I try to attach it, I get an error that
> says "Error 5173: Cannot associate files with different
> databases." The ldf files are on a differend drive than
> the mdf file, but they were like that before I detached
> them. Nothing has changed on this database. How do I get
> this DB attached?
> Thanks.|||I've double and triple checked the file names and they are
correct. All permissions look correct. It's only
one .mdf and one .ldf that need to attach.
If I run "sp_attach_single_file_db", will that create a
new log file? If so, is there a way for me to point the
log file somewhere else. I have to keep the log file on a
seperate drive due to disk space (60GB log file).
Thanks again.
>--Original Message--
>Hi
>At a guess you have either got the wrong file names are
missing files or
>they are in the wrong order!
>You may also want to check the permissions on the files
and directories.
>If that fails you may want to try
sp_attach_single_file_db and only specify
>the data file (make sure you keep a copy of the original
files)
>John
>"Ken" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1f03501c457ae$c2d08cf0$a601280a@.phx
.gbl...
and[vbcol=seagreen]
that[vbcol=seagreen]
get[vbcol=seagreen]
>
>.
>|||Hi
I would make sure that you keep safe copies of all the files!
I would then try to re-attach them in the original locations, if that works
then detach them again and move the files.
Alternatively if you backed up the database before trying this, you could
restore the backup using the MOVE option. See Books online about the RESTORE
command.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1f1a301c457b6$0dbf1ef0$a301280a@.phx
.gbl...[vbcol=seagreen]
> I've double and triple checked the file names and they are
> correct. All permissions look correct. It's only
> one .mdf and one .ldf that need to attach.
> If I run "sp_attach_single_file_db", will that create a
> new log file? If so, is there a way for me to point the
> log file somewhere else. I have to keep the log file on a
> seperate drive due to disk space (60GB log file).
> Thanks again.
>
> missing files or
> and directories.
> sp_attach_single_file_db and only specify
> files)
> message
> and
> that
> get
detached all of my databases. The restore went fine, and
I was able to re-attach all of my databases, except for
one of them. When I try to attach it, I get an error that
says "Error 5173: Cannot associate files with different
databases." The ldf files are on a differend drive than
the mdf file, but they were like that before I detached
them. Nothing has changed on this database. How do I get
this DB attached?
Thanks.Hi
At a guess you have either got the wrong file names are missing files or
they are in the wrong order!
You may also want to check the permissions on the files and directories.
If that fails you may want to try sp_attach_single_file_db and only specify
the data file (make sure you keep a copy of the original files)
John
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:1f03501c457ae$c2d08cf0$a601280a@.phx
.gbl...
> I had to do a restore of my msdb. Before I did, I
> detached all of my databases. The restore went fine, and
> I was able to re-attach all of my databases, except for
> one of them. When I try to attach it, I get an error that
> says "Error 5173: Cannot associate files with different
> databases." The ldf files are on a differend drive than
> the mdf file, but they were like that before I detached
> them. Nothing has changed on this database. How do I get
> this DB attached?
> Thanks.|||I've double and triple checked the file names and they are
correct. All permissions look correct. It's only
one .mdf and one .ldf that need to attach.
If I run "sp_attach_single_file_db", will that create a
new log file? If so, is there a way for me to point the
log file somewhere else. I have to keep the log file on a
seperate drive due to disk space (60GB log file).
Thanks again.
>--Original Message--
>Hi
>At a guess you have either got the wrong file names are
missing files or
>they are in the wrong order!
>You may also want to check the permissions on the files
and directories.
>If that fails you may want to try
sp_attach_single_file_db and only specify
>the data file (make sure you keep a copy of the original
files)
>John
>"Ken" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1f03501c457ae$c2d08cf0$a601280a@.phx
.gbl...
and[vbcol=seagreen]
that[vbcol=seagreen]
get[vbcol=seagreen]
>
>.
>|||Hi
I would make sure that you keep safe copies of all the files!
I would then try to re-attach them in the original locations, if that works
then detach them again and move the files.
Alternatively if you backed up the database before trying this, you could
restore the backup using the MOVE option. See Books online about the RESTORE
command.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1f1a301c457b6$0dbf1ef0$a301280a@.phx
.gbl...[vbcol=seagreen]
> I've double and triple checked the file names and they are
> correct. All permissions look correct. It's only
> one .mdf and one .ldf that need to attach.
> If I run "sp_attach_single_file_db", will that create a
> new log file? If so, is there a way for me to point the
> log file somewhere else. I have to keep the log file on a
> seperate drive due to disk space (60GB log file).
> Thanks again.
>
> missing files or
> and directories.
> sp_attach_single_file_db and only specify
> files)
> message
> and
> that
> get
Error 5173
I had to do a restore of my msdb. Before I did, I
detached all of my databases. The restore went fine, and
I was able to re-attach all of my databases, except for
one of them. When I try to attach it, I get an error that
says "Error 5173: Cannot associate files with different
databases." The ldf files are on a differend drive than
the mdf file, but they were like that before I detached
them. Nothing has changed on this database. How do I get
this DB attached?
Thanks.Hi
At a guess you have either got the wrong file names are missing files or
they are in the wrong order!
You may also want to check the permissions on the files and directories.
If that fails you may want to try sp_attach_single_file_db and only specify
the data file (make sure you keep a copy of the original files)
John
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:1f03501c457ae$c2d08cf0$a601280a@.phx.gbl...
> I had to do a restore of my msdb. Before I did, I
> detached all of my databases. The restore went fine, and
> I was able to re-attach all of my databases, except for
> one of them. When I try to attach it, I get an error that
> says "Error 5173: Cannot associate files with different
> databases." The ldf files are on a differend drive than
> the mdf file, but they were like that before I detached
> them. Nothing has changed on this database. How do I get
> this DB attached?
> Thanks.|||I've double and triple checked the file names and they are
correct. All permissions look correct. It's only
one .mdf and one .ldf that need to attach.
If I run "sp_attach_single_file_db", will that create a
new log file? If so, is there a way for me to point the
log file somewhere else. I have to keep the log file on a
seperate drive due to disk space (60GB log file).
Thanks again.
>--Original Message--
>Hi
>At a guess you have either got the wrong file names are
missing files or
>they are in the wrong order!
>You may also want to check the permissions on the files
and directories.
>If that fails you may want to try
sp_attach_single_file_db and only specify
>the data file (make sure you keep a copy of the original
files)
>John
>"Ken" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1f03501c457ae$c2d08cf0$a601280a@.phx.gbl...
>> I had to do a restore of my msdb. Before I did, I
>> detached all of my databases. The restore went fine,
and
>> I was able to re-attach all of my databases, except for
>> one of them. When I try to attach it, I get an error
that
>> says "Error 5173: Cannot associate files with different
>> databases." The ldf files are on a differend drive than
>> the mdf file, but they were like that before I detached
>> them. Nothing has changed on this database. How do I
get
>> this DB attached?
>> Thanks.
>
>.
>|||Hi
I would make sure that you keep safe copies of all the files!
I would then try to re-attach them in the original locations, if that works
then detach them again and move the files.
Alternatively if you backed up the database before trying this, you could
restore the backup using the MOVE option. See Books online about the RESTORE
command.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1f1a301c457b6$0dbf1ef0$a301280a@.phx.gbl...
> I've double and triple checked the file names and they are
> correct. All permissions look correct. It's only
> one .mdf and one .ldf that need to attach.
> If I run "sp_attach_single_file_db", will that create a
> new log file? If so, is there a way for me to point the
> log file somewhere else. I have to keep the log file on a
> seperate drive due to disk space (60GB log file).
> Thanks again.
>
> >--Original Message--
> >Hi
> >
> >At a guess you have either got the wrong file names are
> missing files or
> >they are in the wrong order!
> >You may also want to check the permissions on the files
> and directories.
> >
> >If that fails you may want to try
> sp_attach_single_file_db and only specify
> >the data file (make sure you keep a copy of the original
> files)
> >
> >John
> >
> >"Ken" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1f03501c457ae$c2d08cf0$a601280a@.phx.gbl...
> >> I had to do a restore of my msdb. Before I did, I
> >> detached all of my databases. The restore went fine,
> and
> >> I was able to re-attach all of my databases, except for
> >> one of them. When I try to attach it, I get an error
> that
> >> says "Error 5173: Cannot associate files with different
> >> databases." The ldf files are on a differend drive than
> >> the mdf file, but they were like that before I detached
> >> them. Nothing has changed on this database. How do I
> get
> >> this DB attached?
> >>
> >> Thanks.
> >
> >
> >.
> >
detached all of my databases. The restore went fine, and
I was able to re-attach all of my databases, except for
one of them. When I try to attach it, I get an error that
says "Error 5173: Cannot associate files with different
databases." The ldf files are on a differend drive than
the mdf file, but they were like that before I detached
them. Nothing has changed on this database. How do I get
this DB attached?
Thanks.Hi
At a guess you have either got the wrong file names are missing files or
they are in the wrong order!
You may also want to check the permissions on the files and directories.
If that fails you may want to try sp_attach_single_file_db and only specify
the data file (make sure you keep a copy of the original files)
John
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:1f03501c457ae$c2d08cf0$a601280a@.phx.gbl...
> I had to do a restore of my msdb. Before I did, I
> detached all of my databases. The restore went fine, and
> I was able to re-attach all of my databases, except for
> one of them. When I try to attach it, I get an error that
> says "Error 5173: Cannot associate files with different
> databases." The ldf files are on a differend drive than
> the mdf file, but they were like that before I detached
> them. Nothing has changed on this database. How do I get
> this DB attached?
> Thanks.|||I've double and triple checked the file names and they are
correct. All permissions look correct. It's only
one .mdf and one .ldf that need to attach.
If I run "sp_attach_single_file_db", will that create a
new log file? If so, is there a way for me to point the
log file somewhere else. I have to keep the log file on a
seperate drive due to disk space (60GB log file).
Thanks again.
>--Original Message--
>Hi
>At a guess you have either got the wrong file names are
missing files or
>they are in the wrong order!
>You may also want to check the permissions on the files
and directories.
>If that fails you may want to try
sp_attach_single_file_db and only specify
>the data file (make sure you keep a copy of the original
files)
>John
>"Ken" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1f03501c457ae$c2d08cf0$a601280a@.phx.gbl...
>> I had to do a restore of my msdb. Before I did, I
>> detached all of my databases. The restore went fine,
and
>> I was able to re-attach all of my databases, except for
>> one of them. When I try to attach it, I get an error
that
>> says "Error 5173: Cannot associate files with different
>> databases." The ldf files are on a differend drive than
>> the mdf file, but they were like that before I detached
>> them. Nothing has changed on this database. How do I
get
>> this DB attached?
>> Thanks.
>
>.
>|||Hi
I would make sure that you keep safe copies of all the files!
I would then try to re-attach them in the original locations, if that works
then detach them again and move the files.
Alternatively if you backed up the database before trying this, you could
restore the backup using the MOVE option. See Books online about the RESTORE
command.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1f1a301c457b6$0dbf1ef0$a301280a@.phx.gbl...
> I've double and triple checked the file names and they are
> correct. All permissions look correct. It's only
> one .mdf and one .ldf that need to attach.
> If I run "sp_attach_single_file_db", will that create a
> new log file? If so, is there a way for me to point the
> log file somewhere else. I have to keep the log file on a
> seperate drive due to disk space (60GB log file).
> Thanks again.
>
> >--Original Message--
> >Hi
> >
> >At a guess you have either got the wrong file names are
> missing files or
> >they are in the wrong order!
> >You may also want to check the permissions on the files
> and directories.
> >
> >If that fails you may want to try
> sp_attach_single_file_db and only specify
> >the data file (make sure you keep a copy of the original
> files)
> >
> >John
> >
> >"Ken" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1f03501c457ae$c2d08cf0$a601280a@.phx.gbl...
> >> I had to do a restore of my msdb. Before I did, I
> >> detached all of my databases. The restore went fine,
> and
> >> I was able to re-attach all of my databases, except for
> >> one of them. When I try to attach it, I get an error
> that
> >> says "Error 5173: Cannot associate files with different
> >> databases." The ldf files are on a differend drive than
> >> the mdf file, but they were like that before I detached
> >> them. Nothing has changed on this database. How do I
> get
> >> this DB attached?
> >>
> >> Thanks.
> >
> >
> >.
> >
Friday, March 9, 2012
ERROR 5120
I tried attach/detach approach to migrate sql server 2000 databases to sql 2005. I installed a fresh copy of sql server 2005 on windows 2003 server. I could move most of my databases except one. While attaching the database to the sql 2005 I got the following error:
Unable to oen "c:\program files\microsoftSQL server \MSSQL.1\MSSQL\DATA\mydb_log.ldf". operating system error 32:" 32(the process cannot access the file because it is being used by another process)". microsoft SQL server, error:5120)
You must be attaching the wrong file (one being used by another database).|||I have a similar problem with the same error:
1) Detached MyDb.mdf (and .ldf) (Auto close = True)
2) copied MyDb.mdf and MyDb.ldf to a different folder
3) Re-attached MyDb from original location
4) Renamed copy to MyDbX.mdf and MyDbX.ldf
5) Attach copy (MyDbX.mdf), and in attach dialog change "Attach As" column to MyDbX
= Error 5120
Problem was the rename at step 4. Without this step the attach works OK, with step 5 renaming the database.
Without going back to check, I think step 4 was ok in SQL 2000 EM.
Error 4305 - Log-Shipping
Hi-
SQL 2000
Win 2k sp4
We have created a maintenance plan to ship one of our databases to a
secondary server. The initial snapshop shipped fine but we get error 4305
when trying to apply subsequent shipped tran logs.
The error says that the transaction log is too new.
We have another maintenance plan running on this DB that does backing
up(complete and tran logs) and it did do a tran-log backup after the
log-shipping snapshot. The log-shipping plan then made a tran log backup and
shipped it(which is too new).
I am trying to clarify if we can have two maintenance plans on the same
db(one for backup and one for log-shipping). Is this is what is causing this
error since they are overlapping.
I have not been able to find any info on this and am inclined to think they
are and I can only have oneplan that does all the backing up and the
log-shipping.
Can anyone confirm?
Thanks.
Hi
With log shipping, you can do a Full and Differential backup at any time,
but transaction logs are sacred. Only log shipping can do the logs, otherwise
you would loose transactions.
Regards
Mike
"Jez" wrote:
> Hi-
> SQL 2000
> Win 2k sp4
> We have created a maintenance plan to ship one of our databases to a
> secondary server. The initial snapshop shipped fine but we get error 4305
> when trying to apply subsequent shipped tran logs.
> The error says that the transaction log is too new.
> We have another maintenance plan running on this DB that does backing
> up(complete and tran logs) and it did do a tran-log backup after the
> log-shipping snapshot. The log-shipping plan then made a tran log backup and
> shipped it(which is too new).
> I am trying to clarify if we can have two maintenance plans on the same
> db(one for backup and one for log-shipping). Is this is what is causing this
> error since they are overlapping.
> I have not been able to find any info on this and am inclined to think they
> are and I can only have oneplan that does all the backing up and the
> log-shipping.
> Can anyone confirm?
> Thanks.
>
>
|||Thanks Mike for quick response
jez.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:BD6B7EE8-97DA-4522-B201-0494A27C54F0@.microsoft.com...[vbcol=seagreen]
> Hi
> With log shipping, you can do a Full and Differential backup at any time,
> but transaction logs are sacred. Only log shipping can do the logs,
> otherwise
> you would loose transactions.
> Regards
> Mike
> "Jez" wrote:
SQL 2000
Win 2k sp4
We have created a maintenance plan to ship one of our databases to a
secondary server. The initial snapshop shipped fine but we get error 4305
when trying to apply subsequent shipped tran logs.
The error says that the transaction log is too new.
We have another maintenance plan running on this DB that does backing
up(complete and tran logs) and it did do a tran-log backup after the
log-shipping snapshot. The log-shipping plan then made a tran log backup and
shipped it(which is too new).
I am trying to clarify if we can have two maintenance plans on the same
db(one for backup and one for log-shipping). Is this is what is causing this
error since they are overlapping.
I have not been able to find any info on this and am inclined to think they
are and I can only have oneplan that does all the backing up and the
log-shipping.
Can anyone confirm?
Thanks.
Hi
With log shipping, you can do a Full and Differential backup at any time,
but transaction logs are sacred. Only log shipping can do the logs, otherwise
you would loose transactions.
Regards
Mike
"Jez" wrote:
> Hi-
> SQL 2000
> Win 2k sp4
> We have created a maintenance plan to ship one of our databases to a
> secondary server. The initial snapshop shipped fine but we get error 4305
> when trying to apply subsequent shipped tran logs.
> The error says that the transaction log is too new.
> We have another maintenance plan running on this DB that does backing
> up(complete and tran logs) and it did do a tran-log backup after the
> log-shipping snapshot. The log-shipping plan then made a tran log backup and
> shipped it(which is too new).
> I am trying to clarify if we can have two maintenance plans on the same
> db(one for backup and one for log-shipping). Is this is what is causing this
> error since they are overlapping.
> I have not been able to find any info on this and am inclined to think they
> are and I can only have oneplan that does all the backing up and the
> log-shipping.
> Can anyone confirm?
> Thanks.
>
>
|||Thanks Mike for quick response
jez.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:BD6B7EE8-97DA-4522-B201-0494A27C54F0@.microsoft.com...[vbcol=seagreen]
> Hi
> With log shipping, you can do a Full and Differential backup at any time,
> but transaction logs are sacred. Only log shipping can do the logs,
> otherwise
> you would loose transactions.
> Regards
> Mike
> "Jez" wrote:
Error 4305 - Log-Shipping
Hi-
SQL 2000
Win 2k sp4
We have created a maintenance plan to ship one of our databases to a
secondary server. The initial snapshop shipped fine but we get error 4305
when trying to apply subsequent shipped tran logs.
The error says that the transaction log is too new.
We have another maintenance plan running on this DB that does backing
up(complete and tran logs) and it did do a tran-log backup after the
log-shipping snapshot. The log-shipping plan then made a tran log backup and
shipped it(which is too new).
I am trying to clarify if we can have two maintenance plans on the same
db(one for backup and one for log-shipping). Is this is what is causing this
error since they are overlapping.
I have not been able to find any info on this and am inclined to think they
are and I can only have oneplan that does all the backing up and the
log-shipping.
Can anyone confirm?
Thanks.Hi
With log shipping, you can do a Full and Differential backup at any time,
but transaction logs are sacred. Only log shipping can do the logs, otherwis
e
you would loose transactions.
Regards
Mike
"Jez" wrote:
> Hi-
> SQL 2000
> Win 2k sp4
> We have created a maintenance plan to ship one of our databases to a
> secondary server. The initial snapshop shipped fine but we get error 4305
> when trying to apply subsequent shipped tran logs.
> The error says that the transaction log is too new.
> We have another maintenance plan running on this DB that does backing
> up(complete and tran logs) and it did do a tran-log backup after the
> log-shipping snapshot. The log-shipping plan then made a tran log backup a
nd
> shipped it(which is too new).
> I am trying to clarify if we can have two maintenance plans on the same
> db(one for backup and one for log-shipping). Is this is what is causing th
is
> error since they are overlapping.
> I have not been able to find any info on this and am inclined to think the
y
> are and I can only have oneplan that does all the backing up and the
> log-shipping.
> Can anyone confirm?
> Thanks.
>
>|||Thanks Mike for quick response
jez.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:BD6B7EE8-97DA-4522-B201-0494A27C54F0@.microsoft.com...[vbcol=seagreen]
> Hi
> With log shipping, you can do a Full and Differential backup at any time,
> but transaction logs are sacred. Only log shipping can do the logs,
> otherwise
> you would loose transactions.
> Regards
> Mike
> "Jez" wrote:
>
SQL 2000
Win 2k sp4
We have created a maintenance plan to ship one of our databases to a
secondary server. The initial snapshop shipped fine but we get error 4305
when trying to apply subsequent shipped tran logs.
The error says that the transaction log is too new.
We have another maintenance plan running on this DB that does backing
up(complete and tran logs) and it did do a tran-log backup after the
log-shipping snapshot. The log-shipping plan then made a tran log backup and
shipped it(which is too new).
I am trying to clarify if we can have two maintenance plans on the same
db(one for backup and one for log-shipping). Is this is what is causing this
error since they are overlapping.
I have not been able to find any info on this and am inclined to think they
are and I can only have oneplan that does all the backing up and the
log-shipping.
Can anyone confirm?
Thanks.Hi
With log shipping, you can do a Full and Differential backup at any time,
but transaction logs are sacred. Only log shipping can do the logs, otherwis
e
you would loose transactions.
Regards
Mike
"Jez" wrote:
> Hi-
> SQL 2000
> Win 2k sp4
> We have created a maintenance plan to ship one of our databases to a
> secondary server. The initial snapshop shipped fine but we get error 4305
> when trying to apply subsequent shipped tran logs.
> The error says that the transaction log is too new.
> We have another maintenance plan running on this DB that does backing
> up(complete and tran logs) and it did do a tran-log backup after the
> log-shipping snapshot. The log-shipping plan then made a tran log backup a
nd
> shipped it(which is too new).
> I am trying to clarify if we can have two maintenance plans on the same
> db(one for backup and one for log-shipping). Is this is what is causing th
is
> error since they are overlapping.
> I have not been able to find any info on this and am inclined to think the
y
> are and I can only have oneplan that does all the backing up and the
> log-shipping.
> Can anyone confirm?
> Thanks.
>
>|||Thanks Mike for quick response
jez.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:BD6B7EE8-97DA-4522-B201-0494A27C54F0@.microsoft.com...[vbcol=seagreen]
> Hi
> With log shipping, you can do a Full and Differential backup at any time,
> but transaction logs are sacred. Only log shipping can do the logs,
> otherwise
> you would loose transactions.
> Regards
> Mike
> "Jez" wrote:
>
Error 4305 - Log-Shipping
Hi-
SQL 2000
Win 2k sp4
We have created a maintenance plan to ship one of our databases to a
secondary server. The initial snapshop shipped fine but we get error 4305
when trying to apply subsequent shipped tran logs.
The error says that the transaction log is too new.
We have another maintenance plan running on this DB that does backing
up(complete and tran logs) and it did do a tran-log backup after the
log-shipping snapshot. The log-shipping plan then made a tran log backup and
shipped it(which is too new).
I am trying to clarify if we can have two maintenance plans on the same
db(one for backup and one for log-shipping). Is this is what is causing this
error since they are overlapping.
I have not been able to find any info on this and am inclined to think they
are and I can only have oneplan that does all the backing up and the
log-shipping.
Can anyone confirm?
Thanks.Hi
With log shipping, you can do a Full and Differential backup at any time,
but transaction logs are sacred. Only log shipping can do the logs, otherwise
you would loose transactions.
Regards
Mike
"Jez" wrote:
> Hi-
> SQL 2000
> Win 2k sp4
> We have created a maintenance plan to ship one of our databases to a
> secondary server. The initial snapshop shipped fine but we get error 4305
> when trying to apply subsequent shipped tran logs.
> The error says that the transaction log is too new.
> We have another maintenance plan running on this DB that does backing
> up(complete and tran logs) and it did do a tran-log backup after the
> log-shipping snapshot. The log-shipping plan then made a tran log backup and
> shipped it(which is too new).
> I am trying to clarify if we can have two maintenance plans on the same
> db(one for backup and one for log-shipping). Is this is what is causing this
> error since they are overlapping.
> I have not been able to find any info on this and am inclined to think they
> are and I can only have oneplan that does all the backing up and the
> log-shipping.
> Can anyone confirm?
> Thanks.
>
>|||Thanks Mike for quick response
jez.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:BD6B7EE8-97DA-4522-B201-0494A27C54F0@.microsoft.com...
> Hi
> With log shipping, you can do a Full and Differential backup at any time,
> but transaction logs are sacred. Only log shipping can do the logs,
> otherwise
> you would loose transactions.
> Regards
> Mike
> "Jez" wrote:
>> Hi-
>> SQL 2000
>> Win 2k sp4
>> We have created a maintenance plan to ship one of our databases to a
>> secondary server. The initial snapshop shipped fine but we get error 4305
>> when trying to apply subsequent shipped tran logs.
>> The error says that the transaction log is too new.
>> We have another maintenance plan running on this DB that does backing
>> up(complete and tran logs) and it did do a tran-log backup after the
>> log-shipping snapshot. The log-shipping plan then made a tran log backup
>> and
>> shipped it(which is too new).
>> I am trying to clarify if we can have two maintenance plans on the same
>> db(one for backup and one for log-shipping). Is this is what is causing
>> this
>> error since they are overlapping.
>> I have not been able to find any info on this and am inclined to think
>> they
>> are and I can only have oneplan that does all the backing up and the
>> log-shipping.
>> Can anyone confirm?
>> Thanks.
>>
SQL 2000
Win 2k sp4
We have created a maintenance plan to ship one of our databases to a
secondary server. The initial snapshop shipped fine but we get error 4305
when trying to apply subsequent shipped tran logs.
The error says that the transaction log is too new.
We have another maintenance plan running on this DB that does backing
up(complete and tran logs) and it did do a tran-log backup after the
log-shipping snapshot. The log-shipping plan then made a tran log backup and
shipped it(which is too new).
I am trying to clarify if we can have two maintenance plans on the same
db(one for backup and one for log-shipping). Is this is what is causing this
error since they are overlapping.
I have not been able to find any info on this and am inclined to think they
are and I can only have oneplan that does all the backing up and the
log-shipping.
Can anyone confirm?
Thanks.Hi
With log shipping, you can do a Full and Differential backup at any time,
but transaction logs are sacred. Only log shipping can do the logs, otherwise
you would loose transactions.
Regards
Mike
"Jez" wrote:
> Hi-
> SQL 2000
> Win 2k sp4
> We have created a maintenance plan to ship one of our databases to a
> secondary server. The initial snapshop shipped fine but we get error 4305
> when trying to apply subsequent shipped tran logs.
> The error says that the transaction log is too new.
> We have another maintenance plan running on this DB that does backing
> up(complete and tran logs) and it did do a tran-log backup after the
> log-shipping snapshot. The log-shipping plan then made a tran log backup and
> shipped it(which is too new).
> I am trying to clarify if we can have two maintenance plans on the same
> db(one for backup and one for log-shipping). Is this is what is causing this
> error since they are overlapping.
> I have not been able to find any info on this and am inclined to think they
> are and I can only have oneplan that does all the backing up and the
> log-shipping.
> Can anyone confirm?
> Thanks.
>
>|||Thanks Mike for quick response
jez.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:BD6B7EE8-97DA-4522-B201-0494A27C54F0@.microsoft.com...
> Hi
> With log shipping, you can do a Full and Differential backup at any time,
> but transaction logs are sacred. Only log shipping can do the logs,
> otherwise
> you would loose transactions.
> Regards
> Mike
> "Jez" wrote:
>> Hi-
>> SQL 2000
>> Win 2k sp4
>> We have created a maintenance plan to ship one of our databases to a
>> secondary server. The initial snapshop shipped fine but we get error 4305
>> when trying to apply subsequent shipped tran logs.
>> The error says that the transaction log is too new.
>> We have another maintenance plan running on this DB that does backing
>> up(complete and tran logs) and it did do a tran-log backup after the
>> log-shipping snapshot. The log-shipping plan then made a tran log backup
>> and
>> shipped it(which is too new).
>> I am trying to clarify if we can have two maintenance plans on the same
>> db(one for backup and one for log-shipping). Is this is what is causing
>> this
>> error since they are overlapping.
>> I have not been able to find any info on this and am inclined to think
>> they
>> are and I can only have oneplan that does all the backing up and the
>> log-shipping.
>> Can anyone confirm?
>> Thanks.
>>
Wednesday, March 7, 2012
Error 3724 : Cannot delete database
Hi,
I wanted to delete some databases used for replication. When I
tried to delete them above error message was displayed. I used the the
following SPs and then deleted the databases :
sp_dboption 'databasename', 'published' , 'false'
go
sp_dboption 'databasename', 'merge publish' , 'false'
go
However one database still did not get deleted and displayed the
above error. I then tried changing the Category field to 0 for the database
in "sysdatabases" table of master. ODBC Error was displayed containing the
message "You do not have enough rights to alter a system database".
Can someone tell me how to solve this problem.
Thanks and Regards,
Harshad
Hi,
Try Tools --> Replication --> Configure
Publishing,Subscribers,Distribution --> Publication database --> Disable the
appropriate database
Then try dropping the database.
Hope this helps.
"Harshad Phadnis" wrote:
> Hi,
> I wanted to delete some databases used for replication. When I
> tried to delete them above error message was displayed. I used the the
> following SPs and then deleted the databases :
> sp_dboption 'databasename', 'published' , 'false'
> go
> sp_dboption 'databasename', 'merge publish' , 'false'
> go
>
> However one database still did not get deleted and displayed the
> above error. I then tried changing the Category field to 0 for the database
> in "sysdatabases" table of master. ODBC Error was displayed containing the
> message "You do not have enough rights to alter a system database".
> Can someone tell me how to solve this problem.
> Thanks and Regards,
> Harshad
I wanted to delete some databases used for replication. When I
tried to delete them above error message was displayed. I used the the
following SPs and then deleted the databases :
sp_dboption 'databasename', 'published' , 'false'
go
sp_dboption 'databasename', 'merge publish' , 'false'
go
However one database still did not get deleted and displayed the
above error. I then tried changing the Category field to 0 for the database
in "sysdatabases" table of master. ODBC Error was displayed containing the
message "You do not have enough rights to alter a system database".
Can someone tell me how to solve this problem.
Thanks and Regards,
Harshad
Hi,
Try Tools --> Replication --> Configure
Publishing,Subscribers,Distribution --> Publication database --> Disable the
appropriate database
Then try dropping the database.
Hope this helps.
"Harshad Phadnis" wrote:
> Hi,
> I wanted to delete some databases used for replication. When I
> tried to delete them above error message was displayed. I used the the
> following SPs and then deleted the databases :
> sp_dboption 'databasename', 'published' , 'false'
> go
> sp_dboption 'databasename', 'merge publish' , 'false'
> go
>
> However one database still did not get deleted and displayed the
> above error. I then tried changing the Category field to 0 for the database
> in "sysdatabases" table of master. ODBC Error was displayed containing the
> message "You do not have enough rights to alter a system database".
> Can someone tell me how to solve this problem.
> Thanks and Regards,
> Harshad
Sunday, February 26, 2012
Error 3013
Got a weird one...
I have 20 databases on one SQL 2000 sp4, .2187 instance.
I also have a backup database and backup log script that I run, which
enumerates the datbase list and runs a backup for each one.
One specific database errors, while the ones before and after it succeed.
It used to work just fine, no changes (especially since I can pinpoint the
failure time to 5:50am, and I know nobody was fooling with the server at
that time).
The error is 3013 - "Backup terminating abnormally" (not the verbatim
error). Error happens for full backup and t-log.
I cannot backup manually through QA or Enterprise manger either. Nor can I
backup to a different location one the same or different drives...
Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in the
\LOG folder. Plenty of space on the backup drive, running out on the T-Log
drive...
Help?
Thanks,
Kevin Hill
3NF Consulting
http://kevinbhill.powweb.com/NewsGroups.htm
My random SQL Server encounters:
http://kevin3nf.blogspot.com/
Forgot to mention that DBCC CheckAlloc and DBCC CheckDB come up clean
"Kevin3NF" <Kevin@.DontSpamMe3NF-inc.com> wrote in message
news:%23Qybs8kFHHA.1080@.TK2MSFTNGP05.phx.gbl...
> Got a weird one...
> I have 20 databases on one SQL 2000 sp4, .2187 instance.
> I also have a backup database and backup log script that I run, which
> enumerates the datbase list and runs a backup for each one.
> One specific database errors, while the ones before and after it succeed.
> It used to work just fine, no changes (especially since I can pinpoint the
> failure time to 5:50am, and I know nobody was fooling with the server at
> that time).
> The error is 3013 - "Backup terminating abnormally" (not the verbatim
> error). Error happens for full backup and t-log.
> I cannot backup manually through QA or Enterprise manger either. Nor can
> I backup to a different location one the same or different drives...
> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in
> the \LOG folder. Plenty of space on the backup drive, running out on the
> T-Log drive...
> Help?
> Thanks,
> Kevin Hill
> 3NF Consulting
> http://kevinbhill.powweb.com/NewsGroups.htm
> My random SQL Server encounters:
> http://kevin3nf.blogspot.com/
>
>
>
|||Kevin3NF wrote:
> Got a weird one...
> I have 20 databases on one SQL 2000 sp4, .2187 instance.
> I also have a backup database and backup log script that I run, which
> enumerates the datbase list and runs a backup for each one.
> One specific database errors, while the ones before and after it succeed.
> It used to work just fine, no changes (especially since I can pinpoint the
> failure time to 5:50am, and I know nobody was fooling with the server at
> that time).
> The error is 3013 - "Backup terminating abnormally" (not the verbatim
> error). Error happens for full backup and t-log.
> I cannot backup manually through QA or Enterprise manger either. Nor can I
> backup to a different location one the same or different drives...
> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in the
> \LOG folder. Plenty of space on the backup drive, running out on the T-Log
> drive...
> Help?
> Thanks,
> Kevin Hill
> 3NF Consulting
> http://kevinbhill.powweb.com/NewsGroups.htm
> My random SQL Server encounters:
> http://kevin3nf.blogspot.com/
>
>
>
Anything in the error log?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Just backup failed
Customer has since changed the db to Simple, and now we cannot change it
back to FULL (3013 and 5069)
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45742818.40402@.realsqlguy.com...
> Kevin3NF wrote:
> Anything in the error log?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Kevin3NF wrote:
> Just backup failed
> Customer has since changed the db to Simple, and now we cannot change it
> back to FULL (3013 and 5069)
>
- Are you able to run queries against this database?
- Are you able to modify data within this database?
- Is something (tape backup, anti-virus software) preventing SQL from
writing to the MDF or LDF file?
- Does the SQL Server service account have proper rights to the MDF and
LDF files?
This sounds to me like something is restricting writes to the
transaction log file...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||See below...everything appears to be working normally except the backups
This database is a replication publisher and that process is working just
fine..
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45743322.7040206@.realsqlguy.com...
> Kevin3NF wrote:
> - Are you able to run queries against this database?
Yes
> - Are you able to modify data within this database?
Yes
> - Is something (tape backup, anti-virus software) preventing SQL from
> writing to the MDF or LDF file?
Not that I am aware of...failures have been happening since 11/30
> - Does the SQL Server service account have proper rights to the MDF and
> LDF files?
Yes
> This sounds to me like something is restricting writes to the transaction
> log file...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Everything else has failed, so we aer using DTS to send everything to a new
database...
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45743322.7040206@.realsqlguy.com...
> Kevin3NF wrote:
> - Are you able to run queries against this database?
> - Are you able to modify data within this database?
> - Is something (tape backup, anti-virus software) preventing SQL from
> writing to the MDF or LDF file?
> - Does the SQL Server service account have proper rights to the MDF and
> LDF files?
> This sounds to me like something is restricting writes to the transaction
> log file...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
I have 20 databases on one SQL 2000 sp4, .2187 instance.
I also have a backup database and backup log script that I run, which
enumerates the datbase list and runs a backup for each one.
One specific database errors, while the ones before and after it succeed.
It used to work just fine, no changes (especially since I can pinpoint the
failure time to 5:50am, and I know nobody was fooling with the server at
that time).
The error is 3013 - "Backup terminating abnormally" (not the verbatim
error). Error happens for full backup and t-log.
I cannot backup manually through QA or Enterprise manger either. Nor can I
backup to a different location one the same or different drives...
Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in the
\LOG folder. Plenty of space on the backup drive, running out on the T-Log
drive...
Help?
Thanks,
Kevin Hill
3NF Consulting
http://kevinbhill.powweb.com/NewsGroups.htm
My random SQL Server encounters:
http://kevin3nf.blogspot.com/
Forgot to mention that DBCC CheckAlloc and DBCC CheckDB come up clean
"Kevin3NF" <Kevin@.DontSpamMe3NF-inc.com> wrote in message
news:%23Qybs8kFHHA.1080@.TK2MSFTNGP05.phx.gbl...
> Got a weird one...
> I have 20 databases on one SQL 2000 sp4, .2187 instance.
> I also have a backup database and backup log script that I run, which
> enumerates the datbase list and runs a backup for each one.
> One specific database errors, while the ones before and after it succeed.
> It used to work just fine, no changes (especially since I can pinpoint the
> failure time to 5:50am, and I know nobody was fooling with the server at
> that time).
> The error is 3013 - "Backup terminating abnormally" (not the verbatim
> error). Error happens for full backup and t-log.
> I cannot backup manually through QA or Enterprise manger either. Nor can
> I backup to a different location one the same or different drives...
> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in
> the \LOG folder. Plenty of space on the backup drive, running out on the
> T-Log drive...
> Help?
> Thanks,
> Kevin Hill
> 3NF Consulting
> http://kevinbhill.powweb.com/NewsGroups.htm
> My random SQL Server encounters:
> http://kevin3nf.blogspot.com/
>
>
>
|||Kevin3NF wrote:
> Got a weird one...
> I have 20 databases on one SQL 2000 sp4, .2187 instance.
> I also have a backup database and backup log script that I run, which
> enumerates the datbase list and runs a backup for each one.
> One specific database errors, while the ones before and after it succeed.
> It used to work just fine, no changes (especially since I can pinpoint the
> failure time to 5:50am, and I know nobody was fooling with the server at
> that time).
> The error is 3013 - "Backup terminating abnormally" (not the verbatim
> error). Error happens for full backup and t-log.
> I cannot backup manually through QA or Enterprise manger either. Nor can I
> backup to a different location one the same or different drives...
> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in the
> \LOG folder. Plenty of space on the backup drive, running out on the T-Log
> drive...
> Help?
> Thanks,
> Kevin Hill
> 3NF Consulting
> http://kevinbhill.powweb.com/NewsGroups.htm
> My random SQL Server encounters:
> http://kevin3nf.blogspot.com/
>
>
>
Anything in the error log?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Just backup failed
Customer has since changed the db to Simple, and now we cannot change it
back to FULL (3013 and 5069)
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45742818.40402@.realsqlguy.com...
> Kevin3NF wrote:
> Anything in the error log?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Kevin3NF wrote:
> Just backup failed
> Customer has since changed the db to Simple, and now we cannot change it
> back to FULL (3013 and 5069)
>
- Are you able to run queries against this database?
- Are you able to modify data within this database?
- Is something (tape backup, anti-virus software) preventing SQL from
writing to the MDF or LDF file?
- Does the SQL Server service account have proper rights to the MDF and
LDF files?
This sounds to me like something is restricting writes to the
transaction log file...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||See below...everything appears to be working normally except the backups
This database is a replication publisher and that process is working just
fine..
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45743322.7040206@.realsqlguy.com...
> Kevin3NF wrote:
> - Are you able to run queries against this database?
Yes
> - Are you able to modify data within this database?
Yes
> - Is something (tape backup, anti-virus software) preventing SQL from
> writing to the MDF or LDF file?
Not that I am aware of...failures have been happening since 11/30
> - Does the SQL Server service account have proper rights to the MDF and
> LDF files?
Yes
> This sounds to me like something is restricting writes to the transaction
> log file...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Everything else has failed, so we aer using DTS to send everything to a new
database...
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45743322.7040206@.realsqlguy.com...
> Kevin3NF wrote:
> - Are you able to run queries against this database?
> - Are you able to modify data within this database?
> - Is something (tape backup, anti-virus software) preventing SQL from
> writing to the MDF or LDF file?
> - Does the SQL Server service account have proper rights to the MDF and
> LDF files?
> This sounds to me like something is restricting writes to the transaction
> log file...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Error 3013
Got a weird one...
I have 20 databases on one SQL 2000 sp4, .2187 instance.
I also have a backup database and backup log script that I run, which
enumerates the datbase list and runs a backup for each one.
One specific database errors, while the ones before and after it succeed.
It used to work just fine, no changes (especially since I can pinpoint the
failure time to 5:50am, and I know nobody was fooling with the server at
that time).
The error is 3013 - "Backup terminating abnormally" (not the verbatim
error). Error happens for full backup and t-log.
I cannot backup manually through QA or Enterprise manger either. Nor can I
backup to a different location one the same or different drives...
Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in the
\LOG folder. Plenty of space on the backup drive, running out on the T-Log
drive...
Help?
Thanks,
Kevin Hill
3NF Consulting
http://kevinbhill.powweb.com/NewsGroups.htm
My random SQL Server encounters:
http://kevin3nf.blogspot.com/Forgot to mention that DBCC CheckAlloc and DBCC CheckDB come up clean
"Kevin3NF" <Kevin@.DontSpamMe3NF-inc.com> wrote in message
news:%23Qybs8kFHHA.1080@.TK2MSFTNGP05.phx.gbl...
> Got a weird one...
> I have 20 databases on one SQL 2000 sp4, .2187 instance.
> I also have a backup database and backup log script that I run, which
> enumerates the datbase list and runs a backup for each one.
> One specific database errors, while the ones before and after it succeed.
> It used to work just fine, no changes (especially since I can pinpoint the
> failure time to 5:50am, and I know nobody was fooling with the server at
> that time).
> The error is 3013 - "Backup terminating abnormally" (not the verbatim
> error). Error happens for full backup and t-log.
> I cannot backup manually through QA or Enterprise manger either. Nor can
> I backup to a different location one the same or different drives...
> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in
> the \LOG folder. Plenty of space on the backup drive, running out on the
> T-Log drive...
> Help?
> Thanks,
> Kevin Hill
> 3NF Consulting
> http://kevinbhill.powweb.com/NewsGroups.htm
> My random SQL Server encounters:
> http://kevin3nf.blogspot.com/
>
>
>|||Kevin3NF wrote:
> Got a weird one...
> I have 20 databases on one SQL 2000 sp4, .2187 instance.
> I also have a backup database and backup log script that I run, which
> enumerates the datbase list and runs a backup for each one.
> One specific database errors, while the ones before and after it succeed.
> It used to work just fine, no changes (especially since I can pinpoint the
> failure time to 5:50am, and I know nobody was fooling with the server at
> that time).
> The error is 3013 - "Backup terminating abnormally" (not the verbatim
> error). Error happens for full backup and t-log.
> I cannot backup manually through QA or Enterprise manger either. Nor can
I
> backup to a different location one the same or different drives...
> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in t
he
> \LOG folder. Plenty of space on the backup drive, running out on the T-Lo
g
> drive...
> Help?
> Thanks,
> Kevin Hill
> 3NF Consulting
> http://kevinbhill.powweb.com/NewsGroups.htm
> My random SQL Server encounters:
> http://kevin3nf.blogspot.com/
>
>
>
Anything in the error log?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Just backup failed
Customer has since changed the db to Simple, and now we cannot change it
back to FULL (3013 and 5069)
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45742818.40402@.realsqlguy.com...
> Kevin3NF wrote:
> Anything in the error log?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Kevin3NF wrote:
> Just backup failed
> Customer has since changed the db to Simple, and now we cannot change it
> back to FULL (3013 and 5069)
>
- Are you able to run queries against this database?
- Are you able to modify data within this database?
- Is something (tape backup, anti-virus software) preventing SQL from
writing to the MDF or LDF file?
- Does the SQL Server service account have proper rights to the MDF and
LDF files?
This sounds to me like something is restricting writes to the
transaction log file...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||See below...everything appears to be working normally except the backups
This database is a replication publisher and that process is working just
fine..
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45743322.7040206@.realsqlguy.com...
> Kevin3NF wrote:
> - Are you able to run queries against this database?
Yes
> - Are you able to modify data within this database?
Yes
> - Is something (tape backup, anti-virus software) preventing SQL from
> writing to the MDF or LDF file?
Not that I am aware of...failures have been happening since 11/30
> - Does the SQL Server service account have proper rights to the MDF and
> LDF files?
Yes
> This sounds to me like something is restricting writes to the transaction
> log file...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Everything else has failed, so we aer using DTS to send everything to a new
database...
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45743322.7040206@.realsqlguy.com...
> Kevin3NF wrote:
> - Are you able to run queries against this database?
> - Are you able to modify data within this database?
> - Is something (tape backup, anti-virus software) preventing SQL from
> writing to the MDF or LDF file?
> - Does the SQL Server service account have proper rights to the MDF and
> LDF files?
> This sounds to me like something is restricting writes to the transaction
> log file...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
I have 20 databases on one SQL 2000 sp4, .2187 instance.
I also have a backup database and backup log script that I run, which
enumerates the datbase list and runs a backup for each one.
One specific database errors, while the ones before and after it succeed.
It used to work just fine, no changes (especially since I can pinpoint the
failure time to 5:50am, and I know nobody was fooling with the server at
that time).
The error is 3013 - "Backup terminating abnormally" (not the verbatim
error). Error happens for full backup and t-log.
I cannot backup manually through QA or Enterprise manger either. Nor can I
backup to a different location one the same or different drives...
Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in the
\LOG folder. Plenty of space on the backup drive, running out on the T-Log
drive...
Help?
Thanks,
Kevin Hill
3NF Consulting
http://kevinbhill.powweb.com/NewsGroups.htm
My random SQL Server encounters:
http://kevin3nf.blogspot.com/Forgot to mention that DBCC CheckAlloc and DBCC CheckDB come up clean
"Kevin3NF" <Kevin@.DontSpamMe3NF-inc.com> wrote in message
news:%23Qybs8kFHHA.1080@.TK2MSFTNGP05.phx.gbl...
> Got a weird one...
> I have 20 databases on one SQL 2000 sp4, .2187 instance.
> I also have a backup database and backup log script that I run, which
> enumerates the datbase list and runs a backup for each one.
> One specific database errors, while the ones before and after it succeed.
> It used to work just fine, no changes (especially since I can pinpoint the
> failure time to 5:50am, and I know nobody was fooling with the server at
> that time).
> The error is 3013 - "Backup terminating abnormally" (not the verbatim
> error). Error happens for full backup and t-log.
> I cannot backup manually through QA or Enterprise manger either. Nor can
> I backup to a different location one the same or different drives...
> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in
> the \LOG folder. Plenty of space on the backup drive, running out on the
> T-Log drive...
> Help?
> Thanks,
> Kevin Hill
> 3NF Consulting
> http://kevinbhill.powweb.com/NewsGroups.htm
> My random SQL Server encounters:
> http://kevin3nf.blogspot.com/
>
>
>|||Kevin3NF wrote:
> Got a weird one...
> I have 20 databases on one SQL 2000 sp4, .2187 instance.
> I also have a backup database and backup log script that I run, which
> enumerates the datbase list and runs a backup for each one.
> One specific database errors, while the ones before and after it succeed.
> It used to work just fine, no changes (especially since I can pinpoint the
> failure time to 5:50am, and I know nobody was fooling with the server at
> that time).
> The error is 3013 - "Backup terminating abnormally" (not the verbatim
> error). Error happens for full backup and t-log.
> I cannot backup manually through QA or Enterprise manger either. Nor can
I
> backup to a different location one the same or different drives...
> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in t
he
> \LOG folder. Plenty of space on the backup drive, running out on the T-Lo
g
> drive...
> Help?
> Thanks,
> Kevin Hill
> 3NF Consulting
> http://kevinbhill.powweb.com/NewsGroups.htm
> My random SQL Server encounters:
> http://kevin3nf.blogspot.com/
>
>
>
Anything in the error log?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Just backup failed
Customer has since changed the db to Simple, and now we cannot change it
back to FULL (3013 and 5069)
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45742818.40402@.realsqlguy.com...
> Kevin3NF wrote:
> Anything in the error log?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Kevin3NF wrote:
> Just backup failed
> Customer has since changed the db to Simple, and now we cannot change it
> back to FULL (3013 and 5069)
>
- Are you able to run queries against this database?
- Are you able to modify data within this database?
- Is something (tape backup, anti-virus software) preventing SQL from
writing to the MDF or LDF file?
- Does the SQL Server service account have proper rights to the MDF and
LDF files?
This sounds to me like something is restricting writes to the
transaction log file...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||See below...everything appears to be working normally except the backups
This database is a replication publisher and that process is working just
fine..
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45743322.7040206@.realsqlguy.com...
> Kevin3NF wrote:
> - Are you able to run queries against this database?
Yes
> - Are you able to modify data within this database?
Yes
> - Is something (tape backup, anti-virus software) preventing SQL from
> writing to the MDF or LDF file?
Not that I am aware of...failures have been happening since 11/30
> - Does the SQL Server service account have proper rights to the MDF and
> LDF files?
Yes
> This sounds to me like something is restricting writes to the transaction
> log file...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Everything else has failed, so we aer using DTS to send everything to a new
database...
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45743322.7040206@.realsqlguy.com...
> Kevin3NF wrote:
> - Are you able to run queries against this database?
> - Are you able to modify data within this database?
> - Is something (tape backup, anti-virus software) preventing SQL from
> writing to the MDF or LDF file?
> - Does the SQL Server service account have proper rights to the MDF and
> LDF files?
> This sounds to me like something is restricting writes to the transaction
> log file...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Error 3013
Got a weird one...
I have 20 databases on one SQL 2000 sp4, .2187 instance.
I also have a backup database and backup log script that I run, which
enumerates the datbase list and runs a backup for each one.
One specific database errors, while the ones before and after it succeed.
It used to work just fine, no changes (especially since I can pinpoint the
failure time to 5:50am, and I know nobody was fooling with the server at
that time).
The error is 3013 - "Backup terminating abnormally" (not the verbatim
error). Error happens for full backup and t-log.
I cannot backup manually through QA or Enterprise manger either. Nor can I
backup to a different location one the same or different drives...
Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in the
\LOG folder. Plenty of space on the backup drive, running out on the T-Log
drive...
Help?
Thanks,
Kevin Hill
3NF Consulting
http://kevinbhill.powweb.com/NewsGroups.htm
My random SQL Server encounters:
http://kevin3nf.blogspot.com/Forgot to mention that DBCC CheckAlloc and DBCC CheckDB come up clean
"Kevin3NF" <Kevin@.DontSpamMe3NF-inc.com> wrote in message
news:%23Qybs8kFHHA.1080@.TK2MSFTNGP05.phx.gbl...
> Got a weird one...
> I have 20 databases on one SQL 2000 sp4, .2187 instance.
> I also have a backup database and backup log script that I run, which
> enumerates the datbase list and runs a backup for each one.
> One specific database errors, while the ones before and after it succeed.
> It used to work just fine, no changes (especially since I can pinpoint the
> failure time to 5:50am, and I know nobody was fooling with the server at
> that time).
> The error is 3013 - "Backup terminating abnormally" (not the verbatim
> error). Error happens for full backup and t-log.
> I cannot backup manually through QA or Enterprise manger either. Nor can
> I backup to a different location one the same or different drives...
> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in
> the \LOG folder. Plenty of space on the backup drive, running out on the
> T-Log drive...
> Help?
> Thanks,
> Kevin Hill
> 3NF Consulting
> http://kevinbhill.powweb.com/NewsGroups.htm
> My random SQL Server encounters:
> http://kevin3nf.blogspot.com/
>
>
>|||Kevin3NF wrote:
> Got a weird one...
> I have 20 databases on one SQL 2000 sp4, .2187 instance.
> I also have a backup database and backup log script that I run, which
> enumerates the datbase list and runs a backup for each one.
> One specific database errors, while the ones before and after it succeed.
> It used to work just fine, no changes (especially since I can pinpoint the
> failure time to 5:50am, and I know nobody was fooling with the server at
> that time).
> The error is 3013 - "Backup terminating abnormally" (not the verbatim
> error). Error happens for full backup and t-log.
> I cannot backup manually through QA or Enterprise manger either. Nor can I
> backup to a different location one the same or different drives...
> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in the
> \LOG folder. Plenty of space on the backup drive, running out on the T-Log
> drive...
> Help?
> Thanks,
> Kevin Hill
> 3NF Consulting
> http://kevinbhill.powweb.com/NewsGroups.htm
> My random SQL Server encounters:
> http://kevin3nf.blogspot.com/
>
>
>
Anything in the error log?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Just backup failed
Customer has since changed the db to Simple, and now we cannot change it
back to FULL (3013 and 5069)
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45742818.40402@.realsqlguy.com...
> Kevin3NF wrote:
>> Got a weird one...
>> I have 20 databases on one SQL 2000 sp4, .2187 instance.
>> I also have a backup database and backup log script that I run, which
>> enumerates the datbase list and runs a backup for each one.
>> One specific database errors, while the ones before and after it succeed.
>> It used to work just fine, no changes (especially since I can pinpoint
>> the failure time to 5:50am, and I know nobody was fooling with the server
>> at that time).
>> The error is 3013 - "Backup terminating abnormally" (not the verbatim
>> error). Error happens for full backup and t-log.
>> I cannot backup manually through QA or Enterprise manger either. Nor can
>> I backup to a different location one the same or different drives...
>> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in
>> the \LOG folder. Plenty of space on the backup drive, running out on the
>> T-Log drive...
>> Help?
>> Thanks,
>> Kevin Hill
>> 3NF Consulting
>> http://kevinbhill.powweb.com/NewsGroups.htm
>> My random SQL Server encounters:
>> http://kevin3nf.blogspot.com/
>>
>>
> Anything in the error log?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Kevin3NF wrote:
> Just backup failed
> Customer has since changed the db to Simple, and now we cannot change it
> back to FULL (3013 and 5069)
>
- Are you able to run queries against this database?
- Are you able to modify data within this database?
- Is something (tape backup, anti-virus software) preventing SQL from
writing to the MDF or LDF file?
- Does the SQL Server service account have proper rights to the MDF and
LDF files?
This sounds to me like something is restricting writes to the
transaction log file...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||See below...everything appears to be working normally except the backups
This database is a replication publisher and that process is working just
fine..
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45743322.7040206@.realsqlguy.com...
> Kevin3NF wrote:
>> Just backup failed
>> Customer has since changed the db to Simple, and now we cannot change it
>> back to FULL (3013 and 5069)
> - Are you able to run queries against this database?
Yes
> - Are you able to modify data within this database?
Yes
> - Is something (tape backup, anti-virus software) preventing SQL from
> writing to the MDF or LDF file?
Not that I am aware of...failures have been happening since 11/30
> - Does the SQL Server service account have proper rights to the MDF and
> LDF files?
Yes
> This sounds to me like something is restricting writes to the transaction
> log file...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Everything else has failed, so we aer using DTS to send everything to a new
database...
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45743322.7040206@.realsqlguy.com...
> Kevin3NF wrote:
>> Just backup failed
>> Customer has since changed the db to Simple, and now we cannot change it
>> back to FULL (3013 and 5069)
> - Are you able to run queries against this database?
> - Are you able to modify data within this database?
> - Is something (tape backup, anti-virus software) preventing SQL from
> writing to the MDF or LDF file?
> - Does the SQL Server service account have proper rights to the MDF and
> LDF files?
> This sounds to me like something is restricting writes to the transaction
> log file...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
I have 20 databases on one SQL 2000 sp4, .2187 instance.
I also have a backup database and backup log script that I run, which
enumerates the datbase list and runs a backup for each one.
One specific database errors, while the ones before and after it succeed.
It used to work just fine, no changes (especially since I can pinpoint the
failure time to 5:50am, and I know nobody was fooling with the server at
that time).
The error is 3013 - "Backup terminating abnormally" (not the verbatim
error). Error happens for full backup and t-log.
I cannot backup manually through QA or Enterprise manger either. Nor can I
backup to a different location one the same or different drives...
Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in the
\LOG folder. Plenty of space on the backup drive, running out on the T-Log
drive...
Help?
Thanks,
Kevin Hill
3NF Consulting
http://kevinbhill.powweb.com/NewsGroups.htm
My random SQL Server encounters:
http://kevin3nf.blogspot.com/Forgot to mention that DBCC CheckAlloc and DBCC CheckDB come up clean
"Kevin3NF" <Kevin@.DontSpamMe3NF-inc.com> wrote in message
news:%23Qybs8kFHHA.1080@.TK2MSFTNGP05.phx.gbl...
> Got a weird one...
> I have 20 databases on one SQL 2000 sp4, .2187 instance.
> I also have a backup database and backup log script that I run, which
> enumerates the datbase list and runs a backup for each one.
> One specific database errors, while the ones before and after it succeed.
> It used to work just fine, no changes (especially since I can pinpoint the
> failure time to 5:50am, and I know nobody was fooling with the server at
> that time).
> The error is 3013 - "Backup terminating abnormally" (not the verbatim
> error). Error happens for full backup and t-log.
> I cannot backup manually through QA or Enterprise manger either. Nor can
> I backup to a different location one the same or different drives...
> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in
> the \LOG folder. Plenty of space on the backup drive, running out on the
> T-Log drive...
> Help?
> Thanks,
> Kevin Hill
> 3NF Consulting
> http://kevinbhill.powweb.com/NewsGroups.htm
> My random SQL Server encounters:
> http://kevin3nf.blogspot.com/
>
>
>|||Kevin3NF wrote:
> Got a weird one...
> I have 20 databases on one SQL 2000 sp4, .2187 instance.
> I also have a backup database and backup log script that I run, which
> enumerates the datbase list and runs a backup for each one.
> One specific database errors, while the ones before and after it succeed.
> It used to work just fine, no changes (especially since I can pinpoint the
> failure time to 5:50am, and I know nobody was fooling with the server at
> that time).
> The error is 3013 - "Backup terminating abnormally" (not the verbatim
> error). Error happens for full backup and t-log.
> I cannot backup manually through QA or Enterprise manger either. Nor can I
> backup to a different location one the same or different drives...
> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in the
> \LOG folder. Plenty of space on the backup drive, running out on the T-Log
> drive...
> Help?
> Thanks,
> Kevin Hill
> 3NF Consulting
> http://kevinbhill.powweb.com/NewsGroups.htm
> My random SQL Server encounters:
> http://kevin3nf.blogspot.com/
>
>
>
Anything in the error log?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Just backup failed
Customer has since changed the db to Simple, and now we cannot change it
back to FULL (3013 and 5069)
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45742818.40402@.realsqlguy.com...
> Kevin3NF wrote:
>> Got a weird one...
>> I have 20 databases on one SQL 2000 sp4, .2187 instance.
>> I also have a backup database and backup log script that I run, which
>> enumerates the datbase list and runs a backup for each one.
>> One specific database errors, while the ones before and after it succeed.
>> It used to work just fine, no changes (especially since I can pinpoint
>> the failure time to 5:50am, and I know nobody was fooling with the server
>> at that time).
>> The error is 3013 - "Backup terminating abnormally" (not the verbatim
>> error). Error happens for full backup and t-log.
>> I cannot backup manually through QA or Enterprise manger either. Nor can
>> I backup to a different location one the same or different drives...
>> Tried to generate a mini-dump using DBCC DUMPTRIGGER, but got nothing in
>> the \LOG folder. Plenty of space on the backup drive, running out on the
>> T-Log drive...
>> Help?
>> Thanks,
>> Kevin Hill
>> 3NF Consulting
>> http://kevinbhill.powweb.com/NewsGroups.htm
>> My random SQL Server encounters:
>> http://kevin3nf.blogspot.com/
>>
>>
> Anything in the error log?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Kevin3NF wrote:
> Just backup failed
> Customer has since changed the db to Simple, and now we cannot change it
> back to FULL (3013 and 5069)
>
- Are you able to run queries against this database?
- Are you able to modify data within this database?
- Is something (tape backup, anti-virus software) preventing SQL from
writing to the MDF or LDF file?
- Does the SQL Server service account have proper rights to the MDF and
LDF files?
This sounds to me like something is restricting writes to the
transaction log file...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||See below...everything appears to be working normally except the backups
This database is a replication publisher and that process is working just
fine..
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45743322.7040206@.realsqlguy.com...
> Kevin3NF wrote:
>> Just backup failed
>> Customer has since changed the db to Simple, and now we cannot change it
>> back to FULL (3013 and 5069)
> - Are you able to run queries against this database?
Yes
> - Are you able to modify data within this database?
Yes
> - Is something (tape backup, anti-virus software) preventing SQL from
> writing to the MDF or LDF file?
Not that I am aware of...failures have been happening since 11/30
> - Does the SQL Server service account have proper rights to the MDF and
> LDF files?
Yes
> This sounds to me like something is restricting writes to the transaction
> log file...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Everything else has failed, so we aer using DTS to send everything to a new
database...
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45743322.7040206@.realsqlguy.com...
> Kevin3NF wrote:
>> Just backup failed
>> Customer has since changed the db to Simple, and now we cannot change it
>> back to FULL (3013 and 5069)
> - Are you able to run queries against this database?
> - Are you able to modify data within this database?
> - Is something (tape backup, anti-virus software) preventing SQL from
> writing to the MDF or LDF file?
> - Does the SQL Server service account have proper rights to the MDF and
> LDF files?
> This sounds to me like something is restricting writes to the transaction
> log file...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Friday, February 24, 2012
Error 28055 when creating mirroring endpoint.
I tried to build a certifictae based endpoint to mirror databases.
However all the time I try to create the endpoint I got the Message:
"The certificta e'MyCert' is not valid fer endpoint authentication. The
certificate must have a privare key encrypted with the database master key
and current UTC date has to be between the certificate start date end
expiration date."
I used the sripts like shown in the example at the help file:
use master
create master key encryption by password ='MyPassword'
go
create certificate 'MyCert' with subject ='Mirroring Certificate',
Expiry_date = '12/31/2020'
go
create Endpoint EPSorry to fast fingers :-)
create Endpoint EP
state=started
as tcp (listener_port=5022, listener_ID=ALL)
for database_mirroring(
Authentication=certificate MyCert,
encryption=required Algorithm AES,
Role=all)
and this create statement will fail all the time
Regards
Michael|||If you live in the eastern hemisphere the newly created cert start date is
not yet valid for a number of hours equal to the difference of hours between
your zone and GMT. So simply specify a start date for the certificate as
well:
create certificate 'MyCert' with subject ='Mirroring Certificate',
Expiry_date = '12/31/2020',
start_date = '02/21/2007';
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Michael W. Schneider" <MichaelWSchneider@.discussions.microsoft.com> wrote
in message news:346EF8CB-35F7-4CEF-8DD0-7A38DBCC87C8@.microsoft.com...
> Sorry to fast fingers :-)
> create Endpoint EP
> state=started
> as tcp (listener_port=5022, listener_ID=ALL)
> for database_mirroring(
> Authentication=certificate MyCert,
> encryption=required Algorithm AES,
> Role=all)
> and this create statement will fail all the time
> Regards
> Michael
>
However all the time I try to create the endpoint I got the Message:
"The certificta e'MyCert' is not valid fer endpoint authentication. The
certificate must have a privare key encrypted with the database master key
and current UTC date has to be between the certificate start date end
expiration date."
I used the sripts like shown in the example at the help file:
use master
create master key encryption by password ='MyPassword'
go
create certificate 'MyCert' with subject ='Mirroring Certificate',
Expiry_date = '12/31/2020'
go
create Endpoint EPSorry to fast fingers :-)
create Endpoint EP
state=started
as tcp (listener_port=5022, listener_ID=ALL)
for database_mirroring(
Authentication=certificate MyCert,
encryption=required Algorithm AES,
Role=all)
and this create statement will fail all the time
Regards
Michael|||If you live in the eastern hemisphere the newly created cert start date is
not yet valid for a number of hours equal to the difference of hours between
your zone and GMT. So simply specify a start date for the certificate as
well:
create certificate 'MyCert' with subject ='Mirroring Certificate',
Expiry_date = '12/31/2020',
start_date = '02/21/2007';
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Michael W. Schneider" <MichaelWSchneider@.discussions.microsoft.com> wrote
in message news:346EF8CB-35F7-4CEF-8DD0-7A38DBCC87C8@.microsoft.com...
> Sorry to fast fingers :-)
> create Endpoint EP
> state=started
> as tcp (listener_port=5022, listener_ID=ALL)
> for database_mirroring(
> Authentication=certificate MyCert,
> encryption=required Algorithm AES,
> Role=all)
> and this create statement will fail all the time
> Regards
> Michael
>
Sunday, February 19, 2012
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
> > > >
> > > >
> > > >
> >
> >
> >
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
> > > >
> > > >
> > > >
> >
> >
> >
error 229
Hello there !
I encountered this error when i followed next steps:
started Enterprise Manager >> My SQL Server group >>
Databases >> Tables
This is the error description:
Error 229 :
1)SELECT permission denied on object 'sysobjects'
database 'diamond', owner 'dbo'
2)SELECT permission denied on object 'sysindexes'
database 'diamond', owner 'dbo'
When i selected my database(diamond) & right clicked then
same error occured but with different objects -
'sysfiles', 'sysusers', 'sysfilegroups'The owner of the 'diamond' database hasn't granted you any permission to
view the contents. Get the owner of the database to grant you permission and
retry.
--
HTH
Ryan Waight, MCDBA, MCSE
"Deepak Sakpal" <deepaksakpal@.hotmail.com> wrote in message
news:183d01c3865c$aff6b4c0$a301280a@.phx.gbl...
> Hello there !
> I encountered this error when i followed next steps:
> started Enterprise Manager >> My SQL Server group >>
> Databases >> Tables
> This is the error description:
> Error 229 :
> 1)SELECT permission denied on object 'sysobjects'
> database 'diamond', owner 'dbo'
> 2)SELECT permission denied on object 'sysindexes'
> database 'diamond', owner 'dbo'
>
> When i selected my database(diamond) & right clicked then
> same error occured but with different objects -
> 'sysfiles', 'sysusers', 'sysfilegroups'|||Seems like someone revoked SELECT permission on those system table for the public role. Or perhaps
DENY the permissions.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Deepak Sakpal" <deepaksakpal@.hotmail.com> wrote in message
news:183d01c3865c$aff6b4c0$a301280a@.phx.gbl...
> Hello there !
> I encountered this error when i followed next steps:
> started Enterprise Manager >> My SQL Server group >>
> Databases >> Tables
> This is the error description:
> Error 229 :
> 1)SELECT permission denied on object 'sysobjects'
> database 'diamond', owner 'dbo'
> 2)SELECT permission denied on object 'sysindexes'
> database 'diamond', owner 'dbo'
>
> When i selected my database(diamond) & right clicked then
> same error occured but with different objects -
> 'sysfiles', 'sysusers', 'sysfilegroups'
I encountered this error when i followed next steps:
started Enterprise Manager >> My SQL Server group >>
Databases >> Tables
This is the error description:
Error 229 :
1)SELECT permission denied on object 'sysobjects'
database 'diamond', owner 'dbo'
2)SELECT permission denied on object 'sysindexes'
database 'diamond', owner 'dbo'
When i selected my database(diamond) & right clicked then
same error occured but with different objects -
'sysfiles', 'sysusers', 'sysfilegroups'The owner of the 'diamond' database hasn't granted you any permission to
view the contents. Get the owner of the database to grant you permission and
retry.
--
HTH
Ryan Waight, MCDBA, MCSE
"Deepak Sakpal" <deepaksakpal@.hotmail.com> wrote in message
news:183d01c3865c$aff6b4c0$a301280a@.phx.gbl...
> Hello there !
> I encountered this error when i followed next steps:
> started Enterprise Manager >> My SQL Server group >>
> Databases >> Tables
> This is the error description:
> Error 229 :
> 1)SELECT permission denied on object 'sysobjects'
> database 'diamond', owner 'dbo'
> 2)SELECT permission denied on object 'sysindexes'
> database 'diamond', owner 'dbo'
>
> When i selected my database(diamond) & right clicked then
> same error occured but with different objects -
> 'sysfiles', 'sysusers', 'sysfilegroups'|||Seems like someone revoked SELECT permission on those system table for the public role. Or perhaps
DENY the permissions.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Deepak Sakpal" <deepaksakpal@.hotmail.com> wrote in message
news:183d01c3865c$aff6b4c0$a301280a@.phx.gbl...
> Hello there !
> I encountered this error when i followed next steps:
> started Enterprise Manager >> My SQL Server group >>
> Databases >> Tables
> This is the error description:
> Error 229 :
> 1)SELECT permission denied on object 'sysobjects'
> database 'diamond', owner 'dbo'
> 2)SELECT permission denied on object 'sysindexes'
> database 'diamond', owner 'dbo'
>
> When i selected my database(diamond) & right clicked then
> same error occured but with different objects -
> 'sysfiles', 'sysusers', 'sysfilegroups'
Error 22272: Cannot load the DLL xpstar.dll...
Hi,
I would look for MSVCR71.DLL in SYSTEM32. I would suspect that this is
what is missing.)
>
I would look for MSVCR71.DLL in SYSTEM32. I would suspect that this is
what is missing.)
>
Nope. That one's in 9 different places in the system, including
system32.|||Pupkin (spamagnet@.dorrk.com) writes:
>>
>I would look for MSVCR71.DLL in SYSTEM32. I would suspect that this is
>what is missing.)
>>
>
Nope. That one's in 9 different places in the system, including
system32.
And XPSTAR.DLL itself is in the BINN directory for the instance?
I'm running SQL Server 2000 on a Windows 2003 server. It's serving
several small web site databases.
About a month ago my automatic backups stopped working, and whenever I
try to edit or set up a new maintenance plan or export or import data
through Enterprise Manager, I get the following error:
Error 22272: Cannot load the DLL xpstar.dll, or one of the DLLs it
references. Reason: 127(The specified procedure could not be found.).
I've also tried doing these tasks with some third-party apps (EMS,
Teratrax), and I get the same errors.
I've Googled this but the two fixes I've read about (reinstalling MDAC
and reinstalling SP4) didn't work for me.
I'm not sure what caused it -- probably a Windows update -- because I
didn't notice it until several weeks later.
Any idea what I can do to fix this without completely reinstalling SQL
Server? Its other functionality is unaffected; maybe it's a Windows
problem?
At a loss. Thanks.Pupkin (spamagnet@.dorrk.com) writes:
Quote:
Originally Posted by
About a month ago my automatic backups stopped working, and whenever I
try to edit or set up a new maintenance plan or export or import data
through Enterprise Manager, I get the following error:
>
Error 22272: Cannot load the DLL xpstar.dll, or one of the DLLs it
references. Reason: 127(The specified procedure could not be found.).
>
I've also tried doing these tasks with some third-party apps (EMS,
Teratrax), and I get the same errors.
I would look for MSVCR71.DLL in SYSTEM32. I would suspect that this is
what is missing.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In article <Xns981A1C3B728EYazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
Quote:
Originally Posted by
Pupkin (spamagnet@.dorrk.com) writes:
Quote:
Originally Posted by
About a month ago my automatic backups stopped working, and whenever I
try to edit or set up a new maintenance plan or export or import data
through Enterprise Manager, I get the following error:
Error 22272: Cannot load the DLL xpstar.dll, or one of the DLLs it
references. Reason: 127(The specified procedure could not be found.).
I've also tried doing these tasks with some third-party apps (EMS,
Teratrax), and I get the same errors.
>
I would look for MSVCR71.DLL in SYSTEM32. I would suspect that this is
what is missing.)
>
Nope. That one's in 9 different places in the system, including
system32.|||Pupkin (spamagnet@.dorrk.com) writes:
Quote:
Originally Posted by
In article <Xns981A1C3B728EYazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
Quote:
Originally Posted by
>Pupkin (spamagnet@.dorrk.com) writes:
Quote:
Originally Posted by
About a month ago my automatic backups stopped working, and whenever I
try to edit or set up a new maintenance plan or export or import data
through Enterprise Manager, I get the following error:
>
Error 22272: Cannot load the DLL xpstar.dll, or one of the DLLs it
references. Reason: 127(The specified procedure could not be found.).
>
I've also tried doing these tasks with some third-party apps (EMS,
Teratrax), and I get the same errors.
>>
>I would look for MSVCR71.DLL in SYSTEM32. I would suspect that this is
>what is missing.)
>>
>
Nope. That one's in 9 different places in the system, including
system32.
And XPSTAR.DLL itself is in the BINN directory for the instance?
I chanced on MSVCR71 after having looked at the dependencies for
XPSTAR.DLL and that was the only that looked suspicious.
Then again, the message says that the procedure could not be found.
I believe that if an entire DLL was missing, the error would be another.
But whether this missing procedure is xpstar.dll itself, or in a
referenced DLL that has been replaced I have no idea.
Reinstall the SQL Server tools? Don't forget to reapply service packs.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Friday, February 17, 2012
Error 21776: [SQL-DMO] The name 'XXXX' was not found in the Users collection
Hello --
We have a SQL Server 2000 machine that has approximately 500 user databases.
Don't ask why -- I did not create this mess.
Anyway, we have about 200 users access the SQL Server as a DSS machine
using SA. We've finally weaned all users off of SA and each have their on
ID per customer.
Tonight, I was finally trying to change the "SA" password from within
Enterprise Manager and received the following message:
Error 21776: [SQL-DMO] The name 'XXXX' was not found in the Users
collection. If the name is a qualified name, use [] to separate various
parts of the name, and try again.
It looks like I'm receiving this message for every database we have.
I brought up another instance of Enterprise Manager and looked at each
database property and noticed that the owner of database was either "SA",
"NT user that happens to be logged into server most of the time", or some
other local SQL Server user.
How do I fix this problem?
All databases should simply belong to DBO and no one inparticular.
The problem is, I have multiple people that create databases and they all
log into the server differenently and use different steps in creating the
database.
Can I simply ignore these error messages?
If I do need to change the owner of all 500 databases, is there an easier
way than manually running sp_changedbowner?
--
...david
http://www.micro-mess.com
http://www.va-mustang.com
If you wish to reply to me personally, please remove
the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!All databases need to be owned by a valid login or you may get errors in
Enterprise Manager. You can use a script below to generate a script that
changes database ownership to the login of your choosing.
SELECT 'EXEC ' +
CATALOG_NAME +
'..sp_changedbowner ''sa'''
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN
(
'master',
'model',
'msdb',
'tempdb',
'distribution'
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DavidM" <scandal_123@.cox.net> wrote in message
news:uYTjmAcxDHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hello --
> We have a SQL Server 2000 machine that has approximately 500 user
databases.
> Don't ask why -- I did not create this mess.
> Anyway, we have about 200 users access the SQL Server as a DSS machine
> using SA. We've finally weaned all users off of SA and each have their on
> ID per customer.
> Tonight, I was finally trying to change the "SA" password from within
> Enterprise Manager and received the following message:
> Error 21776: [SQL-DMO] The name 'XXXX' was not found in the Users
> collection. If the name is a qualified name, use [] to separate various
> parts of the name, and try again.
> It looks like I'm receiving this message for every database we have.
> I brought up another instance of Enterprise Manager and looked at each
> database property and noticed that the owner of database was either "SA",
> "NT user that happens to be logged into server most of the time", or some
> other local SQL Server user.
> How do I fix this problem?
> All databases should simply belong to DBO and no one inparticular.
> The problem is, I have multiple people that create databases and they all
> log into the server differenently and use different steps in creating the
> database.
> Can I simply ignore these error messages?
> If I do need to change the owner of all 500 databases, is there an easier
> way than manually running sp_changedbowner?
> --
> ...david
> http://www.micro-mess.com
> http://www.va-mustang.com
> If you wish to reply to me personally, please remove
> the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!
>
We have a SQL Server 2000 machine that has approximately 500 user databases.
Don't ask why -- I did not create this mess.
Anyway, we have about 200 users access the SQL Server as a DSS machine
using SA. We've finally weaned all users off of SA and each have their on
ID per customer.
Tonight, I was finally trying to change the "SA" password from within
Enterprise Manager and received the following message:
Error 21776: [SQL-DMO] The name 'XXXX' was not found in the Users
collection. If the name is a qualified name, use [] to separate various
parts of the name, and try again.
It looks like I'm receiving this message for every database we have.
I brought up another instance of Enterprise Manager and looked at each
database property and noticed that the owner of database was either "SA",
"NT user that happens to be logged into server most of the time", or some
other local SQL Server user.
How do I fix this problem?
All databases should simply belong to DBO and no one inparticular.
The problem is, I have multiple people that create databases and they all
log into the server differenently and use different steps in creating the
database.
Can I simply ignore these error messages?
If I do need to change the owner of all 500 databases, is there an easier
way than manually running sp_changedbowner?
--
...david
http://www.micro-mess.com
http://www.va-mustang.com
If you wish to reply to me personally, please remove
the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!All databases need to be owned by a valid login or you may get errors in
Enterprise Manager. You can use a script below to generate a script that
changes database ownership to the login of your choosing.
SELECT 'EXEC ' +
CATALOG_NAME +
'..sp_changedbowner ''sa'''
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN
(
'master',
'model',
'msdb',
'tempdb',
'distribution'
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DavidM" <scandal_123@.cox.net> wrote in message
news:uYTjmAcxDHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hello --
> We have a SQL Server 2000 machine that has approximately 500 user
databases.
> Don't ask why -- I did not create this mess.
> Anyway, we have about 200 users access the SQL Server as a DSS machine
> using SA. We've finally weaned all users off of SA and each have their on
> ID per customer.
> Tonight, I was finally trying to change the "SA" password from within
> Enterprise Manager and received the following message:
> Error 21776: [SQL-DMO] The name 'XXXX' was not found in the Users
> collection. If the name is a qualified name, use [] to separate various
> parts of the name, and try again.
> It looks like I'm receiving this message for every database we have.
> I brought up another instance of Enterprise Manager and looked at each
> database property and noticed that the owner of database was either "SA",
> "NT user that happens to be logged into server most of the time", or some
> other local SQL Server user.
> How do I fix this problem?
> All databases should simply belong to DBO and no one inparticular.
> The problem is, I have multiple people that create databases and they all
> log into the server differenently and use different steps in creating the
> database.
> Can I simply ignore these error messages?
> If I do need to change the owner of all 500 databases, is there an easier
> way than manually running sp_changedbowner?
> --
> ...david
> http://www.micro-mess.com
> http://www.va-mustang.com
> If you wish to reply to me personally, please remove
> the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!
>
Subscribe to:
Posts (Atom)