Welcome to the World of SQLServer.........

How to Troubleshoot Tempdb issues without restarting SQLServices

2013-01-04T11:15:00.002+05:30

TEMPDB:

Whenever Tempdb gets full and there is no space on tempdb drive…… it may not allow new connections through SSMS try following steps to put the SQL back to normal state.

1. Try in connecting through Query Analyzer

2. Check any live transactions are going on : DBCC Opentran(‘tempdb’) and check what spid is doing (- DBCC inputbuffer(SPID)), if it is long running query and try to kill it – Kill SPID

3. If there is no long running query found in step-2 then issue check point by running following query, this will flushes out the committed transactions

Use Tempdb
Checkpoint

4. After step-3 the SQL will begin to respond normally and now you need to shrink the log/data file accordingly

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, SIZE = 1280MB)
GO

5. Sometimes nothing will work in that case try below code if this doesn’t work then the final option is restarting SQL services

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE (tempdev16,18250)
Go

Following are the scenarios where in Tempdb will be used in.

Ø Any sorting that requires more memory than has been allocated to SQL Server will be forced to do its work in tempdb;

Ø If the sorting requires more space than you have allocated to tempdb, one of the above errors will occur;

Ø DBCC CheckDB('any database') will perform its work in tempdb -- on larger databases, this can consume quite a bit of space;

Ø DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb;

Ø Large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb;

Ø Any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb;

SQL 2008/R2/2012 Cluster installation error: Failover cluster instance name “ABCDEFGH” already exists as cluster resource?

2012-12-20T16:32:00.000+05:30

SQL 2008/R2/2012 Cluster installation error: Failover cluster instance name “ABCDEFGH” already exists as cluster resource?

Cause:

Usually this error pops up when you re-install SQL2008/R2/2012 on a cluster (which had SQL2008/R2/2012 instance in the past). Installer will actually checks whether input SQLNetworkName resource is already there in the Cluster, if it finds any orphan resource related then error will be displayed.

Resolution: when you uninstall the existing SQLServer on Windows 2008/R2 clusters sometimes uninstaller will not completely remove SQL resources from cluster. If you open cluster administrator through GUI you can’t find it so you need to check through command prompt.

Startà Runà CMD – Run as Administrator

At command prompt: type à Cluster res This command will list you all the resources including orphan resources.

To delete the orphan resource: type à Cluster res <resource name> /delete

< Resource Name – here you need to supply the exact resource name> in case of any dependencies it will prompt and you need to first delete dependencies and then delete SQLNetworkName resource>

Once you delete SQLNetworkName resource then try re-running the setup.

What is SQLSerrver2012 Contained Database Authentication and how it works?

2012-12-14T15:36:00.000+05:30

What is Contained Database Authentication?

This is one of the new feature introduced in SQLServer 2012, before we actually get in to it first I would try to explain drawback of login/users especially when you move the database from one server to another server.

There are different methods one can use to copy/move a database, when you move/copy a database to other sql server, you need to sync the login and users, if the login is not there on destination server you have to create and you need to give right access if login is there but user doesn’t have proper access…… so DBA is required to resolve those login/user access because prior to SQL2012 logins will be only created at Syslogins table in Master database and when you give access to particular databases those logins will be created as a Users in respective databases. When you move databases from one location to another - users will go along with databases but not logins. When it comes to accessing SQL server first it verifies with syslogins , if it is there then only it is allowed to connect.

This has been greatly over come with new feature Contained Database Authentication, in this users logins are directly stored on user Databases (instead of syslogins in master database which is default in prior versions). It is very secure because users can only perform DML operations inside the user databases and not database instance level operations. It also reduces the need to login to the database instance and avoid orphaned or unused logins in the database instance. This feature is used in AlwaysOn to facilitate better portability of user databases among servers in the case of server failover without the need to configure logins for all database servers in the cluster.

Example:

Step1: Enable Contained Database Authentication option on SQLServer.

Ø Connect to SQLServer through SSMS

Ø Right click on instance and go to Properties à Advanced Page

