This includes installation prompts for pending operations, shrinking the database, compressing the database, transferring the database to new users for existing user rights, checking backup sets, repairing databases, and so on
(i) Suspend operation
When installing a SQL or SP patch, the system prompts for a pending installation operation, requiring a reboot, where it is often useless to restart, the solution:
To HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
Delete PendingFileRenameOperations
(ii) Shrinking of the database
--Rebuilding indexes
DBCC REINDEX
DBCC Indexdefrag
--Shrink data and logs
DBCC SHRINKDB
DBCC Shrinkfile
(iii) compressed database
DBCC SHRINKDATABASE (dbname)
(iv) Transfer of the database to new users with existing user rights
exec sp_change_users_login ' update_one ', ' newname ', ' oldname '
Go
(v) Checking backup sets
RESTORE verifyonly from disk= ' E:\dvbbs.bak '
(vi) Repair of 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 repairs performed by repair_rebuild, including assigning and unassign rows and pages to correct assignment errors, structural rows or pages, and deleting corrupted text objects. These fixes can cause some data loss. A repair operation can be completed under a user transaction to allow the user to roll back the changes. If you roll back the repair, the database will still contain errors and should be recovered from the backup. If an incorrect fix is omitted due to the level of repair provided, any fixes that depend on the repair will be omitted. After the repair is complete, back up the database.
--repair_fast for small, time-consuming fixes, such as fixing additional keys in a nonclustered index. These fixes can be done quickly, and there is no risk of losing data.
--repair_rebuild performs all the repairs performed by Repair_fast, including the need for a longer period of repair, such as rebuilding an index. There is no risk of losing data when performing these repairs.
--DBCC CHECKDB (' Dvbbs ') with No_infomsgs,physical_only
Two methods of SQL Server log cleanup
In the use of the process, we often encounter a very large database log, here are two ways to deal with ...
Method One
In general, the contraction of the SQL database does not greatly reduce the size of the database, its primary role is to shrink the log size, you should do this periodically to avoid the database log too large
1, set the database mode to Simple mode: Open SQL Enterprise Manager, in the console root in turn, click the Microsoft SQL Server-->sql Server group--> double hit Open your server--> double-click to open the database directory--> Select your database name (such as Forum Database Forum)--> and then right-click to select Properties--> Select option--> in the failover mode select "Simple" and then press OK to save
2, in the current database point right, look at all tasks in the shrinking database, the general inside the default settings do not need to adjust, direct point to determine
3. After the database is finished, it is recommended that you reset your database properties to standard mode, with the 1th, because the log is often an important basis for restoring the database in some unusual cases.
Method Two
Copy Code code as follows:
SET NOCOUNT on
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
Use TableName-the name of the database to manipulate
SELECT @LogicalFileName = ' Tablename_log ',--log file name
@MaxMinutes =--Limit on time allowed to wrap log.
@NewSize = 1-the size of the log file you want to set (M)
--Setup/initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
From Sysfiles
WHERE name = @LogicalFileName
SELECT ' Original Size of ' + db_name () + ' LOG is ' +
CONVERT (VARCHAR, @OriginalSize) + ' 8K pages or ' +
CONVERT (VARCHAR (@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 ' + db_name () + ' LOG is ' +
CONVERT (VARCHAR (), size) + ' 8K pages or ' +
CONVERT (VARCHAR (), (size*8/1024)) + ' MB '
From Sysfiles
WHERE name = @LogicalFileName
DROP TABLE Dummytrans
SET NOCOUNT off
several ways to delete duplicate data in a database
During the use of the database, due to the problems of the program, there are sometimes duplicate data, which causes the database to be set up incorrectly.
Method One
Declare @max integer, @id integer
Declare cur_rows cursor Local for select main field, COUNT (*) from table name Group by 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 duplicates of the record, one is a completely duplicate record, that is, all the fields are duplicate records, the second is some key fields duplicate records, such as the Name field repeat, and other fields do not necessarily repeat or repeat can be ignored.
1, for the first repetition, easier to solve, the use of
SELECT DISTINCT * FROM tablename
You can get a result set without duplicate records.
If the table needs to delete duplicate records (1 of duplicate records are retained), you can delete them in the following ways
SELECT DISTINCT * into #Tmp tablename
DROP TABLE TableName
SELECT * INTO TableName from #Tmp
drop table #Tmp
This duplication occurs because the table is poorly designed and can be resolved by adding a unique index column.
2. This type of repetition usually requires the first record in the duplicate record to be retained, as follows
Suppose there is a duplicate field of name,address that requires a unique result set for both fields
Select Identity (int,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)
The last select gets the name,address result set (but one more autoid field, which can be written in the SELECT clause to omit this column)
Two ways to change the user of a table in a database
You may often encounter a database backup restored to another machine the result is that all tables cannot be opened, because the database user was used when the table was built ...
--Change a table
EXEC sp_changeobjectowner ' tablename ', ' dbo '
--store changes to all tables
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,
' Owner ' = user_name (UID)
From sysobjects
where user_name (UID) = @OldOwner
Order BY name
OPEN Curobject
FETCH NEXT from Curobject into @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 into @Name, @Owner
End
Close Curobject
Deallocate Curobject
Go
Direct loop write data in SQL Server
There's nothing left to say, see it for yourselves, sometimes it's useful
DECLARE @i int
Set @i=1
While @i<30
Begin
INSERT INTO Test (userid) VALUES (@i)
Set @i=@i+1
End
No database log File Recovery database method two
Incorrect deletion of database log files or other causes of database log corruption
Method One
1. Create a new database with the same name
2. Stop SQL Server again (be careful not to detach the database)
3. Overwrite the new database with the data file of the original database
4. Restart SQL Server
5. When you open Enterprise Manager, there will be doubt, regardless, execute the following statement (note modify the database name)
6. After the completion of the general access to the data in the database, at this time, the database itself generally have problems, the solution is to use
The database script creates a new database and guides the data into the line.
Use MASTER
Go
sp_configure ' ALLOW UPDATES ', 1 reconfigure with OVERRIDE
Go
UPDATE sysdatabases SET STATUS =32768 WHERE name= ' suspect database name '
Go
sp_dboption ' suspect database name ', ' Single user ', ' true '
Go
DBCC CHECKDB (' Suspect database name ')
Go
Update sysdatabases set status =28 where Name= ' suspect database name '
Go
sp_configure ' allow updates ', 0 reconfigure with override
Go
sp_dboption ' suspect database name ', ' Single user ', ' false '
Go
Method Two
The cause of the thing
Yesterday, the system administrator told me that there was not enough disk space on one of our internal application databases. I noticed that the database event log file Xxx_data.ldf file has grown to 3GB, so I decided to shrink the log file. After shrinking the database and so on, I made the biggest and most stupid mistake since I entered the industry: I accidentally deleted this log file! I later read all the articles on database recovery and said, "It's important to keep the database log file in any case," and even Microsoft even has a KB article about how to recover the database by only log files. I really don't know what I was thinking at that time?!
It's broken! The database is not connected, and Enterprise Manager says "(suspect)" next to it. And most of all, this database has never been backed up. The only thing I can find is another database server that was migrated six months ago, but the application is available, but there are many fewer records, tables, and stored procedures. I wish it was just a nightmare!
Recovery steps with no effect
Attaching a database
_rambo said that when there is no active log in the deleted log file, you can do so to recover:
1, separate the suspect database, you can use the sp_detach_db
2, attach the database, you can use sp_attach_single_file_db
However, unfortunately, after execution, SQL Server challenged the data file and log file, so the database data file cannot be attached.
DTS Data Export
No, unable to read the XXX database, DTS Wizard reported "initialization context error".
Emergency mode
The pleasant Red Childe said that there is no log for recovery, you can do this:
1, set the database to emergency mode
2, re-create a log file
3, restart SQL Server
4, the application database is set to single user mode
5, do DBCC CHECKDB
6, if there is no big problem will be able to change the database state back, remember to do not forget the system table to change the options to turn off
I practiced, the application of the data file to remove the database, to re-establish a database xxx with the same name, and then stop the SQL service, the original data file back to cover. After, follow the steps of satisfying red Childe.
However, it is also regrettable that the other steps have been very successful in addition to step 2nd. Unfortunately, after restarting SQL Server, this application database is still suspect!
But, to my relief, I was able to select the data and give me a big breath. Only when the component uses the database, the report says: "Error:-2147467259, failed to run BEGIN TRANSACTION in database ' XXX ' because the database is in a bypass recovery mode." ”
All steps in the final successful recovery
Set Database as emergency mode
Stop the SQL Server service;
Removing the data file of the application database xxx_data.mdf;
Re-establish a database with the same name xxx;
Stop the SQL service;
The original data file to be overwritten back;
Run the following statement to set the database to emergency mode;
Run the use Master
Go
sp_configure ' allow updates ', 1
Reconfigure with override
Go "
Execution results:
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
Changed configuration option ' allow updates ' from 0 to 1. Please run the RECONFIGURE statement to install.
Then run "Update sysdatabases set status = 32768 where name = ' XXX '"
Execution results:
(The number of rows affected is 1 rows)
Restart the SQL Server service;
Run the following statement to set the application database to single user mode;
Run "sp_dboption ' XXX", ' Single user ', ' true '
Execution results:
The command has completed successfully.
u do DBCC CHECKDB;
Run "DBCC CHECKDB (' XXX ')"
Execution results:
DBCC results for ' XXX '.
DBCC results for ' sysobjects '.
The object ' sysobjects ' has 273 rows, which are in 5 pages.
DBCC results for ' sysindexes '.
The object ' sysindexes ' has 202 rows, which are in 7 pages.
DBCC results for ' syscolumns '.
.........
current 1/2 page
1 2 Next read the full text
