-
SQL Code
--Backup declare @sql varchar (8000) set @sql = ' backup database mis to disk= ' d:\databack\mis\mis ' + RTrim (CONVERT (Varchar,getdate (), ()) + '. Bak ' exec (@sql) --deleted 15 days ago backup file set @sql = ' del d:\ Databack\mis\mis ' +rtrim (CONVERT (varchar,getdate () -15,112)) + '. Bak ' exec [email protected ] --Clear Log dump transaction databasename with no_log backup log databasename with no_log --View information about users and processes in the database sp_who --view information about active users and processes in the SQL Server database sp_who ' Active ' --View the case of a lock in a SQL Server database sp_lock --process number--50 is internal to the SQL Server system, the process number is greater than the user's connection process. --spid is the process number, DBID is the database number, ObjID is the data object number--the method of shrinking the database log file--shrinking the Simple Recovery model database log, the size of the @database_name_log after shrinking is m backup log @ Database_name withno_log dbcc shrinkfile (@database_name_log, 5) --methods for parsing SQL Server SQL statements : set statisticstime {on | off} set statistics io {on | off} - -Graphical display of query execution plan--Display estimated evaluation plan (D) in Query Analyzer------ctrl-l or click on the graph in the toolbar-text mode to display the query execution plan Set showplan_all {On |off} set showplan_text { on | off } set statistics profile { on | off }
-
--full backup, once a week
Use Master
GO
DECLARE @str varchar (100)
Set @str = ' D:\DBtext\jgj\DBABak\FullBak ' +replace (replace (replace (CONVERT (Varchar,getdate (), 20), '-', '), ', '), ': ', ') + '. Bak '
BACKUP DATABASE [Demo] to [email protected]
With Retaindays=15,noformat,noinit,
Name=n ' Demo full backup ', Skip,norewind,
nounload,stats=10
GO
--Truncate Log
Use Master
GO
BACKUP LOG Demo with no_log
GO
--Shrink log file
Use Demo
GO
DBCC shrinkfile (N ' Demo_log ', 0,truncateonly)
GO
--differential backup, once per day
Use Master
GO
DECLARE @str varchar (100)
Set @str = ' D:\DBtext\jgj\DBABak\DiffBak ' +replace (replace (replace (CONVERT (Varchar,getdate (), 20), '-', '), ', '), ': ', ') + '. diff '
BACKUP DATABASE [Demo] to [email protected]
With Differential,retaindays=8,noformat,noinit,
Name=n ' demo differential backup ', Skip,norewind,
nounload,stats=10
GO
--log backups, once per hour
Use Demo
GO
DECLARE @str varchar (100)
Set @str = ' D:\DBtext\jgj\DBABak\logbak ' +replace (replace (replace (CONVERT (Varchar,getdate (), 20), '-', '), ', '), ': ' , ') + '. Trn '
BACKUP LOG [Demo] to [email protected]
With Retaindays=3,noformat,noinit,
Name=n ' demo log backup ', Skip,norewind,
nounload,stats=10
GO
--Delete outdated backup files two times a day
DECLARE @str varchar (+), @dir varchar (+), @fileName varchar (30)
Set @dir = ' del D:\DBtext\jgj\DBABak\ '
Set @filename =left (replace (replace (CONVERT (varchar,getdate () -15,20), '-', '), ', '), ': ', '), 8)
Set @[email protected]+ ' Fullbak ' [email protected]+ ' *.bak '
EXEC xp_cmdshell @str
Set @filename =left (replace (replace (CONVERT (varchar,getdate () -8,20), '-', '), ', '), ': ', '), 8)
Set @[email protected]+ ' Diffbak ' [email protected]+ ' *.diff '
EXEC xp_cmdshell @str
Set @filename =left (replace (replace (CONVERT (varchar,getdate () -8,20), '-', '), ', '), ': ', '), 8)
Set @[email protected]+ ' Logbak ' [email protected]+ ' *.trn '
EXEC xp_cmdshell @str
-
-
SQL Code
--How do I delete a SQL2005 outdated database backup file? In the SQL2005 database, it is not possible to automatically delete outdated backup files, so borrowing a third-party plug-in to complete this function. --Method One: Delete the expired backup file under the specified directory by forfiles purpose: Delete directory I: \sqldataup Zhongtian before. Bak file: Step: 1, define the Forfiles batch script as follows: C: \> forfiles/p I: \sqldataup/m *. Bak/c "cmd/c del/q @path"/d-5 returns the current drive letter C: \> if execution succeeds. --Returns a message if no files need to be deleted: The file was not found with the specified search criteria. For example:--c:\>forfiles/p i:\sqldataup/m *.bak/c "cmd/c del/q @path"/d-5-Error: The file was not found with the specified search criteria. 2, call the batch script through the scheduled task, the graph is too long see here 3, of course, you can also use SQLAgent call CMDEXEC to complete the batch job. Mode two: If the PowerShell 1.0 program is installed by default in SQL2008, you can write a script in PowerShell to complete the timed deletion of expired files. Too much content, see article http://blog.csdn.net/claro/archive/2009/08/18/4458417.aspx.
-
SQL Code
--It's a good idea to back up the logs and recover the data from the log ... The following is a log processing method is generally not recommended to do 4th, 62 steps 4th unsafe, it is possible to damage the database or lost data 6th step if the log reaches the upper limit, subsequent database processing will fail and can be resumed after the log is cleaned up. --*/-- All of the following library names refer to the library name of the database you are working with 1. Empty log DUMP TRANSACTION library name with no_log 2. Truncate transaction log: backup LOG Library name with no_log 3. Shrinking the database file (if not compressed, the database file does not decrease Enterprise Manager--Right click on the database you want to compress--all tasks--shrink the database--Shrink the file--Select the log file--select shrink to XXM in the shrink mode, here will give a minimum number of m allowed to shrink to, enter this number directly, OK--Select the data file--choose Shrink to XXM in the contraction mode, here will give a allowable shrinkage to the minimum m number, directly enter this number, OK can also use SQL statement to complete the --shrinking database dbcc Shrinkdatabase (library name) --shrinks the specified data file, 1 is the file number, which can be queried by this statement: SELECT * from Sysfilesdbcc shrinkfile (1) 4. To maximize the size of the log file (if it is sql 7.0, this step can only be done in Query Analyzer) a. Detaching a database: Enterprise Manager--server--database--right--detach database B. Delete the log file from my Computer c. Attaching the database: Enterprise Manager--server--database--right--Attach database This method will generate a new log with a size of more than 500 K or code: The following example separates pubs, and then attaches a file in pubs to the current server. A. Separation EXEC sp_detach_db @dbname = ' library name ' b. Deleting a log file C. re-attaching exec sp_attach_single_ file_db @dbname = ' library name ', @physname = ' c:\Program Files\Microsoft SQL Server\mssQl\data\ library name. mdf ' 5. To automatically shrink in the future, do the following: Enterprise Manager--server--right-click Database--Properties--Options--select ' Auto shrink '--sql statement Setup method: Exec sp_dboption ' Library name ', ' autoshrink ', ' TRUE ' 6. If you want to keep it from growing too large in the future. Enterprise Manager-Server-right-click Database-Properties-transaction log-limits file growth to XM ( X is the maximum data file size you allow) The--sql statement is set:alter database the library name Modify file (name= logical file name, maxsize=20)
-
--View index information for a table
EXEC sp_helpindex TB
--Combine sys.indexes and sys.index_columns,sys.objects,sys.columns to query the information of the table or view to which the index belongs
Select
O.name as table name,
I.name as index name,
C.name as column name,
I.type_desc as type description,
Is_primary_key as PRIMARY KEY constraint,
Is_unique_constraint as Unique constraint,
Is_disabled as Disabled
From
Sys.objects o
INNER JOIN
Sys.indexes I
On
i.object_id=o.object_id
INNER JOIN
Sys.index_columns IC
On
IC.INDEX_ID=I.INDEX_ID and ic.object_id=i.object_id
INNER JOIN
Sys.columns C
On
IC.COLUMN_ID=C.COLUMN_ID and ic.object_id=c.object_id
Go
--Query the key and column information for the index
Select
O.name as table name,
I.name as index name,
C.name as field number,
From
sysindexes i inner join sysobjects o
On
I.id=o.id
INNER JOIN
Sysindexkeys K
On
O.id=k.id and I.indid=k.indid
INNER JOIN
syscolumns C
On
C.id=i.id and K.colid=c.colid
where
O.name= ' table name '
---query for index operation information
SELECT * FROM Sys.dm_db_index_usage_stats
--Query the statistics for the specified table (Sys.stats and sysobjects union queries)
Select
O.name,--Table Name
S.name,--The name of the statistic information
auto_created,--statistics are automatically created by the query processor
user_created--statistics are created by user display
From
Sys.stats
INNER JOIN
sysobjects o
On
S.object_id=o.id
where
O.name= ' table name '
Go
--View the information in the statistics column
Select
O.name,--Table Name
S.name,--The name of the statistic information
SC.STATS_COLUMN_ID,
C.name---column name
From
Sys.stats_columns SC
INNER JOIN
sysobjects o
On
Sc.object_id=o.id
INNER JOIN
Sys.stats s
On
SC.STATS_ID=S.STATS_ID and sc.object_id=s.object_id
INNER JOIN
Sys.columns C
On
SC.COLUMN_ID=C.COLUMN_ID and sc.object_id=c.object_id
where
O.name= ' table name '
--View detailed information about the statistics
DBCC SHOW_STATISTICS
--View statistics automatically created by index
EXEC sp_autostats ' object name '
--Turn off database options for auto-generate statistics
Alter DATEBASE database name set Auto_create_statistics off
--Create statistical information
Create statistics statistics name on table name (column name)
[With
[[Fullscan
Sample Number{percent|rows}]
[NoRecompute]
]
Go
Explain the above parameters:
Fullscan: Specifies that statistics are collected for all rows in a table or view
Sample Number{percent|rows}: Specifies the number of rows or percentages of data that should be read by random sampling the sample option cannot be used with the FULLSCAN option
NoRecompute: Specifies that the database engine does not automatically recalculate statistics
--Calculate random sampling statistics
Create statistics statistics name on table name (column name)
With sample 5 percent---create statistics, press 5% to calculate random sampling statistics
Go
--Create statistical information
EXEC sp_createstats--parameters to check their own help, not listed here
--Modify the statistical information
Update STATISTICS Table name | view Name
Index name | Statistics name, index name | statistics Name,.....
[With
[[Fullscan
Sample Number{percent|rows}]
[NoRecompute]
]
---parameters are similar to the CREATE STATISTICS statement, here are a few common applications
1. Update all statistics for the specified table
Update STATISTICS table name
2. Update statistics for a single index of a specified table
Update STATISTICS table name Index name
3. Perform a full scan of the table to update the statistical information
Update STATISTICS table name (column name) with Fullscan
--Get disk read and write status
Select
@ @total_read as ' number of read disk ',
@ @total_write as ' number of writes to disk ',
@ @total_error as ' number of disk write errors ',
GETDATE () as ' current time '
--Get I/O statistics for a database file
SELECT * from Fn_virtualfilestats (null,null)
--Two parameters
database_id--Specifies the database number, or if NULL, returns I/O statistics for all DB instances
FILE_ID--The number of the file, and if NULL, returns information for all files
--Get I/O work
Select
@ @id_busy,--sql the time it takes to perform input and output operations since the last startup
@ @timeticks,--the number of microseconds per clock cycle
@ @id_busy *@ @timeticks as ' I/O operation milliseconds ',
GETDATE () as ' current time '
--View SQL SEVER CPU activity, working condition
Select
@ @cpu_busy,--working hours since the last startup
@ @timeticks,--the number of microseconds per clock cycle
@ @cpu_busy *cast (@ @timeticks as float)/1000 as ' CPU operating time (sec) ',
@ @idie *cast (@ @timeticks as float)/1000 as ' CPU idle time (sec) '
GETDATE () as ' current time '
--Get network packet statistics
Select
GETDATE () as ' current time ',
@ @pack_received as ' number of input packets ',
@ @pack_sent as ' output packet count ',
@ @packet_error as ' number of error packets '
Server configuration options
--Start AWE
sp_configure ' show advanced options ', 1
Reconfigure
Go
sp_configure ' awe enable ', to activate AWE option, to support more than 4G memory specific usage See note three
Go
sp_configure ' show advanced options ', 0
Reconfigure
Go
--Specify the number of rows in the cursor set
sp_configure ' show advanced options ', 1
Reconfigure
Go
sp_configure ' cursor threshold '--Specifies the number of rows in the cursor set, which exceeds this number, asynchronously generates the cursor keyset set
Go
sp_configure ' show advanced options ', 0
Reconfigure
Go
--Specify default language values for full-text indexed columns
sp_configure ' show advanced options ', 1
Reconfigure
Go
sp_configure ' default Full-text language '--2052 representative Simplified Chinese, specific query Books Online
Go
sp_configure ' show advanced options ', 0
Reconfigure
Go
--Controls whether the trigger returns a result set
sp_configure ' show advanced options ', 1
Reconfigure
Go
sp_configure ' disallow results from triggers ', 1--1 representative on
Go
sp_configure ' disallow results from triggers ', 0--0 stands off
Go
sp_configure ' show advanced options ', 0
Reconfigure
Go
--Controls the maximum amount of memory initially allocated for creating an index
sp_configure ' index create memory ', 4096
GO
--Set the maximum number of available locks
sp_configure ' show advanced options ', 1
Reconfigure
Go
sp_configure ' locks '---to set the words in the back add ', Number '
Go
sp_configure ' show advanced options ', 0
Reconfigure
Go
--Set the number of worker threads that the SQL process can use
sp_configure ' show advanced options ', 1
Reconfigure
Go
sp_configure ' max worker threads '--to set the words in the back add ', Number '
Go
sp_configure ' show advanced options ', 0
Reconfigure
Go
--Specify when a query waits for the required resources before timing out
sp_configure ' query wait ', number
Go
--Specify how long the remote operation can last before the SQL Server times out
sp_configure ' remote query timeout ', number
Go
--whether the system stored procedure is allowed to run xp_cmdshell
sp_configure ' show advanced options ', 1
Reconfigure
Go
sp_configure ' xp_cmdshell ', 1
Reconfigure
Go
sp_configure ' show advanced options ', 0
Reconfigure
Go
--Control the execution of a stored procedure from a local or remote server running an instance of SQL Server
sp_configure ' show advanced options ', 1
Reconfigure
Go
sp_configure ' remote access ', 1--1 means allow
Reconfigure
Go
sp_configure ' remote access ', 0--0 means prohibit
Reconfigure
Go
sp_configure ' show advanced options ', 0
Reconfigure
Go
---more View Books Online
--Start, pause, and stop the local SQL SERVER service
net start MSSQLSERVER--Start
NET pause MSSQLSERVER--Pause
NET continue MSSQLSERVER---services that continue to be stopped
net stop MSSQLSERVER--stop
--Query server configuration options information
SELECT * FROM Sys.configurations
Go
--In the results obtained
CONFIGURATION_ID--Unique ID for configuration options
Name--Names of configuration options
Value--Values for configuration options
Minimum--Minimum value for configuration options
Maximum--Maximum value of configuration options
Value_in_use--The running value currently used by the configuration option
Description--Description of configuration options
Is_dynamic--equals 1 indicates a variable that needs to execute the reconfiguration statement to take effect
Is_anvanced--equals 1 indicates a variable that requires the show advanced statement to take effect
--You can also use sp_configure to query server configuration option information, except that the parameters are different, see Books Online
SQL Server Routine Maintenance scripts
