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