How to Troubleshoot Tempdb issues without restarting SQLServices

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?

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?

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?

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:

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

How to list traces and stop from SQL Server?

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?

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

1. Attach/Dettach Method
2. Take Full backup and Restore
3. Alter Database command within same instance
4. 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^nd  is for moving to other instance and 3^rd method is only applicable within same instance

Attach/Dettach:

This is the simple and easy method

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

Take Full backup and Restore:

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

Alter Database command:

1. Run following command

USE master;

GO

ALTER DATABASE ABC

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

GO

ALTER DATABASE ABC

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

GO

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?

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:

1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
3. In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab.
4. In the Existing parameters box, select the –d parameter to move the master data file. Click Update to save the change.
5. 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

6. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.
7. Move the master.mdf and mastlog.ldf files to the new location.
8. 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

1. ALTER DATABASE MSDB/Model MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
2. Stop the instance of SQL Server or shut down the system to perform maintenance. For more information, see Stopping Services.      
3. Move the file or files to the new location.
4. 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;

GO

ALTER DATABASE tempdb

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

GO

ALTER DATABASE tempdb

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

GO

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