suspect mode: database

---once the database goes to suspect mode
--follow the steps to recover the database

--1. reset the database from suspect mode
--sp_resetstatus turns off the suspect flag on a database.
--This procedure updates the mode and status columns of the named database in sys.databases.
--Also note that only logins having sysadmin priveleges can perform this :


use master
GO
exec sp_resetstatus 'db1';
--2.set the database to emergency mode
--Once the database is set to EMERGENCY mode it becomes a READ_ONLY copy
--only members of sysadmin fixed server roles have privileges to access it.
--The basic purpose for this is to facilitate troubleshooting

alter database db1 set emergency;

--3.run the consistency test
dbcc checkdb(db1)

--4. Roll the databse to single user mode
use master
GO
ALTER DATABASE db1 SET SINGLE_USER
WITH ROLLBACK IMMEDIATE


--5. this query will attempt to repir all the reported loss. This query can cause some data loss
dbcc checkdb('db1',REPAIR_ALLOW_DATA_LOSS)

--6.finally bring the database ONLINE and set to multiuser mode
ALTER DATABASE db1 SET MULTI_USER
WITH ROLLBACK IMMEDIATE

Stored procedure with an OUTPUT parameter

CREATE procName1 (@intClientId int, @account varchar(50), @intCount int OUTPUT)
AS

SELECT @intCount = COUNT (*) FROM Clients where clientId = @intClientId

SELECT @intCount

GO


--procedure call
Declare @out int
EXECUTE procName1 999, 'ABCSDRF',@out OUTPUT
SELECT @out

Dynamic sql: executing stored procedure using a sp_executesql

I had a situation where the logic on finding the duplicate records( based on the account number match) was different on each client. I built a logic where each client had it's own stored procedure called proc_check_dup_logic and the stored procedure name used by each client was stored in a table in a central admin database. I had a wrapper stored procedure in the admin database that would find the duplicate logic stored procedure name from the table and execute that using sp_executesql.

I try to stay away from dynamic sql but this was pretty fun writing and I unerstood the parameters scope as well.



@procname is a storedrocedure variable where the name is assigned and it's a storeprocedure with an OUTPUT value.

SELECT @procname = procedurename from table1 WHERE clientId = 10

BEGIN
--call special dup check logic

DECLARE @sqlStr NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

--the follwing are the parameters in this scope

DECLARE @parmINAccount VARCHAR(30),@parmINHospId int , @ParmINpatType varchar(1),@paramINPtno int
DECLARE @parmRET1 int
--assigning the values to be passed to the storeprocedure on sp_executesql call
SET @parmINAccount = @strAccount
SET @parmINHospId = @intHospital
SET @ParmINpatType = @patType
SET @paramINPtno = @intPtno

SET @sqlStr = N'Exec ' + @procName + ' @intPtno,@strAccount,@intHospital,@patType, @outDupCheck OUTPUT'

--the following parameters name and datatype should be exactly the same as the parameters in the called storedprocedure (e.g. @procname in this case)

SET @ParmDefinition=N'@IntPtno int, @strAccount varchar(50),@intHospital int,@patType varchar(1), @outDupCheck int OUTPUT';

EXECUTE sp_executesql @sqlStr, @ParmDefinition, @IntPtno = @paramINPtno, @strAccount = @parmINAccount, @intHospital=@parmINHospId,@patType=@ParmINpatType,@outDupCheck = @parmRET1 OUTPUT
Select @outDupCheck = @parmRET1

END

Error handler: TRY CATCH

BEGIN TRANSACTION TRAN1

BEGIN TRY

INSERT INTO.....

UPDATE.....

END TRY

BEGIN CATCH

If @@TRANCOUNT > 0

--error happened

ROLLBACK TRANSACTION TRAN1

END CATCH

If @@TRANCOUNT > 0

COMMIT TRANSACTION TRAN1

GO

Script: Log file size to a user table