Ø Select True on ”Enable Contained Database”

Step2: Create a Database set option Containment type with “Partial”.

Ø Connect to SQLServer through SSMS

Ø Right click on databases and create a new database “Contained_DB” while creating go to Option

Ø Select “Partial” for Containment Type.

Step3: Create a user “Contained_User” in Contained_DB

Ø Right click on “Contained_DB” click on Security – USER

Ø Right click on User and click on new user

Ø Select “SQL user with Password” for user type and furnish User name Contained_User and type password to complete the user creation.

Step4 : How to connect with this user.

Ø Connect to SSMS

Ø click on Connection Property Tab

Ø Type database name as Contained_DB in connect to database box

Ø Click on login tab Type the SQLServername

Ø Select authentication to “SQL”

Ø Type user name as Contained_User and password

Ø Click on Connect

How do I enable xp_cmdshell?

2012-12-11T15:51:00.000+05:30

How do I enable xp_cmdshell?

Xp_cmdshell is extended stored procedure, with help of this one can execute any kind of Dos commands. Usage of this are: directory listing, creating folders, copy files….

DBA should limit grating this access to users otherwise there is a chance of misutilization and thats why most of the companies disable it for security reasons.

This is a server level option which comes up with Surface Area Configuration, by default xp_cmdshell is disabled on SQLServer. You can enable it by running following query.

USE MASTER
GO
EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO

RECONFIGURE
GO

You can also enable it through SSMS:

Connect to SQLServer through SSMS
Right click on SQLServer and go to Properties
Click on Facets
On view Facets page select Surface Area Configuration from drop down list

Got to XPCmdShellEnabled property and type “True” and click ok.

How to list traces and stop from SQL Server?

2012-12-11T15:22:00.003+05:30

How to list traces and stop from SQL Server?

We often create custom traces to capture some information on sql server and most of the times we forgot to stop them.

Following query will list all the traces which are running on a SQLServer.

SELECT * FROM ::fn_trace_getinfo(default) WHERE property = 2;

Copy the Trace id which you want to stop trace and feed that in below query and execute it.

DECLARE @TraceID int

SET @TraceID = <input the Trace id value from above query>

EXEC sp_trace_setstatus @TraceID, 0

EXEC sp_trace_setstatus @TraceID, 2

What are the different ways to move User Databases in SQLServer?

2012-12-09T16:28:00.000+05:30

What are the different ways to move User Databases in SQLServer?

There are different methods to move user databases from one location to another location

Attach/Dettach Method
Take Full backup and Restore
Alter Database command within same instance
Transfer Database Task through SSIS

Out of 4 methods first and fourth methods you can follow to move a user database within same instance or on other instance, whereas 2^ndis for moving to other instance and 3^rd method is only applicable within same instance

Attach/Dettach:

This is the simple and easy method

Connect to SQLInstance through SSMS
Click on database which you want to move
Right click and select Dettach
Move database files (.MDF an d.LDF) locations to new location
Right click on Databases select Attach
Here give new .MDF and .LDF location

Take Full backup and Restore:

Connect to SQLInstance through SSMS
Click on database which you want to move
Right click –Tasksà Backup
Take full backup
Copy the backup to other sql server
Restore it

Alter Database command:

Run following command

USE master;

ALTER DATABASE ABC

MODIFY FILE (NAME = ABC, FILENAME = 'E:\SQLData\ ABCdata.mdf');

ALTER DATABASE ABC

MODIFY FILE (NAME = ABC, FILENAME = 'F:\SQLLog\ ABC log.ldf');

2. Copy the files to new location

3. Restart SQL Services.

Transfer Database Task through SSIS:

The Transfer Database Task is used to move a database to another SQL Server instance or create a copy on the same instance (with different database name).

How to move Master/Model/MSDB/Tempdb System Database files in SQLServer?

2012-12-09T15:53:00.001+05:30

How to move Master/Model/MSDB/Tempdb System Database files in SQLServer?

There are times when you want to move the system databases files from installed locations to new location..... This can be accomplished without any errors if you follow the moving order of the database.

