Shulou(Shulou.com)06/01 Report--

SQL Server data Automation Operation and maintenance

When it comes to the current types of databases, there are three main popular types: Mysql, Sql Server and Oracle. I believe we are already very familiar with the differences, so I won't introduce them here. Today, we will mainly introduce the problems encountered in my recent work. The amount of data stored in our access control database is too large, resulting in very slow operation and query from the page, affecting the efficiency of daily operations. Because the leaders require that the data need to be retained. To facilitate future enquiries

Therefore, we generally judge by the date that the deletion of data other than the specified date cannot be realized. Of course, we want to insert the data other than the specified date into the backup database, and then delete the data outside the specified date. In fact, this idea is also correct, but in this case, I need to execute two statements. If the first one fails, the second one certainly cannot be executed. So we integrate the two commands into one according to this idea, and then we simulate it.

There are more than 3 million pieces of data in our table, which is a little too much, so the query must be slow.

In order to ensure the security of the data during operation, we need to back up the table.

Let's make a copy of the cardinfo table and then operate it.

Select * into cardinfo_temp from cardinfo

Let's check.

Select * into cardinfo_temp from DB.dbo.cardinfo

Next, we are ready to start the preparation before the operation.

We need to create a table as a data backup table, of course, since it is a backup, the structure of the table must be the same, so we still follow the above method, copy a backup table, and then empty the data

Select * into cardinfo_bak from DB.dbo.cardinfo

Then empty the data in the backup table

Delete cardinfo_bak

We confirm the data.

Select count (*) from cardinfo_bak

We need to move all the data beyond 3 months to the backed-up table

Delete from cardinfooutput deleted.* into cardinfo_bakwhere card_date_ts < dateadd (mm,-3,getdate ())

We started the execution, and there was an error in the execution.

We have two solutions according to the error report, but we use the second.

two。 Delete and rebuild the table DB_Bak.dbo.acc_monitor_log, and remove the identity attribute from the original identity column in the table during reconstruction, so let's modify it. Right-click on the table-design-ID--- indicates the attribute-- will be changed to no.

Then after saving, we execute it again, and the execution is successful.

Next, let's make statistics.

Select count (*) from cardinfo select count (*) from cardinfo_bak

If we want to manipulate the data within three months,

Delete from cardinfooutput deleted.* into cardinfo_bakwhere card_date_tls between dateadd (mm,-3,getdate ()) and getdate

We then need to execute the execution command regularly by configuring the job.

We need to enable the SQL Agent service.

Let's do a new homework.

Define the job name

On the step page-New

Define step name, command, database information, etc.

Save confirmation

New Home Plan-execution time

Finally complete the whole configuration

Once again, this is the end of our environment. If you have any questions, please leave me a message. Thank you.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.