Shrink the Unshrinkable SQL Transaction Log

There are many reason for not shrinking of Transaction log. Our production database backup was failing for last several weeks, so the backup process could not clear out the transaction log. Our approx 1 GB database grow up to 37.5 GB.

The SQL Server GUI for shrinking the database rendered no effect, and even using the DBCC SHRINKFILE command was not working.

The key, as explained by Pinal Dave, is to run the SHRINKFILE command twice,with an explicit backup log truncation in between both runs. This code here will get you up and running:

DBCC SHRINKFILE("DemoData_Log", 1)

BACKUP LOG DemoData WITH TRUNCATE_ONLY

DBCC SHRINKFILE("DemoData_Log", 1)

Query to get SQL Server name, Version, Edition, Authentication Mode, No of CPU and RAM

Below query returns the SQL Server name, Version, Edition, Authentication Mode, No of CPU and RAM available in system

SELECT SERVERPROPERTY('ServerName') AS [SQLServer],
SERVERPROPERTY('ProductVersion') AS [VersionBuild], 
SERVERPROPERTY('ProductLevel') AS [Product],
SERVERPROPERTY ('Edition') AS [Edition],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthOnly], 
SERVERPROPERTY('IsClustered') AS [IsClustered], 
[cpu_count] AS [CPUs], 
round(cast([physical_memory_in_bytes]/1048576 as real)/1024,2) AS [RAM (GB)]FROM [sys].[dm_os_sys_info]

Backup types in sql server

In SQL Server 2008 or later following are the backup types:


Full backup
Differential backup
Partial backup
Differential partial backup
File backup
Differential file backups
Transaction Log Backups
Copy-Only Backups

Find out which host / IP address and Program deleted the records from SQL Server

You will not be able to know from which system records are deleted directly. But you can get this information if you add a trigger and a logging table. Use below query to implement a logging table and a trigger:

CREATE TABLE dbo.DeleteLogTable(   PK_From_Table INT,  Host NVARCHAR(128),  IP VARCHAR(48),  Program NVARCHAR(128),  Deleted_When DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);GO

CREATE TRIGGER dbo.LogDelete_TableON dbo.TableNameFOR DELETEASBEGIN  SET NOCOUNT ON;  DECLARE @p NVARCHAR(128), @h NVARCHAR(128), @i VARCHAR(48);  SELECT @p = s.host_name, @h = host_name, @i = c.client_net_address    FROM sys.dm_exec_sessions AS s    INNER JOIN sys.dm_exec_connections AS c    ON s.session_id = c.session_id    WHERE s.session_id = @@SPID;  INSERT dbo.DeleteLogTable(PK_From_Table, Program, Host, IP)    SELECT PK_Column, @p, @h, @i    FROM deleted;END
GO

Script to list dependencies on database objects

Use below query to get a List of Database Dependencies Object: 

SELECT OBJECT_NAME(FKC.PARENT_OBJECT_ID) Foreign_Key_Table,
C2.NAME Foreign_Key_Field,
OBJECT_NAME(FKC.REFERENCED_OBJECT_ID) Primary_Key_Table,
C3.NAME Primary_Key_Field,
OBJECT_NAME(FKC.CONSTRAINT_OBJECT_ID) Relation_Name
FROM  SYS.FOREIGN_KEY_COLUMNS FKC
JOIN  SYS.COLUMNS C2 ON FKC.PARENT_OBJECT_ID = C2.OBJECT_ID
            AND FKC.PARENT_COLUMN_ID = C2.COLUMN_ID
JOIN  SYS.COLUMNS C3 ON FKC.REFERENCED_OBJECT_ID = C3.OBJECT_ID
            AND FKC.REFERENCED_COLUMN_ID = C3.COLUMN_ID
ORDER BY Foreign_Key_Table
GO

Msg 306, Level 16, State 2, Line 23

You will receive below error when you try to ORDER BY or GROUP BY a column with data type Image, Text, Ntext.

Msg 306, Level 16, State 2, Line 23
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Image – Data type is used for storing variable length binary data.
Ntext – Data type is used for storing variable length of Unicode data.
Text – Data types is used for storing variable length non-Unicode data.

The image, text and ntext, data type columns cannot be compared or sorted, except when using IS NULL or LIKE operator.

The solution for this type of error is to convert Image column to VarBinary, Text column to VarChar and Ntext column to nVarChar when you use these columns in ORDER BY or GROUP BY clause. 

