SQL Server regularly backs up databases and regularly kills database deadlocks

Last Friday, the team lead told me about the deadlock process to kill the database. Sometimes this happens when the database is constantly written at the same time, because I am not very familiar with the database, when the team leader said it, I decided that I had to wait for a moment. Otherwise, how can I improve my skills? I will not study it now. Maybe I will study it again next time. If it comes out, I can try again later.

Last Friday, the team lead told me about the deadlock process to kill the database. Sometimes this happens when the database is constantly written at the same time, because I am not very familiar with the database, when the team leader said it, I decided that I had to wait for a moment. Otherwise, how can I improve my skills? I will not study it now. Maybe I will study it again next time. If it comes out, I can try again later.

Last Friday, the team lead told me about the deadlock process to kill the database. Sometimes this happens when the database is constantly written at the same time, because I am not very familiar with the database, when the team leader said it, I decided that I had to wait for a moment. Otherwise, how can I improve my skills? I will not study it now. Maybe I will continue to study it next time. I can use it next time. Later, the team lead added: "The problem of regular database backup should be solved ", just do what you say.

PS: Sqlserver 2008 R2, windows 8 64-bit

1. Back up the database

To back up data, we need to use the Sqlserver proxy. By default, the database proxy is not enabled. We need to manually enable it.

Execute the backup database script. Now we will release the script. In fact, we can replace the file path and database name to be saved in this code to implement backup. However, the scheduled backup has not been achieved.

-- Automatically back up and save the SQL Database Job script DECLARE @ filename VARCHAR (255) DECLARE @ date DATETIMESELECT @ date = GETDATE () SELECT @ filename = 'G: \ storage location \ database name-'+ CAST (DATEPART (yyyy, @ date) as varchar) +'-'+ CAST (DATEPART (mm, @ date) as varchar) + '-' + CAST (DATEPART (dd, @ date) as varchar) + '. bak 'backup DATABASE [DATABASE name] to disk = @ filename with initgodeclare @ olddate datetimeselect @ OLDDATE = GETDATE ()-5 EXECUTE master. dbo. xp_delete_file 0, N 'G: \ storage location ', N 'bak', @ olddate, 1

　　

2. Regular backup of specified database

I just started the Sqlserver proxy service. In fact, my own understanding is that it is a timer that keeps executing the tasks assigned to him by some operators. It feels a bit like an alarm. See my demo steps.

Step 1

Step 2

Step 3

Step 4

Step 5

The above step completes the regular backup of the specified database function!

**************************************** **********************************

1. Killing the database deadlock Process

Next, we will introduce some methods to kill the database deadlock process.

After a long time in the afternoon, I read a lot of articles and found that many of them use the sys in the master. sysprocesses table (http://msdn.microsoft.com/zh-cn/library/ms179881 (SQL .90 ). aspx) Here is an msdn explanation of this table. If you are not familiar with it, refer to the meaning of each table field here.

According to the opinions on the Internet, most of them write a stored procedure in the master database, and then use the job method.Periodically kill the deadlock ProcessI think this method is feasible!

The following is the SQL statement of the stored procedure.

-- Database deadlock solution, combined with the job (Baidu) to regularly clear the database deadlock process, stored procedures in the master database USE masterGOSET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ongocreate procedure sp_who_lockAS begin declare @ spid INT, @ bl INT, @ intTransactionCountOnEntry INT, @ intRowcount INT, @ intCountProperties INT, @ intCounter int create table # tmp_lock_who (id int identity (1, 1), spid SMALLINT, bl SMALLINT) IF @ ERROR <> 0 RETURN @ error insert into # tmp_lock_who (spid, bl) SELECT 0, blocked FROM (SELECT * FROM sys. sysprocesses WHERE blocked> 0) a where not exists (SELECT * FROM sys. sysprocesses WHERE blocked> 0) B WHERE. blocked = spid) union select spid, blocked FROM sys. sysprocesses WHERE blocked> 0 IF @ ERROR <> 0 RETURN @ ERROR -- find the number of records in the temporary table SELECT @ intCountProperties = COUNT (*), @ intCounter = 1 FROM # tmp_lock_who IF @ ERROR <> 0 RETURN @ error if @ intCountProperties = 0 select n 'no blocking and deadlock information now' AS message -- the loop starts WHILE @ intCounter <= @ intCountProperties BEGIN -- Take the first record SELECT @ spid = spid, @ bl = bl FROM # tmp_lock_who WHERE Id = @ intCounter begin if @ spid = 0 select n 'causes a database deadlock:' + CAST (@ bl as varchar (10 )) + N' process number. The SQL syntax it executes is AS follows: 'else SELECT n' process number SPID: '+ CAST (@ spid AS VARCHAR (10 )) + N' is blocked by the Process Code SPID: '+ CAST (@ bl as varchar (10) n'. The SQL syntax of the current process is AS follows: 'dbcc INPUTBUFFER (@ bl) END -- loop pointer move down SET @ intCounter = @ intCounter + 1 end drop table # tmp_lock_who RETURN 0 ENDgo

After the preceding SQL statement is executed, a stored procedure is generated in the master database. The called code is very simple.

-- Execute EXEC sp_who_lockGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO

After the call is complete, all deadlocks in the database can be killed.

Question: How to determine whether the process is a deadlock process is the key point. In msdn, sys. in the sysprocesses table, there is a field blocked, and all the deadlock process data greater than 0 is the database. Then, run KILL + SPID (process ID) to KILL the deadlock process, this is the main idea.

2. regularly Killing the database deadlock Process

For the process of regularly Killing database deadlocks, pay attention to the following two points:

1. The execution sequence. Now the master database creates the stored procedure and then creates the job.

2. the Execution Code of a job is the stored procedure that kills the deadlock process (the SQL statement that calls the stored procedure cannot contain statements such as DROP xxx, after the first job is executed, an error will be reported for the second time, and the job will never be executed successfully.

Step 1

You need to execute the Stored Procedure for killing the deadlock process. Only the stored procedure exists in the master database can be called as a job. Otherwise, the call fails.★

Step 2

Create a new job and put the SQL statement that just called the stored procedureCreate a job => step Options => command. Last stepExecution PlanSet by yourself according to the actual situation

I also absorbed the two difficulties in SQL Server databases this afternoon. You can easily handle similar problems in the future. If you feel helpful, you can recommend it to your younger brother!

If there is any error in the article, you can contact my QQ:707055073

My group:152652959