DECLARE @rowcnt INT
DECLARE @iterator INT
DECLARE @dbname VARCHAR(200)
DECLARE @exec_sql VARCHAR(500)
SET @rowcnt = 0
SET @iterator = 1
CREATE TABLE #db_file_info ( [Database_Name] SYSNAME NOT NULL, [File_ID] SMALLINT NOT NULL, [File_Type] VARCHAR(10) NOT NULL, [File_Name] SYSNAME NOT NULL, [File_Path] VARCHAR(500) NOT NULL, [File_Size_in_MB] INT NOT NULL, [Space_Used_in_MB] INT NOT NULL, [Space_Left_in_MB] INT NOT NULL, [Time_collected] smalldatetime )
CREATE TABLE #db ( dbid INT, name VARCHAR(200))
INSERT INTO #db
SELECT database_id, name FROM sys.databases
WHERE --database_id <> 11 AND [state] <> 6
SET @rowcnt = @@ROWCOUNT
WHILE @iterator <= @rowcnt
BEGIN
SELECT @dbname = '['+ name + ']' FROM #db
SET @exec_sql = ' USE ' + @dbname + ';
Insert into #DB_FILE_INFO Select db_name(),fileid,case when groupid = 0 then ''log file'' else ''data file'' end, name,filename, [file_size] = convert(int,round ((sysfiles.size*1.000)/128.000,0)), [space_used] = convert(int,round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,0)), [space_left] = convert(int,round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,0)), getdate() from dbo.sysfiles; '
EXEC( @exec_sql)
SET @iterator = @iterator + 1
END
INSERT INTO SQLAdmin_Monitor_db_file_info
SELECT * FROM #db_file_info
DROP TABLE #db
DROP TABLE #db_file_info

Modifying database file

All the database files are set to autogrowth to 10% from the initial sizes. But,the auto growth can happen any time during business hours causing the database slow down. Therefore I am taking the following approach -

1 I have set up a sql job ( calls a script listed here) that logs the current datafile and the log file size in a table in interval of 4 hours daily. I use that historical data to research when the data file needs to grow.
2. Then I run this script from the table to find which database file has more than 95% filled up and those are the candidate for me to manually resize ( expand) the data file.
3. I use the follwing script to resize the datafile
use db1
Go

---select * from sys.sysfiles

alter database client_343
modify file
(
name = client_343,
size = 1024MB
);

Script: To find the database file where the file space is used more than 94 % of the current file size

--The historic data is logged in a table in every 4 hours and the following query in run in the table to find the current grown rate of the databse file

SELECT Database_Name,Space_Used_in_MB,File_Size_in_MB,(cast(Space_Used_in_MB as float)/cast(File_Size_in_MB as float)) * 100 as newval
FROM Monitor_db..SQLAdmin_Monitor_db_file_info
WHERE FILE_ID = 1 AND (cast(Space_Used_in_MB as float)/cast(File_Size_in_MB as float)) * 100 >= 95 AND Time_collected IN (SELECT MAX(time_collected) FROM hCFS_SQLMonitor..SQLAdmin_Monitor_db_file_info )
ORDER BY File_Size_in_MB

restore msdb database

USE master
GO
--just to verify if the below command succeeds the restore should succeed too
RESTORE VERIFYONLY
FROM DISK = N'V:\SANSQL01Backups\Daily\msdb\msdb_backup_2014_10_05_194001_4668459.bak'
--restore the database
RESTORE DATABASE [msdb]
FROM DISK = N'V:\SANSQL01Backups\Daily\msdb\msdb_backup_2014_10_05_194001_4668459.bak'
WITH REPLACE
GO
--msdb database was curripted and when to suspect mode. So fixed it by restoring a copy of a backup

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

Drop the user defined objects

--remove all user defined tables exec sp_MSforeachtable @command1 = 'Drop table ?';
--remove all the user defined stored procedures from the database
declare @procname nvarchar(max)
declare curProc Cursor for SELECT [name] from sys.objects where type = 'P'
Open curProc Fetch next from curProc into @procName
While @@fetch_Status = 0
Begin
Exec('drop procedure ' + @procName)
Fetch Next From curProc Into @procName
End
Close curProc
Deallocate curProc --drop all user defined views

