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.