A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


SQL 2K5 Suspect Mode Database (EMERGENCY MODE)

 
SQL server can become marked suspect for many different reasons. 
File corruptions, hardware issues, OS issues, translog errors and on and on ... 
 
Find the cause and then get your database back..... 
 
Find the suspect database:
SELECT * FROM master.sys.databases WHERE state_desc='SUSPECT'
 

Now Fix the database:

USE master;
GO


---configure database set option: ONLINE:OFFLINE:EMERGENCY.
---Emergency: allows access to the database for data moving.(Read Only Mode)

ALTER
DATABASE sandbox SET EMERGENCY
GO

--- EMERGENCY MODE ALLOWS FOR DBCC CHECKDB, Allow data loss option requires single user mode.
ALTER
DATABASE sandbox SET SINGLE_USER
GO

/*
if this statement succeeds the database is brought back online.
you'll need to set it to multi-user mode.
if it fails, there's no way to recover. You'll need to restore from backup.
NO_INFOMSGS : Suppresses all informational messages (Severity 10) and the report of space used.*/

DBCC
CHECKDB (sandbox, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

--- use my db.
USE
sandbox
GO

--- Checks the allocation and structural integrity of all the objects in the specified database.
DBCC
CHECKDB WITH NO_INFOMSGS;

 

 
 
 

Toys


Random link(s) to tools I like.....
 
 In no particular order.
 
1. www.websequencediagrams.com
2. www.jingproject.com
 
https://www.thinkwithgoogle.com/
 
 
IBM Watson for quick visuals
 
Gantt Chart Javascript Library
 
Ecommerce Conversion Rate Optimization Ideas
 
 SEO Tool :  https://varvy.com/
 Site Optimizer: https://www.seoptimer.com
 
 
 Shared Whiteboard : 
 
Best Project Management and Roadmap tool
 
Best Tech Edu Sites
 
 UI and Design Tools
 
 
 
 API toys : 
 
 
 
 

Fixing Orphaned SQL Server Users. SQL 2k, 2k5

After you transfer logins and passwords to the destination server, users may be unable to access the database. Logins are associated to users by the security identifier (SID), and if the SID is inconsistent after you move a database, SQL Server may deny the user access to the database. the logn names can be the same but the underlying guid/sid is different.
 
Report the orphans for your knowledge:

    EXEC sp_change_users_login 'Report'

If you already have a user with the same name and you know what the issue is....

    EXEC sp_change_users_login 'Auto_Fix', 'db_user'