I am running some DBCC checks as a routine maintenance task on my sql server 2000 servers. I am running DBCC CHECKDB, DBCC CHECKCONSTRAINTS and DBCC CHECKCATALOG. I have established a set of procedures and tables to capture the output from the error log after running these DBCC commands. After running all three, it appears the same info captured for CHECKDB is not logged for these two: CHECKCONSTRAINTS and CHECKCATALOG. Does anyone know why these are not logged and if it is even necessary for me to run these 2 extra DBCC checks? If not, can anyone recommend what database consistency checks should be executed on a daily basis for a production database server?
DBCC CHECKDB (DBNAME) executed by lshores found 0 errors and repaired 0 errors. Elapsed time: 0 hours 20 minutes 32 seconds.
We are trying to trace the source of deadlocks using dbcc. Unfortunately the deadlock list produced only contains part of the statments; we cannot see the full SQL of the statements involved.
dbcc seems to have a limit of 255 characters per line. Is there anyway to configure the output from dbcc to use more than this?
Alternatively, is there any way to use SQL Profiler to the same end? The source of the deadlocks looks like some adhoc stored procedures - I'm not sure if it is possible to output the source of these SPs via the profiler. Is this possible?
I am playing with DBCC command to check the contsrainst on a perticular table (DBCC CHECKCONSTRAINTS ('myTable') WITH ALL_CONSTRAINTS), it always gives the following result:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I click on menu item SSIS and enable logging to my SQL Database (SSIS log provider for SQL Server). When I run the package it runs fine for the first time. Upon subsequent runs I get the following error.
[Log provider "SSIS log provider for SQL Server"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.".
It seems like I need to drop the table every time to avoid this error. Is there a way I can log all package logs to single table historically? Any weblink or tip is appreciated.
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
I am getting the following error when I log into SQL server using MS SQL Server Management Studio.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) (.Net SqlClient Data Provider)".
I tried to allow remote connections using Surface Area Configuration, but it is not allowing me to do so.
I am not sure what I need to do. Anybody, please advice.
I'm using the Execute SQL Task to import error messages into a table. I'm using the following:
"INSERT INTO SSISLog (EventDescription) VALUES (" +@[System::ErrorDescription]+")"
I've got an import task with deliberate errors in the file to be imported to test the error logging task. When I execute it, I get the following messages:
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". )" failed with the following error: "The name 'An' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (There was an error with input column "IssueNo" (94) on input "Destination Input" (68). The column status returned was: "The value could not be converted because of a potential loss of data.". )" failed with the following error: "The name 'There' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (The "input "Destination Input" (68)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (68)" specifies failure on error. An error occurred on the specified object of the specified component. )" failed with the following error: "The name 'The' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (The ProcessInput method on component "Destination - tbl_clients" (55) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. )" failed with the following error: "The name 'The' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (Thread "WorkThread0" has exited with error code 0xC0209029. )" failed with the following error: "The name 'Thread' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Hi, I decided to use the SQL Server log provider to store logging data of all my Integration Services packages. I also created some reports about this data for operating purposes. I have a problem occurs the name of the executing package is not always written to the log,but the name of the single task which failed. But that is not very useful information for operating, because I do not see any chance to get the name of the package by the information which is logged in the sysdtslog90 table in the database which I defined for SSIS Logging.
How do I configure the package to always log the package information into the table, too?
How can I avoid certain messages from SQL Server being recorded into the Event viewer ? For example, every time I truncate the transaction log with 'Backup log with truncate_only', It is being recorded into the Event viewer as an Error. But, I know that it is not an error. How can I avoid this ?
I have a small requirement in SSIS Error Logging Mechanism. Presently in my SSIS package i am using a File Connection Manager for creating a Log file. I have a problem on this regard. Every time when i am executing my DTS package, the error log messages are getting appended to my error log at OS level (say D:error_messg.log). And for this reason whenever my DTS package is getting executed the size of the file is keep on increasing and there by killing my disk space.
I have a requirement for this error logging mechanism. At any time my log file should not exceed more than 20MB. Or can we remove the log events a week ago or say more than 2 days or say. Just ensuring the log file do not fill up the disk space eventually.
How can we do this? Any suggestions are greatly appreciated.
I got a error message with SQL 2000 SP3. I do not know this is a bug or table is real corrupted. need help with this one.
(1) DBCC CHKDB output:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'TAMVSQLNYUNYU' as 'NYUMC amnyuclustadmin' (trusted) Starting maintenance of database 'PowerPath_NYU' on 9/6/2005 4:20:03 AM [1] Database PowerPath_NYU: Check Data and Index Linkage... [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8964: [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 405628538. The text, ntext, or image node at page (1:1001056), slot 10, text ID 1595707686912 is not referenced. [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'audit_record' (object ID 405628538). [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in database 'PowerPath_NYU'. [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PowerPath_NYU ).
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 405628538. The text, ntext, or image node at page (1:1001056), slot 10, text ID 1595707686912 is not referenced. [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'audit_record' (object ID 405628538). [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in database 'PowerPath_NYU'. [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PowerPath_NYU ). ** Execution Time: 0 hrs, 2 mins, 20 secs **
[2] Database PowerPath_NYU: Check Data and Index Allocation... WARNING: Database PowerPath_NYU is currently being used by 36 user(s). This may cause spurious problems to be reported.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[3] Database PowerPath_NYU: Check System Data...
** Execution Time: 0 hrs, 0 mins, 3 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance for database 'PowerPath_NYU' on 9/6/2005 4:22:27 AM SQLMAINT.EXE Process Exit Code: 1 (Failed)
I also run DBCC CHKTABLE and follwoing is the output. Server: Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 405628538. The text, ntext, or image node at page (1:1001056), slot 10, text ID 1595707686912 is not referenced. DBCC results for 'audit_record'. There are 1958345 rows in 42549 pages for object 'audit_record'. CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'audit_record' (object ID 405628538). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (PowerPath_NYU.dbo.audit_record ).
Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) If I insert a wrong password I get the same error message. Which means that I don’t have access to the file. I definitely could use some help since from my provider I get none. Thank you Dov Kruman
Hello,I was wondering if there were any built-in objects to handle error handling, in any of the SQL Server objects, or just to use event log or trace or debug or something like that. Or is it not recommended at all to use try.. catch, for whatever reason.Thanks a lot.
I am getting the following error. “The user instance login flag is not supported on this version of SQL Server. The connection will be closed.� Background i created a site on my development machine, and every thing worked, this computer is running sql EX. I uploaded the site to the production server (running sql 2005 standard) and changed the connection string (i have used this connection string with other dB’s to display data only, and it works) but i tried it with the ASPNETDB.mdf in the AppData folder and i get the above message.
Do i have to set some special permissions in the db for it to work. I need to be able to insert, update, and delete in this app.
I presently have Error Logging turned on for the SQL Executive, but I no longer want it. When I open up the "Configure SQL Executive" window under the Server menu, the "Error Log File" field is dimmed and there is no option to turn it off.
Just like in DTS where we can add error file so that if the DTS package fails we can see what caused the DTS to fail, likewise do we have anything like error logging file in SSIS. I greatly appreciate your help on this. thanks!!
I have merge replication setup to a bunch of mobile subscribers. I want everything managed from the server and I want everything to run continuously. The reason we want this is that we want subscribers to automatically synchronize data within 60 seconds of plugging into the network from any location. We do not want to write anything to have to detect connectivity and initiate it. We do not want it initiated from a subscriber either. This creates a SEVERE logging issue and has halted a roll out at 2 different customers. We can't get beyond about 60 subscribers on a system that was running 3700 subscribers in SQL Server 2000. The server quite literally freezes and becomes non-responsive 1 - 2 times per day. We've tracked this back to the volume of logging into the Windows event log that is being caused by the replication engine.
When subscribers are disconnected, they throw constant errors, to the tune of 1 error every 2 minutes into the SQL Server error log as well as the Windows event log. This is because someone decided that these should be level 18 errors. There is no error. The publisher could not contact the subscriber, so I want it to simply log an error, shut up, go back to sleep, and then try again. I do NOT want a message in my SQL Server error log and I do NOT want a message in my Windows event log.
We are currently logging over 60,000 messages per day into both the Windows event log and the SQL Server error logs for something that we KNOW is a NORMAL operational state of the system. The merge agent doesn't have a parameter that I can feed it to ignore 14151 errors.
The culprit is in sys.sp_MSrepl_raiseerror. Since it is a system object, I can't override it and change the severity level of the error. So, right now we are stuck and the SQL Server error log as well as Windows event logs are being rendered quite useless on the system.
Does anyone have any idea how I can forcefully change sys.sp_MSrepl_raiseerror or in some other way suppress the logging of 14151 errors from the Windows and SQL Server logs?
How can I copy the error messages when I execute a SSIS package?. The Progress tab or the Execution results tab both dont have the means to copy the results. I would like the errors to be output to a text file under a directory on my drive system (some thing like C:SSISExecResults.txt). Is this possible?. If so how do I configure my package to output the package execution results to a text file?.
When the embedded DTS package fails at runtime, and logging has been enabled for the package (and all log events selected for reporting on for the package and the task), the DTS error (i.e. any meaningful errors) are not thrown up to/caught by the SSIS/outer level. All you get is something like:
COMException - error returned from a call to a COM component.
Does anyone have any comments &/or know to get errors thrown from within an embedded DTS package, thrown up to the wrapping SSIS package?
I just installed SQL Server 2005 with SP2 on Vista. When I try to login from SQL Server Management Studio, I get the following error message in the log: --- 2007-09-15 18:58:02.23 Logon Error: 18456, Severity: 14, State: 11. 2007-09-15 18:58:02.23 Logon Login failed for user '<computer name><user account>'.[CLIENT: <local machine>] ---
Since I can't login, I can't try some of the stuff I have seen here, such as creating new logins etc.
I installed Analysis Server and Integration Services at the same time. Logging in to them both works fine.
I use windows authentication. I don't know if changing that helps, as I don't know how to change it without logging in in the first place...
I am new in this profession and have been hearing DBCC error. Could somebody please explain to me how to handle it in case I some day get it on my application. I do not want to wait until I face it before I start trouble-shooting what I do not know how to go about it. Thanks for your kind help.
1. What is the status of the database when the DBCC command is executed wheather it is locked or users can still acess the data.
2. Is there a way to identify Object for which the ID 2029614669 is given when DBCC is executed on the Database.
the message log is as follows.
Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '(Object ID 2029614669)'. Check sysobjects. DBCC results for 'xyz'. CHECKDB found 2 allocation errors and 2 consistency errors in database 'xyz'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
What is the solution to rectify this problem immediately.The size of the Database is 18.5 GB as on today.
Apart from what is suggested to use DBCC not frequently what should be the schedule to use DBCC if the databse size is increassing.
I am receiving an error message from my DBCC CHECKDB: Msg 2511, Sev 16: Table Corrupt: Object ID 1670297010. Index ID 3 Keys out of order on page (11:354289), slots 246 and 247. I have found the Object ID is sp_MSaddinitialarticle. Will running the DBCC CHECKDB with the REPAIR_REBUILD fix this problem?? Help!!
I ran DBCC checktable, and got the messege "A row on page 234113 was accessed that has an illegal length of -28032 in database", Could any one help me what does this error mean?
I have some errors in my DB, we do have a backup plan in place, but the person who put it in place is no longer here. It's all automated and running, the problem is , we do A LOT of transactions a day, and the error has been in the db since 9/20(2.5 weeks) when someone shut the power off accidentally. So i'm reluctant to do a backup, mainly since i have NO idea how to do one, we do full and incremental, full on weekend, incremental weeknights.
below is the biggest problem SYSINDEXES :(. a few other tables had problems but DBCC with repair_allow_data_loss has fixed those with 'hardly' any data loss. What can I do here?
THanks in advance
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 2, index ID 0, page ID (1:4917). The PageId in the page header = (1:4925). Server: Msg 8928, Level 16, State 1, Line 1 Object ID 2, index ID 255: Page (1:4917) could not be processed. See other errors for details. DBCC results for 'sysindexes'. There are 485 rows in 30 pages for object 'sysindexes'. CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'sysindexes' (object ID 2).
1. How to fix these errors? 2. Are the records in the DB still good? 3. How to prevent this happening again?
The database server is running Windows 2000 sp4 and SQL 2000 sp3. It's running on the RAID5 in MS Cluster environment.
I really appreciate your assistance. Thank you.
The error message samples are:
Msg 8974, Sev 16: Text node referenced by more than one node. Object ID 837578022, text, ntext, or image node page (1:130912), slot 27, text ID 25207869603840 is pointed to by page (1:1392667), slot 2 and by page (1:1210230), slot 10. [SQLSTATE 42000] Msg 8961, Sev 16: Table error: Object ID 837578022. The text, ntext, or image node at page (1:130912), slot 30, text ID 25199404974080 does not match its reference from page (1:131226), slot 17. [SQLSTATE 42000] Msg 8974, Sev 16: Text node referenced by more than one node. Object ID 837578022, text, ntext, or image node page (1:130912), slot 30, text ID 25254380437504 is pointed to by page (1:1392667), slot 4 and by page (1:131226), slot 17. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 837578022. The text, ntext, or image node at page (1:134162), slot 23, text ID 25204615217152 is not referenced. [SQLSTATE 42000]
Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 211 consistency errors in table 'TABLE_ONE' (object ID 837578022). [SQLSTATE 01000] Msg 2536, Sev 16: DBCC results for 'TABLE_TWO'. [SQLSTATE 01000]
Msg 8974, Sev 16: Text node referenced by more than one node. Object ID 853578079, text, ntext, or image node page (1:129320), slot 38, text ID 25496001314816 is pointed to by page (1:239021), slot 101 and by page (1:487008), slot 100. [SQLSTATE 42000] Msg 8961, Sev 16: Table error: Object ID 853578079. The text, ntext, or image node at page (1:129320), slot 39, text ID 25199327903744 does not match its reference from page (1:487008), slot 101. [SQLSTATE 42000] Msg 8974, Sev 16: Text node referenced by more than one node. Object ID 853578079, text, ntext, or image node page (1:129320), slot 39, text ID 25496001642496 is pointed to by page (1:239021), slot 102 and by page (1:487008), slot 101. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 853578079. The text, ntext, or image node at page (1:136454), slot 1, text ID 25031875559424 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 853578079. The text, ntext, or image node at page (1:136454), slot 2, text ID 25031875887104 is not referenced. [SQLSTATE 42000]
Msg 8964, Sev 16: Table error: Object ID 853578079. The text, ntext, or image node at page (1:136603), slot 2, text ID 25031875231744 is not referenced. [SQLSTATE 42000] Msg 8928, Sev 16: Object ID 853578079, index ID 255: Page (1:137476) could not be processed. See other errors for details. [SQLSTATE 42000] Msg 8939, Sev 16: Table error: Object ID 853578079, index ID 255, page (1:137476). Test (m_freeCnt == freeCnt) failed. Values are 357 and 525. [SQLSTATE 42000]
Msg 2593, Sev 16: There are 1972569 rows in 14051 pages for object 'TABLE_TWO'. [SQLSTATE 01000] Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 166 consistency errors in table 'TABLE_TWO' (object ID 853578079). [SQLSTATE 01000]"
hi i got problem when i want to shrink file into one of my database it always cause error "A severe error occurred on the current command. The results, if any, should be discarded."
it happend in DBCC ShrinkFile (@name, 0) i dont know why it occurs anybody can help?
thanks
-- shrink all files within the database Declare @curFiles Cursor Declare @Name sysname
Set @curFiles = Cursor Local Fast_Forward Read_Only For Select RTrim(LTrim(name)) from sysfiles
Open @curFiles
Fetch Next From @curFiles Into @Name While @@Fetch_Status = 0 Begin
-- Cause problem because transaction log backup had run at the same time --that the shrink was occurring which is what caused this latch problem DBCC ShrinkFile (@name, 0) Fetch Next From @curFiles Into @Name End Close @curFiles; deallocate @curFiles; go
arifliminto86
[edit by tkizer]: moved thread out of Data Corruption forum
Any body got any ideas how we might get around the following error.
command used:
dbcc shrinkfile('DB_Data',EMPTYFILE)
Result:
DBCC SHRINKFILE: Page 3:9224674 could not be moved because the partition to which it belonged was dropped.
Msg 2555, Level 16, State 2, Line 1
Cannot move all contents of file "DB_Data" to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
the file needs to be split from 1 x 200G file into multiple data files in the same filegroup. works for a couple of hours the gives this error, file is still 100G, but has 99% empty space.
I have used skiperrors parameter for 2601,2627 and 20598 errors in my Transaction replication setup. When transactions with above error are encountered, its skipped but I€™m not getting sql command that is skipped in MSrepl_errors table. Since SQL server 2000 is not saving transaction sequence number in the MSrepl_errors we are not getting the command, which have the errors.
Is there any way to track the commands those are skipped in transactional replication.
