Lost Administrator SA password SQL server 2012

by Muthukkumaran kaliyamoorthy Published on: July 27, 2014
Comments: 8 Comments
Tags:
Categories:DBA

There are many blog posts talking about, how to recover “SysAdmin” password in case if it’s lost OR the server is new to you and no idea that has own and access to it. In my case the second option J

In my opinion, it’s very easy to work on GUI instead of command line CMD :-) I used a SSMS.

 

Steps:

1. Stop the SQL server agent
2. Take the SQL server in single user mode, earlier version, it’s a bit hard

All programs –> SQL server 2012 –> Configuration Tools –> SQL server configuration manager

Stop the agent and take the SQL server into single user mode.

 

3. Reboot the SQL service
4. Launch the SSMS and don’t click the connect button, you need to cancel it and then click the “New query” window on top of the SSMS and then connect.

If you click the connect button the object explorer will take one connection, Single user mode will allow only one connection. Because of that we need to cancel and click the new query window.

Note: You need to be an OS administrator.

 

 

5. Create a new login and add as an admin

 

CREATE LOGIN XXX WITH PASSWORD=Complex password'

SP_ADDSRVROLEMEMBER XXX,'SYSADMIN'

 

Hope this would help someone.

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

An application error occurred on the server running on SQL server

Recently one of my application website went down.

 

I checked all the basic connectivity troubleshooting and seem everything was looking and working fine. Finally, I found the problem with the browser service but that’s also in running state.

Error from the event viewer:

 

 

The quick solution is rebooting the SQL browser (Start –>All programs–>Microsoft SQL server 200X–>Configuration tools –>SQL server configuration Manager) without rebooting SQL service.

I searched and found a couple of MS links (KB-2526552 And SQLBrowser Unable to start) but, I did not apply it. I used another way that is also a permanent fix.

 

Troubleshooting ways and a permanent fix:

For me it’s a named instance and listening a dynamic port and DBAs knows the browser service is mainly for named instance.

From the local machine we can connect the server through SSMS by using server name and server name + port number. But, other than local machine you cannot connect the server by using server name. (You can test that by connecting some other server or better install only SSMS on the application server and try to connect it) so I went to the application server and opened a connection string as expected the data source only has the server name. So We changed it from Datasource “from Data Source=Servername\Instance to Data Source= Servername\Instance,port” Ex: Muthu1\SQL1,5432.

Application team made a standard to always include a port number in the connection string block i.e. FQDN.

 

A Basic SQL Connectivity checks:

  • Check SQL service is running or not and try to connect through SSMS from local and remote
  • Check TCP/IP protocol enabled on SQL server configuration manager and find the port number
  • Connect using a server+port number from SSMS local and remote
  • For firewall block/port not opened you can check through command prompt TELNET server port ex: TELNET server 1433
  • Check remote connections are enabled & SQL Browser service is running (For a named instance which is not using FQDN)
  • Check you have any alias/DNS name

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Create and setup a SQL clustering on your Desktop or Laptop

How to create and setup a SQL clustering on your Desktop or Laptop

 

I thought to write this post after I discussed in a forum. SQL clustering is a bit complicated one. I mean most of the DBAs are not configured or tested it, because we don’t get a chance to do or to learn SQL clustering. Also it needs bit knowledge from out of SQL (Windows AD, Network and Storage (SAN)).

In this post, I will give you an idea how to configure SQL failover clustering to yourself. We are doing everything in virtual environment (Single machine) the same you can do physically but you need all in physically in production environment (Machine, Domain, Storage & network). It’s not possible to show all the steps and screen shot here because it has more than 40 screenshots. I have prepared a document and will share with you if needed.

What is SQL clustering?

In general cluster means more than one.

SQL clustering is a collection of two or more servers access the data from same single shared storage (SAN), if one server failed another one server will take the data from the storage and the application will be running without any problem.

I suggest readers to read Kendra Little’s article. It’s good article and she explains with diagram.

http://www.brentozar.com/archive/2012/02/introduction-sql-server-clusters/

 

The followings are the software for setup clustering.

Software Free/Trail URL location
VMware workstation 30 day trial VMware workstation
Starwind 30 day trial Starwind iscsi SAN
Windows server 2008 60 day trial Windows server 2008
SQL server 2008 180 day trial SQL server 2008

 

