SQL Server Job Failed [SQLSTATE 42000] (Error 3013) and (Error 3202)

My SQL Server job is failing throwing errors as shown below 

[SQLSTATE 01000] (Message 3211)  Write on
"\\backup-location\folder\database_name.BAK"
failed: 2(The system cannot find the file specified.) [SQLSTATE 42000] (Error 3202)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

The sql jobs are failing due to network dis-connectivity to the network location where my backups will be written to.
So I made use to retry_attempts and retry_interval parameter options available in job properties. as show below pic.

But I have 102 steps,  I don't want to waste my time in updating all the job steps, instead I want to update all steps at once. Here is the query that I used to update all steps of a particular job that I wanted to update retry_interval and retry_attempts of a job.


TO SET RETRY ATTEMPTS to the job
Update sysjobsteps
Set retry_attempts=3 (--set as you prefer)
from sysjobs S
inner join [dbo].[sysjobsteps] j
on j.job_id=S.job_id
where name='JOB NAME'

TO SET RETRY INTERVALS to the job

Update sysjobsteps
Set retry_interval=1 (--set as you prefer) 
from sysjobs S
inner join [dbo].[sysjobsteps] j
on j.job_id=S.job_id
where name='JOB NAME'

Note: Run at your own risk. It worked for me. This may be not the ideal solution since it is more related to the Network packets being dropped, and in-stable Network. I spent several months on resolution for network issues. But I used this, hope this helps others. 

Identity Specification (Is Identity) SQL Server Table

Saving Changes is not Permitted. Tables need to be dropped and re-created. Message is appeared while you try to change the (Is Identity) Column  Property of a table.

How to make a change without dropping the table.
Follow the below steps.

Go to Tools>Options>Designers> Un-check Prevent Saving changes that requires table re-creation. 

Message 15063, Login Already Exists

Recently, I received an error from a Share-Point 2013 database creation from the Farm Account

As you all know, while triggering a new database from SharePoint Site its uses a farm account to create a new database on SQL Server, and during the creation as a DBA its our responsibility to grant necessary permissions, in my case I have granted DB_Creator server role as per MS recommendations.
after the database has created by the farm and is the owner of the database, but still its not able to access within the database. Very strange huh..
and then I got involved in to the issue to have it fixed.

I tried to add users, since it does not exist in the database, but the user has db_owner rights when I tired to add Farm account to the database from the login in properties users mappings in the GUI very strange. and I received an error while tried to add and hit OK.

The login already has an account under a different user name. 15063

I verified the database login and user info by running the below script. 

SELECT  l.loginname , u.name AS username

FROM    sysusers u INNER JOIN master..syslogins l ON u.sid = l.sid

I found that the users is mapped to [DBO]. So  I changed the database owner to 'sa'

by running the below script

sp_changedbowner 'sa'

and then I ran the below statements to grant access to the existing farm account. 

CREATE USER [domain\FARMacocunt] FOR LOGIN [domain\FARMacocunt]

GO

ALTER ROLE [db_owner] ADD MEMBER [domain\FARMacocunt]

GO

That's how I resolved the issue,  but I do not know why it happened to behave like this during the time of new database creation. 

Lost SQL Server access, forgot 'sa' password no clue how to gain access?

I recently encountered an issue where I didn't have access to SQL Server and I was asked for help to debug and gain back control on the system and the server hosting sql server database. 

Here is what to do.

Step1) Login to the server as administrator. 

Step2) Download PSExec tool from 

https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

Its windows sysinternals utility. 

Why? 

PsExec allows you to run programs as the NT AUTHORITY\SYSTEM account

Step3) Find the location of your SSMS.( SQL Server Management Studio) 

Right click on the properties of SSMS and click on shortcut tab. 

Copy the location to a notepad. 

Step4) Open Command Prompt run as administrator

Step5) Navigate to the folder where you have downloaded and extracted PSexec tool.

Step6) Run the following command to start your SSMS.

PsExec -s -i "F:\SQLServer\Installs\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"

Please refer to https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

s-    Runsthe remote process in the System account.  i-  Runs the program so that it interacts with the desktop of the specified session on the remote system. If no session is specified the process runs in the console session. Step7 )  go to Security and gain access or add user as login and add them to the sysadmin server role.

Database in Recovery Pending State

Database 'db_name' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Looks like Log file got corrupted and not allowing the database to come online. 

First thing make sure you have .mdf and .ldf files in your drives. 

next verify 

select * from sys.sysdatabases where name ='db_name'

Verify the filename column sees the database .mdf file with full path. 

Next 

Execute the following commands to bring your database to online. 

