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'

How to Find Out Total Disk Free Space For SQL Server DBs disks using T-SQL

How to Find Out Total Disk Space For DBs disks using T-SQL for SQL Sever with Automatic Version Detiction



 

if
left(cast(serverproperty('productversion') as varchar) , 4) in('10.5','11.0','12.0','13.0')

begin

--2008 R2 SP1

select left(cast(serverproperty('productversion') as varchar) , 4) as SQLServerVersion,A.name, volume_mount_point

-- , (cast(total_bytes as decimal(19,2))/1024/1024/1024)as total_Disk_Capacity_GB --original

, cast((cast(total_bytes as decimal(19,2))/1024/1024/1024)as decimal(19,2)) as total_Disk_Capacity_GB --rounded

-- , (cast(available_bytes as decimal(19,2))/1024/1024/1024)as available_Space_GB --original

, cast((cast(available_bytes as decimal(19,2))/1024/1024/1024)as decimal(19,2)) as available_Space_GB --rounded

from sys.master_files A

CROSS APPLY sys.dm_os_volume_stats(A.database_id, A.file_id) --all DBs files

--CROSS APPLY sys.dm_os_volume_stats(db_id(A.name), A.file_id) --for the current DBs

where (A.file_id=1 or A.file_id=2)and A.database_id >4

end

else


begin

--2008 or earlier

IF OBJECT_ID('tempdb..#TMP_DRIVES') is not Null

DROP TABLE #TMP_DRIVES

CREATE TABLE #TMP_DRIVES (DRIVE CHAR(1) NOT NULL

,MBFREE INT NOT NULL)

INSERT INTO #TMP_DRIVES

EXEC xp_fixeddrives





select
left(cast(serverproperty('productversion') as varchar) , 4)as SQLServerVersion

,DRIVE as Volum_mount_drive

,convert(decimal(19,2),(cast(MBFREE as decimal(19,2))/1024 )) as Available_Space_GB

--,name

from #TMP_DRIVES a--, sys.master_files b

where DRIVE in(select LEFT(physical_name,1) from sys.master_files where database_id>4)

drop table #TMP_DRIVES

end

Running deployed SSIS package from SQL Server job generates an error

If you Do the following Scenario:-

1- Developed an SSIS package in 64Bit with Attunity Connector to connect to Oracle DB. And Configure the package property named "Run64BitRuntime" to fales, to run in 32Bit at runtime.

2- Deploy it to Integration Server.

3- Create an SQL Server Job to schedule running it.

4- Got the following error when running the job "Executed as user: ???. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4000.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  ??? "running job time"  Error: ??? "running job date and time"     Code: 0xC0014005     Source:       Description: The connection type "MSORA" specified for connection manager "??? Name of your attunity connector" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.  End Error  Error: ??? "End job date and time"     Code: 0xC0010018     Source:       Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts">  <DTS:Property DTS:Name="DelayValidation">0</DTS:Property>  <DTS:Property DTS:Name="ObjectName">Attunity Connector Name</DTS:Property>  <DTS:Property DTS:Name="DTSID">???" from node "DTS:ConnectionManager".  End Error  Could not load package "??? Name of your SSIS Package" because of error 0xC0010014.  Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.  Source:   Started:  ???  Finished: ???  Elapsed:  ??? seconds.  The package could not be loaded.  The step failed."


    Kindly open the job>>>open the step that call your SSIS package>>>Excution Options>>> Check the option "Use 32 bit runtime" as shown belo:-

     Run you SQL Server job, it will run correctly.