SQL server-focus transactions on local variables, temporal tables, table variables, and how the log file is full when it is fully stored (31)

Objective

Next we end up with the last few sections of the SQL Server Basic series, and then we'll talk about SQL Server performance tuning, and we'll get to the topic.

SQL Server transactions affect local variables

What does a transaction have to do with a variable, in other words, when we roll back the transaction and commit the transaction to the local variable, let's look at a concrete example.

PRINT'test after rolling back a transaction'DECLARE @FlagINT intset @FlagInt=1PRINT @FlagInt----the value of this variable is 1BEGIN transactionset @FlagInt=2----set the variable value to 2PRINT @FlagIntROLLBACK transactionprint @FlagInt----what is the value of this variable? Goprint'--------------------'PRINT'Test after committing a transaction'DECLARE @FlagINT intset @FlagInt=1PRINT @FlagInt----the value of this variable is 1BEGIN transactionset @FlagInt=2----set the variable value to 2PRINT @FlagIntCOMMIT transactionprint @FlagInt----what is the value of this variable? GO

From the above diagram we can clearly know: by changing the local variable value, but after the rollback and after the commit to the variable does not work at all, we conclude that the local variable is not affected by the transaction, because its scope is limited.

SQL Server transaction impact on temporary variables

Let's start by creating a temporary and inserting a piece of data, then opening the transaction to insert a piece of data and rolling back the transaction to see what the result is, as shown in the following example:

Use adventureworks2012go--Create a temporary table and insert a row of data into the CREATE TABLE #TempTable (Col1 VARCHAR ( -) INSERT into #TempTable (Col1) VALUES ('Temp table-outside Tran');--Query the value of the temporary table insert Select Col1 as Temptable_beforetransactionfrom #TempTable; BEGIN TRAN--insert a row of data insert into #TempTable (Col1) VALUES ('Temp table-inside Tran'); ROLLBACK--querying for values in temporary tables select Col1 as Temptable_aftertransactionfrom #TempTable; GO--Delete temporary table drop table #TempTableGO

From the observation that when a data is inserted by default for the temp table-outside Tran , we then turn on the transaction and insert a data for the temp table-inside Tran , and then the transaction is rolled back, at which point the rollback Data is consistent with the default inserted data, based on which we conclude that the temporary table is affected by the transaction.

SQL Server transactions affect table variables

Use adventureworks2012go-- Create a table variable and insert a row of data declare @TableVar table (Col1 VARCHAR) INSERT into @TableVar (Col1) VALUES ('Table var-outside Tran'); --The query does not open the transaction before the data select Col1 as Tablevar_beforetransactionfrom @TableVar; Begin TRAN-- opens a transaction and inserts a row of data insert into @TableVar (Col1) VALUES ('Table var-inside TRAN ' ); ROLLBACK-- query open transaction after data select Col1 as Tablevar_aftertransactionfrom @TableVar; GO

When we insert a data for table Var-outside Tran By default, we open the transaction to insert a data for table Var-inside Tran , and then rollback to find that the result is still tabl E var-inside Tran , we come to the conclusion that the table variable is not affected by the firm .

Shrink log files

When the log file fills up, we may need to shrink the logfile to a minimum, so how do we do it? We can shrink the log file in the following three ways, please continue to look down.

Shrink mode One (executed with new query statement)

Before SQL Server 2005 we can do this by

Use adventureworks2012godbcc shrinkfile ('truncatelog'1) BACKUP LOG AdventureWorks2012 with TRUNCATE_ONLYDBCC shrinkfile ('truncatelog'1 ) GO

If you run the above command under SQL Server 2005, you receive the following error:

At this point we should run the following query.

1 ALTER DATABASE [AdventureWorks2012] SET RECOVERY full with No_waitgo

The success of this operation will result in the following:

At this point we then right-click on the database properties to get the smallest log file size after we shrink, as follows:

Shrink log file Mode Two (shrink the log file at the correct time)

By right-clicking the task of the database that you want to shrink the log file for corresponds to, shrink-file, as follows:

Shrink log file Mode three (completely remove log files)

Sometimes we don't need large log files at all, we need to completely remove the log files to free up hard disk space, we can achieve what we need with the following four steps.

(1) Separating the database

(2) Renaming log files

(3) Reattach the database with no log files

(4) Delete log file

The demo is as follows:

Summarize

In this section we explain in detail that transactions do not work on local variables and table variables, and we take several ways to shrink the logs when the log file fills up, and we begin the next section to enter the last few deadlocks in the SQL Server Basic series.

SQL server-focus transactions on local variables, temporal tables, table variables, and how the log file is full when it is fully stored (31)