SQL SERVER – Start SQL Server Instance in Single User Mode

There are certain situation when user wants to start SQL Server Engine in “single user” mode from the start up.

To start SQL Server in single user mode is very simple procedure as displayed below.

Go to SQL Server Configuration Manager and click on  SQL Server 2005 Services. Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box.

Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server services to take this in effect. Once this is done, now you will be only able to connect SQL Server using sqlcmd.

Make sure to remove newly added params after required work is completed to restart it in multi user mode.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Shrinking NDF and MDF Files – A Safe Operation

Just a day ago I have received following email from Siddhi and I found it interesting so I am sharing with all of you.

Hello Pinal,

I have seen many blogs from you on SQL server and i have always found them useful and easy to understand. Thanks for all the information you provide.

I have one query about shrinking NDF and MDF files.

Can we shrink NDF and MDF files?? If you do so is there any data loss?

I have been shrinking the .LDF files every now and then but I am not too sure about NDF and MDF files.

Can you please answer my query.

Waiting for your early response.


Shrinking MDF and NDF file is possible and there is no chance of data loss.

It is not always advisable to shrink those file as those files are usually growing. There are cases when one database is separated in multiple database of any large table is dropped from database MDF and NDF can contain large empty space. This is the time they should be shrank. Shrinking database can be many hours process but it there are very slim chances of data lose.
Following is the script to shrink whole database.

Following is the script to shrink single file.
DBCC SHRINKFILE (logicalLogFileName)

To find logicalLogFileName following command has to be ran.
USE dbName
EXEC sp_helpfile

Let us understand this using database AdventureWorks.
/* Shrink Whole AdventureWorks Database */
/* Get the Logical File Name */
USE AdventureWorks
EXEC sp_helpfile
/* Shrink MDF File of AdventureWorks Database */
DBCC SHRINKFILE (AdventureWorks_Data)
Following image of the same process show when whole process is done there will be resultset with information about the new states of the database files.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – CWE/SANS TOP 25 Most Dangerous Programming Errors

I just came across very interesting article from SANS Institute. Experts from more than 30 US and international cyber security organizations have released list of 25 most dangerous programming errors and their resolution. It may be possible that many of the programmers may not understand what this errors are and how to implement their solution. As said this are 25 most dangerous errors and all the developers should atleast know what they are so they do not are prevented from origin. Here are four major advantages listed by SANS.

  • Software buyers will be able to buy much safer software.
  • Programmers will have tools that consistently measure the security of the software they are writing.
  • Colleges will be able to teach secure coding more confidently.
  • Employers will be able to ensure they have programmers who can write more secure code.

Following is the list of 25 most dangerous programming errors.

CATEGORY: Insecure Interaction Between Components

CWE-20: Improper Input Validation
CWE-116: Improper Encoding or Escaping of Output
CWE-89: Failure to Preserve SQL Query Structure (aka ‘SQL Injection’)
CWE-79: Failure to Preserve Web Page Structure (aka ‘Cross-site Scripting’)
CWE-78: Failure to Preserve OS Command Structure (aka ‘OS Command Injection’)
CWE-319: Cleartext Transmission of Sensitive Information
CWE-352: Cross-Site Request Forgery (CSRF)
CWE-362: Race Condition
CWE-209: Error Message Information Leak

CATEGORY: Risky Resource Management

CWE-119: Failure to Constrain Operations within the Bounds of a Memory Buffer
CWE-642: External Control of Critical State Data
CWE-73: External Control of File Name or Path
CWE-426: Untrusted Search Path
CWE-94: Failure to Control Generation of Code (aka ‘Code Injection’)
CWE-494: Download of Code Without Integrity Check
CWE-404: Improper Resource Shutdown or Release
CWE-665: Improper Initialization
CWE-682: Incorrect Calculation

CATEGORY: Porous Defenses

