Faster SQL Server on Twitter

Follow me on Twitter for  DBA,SQL Server,DB2 and Performance Tuning very large databases infomation.

@jackvamvas

 

Email +Jack Vamvas at jack@sqlserver-dba.com  for any other questions. I do try and respond as quickly as possible. These posts may help you with urgent issues, also use the Search feature for other posts.

How to troubleshoot a slow running query in SQL Server

Current queries executing on SQL Server

Find high impact queries with sys.dm_exec_query_stats  

 

Forgotten sa password

It is frustrating to forget the sa password . Despite maintaining a password vault , it is  possible someone has changed the password or you don’t have access to the password vault. Don’t worry. If you have access to a Local administrator member than this method can help

1) Set the Single-User mode for the SQL Server Instance

go to start->run->type cmd->type services.msc->search  for SQL server (MSSQLserver) name->right click properties->

->under General tab you can see path to executable. Make a note of the sql server executable path  .

 go to start->run->type cmd->type  <sql server executable path> -m s<instance_name>

example if SQL Server Instance is called “Test1”:

 

C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn\sqlserver.exe –m sTest1

 

Note:
a) Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
b) Using the –s switch avoids the error :  Sql server installation is either corrupt or has been tampered with error getting instance id from name

2) Add a sysadmin account  through SQLCMD

 

EXEC sp_addsrvrolemember 'Org1\Johnny', 'sysadmin';
GO

 

3) Return to multiuser mode for the SQL Server Instance

 Stop the single user-mode session

Start the SQL Server Instance in multiuser mode

 

4) Change sa password

Using either SSMS or SQLCMD , reset the sa password

 

sp_password NULL,'new_password','sa'

 Read More

Find Weak passwords in SQL Server - SQL Server DBA

sp_password and ALTER LOGIN - SQL Server DBA

Account Locked out - SQL Server DBA

Sql server installation is either corrupt or has been tampered with error getting instance id from name

When attempting to start a SQL Server named instance in single user mode using the command line steps below , I receive a "Sql server installation is either corrupt or has been tampered with error getting instance id from name" . Is there a workaround?

Cd C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn>
Sqlservr.exe –m

 

To avoid this error , add the flag    -s<InstanceName> to the string .   If I have a SQL Server Instance called MyInstance ,  the command  should be :

 

Sqlserver.exe –m –sMyInstance

 

Connect to the server through SQLCMD - C:>\sqlcmd -S <sql server name>

Read More

  MULTI – RESTRICTED USER - SQL Server DBA

Drop Connections with ROLLBACK IMMEDIATE to allow a Detach database

Database Security Countermeasures against hacker attacks

 

Find Weak passwords in SQL Server

How can I find weak passwords in SQL Server logins?

Identifying weak passwords is an  important part of Security Risk Analysis. Setting  up a procedure to check for weak passwords should be part of the DBA daily healthcheck.  It is straightforward to check for a weak password using a SELECT statement and the PWDCOMPARE function.

The PWDCOMPARE function accepts 2 arguments – the first is the text password and the varbin value of the SQL password hash

 

--find SQL login with blank passwords
select name,type_desc,create_date from sys.sql_logins where pwdcompare('', password_hash) = 1

--find SQL login with password same as name

select name,type_desc,create_date from sys.sql_logins where pwdcompare(name, password_hash) = 1

 

 

Read More

Powershell sql server security audit - SQL Server DBA

How to create a SQL Server Security Audit - SQL Server DBA

Database Server Security Audit Process - SQL Server DBA

Find who made a database security change - SQL Server DBA

Event ID 55 : The file system structure on the disk is corrupt and unusable on volume

A message appeared in the a database server   Event Viewer   System Logs.The file system structure on the disk is corrupt and unusable on volume “Drive letter:”  I picked this error through  a daily report on Powershell Get-EventLog and Event Log messages   How can I resolve?

1) Identify the volume drive

 You’ll see the volume referenced at the end of the message. i.e The file system structure on the disk is corrupt and unusable on volume drive_letter

2) Use chkntfs to identify the file system state

e.g   chkntfs drive_letter

 

3) If the suffix   “is dirtry” appears ,

take action , go to step 4

4) Run chkdsk drive_letter

e.g chkdsk /X Driv_letter

According to msdn : “/x : Use with NTFS only. Forces the volume to dismount first, if necessary. All open handles to the drive are invalidated. /x also includes the functionality of /f. “

Before proceeding with step 4 – communicate with relevant  users , and make sure that all files are first backed up .

 

Read More

SQL Server – Daily Health Check Script with Powershell 

Career in DBA

A DBA career path is about thinking in strategic terms .  What  other skills can a DBA  develop their  in a  complex database server environment?

A rising trend  in the database server world is  a  hybrid of DBA and application administrator.

A common source of pressure is increased demand for DBA time but decreasing budgets for DBAs. Thinking clearly about career progression is a challenge. Here are some ideas.

Managing Enterprise infrastructure across the application stack is increasing in demand .  In an earlier post - DBA supertech . Specialise or generalise?   I introduced the idea of thinking beyond the database server.

A critical DBA skillset to develop is interacting at the Application, RDBMS OS, Hardware, network and storage layer.

How do you progress?

Basic DBA

1) Solid DBA skills  - backup and recovery, basic admin, security management, performance tuning, T-SQL. Read more on I want to become a DBA. What should I first learn ?

2) Dependant application admin skills

Intermediate DBA

1) Intermediate DBA skills – Solid basic skills & solid knowledge of an advanced SQL Server feature such High Availability ,Business Intelligence,Datawarehouse or other SQL Server – DBA Skills Growth Areas

2) Intermediate Dependant application admin skills

3) Deploy other middleware software – and interact with development teams on coding patterns, XML,.Net,single sign on

Advanced DBA

1) Advanced DBA skills – Intermediate DBA skills & solid knowledge of Enterprise management software. Experience and skills to operate in a complex database server environment

2) Advanced Dependant application admin skills such as Websphere, Business Objects,Sharepoint.

3) Deploy other middleware software – and interact with development teams on coding, XML,.Net,single sign on, connectivity, distributed transactions

4) Knowledge and experience of managing full Enterprise infrastructure in one of : BackUp \Recovery\Disaster Recovery, Security Management, Performance , Diagnostics

5) Knowledge of other database server systems such as DB2

Let me know  your thoughts and experiences . It is difficult to obtain experience and knowledge to progress. Hopefully this post offered some ideas

Read More

 Top 10 DBA Tips to develop your competitive edge

DBA Resume FAQ

DBA Ignorance , complacency and arrogance experiment