What are the script statements commonly used in daily database maintenance
Editor to share with you what is commonly used in the daily maintenance of the database script statements, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article. Let's learn about it!
1. Database backup operation:
Declare@sqlvarchar (8000) set@sql='backupdatabasesmallerptodisk=''d:\'+ RTRIM (CONVERT (varchar,getdate (), 112) + .bak''exec (@ sql); (note: any quotation mark is indispensable, otherwise an error will be reported)
2. Delete the backup files from 5 days ago:
Declare@sqlvarchar (8000) set@sql='deld:\'+ RTRIM (CONVERT (varchar,getdate ()-5112)) + '.bak' 'execmaster..xp_cmdshell@sql; (Note: xp_cndshell defaults that this component is blocked and needs to be enabled before it can operate properly: use the following statement to enable EXECsp_configure'showadvancedoptions',1;RECONFIGURE;EXECsp_configure'xp_cmdshell',1;RECONFIGURE;)
3. Shrink the database statement:
Dumptransactionsmallerpwithno_log; (Note: this sentence will indicate that there is a syntax error near transaction, which is no longer used in SQL2008 to shrink the database. And use: alter, the method of use is as follows: alterdatabasesmallerpsetrecoverysimpledbccshrinkdatabase (smallerp,0);) backuplogsmallerpwithno_log; (Note: this sentence will be executed with an error: this statement does not support one or more options (no_log), also because SQL2008 no longer supports this writing.)
4. Check the active user and process information in the database:
Sp_who'active'; (Note: if you just write this, it will prompt you that there is a syntax error near sp_who, but you can look up the data during execution. If you add an exec to a recent article, you will no longer indicate an error. And the process number from 1 to 50 is used internally in the SQLSERVER system, and only the process number greater than 50 is the user's connection process. Spid is the process number, dbid is the database number, objid is the data object number)
What are the script statements commonly used in daily database maintenance
5. View the user and process information in the database:
Execsp_who
6. Check the locks in the database:
Execsp_lock
7. The method of analyzing the sql sentence in SQLSERVER:
Setstatisticstime {on | off} (you only need to write on or off when writing a statement) setstatisticsio {on | off}
8. Display the query execution plan in text:
Setshowplan_all {on | off} setshowplan_text {on | off} setstatisticsprofile {on | off}
9. Differential backup:
Declare@strvarchar set@str='D:\'+ replace (convert (varchar,getdate (), 20),'-','),','),':,') + .diff'BACKUPDATABASEsmallerpTODISK=@strWITHDIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT,NAME=N'Demo differential backup, SKIP,NOREWIND,NOUNLOAD,STATS=10GO
10. Delete expired backup files and set twice a day:
Declare@strvarchar, @ dirvarchar, @ fileNamevarchar (30) set@dir='delD:\ 'set@filename=left (replace (replace (convert (varchar,getdate ()-15 varchar,getdate () 20),' -','),','),':,'), 8) set@str=@dir+'fullbak'+@filename+'*.bak'execxp_cmdshell@strset@filename=left (replace (replace (convert (varchar,getdate ()-8) 20),'-','),',') ':','), 8) set@str=@dir+'diffbak'+@filename+'*.diff'execxp_cmdshell@strset@filename=left (replace (convert (varchar,getdate ()-8 ~ ()) 20),'-','),','),),','), 8) set@str=@dir+'logbak'+@filename+'*.trn'execxp_cmdshell@str
These are all the contents of this article entitled "what are the script statements commonly used in daily database maintenance?" Thank you for your reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
