SQL Transaction Log is full Error.

Issue: Transaction log is full error when users are running any transaction on sql server.

When does this error comes up? When user is trying to run transaction and there is not space on transaction log file you get 'Log Is Full' error 9002.

How to Resolve this:

1. Shrink Transaction log file because that's the quickest way to reduce size of log file.
2. Take log backup.
3. Make sure Log file's initial size is not too high. Sometimes Initial size of log file increased so much that we cant even shrink it. Try to change initial size of log file by using following command:

         ALTER DATABASE DB_NAME MODIFY FILE
                                       (NAME = 'Logical_log_file_name', SIZE = 200)  

    4. Then shrink log file again.
    5.  If you dont see any changes in log file size then last thing you do is to change your database's recovery model to simple and then bring it back to full.
    6. This will solve your Log Is Full error message.

If above process doesnt work, then try following. ( If recovery model is full )

1. Change database recovery model to simple.
2. Then again change it back to full.
3. Shrink database and you should be fine.

How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005, and Microsoft SQL Server 2008, on different servers.

To transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B, follow these steps:

To transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B, follow these steps:

1. On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.
2. Open a new Query Editor window, and then run the following script.

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
     DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
   DECLARE @tempint int
   DECLARE @firstint int
   DECLARE @secondint int
   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
   SELECT @firstint = FLOOR(@tempint/16)
   SELECT @secondint = @tempint - (@firstint*16)
   SELECT @charvalue = @charvalue +
       SUBSTRING(@hexstring, @firstint+1, 1) +
       SUBSTRING(@hexstring, @secondint+1, 1)
   SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
     DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
    DECLARE login_curs CURSOR FOR

       SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
      DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin IF (@@fetch_status = -1)
BEGIN
      PRINT 'No login(s) found.'
     CLOSE login_curs
     DEALLOCATE login_curs
     RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        PRINT ''
        SET @tmpstr = '-- Login: ' + @name
        PRINT @tmpstr
        IF (@type IN ( 'G', 'U'))
        BEGIN -- NT authenticated account/group

           SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
           END
           ELSE BEGIN -- SQL Server authentication
                  -- obtain password and sid
                 SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
                EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

               -- obtain password policy state

               SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

              SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

                        SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

                 IF ( @is_policy_checked IS NOT NULL )
                 BEGIN
                      SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
                 END
                 IF ( @is_expiration_checked IS NOT NULL )
                 BEGIN
                     SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
                 END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
        SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
        SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
        SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

3. Run the following statement.
 
EXEC sp_help_revlogin

The output script that is generated by the sp_help_revlogin stored procedure is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

4. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

Important Before you go to step 5, review the information in the "Remarks" section.

5.  Open a new Query Editor window, and then run the output script that is generated in step 3.



Link: http://support.microsoft.com/kb/918992

Kill All Database Connections to a SQL Server Database

Everyday, when i have to refresh databases I have to run sp_who2 and see if there is any active connection to database. It's painful process of checking each connection and killing each active process id. After you kill all active connection, you run sp_who2 again and you find few more connection. If you have to kill 3-4 connection, then it's ok to kill one by one but when you have to kill over 10 active connection this process is painful.
After searching online, I have found really good article and would like to share with everyone.

Run Following code after changing your db name

-- Create the sql to kill the active database connections
declare @execSql varchar(1000), @databaseName varchar(100)
-- Set the database name for which to kill the connections
set @databaseName = 'myDatabase'
set @execSql = ''
select  @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from    master.dbo.sysprocesses
where   db_name(dbid) = @databaseName
     and
     DBID <> 0
     and
     spid <> @@spid
exec(@execSql)

Here I have found even smaller version of this process.

alter database dbName set single_user with rollback immediate 
alter database dbName set multi_user with rollback immediate 

I hope it will help many dba's like me. If you have something better to share please post your comment here.

Link: http://blog.tech-cats.com/2008/01/kill-all-database-connections-to-sql.html

What is CXPackets Wait Type?

 

CXPACKET waits occur when a parallel query is executed and one or more threads have to wait on one of the other threads to complete.  The time spent in the waiting threads of a parallel query is the time that is measured by CXPACKET.

You can get CXPACKET wait when you run sys.dm_os_wait_stats. 

Why must parallel queries wait?

SQL Server will parallelize a query by dividing each operation into equal sized sets for processing.  These sets are then split up into multiple threads targeting specific processors.  This allows each thread to be executed in parallel by each of the processors.  Even with SQL Server splitting up the work into equal sized pieces, it is still impossible for all of the threads to complete at exactly the same time to prevent one or more of the threads from experiencing CXPACKET waits.  In short, if you have parallel query execution you will have CXPACKET waits.  

 Should I be concerned about CXPACKET waits?

This is not something to be concerned about until the CXPACKET waits become excessive.  Once CXPACKET waits are excessive, you know you have some performance tuning that needs to be performed on a specific query or the whole SQL Server.

CXPACKET waits are only a symptom and not an actual problem.  If you are experiencing high CXPACKET waits, you need to find out why the SQL Server engine divided a query into equal size sets that don’t get processed within a relatively equal time period.  This can be due to any of the following:

• Out of data statistics causing SQL Server to incorrectly divide the query into equal sized sets
• Fragmented indexes causing slower IO speeds that impact one thread over the others
• Client applications not efficiently processing result sets
• Hyper-Threading that causes SQL Server to process threads on hyper-threaded cores instead of only physical cores
• CPU pressure
• Memory pressure
• Incorrectly configured ‘max degree of parallelism’ server option
• Incorrectly configure ‘cost threshold for parallelism’ server option