Planning the IP address

Component Name IP address
Domain controller xxx.xxx.1.6
Node1 / Machine1 xxx.xxx.1.7
Node2 / Machine2 xxx.xxx.1.8
Windows cluster xxx.xxx.1.9
MSDTC xxx.xxx.1.10
Virtual SQL server xxx.xxx.1.11
Private IP xxx.xxx.2.11
Public IP xxx.xxx.2.12

 

 

Installing VMware

 

Installing a VMware workstation is an easy one. Just click the typical and next.

 

 

Creating VMware

 

Creating three virtual machines using the windows trail edition and assigning the right amount of RAM, CPU & disk Space for each guest host. Configure the network adapter for public and private network for internet connection.

 

1. Domain controller (DC)

 

2. Machine1 (Node1)

 

3. Machine2 (Node2)

Creating Domain DC

 

The domain controller is also known as active directory.

http://en.wikipedia.org/wiki/Active_Directory

 

  • Install and create a domain.

Creating Shared disk

 

We know SQL clustering needs a shared disk typically SAN. The use of StarWind’s ISCSI we can make our local disk as shared disk. Because we don’t have a SAN storage server physically so i am creating the shared storage in the DC machine itself using StarWind’s iSCSI.

 

Format the disk on each guest and use it.

Creating and installing windows Failover cluster

  • Installing the windows fail over cluster.
  • Validate and add the nodes.

 

  • Assign the IP and Cluster the Nodes
  • Add the MSDTC

 

 

Installing SQL Failover cluster

Install the SQL server failover cluster on both the nodes. Its easy one just you can give the network IP address and choose the shared disk.

Second node will automatically display the SQL cluster name and we can just add it.

 

To view the Cluster admin go to run –> type CLUADMIN.MSC

Node1 (Muthu1) is the primary host. Which will be taking care of the SQL server and the database applications.

 

Use this command to check which node the cluster SQL server is currently running.

SELECT SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’)

 

 

How do I know, Is the cluster automatically fail over the server, if anything goes wrong on the running node?

Just test it. Power off the Node1 (Muthu1) and you can see the SQL server automatically fail over to the node2 (muthu2).

 

When the SQL cluster will be down? Or Is clustering is disaster recovery?

SQL fail over clustering is not a disaster recovery. In case if the storage (SAN) is not available then clustering won’t start.

 

Testing: Power off the DC. In our case DC machine has storage shared disk. Hence the shared disk (SAN) goes off, then SQL clustering will not work.

 

My special thanks go to my VM ware team.

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Login failed for [SQLSTATE 28000] (Error 18456) the step failed

by Muthukkumaran kaliyamoorthy Published on: June 18, 2012
Comments: 1 Comment
Tags: , ,
Categories:DBA

Login failed for [SQLSTATE 28000] (Error 18456) the step failed

 

When you query the data from another SQL server (Linked server) and SQL server & agent service is run as a domain user. You may get the error following error message in your job history.

Error:

Executed as user: Domain\XXX.Service. Login failed for user ‘ SSBF\SQL.Service ‘. [SQLSTATE 28000] (Error 18456).  The step failed.

Solution:

Step 1: Check the source SQL server agent logon service account name

All programs –> SQL server 2008 –> Configuration Tools –> SQL server configuration manager. Go to the SQL server agent  –> properties –> Check the logon tab and find the account name.

Step 2: Check the same is available in the target SQL server.

Under the security tab –>logins.  If it’s not available add the domain user with necessary permission on your target machine.

I hope this will help someone.

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Find the database free space sql server The transaction log for database is full

The transaction log for database is full. To find out why space in the log cannot be reused

OR

Could not allocate space for object because the filegroup is full

Reema:

Hello DBA it seems our application not working today. We got some error it looks like some problem with the database.

DBA:

This error mostly occurs because of insufficient space on the drive.

Have you checked the drive space?

Reema:

Yeah, I have checked the drive space in a server –> my computer. It looks I have plenty of space on the server.

DBA:

Can you check the database auto growth setting in the database?

Reema:

I have 30 databases on my server. How can I check it?

DBA:

Check the error log and find which database throwing error and right click the database check the auto growth setting.

Reema:

It seems I am not principally SQL DBA. Can you tell me the steps?

The conversion goes like this.

 

Use of this script:

  1. When you face an insufficient disk space problem you can add your drive letter to the “WHERE CLAUSE” and you can find which file is using more disk space.
    “WHERE A.PHYSICAL_NAME LIKE ‘E%’”

 

  1. In some case, The auto growth setting will be restricted. In this case You can easily find the auto growth setting for all databases and you can change the setting for the database (DB which is causing a problem).

 

  1. In some case, The database size is big but it will not use as much space but you will get an insufficient disk space error.  In this case you can easily identify the database by using second script and you can reclaim the space.

 

Check the drive free space:

XP_FIXEDDRIVES

 

For all Databases:

The following script will show the database current size, auto growth setting and file location.

SELECT DB_NAME(A.DATABASE_ID) AS DBNAME,A.NAME AS FILENAME ,

SIZE/128.0 AS CURRENTSIZE_MB,

B.RECOVERY_MODEL_DESC,A.TYPE_DESC ,

CASE WHEN IS_PERCENT_GROWTH = 0

THEN LTRIM(STR(A.GROWTH * 8.0 / 1024,10,1)) + ' MB, '

ELSE 'BY ' + CAST(A.GROWTH AS VARCHAR) + ' PERCENT, 'END +

CASE WHEN MAX_SIZE = -1 THEN 'UNRESTRICTED GROWTH'

ELSE 'RESTRICTED GROWTH TO ' +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ' MB'

END AS AUTOGROW,A.PHYSICAL_NAME

FROM SYS.MASTER_FILES A JOIN SYS.DATABASES B

ON A.DATABASE_ID =B.DATABASE_ID

--WHERE A.PHYSICAL_NAME LIKE 'E%'

-- AND A.FILE_ID =2

ORDER BY A.SIZE/128.0 DESC

 

For a particular Database:

The following script will give about the particular database current size, used space; free space and auto grow setting.

SELECT DB_NAME() AS DBNAME,

NAME AS FILENAME,

SIZE/128.0 AS CURRENTSIZE_MB,

SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0)  AS USEDSPACE_MB,

SIZE/128.0 - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0 AS FREESPACEMB,

PHYSICAL_NAME,DATABASEPROPERTYEX (DB_NAME(),'RECOVERY') AS RECOVERY_MODEL,TYPE_DESC,

CASE WHEN IS_PERCENT_GROWTH = 0

THEN LTRIM(STR(GROWTH * 8.0 / 1024,10,1)) + ' MB, '

ELSE 'BY ' + CAST(GROWTH AS VARCHAR) + ' PERCENT, 'END +

CASE WHEN MAX_SIZE = -1 THEN 'UNRESTRICTED GROWTH'

ELSE 'RESTRICTED GROWTH TO ' +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ' MB'

END AS AUTO_GROW

FROM SYS.MASTER_FILES

WHERE DATABASE_ID = DB_ID()

 

In most cases, The log file gets bigger when you are not maintained in your database properly. That is choosing the right recovery model and backup types it will help to minimize the log file size.
http://www.sqlserverblogforum.com/2011/06/sql-server-recovery-models-and-backup-types/

Bear in mind: Ask your customer about their RPO and RTO and scheduled the log backup regularly.

 

Additional Reading:

http://www.sqlserverblogforum.com/2011/04/shrinking-the-log-file-script-for-sql-server-database/

http://www.sqlserverblogforum.com/2011/03/difference-between-truncating-and-shrinking-the-transaction-log-file/

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

SQL server failed to start the system cannot find the path specified

Recently one of our SQL server unable to start it. The problem with the storage LUNs.

 

 

Things you have to do: When you get this above message while starting SQL server service.

Check the event viewer both system and application log. There you will know the exact problem.

 

 

Error: SQL server failed to start the system cannot find the path specified

First check the drives and drive letters in my computer. Then verify the SQL server installed dive is available in your server.

Go to Start –> All programs –> Microsoft SQL server 2008 –> configuration tools –> SQL server configuration manager –> Right click the SQL server –> Properties –> Advanced –> Check the install path and startup parameters.

 

In my case I could not see the Installation drive in my server. The drives (LUN) that’s not connected properly to the server and we went to the storage side and connected the drive (LUN) and it works as expected.

Hope this would help someone.

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

page 1 of 6»

Welcome , today is Sunday, August 31, 2014