Microsoft recommends that SqlServer binaries (System database files and Installation files) should not be on System drive –“C”, the reason behind this is, if Operating System gets corrupted you will lose binaries otherwise if it is on nonsystem drive(other than C drive) you will not lose binaries.

Most of the companies use nonsystem drive for binaries… Let’s say one of the Junior DBA installed SqlServer but mistakenly used “C” drive for binaries and now you have to move them to nonsystem drive for example ”D” drive.

Following query tells you about the system database file locations.

SELECT physical_name
FROM sys.master_files

First we need to move Master, msdb, model and tempdb:

From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab.
In the Existing parameters box, select the –d parameter to move the master data file. Click Update to save the change.
In the Specify a startup parameter box, change the parameter to the new path of the master database.

In the Existing parameters box, select the –l parameter to move the master log file. Click Update to save the change. In the Specify a startup parameter box, change the parameter to the new path of the master database.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data file.

-dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf

-lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

If the planned relocation for the master data file is E:\SQLData, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf

-lE:\SQLData\mastlog.ldf

Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.
Move the master.mdf and mastlog.ldf files to the new location.
Restart the instance of SQL Server.

Verify the file change for the master database by running the following query.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');

For MSDB and Model database perform following steps

ALTER DATABASE MSDB/Model MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
Stop the instance of SQL Server or shut down the system to perform maintenance. For more information, see Stopping Services.
Move the file or files to the new location.
Restart the instance of SQL Server or the server. For more information, see Starting and Restarting Services.

Now its turn to move Tempdb:

USE master;

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');

After running above query, restart the SQL Services

After moving system data files, verify them by running following query.

SELECT physical_name
FROM sys.master_files

How to kill a SQL Connection/SPID through Activity Monitor?

2012-12-08T19:22:00.001+05:30

How to kill a SQL Connection through Activity Monitor?

Activity Monitor is Graphical tool which tells you about the currently running processes, Resource Waits, Data file I/O and Expensive Queries.

This information is compiled from various System Tables and Views and if you have right permission you could re-arrange columns, click to sort, and right-click to kill a process and another advantage is- it will guide you to other tools that will help you solve those problems.

You need proper permissions(“View Server State”) to view Activity Monitor and to kill a connection you need SA permissions

The processor time shown here is a percentage of the time that SQL Server is exercising the processor — not the entire CPU itself. What that means is that while SQL Server might not be using a lot of processor, the Windows Operating system might have other tasks that are. Also, if you tied out only one processor to SQL Server on a dual-processor box, you need to take that into account as well.

Waiting tasks are the SQL Server operations that are waiting on a resource to do their work. There is an entire school of thought about using wait information for Performance Tuning, and it has merit. Basically this school of thought says that if your system isn’t waiting on anything, it is working as fast as it can.

The Database I/O graph shows how often the system is hitting the storage subsystem. It’s not an exact or comprehensive measurement, but it can quickly show you that this is where a lot of activity is going on. You want to avoid hitting the drive as much as you can, since this physical component is one of the slowest in the system.

Batch Requests show general SQL Server query activity, although it’s only one of the measurements in this area.

How do I open Activity Monitor?

Connect to SQLServer through SSMS

Right click on Server and click on Activity Monitor

Expand Process box
Select the connection which you want to kill and right click and finally click on “Kill Process”

How to locate Surface Area Configuration in SQLServer2008/R2/2012?

2012-12-08T18:32:00.003+05:30

How to locate Surface Area Configuration Tool in SQLServer2008/R2/2012?

Surface Area Configuration is a security tool in SQLServer to disable/enable certain Server level (xpcmdshell, Remote DAC,SQLMail...)components. In SQLServer2005 you can directly open it under Configuration Tools but starting from SQL2008 and higher versions it is embedded in Facets.

What is Facet?

Facets is the property of SQL Server which the policy will consider managing. There are several facets on which policies could be implemented. For example, we will use the “Database Option” facet to implement a policy which will ensure that the AutoShrink option should be TRUE for all hosted databases on the server. Similarly, we will be creating policies on the Stored Procedure facet