Example for GROUP BY clause:
SELECT * FROM dbo.CustomerMaster GROUP BY CustomerType;

Modify the query as 
SELECT * FROM dbo.CustomerMaster  GROUP BY CAST (CustomerType as nvarchar)

Example for ORDER BY clause:
SELECT * FROM dbo.CustomerMaster ORDER BY CustomerName;

Modify the query as 
SELECT * FROM dbo.CustomerMaster ORDER BY CAST(CustomerName as nVarChar)

Estimated completion time for Backup and Restore

Use below script to get the estimated time while Backup or Restore database:


SELECT session_id, start_time, status, command, percent_complete, estimated_completion_time, 
estimated_completion_time /60/1000 as estimate_completion_minutes,
DATEADD(n,(estimated_completion_time /60/1000),GETDATE()) as estimated_completion_time
FROM sys.dm_exec_requests where command = 'RESTORE DATABASE' OR command = 'BACKUP DATABASE' 
GO

You can use above query for long running queries also. In where condition you had to replace the "Restore Command" or "Backup Database" with starting keyword from Query.

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

You may get the below error while executing sp_executesql:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

To solve this error add N before the query:

EXEC sp_executesql N'SELECT GetDate()'

Error: 5133 SQL Server while attaching the Database

Today, one of my support executive was trying to attach a database received from client in SQL SERVER 2008 R2. He had received only one file, DemoDB.mdf

Now when he was attaching that file, SQL server is giving below error:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'XXX-XX--XX'.  (Microsoft.SqlServer.Smo)


For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Directory lookup for the file "G:\SQLLOG\NAV_D_CQPA_370B_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft SQL Server, Error: 5133)
------------------------------
I checked the file path during attach. I found that as he does not have the log file. SQL was taking a random path which is not exist in the system. I removed the ldf file and the database is attached successfully.

MUST_CHANGE option cannot be used when CHECK_EXPIRATION is OFF.

Sometime you may get the below error while executing some script:

Msg 15099, Level 16, State 1, Line 1
The MUST_CHANGE option cannot be used when CHECK_EXPIRATION is OFF.

To solve this error, Add CHECK_EXPIRATION=ON TO the script or remove MUST_CHANGE from the script.


CREATE LOGIN [username] WITH PASSWORD=N'pa@Ssword' MUST_CHANGE, CHECK_EXPIRATION=ON

Shrink Database fails with Error: Backup, file manipulation operations

Sometimes you may get below error while trying to shrink their databases manually or using SSMS.

Executing the query "DBCC SHRINKDATABASE(N'DemoDate', 10, TR..." failed with the following error: "Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. 

Probable reason for above error:

While shrink Database backup is running. You cannot do below operation while doing backup:

• The file truncation phase of shrink database or shrink file. 

• File management operations such as the ALTER DATABASE statement with either ADD FILE or REMOVE FILE clauses. 

Please note that if backup is running and one of the above operations is attempted, then operation fails immediately. When user is trying to add or remove a file and if backups is started then backup will wait for a timeout period, then fail.

If user is trying to shrink database or file while a backup is running, the shrink stops without truncating the file. If backup started after shrink database operation then backup waits for shrink operation to complete.

The solution of this error is reissue the statement after the current backup or file manipulation operation is completed.

Msg 8992, Level 16, State 1, Line 1

Today I had got the some error in a database. I ran DBCC CheckDB and found below errors:


DBCC results for 'Data'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=399091823) of row (object_id=399091823,column_id=11) in sys.columns does not have a matching row (object_id=399091823) in sys.objects.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for 'sys.sysrowsetcolumns'.
There are 4119 rows in 34 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 480 rows in 7 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 515 rows in 13 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 4119 rows in 40 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 480 rows in 7 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 515 rows in 6 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 134 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 3337 rows in 69 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 41, index ID 1, partition ID 281474979397632, alloc unit ID 281474979397632 (type In-row data). Keys out of order on page (1:565), slots 9 and 10.
There are 3866 rows in 99 pages for object "sys.syscolpars".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.syscolpars' (object ID 41).
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 28 rows in 13 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 1878 rows in 52 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 2117 rows in 14 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 4438 rows in 725 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 17 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 1861 rows in 15 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 1135 rows in 21 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 3 rows in 1 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 96 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:1332) in object ID 1712985429, index ID 1, partition ID 72057594446741504, alloc unit ID 72057594164084736 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:1333) in object ID 1712985429, index ID 1, partition ID 72057594446741504, alloc unit ID 72057594164084736 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:1334) in object ID 1712985429, index ID 1, partition ID 72057594446741504, alloc unit ID 72057594164084736 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:1335) in object ID 1712985429, index ID 1, partition ID 72057594446741504, alloc unit ID 72057594164084736 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.

