MSSQL

Fix User Server Principal authentication error after db restore

MSSQL

use database_name; — ALTER DATABASE database_name SET READ_WRITE ——– Reset SQL user account guids ——————— DECLARE @UserName nvarchar(255) DECLARE orphanuser_cur cursor for SELECT UserName = su.name FROM sysusers su JOIN sys.server_principals sp ON sp.name = su.name WHERE issqluser = 1 AND (su.sid IS NOT NULL AND su.sid <> 0x0) AND suser_sname(su.sid) is null ORDER BY …

Fix User Server Principal authentication error after db restore Read More »

TSQL Clear All Database objects

MSSQL

use dbname; DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR SET @Cursor = CURSOR FAST_FORWARD FOR SELECT DISTINCT sql = ‘ALTER TABLE [‘ + tc2.TABLE_NAME + ‘] DROP [‘ + rc1.CONSTRAINT_NAME + ‘]’ FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql WHILE (@@FETCH_STATUS = 0) BEGIN Exec …

TSQL Clear All Database objects Read More »

MSSQL Check Running Queries

Leave a Comment / MSSQL

SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_time FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Some useful Commands for Check and Maintain MSSQL Database

Leave a Comment / MSSQL

— Shrink Database DBCC SHRINKDATABASE (AdventureWorks2008R2, TRUNCATEONLY); — Rebuild All Index on a table ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); — Reorganize all indexes on the HumanResources.Employee table. ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ; — Check DB for database consistency DBCC CHECKDB — Reclaims …

Some useful Commands for Check and Maintain MSSQL Database Read More »