This database sits on SQL server 2000. The problem is with a copy of the production database. My concern is that if it is a hardware issue then I have more problems on my hand. Bellow are the errors. teh first one is from the SQL server log the second is using DBCC CHECKDB
Point to note : this a VMware server.
I/O error (torn page) detected during read at offset 0x0000000093a000 in file 'E:Program FilesMicrosoft SQL ServerMSSQLdataPPFILESSQLT.mdf'..
*-*-*-*-
DBCC results for 'ppfilessqlt'.
DBCC results for 'sysobjects'.
There are 156 rows in 3 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 181 rows in 12 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 819 rows in 14 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 173 rows in 15 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 83 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 21 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 1350 rows in 69 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 508 rows in 3 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'PPFILES_L#MGP100'.
There are 9 rows in 1 pages for object 'PPFILES_L#MGP100'.
DBCC results for 'PPFILES_L#OHP100'.
There are 65966 rows in 1627 pages for object 'PPFILES_L#OHP100'.
DBCC results for 'MTDPromoSale'.
There are 0 rows in 0 pages for object 'MTDPromoSale'.
DBCC results for 'PPFILES_L#PGP100'.
There are 1028 rows in 21 pages for object 'PPFILES_L#PGP100'.
DBCC results for 'PPFILES_L#PRP100'.
There are 2772 rows in 65 pages for object 'PPFILES_L#PRP100'.
DBCC results for 'CalendarMaster'.
There are 6 rows in 1 pages for object 'CalendarMaster'.
DBCC results for 'PPFILES_L#RLP100'.
There are 73 rows in 2 pages for object 'PPFILES_L#RLP100'.
DBCC results for '~TMPCLP15451'.
There are 480 rows in 10 pages for object '~TMPCLP15451'.
DBCC results for 'PPFILES_L#POP100'.
There are 57 rows in 1 pages for object 'PPFILES_L#POP100'.
DBCC results for 'PPFILES_L#RUP100'.
There are 4 rows in 1 pages for object 'PPFILES_L#RUP100'.
DBCC results for 'PPFILES_L#SLP150'.
There are 0 rows in 1 pages for object 'PPFILES_L#SLP150'.
DBCC results for 'PPFILES_L#VIP100'.
There are 0 rows in 0 pages for object 'PPFILES_L#VIP100'.
DBCC results for 'Divisions'.
There are 10 rows in 1 pages for object 'Divisions'.
DBCC results for 'Brand'.
There are 121 rows in 2 pages for object 'Brand'.
DBCC results for 'UserList'.
There are 20 rows in 1 pages for object 'UserList'.
DBCC results for 'ItemProposal_H'.
There are 18 rows in 1 pages for object 'ItemProposal_H'.
DBCC results for 'MTDSale'.
There are 138 rows in 2 pages for object 'MTDSale'.
DBCC results for 'SystemSetup'.
There are 199 rows in 2 pages for object 'SystemSetup'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1726629194, index ID 0: Page (1:1181) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1726629194, index ID 0, page (1:1181). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
DBCC results for 'PPFILES_L#SYP100'.
There are 188 rows in 2 pages for object 'PPFILES_L#SYP100'.
DBCC results for 'PPFILES_L#CSP100'.
There are 11130 rows in 521 pages for object 'PPFILES_L#CSP100'.
DBCC results for 'Product'.
There are 10640 rows in 133 pages for object 'Product'.
DBCC results for 'ProductSetup'.
There are 15974 rows in 215 pages for object 'ProductSetup'.
DBCC results for 'PPFILES_L#ODP100'.
There are 1557837 rows in 16352 pages for object 'PPFILES_L#ODP100'.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'PPFILES_L#ODP100' (object ID 1726629194).
DBCC results for 'ItemProposal_H_temp'.
There are 6 rows in 1 pages for object 'ItemProposal_H_temp'.
DBCC results for 'ItemProposal_D_Temp'.
There are 102 rows in 2 pages for object 'ItemProposal_D_Temp'.
DBCC results for 'Messages'.
There are 9 rows in 1 pages for object 'Messages'.
DBCC results for 'ItemProposal_D'.
There are 543 rows in 8 pages for object 'ItemProposal_D'.
DBCC results for 'dtproperties'.
There are 7 rows in 1 pages for object 'dtproperties'.
DBCC results for 'AnimationCode'.
There are 128 rows in 2 pages for object 'AnimationCode'.
DBCC results for 'L#RLP100_Back'.
There are 23235 rows in 664 pages for object 'L#RLP100_Back'.
DBCC results for 'Calendar'.
There are 81 rows in 1 pages for object 'Calendar'.
DBCC results for 'L#CSP100_Back'.
There are 486 rows in 35 pages for object 'L#CSP100_Back'.
CHECKDB found 0 allocation errors and 2 consistency errors in database 'PPFILESSQLT'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PPFILESSQLT ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I am receiving an Msg 2540 on a database when I perform the DBCCs. It looks like the indexes on this one table got corrupt. I know this because when I perform the DBCCs with the noindex option then I do not get any errors. I tried to drop and recreate the indexes (two indexes - one clustered and one unique) but to no avail. The dbccs continued to produce the error.
If someone has any ideas please respond. This is a prodn problem and so any input would be greatly appreciated. Thanks again Kiran
After upgrading a server from SQL Server 2000 to 2005, an index was suddenly being reported as corrupt in the SQL Server log (probably every time an Insert was done). DBCC CHECKDB / CHECKTABLE reported no errors, with or without the new WITH DATA_PURITY option.
Anyone else experienced something similar?
How can an index be reported as corrupt when DBCC doesn't report it?
Dropping and re-creating the index solved the problem. I've restored a backup of the database (made before dropping/re-creating the index) to try repeating the problem, but no success so far.
I am trying to index through the columns of MyTable so I can do the same work on all columns. I know how to get the column names from MyTable but when I use @MyColName in the SELECT statement to get MyTable Column 0 Row values I get a table with the column name in each row cell. I can't get the syntax correct to return the value in each cell for that column. This is a extremely simplified example !!!!!!DECLARE @MyColName nvarchar(30) --Get the MyTable Column 0 NameSELECT @MyColName = Col_Name(Object_ID('MyTable'), 0) --Display the MyTable Column 0 Row valuesSELECT @MyColName FROM MyTable --This is the syntax I can not get correct
It's been always said that it is best to put index on commonly joined fields in the table. But putting too much index on the table would cause the table to be slow on insert and update.
My question is, how do you deal with your fields that uses look up tables? Like for example for these fields
Those fields don't come a big part in the table, though when I query the table I always join them with their respective primary table to get their respective text value. Do I still need to put Index & FK relationships to these fields?
What fields are normally good candidates for index or fk relationships?
SQL SERVER 2000System let's you alter the system tables and add indexes. However, it won'tlet you drop the index afterward.Anybody know how to drop an index on a system table?Thanks,Kevin
In SQL Server 2000 one could DBReindex every index that exists in a given database. You can do the same in SQL Server 2005. But how can this be done with the new Alter Index command? It does not allow me to pass in a variable for the object. Any ideas on how to get this done in with Alter Index in 2005? Thanks!
This I can't get to work:
DECLARE @TableName nvarchar(100) SET @TableName = 'Account'
USE database; GO ALTER INDEX ALL ON @TableName REBUILD GO
What is the best procedure/sequence to reduce some tables containing large number of rows of a SQL 2000 server? The idea is first to check which tables grow extremely fast (all statistics, user or log tables), reduce the table according to the number of months the user wishes to keep in the table. As a second step backup remaining rows of table as txt files on harddisk (using DTS), UPDATE STATISTICS and re-indexing reduced table. Run DTS Package every month once (delete oldest month and backup newest month) and do the same as above to keep size of tables adequate. What is a fast way to reduce number of rows of a large table - the following example produces an error (timeout expired) of my ADO connection when executing: SET @str = 'DELETE FROM ' + @ProcessTable + ' WHERE ' + @SelectedColumn + ' < DATEADD (m,' +' -' + @KeepMonthsInDatabase + ', + GETDATE())' EXEC (@str) Adding ConnectionTimout = 0 did not help unfortunately.
What is the best way to re-index the table just maintained?
There is a index: CustomerInfo_1 with keys: customerId, EnteryDate DESC I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored? I tried system tables such as sysindexes and sysindexkeys tables. But could not find it. Any help in this regard will be truly appreciated.
There is a index: CustomerInfo_1 with keys: customerId, EnteryDate DESC I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored? I tried system tables such as sysindexes and sysindexkeys tables. But could not find it. Any help in this regard will be truly appreciated.
Is there any reason why only heaps can have forwarded records while tables with clustered index have to resort to page split when a page becomes saturated with data due to an update or insertion? Both have drawbacks, forwarded records add another level of indirection, leading to increased I/O overhead, while page split may cause further split in the parent pages which can cascade even further. May be that the cascading is a one-time only cost to be incurred, while the indirection overhead repeats everytime there is a query. Then why not go for page split in heap also? The additional overhead to be had for adding another pointer to the IAM page is also one-time.
I am developing database support for my application and I am using EDB. I would like to use sort orders to make the code easier but I was wondering if sort orders generate index tables. I mean, do they only sort the records during addition or they also generate index tables to speed up queries. In other words, do they make the database file bigger? In the MSDN documentation I can not find anything on this.
get a list of indexes on all tables in all dbs on a SQL server.
If the index property to allow page locks is off, then turn it on, re-index and turn it off again. My problem is: i want to use ' Use <db>' statement in the middle of my script but it is not working.I tried using dynamic SQL with
set @cmd='use '+ @dbname exec (@cmd) But this is not working. Can we use 'use' statement in the middle of a script? If not what is the alternative?
My script looks as follows:
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
declare @Index varchar(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd='use '+@Database
print (@cmd)
exec (@cmd)
declare IndexCursor CURSOR for select name from sys.indexes where object_id=object_id(@Table)
open IndexCursor
fetch next from IndexCursor into @Index
print (@table)
--select name from sys.indexes where object_id=object_id(@Table)
print (@index)
WHILE @@FETCH_STATUS = 0
begin
if (INDEXPROPERTY(OBJECT_ID(@Table),@Index,'IsPageLockDisallowed')=1)
begin
print (@Index + ' page locking off')
-- SET @cmd='ALTER INDEX '+@Index +' ON '+@Table+' SET (ALLOW_PAGE_LOCKS = ON) reorganize
-- ALTER INDEX '+@Index +' ON '+@Table+' SET (ALLOW_PAGE_LOCKS = OFF)'
end
else
begin
print (@Index + ' page locking on')
-- SET @cmd='ALTER INDEX '+@Index +' ON '+@Table+' reorganize'
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
We are using SQL2005. I have a stored proc that runs a Select query based on a complex view. The sproc has two input date parameters (StartDate and EndDate). We are experiencing SQL timeout problems when the sproc is run with certain Start and End Dates. We have run the SQL Profiler and created a trace (trc) file (We've used the 'Default' trace configuration). We have used the trace file in SQL Server Management Studio to try and automatically create indexes on some of our tables. Unfortunatly SQL Server Management does not make any index recommendations. I think we are not capturing the right information in our trace file to allow SQL Server Management Studio to do its job. How do I use SQL Profiler to capture a trace of my sprocs query, so that it can be used by SQL Server Management Studio, to recommend index changes? Any help appreciated. Reagrds, Paul.
Just wonder if system base tables always use clustered index? I am using SQL Server 2005 and find sys.sysidxstats base table is using heap, not clustered index. Why?
I would like to know the impacts (if any) of adding nonclustered index with included columns on large tables (these tables are populated by bulk insert from text files).
SELECT e1.EntityIdentity as CompanyID FROM dbo.Entitye1 --company JOIN dbo.EntityAssociationea ON e1.EntityID = ea.EntityID1 JOIN dbo.Entitye2 --user ON ea.EntityID2 = e2.EntityID
This query occurs as a sub-query in many stored procedures where exists a WHERE clause that includes CompanyID IN (above query).
Since dbo.Entity and dbo.EntityAssociation change infrequently I thought that an indexed view would really improve performance. But I've found one of the seemingly undocumented Microsoft features when trying to create the clustered index and get the following error msg:
Msg 1947, Level 16, State 1, Line 1 Cannot create index on view "ROICore.dbo.vEntityEntityAssociation_CompanyUser". The view contains a self join on "ROICore.dbo.Entity".
I really need to improve performance on this subquery. Entity currently has over 20m rows and EntityAssociation over 35m rows and both are growing.
How to improve performance? Indexes on both tables for the most part give index seeks, but I thought my saviour might be the index view. Obviously this will not work.
1. I need to make use of in memory engine for my pr-existed develop procedures ,tables ,index. do I need and code changes for application and how to store tables /indexes in OLTP memory
Assume table index may have primary key index as well.
2. If table with one primary index and 2 foreign constraints, 3 non clusters indexed. which one able o load to memory area and how t do that.
3. In memory is lock free zone. usually locks will happpen in RDMS context . how this works without locks.
We are planning to upgrade. We are using Sql 2008R2 now. Which is the better option migrating to SQL 2012 or migrating to 2014?I am thinking 2014 has memory optimized tables and updatable column stored index. So it is better option.
I found this script some where on internet..i am not sure what it does and how to i solve the problem because my friend has already run the script and i donno how to get back to the original configuration.
/* this script works on a database that is a copy of pubs. I created this database easily with DTS This only works on SQL Server 7 databases */
/* NOTE: This script will truly corrupt a database. It should not be run on ANY real database. It is only for simulating corruption for training purposes.
*/ sp_configure allow, 1 go reconfigure with override go update sysindexes set FirstIAM = 1234 where id = OBJECT_ID('roysched') go sp_configure allow, 0 go reconfigure with override go
How to sovlve the issue after the corrupt is done...how do i modify the info back to original..
Are there any other process or script that i can simulate to corrupt the databse and solutions pertaining to the same..i would like to experiment and then see how it goes and how quickly i can solve...
The ldf file of a db was deleted by mistake. This was done whilst the SQL Server Service was still running. The db was not detached cleanly. I ran sp_detach_db on the db and tried re-attaching but got the error message: "Database [dbname] cannot be opened because some of the files could not be activated". Subsequently I have tried creating a new db called the same as the old one. Stopping the MSSQL service, copying over the mdf and re-starting the service. The db still comes up as suspect and the error log shows "Device activation error. The physical file name 'D:LogsETERBK_Log.LDF' may be incorrect". I have tried copying over the new ldf to the correct location but the same message appears. I am not interested in getting the data back but would like to get back the stored procedures. Can anyone help this is urgernt?
Have a corrupt SQL database that I am unable to repair using any of the repair options with DBCC CHECKDB. Below is the output from the repair_allow_data_loss option.
I can't seem to figure out how to go about fixing this. Any takers?!
Server: Msg 8946, Level 16, State 12, Line 2 Table error: Allocation page (1:24264) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page. Server: Msg 8921, Level 16, State 1, Line 1 CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 0, index ID 0, page (1:24264). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 0, index ID 0, page (1:24264). Test ((m_type >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 101. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 0, index ID 0, page (1:24264). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 0, index ID 0, page (1:24264). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 0, index ID 0, page (1:24264). Test ((m_type >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 101. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 0, index ID 0, page (1:24264). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192. Server: Msg 8998, Level 16, State 1, Line 1 Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 7 pages from (1:24264) to (1:32351). See other errors for cause. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 0, index ID 0, page (1:32352). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 0, index ID 0, page (1:32352). Test ((m_type >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 101. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 0, index ID 0, page (1:32352). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192. Server: Msg 8998, Level 16, State 1, Line 1 Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 7 pages from (1:32352) to (1:40439). See other errors for cause. DBCC results for '8602955'. The system cannot self repair this error. The system cannot self repair this error. The system cannot self repair this error. The repair level on the DBCC statement caused this repair to be bypassed. The system cannot self repair this error. The system cannot self repair this error. The repair level on the DBCC statement caused this repair to be bypassed. The system cannot self repair this error. The system cannot self repair this error. The system cannot self repair this error. The repair level on the DBCC statement caused this repair to be bypassed. CHECKDB found 8 allocation errors and 3 consistency errors not associated with any single object. DBCC results for 'sysobjects'. There are 1395 rows in 27 pages for object 'sysobjects'. CHECKDB found 8 allocation errors and 3 consistency errors in database '8602955'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I had installed a third-party software on my server and that corrupted my Master DB. Over the weekend when we bought SQL server down, we could not bring it back up. I had to re-install SQL server and then restore all my databases from the backup. Is there a better way of doing this??? Curiously Someone who calls himself a DBA
I have a strange problem with one of the jobs created through a maintenace plan. The idea is that every 2 hours a transaction log backup is created and backups older than 2 days should be removed. This worked for several months without a problem, but suddenly today the job reports failed as status.
I looked into jobhistory but all I got is the following message:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Strange enough reports the sqllog that the t-log has been backed up succesfully. The files are on disk and I can restore them without a problem.
