Share　

SQL Server Express資料庫維護

 

由於Microsoft SQL Server Express 版本無法使用SQL Agent 排程，且SQL 2005 Server 會有4G 容量上限的限制，因此必須使用手動方式整理資料庫，或是使用此排程來操作，此設定需搭配Windows 排程來執行，當然也可以手動執行。

 

介紹說明： 

一、將批次檔寫好放在固定位置不會被刪除 

二、將要執行的SQL Script 放置與批次檔同一個資料夾中，並確認是否正確

三、設定Windows 排程，執行批次檔

四、驗證排程是否可正常執行

五、排程1 「超過三十天的紀錄移轉至歷史紀錄」
六、排程2 「將歷史記錄移到其他資料庫」

 

一、將批次檔寫好放在固定位置不會被刪除 

 

批次檔案內容說明如下

‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐

@echo off

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe" ‐S 127.0.0.1 ‐Usa ‐Pkeepout ‐i

D:\backup_full.sql ‐o D:\backup_full.log

‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐

(1) 先檢查 sqlcmd.exe 程式放在哪個位置

(2) 再將程式路徑複製出來，如果路徑中有空格，需要在前後加雙引號。

(3) –S 後面請加上SQL Server 的IP 位置

(4) –U 後面請加上SQL Server 的帳號。

(5) –P 後面請加上SQL Server 該帳號的密碼。

(6) –i 後面為要執行的SQL Script 語法與存放路徑。

(7) –o 後面為執行後所產生的log 放置的地方與檔名。

*批次檔範例下載backup.zip (248 Byte ,下載:5次)

==========================================================  

 

二、將要執行的SQL Script 放置與批次檔同一個資料夾中，並確認是否正確  

註：文中所有之DB_Name，意指SmartIT之資料庫名稱，標記紅色字體為可變更。

說明： 

將需要執行的SQL Script 放入與批次檔同一個資料夾中，方便管理與維護。

每一個SQL Script 都建一個批次檔來執行，目的是希望每個排程都按照指定時間執行，並且不是每個排程都需要每天做，所以分開執行最好。

這裡建議幾個常用的Script，提供參考。

1、DB Backup 「資料庫單一備份」

-----------------------------------------DB 單一Backup-----------------------------------------

BACKUP DATABASE [DB_Name]

TO DISK = N'D:\SmartIT_DB_BAK\smartit_bk.bak' WITH INIT,NOUNLOAD,

NAME = N'smartit_bk',NOSKIP,STATS = 10,NOFORMAT

-----------------------------------------DB 單一Backup-----------------------------------------

*DB_Name，意指SmartIT資料庫名稱
*D:\SmartIT_DB_BAK\smartit_bk.bak，意指備份路徑及備份檔案名稱

= 

2、清除交易紀錄檔

-----------------------------------------清除交易紀錄檔-----------------------------------------

use DB_Name

Backup Log  DB_Name with TRUNCATE_ONLY

dbcc shrinkfile ( DB_Name_log , 2)

-----------------------------------------清除交易紀錄檔-----------------------------------------

= 

3、資料庫壓縮

-----------------------------------------資料庫壓縮-----------------------------------------

DBCC SHRINKDATABASE ( DB_Name, 10)

-----------------------------------------資料庫壓縮-----------------------------------------

= 

4、DB Backup 「週備份七個檔案」

-----------------------------------------DB Backup 週備份-----------------------------------------

DECLARE @bkFileName nvarchar(255)

SET DATEFIRST 1

SELECT @bkFileName =

CASE DATEPART(dw, GETDATE())

WHEN 1 THEN N'D:\星期一.bak'

WHEN 2 THEN N'D:\星期二.bak'

WHEN 3 THEN N'D:\星期三.bak'

WHEN 4 THEN N'D:\星期四.bak'

WHEN 5 THEN N'D:\星期五.bak'

WHEN 6 THEN N'D:\星期六.bak'

WHEN 7 THEN N'D:\星期日.bak'

