SqlServer 實用操作小技巧集合第1/2頁

2024-08-31 00:59:13

字體：大中小

來源：轉載

供稿：網友

包括安裝時提示有掛起的操作、收縮數據庫、壓縮數據庫、轉移數據庫給新用戶以已存在用戶權限、檢查備份集、修復數據庫等
（一）掛起操作
在安裝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

無數據庫日志文件恢復數據庫方法兩則
數據庫日志文件的誤刪或別的原因引起數據庫日志的損壞

方法一
1.新建一個同名的數據庫
2.再停掉sql server(注意不要分離數據庫)
3.用原數據庫的數據文件覆蓋掉這個新建的數據庫
4.再重啟sql server
5.此時打開企業管理器時會出現置疑，先不管，執行下面的語句（注意修改其中的數據庫名)
6.完成后一般就可以訪問數據庫中的數據了,這時,數據庫本身一般還要問題,解決辦法是,利用
數據庫的腳本創建一個新的數據庫,并將數據導進去就行了.
USE MASTER
GO
SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE
GO
UPDATE SYSDATABASES SET STATUS =32768 WHERE
Go
sp_dboption '置疑的數據庫名', 'single user', 'true'
Go
DBCC CHECKDB('置疑的數據庫名')
Go
update sysdatabases set status =28 where
Go
sp_configure 'allow updates', 0 reconfigure with override
Go
sp_dboption '置疑的數據庫名', 'single user', 'false'
Go
方法二
事情的起因
昨天，系統管理員告訴我，我們一個內部應用數據庫所在的磁盤空間不足了。我注意到數據庫事件日志文件XXX_Data.ldf文件已經增長到了3GB，于是我決意縮小這個日志文件。經過收縮數據庫等操作未果后，我犯了一個自進入行業以來的最大最愚蠢的錯誤：竟然誤刪除了這個日志文件！后來我看到所有論及數據庫恢復的文章上都說道：“無論如何都要保證數據庫日志文件存在，它至關重要”，甚至微軟甚至有一篇KB文章講如何只靠日志文件恢復數據庫的。我真是不知道我那時候是怎么想的？！
這下子壞了！這個數據庫連不上了，企業管理器在它的旁邊寫著“(置疑)”。而且最要命的，這個數據庫從來沒有備份了。我唯一找得到的是遷移半年前的另外一個數據庫服務器，應用倒是能用了，但是少了許多記錄、表和存儲過程。真希望這只是一場噩夢！
沒有效果的恢復步驟
附加數據庫
_Rambo講過被刪除日志文件中不存在活動日志時，可以這么做來恢復：
1，分離被置疑的數據庫，可以使用sp_detach_db
2，附加數據庫，可以使用sp_attach_single_file_db
但是，很遺憾，執行之后，SQL Server質疑數據文件和日志文件不符，所以無法附加數據庫數據文件。
DTS數據導出
不行，無法讀取XXX數據庫，DTS Wizard報告說“初始化上下文發生錯誤”。
緊急模式
怡紅公子講過沒有日志用于恢復時，可以這么做：
1，把數據庫設置為emergency mode
2，重新建立一個log文件
3，把SQL Server 重新啟動一下
4，把應用數據庫設置成單用戶模式
5，做DBCC CHECKDB
6，如果沒有什么大問題就可以把數據庫狀態改回去了，記得別忘了把系統表的修改選項關掉

我實踐了一下，把應用數據庫的數據文件移走，重新建立一個同名的數據庫XXX，然后停掉SQL服務，把原來的數據文件再覆蓋回來。之后，按照怡紅公子的步驟走。
但是，也很遺憾，除了第2步之外，其他步驟執行非常成功?？上В貑QL Server之后，這個應用數據庫仍然是置疑！
不過，讓我欣慰的是，這么做之后，倒是能夠Select數據了，讓我大出一口氣。只不過，組件使用數據庫時，報告說：“發生錯誤：-2147467259,未能在數據庫 'XXX' 中運行 BEGIN TRANSACTION，因為該數據庫處于回避恢復模式。”

最終成功恢復的全部步驟
設置數據庫為緊急模式
停掉SQL Server服務；
把應用數據庫的數據文件XXX_Data.mdf移走；
重新建立一個同名的數據庫XXX；
停掉SQL服務；
把原來的數據文件再覆蓋回來；
運行以下語句，把該數據庫設置為緊急模式；
運行“Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go”
執行結果：
DBCC 執行完畢。如果 DBCC 輸出了錯誤信息，請與系統管理員聯系。
已將配置選項 'allow updates' 從 0 改為 1。請運行 RECONFIGURE 語句以安裝。

接著運行“update sysdatabases set status = 32768 where name = 'XXX'”
執行結果：
（所影響的行數為 1 行）

重啟SQL Server服務；
運行以下語句，把應用數據庫設置為Single User模式；
運行“sp_dboption 'XXX', 'single user', 'true'”
執行結果：
命令已成功完成。

ü 做DBCC CHECKDB；
運行“DBCC CHECKDB('XXX')”
執行結果：
'XXX' 的 DBCC 結果。
'sysobjects' 的 DBCC 結果。
對象 'sysobjects' 有 273 行，這些行位于 5 頁中。
'sysindexes' 的 DBCC 結果。
對象 'sysindexes' 有 202 行，這些行位于 7 頁中。
'syscolumns' 的 DBCC 結果。
………

當前1/2頁

上一篇：sql中all,any,some用法

下一篇：sql 服務器知識