alter database [db_databasename] set offline with rollback immediate

alter database [db_databasename] set online with rollback immediate

with this your database is online and accessible. 

I would also recommend to run the following command to make sure no corruption. 

DBCC CHECKDB ('db_databasename')

High PLE high Performance.

Page Life Expectancy commonly called as PLE,  is number of seconds a page will stay in the buffer pool. 
The longer the pages stays in memory cache, the higher the PLE is, the high PLE gives high performance. Each time request comes, first SQL Server will check in the memory cache if it exists or not. If the results or data is found in memory cache (which is also called as buffer pool) decreasing the search time, then it will display the results that is read from cache. If sql server could not find the information in cache then it will look for the data in pages (that is stored in hard disk) now we are increasing the search time. So the high PLE leads to high performance. Below is the query to check the PLE value. 

select 
[object_name],
[counter_name],
[cntr_value] as Current_value
from
sys.dm_os_performance_counters
where
[object_name] LIKE '%Manager%'
and
[counter_name] = 'Page life expectancy'

Also always Buffer cache hit Ratio should be 100 or close to 100.
Below is the query to check the Buffer cache hit Ratio.

Both values can be obtained from Perfmon tool by adding respective counters.

select 
[object_name], 
counter_name, 
cntr_value as current_Value
from 
sys.dm_os_performance_counters
where 
[object_name] LIKE '%Buffer Manager%'
and
[counter_name] = 'Buffer cache hit ratio'

SQL Server Error Log Recycle (ERRORLOG file growing large)

SQL Server Error log is too large and has grown upto 24GB, it takes a lot of time to open and sometimes hangs and SSMS gets into not-responding state.

Go to the location of the error log and see what's the size as shown in the pics below.
If you don't know the location of file, open management studio new query and execute the below query

use master
go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', null, null, N'asc' 
go

From the results set in the text column you can find the location of your error log file.

Recycle log means creating the new error log file to start recording events in the new file so that its easy to search something in a new file rather that has fewer events rather than searching large file takes time.

Open management studio connect to the Instance, open new query.
and run the following statement.

use master
go
exec sp_cycle_errorlog
go


New ERROLOG file will be created by renaming the older file (24Gb) to ERRORLOG.1. You can comfortably delete the file to save space if you don't need it.
and expand the management studio you will see two files.

1) Current being the new file and starts to record new events.

2 Archive being the old recorded events, if you don't need it you can delete the physical file from the location.

Where does SQL Server 2012 License Key gets stored?

Many of my folks had a question about MicroSoft SQL Server License Key.

SQL Server license key can be found at the below location the registries. 

launch REGEDIT(Registry Editor) from the command prompt type: regedit

Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup DIGITALPRODUCT ID

Connecting to the Integration Services service on the computer "SERVER_NAME" failed with the following error: "Access is denied."

TITLE: Connect to Server
------------------------------
Connecting to the Integration Services service on the computer "SERVER_NAME" failed with the following error: "Access is denied."
By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.
------------------------------
Connecting to the Integration Services service on the computer "SERVER_NAME" failed with the following error: "Access is denied."
By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.

This error occurred when I upgraded SQL Server 2008R2 to SQL Server 2012.
Everything was working until then, then I realized going back to old school its permission error, after integration services installed as an administrator needs to grant access to the users and groups who were in earlier version of SQL Server 2008R2, 2005 including me who actually installed\upgraded to sql 2012.

Here is the fix that I used it works after I granted access to the users or groups as below to DCOM.
First launch Component services.
from Search type component or from command prompt  type Dcomcnfg.exe and hit enter.
And from the below figure expand DCOM Config, to the
MicroSoft SQL Server Integration Services 11.0
1) Right click and select Properties.
2) Select Security Tab
3) On Launch and Activation Permissions select Edit.
4) now Add the member or groups that needs access to Integration Services.
and Finally Restart your Integration Services and Restart SSMS.

.

Cannot see ODBC Driver after installation of Oracle 11g client.

I installed the ORACLE client tool full Administrator option
Still not able to see the ODBC drivers for Oracle?

I know I have installed full administrator. Now next thing what I did was I went back and verified what version of Oracle Client I installed, I found it was 32 bit and My server is windows server 2008 r2 is 64 Bit.
So that explains we have a compatibility issues.

Without again re-installing 64 bit Oracle 11g client.
I went into
C:\Windows\SysWOW64\odbcad32.exe and launched the ODBC data source Administrator.
Now I see all the driver of 32 bit that I installed.

In my case I need 32 bit drivers, so If you still need 64 bit drivers Please install 64 bit client Oracle 11G drivers only.