Recover sa password

If you have forgot your ‘sa’ account password and do not have any other account with admin privileges available. Also as a best practice you ave removed the access from builtin\Admin account also.  You can follow below steps to recover/reset ‘sa’ account password:-

1) Go to SQL Server configuration manager and stop the SQL Server service for which you want to reset ‘sa’ password.

2) Right click on SQL Server service –> Properties –> go to advanced tab.

3) Under the advanced panel go to Startup Parameters option and add ;-m at the end of the current parameter values, without any spaces. -m option is used to start SQL Server in single user mode.

4) Restart SQL Server. This will start SQL Server in single user mode.

5) Open SQL Server management studio and open new query window. Here connect to the instance with windows authentication. SQL Server will allow you to connect as admin.

Note:- When you start SQL Server in single user mode and are connected to box with admin rights on physical server, SQL Server allows your windows account to act as a admin to SQL Server instance also.

6) Now create a new temporary account with sysadmin privilige.

create user temp with password = 'Temp123';

go

exec sp_addsrvrolemember 'temp,'sysadmin';

go

or, if you already have a account to which you want to grant sysadmin access, you can do that also.

8) Stop SQL Server service and remove ;-m from startup parameters.

9) Connect to SQL Server with the account we have created at step 7.

10) Now change your ‘sa’ account password and then connect using your new ‘sa’ account password.

The operating system returned an Error 1117

Today I ran consistency check on a database and it throw below error:

DBCC checkdb on msdb database failed with errors:- Msg 1823, Level 16, State 2, Line 1 A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 823, Level 24, State 2, Line 1
The operating system returned error 1117(failed to retrieve text for this error. Reason: 1815) to SQL Server during a read at offset 0000000000000000 in file 'c:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf:MSSQL_DBCC7'.
Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

In most cases this error be caused by a permissions issue on the root of the drive where the database belongs. I tried running CheckDB query on some other databases located at the same location. and I had not got any error on other databases. So it is confirmed that the error is not related to permissions.

The corruption is so bad that I could not run CheckDB on that database. Than I restored the database from last good backup.

Transaction Log Backup job failed

Our backup log job failed with following error: 

Executed the below query: 

"BACKUP LOG [DemoData] TO  DISK = N'C:\Backup\xxx\xxx_backup_a.trn' WITH NOFORMAT, NOINIT,  NAME = N'xxx_backup_a', SKIP, REWIND, NOUNLOAD,  STATS = 10 "

and it failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. 

Here as error stated, the log backup job is failed as there is no FULL backup exists, in this 
situation, we might be having FULL backup for ALL DATABASES schedule to run weekly, and Transaction log backup for ALL DATABASES is scheduled to run daily. Here user creates the database dynamically or randomly in the middle of the week so FULL backup does not exists which cause give error.

There is no easy way to handle such situation but for safer side we can follow standards 

• Make a note for when are we creating database and whenever we creates database make sure to run full backup job for them. 
• Make the maintenance plan static and select particular databases only instead of ALL USER DATABASE.

Backup job failed -File in use Error 32

Sometime backup job failed with the following error:


Alert description: BackupDiskFile::CreateMedia: Backup device 'xxx.bak' failed to create. Operating system error 32(The process cannot access the file because it is being used by another process.).


Here if we see the error, it clearly states that the file is in use somewhere and for this reason backup job failed. Generally we creates backup maintenance plan to take backup and after backup completes successfully we also include the maintenance cleanup task, which maintains the retention period of old backup files to keep our Drive space free. 

Convert Second to HH:MM:SS

You can convert seconds to HH:MM:SS in various method. 

Method 1 

DECLARE @Seconds INT 
SET @Seconds = 7600
SELECT CONVERT(VarChar(10),@Seconds/3600)  
    +':' + RIGHT('00'+CONVERT(VarChar(2),(@Seconds%3600)/60),2) 
    +':' + RIGHT('00'+CONVERT(VarChar(2),@Seconds%60),2) AS [HH:MM:SS] 
GO 

The result is  02:06:40

Method 2 


DECLARE @Seconds INT,@Hour VarChar(10) 
SET @Seconds = 87810 
SET @Hour = DateDiff(HH, 0, DateAdd(SS,@Seconds,0)) 
SELECT STUFF(CONVERT(VarChar(8), DateAdd(SS,@Seconds,0),108),1,2,@Hour)  AS [HH:MM:SS]
GO 

The Result is 24:23:30