I have a MS SQL Server 6.5, SP5A database running in a MS Cluster Server environment. The transaction log (500MG)is dumped every 30 minutes to control the size and ease the point in time recovery. I have noticed that many times throughout the day, when you do a sp_spaceused on the database, the transaction log space doesn't get reported as going back to zero or near zero. It more or less stays where it was at, maybe a few MG relief. When I look at the output from the dump transaction statement (Dump Transaction dbname to dbname_tlog_dump with NOINIT, STATS), it reports that many, many pages have been dumped. However, I have been watching the sp_spaceused and it doesn't really seemed to be reflected in the numbers. (Or even in the Enterprise Manager Tool/Manage Databases - which I know has been known to report incorrectly). I have had to run dbcc checktable(syslogs) to ensure that the transaction log doesn't fill up.
Question: Does this hurt anything on recoverability by running dbcc checktable(syslogs)? Does anyone know of a bug around space not being freed in SP5A or in a MS Cluster Server environment? The application is relatively new, so I don't discount the fact that something could be going on there as well. Ideas?
We are using SQL 6.5 and we have had problems before with our SEM reporting an incorrect value for the log size when we had just truncated the log. I would run DBCC CHECKTABLE (syslogs) just like MS Tech article Q183100 states and it would update sysindexes and correct the problem. That was on a database that is only 500mb and only has 21 rows in syslogs and it would take only a few second to run the checktable command. Now I have to run it on a database that is 5gigs and has 2,877,358 rows in syslogs. My concern is that this will take an extremely long time to run.
Does anyone have any ideas on how long this could run?
I ran a dbcc checktable command on my table and I got 21 consistency errors. What baffles me is that no specific error is reported. How do I rectify this situation?
when i run query like select * from item where itemid=1476941 it gives me this error ... do ne body know y this happening ??? other itemids works fine.. only this item id creating problem ...
Error: Database ID 7, page (1:99330), slot 27 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.
I have a SQL Database (150 gb), Some table are corrupted. I ran the command DBCC CHECKTABLE for 1 table the most important table in my system, but this command delays 50 hours and still running. Is there something I can do to do it faster? what else can I do?
I downloaded SharePoint Database Explorer on SBS from http://mindsharpblogs.com/james/archive/2005/01/20/190.aspx. I followed the instructions published on http://blogs.technet.com/sbs/archive/2007/01/05/using-sharepoint-database-explorer-on-sbs.aspx. After copying the STS_servername_1.mdf and STS_servername_1_log.ldf files to the folder, I experience problem executing the sp_attach_db statement.
My statement reads, EXEC sp_attach_db @dbname='STS_servername_1',@filename1='C:Program FilesMicrosoft SQL ServerMSSQL$SHAREPOINTDataSTS_SERVERNAME_1.mdf',@filename2='C:Program FilesMicrosoft SQL ServerMSSQL$SHAREPOINTDataSTS_SERVERNAME_1_log.LDF' GO
I received the following message after executing, Msg 1813, Level 16, State 2, Line 1 Could not open new database 'STS_servername_1'. CREATE DATABASE is aborted. Msg 602, Level 21, State 50, Line 1 Could not find row in sysindexes for database ID 5, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
What does "Could not find row in sysindexes for database ID 5, object ID 1, index ID 1." mean? What should I do? How do you run DBCC CHECKTABLE? From the error message, I don't know which table should I run the DBCC CHECKTABLE command with...
We are having quite a time since moving a large database to a newserver (actually built new server, renamed as old to make seamless forusers, etc.)Import 104 million row database (5 column) into table (CD_Assets_bad2)from existing (CD_Assets):Account(varchar(8))TransactionDate(datetime(8)Flow(varchar(1))Category(varchar(7))TotalValue(decimal(8))Run DBCC CheckTable - no issues.Create 4 non clustered indexes (3 single column, 1 two-column). Allindexes create fine.Run DBCC CheckTable again and receive the following:Server: Msg 8951, Level 16, State 1, Line 1Table error: Table 'CD_Assets_bad2' (ID 244195920). Missing or invalidkey in index 'idx_totalvalue' (ID 7) for the row:Server: Msg 8955, Level 16, State 1, Line 1Data row (1:11154499:98) identified by (RID = (1:11154499:98) ) hasindex values (TotalValue = -10).Server: Msg 8952, Level 16, State 1, Line 1Table error: Database 'CD', index 'CD_Assets_bad2.idx_totalvalue' (ID244195920) (index ID 7). Extra or invalid key for the keys:Server: Msg 8956, Level 16, State 1, Line 1Index row (1:20855652:338) with values (TotalValue = -0¤4?) points to the data row identified by (RID = (1:11154499:98)).DBCC results for 'CD_Assets_bad2'.There are 104397173 rows in 677904 pages for object 'CD_Assets_bad2'.CHECKTABLE found 0 allocation errors and 2 consistency errors in table'CD_Assets_bad2' (object ID 244195920).repair_fast is the minimum repair level for the errors found by DBCCCHECKTABLE (CD.dbo.CD_Assets_bad2 ).Any ideas? It seems like some sort of corruption, but the indexcreates fine. If anyone can help please let me know. If I can provideany addtional information that might help, please let me know.Thanks,DavidJoin Bytes!
I have a problems with syslogs being filled constantly and running out of space for my users to get anything done. Me and another consultant thought that it may have been due to the fact that the logins for this database were corrupt and a query was run to update certain table. Good thought but we changed it to another database and it occurred again. Question? What can cause this too occur and what can I do to prevent it from happening? I am going tonight to delete database on previous server and reinstall the sql script to create database and restore from a backup and hopefully correct this problem. What does anybody think about this situation?
I have a large database that I am trying to BCP into. The BCP is approximately 12 million rows. I have used the '-b' option for every 20000 records. Doesn't that clear the transaction log? I have multiple BCP jobs that need to be run of this size. I am having problems with syslogs filling up which I believe is just a reference to the transaction log? This has me confused because after the BCP I check the transaction log size in EM and it looks empty. Can anyone help?
hello everyone, how can i fix my corrupt syslogs? i've tried dbcc checktable , dbcc checkalloc, and dbcc fix_al and still reports the same message.we're using ms sql 6.5. PLEASE HELP!
We have run the following command... DBCC CHECKDB ('db_name') ... and 12'622 consistency errors were found (all on the same index on the same table). So next we ran... DBCC CHECKTABLE ('table_name', REPAIR_ALLOW_DATA_LOSS) ... and this fixed SOME of the errors, but not all... 12586 consistency errors in table
Does anyone have any ideas? Just to confirm this is SQL Server 2000. I do have both of the results logs available if that would help.
Hi, i am porting data using bcp. Everytime my syslogs table is running out of space. I am not able to get the data. Why my syslogs is filling everytime. I did trucate log also, still my syslogs table is full. This is 65 environment, it is very urgent pls any one respond to it.
Does anyone know a process for repairing a corrupt syslogs table (mssql 6.5). I have tried checkdb, newalloc and rebuilding the extents. All the latter did was screw up the device assignment. Any suggestions?
My tempdb keeps filling up. A look at sysindexes indicates that all of the space used is in syslogs. Running dump tran (no_log, truncate_only), dump database, and checkpoint give no relief. No transactions are open. Log and data are on shared devices (2MB on master device, 1024MB on a dedicated tempdb device). Why won't my tempdb log dump off? Thanks, Chris
Hi! I had following problem: dbcc checktable('syslogs') Checking syslogs Msg 2503, Level 16, State 1 Table Corrupt: Page linkage is not consistent; check the following pages: (current page#=438165; page# pointing to this page=438166; previous page# indicated in this page=0) DBCC execution completed. If DBCC printed error messages, see your System Administrator. dbcc newalloc(kmn,NOINDEX) ************************************************** ************* TABLE: syslogsOBJID = 8 INDID=0 FIRST=420675 ROOT=460295 DPAGES=57143SORT=0 Msg 2525, Level 16, State 1 Table Corrupt: Object id wrong; tables: alloc page 420608 extent id=420672 l page#=420675 objid in ext=0 (name = 0) objid in page=8 (name = syslogs)objid in sysindexes=8 (name = syslogs) Page linkage failed for INDID 0; Processed 1 pages; Data level: 0. 0 Data Pages in 1 extents. TOTAL # of extents = 0 ************************************************** ************* Restoring a database from a backup copy is impossible.
I inherited an application (or two) that run on SQL Server 6.5, which Ihaven't used in years, and am having a problem. I get the error:------------------------------------------------------------------------Can't allocate space for object 'Syslogs' in database 'master' becausethe 'logsegment' segment is full. If you ran out of space in Syslogs,dump the transaction log. Otherwise, use ALTER DATABASE orsp_extendsegment to increase the size of the segment.------------------------------------------------------------------------....when I... well... just about everything. If I try:DUMP TRAN master WITH NO_LOG....I get:------------------------------------------------------------------------Can't allocate space for object 'Syslogs' in database 'master' becausethe 'logsegment' segment is full. If you ran out of space in Syslogs,dump the transaction log. Otherwise, use ALTER DATABASE orsp_extendsegment to increase the size of the segment.Unable to write CHECKPOINT record in database 1 because the log is outof space.Unable to continue logged version of DUMP TRANsaction. Please free upsome space in database 1 or use the NO_LOG option.------------------------------------------------------------------------....If I try to expand the device or database, I get the "Can't allocatespace..." error. If I use "DBCC UPDATEUSAGE(0)" I get nothing, "DBCCCHECKTABLE(syslogs)" tells me it's using one page and has 29 rows(which is what I get if I "SELECT * FROM syslogs").How can I expand the space available to the master database orotherwise resolve this problem?Thanks in advance for any help anyone can offer. I know there probablyaren't many of you still using 6.5.Regards,Brian
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 followed the advice of Paul Randal, but Im stumped as I am not able to determin what the corruption issues are. This is SQL 2000 and the database is a Solomon database that was recently upgraded to 6.5. the error I get when running the DBCC checkdb is as follows:
Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:18645) with latch type SH. sysindexes failed. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now, the bad news. I am a bit of a novice and have picked this up from someone who left my company. It appears the latch error has been around for some time and only reared up when I instituted a new back up system that runs a dbcc check befor backing up. I don't think I have any clean backups.
Can I allow a non SA account to issue DBCC or TRUNCATE TABLE on a DBO table ?
I've tried setting this up in a SP where I've granted execute only to the user but the user receives "Only the DBO of the database may run the DBCC UPDATEUSAGE command".
Do I have to set the user up as an alias to DBO ? I'd rather not.
In past days we have a problem with a database. When we tried to fix the problem we found that SQL 6.5 has a wizard for maintain the DB, but these one doesnt have an option to fix an individual table. Seeing the way of work of this utility we look that it uses a bcp file.
What is the difference in : 1) apply a bcp(out) to an indivual table, drop the table, create table, bcp(in), create indexes. We believe this method fixes the fragmentation problem or 2) dbcc checktable(to the table).This method doesnt fix the fragmentation problem
We are new to SQL Server and we are currently setting up a job that does a dbreindex for tables that need it. I got the scripts for this from swynk. 50 of the tables that needed a dbreindex, (under 90% scan density) were not impacted by running the dbreindex. I.e., I ran the dbreindex and it left it at 50% scan density. The fill factor for these tables is set to 100%. I tried update stats, updateusage...nothing changes.... I am sure there must be a logical reason as to why the dbreindex had no impact on these tables. Does any one have any info on this? Thanks in advance!
