本主題介紹如何使用 SQL Server Management Studio
中的對象資源管理器收縮資料庫。收縮後的資料庫不能小於資料庫的最小大小。最小大小是在資料庫最初創建時指定的大小，或是上一次使用文件大小更改操作（如
DBCC SHRINKFILE）設置的顯式大小。例如，如果資料庫最初創建時的大小為 10 MB，後來增長到 100
MB，則該資料庫最小隻能收縮到 10 MB，即使已經刪除資料庫的所有數據也是如此。
收縮資料庫
在對象資源管理器中，連接到 SQL Server 資料庫引擎實例，然後展開該實例。
展開「資料庫」，再右鍵單擊要收縮的資料庫。
指向「任務」，指向「收縮」，然後單擊「資料庫」。
根據需要，可以選中「在釋放未使用的空間前重新組織文件」復選框。如果選中該復選框，必須為「收縮後文件中的最大可用空間」指定值。
選中該選項的作用與執行 DBCC SHRINKDATABASE 時指定 target_percent 值相同。清除該選項的作用與使用 TRUNCATEONLY 選項執行 DBCC SHRINKDATABASE 相同。TRUNCATEONLY 將文件收縮到最後分配的區。這將減小文件的大小，但不移動任何數據。默認情況下，該選項為清除狀態。
輸入收縮資料庫後資料庫文件中剩下的最大可用空間百分比。允許的值介於 0 和 99 之間。該選項僅在選中「在釋放未使用的空間前重新組織文件」時可用。
單擊「確定」。
收縮資料庫
當
資料庫中的資料庫文件過於龐大或者存在未使用頁時，可以使用收縮資料庫方法來實現對資料庫的收縮，同樣，事務日誌文件也可以收縮。資料庫文件可以作為組或
單獨地進行手工收縮，也可設置為按指定的時間間隔自動收縮。收縮資料庫的活動是在後台進行，並不影響資料庫內的用戶活動。
操作步驟如下：
（1）打開SQL Server企業管理器。雙擊打開「資料庫」節點，在要收縮的資料庫名稱上，單擊滑鼠右鍵，在彈出的快捷菜單中選擇「所有任務」→「收縮資料庫」命令，彈出「收縮資料庫」對話框，如圖1所示。
收縮資料庫
（2）指定資料庫的收縮量，可以從以下選項中選擇。
在「收縮後文件中的最大可用空間」文本框中輸入收縮後資料庫中剩餘的可用空間量。以「資料庫大小，可用空間」值作為依據。如最大可用空間是「25%」，則在該選項中設置小於該數值的百分比，如「12%」。
選擇「在收縮前將頁移到文件起始位置」復選框，使釋放的文件空間保留在資料庫文件中，並使包含數據的頁移到資料庫文件的起始位置。
（3）在「調度」選項框中，選擇是否自動定期執行資料庫壓縮操作。選中「根據本調度來收縮資料庫」復選框，單擊「更改」按鈕，創建或更改自動收縮資料庫的頻率和時間。
（4）最後單擊「確定」按鈕完成操作。
Ⅱ SqlServer數據文件太大，表佔用空間太大，怎麼釋放空間
1、最直接的就是看哪些數據可以不用存儲就直接刪除了，設計系統時就應該考慮這些問題的
2、如果有過期數據備份後刪除吧，或者過期的過程數據，也可以備份後刪除
3、可能是索引碎片的原因，另外就是非聚集索引佔用的空間，可以試一下先重建聚集索引，然後看看空間是否變小了很多，若是，則需要重建全部索引
Ⅲ SQLSERVER可用空間只有1兆多,為什麼
正常。可用空間1.27M，是相對於資料庫分派空間20G而言，不是磁碟的可用空間。當20G用完後，它會怎麼增加，只要磁碟有空間就行了，不用當心。
Ⅳ 怎麼查看整個sqlserver資料庫的剩餘空間大小
查詢各個磁碟分區的剩餘空間：
Exec master.dbo.xp_fixeddrives
查詢當前資料庫的磁碟使用情況
Exec sp_spaceused
Ⅳ 為什麼SQL Server使用很少的內存
默認情況下，SQLServer會依據可獲得的系統資源動態改變它的內存需求。如果SQLServer需要更多的內存，它會要求操作系統確定是否有空閑的物理內存可用，並使用可用的內存。若SQLServer不再需要當前分配給它的內存，它就將內存釋放給操作系統。當SQLServer動態使用內存時，它要求系統定期地檢測可用的物理內存數量。SQLServer根據伺服器活動增大或收縮高速緩沖存儲器，以使可用物理內存保持在4MB到10MB之間。這就避免了系統進行換頁操作。[也就是說，這種情況下SQLSERVER本身不會使物理可用內存小於4M，如果比較長的時間內都小於4M的話，則要看一下是不是該伺服器上其它應用程序有問題]第二種情況：限制使用內存使用setworkingsetsize為sqlserver保留等於伺服器內存設置的物理內存空間。即使是sqlserver進程此時是空閑的，系統也不會將SQLServer頁交換出去。使用minservermemory保證sqlserver使用的最小內存。SQLServer啟動時不立即分配minservermemory中所指定的內存量。但是，當內存使用由於客戶端負荷而達到該值後，SQLServer將無法從已分配的緩沖池中釋放內存。使用maxservermemory則防止SQLServer使用多於指定數量的內存，這樣剩餘的可用內存可以快速運行其它應用程序。SQLServer啟動時不立即分配maxservermemory中所指定的內存。內存使用隨SQLServer的需要而增長，直到達到maxservermemory中所指定的值。SQLServer無法超過該內存使用值，除非增加maxservermemory值。第一種情況比較適用於伺服器專做sqlserver伺服器的情況，第二種情況適用於為在同一台計算機上運行的其它應用程序保留一定的內存以便於快速響應。（另：如果想動態分配sqlserver的內存，則不要設置setworkingsetsize選項，使用默認值即可。至於這些參數如何設置參見另外的文檔）監視SQLServer所使用的內存和計數器有助於確定：是否由於缺少可用物理內存存儲高速緩存中經常訪問的數據而導致瓶頸存在。如果是這樣，SQLServer必須從磁碟檢索數據。是否可通過添加更多內存或使更多內存可用於數據高速緩存或SQLServer內部結構來提高查詢性能。SQLServer需要從磁碟讀取數據的頻率。與其它操作相比，例如內存訪問，物理I/O會耗費大量時間。盡可能減少物理I/O可以提高查詢性能。對sqlserver伺服器內存的監視：Memory:AvailableBytes計數器表示當前進程可使用的物理內存位元組數。如果小於4M或更小，說明計算機上總的內存可能不足，或某個程序沒有釋放內存●Memory:PageFaults/sec每秒軟性頁面失效的數目（包括有些可以直接在內存中滿足而有些需要從硬碟讀取）較page/sec只表明數據不能在內存指定工作集中立即使用。如果該值偶爾走高，表明當時有線程競爭內存。如果持續很高，則內存可能是瓶頸。Memory:Pages/sec計數器表示由於缺頁處理而從磁碟取回的頁數，或由於缺頁處理而寫入磁碟以釋放工作集空間的頁數。●PageReads/sec每秒發出的物理資料庫頁讀取數。這一統計信息顯示的是在所有資料庫間的物理頁讀取總數。由於物理I/O的開銷大，可以通過使用更大的數據高速緩存、智能索引、更高效的查詢或者改變資料庫設計等方法，使開銷減到最小。●PageWrites/sec所發出的物理資料庫頁寫入的數目。若查看原帖>>
Ⅵ 誰總結下sqlserver資料庫優化知識的詳解和具體操作最優方案！
給你一些我常用的腳本！至於方案，我覺得完全在於積累經驗！
--前10名其他等待類型
SELECT TOP 10 *
from sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
SELECT *FROM sys.dm_os_wait_stats WHERE wait_type like 'PAGELATCH%'
OR wait_type like 'LAZYWRITER_SLEEP%'
--CPU的壓力
SELECT scheler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schelers
WHERE scheler_id < 255
--表現最差的前10名使用查詢
SELECT TOP 10 ProcereName = t.text,
ExecutionCount = s.execution_count,
AvgExecutionTime = isnull ( s.total_elapsed_time / s.execution_count, 0 ),
AvgWorkerTime = s.total_worker_time / s.execution_count,
TotalWorkerTime = s.total_worker_time,
MaxLogicalReads = s.max_logical_reads,
MaxPhysicalReads = s.max_physical_reads,
MaxLogicalWrites = s.max_logical_writes,
CreationDateTime = s.creation_time,
CallsPerSecond = isnull ( s.execution_count / datediff ( second , s.creation_time, getdate ()), 0 )
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY
s.max_physical_reads DESC
SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms總信號等待時間 ,
SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms資源的等待時間,
SUM(signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [signal_wait_percent信號等待％],
SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [resource_wait_percent資源等待％]
FROM sys.dm_os_wait_stats
--一個信號等待時間過多對資源的等待時間那麼你的CPU是目前的一個瓶頸。
--查看進程所執行的SQL語句
if (select COUNT(*) from master.dbo.sysprocesses) > 500
begin
select text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) from master.sys.sysprocesses a
end
select text,a.* from master.sys.sysprocesses a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
where a.spid = '51'
dbcc inputbuffer(53)
with tb
as
(
select blocking_session_id,
session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 as
(
select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)',
total_scheled_time,reads,writes,logical_reads
from tb a inner join master.sys.dm_exec_sessions b
on a.session_id=b.session_id
)
select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id
--當前進程數
select * from master.dbo.sysprocesses
order by cpu desc
--查看當前活動的進程數
sp_who active
--查詢是否由於連接沒有釋放引起CPU過高
select * from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -10, getdate())
and login_time < dateadd(minute, -10, getdate())
--強行釋放空連接
select 'kill ' + rtrim(spid) from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -60, getdate())
and login_time < dateadd(minute, -60, getdate())
--查看當前佔用 cpu 資源最高的會話和其中執行的語句(及時CPU)
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc
--查看緩存中重用次數少，佔用內存大的查詢語句（當前緩存中未釋放的）--全局
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc
SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num >1
ORDER BY qs.plan_generation_num
SELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --統計總的CPU時間
--ORDER BY avg_cpu_time DESC --統計平均單次查詢CPU時間
-- 計算可運行狀態下的工作進程數量
SELECT COUNT(*) as workers_waiting_for_cpu,s.scheler_id
FROM sys.dm_os_workers AS o
INNER JOIN sys.dm_os_schelers AS s
ON o.scheler_address=s.scheler_address
AND s.scheler_id<255
WHERE o.state='RUNNABLE'
GROUP BY s.scheler_id
--表空間大小查詢
create table #tb(表名 sysname,記錄數 int,保留空間 varchar(100),使用空間 varchar(100),索引使用空間 varchar(100),未用空間 varchar(100))
insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?'''
select * from #tb
go
SELECT
表名,
記錄數,
cast(ltrim(rtrim(replace(保留空間,'KB',''))) as int)/1024 保留空間MB,
cast(ltrim(rtrim(replace(使用空間,'KB',''))) as int)/1024 使用空間MB,
cast(ltrim(rtrim(replace(使用空間,'KB',''))) as int)/1024/1024.00 使用空間GB,
cast(ltrim(rtrim(replace(索引使用空間,'KB',''))) as int)/1024 索引使用空間MB,
cast(ltrim(rtrim(replace(未用空間,'KB',''))) as int)/1024 未用空間MB
FROM #tb
WHERE cast(ltrim(rtrim(replace(使用空間,'KB',''))) as int)/1024 > 0
--order by 記錄數 desc
ORDER BY 使用空間MB DESC
DROP TABLE #tb
--查詢是否由於連接沒有釋放引起CPU過高
select * from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -10, getdate())
and login_time < dateadd(minute, -10, getdate())
--強行釋放空連接
select 'kill ' + rtrim(spid) from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -60, getdate())
and login_time < dateadd(minute, -60, getdate())
----查看當前佔用 cpu 資源最高的會話和其中執行的語句(及時CPU)
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc
----查看緩存中重用次數少，佔用內存大的查詢語句（當前緩存中未釋放的）--全局
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc
SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num >1
ORDER BY qs.plan_generation_num
SELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --統計總的CPU時間
--ORDER BY avg_cpu_time DESC --統計平均單次查詢CPU時間
-- 計算可運行狀態下的工作進程數量
SELECT COUNT(*) as workers_waiting_for_cpu,s.scheler_id
FROM sys.dm_os_workers AS o
INNER JOIN sys.dm_os_schelers AS s
ON o.scheler_address=s.scheler_address
AND s.scheler_id<255
WHERE o.state='RUNNABLE'
GROUP BY s.scheler_id
SELECT creation_time N'語句編譯時間'
,last_execution_time N'上次執行時間'
,total_physical_reads N'物理讀取總次數'
,total_logical_reads/execution_count N'每次邏輯讀次數'
,total_logical_reads N'邏輯讀取總次數'
,total_logical_writes N'邏輯寫入總次數'
, execution_count N'執行次數'
, total_worker_time/1000 N'所用的CPU總時間ms'
, total_elapsed_time/1000 N'總花費時間ms'
, (total_elapsed_time / execution_count)/1000 N'平均時間ms'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'執行語句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;
Ⅶ SqlServer是如何管理，分配存儲空間的呢
Sql Server 區管理(GAM,SGAM)
大家都知道Sql Server 中數據文件存儲的最小單位是頁面(Page),但實際SQLSERVE並不是以頁面為單位給數據分配空間的，Sql Server默認的存儲分配單位是盤區(Extend)。這樣做的主要原因是為了避免頻繁的讀寫IO，提升性能。在表或其它對象分配存儲空間，不是直接分配一個8K的頁面，而是以一個盤區(Extend)為存儲分配單位，一個盤區為8個頁面(Size = 8＊8K＝64K)。
這樣，對區得操作就會非常頻繁，也要求Sql Server有自己的一套系統管理著數量眾多的區。其中最突出的出一個問題，那就是在存儲那些只有少量數據，不足8K的對象，如果也是分配給一個盤區，就會存在存儲空間上的浪費，降低了空間分配效率。
為解決上述問題，SQLSERVER提供了一種解決方案，定義了兩種盤區類型,統一盤區和混合盤區。
全局分配映射表 (GAM)：統一盤區，GAM 頁記錄已分配的區。每個 GAM 包含 64,000 個區，相當於近 4 GB 的數據。GAM 用一個位來表示所涵蓋區間內的每個區的狀態。如果位為 1，則區可用；如果位為 0，則區已分配。
共享全局分配映射表 (SGAM)：由多個對象共同擁有該盤區，SGAM 頁記錄當前用作混合區且至少有一個未使用的頁的區。每個 SGAM 包含 64,000 個區，相當於近 4 GB 的數據。SGAM 用一個位來表示所涵蓋區間內的每個區的狀態。如果位為 1，則區正用作混合區且有可用頁。如果位為 0，則區未用作混合區，或者雖然用作混合區但其所有頁均在使用中。
在實際為對象分配存儲盤區時，為了提高空間利用率，默認的情況下，如果一個對象一開始大小小於8個頁面，就盡量放在混合盤區中，如果該對象大小增加到8個頁面後，SQLSERVER會為這個對象重新分配一個統一盤區。
據區當前的使用情況，GAM 和 SGAM 中每個區具有以下位模式：
第0個bit為保留位元組，始終為0
第1個bit表示該頁面是否已分配，我們知道GAM頁用來管理區是否已分配，但一個區包含8個頁面，所以用該bit用來准確定位該區的某個頁面是否已分配出去了。
第2個bit表示該頁面是否混合分區的一個頁面。
第3個bit表示該頁面是否是一個IAM(索引分配映射)頁面。
第4個bit表示該頁面中是否包含幻影或已刪除記錄，這有助於SQL Server定期清理幻影或已刪除記錄。
第5~7個頁面表示該頁面的空間使用率情況。
Ⅷ sqlserver升級需要注意哪些事項
1，建立一系列檢查表；DBA和開發人員應准備一系列需要在資料庫升級前，升級期間和升級後執行的檢查表。
2，備份所有的重要文件；資料庫文件以及任何應用文件，腳本文件，extract file等。
3，保證資料庫一致性；對要升級的資料庫運行DBCC CHECKDB，以確保它們在執行升級前處於一致的狀態。
4，保留足夠的磁碟空間；估計在sql server升級過程中升級sql server組件，用戶資料庫以及任何資料庫文件所需的磁碟空間，升級過程中所需的磁碟容量，可能是升級完成後所需容量的2-4倍。
5，確保系統資料庫所需的磁碟空間；配置系統資料庫(master,model,msdb,tempdb)，使其在升級過程中自動增長，並確保它們有足夠的磁碟空間。
6，傳輸登錄信息；sql server升級前確保所有的資料庫伺服器把登錄信息放在master資料庫中，這一步對恢復資料庫很重要，因為系統登錄信息駐留在master資料庫中，並且在新實例中必須進行重建。
7，禁用所有的啟動存儲過程；升級過程往往會多次停止和啟動正在升級的sql server實例，設置為啟動時執行存儲過程可能會阻止升級過程。
8，停止復制；停止復制並確保復制日誌是空的，以便開始升級過程。
9，退出所有應用程序；如果本地應用程序連接到要升級的實例，則某些應用程序，包括sql server依賴的所有服務，可能會導致升級過程失敗。
10，在升級後注冊伺服器；sql server升級過程將刪除以前的sql server實例的注冊表設置，升級以後，必須注冊伺服器。
11，重新植入全文目錄；sql server升級過程將資料庫標記為禁用全文本，目錄必須被重新植入，但是安裝程序不會自動地運行該操作，因為它可能比較耗時，該操作增強了sql server 2005系統的性能，所以應計劃在升級後的某個方便時間重新植入全文目錄。
12，更新統計信息；為了幫助優化查詢性能，升級後應更新所有資料庫的統計信息。
13，更新使用計數器；在sql server的早期版本中，表及索引行數和頁數的值可能會出現錯誤，為了糾正任何不合法的行數或頁數，升級後對所有資料庫運行DBCC UPDATESAGE。
14，配置新的sql server安裝。為了減少系統的可攻擊外圍區域，sql server 2005有選擇地安裝和激活關健的服務和性能，你需要定製該配置，以便得到本次安裝的最佳安全性，性能和功能。
Ⅸ SQLServer 最大內存佔用是多少
沒有任何額外的進程下佔用不到200M但哪個是你剛安裝系統的情況下.要是時間長了 多了幾個插件多了幾個進程的話 就會多佔一點,但最多也就200M左右.哪個只是任務管理器里的數字.但每個系統都有虛擬內存,要是你的內存是 512的話就把虛擬內存調到 1500M就可以了.256的話減少一點.
Ⅹ sqlserver的備份設備中最多能保留多少份備份
要看你需要多少備份內容！還有你的硬碟有多大，硬碟大的話可以保留天數多點！設置備份的時候可以選擇保留的備份天數！
企業管理器里選中你要備份的資料庫點右鍵，選所有任務－>維護計劃.後面的設置就根據你的內容選。注意的是，服務管理器的SQL Server Agent服務要開啟！這個是自動備份用的。