Corrupted Database After Partitioning And Check Disk
Apr 16, 2015
We have repartioned a server (create a larger C-drive/system partition). It's about Windows Server 2003. Before repartioning SP2 was not installed. After repartioning checkdisk started automatically (as normal after using a disk partitioning tool). Checkdisk did some interesting and bad things with my files: changing file permissions and make some files corrupt, how I don't know. Something to do with SP2 not installed and the NTFS filesystem. After this action I have a corrupted SQL database. Tried to repair with the DBCC CHECKDB repair commands, but fail. Error in event viewer:
Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x0000003bdc4000 in file 'D:program filesMicrosoft SQL ServerMSSQLData352.MDF'.
I also still have NTFS errors in my event viewer. What to do? How can I repair the database? Only running checkdisk again? (and will this work?) Than all file security will be reset to Administrators.
Other ways to rebuild my database? (tools???) Unfortunally there isn't a good backup available due to a lack of good configuration of the backup software.
Hello,I am trying to recover a SQL Server 7 database from another hard diskdrive that has a corrupted Windows 2000 Advanced Server installation. Iam not able to repair the corrupted Windows 2000 Advanced Serverinstallation but the file system is intact. I have installed a new copyof SQL Server 7 onto a new hard disk and have used the sp_attach_dbsystem stored procedure to attach the database from the old hard driveinto the new installation of SQL Server 7 on the new hard drive. Thedatabase shows up in the Enterprise Manager View and all the data andstructure are there, but when I try to run a Web site that uses ODBCconnections to the database I get the following error:ODBC Error Code = 08001 (Unable to connect to data source)[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL servernot found.I have double-checked that all the NTFS permissions, ODBC DSNs, and WebServer settings are correct and when I run the SQL Server ODBC drivertest at the completion of each ODBC DSN setup, it is successfull.1) Why am I getting this ODBC error?2) What steps have I missed in the recovery process?3) If I have done something wrong recovering my database, can I stillrecover it since I still have: a) The old master database file and logfile; b) The old database (non-master) file and log file; c)A backed-up(Enterprise Manager -> Right Click A Database -> All Tasks -> BackupDatabase) copy of the old database (non-master) file; ?My System Specifications:-Windows 2000 Advanced Server-SQL Server 7-IIS 5.0-Cold Fusion Server 4.5 Professional Full EditionThanks For Your Time,Nathan
yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
hello,all I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) . is it possible to do it ? if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ? thanks in advanced!
Here is one of the MCDBA exam question. Does anyone explain how to do the following? I click new alert, and selected 'Sql server performance condition alert' Type But I don't know which object and counter I should choose to get disk space left. Thank you..
Q: You are the administrator of Microsoft Windows 2000 Advanced Server computer. The server is also running SQL Server 2000 and contains a database named Sales. The server is configured as shown in the exhibit. The sales database is configured by using the default options and increases by 50MB per day. You want an operator to be notified automatically before the database runs out of disk space. You also want to minimize the amount of administrative overhead necessary for this database. What should you do?
A: Configure systems monitor to log an alert if the hard disk database contains less than 600MB of disk space. Configure SQL server agent to send an e-mail message notifying the operator of the system monitor alert.
Explanation: The solution with least administrative effort is to set up a system monitor alert to log when disk space is below a certain threshold, here 600MB. Then configure the SQL Server agent to trap this event and send an e-mail notifying the operator when this occurs. Note: Using SQL Server Agent, you can define alerts and configure responses to alerts. SQL Server Agent monitors the Windows application log and compares each SQL Server event logged with the alerts that have been defined. If a match is found, an event alert fires. SQL Server Agent can also monitor specific SQL Server performance object counters and fire a performance condition alert when the value for the counter is less than, equal to, or greater than a defined threshold. The Create Alert Wizard can be used to configure the SQL Server agent for alerts.
Is there anyway to check if server is having disk latency or IO issues?Found below in SQL error log
Date10/1/2014 8:28:58 AM LogSQL Server (Current - 10/1/2014 12:00:00 AM)
Sourcespid10s
Message SQL Server has encountered 8500 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:Fin.mdf] in database [Fin] (5). The OS file handle is 0x0000000000001368. The offset of the latest long I/O is: 0x0001104a7da000
I have a SQL 2000 database whose MDF is approx. 105 GB. Everything has been working fine for months until this past weekend when the database server dropped offline. I restarted the services and SQL server looks to have started up okay. However, trying to access it via Enterprise Manager causes EM to hang. Checking the Windows event log, there are a whole series of messages stating ...
3455 : Analysis of database 'ptportal' (7) is XX% complete (approximately 61 more seconds)
This 'analysis' ran for over 12 hours ... I have never seen this run before, much less this long. Now, there are a new series of messages stating ...
3450 : Recovery of database 'ptportal' (7) is XX% complete (approximately 56526 more seconds) (Phase 2 of 3).
This 'recovery' has been running for 9 hours now. Does anybody have any idea what has happened here or what is going on? My guess is that this database has been corrupted and SQL Server is trying to do a repair? Anybody know how long this usually takes and what the success liklihood would be?
I have a database corrupted showing on the Object Explorer the name of the database and showing near (6.5). Deleted not accepted any other function not allow. Is there any way to take off from the Object Explorer?
I am backing up SQL 6.5 DB using Backup Exec 7.2 on NT 4.0 server SP4 with SQL server agent. The backup job ends in Failed status and a warning that DBname is corrupt. I then try to run DBCC Checkdb, newalloc, and checkcatalog commands to repair the DB. No further ahead yet. Are there any other commands that I can perform on the DB so that the corruption can be repaired, or verified there indeed is corruption of DB?
i developed an application which running on PDAs.i am using sqlserver ce database.my database file sometimes corrupted,why sqlce databases are corrupted and what can i do not corrupted.i can repair my database file when it corrupted but my customers not understand when database file was corrupted,so they think that program doesnot work.
Hi, my 'model' database corrupted and my SQL Server 2005 Express can't be started.
I believe I have the SAME problem as his: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=455524&SiteID=1
but when I try the command start sqlservr.exe -c -T3608, an error message appeared:
"Your SQL Server installation is either corrupt of has been tampered with (Error getting instance ID from name). Please uninstal then re-run setup to correct this problem"
Well, the problem is that I have an ActiveBPEL database inside, and I really need a way without reinstalling it.
hi all, before i had a big database more than 90GB and it's growing very sharp so what i did is to create a summary database that gonna hold some aggregation and i create also three tables that gonna hold the data, on will hold the data for the last three months (very detailed information) one between 3 months and a year(less detailed) and the last one older than a year(less less detailed) these are the company requirement i'm working with. then i transfer the data to the new tables with stored procedures. now my question is: do i need to create a view for these three tables? and if yes how should i do it?!! cus i do not have the same columns in the three tables. thanks experts.
I am a newbie to SQL Server Admin. Today when I ran Dbcc newalloc on Model it is showing corruption on syobjects with a 2525 error. I am not sure how to fix this problem? Since this is a critical payroll application server, I need to solve it at the earliest.
If anybody has faced this problem earlier or knows how to solve, please respond to the following address: vsrikanth@ameren.com
The following list of actions leads to a corrupt database on SQL Server 2005.
Create a database snapshot Drop a table in the database Backup the database Restore from backup Revert to the snapshot
I'm not entirely surprised that it results in a corruption, what is surprising is that I can revert from a snapshot after restoring. There needs to be some kind of check to prevent reverting to a snapshot in a case like this. Until SQL Server prevents you from doing it, I'd recommend a best practice is to delete all snapshots before you restore a database so that you cannot do this by accident.
I run MSDE on a Windows XP Server and my database was marked as "suspect" by Enterprise Manager and its icon become gray. I detach that database and try to re-attach it but there is an error as the following:
After that, I uninstall the MSDE and re-install new MSDE SP4 and try to re-attach the database file again and there is still an error. Is there anyway i can recover the data from that database?
Let me start off by saying that under normal circumstance I would just restore from the last good backup. However in this case it appears as though the last good backup was sometime last August ... arg! After much yelling at the person responsible I've been attempting to get my blood pressure below 200 and see what data is recoverable.
First off, this was a RAID5 system that failed 1 drive. Secondly, before we got someone in there to replace said drive it failed a second drive and the system went down. We managed to massage the system back online but it appears that there is some corruption as a result which is no surprise.
I've done DB repairs in the past and it hasn't been too bad, but this time it is looking a little gnarly.
I've kicked everyone off the server and tried starting SQLServer several different ways.
I tried starting the service normally and then flagging the bad DB into single user mode with "ALTER DATABASE foo SET SINGLE_USER". I then did a select * from sysdatabases to make sure it took, which it did. I also tried starting the whole SQLServer in single user mode from the command line, "SQLServr -m".
I can run "DBCC CHECKDB('foo')" and I get a long ugly list of allocation errors. I posted it to a link as the 1349 lines returned is a little long: http://chrisnet.net/sqlbad/dbcc_checkdb.txt
But when I attempt to bite the bullet and destroy data in an attempt to put things back together with: "DBCC CHECKDB('foo', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS" I get: Server: Msg 7919, Level 16, State 2, Line 1 Repair statement not processed. Database needs to be in single user mode. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
But yet the database is in single user mode, according to everything I check check on. Is this just SQL's way of telling me the corruption is too severe to be repaired? No output is displayed in the shell cmd window like it is for a successful DBCC either (when running sqlservr -m).
I was trying to update an SSAS database on a test server and got the following error when trying to deploy from Visual Stuido. I get a similar error in SMS. I tried to delete the database before deploying but I get an error when trying to delete. I rebooted the server.
(SQL Server 2005 R2, Windows Server 2008R2.)
Error 1 File system error: The following file is corrupted: Physical file:
?D:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataHeidtman Sales Cube.0.dbDim Age.0.dim5.Dim Age.Dim Age.dstore. Logical file . Errors in the metadata manager. An error occurred when loading the Age dimension, from the file, '?D:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataHeidtman Sales Cube.0.dbDim Age.5.dim.xml'. Errors in the metadata manager. An error occurred when loading the Heidtman DW cube, from the file, '?D:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataHeidtman Sales Cube.0.dbHeidtman DW.7.cub.xml'. 0 0
A friend of mine called and said that his database was suspect and he detached it and was unable to attach it. The SQL Server Version is 2000 with the latest service packs installed. When I checked it the database (MDF) size was 1MB and the LDF size was 3.8 GB. I was not able to attach the two together and I was not able to attach the database using sp_attach_single_file_db. I did find an old backup of his database and attached that so he can work off of it but it is 1 yr old and he needs recent data. Since the MDF seemed to be blank we cant do much with that, but there seems to be data in the LDF. Is it possible to extract any data from the LDF file?
Conrad writes "I'm currently working on Table Partitioning. I have done everything succesfull for partitioning, what I'm struggling with is to use " SELECT MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY' " to get the last used FileGroup. Now this works just fine, but when I run the following script: --Decalre variables DECLARE @LastFilegroupName VARCHAR(50) DECLARE @FilegroupName VARCHAR(50)
--Retuns the next FileGroup to be used SET @LastFilegroupName = (select MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY') SET @LastFilegroupName = Replace(@LastFilegroupName,'FileGrp','') SET @FilegroupName = 'FileGrp' + CAST((@LastFilegroupName + 1) as varchar(10))
--Alter database statement ALTER DATABASE VadivelTesting ADD FILEGROUP @NewFG_Name
This script gives the following error "Incorrect syntax near '@NewFG_Name'."
When I give it a static name it works fine, but not with the variable.
Please can someone help me, I'm in struggeling with this one."
We have database when trying to make read only throwing below error: with stack dump
Location: recovery.cpp:4517 Expression: m_recoveryUnit->IsIntendedUpdateable () SPID: 51 Process ID: 6448 Msg 926, Level 14, State 1, Line 1 Database 'XXXX' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server errorlog for more information. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database 'XXXX', an error occurred at log record ID (0:0:0). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'XXXX' (database ID 7) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Investigation DONE:
- DBcc checkdb shown Clean - database is online and able to access -Detached database and attached with rebuild log, still could not bring database read_only
Hi, My server went dead(problems with the hard disk), but I have a copy of the whole sql server directory including the database in a external hard disk. I have a new server now and I would like to copy this database (with the reports from reporting services too) from the external hard disk to my new server. can anybody help me please? Thanks.
Note : I have a plain copy of all the sql server directory with all the files including the database not a SQLServer backup done with the wizards.
If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.
-- Initialize Control Mechanism DECLARE@Drive TINYINT, @SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area DECLARE@Drives TABLE ( Drive CHAR(1), Info VARCHAR(80) )
WHILE @Drive <= 122 BEGIN SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives ( Info ) EXEC(@SQL)
UPDATE@Drives SETDrive = CHAR(@Drive) WHEREDrive IS NULL
SET@Drive = @Drive + 1 END
-- Show the expected output SELECTDrive, SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes, SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes, SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes FROM( SELECTDrive, Info FROM@Drives WHEREInfo LIKE 'Total # of %' ) AS d GROUP BYDrive ORDER BYDrive
I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.
Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
I have my little MSDE on my computer. I can add and delete stuff from it through Web Matrix. My question is, where exactly is the database stored in Windows?? Also, say I wanted to copy it to a disk so I could show it to someone else, what file(s) would I need to copy?
The reason why I ask is because I have been doing a website as a project and we have to hand in all files on a disk. Getting all the .aspx & .ascx and the .config file is easy, but the database & stored procedures is proving tricky to find.
One of the drives that stores the database file is close to running out of space. The chance of me getting more space added to this drive any time soon are really low. What are other options I have?
I want to move 2 databases to another physical disk. They are both single file databases. It looks like a fairly straigtforward attach and detach procedure, but I have a couple of questions:
The log files (.ldf) currently reside on a separate physical disk from the data files, if I attach and reattach the data files will the logs remain where they are by default or do I have to re specify there location?
Is there any advantage to running the update statistics portion of the sp_detach_db?
One of the drives that stores the database file is close to running out of space. The chance of me getting more space added to this drive any time soon are really low. What are other options I have?
We are going to implement either a replicated database or use log shipping to another database to support our reporting needs. Basically we are moving reporting over data off our transactional database.
With this in mind, I have several questions: Are there any special configurations in terms of disks for a database in this situation?What are the recommended raid types? Also, we currently store our data in one filegroup and indexes in another so obviously I'll want those on 2 separate physical disks other than the C:. What about the log files? Do I care where they go (on C: or another physical drive separate from C:, data filegroup drive and index filegroup drive)?
