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 

Backup job failed – no space Error 112

Generally we get 112 error for backup and other sql related error log errors as: 

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL Server Driver][SQL Server]Write on 'xxx.BAK' failed, status = 112. See the SQL Server error log for more details. [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally. 

DESCRIPTION: BackupMedium:: ReportIoError: write failure on backup device 'zzz.bak'. Operating system error 112(There is not enough space on the disk.). 

Note– “When you create a maintenance plan and maintenance plan job fails always check the history at maintenance plan history or report location to get the exact error” 
112 error means disk is occupied and full, and if you read the error it clearly specifies that there is not enough space. On this drive 

To solve this error:

• Remove old backup unwanted files from that drive  
• Move the backup or database files to another drive.  
• Increase the drive size(external Drive SAN) 

SQL Server Configuration Manager – Access Denied

Sometime you may get below error message when you try to open SQL Server Configuration Manager.

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager
Access denied [0x80070005]

It may be possible that permissions to your account is messed up at OS level. Try to remove account from local admin group, delete the current profile for the user in the server. Reboot your box. The above issue will be resolved.

What caused this database corruption?

Most database corruption (database consistency errors) comes from hardware issues mainly Hard Disk issues. The first place to look is, IO subsystem not behaving well. 

Try looking into the system event log. You may find disk related errors which could be the root cause of your corruption.

You may see disk related errors like:

11/11/2011 11:11:11 Cissesrv Error None 24606 N/A TESTSERVER01 "Logical drive 3 configured on array controller P410i [Embedded] returned a fatal error during a read/write request from/to the volume.

Logical block address 257513711, block count 1024 and command 32 were taken from the failed logical I/O request.

Array controller P410i [Embedded] is also reporting that the last physical drive to report a fatal error condition (associated with this logical request), is located on bus 0 and ID 3. "

11/11/2011 11:11:11 Cissesrv Error None 24606 N/A TESTSERVER01 "Logical drive 3 configured on array controller P410i [Embedded] returned a fatal error during a read/write request from/to the volume.

Logical block address 257513711, block count 1024 and command 32 were taken from the failed logical I/O request.

Array controller P410i [Embedded] is also reporting that the last physical drive to report a fatal error condition (associated with this logical request), is located on bus 0 and ID 3. "

11/11/2011 11:11:11 dmio Warning None 35 N/A TESTSERVER01 dmio: Disk Harddisk2 block 257512687 (mountpoint E:): Uncorrectable read error

11/11/2011 11:11:11 dmio Information None 29 N/A TESTSERVER01 dmio: Harddisk2 read error at block 257512687: status 0xc0000185

11/11/2011 11:11:11 Disk Error None 11 N/A TESTSERVER01 The driver detected a controller error on \Device\Harddisk2. 

If you find any disk related issue like above than call some Hardware engineer to fix the issue or replace the HDD. The above error messages leads to a disk failure if no action is taken.

Error: 9002, Severity: 17, State: 4

The transaction log for database ‘DemoData’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

The database properties is set to auto-shrink and log file is set to some restricted value.

You will get this error when you are trying to shrink/auto_shrink starts for database log file but it didn’t work because of some active transactions. Please note, active part of the log file cannot be shrunk and can cause delay in shrinking process. In order to identify the exact reason as what is causing this issue, run below select query: -

SELECT log_reuse_wait,log_reuse_wait_desc FROM sys.databases WHERE name LIKE 'DemoData'

If log_reuse_wait is ’0′ that mean you are good to shrink the file now. You can see the short description of each log_reuse_wait value against the log_reuse_wait_desc field.

Here your database needs more log space so increases the log space or set the maximum limit of log file to some higher value/unlimited (make sure auto growth of database log is is enabled). Also, it is recommended to disable auto_shrink as keeping it enabled is not a good option.

SQL Server Maintenance plan not opening

Sometime you may fot the below error while opening SQL Server Maintenance Plan:

TITLE: Microsoft SQL Server Management Studio
‘Agent XPs’ component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online. (ObjectExplorer)

It is possible that someone had disabled the Agent XPs component on your server.

Run below sql query to re-enable Agent XPs and then try to open maintenance plan again. It will work.


sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Attributes do not match

Sometime you may get this error when you are tying to upgrade SQL Server and drive where SQL Server binaries and/or shared features are installed is compressed with NTFS windows file system compression and/or encryption is enabled.

Attributes do not match. Present attributes (Directory, Compressed) , included attributes (0), excluded attributes (Compressed, Encrypted)

To solve above error: 

• Go to “C:\Program Files”  and locate “Microsoft SQL Server” folder.
• Right click and select properties. 
• Go to Advanced option in General Tab. 
• Here look for “Compress contents to save disk space” and “encrypt contents to secure data”. 
• You will find these options as checked. 
• Uncheck these options and apply the changes. 
• Now run the install again and it will run successfully.

Tempdb data file grown high cannot shrink tempdb data file

Today I got the below error:


Could not allocate space for object 'dbo.xxx temporary run storage:  111111111111111' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. 


I checked and found that tempdb is grown to occupy all the drive space approx 500GB. which is very 
abnormal. When checked the processes using 
Sp_who2 ‘active’ 
And 
Select * from sys.dm_exec_sessions and  Select * from sys.dm_exec_requests 
Found that big complicated query (several views using several tables join) creating several hash tables 
and temporary objects using tempdb.  

There are several ways to solve this issue:


As tempdb re-creates when we restart the sql server, so restarting the sql server would be 
easiest way to resolve this issue. But generally no one wants to restart the services just due to this, we have an alternative for it but it has its own performance penalty 


DBCC FREEPROCCACHE: as some objects are stored in cached and due to which tempdb holds 
the objects, so clearing the cache would be good option if you are good to pay just clearing 
cache objects, includes compile plan/execution plan and other memory objects –clearing 
memory (which happens when we restart sql services as well). Due to which all the queries will 
be re-compiled and re-executed and slower down the queries output,


This is a great solution this helped me to resolve my tempdb data file growth issue. 

Ref: http://support.microsoft.com/kb/307487

Change collation for tempdb

You can change collation for any user database with the following query: 

ALTER DATABASE database_name COLLATE collation_name

But changing collation of TempDB is little bit different. In this case you cannot change TempDB collation with ALTER DATABASE command.
Instead, you have to change the collation of model database to the required collation setting that you want for tempdb. After that restart SQL Server services and you will find that tempdb collation has changed.
Note:- Remember whenever SQL Server services are restarted, tempdb is recreated using model database and it will inherit all the properties of model database.

Misconception regarding database repair

Today I came across one article regarding misconceptions about running repair by Paul S. Randal. I liked that article and want to share that article in my blog. There are various misconception regarding repairing database with DBCC CheckDB.

• Repair will not cause data loss. It depends. If you have to use REPAIR_ALLOW_DATA_LOSS, then you're going to lose data. That's why the option is named that - seriously.
• Repair should be run as the default. No. Figure out what's wrong first before deciding what to do about it. If you've got a damaged 1TB clustered index, it's going to get rebuilt by repair. If you don't have an extra 1TB of disk space, it will fail, and then you're back to square one after hours of fruitless effort. You might be able to get away with doing something that doesn't involve taking the database (essentially) offline.
• You can run repair without running DBCC CHECKDB. No. Repair is an option to one of the consistency-checking commands (DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKDB - note that DBCC CHECKFILEGROUP and DBCC CHECKCATALOG don't support repair).
• As soon as you've run repair, everything's fine. No. You should always run DBCC CHECKDB a second time after running repair, to make sure the first repair fixed everything. Sometimes a corruption prevents some deeper checks being done, and when it's fixed, the next DBCC CHECKDB can run the deeper check and find more corruptions. I call this 'corruption masking'. The other reason is that repair probably just deleted some of your data. What effect is that going to have on the application? What if that's medical patient data? Or insurance records? Or back account details?
• Repair can always fix everything. No. There are some things that DBCC CHECKDB cannot fix. See CHECKDB From Every Angle: Can CHECKDB repair everything? for the list.
• Repair is safe to use on system databases. No. It cannot be used on master or tempdb because they cannot be put into single-user mode. You can do it on model, but it's unlikely to have an effect as there aren't any user tables in model (unless you create them) and system tables generally don't get repaired. You can run it on msdb, but it might have strange side-effects. See Is running repair on msdb safe?.
• You can run repairs online. No. Repairs are always offline, in that the database must be in single-user mode.
• REPAIR_REBUILD will fix everything. No. REPAIR_REBUILD only fixes problem in nonclustered indexes. In 2005 onwards, REPAIR_FAST does nothing at all.
• Repairs on a repl Publisher propagate to the Subscribers. No. Anything done by repair is NOT marked for replication. You must reinitialize your Subscribers if you repair a Publisher.
• Repairs always fix-up constraints. No. It has no idea that constraints exist. After repairing a database with constraints, you should run DBCC CHECKCONSTRAINT to make sure they're still valid.
• Repairs try to save data. No. It doesn't go out of it's way to delete data, but it doesn't go out of it's way to save data in most cases. 'Delete what's broken and fix up all the links' is my sound-bite explanation of what repair does. Fix things as fast as possible and as provably correct as possible.
• EMERGENCY mode repair will always work. No. I've seen cases where something broken in the file-system caused it to fail. Don't rely on repair.
• You can undo repairs. It depends. If you started an explicit transaction, then you can roll everything back if you don't like what it did. People rarely do this though. EMERGENCY mode repair can never be rolled back.

Repair's a dangerous beast and should only be used as a last resort, or to bring a VVVLDB online again potentially much faster than a full restore, when a small amount of data loss can be tolerated. This is just a quick brain-dump of things people get wrong about repair.

Ref: http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-database-repair.aspx

Changing column type for a table with clustered index

Suppose you have a very large table with more than 2 million row. Now you need to change a column datatype (say bigint to smallint). 

Now, if the column you are changing is part of the clustered index definition, then you have to rebuild the clustered index. 

Any non-clustered indexes involving that column will have to be rebuilt when you change the column type, also.

If this table is a heap and does not have a clustered index than you you have to rebuild nothing.

View recently run queries

Use below query to get recently run queries

SELECT DEQS.last_execution_time as [Time], DEST.text as [Query] 
FROM 
    sys.dm_exec_query_stats as DEQS cross apply  sys.dm_exec_sql_text(DEQS.sql_handle) as DEST 
order by DEQS.last_execution_time DESC

Generating user instances in SQL Server is disabled. User sp_configure ‘user instances enabled’ to generate user instances.

To solve this issue, please follow the following steps:

1. Start the SQL Server Management Studio Express.
2. Connect to the default instance of that server (Your Machine Name\SQLEXPRESS)
3. Then open a New Query Window. Please check that you are connecting to the master database
4. Type: exec sp_configure ‘user instances enabled’, 1
5. Type: RECONFIGURE
6. Restart the SQL Server

Output time in days, hours and minutes

Use below query that will format a time span by days, hours and minutes.


DECLARE @FromTime DateTime
SET @FromTime = '2009-07-11 14:19:40.000'


DECLARE @ToTime DateTime
SET @ToTime = getUtcDate()


SELECT 
    CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)/(24*60)) + 'd ' 
  + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%(24*60)/60) + 'h '
  + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%60) + 'm' AS Format1
  , CASE 
      WHEN (((DateDiff(mi, @FromTime, @ToTime)/(24*60))) > 0) THEN
          CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)/(24*60)) + 'd ' 
        + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%(24*60)/60) + 'h '
        + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%60) + 'm'
      WHEN (((DateDiff(mi, @FromTime, @ToTime)%(24*60)/60)) > 0) THEN
          CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%(24*60)/60) + 'h '
        + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%60) + 'm'
      ELSE
          CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%60) + 'm'
    END AS Format2
  , CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)/(24*60)) + ':' 
  + RIGHT('00' + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%(24*60)/60), 2) + ':'
  + RIGHT('00' + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%60), 2) AS Format3

Error 3154 - The backup set holds a backup of a database other than the existing database.

Today when I was restoring database on SQL Server Management Studio, I got an error 
Error 3154- "The backup set holds a backup of a database other than the existing database".

Solution


After selecting the database backup, goto option Tab from the Left Hand Side Menu and click on First Restore Option i.e Overwrite the existing database(WITH REPLACE) as shown in screen below.

* Make sure that there is no database file exist at the path mentioned in RESTORE the database file as section with same file name otherwise it will replace those files.

Search a table used in all SP

You can search for a table used in all the stored procedure. It is a very basic information but makes it easy while making changes to a table name and like to change the procedures using the table.


SELECT '[' + SPECIFIC_CATALOG + '].[' + SPECIFIC_SCHEMA + '].[' + SPECIFIC_NAME + ']', ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%STRING%'