SQL Server on the skills of some practical experience

  Sql Server operating on the practical skills of the collection include the installation of a hung at the tips of the operation, contraction database, compressed database, transfer the database to the new user access to existing users, check backup sets, repair database (a) suspend operations (b) 
  Sp Sql or installing patches when prompted to put up before the installation operation to resume, resuming here often useless, the solution: 
  To HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession Manager 
  Delete PendingFileRenameOperations 

  (B) contraction database – rebuild the index DBCC REINDEX 
  DBCC INDEXDEFRAG 
  — Contraction data and logs DBCC SHRINKDB 
  DBCC SHRINKFILE 

  (C) of compressed database dbcc shrinkdatabase (dbname) 

  (D) transfer to the new user to the database already exists user privileges exec sp_change_users_login 'update_one', 'newname', 'oldname' 
go

  (5) inspection backup set RESTORE VERIFYONLY from disk = 'E: dvbbs.bak' 

  (6) repair 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 are three parameters: 

  — REPAIR_ALLOW_DATA_LOSS 

  — REPAIR_REBUILD completed by the implementation of all repair, including the line and page allocation and distribution to cancel the distribution error correction, or structure to the wrong page, and to delete the text has been damaged objects.    These repair may result in some data loss.    Repair operation can be completed in the user services to allow users to roll back the changes.    If the rollback repair, the database will contain errors, should recover from the backup.    As if the level of rehabilitation provided by the omission of a mistake because the restoration, will be determined by the omission of any repair restored.    Repair is completed, the database backup. 
  — REPAIR_FAST small, and not time-consuming repair operation, such as the rehabilitation of non-aggregation of the additional bond index.    These can be completed very soon restored, and there will be no risk of losing data. 

  — REPAIR_REBUILD REPAIR_FAST completed by the implementation of all the restoration, including the need to repair a longer period of time (such as reconstruction index).    Implementation of the restoration will not have the risk of losing data. 

  — DBCC CHECKDB ( 'dvbbs') with NO_INFOMSGS, PHYSICAL_ONLY 

  SQL SERVER log removal of the two methods used in the process we often encounter very large database log, here on the two treatment…… 

  One method under normal circumstances, SQL databases and can not shrink to a large extent reduce the size of the database, and its main role is to shrink the size logs, this operation should be conducted regularly to avoid too large a log database, set up the database model for the simple mode: Open SQL Enterprise Management, in the root directory of the console followed by a point Microsoft SQL Server -> SQL Server Group -> Double-click open your server -> Double-click to open the database directory -> choose your database name (such as databases Forum Forum) -> right-click and then select Properties -> Options -> fault reduction in the mode of choice in the "simple" and then click OK to Save 2, in the current database on the right-point, look at all the tasks in Shrink database, the general inside the default settings do not have to adjust, directly identified three points, shrink the database is completed, recommendations will attribute to your database is set to standard mode, methods of operation with the first point, because the logs in some unusual circumstances are often resume database An important basis for the method of approach SET NOCOUNT ON 
  DECLARE @ LogicalFileName sysname, 
  @ MaxMinutes INT, 
  @ NewSize INT 

  USE tablename – to operate a database of SELECT @ LogicalFileName = 'tablename_log', – the log file name @ MaxMinutes = 10, – Limit on time allowed to wrap log. 
  @ NewSize = 1 – you want to set the size of the log file (M) 

  — 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 of' + 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 

  Remove duplication of data in the database of several methods used in the database because of procedural problems sometimes encountered duplication of data, the data led to a repeat of the database can not be properly set up set up…… 

  A method declare @ max integer, @ id integer 
  declare cur_rows cursor local for select the main field, count (*) from table group by 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 where the main field = @ id 
  fetch cur_rows into @ id, @ max 
end
  close cur_rows 
  set rowcount 0 

  There are two ways the sense of duplication of records, the first fully duplicate records, which means that all fields are duplicate records, the keyword is part of the duplication of records, such as Name field duplication, and other fields not necessarily Duplicate or are repeat can be ignored. 
  1, the first repeat, it was relatively easy to solve, the use of select distinct * from tableName 
  Can be no duplication of records set of results. 
  If the table need to remove duplication of records (a duplicate record-keeping), according to the following methods to delete select distinct * into # Tmp from tableName 
  drop table tableName 
  select * into tableName from # Tmp 
  drop table # Tmp 
  This is to repeat the reasons is the ill-designed table, the index increased only bar to solve. 

  2, to repeat such issues usually require reservations duplicate records in the first record, methods of operation following the assumption that the field is to repeat Name, Address, asked the two field only result sets 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) 
  Finally that has a select Name, Address not repeat the result set (but more than a autoID field, the actual writing can be included in the select clause in eliminating this column) 

  Changes in the database table of their customers two ways we could often get a database backup restore one machine to another result of all the table can not be opened because the table is built at the time when using the database user…… 

  — Change a table exec sp_changeobjectowner 'tablename', 'dbo' 

  — Store all the changes 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, 
  '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

  SQL SERVER directly into the cycle of the好说no data, we see ourselves, and sometimes a bit useless declare @ i int 
  set @ i = 1 
  while @ i <30 
begin
  insert into test (userid) values (@ i) 
  set @ i = @ i +1 
end

  No database log files resume database means two database log files mistakenly deleted or the other causes of damage to the log database 

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • DZone
  • Netvouz
  • NewsVine
  • Technorati

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button