END

BACKUP DATABASE [DB_Name] TO DISK = @bkFileName WITH INIT,NOUNLOAD,NAME=N'SMARTIT 備份',NOSKIP,STATS=10,NOFORMAT

-----------------------------------------DB Backup 週備份-----------------------------------------

*DB_Name，意指SmartIT資料庫名稱
*D:\星期一.bak，意指備份路徑及備份檔案

 

========================================================== 

三、 設定Windows 排程，執行批次檔 

設定Windows 排程希望能由系統每天自動處理，減少人工操作部分

(1)、開啟Windows 排程，選擇[新增排定的工作]啟動視窗後再選擇[下一步]

 

-

(2)、選擇要執行的程式後，點選[下一步]

- 

(3)、選擇批次檔的名稱，在按下[開啟]

 

- 

(4)確認排程名稱後，選擇執行方式，再按[下一步]

 

-

(5)確認執行時間後，在點選[下一步]

 

- 

(6)輸入擁有可執行權限的帳號密碼後，點選[下一步]

 

- 

(7)完成設定，點選[完成]按鈕

- 

(8)設定完成後，開啟排程，並將設定頁面中，在?小時後停止排成的選項，取消勾選

完成後點選套用結束畫面

 

 

==========================================================  

四、 驗證排程是否可正常執行

完成排成設定後可按滑鼠右鍵，選擇[執行]來啟動排程，確認排程是否可執行完畢。

 

 

==========================================================   

五、排程1 「超過三十天的紀錄移轉至歷史紀錄」

 

-----------------------------------------移轉排程-----------------------------------------------

/* 1.網站稽核 */

DELETE FROM url_audit_history

WHERE  (url_audit_history_key IN

           (SELECT url_audit_key

            FROM  url_audit

            WHERE substring(audit_date, 1, 8) < Cast(CONVERT(Varchar(8), GetDate() -30, 112) AS varchar)))

INSERT INTO url_audit_history

SELECT  *

FROM    url_audit

WHERE (SUBSTRING(audit_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() -30,112) AS varchar))

delete from url_audit

where substring( audit_date, 1, 8) < Cast( Convert(Varchar(8), GetDate()-30,112) AS varchar)

/* 2.軟體變更 */

DELETE FROM software_change_history

WHERE (software_change_history_key IN

        (SELECT software_change_key

            FROM  software_change

            WHERE substring(change_date, 1, 8) < Cast(CONVERT(Varchar(8), 

                           GetDate() -30, 112) AS varchar)))

INSERT INTO software_change_history

SELECT   *

FROM    software_change

WHERE   (SUBSTRING(change_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() -30,112) AS varchar))

DELETE FROM software_change

WHERE   (SUBSTRING(change_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() -30,112) AS varchar))

/* 3.系統變更 */

DELETE FROM system_change_history

WHERE  (system_change_history_key IN

         (SELECT  system_change_key

           FROM  system_change

           WHERE  substring(audit_date, 1, 8) < Cast(CONVERT(Varchar(8), GetDate() - 30, 112) AS varchar)))

INSERT INTO system_change_history

SELECT   *

FROM    system_change

WHERE   (SUBSTRING(audit_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,112) AS varchar))

DELETE FROM system_change

WHERE   (SUBSTRING(audit_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,112) AS varchar))

/* 4.應用程式使用率 */

DELETE FROM appusage_history

WHERE (serial IN

      (SELECT serial

        FROM  appusage

        WHERE substring(uses_date, 1, 8) < Cast(CONVERT(Varchar(8), GetDate() - 30, 112) AS varchar)))

INSERT INTO appusage_history

SELECT         *

FROM    appusage

WHERE  (SUBSTRING(uses_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,112) AS varchar))

DELETE FROM appusage

WHERE  (SUBSTRING(uses_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,112) AS varchar))

/* 5.USB檔案稽核記錄  */

DELETE FROM files_audit_log_history

