SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
t.modify_date AS ModifyDate,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, t.modify_date, p.Rows
ORDER BY
t.Name
--釋放佔用的記憶體
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREESESSIONCACHE
GO
CREATE TABLE IIS_Log(
[date] date null,
....
....
)
BULK INSERT IIS_Log FROM 'C:\inetpub\logs\Logfiles\W3SVC1\u_ex18023.log'
WITH (
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
}
cd c:\Windows\System32\Sysprep
sysprep /generalize /oobe /mode:vm /shutdown
--使用語法如下
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
AND S.database_id = DB_ID()
CREATE TABLE Cust(
ID varchar(11) primary key,
Name nvarchar(10),
Birth date,
Mail varchar(50),
Credit varchar(19))
GO
INSERT INTO Cust
VALUES ('A123456789', N'陳一', '19700101', 'aa@gmail.com', '1111-2222-3333-4444')
, ('D234567890', N'蔡三', '19790202', 'bb@gmail.com', '5555-6666-7777-8888')
, ('H987654321', N'老王', '19810303', 'cb@gmail.com', '9999-1111-2222-3333')
ALTER TABLE Cust ALTER COLUMN Birthday ADD MASKED WITH(FUNCTION='default()')
CREATE USER masktester WITHOUT LOGIN
GRANT SELECT ON Cust TO masktester
EXEC AS USER = 'masktester'
SELECT * FROM Cust
ALTER TABLE Cust ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION = 'partial(4,"-XXXX-XXXX-",4)')
ALTER TABLE Cust ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')
EXEC AS USER = 'masktester'
SELECT * FROM Cust
REVERT
--關閉遮罩
GRANT UNMASK TO masktester
--恢復遮罩
REVOKE UNMASK TO masktester
--查出前十名總累積IO數最高的的Query
SELECT TOP 10
total_logical_reads+total_logical_writes AS [總IO],
execution_count AS [執行次數],
total_logical_reads AS [讀取IO數],
total_logical_writes AS [寫入IO數],
st.text AS [SQL指令]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_logical_reads+total_logical_writes> 0
ORDER BY [總IO] DESC
--查出前十名總使用CPU時間最高的的Query
SELECT TOP 10
qs.total_worker_time AS [總執行時間],
qs.execution_count AS [執行次數],
qs.total_worker_time / qs.execution_count AS [平均執行時間],
SUBSTRING(qt.text, qs.statement_start_offset / 2,
(CASE WHEN qs.statement_end_offset = -1 THEN len(CONVERT (NVARCHAR (MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) AS [SQL指令]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
ORDER BY [總執行時間] DESC;
--建立測試資料
IF OBJECT_ID('#TT','U') IS NOT NULL
DROP TABLE #TT
CREATE TABLE #TT
(
id INT,
name VARCHAR(20),
sex char(1),
memo VARCHAR(50),
cr_date datetime default(getdate())
)
INSERT INTO #TT
(id,name,sex,memo)
VALUES
(1,N'John',N'M',N'test11111'),
(2,N'Lee',N'F',N'test22222'),
(3,N'Penny',N'F',N'test33333'),
(4,N'Wade',N'M',N'test444444'),
(5,N'James',N'M',N'test55555')
DECLARE @Content VARCHAR(MAX)
--將所有欄位名稱設成TD，再以''分隔
SET @Content = CAST((SELECT name AS 'TD','',
CASE sex WHEN 'M' THEN '男性' WHEN 'F' THEN '女性' END as 'TD','',
memo AS 'TD',''
FROM #TT
FOR XML PATH('tr'),TYPE) AS VARCHAR(MAX))
DECLARE @HTML VARCHAR(MAX)
SET @HTML='<HTML><BODY><TABLE border=1><TH>姓名</TH><TH>性別</TH><TH>MEMO</TH>'+@Content+'</TABLE>'
SELECT @HTML
USE [master]
ALTER DATABASE [APPSYS] SET PARTNER FAILOVER;USE [master]
ALTER DATABASE MyDB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS --連至備援端DB移除資料庫鏡像
USE [master]
--中斷鏡像同步後，如主體端DB沒有做過Log備份，都可以再次設定鏡像同步
ALTER DATABASE [APPSYS] SET PARTNER OFF;
--將資料庫恢復成RECOVERY模式，可進行存取的狀態
RESTORE DATABASE [APPSYS] WITH RECOVERY;select db_name(database_id) as '資料庫名稱',mirroring_state_desc as '鏡像狀態' from sys.database_mirroring
where mirroring_state is not nullexec sp_help_revlogin
--顯示Login Create Script
--CREATE LOGIN [tt01] WITH PASSWORD = 0x01005B429E7F53752B8B2B8DE7D5F97512738BA25CC527490C78 HASHED, SID = 0x51236A2E950F2A4680B087495907AFE3, DEFAULT_DATABASE = [APPSYS], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF--暫停鏡像，以利後續作業
ALTER DATABASE APPSYS SET PARTNER SUSPEND;
--恢復鏡像
ALTER DATABASE APPSYS SET PARTNER RESUME;
--需等待後續的資料同步完成當SQL Server有特定錯誤發生時，管理人員也沒有時間經常去查看SQL Server錯誤檔， 於是有時特定錯誤可能很重要時，但卻沒有人被通知到，現在小弟實作一個主動通知的機制。 首先先新增警示，在這裡選擇想要被通知的錯誤訊息的等級或其它設定 接著在回應頁籤中，勾選執...
