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