WHERE  (files_audit_log_history_key IN

       (SELECT  files_audit_log_key

         FROM  files_audit_log

         WHERE  substring(affect_date, 1, 8) < Cast(CONVERT(Varchar(8), GetDate()  - 30, 112) AS varchar)))

INSERT INTO files_audit_log_history

SELECT   *

FROM    files_audit_log

WHERE  (SUBSTRING(affect_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,112) AS varchar))

DELETE FROM files_audit_log

WHERE  (SUBSTRING(affect_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,112) AS varchar))

/* 6.管理端稽核記錄  */

DELETE FROM manage_change_log_history

WHERE (manage_change_log_history_key IN

        (SELECT    manage_change_key

         FROM     manage_change

          WHERE   substring(inventory_change_date, 1, 8)  < Cast(CONVERT(Varchar(8), GetDate() - 30, 112) AS varchar)))

INSERT INTO manage_change_log_history

SELECT  *

FROM     manage_change

WHERE  (SUBSTRING(inventory_change_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30, 112) AS varchar))

DELETE FROM manage_change

WHERE  (SUBSTRING(inventory_change_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30, 112) AS varchar))

/* 7.印表機列印記錄 */

DELETE FROM print_log_history

WHERE  (print_log_history_key IN

       (SELECT  print_log_key

        FROM    print_log

        WHERE  substring(print_time, 1, 8) < Cast(CONVERT(Varchar(8), GetDate()  - 30, 112) AS varchar)))

INSERT INTO print_log_history

SELECT   *

FROM     print_log

WHERE   (SUBSTRING(print_time, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30, 112)  AS varchar))

DELETE FROM print_log

WHERE   (SUBSTRING(print_time, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30, 112)  AS varchar))

/* 8.系統稽核記錄 */

DELETE FROM system_audit_log_history

WHERE   (system_audit_log_history_key IN

        (SELECT   system_audit_log_key

         FROM     system_audit_log

          WHERE   substring(audit_date, 1, 8) < Cast(CONVERT(Varchar(8), GetDate()   - 30, 112) AS varchar)))

INSERT INTO system_audit_log_history

SELECT   *

FROM    system_audit_log

WHERE   (SUBSTRING(audit_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,  112) AS varchar))

DELETE FROM system_audit_log

WHERE   (SUBSTRING(audit_date, 1, 8) < CAST(CONVERT(Varchar(8), GETDATE() - 30,  112) AS varchar))

-----------------------------------------移轉排程-----------------------------------------------

 

建議設定方式：

資料庫移轉排程通常為開啟多項稽核時進行設定。大量時則可設定一個月一次，在週六或週日執行比較不影響效能。

========================================================== 

六、排程２ 「將歷史紀錄移到其他資料庫」

說明：

使用下列指令可以將歷史紀錄移轉至其他資料庫做資料存放。

-------------------------- DB 資料移轉-----------------------------

/* 1.網站稽核 */

DELETE FROM  Backup_DB_Name.dbo.url_audit_history  --目的DB

WHERE  ( Backup_DB_Name.dbo.url_audit_history.url_audit_history_key IN  --目的DB比對值

           (SELECT  DB_Name.dbo.url_audit_history.url_audit_history_key  --來源DB比對值

            FROM   DB_Name.dbo.url_audit_history ))  --來源DB

INSERT INTO  Backup_DB_Name.dbo.url_audit_history  --目的DB

SELECT  * FROM     Backup_DB_Name.dbo.url_audit_history  --來源DB

DELETE FROM  Backup_DB_Name.dbo.url_audit_history  --來源DB

/* 2.應用程式使用率  */

DELETE FROM  Backup_DB_Name.dbo.appusage_history  --目的DB

WHERE ( Backup_DB_Name.dbo.appusage_history.serial IN  --目的DB比對值

      (SELECT  DB_Name.dbo.appusage_history.serial  --來源DB比對值

        FROM   DB_Name.dbo.appusage_history))  --來源DB

