SQL Server troubleshooting

From Yggenyk
Jump to navigation Jump to search

Reasons why SQL server installation will fail

A reboot is pending

If something else like a Windows update, ore another program that requires a reboot was installed before SQL server, and scheduled the rest of .dll replacements to happen during next reboot.
Reboot PC before installing SQL server, if it fails, try to reboot again and try once more.

Regional settings not is same as Windows language type

If Regional settings isn't same as Windows language type SQL server will fail to install.
If Windows is (US) the users Regional settings should also be English (US).
The same goes for other regioanl versions of windows.
FIX: If not set regional settings to same as Windows version install SQL server again.
After installing regional settings can be set back.

Username is the same as Computername

Repairing SQL Server database

. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf).

  1. Open the database in SQL Management Server
  2. Find the database under Databases in the Object Explorer
  3. Rightclick the database and select: Tasks - Detatch...
  4. Delete the .ldf file so sequal server can't find it - sometimes it might be better not to delete it, give it a try!
  5. Select: File - New - Query with Current Connection
  6. Execute query: exec sp_attach_single_file_db '<database name>', 'C:\<path to database>.mdf'
  7. Rightclick the database and select: Tasks - Detatch...
  8. Rightclick the Databases and select: Attach... and attach the database again

How to resolve the issue of a database that was in Recovery Pending mode

Stop SQL Server and remove transaction log file of this DB then restart again where DB should go with suspect mode ….If so you can run the below query

  3. DBCC checkdb ([DB_Name], REPAIR_ALLOW_DATA_LOSS )
  4. ALTER DATABASE [DB_Name] SET online;

This solved the problem.

ROLLBACK IMMEDIATE Tells the SQL Server that if it can’t complete the command right away, then the other pending transactions should be rolled back.

NO_WAIT Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, then the request will fail.

REPAIR_ALLOW_DATA_LOSS Is the repair level that DBCC CHECKDB recommends when it finds corruptions. This is because fixing nearly anything that’s not a minor non-clustered index issue requires deleting something to repair it. So, REPAIR_ALLOW_DATA_LOSS will delete things. This means it will probably delete some of your data as well. If, for instance it finds a corrupt record on a data page, it may end up having to delete the entire data page, including all the other records on the page, to fix the corruption. That could be a lot of data. For this reason, the repair level name was carefully chosen. You can’t type in REPAIR_ALLOW_DATA_LOSS without realizing that you’re probably going to lose some data as part of the operation.