Declare @viewName varchar(500)
Declare curView Cursor For Select [name] From sys.objects where type = 'v'
Open curView Fetch Next From curView Into @viewName
While @@fetch_status = 0
Begin
Exec('drop view [' + @viewName+']')
Fetch Next From curView Into @viewName
End
Close curView
Deallocate curView

--Remove all Triggers
-- drop all user defined triggers
Declare @trgName varchar(500)
Declare curTrig Cursor For Select [name] From sys.objects where type = 'tr'
Open curTrig Fetch Next From curTrig Into @trgName
While @@fetch_status = 0 Begin Exec('drop trigger ' + @trgName)
Fetch Next From curTrig Into @trgName
End
Close curTrig
Deallocate curTrig --Remove all functions -- drop all user defined scalar-valued functions
Declare @FncName varchar(500)
Declare curFunc Cursor For Select [name] From sys.objects where type = 'FN'
Open curFunc
Fetch Next From curFunc Into @FncName
While @@fetch_status = 0
Begin
Exec('drop function ' + @FncName)
Fetch Next From curFunc Into @FncName
End Close curFunc
Deallocate curFunc

-- drop all user defined table-valued functions
Declare @FncName1 varchar(500)
Declare curFunc Cursor For Select [name] From sys.objects where type = 'TF'

Open curFunc
Fetch Next From curFunc Into @FncName1
While @@fetch_status = 0
Begin
Exec('drop function ' + @FncName1)
Fetch Next From curFunc Into @FncName1
End
Close curFunc
Deallocate curFunc

Fix : Could not obtain exclusive lock on database 'model'

SELECT spid, loginame, DB_NAME(dbid) FROM master..sysprocesses WHERE DB_NAME(dbid)='Model' If you get the following errors - MS-SQL Database Error --------------------------- Create failed for Database 'DbName1'. An exception occurred while executing a Transact-SQL statement or batch.Could not obtain exclusive lock on database 'model'. Retry the operation later.CREATE DATABASE failed. Some file names listed could not be created. Check related errors. kill the connections

Check a list of objects and permissions

--list views, procs, table

select o.* from sys.database_principals p inner join sys.objects o on p.principal_id = p.principal_id where p.name = 'GRP_App_Users' and o.type in ('U','V','P') and o.is_ms_shipped = 0


--select permissions of a group or user on a database

select p1.*,o.* from sys.database_permissions p1 inner join sys.database_principals p2 on p1.grantee_principal_id = p2.principal_id inner join sys.objects o on o.object_id = p1.major_id where p2.name = 'GRP_SQLReportingUsers'


--FIND SPS WITH EXECUTE PERMISSIONS ON A USER

SELECT [name] FROM sys.objects obj INNER JOIN sys.database_permissions dp ON dp.major_id = obj.object_id WHERE obj.[type] = 'P' -- stored procedure AND dp.permission_name = 'EXECUTE' AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT AND dp.grantee_principal_id = (SELECT principal_id FROM sys.database_principals WHERE [name] = 'GRP_USERS_ABC') ORDER BY NAME

Tempdb space lookup queries

Troubleshooting Insufficient Disk Space in tempdbhttp://msdn.microsoft.com/en-us/library/ms176029.aspx

Capacity Planning for tempdb
http://msdn.microsoft.com/en-us/library/ms345368.aspx

Optimizing tempdb Performance
http://msdn.microsoft.com/en-us/library/ms175527.aspx


SELECT
SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]FROM sys.dm_db_file_space_usage;
--current free and used spaceSELECT SUM(size)*1.0/128 AS [size in MB]FROM tempdb.sys.database_files
--Determining the Amount of Space Used by User Objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],(
SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]FROM sys.dm_db_file_space_usage;
--Determining the Amount of Space Used by Internal ObjectsSELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],(
SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]FROM sys.dm_db_file_space_usage;
--Determining the Longest Running TransactionSELECT transaction_idFROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;--Determining the Amount Space Used by the Version StoreSELECT SUM(version_store_reserved_page_count) AS [version store pages used],(
SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]FROM sys.dm_db_file_space_usage;