CWE-285: Improper Access Control (Authorization)
CWE-327: Use of a Broken or Risky Cryptographic Algorithm
CWE-259: Hard-Coded Password
CWE-732: Insecure Permission Assignment for Critical Resource
CWE-330: Use of Insufficiently Random Values
CWE-250: Execution with Unnecessary Privileges
CWE-602: Client-Side Enforcement of Server-Side Security

Please go to SANS site and click on each error to find their resolution.

Reference : Pinal Dave (http://blog.SQLAuthority.com), list of 25 most dangerous programming errors

SQLAuthority News – Security White Papers

Microsoft Dynamics AX 2009 White Paper: Configuring Kerberos Authentication with Role Centers
This document describes how to configure Kerberos authentication with Enterprise Portal and Role Centers. Kerberos authentication is required to display reports created using Microsoft SQL Server Reporting Services and Microsoft SQL Server Analysis Services on Role Center pages.

Microsoft Dynamics AX 2009 White Paper: Configuring Enterprise Portal and Role Centers with SQL Reporting

This document contains checklists and information to help administrators set up and configure Microsoft Dynamics AX 2009 Enterprise Portal and Role Centers with Microsoft SQL Server® Reporting Services® and Microsoft SQL Server Analysis Services.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – sqlcmd – Using a Dedicated Administrator Connection to Kill Currently Running Query

People are judged from their questions and not their answers. I received wonderful question the other day.

How sqlcmd can be used along with currently running query script posted on your blog?

Please read following two posts before continuing this article as they cover background of this article.

SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

SQL SERVER – Find Currently Running Query – T-SQL

If due to a long running query or any resource hogging query SQL Server is not responding sqlcmd can be used to connect to the server from another computer and kill the offending query. SQL Server provides a special diagnostic connection which allows administrators to access SQL Server when standard connection is not possible. Except very extreme situations dedicated administrator connection (DAC) is always successful.

Let us see how we can use DAC using sqlcmd.

Connect SQL Server using sqlcmd and -A option which will establish DAC.

C:\>sqlcmd -S localhost -U sa -P dev -d master -A

For our test purpose run following query which overload server and probably make it unresponsive. If your configure is not strong I strongly suggest not to run following query.

Following query is just for test purpose and not part of solution.

USE AdventureWorks
FROM Person.Address
CROSS JOIN Person.Address a1
CROSS JOIN Person.Address a2
CROSS JOIN Person.Address a3
CROSS JOIN Person.Address a4
CROSS JOIN Person.Address a5
CROSS JOIN Person.Address a6
CROSS JOIN Person.Address a7
CROSS JOIN Person.Address a8
CROSS JOIN Person.Address a9

Once successfully connected it will provide prompt 1> enter following T-SQL query which will give SessionID of currently running query along with its elapsed time.

FROM sys.dm_exec_requests req
WHERE status = 'running'
AND req.total_elapsed_time > 1

Our previously running query gave session id 52 in on my server. The session id may be different for each SQL Server. Once the session id is figured out it can be killed using KILL [SessionID] command. Always make sure to type command GO after each complete query.


Once above query has run it will kill our example long running query and give following error.

Msg 233, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

sqlcmd is really useful utility of SQL Server and it comes in handy when server is not responsive. I strongly suggest to bookmark this article as it can come to rescue when nothing works and SQL Server is unresponsive.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find Currently Running Query – T-SQL

This is the script which I always had in my archive. Following script find out which are the queries running currently on your server.

SELECT sqltext.TEXT,
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

While running above query if you find any query which is running for long time it can be killed using following command.

KILL [session_id]

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Change Color of Status Bar of SSMS Query Editor

This is one very interesting issue which I have started to follow recently. Just like any other organization my company has many servers. Some are production and some are development. It is very much necessary that query which are written for developer environment does not run for production environment accidentally.

In SQL Server 2008 there is special feature which can change the color of the task bar. This will alert developer to run query on server.

Let us see quick tutorial with images which explains how the color of the status bar in SQL Server management studio can be changed.

Another example of different color status bar.

Reference : Pinal Dave (http://blog.SQLAuthority.com)