INSERT INTO  Backup_DB_Name .dbo.appusage_history  --目的DB

SELECT * FROM  DB_Name .dbo.appusage_history  --來源DB

DELETE FROM  DB_Name .dbo.appusage_history  --來源DB

/* 3.印表機列印記錄    */

DELETE FROM  Backup_DB_Name.dbo.print_log_history  --目的DB

WHERE  ( Backup_DB_Name.dbo.print_log_history.print_log_history_key IN  --目的DB比對值

       (SELECT   DB_Name.dbo.print_log_history.print_log_history_key  --來源DB比對值

        FROM     DB_Name.dbo.print_log_history ))  --來源DB

INSERT INTO  Backup_DB_Name.dbo.print_log_history  --目的DB

SELECT  * FROM   DB_Name.dbo.print_log_history  --來源DB

DELETE FROM  DB_Name.dbo.print_log_history  --來源DB

/* 4.軟體變更  */

DELETE FROM  Backup_DB_Name.dbo.software_change_history  --目的DB

WHERE ( Backup_DB_Name.dbo.software_change_history.software_change_history_key IN  --目的DB比對值

        (SELECT  DB_Name.dbo.software_change_history.software_change_history_key  --來源DB比對值

            FROM   DB_Name.dbo.software_change_history))  --來源DB

INSERT INTO  Backup_DB_Name.dbo.software_change_history  --目的DB

SELECT * FROM  DB_Name.dbo.software_change_history  --來源DB

DELETE FROM  DB_Name.dbo.software_change_history  --來源DB

/* 5.系統變更  */

DELETE FROM  Backup_DB_Name.dbo.system_change_history  --目的DB

WHERE  ( Backup_DB_Name.dbo.system_change_history.system_change_history_key IN  --目的DB比對值

         (SELECT  DB_Name.dbo.system_change_history.system_change_history_key  --來源DB比對值

           FROM   DB_Name.dbo.system_change_history))  --來源DB

INSERT INTO  Backup_DB_Name.dbo.system_change_history  --目的DB

SELECT * FROM  DB_Name.dbo.system_change_history  --來源DB

DELETE FROM  DB_Name.dbo.system_change_history  --來源DB

/* 6.管理端稽核記錄   */

DELETE FROM  Backup_DB_Name.dbo.manage_change_log_history  --目的DB

WHERE ( Backup_DB_Name.dbo.manage_change_log_history.manage_change_log_history_key IN  --目的DB比對值

        (SELECT   DB_Name.dbo.manage_change_log_history.manage_change_log_history_key  --來源DB比對值

         FROM    DB_Name.dbo.manage_change_log_history))  --來源DB

INSERT INTO  Backup_DB_Name.dbo.manage_change_log_history  --目的DB

SELECT * FROM  DB_Name.dbo.manage_change_log_history  --來源DB

DELETE FROM  DB_Name.dbo.manage_change_log_history  --來源DB

/* 7.即時通訊稽核 */

DELETE FROM  Backup_DB_Name.dbo.imlog_history  --目的DB

WHERE  ( Backup_DB_Name.dbo.imlog_history.serial IN  --目的DB比對值

           (SELECT  DB_Name.dbo.imlog_history.serial  --來源DB比對值

            FROM   DB_Name.dbo.imlog_history ))  --來源DB

INSERT INTO  Backup_DB_Name.dbo.imlog_history  --目的DB

SELECT  * FROM   DB_Name.dbo.imlog_history  --來源DB

DELETE FROM  DB_Name.dbo.imlog_history  --來源DB

/* 8.USB檔案稽核 */

DELETE FROM  Backup_DB_Name.dbo.files_audit_log_history  --目的DB

WHERE  ( Backup_DB_Name.dbo.files_audit_log_history.files_audit_log_history_key IN  --目的DB比對值

       (SELECT   DB_Name.dbo.files_audit_log_history.files_audit_log_history_key  --來源DB比對值

         FROM    DB_Name.dbo.files_audit_log_history ))     --來源DB

