[psac]

Sql Server實用操作小技巧集合



　　包括安裝時提示有掛起的操作、收縮資料庫、壓縮資料庫、轉移資料庫給新用戶以已存在用戶權限、檢查制作備份集、修複數據庫等

　　（一）掛起操作

　　在安裝Sql或sp修正檔的時候系統提示之前有掛起的安裝操作，要求重啟，這裡往往重啟無用，解決辦法：
到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
移除PendingFileRenameOperations

　　二）收縮資料庫

--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收縮資料和日誌
DBCC SHRINKDB
DBCC SHRINKFILE

　　（三）壓縮資料庫

　　dbcc shrinkdatabase(dbname)

　　四）轉移資料庫給新用戶以已存在用戶權限

exec sp_change_users_login 'update_one','newname','oldname'
go

　　（五）檢查制作備份集

RESTORE VERIFYONLY from disk='E:\dvbbs.bak'

　　（六）修複數據庫

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 有3個參數:

--REPAIR_ALLOW_DATA_LOSS

-- 執行由 REPAIR_REBUILD 完成的所有修復，包括對行和頁進行分配和取消分配以改正分配錯誤、結構行或頁的錯誤，以及移除已損壞的文本對象。這些修復可能會導致一些資料遺失。修復操作可以在用戶事務下完成以允許用戶回滾所做的更改。如果回滾修復，則資料庫仍會含有錯誤，應該從制作備份進行恢復。如果由於所提供修復等級的緣故遺漏某個錯誤的修復，則將遺漏任何取決於該修復的修復。修復完成後，制作備份資料庫。
--REPAIR_FAST 進行小的、不耗時的修復操作，如修復非聚集索引中的附加鍵。這些修復可以很快完成，並且不會有丟失掉資料的危險。

--REPAIR_REBUILD 執行由 REPAIR_FAST 完成的所有修復，包括需要較長時間的修復（如重建索引）。執行這些修復時不會有丟失掉資料的危險。

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

SQL SERVER日誌清除的兩種方法
在使用程序中大家經常碰到資料庫日誌非常大的情況，在這裡介紹了兩種處理方法……

方法一

一般情況下，SQL資料庫的收縮並不能很大程度上減小資料庫大小，其主要作用是收縮日誌大小，應當定期進行此操作以免資料庫日誌過大

1、設定資料庫模式為簡單模式：開啟SQL企業管理器，在控制台根目錄中依次點開Microsoft SQL Server-->SQL Server組-->雙按開啟你的伺服器-->雙按開啟資料庫目錄-->選項你的資料庫名稱（如論壇資料庫Forum）-->然後點擊右鍵選項內容-->選項選項-->在故障還原的模式中選項「簡單」，然後按確定儲存

2、在當前資料庫上點右鍵，看所有工作中的收縮資料庫，一般裡面的預設設定不用調整，直接點確定

3、收縮資料庫完成後，建議將您的資料庫內容重新設定為標準模式，操作方法同第一點，因為日誌在一些異常情況下往往是恢複數據庫的重要依據

方法二

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT


USE tablename -- 要操作的資料庫名
SELECT @LogicalFileName = 'tablename_log', -- 日誌檔案名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想設定的日誌文件的大小(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



移除資料庫中重複資料的幾個方法
資料庫的使用程序中由於程序方面的問題有時候會碰到重複資料，重複資料導致了資料庫部分設定不能正確設定……

方法一

declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 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 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

方法二

有兩個意義上的重複記錄，一是完全重複的記錄，也即所有字段均重複的記錄，二是部分關鍵字段重複的記錄，比如Name字段重複，而其他字段不一定重複或都重複可以忽略。
1、對於第一種重複，比較容易解決，使用
select distinct * from tableName
就可以得到無重複記錄的結果集。
如果該表需要移除重複的記錄（重複記錄保留1條），可以按以下方法移除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
發生這種重複的原因是表設計不周產生的，增加唯一索引列即可解決。

2、這類重複問題通常要求保留重複記錄中的第一條記錄，操作方法如下
假設有重複的字段為Name,Address，要求得到這兩個字段唯一的結果集
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)
最後一個select即得到了Name，Address不重複的結果集（但多了一個autoID字段，實際寫時可以寫在select子句中省去此列）


更改資料庫中表的所屬用戶的兩個方法
大家可能會經常碰到一個資料庫制作備份還原到另外一台機器結果導致所有的表都不能開啟了，原因是建表的時候採用了當時的資料庫用戶……


--更改某個表
exec sp_changeobjectowner 'tablename','dbo'


--儲存於更改全部表
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中直接循環寫入資料
沒什麼好說的了，大家自己看，有時候有點用處

declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end