How to prevent SSMS from commit automatically

 By default, SSMS perform auto commit after each DML and/or DDL statement. So the question is, what shall we do to prevent SSMS perform auto commit and control when to commit or rollback?

 

                Here is the answer. As shown below, From SSMS open Toolsàoptions

Then from left tree open Query ExecutionàSQL SERVERàANSI

Then from right select “SET IMPLICIT_TRANSACTIONS” THEN CLICK ok. Close your Query Analyzer session. Open new one and enjoy controlling when to commit or rollback.

 

 

 

 

Be careful and don’t worry

Be careful when to use commit or rollback. If you use commit, all your session transactions will be committed from opening your session or from last time you used commit. And if you use rollback, all your session transactions will be rolled back from opening your session or from last time you used rollback.

Don’t worry if you forgot using commit all rollback. If so, and you gone to close SSMS or your session, SSMS will appear the below message to ask you if you want to commit all your session transactions or rollback all of them.  

 

 

How to add a database file to a mirrored SQL Server database when database files are quite different (different file paths)

1-      check the mirroring configuration and partner status

SELECT (SELECT DB_NAME(DB_ID('DB'))) AS DBName,database_id,mirroring_state_desc,

mirroring_role_desc,mirroring_partner_name

,mirroring_partner_instance

FROM sys.database_mirroring

WHERE database_id=DB_ID(' DB')

 

 

2-      Disable any SQL Server backup jobs to reduce any futher complexity. Otherwise, if any log backup occurs in between you will need to restore all log backups on the mirrored server before establishing the database mirror.

 

3-      Remove the mirror partner to initiate this file creation process

ALTER DATABASE DB Name SET PARTNER OFF

 

 

4-      Check the mirroring configuration after removing the mirror with step 1 code, the output should be like the screenshot below.

 

 

5-      Create your database file on the principal server.

 

6-      Run a log backup.

 

7-      Check whether your newly created file is captured in the log backup. Run the RESTORE FILELISTONLY statement to get this info.

 

RESTORE FILELISTONLY FROM DISK = 'D:\Lbak.trn'

                                                                                                             

 

8-      Restore it on the mirrored server.

ESTORE DATABASE DB

FROM DISK = 'D:\Lbak.trn'

 WITH NORECOVERY,

   MOVE 'Logical file name' TO 'Mirrored Physical File path'

,  MOVE 'Logical file name' TO 'Mirrored Physical File path'

,  MOVE 'Logical file name' TO 'Mirrored Physical File path'