Database log management
1 log management
When the log of SQL Server reaches a certain extent, it will block the processing of other transactions, causing the main reasons for log overflow:
1: The system management does not clear the log in time. SQL Server is under the default value, you must regularly back up data and logs, and logs cannot be automatically cleared.
2: Due to the implementation of the illegal statement, if a UPDATE statement is performed, the conditions unlimited generated log is caused until the system crashes. For different error reasons, the seriousness of the error takes different solutions:
1: The log overflows caused by small matters, and the system can start normally. Workaround: 1. Expand Database Log Space: ALTER DATABASE Database Name ON Device Name = Quantity (M) sp_logDevice Database Name, Device Name
2. Clear the log dump Transaction database name with no_log (no_truncate)
2: The log overflow is caused by big things, and the system is not available for a long time or the database cannot be restored: 1. Waiting. For longer transaction recovery time, in Wuxi, once the user performs a SQL statement that updates user information, due to the use of errors, the log overflows, and the recovery log is 14 hours. When logging is completed, the system can run normally.
2. Forcibly empty the log. Methods for an forced emptying log can be used in the case where the database is unable to restore the database or a recent backup. The consequences of this method may be thoroughly destroyed. The steps are as follows:
I Start SQL Server (No Detection Log) II in -V II Modifying Database Status to -32768 (Block Status) Update SysDatabases Set Status = -32768 WHERE Name = Database Name III Authorized Sybase_ts_Role Permissions (Sybase_ts_Role is SQL Server Special Administrator privilege, In daily database management, this role does not need this role) sp_role "grant", "Sybase_ts_role", Saset Role "Sybase_ts_role" IV Clear Log DBCC Rebuild_Log (Database Name, 1, 1)
After completing the above steps, restart SQL Server. If the database can start normally, the database is restored; if you can't start, you can only recreate the database.
Avoid generating log overflows: artificial methods and automatic methods.
Artificial Method: To periodically back up logs and clear use, to increase the space available for logging according to the different amount of task. Advantages: Safety, when the database is broken, it can restore the log of last backup. Disadvantages: It is possible to overflow log overflow for long-term statements.
Automatic Method: Valve the overflow of the log in real time through the threshold of the data. Advantages: There is no log overflow. Disadvantages: Logs may be lost, increase the burden on database processing. The threshold creation method is as follows: 1: Create a segment sp_addsegment section name, database name, device name related process: sp_helpsegment, sp_dropsegment
2: Create a valve sp_addthreshold database name, segment name, free space, execution process name related process: sp_helpthreshold, sp_modifythreshold, after the valve is created, when the idle space reaches the idle space defined by the valve, the Server automatically activates the threshold definition. The stored procedure name. This stored procedure processes the log. The following is an example of the simplest processing process. Create procedure sp_thresholdaction @ dbname varchar (30), @ segmentname varchar (30), @ space_left int, @ status intasdump transaction @dbname to tapedump1print "LOG DUMP: '!% 1' for '!% 2' dumped", @ segmentname, @dbname
/ * Back up the log to the tape, then print the segment name of the backup, database name * /
2 detection of consistency
Database If you often abnormally shortdown may cause an error in data; a long run is possible to cause an index. Direct consequences are obviously declining the running speed, and the BCP backup data (BCP execution result is incorrect). In addition, it will affect the allocation of physical space. The database detection can be implemented by the database, the most important process is as follows:
1: DBCC CHECKCATALOG (Database Name) Check the consistency problem of data in the system data table, such as verifying a type in the syscolumns table contains a line in the SYSTYPES table.
2: DBCC CHECKDB (Database Name, Skip_ncindex) Check the spatial allocation of each table in the database. When data dumps, methods with Skip_ncindex parameters can be used to speed up the check speed.
3: DBCC CheckTable Checks the allocation information of each table, contains the following: page connection, index sorting, pointer consistency, the data line on each page In the Object Assignment Mapping (OAM) page.
4: DBCC Checkalloc (Table | Database Name, FULL, NOFIX) Checking the verified page is all correctly assigned, the unallocated page is not part of the page chain, and the page that is not part of the page chain is not The tag is assigned. In addition to the assigned pages, Checkalloc also checks all assigned pages in the database to make sure they contain valid information.
Damage
SQL Server 6.5:
Update sysdatabases set status = -32768 where name = 'database name'
Update sysdatabases set status = 12 where name = 'database name'
DBCC CHECKDB ('Database Name ")
DUMP Transaction Database Name with no_log
Database restart
SQL Server 7.0:
Solution:
This is the easiest way to have a full backup of the database, and then restore it.
step:
1. Delete the original database:
Use master
Go
DROP DATABASE DB_SUEPECT
2. Establish the same name database:
Use master
Go
Create Database DB_SUSPECT
On
(Name = dbname_dat,
Filename = 'c:',
Size = 10,
FileGrowth = 5)
LOG ON
(Name = 'dbname_log',
Filename = 'g:',
Size = 5MB, FileGrowth = 5MB)
Go
3. Restore the database:
Restore Database DB_SUSPECT
From dbname_backup.dat
4. Database integrity detection:
DBCC Checkdb ('db_suspect')
5. Restart the MSSQLServer service.
If there is no full backup, then use some special methods:
1. Set the database to emergency mode
Use master
Go
sp_configure 'allow updates', 1
Reconfigure with override
Go
Update sysdatabases set status = 32768 where name = 'db_suspect'
Go
2. Stop the SQL Server service:
Net Stop MSSQLServer
3. Remove the data file DBNAME_DAT.mdf of the original database, DBNAME_LOG.LDF:
4. Start SQL Server service:
Net Start MSSQLServer
5. Re-establish a database DB_SUSPECT for the same name;
Use master
Go
Create Database DB_SUSPECT
On
(Name = dbname_dat,
Filename = 'c:',
Size = 10,
FileGrowth = 5)
LOG ON
(Name = 'dbname_log',
Filename = 'g:',
Size = 5MB,
FileGrowth = 5MB)
Go
6. Set the database to run in a single user:
Use master
Go
Alter Database DB_SUSPECT SET SINGLE_USER
Go
7. Stop SQL services:
Net Stop MSSQLServer
8. Cover the original data file back:
9. Start SQL Server service:
Net Start MSSQLServer
10. Reset the status of SQLServer:
Use master
Go
EXEC SP_RESETSTATUS "DB_SUSPECT"
11. Database integrity detection:
DBCC Checkdb ('db_suspect')
12. Restore the database for multi-user mode:
Use master
Go
Alter Database DB_SUSPECT SET MULTI_USER
Go
13. Restore the original configuration of SQLServer:
Use matter
Go
Update sysdatabases set status = 4194320 Where name = 'db_suspect'
Go
14. Configure SQLServer does not allow update system tables:
Use master
Go
sp_configure 'allow updates', 0
Reconfigure with override
Go
15. Restart the MSSQLServer service:
It is best to restart the operating system
16. Backup Database:
You can use SQLServer Enterprise Manager or T-SQL. Need to back up Master and DB_SUSPECT
ISQL strange phenomenon
Use SQLServer 7.0 isql to use on SQLServer 6.5 servers:
ISQL -S -P -U -IXXX.SQL
There will be an execution script is not completed. You need to press CTRL C to continue, plus -c parameters Some machines can solve, some can't.
After replacing the SQLServer 6.5 version of the isql.exe file can be successful.
SQL statement import export
Export to Excel
EXEC MASTER..XP_CMDSHEC 'BCP Northwind.dbo.region Out C: /Temp1.xls -c -q -s "gnetdata / gnetdata" -u "sa" -p ""
Import Excel
SELECT *
From OpenDataSource ('Microsoft.jet.OleDb.4.0',
'Data Source = "C: / Test.xls"; user ID = admin; password =; extended proties = excel 5.0') ... xactions
/ * Dynamic file name
Declare @fn varchar (20), @ s varchar (1000)
Set @fn = 'c: /test.xls'
Set @S = '' 'Microsoft.jet.OleDb.4.0' ',
'' Data Source = "' @ fn '"; user ID = admin; password =; extended Properties = Excel 5.0 '' '
Set @S = 'SELECT * OPENDATASOURCE (' @ s ') ... Sheet1 $'
EXEC (@S)
* /
SELECT CAST (CAST (Subject No. AS Numeric (10, 2)) AS NVARCHAR (255)) '' Convert alias
From OpenDataSource ('Microsoft.jet.OleDb.4.0',
'Data Source = "C: / Test.xls"; user ID = admin; password =; extended proties = excel 5.0') ... xactions
Excel Guide to Remote SQL
INSERT OpenDataSource
'Sqloledb',
'Data Source = Remote IP; User ID = SA; Password = Password'
). Name .dbo. Table name (column name 1, column 2)
SELECT column name 1, column name 2
From OpenDataSource ('Microsoft.jet.OleDb.4.0',
'Data Source = "C: / Test.xls"; user ID = admin; password =; extended proties = excel 5.0') ... xactions
Import text file
EXEC MASTER..XP_CMDSHEC 'BCP DBNAME..TABLENAME IN C: /DT.TXT -C -SSERVERNAME -USA-Password'
Export text file
Exec master..xp_cmdshell 'bcp dbname..tablename out c: /dt.txt -c -ssrvername -usa -ppassword'
or
Exec master..xp_cmdshell 'bcp "Select * from dbname..tablename" Queryout C: /Dt.txt -c -ssrvername -usa -ppassword "
Export to TXT text, separate the exec master..xp_cmdshell 'bcp "with comma" library name .. Table name "D: /tt.txt" -c -t, -u sa -p password'
DBASE IV file
SELECT * FROM
OpenRowSet ('Microsoft.jet.OleDb.4.0'
, 'DBASE IV; HDR = NO; IMEX = 2; Database = C: /', 'SELECT * FROM [Customer Data 4.DBF]')
DBASE III file
SELECT * FROM
OpenRowSet ('Microsoft.jet.OleDb.4.0'
, 'DBASE III; HDR = NO; IMEX = 2; Database = C: /', 'SELECT * FROM [Customer Information 3.dbf]')
FoxPro database
Select * from OpenRowSet ('msdasql',
'Driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = C: /',
'Select * from [aa.dbf]')
Import DBF file
Select * from OpenRowSet ('msdasql',
'Driver = Microsoft Visual FoxPro Driver;
SourceDB = E: / VFP98 / DATA;
SourceType = DBF ',
'Select * from customer where country! = "USA" ORDER BY Country')
Go
Export to DBF
If you want to export data to a structure (ie, existing) FoxPro list, you can directly use the following SQL statement
INSERT INTO OpenRowSet ('msdasql',
'Driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = C: /',
'Select * from [aa.dbf]')
SELECT * FROM table
Description:
SOURCEDB = C: / Specify the folder where the FoxPro table is located
AA.DBF Specifies the file name of the FoxPro table.
Export to Access
INSERT INTO OpenRowset ('Microsoft.jet.OleDb.4.0',
'x: /a.mdb'; 'admin'; '', a table) SELECT * FROM database name ..b table
Import Access
Insert Into B Sleet * from OpenRowSet ('Microsoft.jet.OleDb.4.0',
'x: /a.mdb'; 'admin'; '', a table)
File name is parameter
Declare @fname varchar (20)
Set @fname = 'd: /test.mdb'
EXEC ('SELECT A. * from OpenDataSource (' 'Microsoft.jet.OleDb.4.0' ",
'' ' @ fname ' ';' 'admin' ';' '' '', Topics AS A ') SELECT *
From OpenDataSource ('Microsoft.jet.OleDb.4.0',
'Data Source = "f: /northwind.mdb"; Jet OLEDB: Database Password = 123; User ID = Admin; PASSWORD =;') ... Product
Import XML file
Declare @IDOC INT
Declare @doc varchar (1000)
--Sample XML Document
Set @doc = '
Happy Customer.
'
- Create An Internal Representation of the xml document.
EXEC SP_XML_PREPAREDocument @idoc output, @doc
- Execute A Select Statement Using OpenXML ROWSET PROVIDER.
SELECT *
From OpenXML (@idoc, '/ root / customer / order ", 1)
With (Oid Char (5),
Amount float,
Comment nText 'text ()')
EXEC SP_XML_REMOVEDOCUMENT @IDOC
Guide the entire database
Stored procedure implemented with BCP
Implement data import / export stored procedures, according to different parameters, import / export can be implemented / exported throughout the database / single table
Call example:
- Export call example
---- Export a single table
Exec file2table 'zj', '', '', 'xzkh_sa .. Regional Information', 'C: /Zj.txt', 1 ---- Exporting the entire database
Exec file2table 'zj', '', '', 'xzkh_sa', 'C: / DOCMAN', 1
- Imported call example
---- Import a single table
Exec file2table 'zj', '', '', 'xzkh_sa .. Area,' c: / zj.txt' ,0
---- Import the entire database
Exec file2table 'zj', '', '', 'xzkh_sa', 'C: / DOCMAN', 0
* /
If EXISTS (SELECT 1 from sysobjects where name = 'file2table' and objectproperty (id, 'isprocedure') = 1)
Drop Procedure File2Table
Go
Create Procedure File2Table
@servername varchar (200) - server name
@ username varchar (200) - Username, if using NT authentication mode, it is empty ''
, @ Password Varchar (200) - Password
, @ TBNAME VARCHAR (500) - Database. DBO. Table name, if not specified: .dbo. Download, export all user tables for the database
@ filename varchar (1000) - Import / Export Path / File Name, if the @TBName parameter indicates that the entire database is exported, this parameter is the file storage path, the file name is automatically used .txt
@ isout bit --1 is exported, 0 is imported
AS
Declare @SQL VARCHAR (8000)
If @tbname like '%.%.%' - If the table name is specified, the single table is exported directly.
Begin
Set @ SQL = 'BCP' @ TBNAME
Case when @ ixout = 1 TEN 'OUT' ELSE 'IN' END
'"' @ filename '" / w'
'/ S' @ ServerName
Case When Isnull (@username, '') = '' TEN '' ELSE '/ U' @ Username End
'/ P' isnull (@password, '')
Exec master..xp_cmdshell @SQL
end
Else
Begin exports the entire database, define a cursor, remove all user tables
Declare @m_tbname varchar (250)
IF Right (@ filename, 1) <> '/' set @ filename = @ filename '/'
Set @ m_tbname = 'declare #tb cursor for select name from' @ TBNAME '.. sysobjects where xtype =' 'u' 'EXEC (@m_tbname)
Open #TB
Fetch next from #TB INTO @m_tbname
While @@ fetch_status = 0
Begin
Set @ SQL = 'bcp' @ TBNAME '..' @ m_tbname
Case when @ ixout = 1 TEN 'OUT' ELSE 'IN' END
'"' @ filename @ m_tbname '. txt" / w'
'/ S' @ ServerName
Case When Isnull (@username, '') = '' TEN '' ELSE '/ U' @ Username End
'/ P' isnull (@password, '')
Exec master..xp_cmdshell @SQL
Fetch next from #TB INTO @m_tbname
end
Close #TB
Deallocate #TB
end
Go
