Including the installation prompts the hang operation, shrink the database, compressed database, and transfer the database to new users to check the backup set, repair the database, etc.
(1) hang operation
When installing the SQL or SP patch, there is a hang-on installation operation before the system prompts, requires restart, often restarting the useless, a solution: to HKEY_LOCAL_MACHINE / SYSTEM / CURRENTCONTROLSET / Control / Session Manager Remove PendingFileerenameOperations
2) Shrink Database
- Reconstruction Index DBCC Reindexdbcc Indexdefrag - Shrink Data and Log DBCC Shrinkdbdbcc ShrinkFile
(3) Compressed database
DBCC ShrinkDatabase (DBNAME)
4) Transferring database to new users with existing user rights
EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'NEWNAME', 'OldName'go
(5) Check the backup set
Restore Verifyonly from disk = 'e: /dvbbs.bak'
(6) Repairing the database
Alter Database [DVBBS] Set Single_Usergodbcc Checkdb ('DVBBS', REPAIR_ALLOW_DATA_LOSS) with TablockGoalter Database [DVBBS] SET MULTI_USERGO
- CHECKDB has 3 parameters:
REPAIR_ALLOW_DATA_LOSS
- Perform all fixes completed by REPAIR_REBUILD, including allocation and unassigning and unassigning to correct allocation errors, structural rows or pages, and deleting corrupted text objects. These repairs may result in some data loss. Repair operations can be done under user transactions to allow users to roll back. If rollback fixes, the database still contains errors and should be recovered from backup. If an error is missing, any repair depending on the repair is missing because the repair level is provided. After the repair is complete, the database is backed up. --RePair_fast is small, not time consuming, such as repairing additional keys in the non-aggregated index. These repairs can be completed quickly and will not have the risk of losing data.
--Repair_Rebuild Performs all fixes completed by Repair_Fast, including repair required for a longer period of time (such as rebuilding indexes). Do not have the risk of losing data when performing these fixes.
--DBCC CHECKDB ('DVBBS') with no_infomsgs, physical_only
Two methods of SQL Server log clearance are in the process of use, often encountering the status of database logs, here, introduced two processing methods ...
method one
In general, the shrinkage of the SQL database does not greatly reduce the size of the database. Its main role is to shrink the log size, and this will be performed regularly to prevent the database log.
1. Setting the database mode as simple mode: Open SQL Enterprise Manager, click on the Microsoft SQL Server -> SQL Server Group -> Double click to open your server -> Double click to open your database -> Double click to open the Database directory - > Choose your database name (such as forum database forum) -> then right click to select Properties -> Select Options -> Select "Simple" in the fault reduction mode, then press OK to save
2. Right-click on the current database to see the shrinkage database in all tasks, the default settings in general, do not adjust, direct point to determine
3
Set NoCount Ondeclare @LogicalFileName Sysname, @ Maxminutes Int, @ Newsize Int
Use tablename - Database name to be operated SELECT @LogicalFileName = 'Tablename_log', - log file name @maxminutes = 10, - limit on time allowed to wrap log. @ Newsize = 1 - You want to set the log file you want to set Size (m)
- Setup / initializeDECLARE @OriginalSize intSELECT @OriginalSize = size FROM sysfilesWHERE name = @LogicalFileNameSELECT 'Original Size of' db_name () 'LOG is' CONVERT (VARCHAR (30), @ OriginalSize) '8K pages or' Convert (VARCHAR (30), (@ OriginalSize * 8/1024)) 'mb'From sysfileswhere name = @LogicalFileNamecreate Table DummyTrans (DummyColumn Char (8000) Not null
Declare @counter int, @ starttime datetime, @ trunclog varchar (255) select @starttime = getdate (), @ Trunclog = 'backup log' db_name () 'with truncate_only'
DBCC SHRINKFILE (@LogicalFileName, @NewSize) EXEC (@TruncLog) - Wrap the log if necessary.WHILE @MaxMinutes> DATEDIFF (mi, @StartTime, GETDATE ()) - time has not expiredAND @OriginalSize = (SELECT size FROM Sysfiles where name = @LogicalFileName) And (@originalsize * 8/1024)> @newsize begin - Outer loop.select @counter = 0WHile ((@counter <@originalsize / 16) and (@counter <50000)) Begin - - updateINSERT DummyTrans VALUES ( 'Fill Log') DELETE DummyTransSELECT @Counter = @Counter 1END EXEC (@TruncLog) END SELECT 'Final size of' db_name () 'LOG is' CONVERT (VARCHAR (30), size) '8K Pages Or' Convert (VARCHAR (30), (Size * 8/1024)) 'MB'From sysfiles where name = @LogicalFileNameDrop Table DummyTransset NoCount OFF Remove several method databases of duplicate data in the database Sometimes due to programs sometimes encounter repetitive data, duplicate data has caused database part settings that cannot be set correctly ...
method one
declare @max integer, @ id integerdeclare cur_rows cursor local for select primary field, count (*) from table group by the main field having count (*)> 1open cur_rowsfetch cur_rows into @ id, @ maxwhile @@ fetch_status = 0beginselect @max = @max -1set rowcount @maxdelete from table name Where main field = @idfetch cur_rows INTO @ ID, @ MAXENDCLOSE CUR_ROWSSET ROWCOUNT 0
Method Two
There are two sense recording, one is a full repeated record, that is, all fields are repeated records, and the other is a repeated record of some of the key fields, such as the Name field repeat, and other fields are not necessarily duplicated or repeated. ignore. 1. For the first repetition, it is easier to solve, using the Select Distinct * from TableName, you can get the result set without duplicate records. If the table needs to delete repetitive records (repeated record reserved 1), you can delete Select Distinct * Into #TMP from TablenameDrop Table Tablenameselect * Into Tablename from #tmpdrop table design is not based on the following method. The week is generated, and the unique index can be addressed. 2, this type of repetition problem typically requires the first record in the repeated record, the operation method is assumed to have a repetitive field as name, address, requiring the unique result set of these two fields SelectiTITY (int, 1, 1) as autoID, * into #Tmp from tableNameselect min (autoID) as autoID into # Tmp2 from #Tmp group by Name, autoIDselect * from #Tmp where autoID in (select autoID from # tmp2) to obtain the last select Name, Address not Duplicate result set (but there is more autoid field, you can write this column in the SELECT clause when you are writing)
Two ways to change the users of the database in the database may often encounter a database backup to restore to another machine results that cause all the tables that cannot be opened, the reason is that the database users at the time ...
- Change a table EXEC SP_CHANGEOBJECTOWNER 'TABLENAME', 'DBO'
- Storage Change All Table Create Procedure DBo.user_changeObjectownerbatch@oldowner as nvarchar (128), @ newowner as nvarchar (128) AS
Declare @name as nvarchar (128) Declare @owner as nvarchar (128) Declare @Ownername as nvarchar (128)
Declare curobject cursor for select 'name' = name, 'oowner' = user_name (uid) from sysobjectswhere user_name (uid) = @ OldownerRDer by name
OPEN curObjectFETCH NEXT FROM curObject INTO @Name, @OwnerWHILE (@@ FETCH_STATUS = 0) BEGIN if @ Owner = @ OldOwner beginset @OwnerName = @OldOwner '.' Rtrim (@Name) exec sp_changeobjectowner @OwnerName, @ NewOwnerend-- Select @ name, @ newowner, @ Oldowner
Fetch next from curobject @Name, @Ownerend
Close Curobjectdeallocate Curobject
Go
There is no good to write directly to the data in SQL Server. Everyone looks, sometimes it is a bit used.
Declare @i int set @ i = 1WHILE @i <30beginInsert Into test (userid) VALUES (@i) set @ i = @ i 1nd
No data library log file recovery database method Two database log files erroneous deletion or other causes of database logs Causes Data Log Logs Home | News | Network | System | Development | Other Certification | Course | Course | Find Work | Find Talents | Service Guide You location: recruitment and training> other certification section> database columns> other database SQL SERVER skill sets practical experience source: 9CBS.NET editor: lujiezhen [04-9-13 10:22] author: longrujun
method one
1. Create a newly named database
2. Stop SQL Server again (Be careful not to separate the database)
3. Overwate this newly built database with the original database data file
4. Restart SQL Server
5. At this time, you will be suspicious when you open the Enterprise Manager. Whether you do this, you will do the following statement (pay attention to the database name)
6. The data in the database can be accessed after completion. At this time, the database itself is generally issued, the solution is to create a new database using the database's script and will pass the data.
Use mastergo
SP_Configure 'Allow Updates', 1 Reconfigure with Overridego
Update sysdatabases set status = 32768 where name = 'quoted database name' Go
SP_DBOPTION 'Troubleshooting Database Name,' Single User ',' True'go
DBCC CHECKDB ('Troubleshooting Database Name) GO
Update sysdatabases set status = 28 where name = 'quoted database name' Go
sp_configure 'allow updates', 0 Reconfigure with Overridego
SP_DBOPTION 'Troubleshooting Database Name', 'Single User', 'False'go
Method Two
The cause of things yesterday, the system administrator told me that the disk space where we have internal application database is inadequate. I noticed that the database event log file XXX_DATA.LDF file has grown to 3GB, so I decrease the log file. After the failure of the contracting database, I made a maximum and fooling error since I have entered the industry: I accidentally deleted this log file! Later, I saw all the discussions and database recovery articles: "If you have to ensure that the database log file exists, it is critical, even Microsoft even has a KB article how to recover only by log files. I really don't know what I think at that time? !
This is broken! This database can't be connected, and the enterprise manager is written next to its "(quenus)". And the most important, this database never backed up. I found some of the database server for half a year ago, it can be used, but there are fewer records, tables, and stored procedures. I really hope this is just a nightmare!
Recovery steps that have no effect, attached database _Rambo speechless when there is no activity log in the deleted log file, can be recovered:
1. Separate the quenched database, you can use sp_detach_db2, additional database, you can use sp_attach_single_file_db but, unfortunately, after execution, SQL Server questioned that the data file and log file do not match, so you can't attach the database data file.
The DTS data export cannot be read, and the XXX database cannot be read. The DTS Wizard reports "Initialization context has an error".
Emergency mode Yihong Bani said that there is no log for recovery, you can do this:
1. Set the database to Emergency Mode
2, re-establish a log file
3, restart SQL Server
4, set the application database to single user mode
5, do DBCC Checkdb
6. If there is no big problem, you can change the status status. Remember to turn off the system table to turn off the system table | Dynamic | Network | System | Development | Other Certification | Course | Course | Find Work | | service Guide You are here: recruitment and training> other certification section> database columns> other database SQL SERVER skill sets practical experience source: 9CBS.NET editor: lujiezhen [04-9-13 10:22] author: longrujun
I practiced, remove the data file applying the database, re-establish a database XXX of the same name, then stop the SQL service, and then override the original data file back. Thereafter, walk according to the steps of the Yihong Mon.
However, it is also unfortunate that other steps are very successful in addition to step 2. Unfortunately, after restarting SQL Server, this application database is still quilted!
However, let me be pleased, after doing this, it is able to select data, let me take a breath. However, when the component uses the database, the report says: "An error occurred: -2147467259, Begin Transaction cannot be run in the database 'xxx' because the database is in an evasive recovery mode."
Finally, all steps successfully set up the database to stop the SQL Server service in the emergency mode;
Remove the data file XXX_DATA.MDF of the application database;
Re-establish a database xxx;
Stop SQL service;
Cover the original data file back;
Run the following statement, set the database to emergency mode;
Run "Use Master
Go
sp_configure 'allow updates', 1
Reconfigure with override
Go "
Results of the:
DBCC is executed. If DBCC outputs an error message, contact your system administrator.
The configuration option 'allow updates' has been changed from 0 to 1. Run the Reconfigure statement to install.
Then run "update sysdatabase set status = 32768 where name = 'xxx'"
Results of the:
(The number of rows affects is 1 line)
Restart SQL Server service;
Run the following statement, set the application database to Single User mode;
Run "sp_dboption 'XXX', 'Single User', 'True'"
Results of the:
The command has been successfully completed.
ü make DBCC CHECKDB;
Run "DBCC Checkdb ('XXX')"
Results of the:
'Xxx' DBCC results.
'sysObjects' DBCC results.
Object 'sysObjects' has 273 rows, which are located in page 5.
'sysindexes' DBCC results.
Object 'sysIndexes' has 202 rows, which are located in page 7.
'syscolumn' 's DBCC results.
.........
ü Run the following statement to turn off the modification options for the system table;
Run "sp_resetstatus" xxx "
Go
sp_configure 'allow updates', 0
Reconfigure with override
Go "
Results of the:
Update the entry of the database 'xxx' in sysdatabases, mode = 0, status = 28 (status Suspect_bit = 0),
No rows in sysdatabases are updated because the mode and status have been properly reset. There is no error, no changes are made.
DBCC is executed. If DBCC outputs an error message, contact your system administrator.
The configuration option 'allow updates' has been changed from 1 to 0. Run the Reconfigure statement to install. Home | Dynamic | Network | System | Development | Other Certification | Courses | Finding | Find Talents | Service Guide Your present location: Recruitment and Training> Other Certification Columns> Database Serand> Database Other SQL Server Practical Experience Tips : 9CBS.NET editor: lujiezhen [04-9-13 10:23] author: longrujun
Re-establish another database xxx.lost;
DTS Export Wizard Transmission DTS Export Wizard;
Copy Source Select EmergencyMode's database XXX, import to XXX.LOST;
Select "Replication objects and data between SQL Server Database", trying multiple times, it seems that you can't copy all table structure, but there is no data, no view and stored procedures, and the DTS wizard finally reports the replication;
So finally select "Source Database Copy Table and View", but later discover, this always can only copy a part of the table record;
So select "Data to be transferred with a query", which is missing which table record, which is
The view and stored procedures are added to the SQL statement.
Maintaining the index of the table in SQL Server often encounters some problems in use and creating a database index, where some alternative methods can be used here ...
- Step 1: Check if you need to maintain, check if scan density / scan dens is 100% declare @table_id int set @ Table_ID = Object_ID ('Name') DBCC ShowContig (@table_id)
- Step 2: Reconstruction of the table index DBCC DBREINDEX ('Name ", PK_ index name, 100)
- Take the first step, if you find that the scan density / scan density is still less than 100%, all indexes of the reconstructed table - Yang Wei: not necessarily 100%. DBCC DBREINDEX ('Name ",' ', 100) SQL Server patch installation FAQ Who will take a look at the problem :)
First, the common problem during the patch installation
If you encounter the following matches when installing the patch:
1. "The previous program creates a suspended file operation during the installation process. Before running the installer", follow these steps:
a, restart the machine, then install, if there is still this error, press Step B, enter RegeDit C in Start -> Run to HKEY_LOCAL_MACHINE / SYSTEM / CURRENTCONTROLSET / Control / Session Manager Location D, Select File -> Pour out, save E, right-click PendingFileerenameOperation on the right window, select Delete, then confirm F, restart the installation, problem solving
If there is also the same problem, please check if there is this value in other registry, if you have any deletion.
2, in installing SQL Server SP3, sometimes appears: Whether you use Windows certification or mixing authentication, you have a password error. At this time, you will find the following description: [TCP / IP Sockets] Specified SQL Server Not Found. [TCP / IP Sockets] ConnectionOpen ()). In fact, this is a small bug of SQL Server SP3. When installing SP3, there is no listening to the TCP / IP port, and you can follow the steps below:
1. Open the SQL Server Client Network Utility and Server Network Tool to ensure that the enabled protocol contains Name Pipe, and the location is in the first bit. 2. Make sure [HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLServer / Client / ConnectTo] "DSQuery" = "DBNetLib". If not, build 3 itself, stop MSSQL. 4, and install it.
This will be installed correctly.
Second, SQL Server patch version check
SQL Server patch is not as good as the Windows patch version check, a system administrator, if you don't understand the patch number corresponding to the SQL Server version, may also encounter a little trouble, so in this explanation, through such a way to determine the machine is Safety methods do not have any impact on the system. 1. Log in to SQL Server with the ISQL or SQL query analyzer. If you use ISQL, enter ISQL -U SA in the CMD window, then enter your password, enter; if you use the SQL query analyzer, start, enter, enter SA and passwords (you can also verify using Windows). 2, type in isql: select @@ Version; Go
Or input in the SQL query analyzer (in fact, if you don't want to enter, just open your help about you :)) Select @@ version; then press execution; then return SQL version information, as follows: Microsoft SQL Server 2000 - 8.00 .760 (Intel x86) DEC 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3) 8.00.760 is the version and patch number of SQL Server . The correspondence is as follows: 8.00.194 ------- SQL Server 2000 RTM 8.00.384 ------- (SP1) 8.00.534 ------ (SP2) 8.00.760 --- ---- (SP3)
This way we can see the correct version and patch number of SQL Server.
We can also see more detailed information with XP_MSVER
SQL Server database backup and recovery measures are the most commonly used operation, novices look at ...
First, backup database
1. Open the SQL Enterprise Manager, click on Microsoft SQL Server2, SQL Server Group in the Contents of the Control Bengen -> Double click to open your server -> Double click to open the database directory 3, select your database name (such as forum database) Forum) -> Then click on the tool in the menu -> Select Backup Database 4, the Backup Options Select full backup, the backup in the destination is to select the name point to delete if the original path and name, then add, if it is not The path and name are directly selected, then specify the path and file name, specify the post point to determine the backup window, then determine backup Home | Dynamic | Network | System | Development | Other Certification | Course | Course | Finding Work | | service Guide You are here: recruitment and training> other certification section> database columns> other database SQL SERVER skill sets practical experience source: 9CBS.NET editor: lujiezhen [04-9-13 10:23] author: longrujun
Second, restore the database
1. Open SQL Enterprise Manager, click on the Microsoft SQL Server2, SQL Server Group in the Contents of the Console, SQL Server Group -> Double click to open your server -> Click the new database icon, the name of the new database is 3 Click on the newly entered database name (such as the forum database forum) -> then click Tools in the Menu -> Select Restore Database 4. Select from Devices in the restore option in the pop-up window -> Point selection device -> Click Add -> and select your backup file name -> Add the point OK to return, then the device bar should appear the name of the database backup file you just selected, the backup number is 1 (if you are the same The file has been made multiple backups. You can click on the view next to the backup number. Select the latest backup after the check box) -> then click the option button next to the normal side 5, select in the window appearing Forced restore on existing databases, as well as options that make the database can continue to run but cannot restore other transaction logs in the recovery completion state. Restoring the database file for the intermediate part of the window To set up the installation of your SQL installation (you can also specify your own directory), the logical file name does not need to be changed, move to the physical file name to do according to your recovery machine situation Change, such as your SQL database in D: / Program Files / Microsoft SQL Server / MSSQL / DATA, then follow the directory of your recovery machine to change, and the last file name is best to change your current database name (If it is bbs_data.mdf, now the database is forum, change to forum_data.mdf), log and data files must be related to the related changes in this way (the file name of the log is * _log.ldf), Here you can freely set, provided that this directory must exist (if you can specify D: /SQLDATA/BBS_DATA.MDF or D: /SQLDATA/BBS_LOG.LDF), otherwise recovery will report an error 6, after the modification is complete, click The following determination is restored, then a progress bar, prompting the progress of recovery, the system will automatically prompt after the recovery is completed, such as the intermediate prompt error, please record the relevant error content and ask the person familiar with SQL operation, General errors are nothing more than directory error or file name duplicate or file name error or space is not enough or the database is in use, and the database is using an error. You can try to close all the SQL windows and reopen the recovery operation, if you still prompt you Using an error can stop the SQL service and then restarted, as for the above other errors, you can recover three, shrink the database after the error content is changed accordingly.
Under normal circumstances, the shrinkage of the SQL database does not greatly reduce the size of the database. The main role is to shrink the log size. This action should be performed regularly to prevent the database log excessively 1. Setting the database mode as simple mode: Open SQL Enterprise Management In the control station root directory, click Microsoft SQL Server -> SQL Server Group -> Double click to open your server -> Double click to open the database directory -> Select your database name (such as the forum database forum) -> Then click Right click Select Properties -> Select Options -> Select "Simple" in the fault restore mode, then press OK Save 2, right-click on the current database, see the contraction database in all tasks, generally The default setting is not adjusted, the direct point is determined 3, after the shrink database is complete, it is recommended to reset your database properties to standard mode, the operation method is the first point, because the log is often an important basis for restoring the database in some abnormalities.
Fourth, set the daily automatic backup database strongly recommend that there is conditional user to do this! 1. Open Enterprise Manager, click on the Microsoft SQL Server -> SQL Server Group -> Double click on your server 2 in the root directory of the Control Station -> Select the Database Maintenance Planner above the menu 3, Next Select data to be automatically backed up -> Next update data optimization information, here usually do not do the choice -> Next check data integrity, it is generally not selected 4, the next specified database maintenance plan, default One week backup once, click Change Select Once After you back up, you can determine 5. Next Specify the backup disk directory, select the specified directory, such as you can create a directory in D: DATABAK, then choose to use here This directory, if your database is better to choose to create a subdirectory for each database, then select Remove how many days ago, you will set 4-7 days, see your specific backup requirements, backup file extensions Names are generally BAK to use the default 6, the next step specify the transaction log backup plan, see your needs to do the choice -> The report to generate, generally do not choose -> Next Maintenance plan history, most It is easy to use the default option -> Next step 7, the system is probably prompting the SQL Server Agent service not started, first determine the completion plan setting, then find the SQL green icon in the rightmost status bar in the desktop, double click Open, select the SQL Server Agent in the service, then click the run arrow, select the automatic start service 8 when the OS is started, this time the database plan has been successful, and he will automatically back up the above settings.
Modifying plan: 1. Open Enterprise Manager, click on Microsoft SQL Server in the Contents Grant Catalog -> SQL Server Group -> Double click to open your server -> Management -> Database Maintenance Plan -> Open After you can see your settings, you can modify or delete the operation.
V. Data transfer (new database or transfer server)
In general, it is preferable to perform transfer data using backup and restore operations. In special cases, it can be transferred in a special way. This is an imported export method, and an imported export mode transfer data is that one can be used in a shrink database. Under the case where the size of the database is used to reduce the size of the database, this operation is default that you have a certain understanding of the operation of SQL. If you don't understand the part of these operations, you can contact the relevant personnel or inquiry online information 1, Export all the tables, stored procedures, stored procedures into a SQL file, pay attention to the option to write index scripts and write primary keys, foreign keys, defaults, and check constraint script options 2, create new databases The SQL file 3 established in one step is imported into the original database into the original database in the original database.
Using database log recovery data to time point operations due to abnormal data loss, do not want to use backup data restoration, as long as there is backup and current log storage is intact, you can use this method to try, maybe you can save losses ...
1. If there is a full storage backup (or have multiple differential backups or incremental backups) before mistaken, the first thing to do is to enter a log backup (if you don't make the log file to get Trunc. Log On Chkpt. Option 1 Then you will die) Backup log dbname to disk = 'filename'2, restore a full storage backup, pay attention to use with Norecovery if there are other differences or incremental backups, restore restore one by one Database dbname from disk = 'filename' with norecovery3 Operation can be done in the SQL Server Enterprise Manager, the difficulty is not difficult. . .
Of course, if the misoperation is some operations that do not remember the log, such as Truncate Table, SELECT INTO, etc., it is not possible to recover data by the above method ...
How to restore this problem when the SQL Server2000 database file is corrupted, can be repaired, can only look at your luck ...
In the SQL Server2000, if the database file (non-system database file) is incorrect, only the database of non-MASTER, MSDB is applied.
described as follows:
1 Construction of a test database TEST (Database type is complete) 2 built a table, insertion point record Create Table A (C1 varchar (2)) GoInsert Into a Values ('aa') GoInsert Into a Values ('bb') Go3 Fully backed up, to file test_1.bak4 in making something INSERT INTO A VALUES ('cc') GOCREATE TABLE B (C1 INT) GoInsert Into B Values (1) GoInsert Into B Values (2) GO5 Shutdown Database Server 6 with UltraEdit Edit Database file test_data.mdf, casually modify the point byte content, which is equivalent to the database being fatal damage. 7 Start the database, and run the Enterprise Manager, click on the database, see TEST to become gray, and display it. 8 Run isql -slocalhost -usa -p1> backup log test to disk = 'd: program filesmicrosoft sql servermssqlbackup est_2.bak' with no_truncate2> Go has processed 2 pages, these pages belong to the database 'test' file 'test_log' (in Document 1). The Backup log operation has been successfully handled 2 pages, which took 0.111 seconds (0.087 MB / sec).
9 Recover the oldest full backup 1> Restore Database test from disk = 'd: program filesmicrosoft SQL ServerMssqlbackup Est_1.bak' with Norecovery2> Go has processed 96 pages, these pages belong to the database 'test' file 'test_data' (in Document 1). Process 1 pages, these pages are file 'test_log' (on file 1). Restore Database Successfully handles 97 pages, spent 0.107 seconds (7.368 MB / sec). 10 Restore the nearest log 1> Restore log test from disk = 'd: program filesmicrosoft SQL ServerMssqlbackup est_2.bak' with recovery2> Go has processed 2 pages, these pages belong to the database 'test' file 'test_log' (on file 1 ). Restore log operations have been successfully processed by 2 pages and spent 0.056 seconds (0.173 MB / sec). Home | Dynamic | Network | System | Development | Other Certification | Courses | Finding | Find Talents | Service Guide Your present location: Recruitment and Training> Other Certification Columns> Database Serand> Database Other SQL Server Practical Experience Tips : 9CBS.NET editor: lujiezhen [04-9-13 10:23] author: longrujun
The memory process is written in experience and optimization measures, see ...
First, suitable for the reader object: Database development programmer, database data volume involves an optimized project developer for SP (stored procedures), people with strong interest in the database.
Second, the introduction: In the development of the database, complex business logic and the database are often encountered, this time will be encapsulated with the database operation. If the project's SP is more, there is no specification, and it will affect the future system maintenance difficulties and the difficult to understand of the large SP logic. In addition, if the data volume of the database is large or the project's performance requirements of the project, it will encounter Optimization issues, otherwise the speed may be very slow, after personal experience, an optimized SP is more than one hundred times higher than the efficiency of the Poor SP.
Third, the content:
1. If the developer uses the Table or View used to other libraries, it is important to create a cross-library operation in the current library. It is best not to use "Database.dbo.table_name" directly because sp_depends cannot display the SP. Cross library Table or View, it is inconvenient to check.
2. Developers must have used SET SHOWPLAN ON to analyze the query plan, and have been used to optimize inspection.
3, high program operation efficiency, optimize applications, should pay attention to the following points during SP writing:
a) SQL usage specification:
i. Try to avoid big business operations, use the HOLDLOCK clause to improve the system.
II. Try to avoid repeated access to the same or several tables, especially those with large data volume, can consider extracting data into the temporary table according to the conditions, and then connect.
III. Try to avoid using a cursor, because the efficiency of the cursor is poor, if the data of the cursor exceeds 10,000, then it should be rewritten; if a cursor is used, it is necessary to avoid the operation of the table connection in the cursor cycle. IV. Note that WHERE Form Writings must consider the order of statement, and should determine the front-rear order of the condition clauses according to the index order and range size, as much as possible to make the field order consistent with the index sequence, and the range is from large to small.
v. Do not perform functions, arithmetic operations, or other expression operations on the "=" on the WHERE clause, otherwise the system may not use indexes correctly.
Vi. Try to use exists instead of Select Count (1) to determine whether there is a record, the count function is only used when all the number of rows in the statistics table, and count (1) is more efficient than count (*).
VII. Try to use "> =", do not use ">".
VIII. Note some alternatives between OR clauses and UNION clauses
IX. Note that the data type connection between the tables is avoided between different types of data.
x. Note the relationship between parameters and data type during storage.
Xi. Note the amount of data for INSERT, UPDATE, prevent conflicts with other apps. If the amount of data exceeds 200 data pages (400K), the system will be upgraded, and the page lock will be upgraded to a table-level lock.
b) Norm specification of the index:
i. The creation of indexes should be considered with the application, and the big OLTP table should not exceed 6 indexes.
II. Use the index field as much as possible as query conditions, especially clustered indexes, if necessary, can force the specified index by index index_name.
Iii. Avoiding Table Scan when querying a big table, consider new indexes if necessary.
IV. When using the index field as a condition, if the index is a federated index, the first field in this index must be used as a condition to ensure that the system can be used to use the index, otherwise the index will not be used.
v. Pay attention to the maintenance of the index, periodically reconstruct the index, recompile the stored procedure.
c) Tempdb usage specification:
i. Try to avoid using Distinct, ORDER BY, GROUP BY, HAVING, JOIN, and Cumpute, because these statements will increase the burden of TEMPDB.
II. Avoid frequently create and delete temporary tables to reduce the consumption of system table resources.
III. When new temporary table is new, if the amount of data in one time is large, you can use Select Into instead of CREATE TABLE, avoid log, increase the speed; if the data is not large, in order to mitigate the resources of the system table, it is recommended to first create Table first. Then INSERT.
IV. If the data volume of the temporary table is large, the index needs to be established, then the process of creating a temporary table and the establishment index should be placed in a single sub-storage process so that the system can be used very well to the index of the temporary table. .
v. If you use a temporary table, you must explicitly delete all temporary tables, first truncate Table, then Drop Table, which avoids longer-time locking of the system table.
Vi. Careful use of large temporary tables with other big tables of connection query and modification, reduce system table burden, because this operation will use Tempdb system tables many times in a statement.
d) Reasonable algorithm:
According to the SQL optimization techniques described above and the SQL optimization content in the ASE Tuning manual, combine the actual application, using a variety of algorithms to compare the minimum, and the highest efficiency is the highest. Specific ASE Tune Command: Set Statistics Io ON, SET STATISTICS TIME ON, SET SHOWPLAN ON, etc.