CHECKDB found 0 allocation errors and 6 consistency errors in database 'Data'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Here check DB found consistency errors on multiple system tables in user database. Unlike the report, which we generally see after running DBCC checkdb, this report didn’t show like

‘repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB’

or

‘repair_fast is the minimum repair level for the errors found by DBCC CHECKDB’

or

‘repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB’

None of the above solved the errors. Than I created a blank database and exported all database objects with data from old Database to the newly created database. 

Indexes that are not used

One of the main task of a database administrator is proper handling of Indexes. He will have check for the right indexes from time to time. Below query checks the database and returns the unused indexes.

SELECT o.name AS object_name, i.name AS index_name
   , i.type_desc, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates
FROM sys.indexes i
JOIN sys.objects o ON  i.object_id = o.object_id
LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
          AND    i.index_id = u.index_id
          AND    u.database_id = DB_ID()
WHERE    o.type <> 'S'    
and isnull(u.user_updates,0) > 0
and i.type_desc <> 'HEAP'
ORDER BY    (convert(decimal(19,4),ISNULL(u.user_seeks, 0)) + ISNULL(u.user_scans, 0) 
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc, o.name, i.name

The above query returns the indexes which are never used.

Truncate and Delete

Generally Delete command removes the rows from a table based on some where condition. whereas Truncate removes all the rows from a table.

Truncate

• Truncate is faster and uses fewer system and transaction log resources than Delete. 
• You cannot use  Truncate Table on a table referenced by a FOREIGN KEY constraint.
• Truncate is a DDL Command.
• Truncate resets the identity of the table.
• Truncate removes all the rows from a table, but the table structure, its columns, constraints, indexes and so on remains. The counter used by an identity for new rows is reset to the seed for the column.
• Truncate cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN … END TRANSACTION using T-SQL.
• Truncate removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

Delete

• Delete removes rows one at a time and records an entry in the transaction log for each deleted row.
• Delete does not reset the identity of the table.
• Delete is DML Command.
• Delete can be rolled back.
• Delete does not reset Identity property of the table.
• Delete can be used with or without a WHERE clause.
• Delete activates Triggers if defined on table.

Index was outside the bound of the array

Today I was trying to connect to SQL Server 2012 using SQL Server 2008 Management studio and management studio throws the below error. 

Then I tried the same with SQL Server 2005 Management studio to connect to SQL Server 2008 and also SQL Server 2012 and it returned the same error. It did not show any useful information under Technical details.

The main reason of above errors is SSMS is only backward compatible, it is not forward compatible. It means we can connect to lower version of SQL Server from higher version of SSMS but we cannot connect higher versoin SQL Server with lower version of SSMS.

The best way to upgrade all client tools to higher version and now you can connect to all version of SQL Server.

Add or Remove a node in SQLServer 2005 Failover Cluster

You need to follow some steps in case of adding or removing a node in a cluster or recover a failed node in a failover cluster environment. In case of node1 failure, the SQL Server failover cluster fails over to node2. Use Cluster administrator to evict node from cluster.

You may receive following error while adding or removing a node to the existing cluster.

Setup failed to start on the remote machine. Check the Task scheduler event log on the remote machine.

Error message from event log:-
"SQL Server Remote Setup .job" (setup.exe) 5/20/2011 10:15:00 PM ** ERROR ** 
Unable to start task.
The specific error is:
0×80070005: Access is denied. 

You might be connected via Remote Desktop and you may have some active remote desktop session. Please note that you must close all your remote desktop session sessions before proceeding to installation.

To add or remove a node you must be a local administration on all the nodes of the failover cluster.

Step 1. Add a node to the Cluster
• Assign the quorum
• Run Add node wizard in Custer administrator from new node
• Provide Cluster service account password to add the node
• On the New Node assign the clustered drives used by SQL Server and verify the node recognizes the cluster drives
• The drive where you install SQL Server should have same driver letter as other nodes

Step 2. Add or remove a node in SQL Server 2005 Cluster
• Login to the surviving Node or active node of the SQL Server Cluster
• Click Add or Remove Programs in Control Panel
• Select the Microsoft SQL Server 2005 and click change
• The SQL Server 2005 Installation Wizard starts
• On the Component Selection page, select the SQL Server 2005 instance name to change and click next
• On Feature Maintenance page select a Database Engine component to change and click next
• SQL Server 2005 System Configuration Checker starts
• On the Microsoft SQL 2005 Welcome screen click next
• The System Configuration Checker scans the existing configuration on the computer
• Review the warnings and error messages, correct the errors as needed and click next
• On the Change or Remove Instance page, click Maintain the Virtual Server.
• On the Cluster Node Configuration page select the node from the available nodes list and click add . To remove a node from the failover cluster, select the node and click remove.
• In Remote Account Information, provide the password of the user currently logged in(This user should be a local admin on all the nodes) and click next
• Enter the service account username/password for each listed service and click next.
• On Error and Usage Report page, do not select anything and click next
• On the Ready to Install page, click install
• On the Completing the Microsoft SQL Server Installation Wizard page, you can review the summary log by clicking on the link provided. To exit from Installation wizard click Finish.

Step 3.Install the Workstation Components and Service packs 
• Run the setup.exe from Installation CD or software dump
• Install only workstation components (note that these are not part of the cluster)
• All nodes in a failover cluster must be at same service pack version, install the service packs as same as servicing node or active node.

Ref:http://www.sqlserver-expert.com/2011/07/add-or-remove-node-in-sqlserver-2005.html

Calculate Indexes Size via dm_db_index_physical_stats

Sometimes we may want to know the space used by a index. We can use the sp_spaceused system stored procedure to measure how much space used by different objects.

This code allows you to list how many indexes are present on a table, their type and how big (kb and mb) they are.


SELECT [name],type_desc,space_used_in_kb = (page_count * 8.0),space_used_in_mb = (page_count * 8.0 / 1024.0) 
  FROM sys.indexes I JOIN sys.dm_db_index_physical_stats(db_id(),object_id('.'),null,null,null) P 
    ON I.[object_id] = P.[object_id] 
   AND I.[index_id] = P.[index_id]

Select Middle Record

We can use the SELECT TOP 1 query to find the First or the last record in a table data. From below query we can find the exactly middle record in a table data.

SELECT TOP 1 VchDt
  FROM (SELECT TOP 50 PERCENT VchDt 
          FROM SaleData WITH (NOLOCK)
         ORDER BY VchDt ASC) AS T1 
 ORDER BY 1 DESC

Change tables owner to dbo with sp_changeobjectowner

Sometimes we need to change all tables in the database to be owned by dbo for some maintenance purpose or to fix up some accidental errors. The following small SQL code snippet goes through all user tables in the database and changes their owner to dbo. It uses sp_changeobjectowner system stored procedure:

DECLARE tabcurs CURSOR

FOR

    SELECT 'SOMEOWNER.' + [name]

      FROM sysobjects

     WHERE xtype = 'u'

OPEN tabcurs

DECLARE @tname NVARCHAR(517)

FETCH NEXT FROM tabcurs INTO @tname

WHILE @@fetch_status = 0

BEGIN

    EXEC sp_changeobjectowner @tname, 'dbo'

    FETCH NEXT FROM tabcurs INTO @tname

END

CLOSE tabcurs

DEALLOCATE tabcurs

Function Vs Stored Procedure

Both Function and Stored Procedure (SP) are pre compiled SQL statements but there some basic differences between them: 

• Function must return a value whereas SP may or may not return a value.
• A function can be used in a Join whereas SP can't be used in a Join. 
• Functions can return a table whereas SP can create a table but can't return table.
• XML and output parameters can't be passed to functions whereas it can be with SP. 
• Transaction related statement can be handled in SP whereas it can't be in function.
• We can have extended SP but not extended functions.
• SP can call a function or another stored proc similarly a function can call another function and a stored proc. The catch with function is that no user defined SP can be called. Only extended/system defined procedures can be called.
• SP can be called independently using exec keyword whereas function are called using select statements.
• SP can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this

Disconnect all users from a database

We can disconnect all users from a database by just killing all the process connected to the database. Check below stored procedure to do this:


CREATE PROCEDURE dbo.KillAllConnection
@DB_Name SYSNAME
AS

BEGIN

DECLARE @spid INT
DECLARE @strSQL NVARCHAR(255)

DECLARE processes CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE dbid = DB_ID(@DB_Name) AND spid != @@SPID

OPEN processes
FETCH NEXT FROM processes INTO @spid

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'KILLing '+RTRIM(@spid)
SET @strSQL = 'KILL '+RTRIM(@spid)
EXEC(@strSQL)
FETCH NEXT FROM processes INTO @spid

END

CLOSE processes
DEALLOCATE processes

END

GO

Script to retrieve security information

Query to get server level logins and role assigned:
SELECT a.name as LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,
CASE WHEN b.sysadmin = 1 THEN 'sysadmin'
          WHEN b.securityadmin=1 THEN 'securityadmin'
          WHEN b.serveradmin=1 THEN 'serveradmin'
          WHEN b.setupadmin=1 THEN 'setupadmin'
          WHEN b.processadmin=1 THEN 'processadmin'
          WHEN b.diskadmin=1 THEN 'diskadmin'
          WHEN b.dbcreator=1 THEN 'dbcreator'
          WHEN b.bulkadmin=1 THEN 'bulkadmin'
          ELSE 'Public' END AS 'ServerRole'
FROM sys.server_principals a  JOIN master..syslogins b ON a.sid=b.sid WHERE a.type  <> 'R' AND a.name NOT LIKE '##%'

Query to get database users and roles assigned:

DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName

Query to get Object level permission for user databases:

DECLARE @Obj_sql VARCHAR(2000)
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200))
SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '
INSERT @Obj_table
EXEC sp_msforeachdb @command1=@Obj_sql
SELECT * FROM @Obj_table

