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 susceptor before the system prompts, requires restart, often restarting useless, solution:
To HKEY_LOCAL_MACHINE / System / CurrentControlSet / Control / Session Manager
Delete PendingFileerenameOperations
(2) shrinking the database
- Reconstruction index
DBCC ReIndex
DBCC Indexdefrag
- Shrink data and logs
DBCC Shrinkdb
DBCC 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_User
Go
DBCC CHECKDB ('DVBBS', REPAIR_ALLOW_DATA_LOSS) with Tablock
Go
Alter Database [DVBBS] Set Multi_user
Go
- 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
In the process of use, everyone often encounters a lot of database logs, here introduces 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, see the contraction database in all tasks, the default settings in all tasks do not have to be adjusted, direct point determination 3, after the shrink database is completed, it is recommended to reset your database properties to standard mode, operation method The first point, because the log is often an important basis for restoring the database in some abnormalities.
Method Two
Set nocount on
Declare @LogicalFileName Sysname,
@Maxminutes int,
@Newsize Int
USE
SELECT @ LogicalFileName = 'Tablename_log', --- Log file name
@MaxMinutes = 10, - Limit on time allowed to wrap log.
@Newsize = 1
- Setup / Initialize
Declare @originalsize Int
SELECT @originalsize = size
From sysfiles
Where name = @LogicalFileName
SELECT 'Original Size of' DB_NAME () 'LOG IS'
Convert (VARCHAR (30), @ OriginalSize '8k Pages Or'
Convert (VARCHAR (30), (@ OriginalSize * 8/1024)) 'MB'
From sysfiles
Where name = @LogicalFileName
Create 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 Expired
And @originalsize = (select size from sysfiles where name = @LogicalFileName)
And (@ORIGINALSIZE * 8/1024)> @ Newsize
Begin - Outer Loop.
SELECT @counter = 0
WHILE ((@ counter <@originalsize / 16) and (@counter <50000)))
BEGIN - UPDATE
INSERT DUMMYTRANS VALUES ('Fill Log ") delete DummyTrans
SELECT @counter = @counter 1
End
Exec (@trunclog)
End
SELECT 'FINAL SIZE OFE' DB_NAME () 'LOG IS'
Convert (varchar (30), size) '8K Pages Or'
Convert (VARCHAR (30), (Size * 8/1024)) 'MB'
From sysfiles
Where name = @LogicalFileName
Drop Table DummyTrans
Set nocount off
Several methods of deleting duplicate data in a database
During the use of the database, the repetitive data is sometimes encountered during the use of the database, and the duplicate data has caused the database part settings that cannot be set correctly ...
method one
Declare @max integer, @ id integer
DECLARE CUR_ROWS CURSOR LOCAL for SELECT Main Field, Count (*) from the list of the main field haVing count (*)> 1
Open cur_rows
Fetch Cur_Rows Into @ ID, @max
While @@ fetch_status = 0
Begin
SELECT @max = @max -1
SET ROWCOUNT @max
DELETE FROM table name Where main field = @ID
Fetch Cur_Rows Into @ ID, @max
end
Close Cur_Rows
SET 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, use
Select Distinct * from TableName
You can get the result set without repeated recording.
If the table needs to delete repetitive records (1 repeated record reserved), you can delete it as follows
Select Distinct * Into #tmp from TableName
Drop Table Tablename
Select * INTO TABLENAME FROM #TMP
Drop Table #TMP
This repetition is that the design is not yaw in the case, and the unique index will be resolved.
2, this type of repetition problem typically requires the first record in the repeated record, the operation method is as follows
Suppose there are duplicate fields for Name, Address, requires the unique result set of these two fields.
Select Id, 1, 1) AS Autoid, * INTO #TMP from Tablename
SELECT MIN (AutoID) AS Autoid Into # Tmp2 from #tmp Group by Name, AutoID
Select * from #tmp Where autoid in (Select Autoid from # TMP2)
Last Select gets Name, Address does not repeat result set (but more autoid fields, can write this column in the SELECT clause in the SELECT clause) to change the two methods of the user of the table in the database.
Everyone may often encounter a database backup to restore another machine results that cause all the tables that cannot be opened, because the time when the table is used, using the database users at the time ...
- Change a table
EXEC SP_CHANGEOBJECTOWNER 'TABLENAME', 'DBO'
- Storage changes all tables
Create Procedure DBo.user_changeObjectownerbatch
@Eldowner as nvarchar (128),
@Newowner as nvarchar (128)
AS
Declare @ name nvarchar (128)
Declare @ oowner thas nvarchar (128)
Declare @ oownername ω nvarchar (128)
Declare Curobject Cursor for
SELECT 'Name' = Name,
'Owner'z = User_name (UID)
From sysobjects
Where user_name (uid) = @ Oldowner
ORDER BY NAME
Open Curobject
Fetch next from curobject info @name, @owner
While (@@ fetch_status = 0)
Begin
IF @ Owner = @ Oldowner
Begin
Set @Ownername = @OLDOWNER '.' RTRIM (@name)
EXEC SP_CHANGEOBJECTOWNER @Ownername, @newowner
end
- Select @ name, @ newowner, @ Oldowner
Fetch next from curobject info @name, @owner
End
Close Curobject
Deallocate Curobject
Go
Direct loop data in SQL Server
Nothing to say, everyone look, sometimes a bit used
Declare @i int
Set @ i = 1
While @i <30
Begin
@Sert Into test (userid) VALUES (@i)
@ i = @ i 1
end
No agricultural log file restores database method two
Misler-deleted or other causes of database log files cause damage to database logs
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. After the completion, you can access the data in the database. At this time, the database itself should generally have problems, the solution is to use
The database's script creates a new database and the data is introduced.
Use master
Go
Sp_configure 'allow updates', 1 Reconfigure with override
Go
Update sysdatabases set status = 32768 where name = 'quoted database name' Go
SP_DBOPTION 'Troubleshooting Database Name', 'Single User', 'True'
Go
DBCC CHECKDB ('quoted database name ")
Go
Update sysdatabases set status = 28 where name = 'Troubleshooting Database Name'
Go
sp_configure 'allow updates', 0 Reconfigure with override
Go
SP_DBOPTION 'Troubleshooting Database Name', 'Single User', 'False'
Go
Method Two
Thing's cause
Yesterday, the system administrator told me that our disk space in which we have internal application database is under. 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 articles and database recovery articles on the velochemistry V. A href = "
http://hackbase.com/hacker "target =" _ blank "> 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!
No effect recovery steps
Additional database
_Rambo speesses that there is no activity log in the deleted log file, can be recovered:
1. Separate the quenched database, you can use sp_detach_db
2, additional database, can you use sp_attach_single_file_db
However, unfortunately, after execution, SQL Server questioned that the data files and log files did not match, so they were unable to attach the database data file.
DTS data export
No, you can't read the XXX database, the DTS Wizard reports "Initialization context errors".
Emergency mode
Yihong Bang 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 of the database. Remember to turn off the modification options for the system table.
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, the Begin Transaction cannot be run in the database 'xxx' because the database is in an evasive recovery mode." Final successfully recovered full steps
Setting up the database for emergency mode
stop SQL Server service;
removed the data file of the application database XXX_DATA.MDF;
Re-establish a database XXX;
stop SQL service;
The original data file is overwritten back;
run the following statement, set the database to emergency mode;
"Uses User
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.
Ü do 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 option of 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.
Re-establish another database xxx.lost;
DTS Export Wizard
Run the DTS Export Wizard;
Copy Source Select EmergencyMode's database XXX to import to XXX.LOST;
Select "Copy Objects and Data between SQL Server Database", tried many times, it seems that all table structure is copied, but there is no data, no view and stored procedures, and the DTS wizard final report replication failure;
So finally select "From Source Database Copy Table and View", but later discovered that it can always copy a part of the table record;
I chose to "specify the data to be transmitted with a query", which is missing which table records are missing.
view and stored procedures are added to the SQL statement.
Maintain the index of the table in SQL Server
Some problems often encounter in use and create a database index, where some alternative methods can be used here ...
- Step 1: Check if maintenance is required, check whether 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 ('Table ", PK_ Index, 100)
- Take the first step, if you find that scan density / scan Density is still less than 100%, all indexes of the reconstruction table
- Yang Wei: It is not necessarily 100%.
DBCC DBREINDEX ('Name ",' ', 100)
SQL Server patch installation FAQ
Who touched the problem and see it :)
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, please follow the steps below
b, enter regedit in the start -> operation
C, to HKEY_LOCAL_MACHINE / SYSTEM / CURRENTCONTROLSET / CONTROL / Session Manager location
D, select file -> pour out, save
E. Right-click PENDINGFILENAMEOPERATIONS to right, select Delete, and then confirm
f, restart 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 the installation of SQL Server SP3, sometimes appears: Whether you use Windows certification or mixing authentication, password errors, at this time, the SQLsp.out under the temporary directory will find the following description:
[TCP / IP Sockets] Specified SQL Server NOT FOUND.
[TCP / IP Sockets] ConnectionOpen (Connect ()).
In fact, this is a small bug of SQL Server SP3. When installing SP3, there is no listening to the TCP / IP port, you can follow the steps below:
1. Open the SQL Server Client Network Utility and Server Network Tool to ensure that the Name Pipe is included in the protocol enabled, and the location is in the first.
2, make sure [HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLServer / Client / Connectto]
"Dsquery" = "dbnetlib".
If not, build 3 yourself, stop MSSQL.
4, make installation.
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, enter 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, you can :))
SELECT @@ Version;
Then press execution;
The version information of SQL is returned at this time, 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)
The 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
Backup and recovery of SQL Server database
The most commonly used operation, the novices look at ...
First, backup database
1. Open the SQL Enterprise Manager, and click Microsoft SQL Server in the Contents Root Catalog.
2, SQL Server Group -> Double click to open your server -> Double click to open the database directory
3, choose your database name (such as the forum database forum) -> then click Tools in the Menu -> Select Backup Database
4, the backup options Select full backup, backup in the destination to select the name point delete if the original path and name, then add it, if there is no path and name, then select Add, then specify the path and file name, specified after the specified point Determine return backup window, then click OK to make a backup
Second, restore the database
1. Open the SQL Enterprise Manager, and click Microsoft SQL Server in the Contents Root Catalog.
2, SQL Server Group -> Double click to open your server -> Click the new database icon for the point icon, the name of the new database
3. Click the newly entered database name (such as the forum database forum) -> then click Tools in the Menu -> Select Recovery Database
4. Select from the device -> point selection device -> and select your backup file name -> Add the backup file name -> Add the backup point OK, then the equipment bar should There is a database backup file name you just selected. The backup number is 1 (if you have multiple backups to the same file, you can click on the view next to the backup number, select the latest backup in the check box. ) -> Then click on the option buttons next to the normal side 5. Select to force restore on the existing database in the window, and select Options for the database to continue running but cannot restore other transaction logs in the recovery completion status. 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, provided that this directory must exist (if you can specify D: /SQLDATA/BBS_DATA.MDF or D: /SQLDATA/bbs_log.ldf), otherwise restore will report an error
6. After the modification is complete, click on the determination to recover, then a progress bar, prompt recovery, the system will automatically prompt after the recovery is completed, such as intermediate prompt error, please record the relevant error content and ask questions SQL operations are more familiar, 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, you can try to close all SQL windows and then reopen Recovery operation, if you also prompt the error that is being used, you can restart it and then restart. As for other errors, it can be restored after the error content is changed accordingly.
Third, shrink database
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. After the shrink database is completed, it is recommended to reset your database properties to standard mode, and the operation method is the first point, because the log is often an important basis for recovering the database in some abnormalities
Fourth, set the daily automatic backup database
It is highly recommended that there is a conditional user!
1. Open Enterprise Manager, click on the Microsoft SQL Server in the Control Bengen -> SQL Server Group -> Double click to open your server
2, then point the tools in the menu above -> Select the database maintenance planner
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 on the change to select the point after backup every day.
5. Next Specify the backup disk directory, select the specified directory, such as you can create a directory in the D: / DataBak, then choose to use this directory, if your database is more best to choose to choose for each The database establishes a sub-directory, then selects to delete how many days ago, generally set 4-7 days, see your specific backup requirements, the backup file extension is generally Bak uses the default
6, Next Specify the transaction log backup plan, see your needs to do the selection -> The next step to generate the report, generally do not choose -> Next Maintenance plan history, it is best to use the default option -> Step completion
7. After the completion, the system is likely to prompt the SQL Server Agent service that is 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 SQL Server Agent in the service, then Click to run the arrow, select the automatic start service when you start the OS
8. This time the database plan has been successfully run, he will automatically back up the above settings.
Modify plan:
1. Open Enterprise Manager, click on Microsoft SQL Server in the Contents of the Console -> SQL Server Group -> Double click to open your server -> Management -> Database Maintenance Plan -> After opening To your set plan, you can modify or delete actions
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 some of them, you can consult the relevant personnel or inquiry online information.
1. Export all tables, stored procedures, stored procedures into a SQL file, pay attention to the option to write index scripts and write primary keys, foreign keys, default and check constraints options in the options.
2. New database, perform the SQL file established in the first step in the new database
3. Import the new database into all tables in the original database with SQL import mode
Use database log to recover data to time point operation
Due to the abnormal data loss, do not want to use backup data restore, as long as there is backup and the current log is in good condition, you can use this method to try, maybe it can recover the loss ...
1. If there is a full storage backup (or have multiple differential backups or incremental backups), the first thing to do is to enter
Perform a log backup (if you don't make the log file to be large, Trunc. Log on chkpt. Option is 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,
complex
RESTORE DATABASE DBNAME from Disk = 'FileName' with Norecovery
3. Restore the last log backup, just do the log backup, specify the time before the recovery time point to the wrong operation restore log dbname from disk = 'filename'
with stopt = 'date_time'
These operations 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.
Use the above method to recover data ...
How to recover when the SQL Server2000 database file is corrupted
This kind of problem is more serious, can it 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 a test database TEST (database type is complete)
2 built a table, insertion point record
Create Table A (C1 VARCHAR (2))
Go
INSERT INTO A VALUES ('aa')
Go
INSERT INTO a VALUES ('BB')
Go
3 Full backup, to file test_1.bak
4 is making a little modification
INSERT INTO A VALUES ('cc')
Go
Create Table B (C1 INT)
Go
INSERT INTO B VALUES (1)
Go
INSERT INTO B VALUES (2)
Go
5 Shutdown Database Server
6 Edit the database file Test_Data.mdf with UltraEdit, and modify the point byte content, which is equivalent to a deadly 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 -p
1> Backup log test to disk = 'D: Program FileSmicrosoft SQL ServerMssqlbackup
EST_2.BAK 'with no_truncate
2> Go
2 pages have been processed, these pages belong to the file 'test_log' of the database 'Test' (on the file 1).
The Backup log operation has been successfully handled 2 pages, which took 0.111 seconds (0.087 MB / sec).
9 to restore the oldest complete backup
1> Restore Database Test from Disk = 'D: Program FileSmicrosoft SQL ServerMssql
Backup EST_1.BAK 'with NORECOVERY
2> Go
Table 96, these pages belong to the file 'test_data' (on file 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 recovery
2> Go
2 pages have been processed, these pages belong to the file 'test_log' of the database 'Test' (on the file 1).
Restore log operations have been successfully processed by 2 pages and spent 0.056 seconds (0.173 MB / sec).
Writing experience and optimization measures in stored procedures
Experience, 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 Tuning Command: Set Statistics Io ON, Set Statistics Time ON, SET Showplan ON, etc.
Transfer from:
http://www.5iuu.com/blogview.asp?logid=2146