INSERT INTO  Backup_DB_Name.dbo.files_audit_log_history --目的DB

SELECT * FROM   DB_Name.dbo.files_audit_log_history   --來源DB

DELETE FROM   DB_Name.dbo.files_audit_log_history    --來源DB 

-------------------------- DB 資料移轉-----------------------------

*DB_Name，意指SmartIT來源資料庫名稱
*Backup_DB_Name，意指備份用的目的地資料庫名稱 

 

==========================================================

Share　

 

SQL Server 資料庫維護

 

本文件針對SQL Server 2005上的SmartIT資料庫作效能改進，與資料庫整理時可參考使用。

介紹說明：

一、DB Backup 「單一備份」
二、DB Backup 「每日備份至一週」
三、DB reindex 「資料庫索引重建」
四、資料庫壓縮
五、資料庫修復
六、快速查詢資料庫資料表筆數
七、清除交易紀錄檔
八、操作流程建議
九、排程設定圖解

 

註：文中所有之DB_Name，意指SmartIT之資料庫名稱，標記紅色字體為可變更。

 

一、DB Backup 「單一備份」

 

-----------------------------------------DB Backup-----------------------------------------------

BACKUP DATABASE [DB_Name]

TO DISK = N'D:\SmartIT_DB_BAK\smartit_bk.bak' WITH INIT,NOUNLOAD,
NAME = N'smartit_bk',NOSKIP,STATS = 10,NOFORMAT

----------------------------------------- DB Backup-----------------------------------------------

 

*DB_Name，意指SmartIT資料庫名稱
*D:\SmartIT_DB_BAK\smartit_bk.bak，意指備份路徑及備份檔案名稱

 

建議設定方式：

資料庫備份建議為一周一次，如果資料異動不多，

可設定一個月一次，在週六或週日執行比較不影響效能。

========================================================== 

二、DB Backup 「每日備份至一週」

說明：

使用下列指令可以備份資料庫，只備份七個檔案不會累加，檔名為週一到週日，

會做完整備份。

-------------------------- DB Backup 週備份-----------------------------
DECLARE @bkFileName nvarchar(255)
    SET DATEFIRST 1
    SELECT @bkFileName =
     CASE  DATEPART(dw, GETDATE()) 
       WHEN 1 THEN N'D:\星期一.bak'
       WHEN 2 THEN N'D:\星期二.bak'
       WHEN 3 THEN N'D:\星期三.bak'
       WHEN 4 THEN N'D:\星期四.bak'
       WHEN 5 THEN N'D:\星期五.bak'
       WHEN 6 THEN N'D:\星期六.bak'
       WHEN 7 THEN N'D:\星期日.bak'
     END
BACKUP DATABASE [DB_Name] TO DISK = @bkFileName WITH INIT,NOUNLOAD,NAME=N'SMARTIT 備份',NOSKIP,STATS=10,NOFORMAT
-------------------------- DB Backup 週備份-----------------------------

*DB_Name，意指SmartIT資料庫名稱
*D:\星期一.bak，意指備份路徑及備份檔案

建議操作設定方式：

使用此備份可設定每天都做備份，但是每次備份為full Backup，且不是累加方式備份，

只會產生7個檔案，週一到週日，如果硬碟夠大的話可以用來做這樣的備份。

 

==========================================================

三、DB reindex 「資料庫索引重建」

說明：

重建索引的目的在於：當使用者需透過該欄位，

從資料檔(或表格)搜尋所要的記錄(或行)時，借索引之助，可大幅提高資料檢索的速度。

------------------------ DBREINDEX ALL TABLE -----------------------------
DECLARE @TableName nvarchar(261)
DECLARE @SQLStatement nvarchar(4000)
DECLARE TableList CURSOR LOCAL FAST_FORWARD READ_ONLY  FOR
SELECT
    QUOTENAME(TABLE_SCHEMA) +
    N'.' +
    QUOTENAME(TABLE_NAME)
