Rex IT Blog: 3月 2017

2017年3月21日星期二

Shrink Transaction Log While Using AlwaysOn

	--http://stackoverflow.com/questions/1435935/how-to-get-the-logical-name-of-the-transaction-log-in-sql-server-2005
	--get transaction log name
	--select Name from sys.database_files
	SELECT name as 'transaction log name' FROM sys.master_files WHERE database_id = db_id()AND type = 1;


	--http://dba.stackexchange.com/questions/73850/shrink-transaction-log-while-using-alwayson-availability-group
	--Execute on Primary Only
	if (SELECT role
	FROM sys.dm_hadr_availability_replica_states AS a
	JOIN sys.availability_replicas AS b
	ON b.replica_id = a.replica_id
	WHERE b.replica_server_name = @@SERVERNAME) = 1
	BEGIN
	Use [test_db]
	-- 1) Bakup Trn
	BACKUP LOG [test_db] TO DISK = N'D:\MSSQL\Backup\test_db.trn' WITH NOFORMAT, NOINIT, NAME = N' Trn Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
	-- 2) Move used pages
	DBCC SHRINKFILE (N'test_db_log' , 3000, NOTRUNCATE)
	-- 3) SHRINKFILE Log
	DBCC SHRINKFILE (N'test_db_log' , 3000)
	END

view raw Shrink Transaction Log While Using AlwaysOn hosted with ❤ by GitHub

2017年3月20日星期一

不使用GROUP BY去sum

use Northwind;

select OrderID,CustomerID,Freight,(select sum(Freight) from Orders) As 合計 from Orders;

select OrderID,CustomerID,Freight,sum(Freight) over ( partition by 1 ) As 合計 from Orders;

備註：如果使用
select OrderID,CustomerID,Freight,sum(Freight) from Orders會有以下錯誤訊息
訊息 8120，層級 16，狀態 1，行 1
Column 'Orders.OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.