What should I do about high CXPACKET waits?

As with everything dealing with SQL Server performance, the answer is always the same, it depends.  Now that we have the standard ‘it depends’ answer out of the way, let me give you and easy for me answer:

• Make sure that ‘auto create statistics’ and ‘auto update statistics’ is enabled all all databases
• Rebuild all indexes
• Perform an ‘UPDATE STATISTICS’ on all stats objects with the FULLSCAN
• Hyper-Threading should be disable
• ‘max degree of parallelism’ should not be set to a number greater than the number of physical processor cores
• ‘cost threshold for parallelism’ should not be set too low

You can use MAXDOP option to avoid while executing query using all cpu. Using MAXDOP you can specify exactly how many CPU query will use to perform execution. MAXDOP 0 means use as many CPU as available. 

Example:

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY ProductID
OPTION (MAXDOP 1)

There is a lot of misunderstanding about CXPACKET. CXPACKET isn't the cause of your problems, it is a side effect. Just because you see a lot of CXPACKET waits doesn't mean there's a problem with the query, it means that there is a problem somewhere else too.

Link: http://sqlserverperformance.idera.com/network-performance/cxpacket

Rebuilding the master Database

The master database can be rebuilt using the Rebuild Master utility if:

• A current backup of master is not available.
  
• The backup cannot be restored because an instance of Microsoft® SQL Server™ cannot start due to severe damage to master.

When master has been rebuilt, a current backup of master can be restored or the user databases, backup devices, SQL Server logins, and so on can be re-created using SQL Server Enterprise Manager or the original scripts used to create those entries.

Important  The Rebuild Master utility rebuilds master completely. Because the msdb and model system databases are rebuilt as well, it will normally be necessary to restore backups of those databases.

The general steps required to rebuild master completely if no backup is available are:

• Run the Rebuild Master utility to rebuild the system databases.
  Important  The compact disc or shared network directory containing the SQL Server installation software is required to rebuild the master database.
• Re-create any necessary backup devices.
  
• Reimplement security operations.
  
• Restore msdb if necessary.
  
• Restore model if necessary.
  
• Restore distribution if necessary.
  
• Restore or attach user databases if necessary.

When master has been re-created and any changes have been reapplied, back up master immediately.

To rebuild the master database

To rebuild the system databases you need to run the setup command from the Command prompt and follow the following procedure:

1. Click Start, click Run, type cmd, and then click OK.
2. Run the following command to rebuild the system databases:

start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=Instance_Name REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=StrongPassword

For example:

start /wait D:\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=p@ssw0rd

You don't need your DVD anymore!

A new feature different from previous versions of SQL Server is that the system databases files we use to rebuild the current system databases do not come from the original installation media. So where do they come from? The come from the installation folder on your local computer in BINN\templates. On my machine where I installed SQL Server 2008 RTM on a clean machine with the default instance, this full path was at:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Templates

In this directory you will find master, model, and msdb database and log files that were copied from your installation source as part of setup.

So the logic in rebuilding the system databases is to get these files and copy them into your DATA directory to obtain your new system databases. This is a very nice enhancements for rebuilding system databases because you are not required to have your DVD or original install media.

Link : http://msdn.microsoft.com/en-us/library/aa213831%28v=sql.80%29.aspx

Optimizing Tempdb Performance

The size and physical placement of the tempdb database can affect the performance of a system.

tempdb Size and Placement Recommendations

---

To achieve optimal tempdb performance, we recommend the following configuration for tempdb in a production environment:

• Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small.
• Allow for tempdb files to automatically grow as required. This allows for the file to grow until the disk is full.
• Set the file growth increment to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small, compared to the amount of data that is being written to tempdb, tempdb may have to constantly expand. This will affect performance.
• Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.
• Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.
• Make each data file the same size; this allows for optimal proportional-fill performance.
• Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
• Put the tempdb database on disks that differ from those that are used by user databases.

Modifying tempdb Size and Growth Parameters

---

ALTER DATABASE Tempdb MODIFY FILE
   (NAME = 'Tempdev', SIZE = 200)

Viewing tempdb Size and Growth Parameters

---

Running the following query.

 

SELECT 
    name AS FileName, 
    size*1.0/128 AS FileSizeinMB,
    CASE max_size 
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' = 
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0 
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

Link: http://msdn.microsoft.com/en-us/library/ms175527.aspx

How to save execution plan in XML format

Use SQL Server Management Studio to save execution plans as an XML file, and to open them for viewing.

To save an execution plan by using SQL Server Management Studio options

1. Generate either an estimated execution plan or an actual execution plan by using Management Studio.
2. In the Execution plan tab of the results pane, right-click the graphical execution plan, and choose Save Execution Plan As.
   As an alternative, you can also choose Save Execution Plan As on the File menu.
3. In the Save As dialog box, make sure that the Save as type is set to Execution Plan Files (*.sqlplan).
4. In the File name box provide a name, in the format <name>.sqlplan, and then click Save.

To open a saved XML query plan in SQL Server Management Studio.

1. In SQL Server Management Studio, on the File menu, choose Open, and then click File.
2. In the Open File dialog box, set Files of type to Execution Plan Files (*.sqlplan) to produce a filtered list of saved XML query plan files.
3. Select the XML query plan file that you want to view, and click Open.
   As an alternative, in Windows Explorer, double-click a file with extension .sqlplan. The plan opens in Management Studio.

How to: Display an Actual Execution Plan.
How to: Display the Estimated Execution Plan


Link Source: http://msdn.microsoft.com/en-us/library/ms190646(v=sql.90).aspx