FROM
    INFORMATION_SCHEMA.TABLES
WHERE EXISTS
    (
    SELECT *
    FROM sysindexes
    WHERE id =
        OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
        N'.' +
        QUOTENAME(TABLE_NAME)) AND
        indid IN(0,1)
    )
OPEN TableList
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM TableList INTO @TableName
    IF @@FETCH_STATUS = -1 BREAK
    RAISERROR ('Reindexing %s', 0, 1, @TableName) WITH NOWAIT
    Print(@TableName)
    DBCC DBREINDEX (@TableName, '', 70)
    EXEC(@SQLStatement)
END
CLOSE TableList
DEALLOCATE TableList
------------------------ DBREINDEX ALL TABLE --------------------------

建議操作設定方式：

此設定建議在清除交易紀錄檔之前執行，

於每天晚上22:00執行，因執行後會有交易紀錄。

========================================================== 

四、資料庫壓縮

說明：

執行此設定會縮減 User DB 使用者資料庫的檔案大小，

讓User DB 的檔案騰出 10% 的可用空間。

--------------------------資料庫壓縮--------------------------

DBCC SHRINKDATABASE (DB_Name, 10)

--------------------------資料庫壓縮--------------------------

*DB_Name，意指SmartIT資料庫名稱

建議操作設定方式：

資料庫壓縮可設定手動執行，如有清除資料後再作此壓縮比較適合。

==========================================================

五、資料庫修復

說明：

使用此script的時機，主要在於當我們在使用報表時，

在擷取資料時如果出現『逾時過期』訊息時，建議使用下列指令修復資料庫，

特別注意第一行與最後一行『sp_dboption DB_Name,single, true』

『sp_dboption DB_Name,single, false』與中間指令要分開執行。

Step1:

----------------------------------------------------

sp_dboption DB_Name, single , true

----------------------------------------------------

**DB_Name，意指SmartIT資料庫名稱
**如執行時發生失敗，請重新啟動SQL Server Service後，再試一次

Step2:

----------------------------------------------------

dbcc checktable ("Table_Name",repair_allow_data_loss)
dbcc dbreindex ("Table_Name")

----------------------------------------------------

**Table_Name，意指SmartIT資料表名稱

Step3:

----------------------------------------------------

sp_dboption DB_Name, single , false

----------------------------------------------------

**DB_Name，意指SmartIT資料庫名稱

下列為可能需要修復的資料表對照表:

Table Name / 資料名稱

organization_group    組織
users                      人員
smartit                    SmartIT主表（電腦清單）
software                  軟體
virtual_software        軟體虛擬群組
virtual_software_mapping    虛擬群組對應
software_change       軟體變更
system_change         系統變更
appusage                 軟體使用率
url_audit                  網站稽核
print_log                  印表機稽核
imlog                       即時通訊稽核
files_audit_log          檔案抄寫稽核

**此段設定可針對有問題部分執行即可，不需全部執行。

建議操作設定方式：

資料庫修復使用時機適合在某資料表過大造成報表無法產生時執行，

執行時會將資料庫離線，建議在非資產回傳尖峰時間執行，

且須停止SmartIT Server服務，以加快執行速度。

========================================================== 

六、快速查詢資料庫資料表筆數

說明：

使用下列指令可以快速查詢資料表的大小，

藉此方式可以判斷是否有哪些資料表資料量過大，提供維護人員或管理人員參考使用。

-----------------------快速查詢資料庫資料表筆數-------------------------
select a.name,max(b.rows) as rows
from sysobjects a,sysindexes b where a.id= b.id and a.xtype = 'U'
group by a.name
order by max(b.rows)
-----------------------快速查詢資料庫資料表筆數-------------------------

建議操作設定方式：

使用此Script可搭配資料庫修復使用，目的在於先檢測資料庫每個資料表的資料量，

藉此判斷是否需要做資料庫修復。

 

========================================================== 

七、清除交易紀錄檔

說明：