Example: how to enable xpcmdshell through SAC in SQLserver2008?

Connect to SQLServer through SSMS
Right click on SQLServer
Click on Facets
On view Facets page select Surface Area Configuration from drop down list
Got to XPCmdShellEnabled property and type “True” and click ok.

How to repair Suspect Databse in SQLServer?

2012-12-08T18:01:00.001+05:30

How to repair Suspect Databse in SQLServer?

If you are supporting SQL Server you may see a database in Suspect mode in Production/Non Production, in suspect mode users canot connect to db and they canot perform any transaction.

In what circumstances a healthy databse goes in to suspect mode.

Data/Log file corruption triggers
if SQL cannot complete a rollback or rollforward operation
Power failure at SQL Server
If someone restarts sql services in middle of data loading process to tables through bulkinsert/SSIS

Now we will see how to bring back the database to normal state, first we need to put the database in “Emergency” mode and see any data pages/extents got corrupted by running dbcc checkdb command if see any repair with data loss and bring back it to normal state.

Example: “ABC” is our Database which is actually gone in to suspect mode.

Connect to SQL Server through SSMS and open Query Analyzer and execute following commands.

EXEC sp_resetstatus 'ABC'

ALTER DATABASE ABC SET EMERGENCY

DBCC checkdb('ABC')

