Removal of unused database to free up space.

I am trying to free up some space in the server by detaching the unused database and deleting the ldf & mdf files. Here are the steps I took
1. Performed a full back up of the database
2. Moved the BAK file to a different location in another server
3. Ran the follwing on the database since it was a publisher in the replication
sp_replicationdboption 'dbcentral,'publish','false'
4.Detach the database
5. Restore the db from the bakupfile somewhere to make sure that the back up is good.
6. Delete the files since I have a full back up.

tempdb modify size, move file

Modify the size
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, SIZE = 1024 ).
The database will be resized when the server restarts next.
Move the file
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'x:\full\path\xxxx\tempdb\file\tempdb.mdf' /*, SIZE = .... */ ) the file will be created in the new path when the server starts next

Recovery database from suspect mode

The database was in suspect mode, inaccessible Ran the following query Then restored from the latest full back up

EXEC sp_resetstatus client_367
ALTER DATABASE client_367 SET EMERGENCY
use client_367
DBCC checkdb
ALTER DATABASE client_367 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('client_367', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE client_367 SET MULTI_USER

Permission: To list the permissions on a schema

--the schema name is testschema
select pr.permission_name as permissionName,s.[name] as schemaname,s.[schema_id], p.[name] as principal_name, p.principal_id as pricipalID
from sys.schemas s
inner join sys.database_permissions pr
on s.schema_id = pr.major_Id
inner join sys.database_principals p
on pr.grantee_principal_id = p.principal_id
where s.[name] = 'testschema'

Trace status

Following displays the current status of the trace

DBCC TRACESTATUS (1222, -1);
GO


DBCC TRACEON(1222,-1)
GO

--Turns ON the trace 1222 globally (not just for that session)


DBCC TRACEON(1222,-1)
GO

--turns OFF the trace 1222 globally (not just for that session)

Schema: change schema of a user

The schema of a user was associated to a different schema instead of the default schema dbo. to check the user who do not have the default schema as dbo
select 'ALTER USER [' + name + '] with default_schema = [dbo]'
from sys.database_principals
where type in ('U')
and default_schema_name not in ('dbo')
--the above lists and generates a alter script
ALTER USER [Dom1\user1] with default_schema = [dbo]


--change the schema of the stored procs
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name
FROM sys.Procedures p
INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name = 'CHANGE_ME_Username'

Fix : truncation of dynamic query result

declare @script nvarchar(max) = ''
SELECT @script += 'grant execute on [' + r.ROUTINE_NAME + '] to [GRP_eApps_allUsers]' + char(13) + char(10)
from INFORMATION_SCHEMA.ROUTINES as r
where specific_name like 'proc_eapps%'
order by specific_name
--print @script
DECLARE @stmp varchar(max)
SET @stmp = ISNULL(@script,'')
WHILE @stmp <> ''
BEGIN
PRINT LEFT(@stmp, 8000)
SET @stmp = substring(@stmp, 8001, len(@stmp))
END