交易紀錄檔的產生，是每個與資料庫連結的動作之紀錄，

若SQL Server的交易紀錄一直沒有去清除的話，會導致整個資料庫都不能使用。

指令說明：

執行Backup Log with Truncate_Only 其用法是備份資料庫的Log檔，

由於我們在語法中沒有指定備份的裝置為何，

Sql Server即會認為此動作為單純要把已交易完成的Log資料清空

(已交易完成的資料所指即為已commit的資料)，

而Truncate_Only的選項則是告訴Sql Server目的在清空Log之空間，

至於清出的空間則由Sql Server管理，不還給OS。

Backup Log完成後，要再執行DBCC SHRINKFILE ,其作為為將資料的空間作重整及壓縮至2M。

----------------------------清除交易紀錄檔--------------------------
use DB_Name
Backup Log DB_Name with TRUNCATE_ONLY
dbcc shrinkfile (DB_Name _log , 2)
----------------------------清除交易紀錄檔--------------------------

 *DB_Name，意指SmartIT資料庫名稱

--------清除DB Name為SmartIT的交易紀錄檔範例--------
use SmartIT
Backup Log SmartIT with TRUNCATE_ONLY
dbcc shrinkfile (SmartIT_log , 2)
--------清除DB Name為SmartIT的交易紀錄檔範例--------

建議操作設定方式：

建議交易紀錄檔可設定每天晚上23:30執行，交易紀錄檔越大，存取資料的速度也會越慢。

========================================================== 

八、操作流程建議：

Mdf 過大，資料量過多

順序為

1、資料表查詢 <確認資料表資料大小>

2、詢問user是否可以先進行瘦身(記錄檔維護)

3、清除交易紀錄檔

4、壓縮資料庫

5、db reindex 

6、備份

Ldf 過大，交易紀錄檔太大

順序為

1、清除交易紀錄檔

2、資料表查詢

3、詢問user是否可以先進行瘦身(記錄檔維護)

4、清除交易紀錄檔

5、壓縮

6、db reindex 

7、備份

========================================================== 

九、SQL Server 2005排程設定圖解說明

Step1：開啟資料庫程式。

 
Step2：選擇使用可設定排程的權限登入，並確認連結的資料庫是否正確。

 
Step3：開啟「SQL Server Agent」的作業，在右手邊空白處點選(滑鼠右鍵)，選擇新增作業。

 
Step4：設定排程作業的名稱，本範例 以重建索引設定來說明。

 
Step5：選擇「步驟」，在按下「新增」，建立一個指令執行步驟。

 
Step6：開啟頁面後，先設定步驟名稱、選擇資料庫、在將指令貼到命令區塊中，設定完成後按確定儲存。

 
Step7：選擇「排程」，在按下「新增」，建立一個執行步驟。

 
Step8：設定排程名稱，要執行的天數，與時間，最後再按下「確定」回到設定畫面。

 
Step9：設定完成後可在排程清單中看到所有設定項目。

 
Step10：

建議設定時間分開執行，為避免同時執行排程造成資料庫異常，

必需將每個排程時間都隔開。

Share　

如果要設定排程清除SQL 2008資料庫的交易紀錄檔，

除了將資料庫模式改為- 簡單

 

還可以設定排程執行下面指令即可

 

DBCC SHRINKFILE (SmartIT_log , 2)

--將交易記錄檔的大小壓縮到指定的大小

 

之前適用在SQL2000與SQL2005的指令在SQL2008上不適用

可以改此指令即可，給大家參考。

Share　

如果要設定排程清除SQL 2008資料庫的交易紀錄檔，

除了將資料庫模式改為- 簡單，還可以設定排程執行下面指令即可：

DBCC SHRINKFILE (SmartIT_log , 2)

--將交易記錄檔的大小壓縮到指定的大小

之前適用在SQL2000與SQL2005的指令在SQL2008上不適用

可以改使用此指令即可，供各位參考。

－

Share　

 