ALTER DATABASE ABC SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('ABC', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE ABC SET MULTI_USER

How to start SQLServer in single user mode?

2012-12-07T17:47:00.001+05:30

How to start SQLServer in single user mode?

In some bad situations like whenever your master databse or other system databases got damaged you might need to start the SQL Server in single user mode in order to recover/restore.

Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role

How will I do that?

Click on Start button à Run
Type services.msc and then enter (this opens service applet)
Goto the SQLService and double click on it and stop it.

Type “-m” in Startup Parameters box

Now start the service

Connect to the sql server through SSMS and restore/recover Master database or any system database, after recovering don’t forget to remove “-m” from Startup Parameter.

How to apply Service Pack/Hotfix on SQL2008/R2 Cluster?

2012-12-07T17:14:00.000+05:30

How to apply Service Pack/Hotfix on SQL2008/R2 Cluster?

Applying ServicePack/Hotfix/Cummulatve on SQL2008/R2 cluster is even more simpler…… you should not directly install Service Packs on Production Environment first instead apply them on non Prod (DEV/SIT/UAT) and wait for few days and if you don’t find any errors then apply it on Prod.

Unlike with SQL2005 cluster, down time to apply patches is very minimal with SQL2008/R2 clusters because MS has introduced a new concept called Rolling Upgrade

So what is this Rolling Upgrade? During a rolling upgrade the changes are first applied to the passive node of a failover cluster. Then the SQL Server instance is failed over to the updated node. The upgrade is then applied to the remaining nodes. The total outage time for the SQL Server instance is the amount of time that the SQL Server instance takes to fail over to the updated node and apply the metadata changes for the update

Basic funda in applying patches on SQL2008 or higher is, connect to a Passive node and make sure that it is not hosting any SQL Instance at that moment and apply patch on it and reboot it after reboot move the SQL Instances to Passive node, once moved Passive become Active and Active become Passive, now connect to Passive(Earlier it was Active) and apply the patch and reboot it that’s it….

Anyone who has a very little knowledge on cluster can easily apply service packs if they follow below instructions.

Case-1

We have one SQL instance running from Node-A which is actually a two node cluster, that means we call this cluster as Active –Passive cluster, in our example Node-A is active node and Node-B is Passive node.

Step1:

First you need to take System Databases and User Databases backups and keep them at safer place.

Step2:

Download appropriate Service Pack/Hotfix/Cummulative Update on each cluster node(A/B).

Step3:

Connect to Passive node that means B node in our example and double click on service pack executable file, after successfully applying patch then reboot B node.

Step4:

After rebooting B node move the Instance to B node and verify the SQL Version by running Select @@version à this should show the latest patch version

Step5:

Connect to A node now and double click on service pack executable file, after successfully applying reboot A node.

Step5:

After rebooting A node, move SQL instance from B node to A node and then check the version by running Select @@version à this should show the latest patch version

Case-2

We have two SQL instances, SQLInstanace-1 is running from Node-A and SQLInstanace-2 is running from B node, that means we call this cluster as Active –Active cluster, in our example Node-A is active node and Node-B is also Active node.

Step1:

First you need to take System Databases and User Databases backups and keep them at safer place.

Step2:

Download appropriate Service Pack/Hotfix/Cummulative Update on each cluster node(A/B).

Step3:

Move SQLInstance-2 from B node to A node and connect to B node(now it is not hosting any sql)

and double click on service pack executable file. After successfully applying patch reboot B node.

Step4:

After rebooting B node move the SQLInstanace-1 and SQLInstance-2 from A node to B node, verify the SQL Version by running Select @@version à this should show the latest patch version

Step5:

Now Connect to A node (now it is not hosting any sql) and double click on service pack executable file. After successfully applying patch reboot A node.

Step6:

After rebooting A node, move SQLInstance-1 and move SQLInstance-2 from B node to A node and then check the version by running Select @@version à this should show the latest patch version and finally move the SQLInstance-2 from A to B node.

How to apply Service Pack/Hotfix on SQL2005 Cluster?

2012-12-05T19:47:00.001+05:30

How to apply Service Pack/Hotfix on SQL2005 Cluster?

Applying ServicePack/Hotfix/Cummulatve on SQL2005 cluster is simple…… You should not directly install Service Packs on Production Environment first instead apply them on non Prod (DEV/SIT/UAT) and wait for few days and if you don’t see any issues/errors then apply it on Prod.

Anyone who has a very little knowledge on cluster can easily apply service packs if they follow below instructions.

Case-1

Step1:

First you need to take System Databases and User Databases backups and keep them at safer place.

Step2:

Download appropriate Service Pack/Hotfix/Cummulative Update on each cluster node(A/B).

Step3:

Connect to Active node that means A node in our example and double click on service pack executable file, in this process installer first applies patch on Passive node (B node) after that it will apply patch on A node. After successfully applying patch, move SQL Instance from A node to B node and then reboot A node.

Step4:

After rebooting A node move the Instance back to A node and verify the SQL Version by running Select @@version à this should show the latest patch version

Step5:

Connect to B node and double click on service pack executable file, this patch is cluster aware then it will not pickup SQL Instance/Tools in that case you can simply close that otherwise if it pickup like SQLTools/Integration Service then apply it and finally reboot B node.

Step5:

After rebooting B node, move SQL instance from A node to B node and then check the version by running Select @@version à this should show the latest patch version

Case-2

Step1:

First you need to take System Databases and User Databases backups and keep them at safer place.

Step2:

Download appropriate Service Pack/Hotfix/Cummulative Update on each cluster node(A/B).

Step3:

Connect to A node in our example and double click on service pack executable file, in this process installer first applies patch on Passive node (B node because for SQLInstance-1 B node is passive) after that it will apply patch on A node. After successfully applying patch, move SQL Instance-1 from A node to B node and then reboot A node.

Step4:

After rebooting A node move the SQLInstanace-1 back to A node and verify the SQL Version by running Select @@version à this should show the latest patch version

Step5:

Connect to B node and double click on service pack executable file, in this process installer first applies patch on Passive node (A node because for SQLInstance-2 A node is passive) related to SQLInstance-2 after that it will apply patch on B node. After successfully applying patch, move SQL Instance-2 from B node to A node and then reboot B node.

Step5:

After rebooting B node, move SQLInstance-2 from A node to B node and then check the version by running Select @@version à this should show the latest patch version

Basic SQL Commands for beginers

2012-12-03T17:20:00.004+05:30

Basic SQL Commands for beginers:

Following are useful basic sql syntaxes for freshers.

SQL server is case insensitive, that means - “Create” and “CREATE” have same meaning.

SQL SELECT Statement: