I've been asked to look at a SQL 2005 database that is reporting 'torn page' errors. I can look up the MsgIDs and so on, but I just need a few pointers interpreting the DBCC CHECKDB output in a more general sense. In this sample: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 8...
This wiki post outlines both a problem and a solution. I wanted to post this for others that may be having similar problems, as I couldn't find anything specifically to solve this problem elsewhere. We recently upgraded our SQL Server 2000 database to SQL Server 2005. One of the databases on the server is a back-end to a MS Access datab...
Help getting crushed by this i did DBCC CHECKDB and one of the DB gave Msg 823, Level 24, State 2, Line 2 I/O error 21(The device is not ready.) detected during read at offset 0x0000000001c000 in file 'E:\MSSQL2000\Data\FTSWE_Data.MDF'. I was working on a remote server, from my, local host accessing a remote DB. Also i was on VPN. ...
First, I'm aware of this question which didn't get answered because what the OP was really trying to do was'nt incrementing an identity column I've got an identity column with a current seed value of x, and I would like to reseed it to x+1 (ie I want my identity column to jump directly from x to x+2. I know I can do that using the fo...
I am trying to execute DBCC CHECK DB('MyDB) using ADO.Net, but how can I get the text returned by the command? I have tried the following: SqlCommand sqlCom = new SqlCommand("DBCC CHECKDB ('MyDB')", sqlCon); SqlParameter output = new SqlParameter(); output.Direction = System.Data.ParameterDirection.ReturnValue; sqlCom.Parameters.Add(ou...
In SQL Server 2008, I am using triggers to capture all changes made to a specific table in my database. My goal is to capture the entire change. That is, to capture what data is being inserted, not just that data is being inserted. In the trigger I am using the EventInfo column of the result set returned by DBCC INPUTBUFFER to get the...
I have a SQL Server 2000 database. When I run the following command select * from sysindexkeys This display the appropriate records. I then do a DBCC command for the sysindexkeys. It doesn't display anything. Strange there is no page having the sysindexkeys records. Then how the query display the list of records. ...
DBCC (DB Consistency Check) commands are powerful tools but they don,t have a graphical user interface. now Is there a application that works like DBCC wizard? ...
I am trying to shrink my log file using DBCC SHRINKFILE(db_2.ldf), which is the name for log file It gives me error every time: 8985, Level 16, State 1, Line 1 Could not locate file 'FIelD' for database db in sys.database_files. The file either does not exist, or was dropped. Can you please suggest what can I do to fix it. ...
I want to do some SQL Server performance testing and I want to establish a good baseline. I want to flush all the caches each time so that I know between each test it hasn't cached something so it performs better in between runs. Which DBCC commands should I use to make my SQL server as clean as possible? ...
Our team needs to insert a cruel amount of data into our SQL Server 2008 database. We're looking for a good solution. Now we came up with one, but I have doubts with it, simply because it doesn't feel right. So I'm asking here if this seems like a good solution. Extra challange is that it's a peer-to-peer replicated database over 4 serve...
My question is, should I be running one or both of the shrink command regularly, DBCC SHRINKDATABASE OR DBCC SHRINKFILE ============================= background Sql Server: Database is 200 gigs, logs are 150 gigs. running this command SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128.0 AS Availabl...
Hi, Using Tsql, how can i know when DBCC checkdb was last run on SQL server (2000, 2005 or 2008)? Regards ...
Hi there, I have restore a SQL Server 2000 database onto SQL Server 2005 and then run DBCC CHECKDB WITH DATA_PURITY and I get this error: Msg 2570, Level 16, State 3, Line 2 Page (1:19558), slot 13 in object ID 181575685, index ID 1, partition ID 293374720802816, alloc unit ID 11899744092160 (type "In-row data"). Column "NumberOfShares...
First of all, I know it's better not shrink database. But in our situation we had to shrink the data file to claim more space back. Environment: SQL Server 2005 x64 SP3 Ent running on Windows Server 2003 Enterprise x64. The database has one single data file and one log file. Before we run DBCC SHRINKFILE, the data file has 640GB, in ...
I have a large database (90GB data, 70GB indexes) that's been slowly growing for the past year, and the growth/changes has caused a large amount of internal fragmentation not only of the indexes, but of the tables themselves. It's easy to resolve the (large number of) very fragmented indexes - a REORGANIZE or REBUILD will take care of t...
We're getting a huge LDF file by using a full recovery mode in a SQL Server DB. so we're planning to shrink the log file. Is there any performance penalty for using DBCC SHRINKFILE to reduce the database log file size (LDF)? and what about applying it to the data file (MDF)? ...
We have a database Ms DBCC CHECKDB (MS) Or ALTER DATABASE MS SET SINGLE_USER DBCC CHECKDB(MS,REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE MS SET MULTI_USER Error message Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Msg 0, Level 20, State 0, Line 0 A severe er...
Hi all, I've been struggling with the DBCC INPUTBUFFER in SQL 2005/2008 attempting to get some information on each run of my queries. I know there are a number of other methods available that can do similar things to DBCC INPUTBUFFER, including sp_who/sp_who2, fn_get_sql(), dm_exec_sql_text, and so forth, but none of these others retur...