Interactive Services Dialog Detection window for SQL Agent

Today I got the below error on my client machine: 

"Interactive Services Dialog Detection"

The solution of above problem is desable the interactive service dialog detection window. Follow below steps:

• Go to Start - Run and type services.msc to Starts, stops, and configures Windows services.
• Select SQL Server Agent--> Right click on properties
• Select the logon tab
• Uncheck the “Allow Service to interact with Desktop”
• Restart the SQL Server Agent

Cannot connect to WMI provider. You do not have permission or the server is unreachable.

Today when I was trying to open SQL Server 2012 configuration manager, I got below error: 

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. 
Invalid class [0x80041010] 

I follow below steps to solve above error:

1. First I search for the file "sqlmgmproviderxpsp2up.mof"  (generally it is located under shared folder under version folder). and open command prompt and run below command:
mofcomp C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof
Output
Microsoft (R) MOF Compiler Version 6.1.7600.16385
Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
Parsing MOF file: C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmp
roviderxpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository...
Done!
2. After it is stored in the repository, 
3. Now open SQL Configuration manager, it will work.

Retrieve SQL Server Service Account

This script will pull the name of the SQL Server Service account out of the registry using an undocumented stored procedure. 


-- Declare a variable to hold the value 
DECLARE @serviceaccount varchar(100)

