So Tired !_! 逆水行舟, 不进则退!

24Jun/15

MSSQL 2005 查看表的索引碎片状态

Posted by Nick Xu

MSSQL 2005 提供了一个动态管理函数 sys.dm_db_index_physical_stats
通过调用这个函数,可以方便直观地查看到指定表或视图的数据和索引的大小和碎片信息。
下面这条语句,就可以查看当前数据库中所有索引的碎片情况
SELECT
object_name(a.object_id) [TableName]
,a.index_id
,name [IndexName]
,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
DB_ID()
, NULL
, NULL, NULL, NULL
) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id;
如果 avg_fragmentation_in_percent 一项的值在
10.0 - 30.0 建议使用 ALTER INDEX .. REORGANIZE 语句重新组织索引
> 30.0 建议使用 ALTER INDEX .. REBUILD 语句重建索引
SQL 2005 在 ALTER INDEX 语句中提供了一个重要的参数 ONLINE,如果想重建索引的时候,不会将表锁住,需要将该参数设置为 ON。
例如:
ALTER INDEX ALL ON [dbo].[RecordTable]
REBUILD WITH (
SORT_IN_TEMPDB = ON
,STATISTICS_NORECOMPUTE = ON
,ONLINE = ON);
ONLINE 参数说明如下:
ONLINE = { ON | OFF }
指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。默认值为 OFF。

ON
在索引操作期间不持有长期表锁。在索引操作的主要阶段,源表上只使用意向共享 (IS) 锁。这使得能够继续对基础表和索引进行查询或更新。操作开始时,将对源对象保持极短时间的共享 (S) 锁。操作结束时,如果创建非聚集索引,将对源持有极短时间的 S 锁;当在线创建或删除聚集索引时,或者重新生成聚集或非聚集索引时,将获取 SCH-M(架构修改)锁。对本地临时表创建索引时,ONLINE 不能设置为 ON。
OFF
在索引操作期间应用表锁。创建、重新生成或删除聚集索引或者重新生成或删除非聚集索引的脱机索引操作将对表获取架构修改 (Sch-M) 锁。这样可以防止所有用户在操作期间访问基础表。创建非聚集索引的脱机索引操作将对表获取共享 (S) 锁。这样可以防止更新基础表,但允许读操作(如 SELECT 语句)。
有关详细信息,请参阅联机索引操作的工作方式。有关锁的详细信息,请参阅锁模式。

索引(包括全局临时表中的索引)可以联机重新生成,但以下索引除外:

禁用的索引
XML 索引
本地临时表中的索引
分区索引
聚集索引(如果基础表包含 LOB 数据类型)。
使用 LOB 数据类型列定义的非聚集索引
如果表包含 LOB 数据类型,但这些列中没有任何列在索引定义中用作键列或非键列,则可以联机重新生成非聚集索引。

10Jun/15

postfix smtp服务配置

Posted by Nick Xu

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
# See /usr/share/postfix/main.cf.dist for a commented, more complete version
 
# Debian specific:  Specifying a file name will cause the first
 
# line of that file to be used as the name.  The Debian default
 
# is /etc/mailname.
 
#myorigin = /etc/mailname
 
#smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
 
smtpd_banner = $myhostname ESMTP Server.
 
biff = no
 
# appending .domain is the MUA's job.
 
append_dot_mydomain = no
 
# Uncomment the next line to generate "delayed mail" warnings
 
#delay_warning_time = 4h
 
readme_directory = no
 
# TLS parameters
 
smtpd_tls_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem
 
smtpd_tls_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
 
smtpd_use_tls=yes
 
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
 
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
 
smtpd_client_restrictions = check_client_access hash:/etc/postfix/client_checks,reject
 
# See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for
 
# information on enabling SSL in the smtp client.
 
myhostname = nick-ubuntu-server
 
alias_maps = hash:/etc/aliases
 
alias_database = hash:/etc/aliases
 
#mydestination参数非常重要,因为只有当发来的邮件的收件人地址与该参数值相匹配时,Postfix才会将该邮件接收下来。通过该选项的设置可以过滤掉许多没有经过认证和授权的邮件,从而节省服务器的存储空间,以及节省用户的邮件处理时间。
mydestination = nick-ubuntu-server, localhost.localdomain, youdomain.com
 
relayhost = localhost
relay_domains = nick.txtcc.com
 
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128 mailbox_size_limit = 0 recipient_delimiter = + inet_interfaces = all inet_protocols = all home_mailbox = Mailbox
1
2
cat /etc/postfix/client_checks
127.0.0.1 OK
1
postmap /etc/postfix/client_checks && /etc/init.d/postfix restart
10Jun/15

Send a test mail using Telnet

Posted by Nick Xu

Emulating an SMTP session with Telnet

Let us try to send an email to the user now. As the "example.com" domain does not really existing your DNS settings will likely not point to the right server. So we are simulating an SMTP session with the telnet command. Install the telnet package if you haven't already:

$> aptitude install telnet

Then establish a TCP connection to the SMTP port:

$> telnet localhost smtp

The server should reply:

Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 mailtest ESMTP Postfix (Debian/GNU)

Great. Postfix is listening and wants us to speak SMTP. First we need to be friendly:

ehlo example.com

Postfix appreciates our friendliness and tells us which features it provides:

250-my-new-mailserver
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-ENHANCEDSTATUSCODES
250-8BITMIME
250 DSN

Hey, Postfix, we have a mail from steve@example.com:

mail from:<steve@example.com>

Looks like Postfix is happy with that because return codes that start with a '2' are good news:

250 2.1.0 Ok

Tell Postfix who the recipient of the mail is:

rcpt to:<john@example.com>

Postfix accepts that:

250 2.1.5 Ok

Then we are ready to send the actual mail:

data

Postfix agrees and tells us we can send the actual mail now and end our input with a dot on an empty line:

354 End data with <CR><LF>.<CR><LF>

Okay, type in the mail:

Hi John,

just wanted to drop you a note.
.

Postfix tells us it has received the mail and queued under a queue ID:

250 2.0.0 Ok: queued as A9D64379C4

Thanks, Postfix, we are done:

quit

Checking the logs

Take a look at the /var/log/mail.log file now. You should see something similar to:

postfix/smtpd[...]: connect from localhost[127.0.0.1]
postfix/smtpd[...]: 5FF712A6: client=localhost[127.0.0.1]
postfix/cleanup[...]: 5FF712A6: message-id=<...>
postfix/qmgr[...]: 5FF712A6: from=<steve@example.com>, size=364, nrcpt=1 (queue active)
postfix/pipe[...]: 5FF712A6: to=<john@example.com>, relay=dovecot, ..., status=sent (delivered via dovecot service)
postfix/qmgr[...]: 5FF712A6: removed
postfix/smtpd[...]: disconnect from localhost[127.0.0.1]

The delivery has worked. Postfix has correctly determined that the destination domain is a virtual domain and forwarded the email to the 'dovecot' service.

Checking the user's mailbox

The email should now be somewhere under /var/vmail/example.com/john. Let us take a look:

$> cd /var/vmail/example.com/john/Maildir
$> find
.
./cur
./new
./new/1179521979.V801I2bbf7M15352.mailtest
./tmp

There sits the email. Try to read the mail with the "mutt" program:

$> mutt -f .

The new email is shown:

q:Quit  d:Del  u:Undel  s:Save  m:Mail  r:Reply  g:Group  ?:Help
   1 N   May 18 steve@example.c (0.1K)

Press ENTER to read the email:

From: steve@example.com
To: undisclosed-recipients: ;

Hi John,

just wanted to drop you a note.

So the email arrived at John's account. Perfect. Choose 'q' twice to exit mutt again.

Tagged as: , , , Comments Off
5Jun/15

windows at 定时任务

Posted by Nick Xu

at 命令的参数:
At计划在指定时间和日期在计算机上运行命令和程序。at 命令只能在“计划”服务运行时使用。如果在没有参数的情况下使用,则 at 列出已计划的命令。
at [[\\ComputerName] hours:minutes [/interactive] [{/every:date[,...]|/next:date[,...]}] command]
参数
\\computername
指定远程计算机。如果省略该参数,则 at 计划本地计算机上的命令和程序。
ID
指定指派给已计划命令的识别码。
/delete
取消已计划的命令。如果省略了 ID,则计算机中所有已计划的命令将被取消。
/yes
删除已计划的事件时,对来自系统的所有询问都回答“是”。
hours:minutes
指定命令运行的时间。该时间用 24 小时制(即从 00:00 [午夜] 到 23:59)的 小时: 分钟格式表示。
terative
对于在运行 command 时登录的用户,允许 command 与该用户的桌面进行交互。
/every:
在每个星期或月的指定日期(例如,每个星期四,或每月的第三天)运行 command 命令。
date
指定运行命令的日期。可以指定一周的某日或多日(即,键入 M、T、W、Th、F、S、Su)或一个月中的某日或多日(即,键入从 1 到31 之间的数字)。用逗号分隔多个日期项。如果省略了 date,则 at 使用该月的当前日。
/next:
在下一个指定日期(比如,下一个星期四)到来时运行 command。
command
指定要运行的 Windows 命令、程序(.exe 或 .com 文件)或批处理程序(.bat 或 .cmd 文件)。当命令需要路径作为参数时,请使用绝对路径,也就是从驱动器号开始的整个路径。如果命令在远程计算机上,请指定服务器和共享名的通用命名协定 (UNC) 符号,而不是远程驱动器号。
/?
在命令提示符显示帮助.

 

每天8点关机,这个用的是星期,可以少输点,at 8:00 /every:m,t,w,th,f,s,su shutdown -s

Tagged as: , Comments Off
   
site
site