Common scripts Overview and partial test _mssql2008 for database maintenance

Some database statements may not normally be used much, so the use of the time is always inevitable internet inquiries, a little too troublesome, today in the CSDN forum to see a paste of these statements, a copy of a copy, this would like to copy the end, but like some friends, the real use of them sometimes will be found to be wrong , so choose a day to hit day, and spend some time today in SQL SERVER 2008 to try to run. It didn't pay much attention to the SQL2008 syntax is different from the previous version, because the common select and other statements are the same, always thought that 2008 should be with 2005 version almost no difference. Unexpectedly this try, sure enough to try out the problem, this just found, previously seen from the book backup statements and dump statements have disappeared in 2008 ...

Nonsense not to say, the inclusion of statements and notes affixed to facilitate the subsequent use of the query ~ ~

1. Database backup operation:

Copy Code code as follows:

DECLARE @sql varchar (8000)
Set @sql = ' backup database smallerp to disk= ' D:\ ' +rtrim (CONVERT (Varchar,getdate (), 112)) + '. Bak ' "
EXEC (@sql);

(Note: Quotation marks are not missing any one, otherwise it will be an error)
2, delete 5 days ago Backup files:

Copy Code code as follows:

DECLARE @sql varchar (8000)
Set @sql = ' del d:\ ' +rtrim (CONVERT (Varchar,getdate () -5,112)) + '. Bak ' "
EXEC master.. xp_cmdshell @sql;

(Note: Xp_cndshell default this component is blocked and needs to be enabled before it can function correctly: Use the following statement to enable exec sp_configure ' show advanced options ', 1; Reconfigure; EXEC sp_configure ' xp_cmdshell ', 1; Reconfigure;)
3, shrink the database statement:

Copy Code code as follows:

Dump transaction smallerp with NO_LOG;

(Note: This sentence will prompt transaction near the syntax error, the original in the SQL2008 has no longer use this method to shrink the database.) and use: Alter, using the following methods:

Copy Code code as follows:

ALTER DATABASE SMALLERP set recovery simple
DBCC SHRINKDATABASE (smallerp,0);)
Backup log smallerp with no_log;

(Note: This sentence will be executed when the error: This statement does not support one or more options (no_log), because SQL2008 no longer support this type of writing. )
4. View the active users and process information in the database:

Copy Code code as follows:

sp_who ' active ';

(Note: If you just write this, you will be prompted sp_who syntax errors, but the implementation of the time can be found in the data, if the front plus a exec, then no longer prompted errors.) And the process number of 1-50
Used internally for SQL Server systems, only the process number greater than 50 is the user's connection process. The SPID is the process number, dbid is the database number, ObjID is the data object number)
5, view the user and process information in the database:

Copy Code code as follows:

Exec sp_who;

6, view the database lock situation:

Copy Code code as follows:

Exec sp_lock;

7, analysis of SQL Server SQL statements method:

Copy Code code as follows:

Set STATISTICS Time {in | off} (when writing a statement, you only need to specify on or off)
Set STATISTICS IO {on | off}

8, Text mode display Query execution plan:

Copy Code code as follows:

Set SHOWPLAN_ALL {on | off}
SET SHOWPLAN_TEXT {on | off}
Set STATISTICS Profile {on | off}

9. Differential Backup:

Copy Code code as follows:

DECLARE @str varchar (100)
Set @str = ' d:\ ' +replace (replace (varchar,getdate (), '-', '), ', ', '), ': ', ', ', '. diff '
BACKUP DATABASE smallerp to disk= @str
With Differential,retaindays=8,noformat,noinit,
Name=n ' demo differential backup ', Skip,norewind,
nounload,stats=10
Go

10, delete expired backup files, and set two times a day:

Copy Code code as follows:

DECLARE @str varchar (MB), @dir varchar (m), @fileName varchar (30)
Set @dir = ' del d:\ '
Set @filename =left (replace (replace (varchar,getdate () -15,20, '-', '), ', ', ', ', ', ', ', ', ', '), 8)
Set @str = @dir + ' Fullbak ' + @filename + ' *.bak '
EXEC xp_cmdshell @str
Set @filename =left (replace (replace (varchar,getdate () -8,20, '-', '), ', ', ', ', ', ', ', ', ', '), 8)
Set @str = @dir + ' Diffbak ' + @filename + ' *.diff '
EXEC xp_cmdshell @str
Set @filename =left (replace (replace (varchar,getdate () -8,20, '-', '), ', ', ', ', ', ', ', ', ', '), 8)
Set @str = @dir + ' Logbak ' + @filename + ' *.trn '
EXEC xp_cmdshell @str

First of all, it seems generally not necessary and relatively common ~