網址清單內網址資料的正確輸入規則

 

原先Smart IT 的資料庫如果是建置於SQL Server 2000 上，升級SmartIT 時若想將SQL 升級至SQL Server 2005 以上，可使用【備份】或【複製】來轉移資料。 

方法如下：

 

1.將SQL 資料庫【備份】或【複製】下來

 

【備份】備份出來的檔案為.bak

- 

 

 【複製】需複製的檔案為.mdf & .LDF 兩個檔案

 

 

 

 

2-1.如果是.bak 的備份檔，請使用【還原】的方式掛上資料庫，如下圖：

 

 

 

2-2.如果是為.mdf & .LDF 的檔案，請使用【附加】的方式掛上資料庫：

 

 

 

Share　

本文件針對SQL Server 2008經測試後進行壓縮交易紀錄檔已不支援之指令，用以資料庫整理時參考。

目前測試SQL2008已經不支援以下指令

1.     --01 使用 BACKUP LOG ... WITH NO_LOG  
2.     BACKUP LOG SmartIT
3.     WITH NO_LOG  
4.     GO  
5.       
6.     --02 或是，使用 BACKUP LOG ... WITH TRUNCATE_ONLY  
7.     BACKUP LOG SmartIT  
8.     WITH TRUNCATE_ONLY  
9.     GO  
10.    
11.   –03 或是
12.   DUMP TRANSACTION SmartIT With no_log

於SQL2008上避免交易紀錄擋過大的方式為將資料庫轉為「簡單復原模式」

手動操作方式如下：

Step 1.    對資料庫點選右鍵>屬性



Step2.    在選項裡面的<復原模式>選擇<簡單>，再按確定。交易紀錄檔就不會再增加了，若是要恢復原來模式，重複以上步驟再調回<完整>即可

         
         

若使用指令的話command如下：

1.      --01 將資料庫 SmartIT 切換為「簡單復原模式」，便會自動截斷交易記錄。  
2.      ALTER DATABASE SmartIT 
3.      SET RECOVERY SIMPLE  
4.     GO  
5.       
6.     --02 若決定要備份交易記錄檔(*.ldf)，再將資料庫 SmartIT 切換回「完整復原模式」 
7.     ALTER DATABASE SmartIT
8.     SET RECOVERY FULL  
9.     GO  

==================================

Share　

上班時間禁止玩開心農場

Facebook內提供遊戲竄紅,

在此提供給使用SmartIT 的管理者控管Facebook的方法,

Facebook瀏覽主網址
http://www.facebook.com/

Facebook附加程式主網址,遊戲及心理測驗都會導向此網址
http://apps.facebook.com/

 

如何設定控管禁止使用開心農場(其他小遊戲及心理測驗)

設定一: 新增Facebook的網址至清單

登入Console>資訊安全>網站瀏覽管理>網址清單

點選左方[新增]>新增一筆欲控管網址資料

網址輸入: http://apps.facebook.com/

網址標題輸入:facebook

確認輸入正確後儲存

*此規則會禁用所有網址標題包含Facebook(一般瀏覽Facebook訊息也會被禁用)

若只要控管附加程式,網址標題設定空白即可

設定二: 設定欲禁止瀏覽的電腦或使用者

登入Console>資訊安全>網站瀏覽管理>依網址作禁用管理

點選左方電腦或使用者>選擇黑名單模式

勾選剛新增的Facebook網址

確認輸入正確後儲存

 

*若欲開放中午允許瀏覽,於例外時間請勾選AM 12:00,並儲存.

*若是依使用者登入帳號控管,須於使用者下次登入才會套用設定.

Share　

控管鑲在Excel內的Flash遊戲

若想禁止使用者執行Excel內包覆的遊戲，

可於Client端C:\WINDOWS\system32\Macromed\Flash\

變更檔名Flash*b.ocx (*為Flash版本)

以上資訊分享。

* 變更時確認無Flash程式執行

*注意:變更結果，所有使用Flash元件都會受影響。