--★★SQL Server 2005 and above database batch backup stored procedures (judgment disk letters and paths, error disk letters return, does not exist
--Automatic path creation)★★★★★★★★★★★★★★★★★★★★★★
USE master
GO
--1. Periodically backup database code (retain the original backup):
--The backup file name is: original database name + '_' + backup date.bak
IF OBJECT_ID('sp_backupdatabase') IS NOT NULL
DROP PROC sp_backupdatabase
GO
CREATE PROC sp_backupdatabase
@path NVARCHAR(100)--Path
AS
--Path name format standardization
IF RIGHT(@path,1)<>'\' SET @path=@path+'\'
--Get folder information
DECLARE @t TABLE(id INT IDENTITY,a INT,b INT,c INT)
DECLARE @fpath NVARCHAR(3)
SET @fpath=LEFT(@path,3)
INSERT @t EXEC master..xp_fileexist @fpath
INSERT @t EXEC master..xp_fileexist @path
--If the specified drive letter does not exist, an error message will be returned:
IF EXISTS(SELECT 1 FROM @t WHERE id=1 AND c=0)
BEGIN
RAISERROR(N' input drive letter does not exist, please re-enter!',16,1)
RETURN
END
--If the specified folder does not exist, create:
ELSE IF EXISTS(SELECT 1 FROM @t WHERE b=0 AND id=2)
BEGIN
DECLARE @mddir NVARCHAR(100)
SET @mddir='md '+@path
EXEC master..xp_cmdshell @mddir
END
--Start backup of the database to the specified directory
DECLARE @s nvarchar(4000)
SELECT @s=ISNULL(@s+';','')
+N'BACKUP database ['+name+'] TO DISK = '''
+@path+name+'_'+CONVERT(NVARCHAR(8),getdate(),112)+N'.bak'''
FROM master..sysdatabases
WHERE name NOT IN('master','tempdb','model','msdb','pubs')--The databases that do not participate in the backup are filtered here.
EXEC(@S)
GO
--Calling method:
EXEC sp_backupdatabase 'f:\Backup\tony'
/*
--Return information:
312 pages have been processed for database 'mydb', file 'mydb' (on file 1).
1 page has been processed for database 'mydb', file 'mydb_log' (located on file 1).
BACKUP DATABASE successfully processed 313 pages, spending 0.733 seconds (3.336 MB/s).
Pages of 208 have been processed for database 'test', file 'test' (located on file 1).
1 page has been processed for database 'test', file 'test_log' (located on file 1).
BACKUP DATABASE successfully processed 209 pages, spending 0.413 seconds (3.951 MB/sec).
--Backed file list:
mydb_20100418.bak
test_20100418.bak
*/
--2. Periodic backup of database code (automatically delete the original backup file):
--The backup file name is: original database name.bak
IF OBJECT_ID('sp_backupdatabase') IS NOT NULL
DROP PROC sp_backupdatabase
GO
CREATE PROC sp_backupdatabase
@path NVARCHAR(100)--Path
AS
--Path name format standardization
IF RIGHT(@path,1)<>'\' SET @path=@path+'\'
--Get folder information
DECLARE @t TABLE(id INT IDENTITY,a INT,b INT,c INT)
DECLARE @fpath NVARCHAR(3)
SET @fpath=LEFT(@path,3)
INSERT @t EXEC master..xp_fileexist @fpath
INSERT @t EXEC master..xp_fileexist @path
--If the specified drive letter does not exist, an error message will be returned:
IF EXISTS(SELECT 1 FROM @t WHERE id=1 AND c=0)
BEGIN
RAISERROR(N' input drive letter does not exist, please re-enter!',16,1)
RETURN
END
--If the specified folder does not exist, create:
ELSE IF EXISTS(SELECT 1 FROM @t WHERE b=0 AND id=2)
BEGIN
DECLARE @mddir NVARCHAR(100)
SET @mddir='md '+@path
EXEC master..xp_cmdshell @mddir
END
--Start backup of the database to the specified directory
DECLARE @s nvarchar(4000)
SELECT @s=ISNULL(@s+';','')
+N'BACKUP database ['+name+'] TO DISK = '''
+@path+name+N'.bak'' WITH INIT'
FROM master..sysdatabases
WHERE name NOT IN('master','tempdb','model','msdb','pubs')--The databases that do not participate in the backup are filtered here.
EXEC(@S)
GO
--Calling method:
EXEC sp_backupdatabase 'f:\Backup\tony2'
/*
--Return information:
312 pages have been processed for database 'mydb', file 'mydb' (on file 1).
1 page has been processed for database 'mydb', file 'mydb_log' (located on file 1).
BACKUP DATABASE successfully processed 313 pages, spending 0.599 seconds (4.082 MB/sec).
Pages of 208 have been processed for database 'test', file 'test' (located on file 1).
1 page has been processed for database 'test', file 'test_log' (located on file 1).
BACKUP DATABASE successfully processed 209 pages, spending 0.351 seconds (4.651 MB/s).
--Backed file list:
*/
--★★DBCC is a set of console commands provided by SQL Server. It has very powerful functions and masters some necessary statements. It is not helpful for operating the database. It is classified as follows:
--1. DBCC help class command
* DBCC HELP('?')
--Query all DBCC commands
* DBCC HELP('Command')
--Query the syntax description of the specified DBCC command
* DBCC USEROPTIONS
--Return to the SET option of the current connection's activity (settings)
--2. DBCC check verification command
* DBCC CHECKALLOG ('Database Name')
--Check the consistency of the disk space allocation structure of the specified database
* DBCC CHECKCATALOG ('Database Name')
--Check the consistency between system tables and system tables of the specified database
* DBCC CHECKCONSTAINTS ('tablename')
--Check the integrity of the specified constraints or all constraints on the specified table
* DBCC CHECKDB
--Check the allocation and structural integrity of all objects in the database
* DBCC CHECKFILEGROUP
--Check the allocation and structural integrity of all tables in the specified file group in the current database
* DBCC CHECKTABLE
--Check the integrity of data, indexes and test, ntest and image pages of the specified table or index view
* DBCC CHECKIDENT
--Check the specified current identification value
* DBCC SQLPERF(UMSSTATS) undocumented in BOL
-- Can be used to check whether the CPU has reached the bottleneck
--The most critical reference data num runnable indicates how many threads are waiting for running
--If it is greater than or equal to 2, consider that the CPU reaches the bottleneck
--3. DBCC maintenance command
* DBCC CLEANTABLE ('db_name','table_name')
--Recycle Alter table drop column statement to delete variable length column or text
* DBCC DBREINDEX
--Reconstruct one or more indexes of the specified database
* DBCC INDEXDEFRAG
--Defragment the indexes and nonclustered indexes on tables or views
* DBCC PINTABLE (db_id,object_id)
--Residing table data in memory
--The way to see which tables reside in memory is:
select objectproperty(object_id('tablename'）,'tableispinned')
* DBCC UNPINTABLE (db_id,object_id)
--Undo the table residing in memory
* DBCC SHRINKDATABASE(db_id,int)
--Shrink the data file and log file size of the specified database
* DBCC SHRINKFILE(file_name,int)
--Shrink the specified data file and log file size of the related database
--IV. DBCC performance adjustment command
* DBCC dllname(FREE)
sp_helpextendedproc View loaded extension PROC
--Uninstall the specified extended process dynamic link library (dll) in memory
* DBCC DROPCLEANBUFFERS
--Delete all buffers from the buffer pool
* DBCC FREEPROCCACHE
--Delete all elements from the process buffer
* DBCC INPUTBUFFER
--Show the last statement sent from the client to the server
* DBCC OPENTRAN (db_name)
--Query the transaction that has the longest execution time of a database, which program owns it
* DBCC SHOW_STATISTICS
--Show current distribution statistics for the specified target on the specified table
* DBCC SHOWCONTIG
--Show fragmented information of the data and index of the specified table
* DBCC SQLPERF
(logspace) --View the log status of each DB
(iostats) --View IO status
(threads) --View thread consumption
--Returns a variety of useful statistics
* DBCC CACHESTATS
--Shows statistics on SQL Server 2000 memory
* DBCC CURSORSTATS
--Show the statistics of SQL Server 2000 cursor
* DBCC MEMORYSTATS
--Show how SQL Server 2000 memory is segmented
* DBCC SQLMGRSTATS
--Show SQL statements prepared for read first and read pre-read in buffer
--5. DBCC Undisclosed Commands
* DBCC ERRLOG
--Initialize SQL Server 2000's error log file
* DBCC FLUSHPROCINDB (db_id)
--Clear the stored procedure cache content of a database in the SQL Server 2000 server memory
* DBCC BUFFER (db_name,object_name,int(number of buffers))
--Show the header information and page information of the buffer
* DBCC DBINFO (db_name)
--Show the structure information of the database
* DBCC DBTABLE
--Display table (data dictionary) information for managing data
* DBCC IND (db_name,table_name,index_id)
--View the page information used by a certain index
* DBCC REBUILDLOG
--Rebuild SQL Server 2000 transaction log file
* DBCC LOG (db_name,3) (-1--4)
--View the log information of the things used by a database
* DBCC PAGE
--View information on a database data page
* DBCC PROCBUF
--Show buffer headers and stored procedure headers in process buffer pool
* DBCC PRTIPAGE
--View the page number that each row of an index page points to
* DBCC PSS (user,spid,1)
--Show the process information currently connected to the SQL Server 2000 server
* DBCC RESOURCE
--Show the resources currently used by the server
* DBCC TAB (db_id,object_id)
--Show the structure of the data page
6. DBCC tracking mark
-- Tracking tags are used to temporarily set specific features of the server or shut down specific behaviors, often used to diagnose performance issues or debug stored procedures or complex ---Computer systems
* DBCC TRACEON (3604)
--Open the tracking tag
* DBCC TRACEOFF
--Close the tracking mark
* DBCC TRACESTATS
--View the status of the tracking mark
--7. Use DBCC result set output
--Many DBCC commands can produce table format output (using the WITH TABLERESULTS option). This information can be loaded into
--Medium for future use. The following shows a sample script:
CREATE TABLE DBCCResult (
DBCCFlag INT,
Result INT
)
INSERT INTO DBCCResult
EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
SELECT *
FROM DBCCResult
--8. Official suggestions for using DBCC
--1. Run CHECKDB when the system usage is low.
--2. Please make sure that other disk I/O operations are not performed at the same time, such as disk backup.
--3. Put tempdb into a separate disk system or a fast disk subsystem.
--4. Allow tempdb to have sufficient expansion space on the drive. Use DBCC with ESTIMATE ONLY
--Estimate how much space tempdb will need.
--5. Avoid running queries or batch jobs that occupy a large amount of CPU.
--6. Reduce active transactions when the DBCC command is run.
--7. Use the NO_INFOMSGS option to significantly reduce processing and tempdb usage.
--8. Consider using DBCC CHECKDB with PHYSICAL_ONLY option to check pages and record the first part
--The physical structure. This operation performs a quick check when hardware-induced errors are questioned.
-- When publishing and subscribing to replicate, you can use the server's real name as follows:
select * from sysservers (the original server name can be found)
exec sp_dropserver 'jmsql9' (delete the original server name)
exec sp_addserver 'jmSQL9' ,LOCAL (changed to new server name)
ALTER DATABASE [jm] SET SINGLE_USER (changed to single user mode)
DBCC CHECKDB("databasename",REPAIR_REBUILD) WITH TABLOCK (repair database)
DBCC CHECKTABLE("tablename",repair_rebuild) with tablock (repair table)
DBCC DBREINDEX ('t_icitem' , ' ') Fix all indexes in this table.
ALTER DATABASE [jm] SET MULTI_USER (changed to multi-user mode)
REPAIR_ALLOW_DATA_LOSS: Performs all fixes done by REPAIR_REBUILD, including allocating and deallocation of rows and pages to correct allocation errors, struct rows or pages, and deleting corrupted text objects. These fixes may cause some data loss. The repair operation can be done under user transactions to allow the user to roll back changes. If the rollback fix is done, the database will still contain an error and should be restored from the backup. If a fix for a bug is missed due to the provided fix level, any fixes that depend on that fix will be missed. After the repair is complete, back up the database.
REPAIR_FAST performs small, time-consuming repair operations, such as repairing additional keys in nonclustered indexes. These fixes can be done quickly and there is no risk of losing data.
REPAIR_REBUILD performs all repairs done by REPAIR_FAST, including repairs that require a long time (such as rebuilding indexes), without risk of losing data when performing these repairs.
dbcc shrinkdatabase (jm) compressed database
This article comes from the CSDN blog. Please indicate the source when reprinting: /fredrickhu/archive/2009/10/14/
--★★Get the steps to execute the job★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
select ,s.step_id,s.step_name,
from .sysjobs_view j join s
on j.job_id=s.job_id;
--★★View deadlocks and blocking and kill them★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
if exists (select * from where id = object_id(N'[dbo].[p_lockinfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_lockinfo]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*--Troubleshoot deadlock
Check the current process, or deadlock process, and automatically kill the dead process
Because it is for dead, if there is a deadlock process, you can only view the deadlock process if there is a deadlock process
Of course, you can control it through parameters, regardless of whether there is a deadlock or not, just check the deadlock process.
Thanks: caiyunxia,jiangopen, two of you provide reference information
--Zou Jian 2004.4--*/
/*--Call Example
exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1, --Whether to kill the deadlock process, 1 Kill, 0 only display
@show_spid_if_nolock bit=1 --If there is no deadlock process, will normal process information be displayed? 1 is displayed, 0 is not displayed
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1), flag,
Process ID=spid, thread ID=kpid, block process ID=blocked, database ID=dbid,
Database name = db_name(dbid), user ID = uid, user name = loginame, cumulative CPU time = cpu,
Login time = login_time, number of open transactions = open_tran, process status = status,
Workstation name = hostname, application name = program_name, workstation process ID = hostprocess,
Domain name = nt_domain, network card address = net_address
into #t from(
select flag = 'Deadlocked process',
spid,kpid,,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on = where =0
union all
select '|_Sacrificial_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2
select @count=@@rowcount,@i=1
if @count=0 and @show_spid_if_nolock=1
begin
insert #t
select flag = 'normal process',
spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
from master..sysprocesses
set @count=@@rowcount
end
if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@flag varchar(10)
while @i<=@count
begin
select @spid=process ID,@flag=flag from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @ flag = 'Deadlocked process' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(process ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*, the SQL statement of the process =
from #t a join #t1 b on =
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------- Version 2: I rewritten (KILL deadlock process with a deadlock time of more than 15 seconds ------------------------------------------------------------------------------------------
/*
exec p_lockinfo 0,1;
*/
alter proc p_lockinfo
@kill_lock_spid bit=1, --Whether to kill the deadlock process, 1 Kill, 0 only display
@show_spid_if_nolock bit=1 --If there is no deadlock process, will normal process information be displayed? 1 is displayed, 0 is not displayed
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1), flag,
Process ID=spid, thread ID=kpid, block process ID=blocked, database ID=dbid,
Database name = db_name(dbid), user ID = uid, user name = loginame, cumulative CPU time = cpu, waiting time = waittime,
Login time = login_time, number of open transactions = open_tran, process status = status,
Workstation name = hostname, application name = program_name, workstation process ID = hostprocess,
Domain name = nt_domain, network card address = net_address
into #t from(
select flag = 'Deadlocked process',
spid,kpid,,dbid,uid,loginame,cpu,waittime,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on = where =0
union all
select '|_Sacrificial_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,waittime,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2
select @count=@@rowcount,@i=1
if @count=0 and @show_spid_if_nolock=1
begin
insert #t
select flag = 'normal process',
spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,waittime,login_time,
open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
from master..sysprocesses
set @count=@@rowcount
end
if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(4000))
if @kill_lock_spid=1
begin
declare @spid varchar(10), @ flag varchar(10), @wait time int
while @i<=@count
begin
select @spid=process ID,@flag=flag, @wait time=wait time from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @ flag = 'Deadlocked process' and @wait time >=15000 exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(process ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*, the SQL statement of the process =
from #t a join #t1 b on =
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Previous page12Read the full text