-- Retrieve the Service account from registry 
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'

--Display the Service Account 
SELECT @Serviceaccount

Row count for all the tables in all the database

Use below query to get the row count for all tables in all the databases:


SET NOCOUNT ON
DECLARE @strQuery VARCHAR(4000)
DECLARE @strTable TABLE (DBName VARCHAR(200), TableName VARCHAR(300), COUNT INT)
SET @strQuery='SELECT  ''?'',sysobjects.Name, sysindexes.Rows
FROM   ?..sysobjects INNER JOIN ?..sysindexes ON sysobjects.id = sysindexes.id
WHERE  type = ''U''  AND sysindexes.IndId < 2 order by sysobjects.Name'
INSERT @strTable
EXEC sp_msforeachdb @strQuery
SELECT * FROM @strTable WHERE DBName <> 'tempdb' ORDER BY DBName

CHECK_EXPIRATION option cannot be used when CHECK_POLICY is OFF

Sometime you may get the below error while executing some script:

Msg 15122, Level 16, State 1, Line 1The CHECK_EXPIRATION option cannot be used when CHECK_POLICY is OFF.

To solve this error, Turn On CHECK_POLICY in the script or remove CHECK_EXPIRATION from the script.

CREATE LOGIN [username] WITH PASSWORD=N'pa@Ssword' , CHECK_EXPIRATION=ON, CHECK_POLICY=ON

Unable to start execution of step 1 (reason: line(1): Syntax error)

You may receive the error “Unable to start execution of step 1 (reason: line(1): Syntax error” under below circumstances in SQL Server 2005/2008. 

1. Created a new maintenance plan 
2. Modified the path in existing maintenance plan 
3. Existing database fails due after File servers IP Change..etc 

Error Message:
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details:
Additional Information:
Job ‘ Log_Backup’ failed. (SqlManagerUI) 
Additional information from Log File Viewer:
Executing the query "EXECUTE master.dbo.xp_create_subdir N'\\\\backup\\t..." failed with the following error:"Cannot open backup device \\backup\testdb.trn'. Operating system error 53(The network path was not found.). BACKUP LOG is terminating abnormally.” Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. 
Solution: 
1. Check the login used to schedule the backup has access to backup device path and read/write permissions.

Run below t-sql from New Query to check the permission.
EXECUTE master.dbo.xp_create_subdir N'\\backup \testdb' 

2. If your Backup Maintenance plan suddenly stopped working after Network maintenance then it may be related to name resolution to File Server Name or IP Change or Alias name that you are using in the backup.

Check whether you are able to resolve correct name, IP address using nslookup or check with your Windows team to fix name resolution.
For ex:- nslookup "server name or IP address or File server name"

3. If you still have issues 

• Go to SQL Server Agent\Jobs and click properties 
• Go to Steps section 
• Highlight the Sub plan and click Edit 
• In the “ package section” add a Backslash to the beginning of the path listed 

Script to fetch the code that is executed by an SPID

Use below TSQL Script to fetch the code that is executed by a SPID:


DECLARE @sql_handle AS VARBINARY (1000)

SELECT @sql_handle = SQL_HANDLE
FROM   sys.sysprocesses WITH (NOLOCK)
WHERE  spid = 56; --To Do: Update with SPID OF the Process
  
SELECT * 

FROM   sys.dm_exec_sql_text (@sql_handle)

Some stored procedure related to security

Sp_addrolemember : Adds login to database role in the current database
Syntax : sp_addrolemember 'role_name','login_name'
eg : sp_addrolemember 'db_owner', 'akl'

Sp_droprolemember : Removes server role of the specified login
Syntax sp_droprolemember role_name, login_name
eg : sp_droprolemember 'db_owner', 'akl'

Sp_changedbowner : changes owner of the current database
Syntax : sp_changedbowner 'login name'
eg : sp_changedbowner 'akl'

Sp_addlogin : Create a new login in SQL Server Instance
Syntax : sp_addlogin login_name, password, [def db], [def language]
eg : sp_addlogin 'akl', 'kumar', master

Sp_adduser : Creates a new user and mapped to login in the current database.
Syntax : sp_adduser 'login_name','user_name_in_db' 
eg  : use akldb;GO; sp_adduser 'akl','akl' 

Sp_defaultdb : changes the default database for any login
Syntax : sp_defaultdb login_name, database
eg : sp_defaultdb 'akl','master'

Sp_dropuser : Removes user from the current database
Syntax : sp_dropuser 'user_name
eg : sp_dropuser 'akl'

Sp_droplogin / drop login : Removes login from SQL Server Instance
Syntax : sp_droplogin loginname
eg : sp_droplogin 'akl' (or) drop login akl

TempDb Tips

TempDB must be optimized after some time interval for every SQL Server. There are some techniques to optimize TempDB. Here are some suggestions and techniques found from different sources:

Place TempDB files on the fastest drive available (beware of SSD…great performance, but might burn it out).

Your system may work better with a different TempDB file count. Monitor different configurations for performance.

Create separate data files for each CPU/Core. Make the files the same size, even if they are on the same drive.

If possible, isolate TempDB on a separate disk from other databases.

You only need one TempDB log file, but it should also be on the fastest drive available

Make sure you have enough space in your TempDB files so they do not have to autogrow.

TempDB has some optimization techniques. It caches space for tables that may be used again. For this reason you may see the data allocation of TempDB remain long after your query has completed. 

Be sure to commit or roll back your transactions. If you don’t, any space allocated in TempDB as part of that transaction may not be released.

Remember, if you decide to change the files and/or the size of your TempDB files you will need to stop and restart SQL Server in order for your changes to take place.