Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

SELECT JsonData.out_batch_no,JsonData.openid,” a,” b,CONVERT(FLOAT,JsonData.total_amount*1.0/100) money,’补发’ remark, ROW_NUMBER() OVER(ORDER BY NEWID()) sn
FROM # a
CROSS APPLY OPENJSON(requestMsg)
WITH (
out_batch_no varchar(255) ‘$.out_batch_no’,
total_amount int ‘$.total_amount’,
openid varchar(255) ‘$.transfer_detail_list[0].openid’
— batch_name VARCHAR(100)
) AS JsonData


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

frp

服务器端:
bindPort = 7000
vhostHTTPSPort = 443

frps.exe -c ./frps.ini

本地端:
serverAddr = “8.138.110.11”
serverPort = 7000

[[proxies]]
name = “test_htts2http”
type = “https”
customDomains = [“sta.xxxx.cn”]

[proxies.plugin]
type = “https2http”
localAddr = “127.0.0.1:3333”

crtPath = “./sta.xxxx.cn_public.crt”
keyPath = “./sta.xxxx.cn.key”
hostHeaderRewrite = “127.0.0.1”
requestHeaders.set.x-from-where = “frp”

这样公网对域名sta.xxxx.cn的https请求都会回到本地的3333端口,且是http类型的。

—–http 访问statest.1232.cn:65046端口都会转向本地65046
bindPort = 7000
vhostHTTPPort = 65046

serverAddr = “x.x.x.x”
serverPort = 7000

[[proxies]]
name = “web”
type = “http”
localPort = 65046
customDomains = [“statest.1232.cn”]


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

sqserver目录下产生大量的dump故障转储文件,磁盘空间减少,系统比较慢。

分析log知,有一个错误,msdb。
运行dbcc checkdb('msdb'),发现sysjobhistory索引错误
重建索引:alter index clust on sysjobhistory rebuild
重新运行dbcc checkdb('msdb'),仍不解决
因job比较少,直接删除此索引解决。 drop index nc1 on sysjobhistory

参考: SqlServer上的 *.mdmp文件


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

进线的新粉分为3类,

  1. 纯新粉(正是我们要想要统计的)
  2. 重叠新粉,指新粉已添加了其他客服的微信,又添加了当前客服的微信
  3. 老粉重进,指被删除的老粉又重新添加,微信会不定时将删除的老粉以新粉状态推送出来。

针对第2种与第3种情况,客服人员很难识别出来,在统计新粉的时候会加进去,必然造成新粉多算。
而A9微信通以独创的身份识别技术,从根源上消灭了第2种与第3种情况,保证统计的新粉是精准的。

 


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

aspx页面打开缓慢一直白页,说明在进行排队,系统处理不来。
打开perfmon: asp.net 4.0 的Requests Current, Requests Queued. Requests Queued果然有几百个在等着。
解决方法:在排除程序问题后,
应用程序池-高级设置:最大进程模型开大,直到Requests Queued降下来。
IIS处理并发请求设置


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

查看日志发现是系统文件损坏,master库

重建master库

F:/Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Administrators /SAPWD=xxx /SQLCOLLATION=Chinese_PRC_CI_AS

sapwd是密码

重建后重新加载各用户库


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

/* 
 * 从内容中获取电话号码
 */
--SELECT dbo.GeTelFromContent('已订18620023427/4')
ALTER function [dbo].[GeTelFromContent](@str VARCHAR(MAX))
RETURNS VARCHAR(50)
AS
BEGIN
	DECLARE @validchars VARCHAR(100) = '[1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
	DECLARE @idx INT,@Result VARCHAR(50)
	
	SET @idx = PATINDEX('%'+ @validchars +'%',@str)
	--SELECT @idx
	IF @idx > 0 AND ( @idx = LEN(@str)-10 OR PATINDEX(SUBSTRING(@str,@idx+11,1),'[0-9]')=0 )
	BEGIN
		SET @Result=SUBSTRING(@str ,PATINDEX('%'+ @validchars +'%',@str), 11)
	END
	ELSE
	BEGIN
		SET @Result = ''
	END

	RETURN @Result

END	


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

问题:iisreset提示访问被拒绝,必须是该远程计算机的管理员才能使用此命令
原因:cmd.exe权限不是administrator引起
临时解决:右键cmd.exe 以管理员运行,执行iisreset解决
永远解决:建立cmd.exe的快捷方式或者开始里的cmd,右键-属性-快捷方式-高级-以管理员身份运行。
       或者建立windowssystem32iisreset.exe的快捷方式
       将快捷方式放到lanuch中,随时调用。


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

对数据库启用 透明数据加密(TDE)【此方法适用于 sql server 2008 及以后的版本(含2008)】

注:仅 sql server enterprise(企业版)支持此功能。

它是对整个数据库进行了加密,而且既然是“透明”,也就是说不会影响到任何对数据库的操作,正常的对数据库操作(增删改查什么的),还有备份恢复什么的,都不需要特别的考虑加密问题。只有离开了当前的数据库服务器,就会发现,什么都做不了。
对bak进行还原的时候,在UI上会提示介质找不到,用sql restore时会提示
找不到指纹为 '0xBAA127AA4C8BE3F4BAD4E1369DB9F2D0910D40BA' 的服务器 证书。
需要在新的服务器中导入原来的加密证书即可正常使用。

引自:SQLServer Transparent Data Encryption


SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys;

--整个加密
--1、在 master 数据库中,添加 数据库主密钥:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'helloworld';

--2、在 master 数据库中,添加 加密数据库用的证书:
USE master;
CREATE CERTIFICATE A9Safe WITH SUBJECT = 'A9Safe';

-- 3、在 要加密的数据库 中,设置 证书以及加密算法:
USE Authorize
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE A9Safe;

--对 要加密的数据库 启用加密:
ALTER DATABASE Authorize SET ENCRYPTION ON WITH ROLLBACK IMMEDIATE

USE CedarLog
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE A9Safe;

ALTER DATABASE CedarLog SET ENCRYPTION ON WITH ROLLBACK IMMEDIATE

USE S60623
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE A9Safe;

ALTER DATABASE S60623 SET ENCRYPTION ON WITH ROLLBACK IMMEDIATE

USE SMSDB
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE A9Safe;

ALTER DATABASE SMSDB SET ENCRYPTION ON WITH ROLLBACK IMMEDIATE


--5、首先要从 master 数据库中,备份加密证书:paswword 此密码用于保护私钥,恢复时使用
USE master;
BACKUP CERTIFICATE A9Safe TO FILE = 'D:A9Safe.cer'  
WITH PRIVATE KEY ( FILE = 'D:A9Safe.pkey', ENCRYPTION BY PASSWORD = '$$helloworld$$' );


--6、在其他数据库服务器中,仍然首先建立 数据库主密钥,同第1步操作;
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

--7、然后,开始从文件中恢复证书:
USE master;
CREATE CERTIFICATE A9Safe FROM FILE = 'D:A9Safe.cer'
WITH PRIVATE KEY ( FILE = 'D:A9Safe.pkey', DECRYPTION BY PASSWORD = '$$helloworld$$');

--需要注意的是,数据库加密的关键是 那个证书,数据库主密钥 是用来保护数据库信息的,比如证书的存放什么的,并不直接关系到数据库的加密。
--所以,一定要备份好证书!!!不然别到时候哭着解密不了数据库。


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

对于多值函数,但值类型不一致时,则以第一个为主进行转换
如:


SELECT CHOOSE(2,1.0,'0.25')   --0.3

SELECT IIF(2>3,1.0,'0.25') --0.3

当类型不一致时要转换
 
SELECT CHOOSE(2,1,CONVERT(DECIMAL(8,2),'0.25') )  --0.3
 


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

内存数据表有两大索引,一是hash,一是非聚集,对于hash索引对=起作用,若是范围查询,则会执行表扫描。可以建立hash索引后再建立个非聚集索引解决单个与范围查询的问题,经测试,内存数据表平均比SSD还要快上10倍以上。故可将热表放入内存中,如业绩统计表,利用它插入更新迅速的特点,写触发器更新,利用其查询速度快的优点,提升查询速度。

USE [master]
--创建数据库
CREATE DATABASE [TestDB]
ON  PRIMARY 
( NAME = N'TestDB', FILENAME = N'D:SQL2104SQLDataTestDB.mdf' , 
SIZE = 204800KB , MAXSIZE = UNLIMITED, FILEGROWTH = 204800KB ) 
LOG ON 
( NAME = N'TestDB_log', FILENAME = N'D:SQL2104SQLDataTestDB_log.ldf' , 
SIZE = 204800KB , MAXSIZE = 2048GB , FILEGROWTH = 204800KB )
GO
--创建内存表使用的文件组
ALTER DATABASE [TestDB] ADD FILEGROUP [TestDB_MFG1] CONTAINS MEMORY_OPTIMIZED_DATA
GO
--创建内存表使用的文件夹
ALTER DATABASE [TestDB] 
ADD FILE ( NAME = N'TestDB_MDir1', FILENAME = N'D:SQL2104SQLDataTestDB_MDir1') 
TO FILEGROUP [TestDB_MFG1]
GO

CREATE TABLE [dbo].[TB1_IM]
(
    [c1] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY 
    NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), 
    [c2] [nchar](200)  COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL 
    INDEX ix_c2 NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
    [c3] [nvarchar](200) COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL,
    [c4] [nvarchar](200) COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL,
    INDEX ix_c3_c4 NONCLUSTERED HASH(c3,c4) WITH(BUCKET_COUNT=1000000),
    INDEX ix_c2_c3  NONCLUSTERED (c2,c3)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

说明:
对于原有表,可以右键内存优化表来进行建立

1,内存索引表不支持ALTER TABLE 和 ALTER INDEX操作,如果需要修改表或者修改索引
2,对于内存优化表,有两种持续性可以选择:SCHEMA_ONLY(非持久表)和SCHEMA_AND_DATA(持久表),SCHEMA_ONLY 选项会导致数据在实例重启后丢失;而对于SCHEMA_AND_DATA(持久表),又可以设置完全持久行还是延迟持续性,延迟持续性选项允许在事务提交时可以不立即将日志写入磁盘,从而提升性能,当然代价就是发生故障时可能丢失数据。


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

CREATE FUNCTION [dbo].[UNIX_TIMESTAMP]
(
    @DATE DATETIME=''
)
       
RETURNS DECIMAL
AS
BEGIN
  --注意时区,北京时间统一减8变成格林尼治时间
   IF @DATE='' SET @DATE=GETDATE()
  SET @DATE=DATEADD(HOUR,-8,@DATE)
   RETURN DATEDIFF(SECOND,'1970-01-01',@DATE)
      
END

CREATE FUNCTION [dbo].[FROM_UNIXTIME]
(
    @i DECIMAL
)
       
RETURNS VARCHAR(20)
AS
BEGIN
 RETURN CONVERT(VARCHAR(20),DATEADD(SECOND,@i+28800,'1970-01-01'),120) --北京时间要加回8*3600=28800秒
END

引自:MSSQL(SQL SERVER)中获取UNIX时间戳


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

读取xml报16进制字符错误,因为xml文件中包括特殊字符,如u0018,造成解析错误
解析方法:剔除xml特殊字符再读取

  private string RemoveInvalidXmlChars(string text)
        {
            var validXmlChars = text.Where(ch => XmlConvert.IsXmlChar(ch)).ToArray();
            return new string(validXmlChars);
        }

private void OnRecvMessage(string Message) //接收到插件发来的消息触发该事件
        {
            try
            {
                RtxMessage rm = new RtxMessage();
              
                StringReader sr1 = new StringReader(RemoveInvalidXmlChars(Message.Content));//避免0/这种异常字符
                XmlReader xr1 = XmlTextReader.Create(sr1); // xr1's type is XmlTextReaderImpl

                while (xr1.Read())
                {
                    if (xr1.Name == "Item")
                    {

                        switch (xr1["Key"])
                        {
                            case "Mode": { if (xr1.Read()) { rm.Mode = xr1.Value; }; break; }
                            case "Content": { if (xr1.Read()) { rm.Content = GetContent(xr1.Value); }; break; }
                            case "Initiator": { if (xr1.Read()) { rm.Initiator = xr1.Value; }; break; }
                            case "Key": { if (xr1.Read()) { rm.Key = xr1.Value; }; break; }
                            case "Title": { if (xr1.Read()) { rm.Title = xr1.Value; }; break; }
                            case "im_message_id": { if (xr1.Read()) { rm.im_message_id = xr1.Value; }; break; }
                            default: { break; }
                        }
                    }
                }

             
            }
            catch (COMException xe)
            {
               
            }

        }


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

quarz:用来灵活的调度程序的执行
topself:用来将应用提升为服务

一,建立控制台应用程序,引用右键添加:quarz.net,topself,topself.log4Net
二,main中添加代码,用topself承接程序的管理

using System
using Quartz;
using Topshelf;
using System.IO;

namespace Quarz
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                log4net.Config.XmlConfigurator.ConfigureAndWatch(new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "log4net.config"));
                HostFactory.Run(x =>
                {
                    x.UseLog4Net();//记录topself自己产生的日志

                    x.Service<ServiceRunner>(); //ServiceRunner是quarz的调度程序,以此来调用

                    x.SetDescription("自己的WindowsService调度中心");
                    x.SetDisplayName("CedarQuarz");
                    x.SetServiceName("CedarQuarz");

                    x.EnablePauseAndContinue();
                });

            }
            catch (SchedulerException se)
            {
                Console.WriteLine(se);
            }
        }
    }
}

//ServiceRunner.cs如下
using Quartz;
using Quartz.Impl;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Topshelf;

namespace Quarz
{
    public sealed class ServiceRunner : ServiceControl, ServiceSuspend
    {
        private readonly IScheduler scheduler;

        public ServiceRunner()
        {
            scheduler = StdSchedulerFactory.GetDefaultScheduler();//调用调度计划,在quarz_job.xml配置要执行的job
        }

        public bool Start(HostControl hostControl)
        {
            scheduler.Start();
            return true;
        }

        public bool Stop(HostControl hostControl)
        {
            scheduler.Shutdown(false);
            return true;
        }

        public bool Continue(HostControl hostControl)
        {
            scheduler.ResumeAll();
            return true;
        }

        public bool Pause(HostControl hostControl)
        {
            scheduler.PauseAll();
            return true;
        }


    }
}
//自己写的job实现ijob即可
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Quartz;
using System.Threading.Tasks;
using RTXSAPILib;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;


namespace Quarz
{
    class RtxSendMsg : IJob
    {
        private static RTXSAPILib.IRTXSAPIRootObj _RootObj;
        public static RTXSAPILib.IRTXSAPIRootObj RootObj {
            get {
                if (_RootObj == null) {
                    _RootObj = new RTXSAPIRootObj();
                }
                return _RootObj;
            }
        }

        public static string ServerIP= ConfigurationSettings.AppSettings["RtxServerIP"];
        public static short ServerPort = short.Parse(ConfigurationSettings.AppSettings["RtxServerPort"]);
        public static string ConnString = ConfigurationSettings.AppSettings["constring"];
        public static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        public void Execute(IJobExecutionContext context)
        {
            log.Info("开始");
            //初始化服务器属性
            try
            {
                RootObj.ServerIP =   ServerIP;
                RootObj.ServerPort = ServerPort; //设置服务器端口
                DataTable dt = GetWaitNotifyInfo();
                if (dt == null || dt.Rows.Count == 0) return;
                foreach (DataRow dr in dt.Rows)
                {
                    SendNotify(StringHelper.FormatObjToString(dr["receiverAccount"]),
                        StringHelper.FormatObjToString(dr["CustomerID"]),
                        StringHelper.FormatObjToString(dr["CustomerName"]),
                        StringHelper.FormatObjToString(dr["D_StartTime"]),
                        StringHelper.FormatObjToString(dr["title"]), StringHelper.FormatObjToInt(dr["holdtime"]), StringHelper.FormatObjToString(dr["content"]));
                }
            }
            catch (Exception e) {
                log.Error(e.StackTrace.ToString());
            }
            log.Info("结束");
            //SendNotify("zhengxuesong", "hello", "", "【未接来电】客户65871于2015-10-16 14:10打来电话,请及时跟进");
        }

        public DataTable GetWaitNotifyInfo() {
            SqlParameter[] storedParams =
             {
                new SqlParameter("@Code",""),
                new SqlParameter("@UserID","")
            };
            IDataReader idr = Share.Data.SqlHelper.ExecuteReader(ConnString,
                  CommandType.StoredProcedure,
                  "GetWaitNotifyInfo",
                  storedParams);
            DataTable dt = new DataTable();
            dt.Load(idr);
            return dt;
        }

        public static void SendNotify(string receiverAccount, string CustomerID, string CustomerName, string D_StartTime,  string title, int holdtime, string content)
        {

            try
            {
                if (!RootObj.UserManager.IsUserExist(receiverAccount)) {
                    log.Warn(string.Format("{0}在腾讯通里不存在!", receiverAccount));
                    return ;
                };
                RootObj.SendNotify(receiverAccount, title, holdtime, content); //获取版本信息
                log.Info(string.Format("{0},{1},{2},{3},{4},{5},{6}", receiverAccount, CustomerID, CustomerName, D_StartTime, title, holdtime, content));
            }
            catch (Exception xe)
            {
                log.Error(xe.StackTrace.ToString());

            }

        }

    }
}

 

三,在app.config中指定job配置的xml文件,触发器的规格见文档

  <quartz>
    <add key="quartz.scheduler.instanceName" value="ExampleDefaultQuartzScheduler"/>
    <add key="quartz.threadPool.type" value="Quartz.Simpl.SimpleThreadPool, Quartz"/>
    <add key="quartz.threadPool.threadCount" value="10"/>
    <add key="quartz.threadPool.threadPriority" value="2"/>
    <add key="quartz.jobStore.misfireThreshold" value="60000"/>
    <add key="quartz.jobStore.type" value="Quartz.Simpl.RAMJobStore, Quartz"/>
    <!--******************************Plugin配置********************************************* -->
    <add key="quartz.plugin.xml.type" value="Quartz.Plugin.Xml.XMLSchedulingDataProcessorPlugin, Quartz" />
    <add key="quartz.plugin.xml.fileNames" value="~/quartz_jobs.xml"/>
  </quartz>

四,quartz_jobs.xml如下

<?xml version="1.0" encoding="UTF-8"?>

<!-- This file contains job definitions in schema version 2.0 format -->

<job-scheduling-data xmlns="http://quartznet.sourceforge.net/JobSchedulingData" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0">

  <processing-directives>
    <overwrite-existing-data>true</overwrite-existing-data>
  </processing-directives>

  <schedule>

    <!--TestJob测试 任务配置-->
    <job>
      <name>RtxSendMsg</name>
      <group>Rtx</group>
      <description>向rtx发送提醒</description>
      <job-type>Quarz.RtxSendMsg,Quarz</job-type>
      <durable>true</durable>
      <recover>false</recover>
    </job>
    <trigger>
      <cron>
        <name>向rtx发送提醒每10分钟</name>
        <group>Rtx</group>
        <job-name>RtxSendMsg</job-name>
        <job-group>Rtx</job-group>
        <start-time>2015-01-22T00:00:00+08:00</start-time>
        <cron-expression>0 0/10 * * * ?</cron-expression>
      </cron>
    </trigger>

  </schedule>
</job-scheduling-data>

五,注册服务在bin下执行

 myapp.exe install
 myapp.exe start
 myapp.exe stop
 myapp.exe uninstall


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

log4j.net用了好久,没有总结过,这次做个记录。
作用:可以缓存多少条再写入库,哪些写入哪些不写入可以配置

一,log4j.net诊断
在web.config或者app.config中增加:

 
  <system.diagnostics>
    <trace autoflush="true">
      <listeners>
        <add
          name="textWriterTraceListener"
          type="System.Diagnostics.TextWriterTraceListener"
          initializeData="C:testlog4net.txt" />
      </listeners>
    </trace>
  </system.diagnostics>

  <appSettings>
    <add key="log4net.Internal.Debug" value="true"/>
    </appSettings >

二,写入sqlserver
1,启动时加载

log4net.Config.XmlConfigurator.ConfigureAndWatch(new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "log4net.config"));

2,页面中使用:

public static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
log.Error(e.StackTrace.ToString());

3,数据库里建表


USE [K8]
GO
 
/****** Object:  Table [dbo].[Log4j]    Script Date: 2015/10/17 18:37:59 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Log4j](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[Thread] [VARCHAR](255) NOT NULL,
[Logger] [VARCHAR](255) NOT NULL,
[Date] [DATETIME] NOT NULL,
[Level] [VARCHAR](20) NOT NULL,
[Message] [VARCHAR](4000) NOT NULL,
 CONSTRAINT [PK_Log4j] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
 

4,log4net.config配置如下

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
  </configSections>

  <log4net>
    <appender name="AdoNetAppender_SqlServer" type="log4net.Appender.AdoNetAppender">
      <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
      <connectionString value="Data Source=127.0.0.1;Initial Catalog=mydb;User ID=sa;Password=helloworld;Connect Timeout=200;Max Pool Size=100;pooling=true;"/>
      <commandText value="INSERT INTO Log4j ([Date],[Thread],[Level],[Logger],[Message]) VALUES (@log_date, @thread, @log_level, @logger, @message)"/>
      <bufferSize value="5" />     <!--缓存多少条再写入库里-->
      <threshold value="All"/> <!--记录哪些,如info,debug,error-->
      <parameter>
        <parameterName value="@log_date"/>
        <dbType value="DateTime"/>
        <layout type="log4net.Layout.PatternLayout" value="%date{yyyy'-'MM'-'dd HH':'mm':'ss'.'fff}"/>
      </parameter>
      <parameter>
        <parameterName value="@thread"/>
        <dbType value="String"/>
        <size value="255"/>
        <layout type="log4net.Layout.PatternLayout" value="%thread"/>
      </parameter>
      <parameter>
        <parameterName value="@log_level"/>
        <dbType value="String"/>
        <size value="50"/>
        <layout type="log4net.Layout.PatternLayout" value="%level"/>
      </parameter>
      <parameter>
        <parameterName value="@logger"/>
        <dbType value="String"/>
        <size value="255"/>
        <layout type="log4net.Layout.PatternLayout" value="%logger"/>
      </parameter>
      <parameter>
        <parameterName value="@message"/>
        <dbType value="String"/>
        <size value="4000"/>
        <layout type="log4net.Layout.PatternLayout" value="%message"/>
      </parameter>
    </appender>
    
    <!-- 控制台前台显示日志   -->
    <appender name="ColoredConsoleAppender" type="log4net.Appender.ColoredConsoleAppender">
      <mapping>
        <level value="ERROR" />
        <foreColor value="Red, HighIntensity" />
      </mapping>
      <mapping>
        <level value="Info" />
        <foreColor value="Green" />
      </mapping>
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%n%date{HH:mm:ss,fff} [%-5level] %m" />
      </layout>

      <filter type="log4net.Filter.LevelRangeFilter">
        <param name="LevelMin" value="Info" />
        <param name="LevelMax" value="Fatal" />
      </filter>
    </appender>


    <root>
      <!--(高) OFF > FATAL > ERROR > WARN > INFO > DEBUG > ALL (低) -->
      <level value="all" />
      <appender-ref ref="AdoNetAppender_SqlServer"/>
      <!--
      <appender-ref ref="ColoredConsoleAppender"/>
 <appender-ref ref="RollingLogFileAppender"/>
       -->

    </root>
  </log4net>
</configuration>


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[p_lockinfo]
    @kill_lock_spid BIT = 1 , --是否杀掉死锁的进程,1 杀掉, 0 仅显示
    @show_spid_if_nolock BIT = 1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
AS 
    DECLARE @count INT ,
        @s VARCHAR(MAX) ,
        @i INT
    SELECT  id = IDENTITY( INT,1,1 ),
            标志 ,
            进程ID = spid ,
            线程ID = kpid ,
            块进程ID = blocked ,
            数据库ID = dbid ,
            数据库名 = DB_NAME(dbid) ,
            用户ID = uid ,
            用户名 = loginame ,
            累计CPU时间 = cpu ,
            登陆时间 = login_time ,
            打开事务数 = open_tran ,
            进程状态 = status ,
            工作站名 = hostname ,
            应用程序名 = program_name ,
            工作站进程ID = hostprocess ,
            域名 = nt_domain ,
            网卡地址 = net_address,
			sql_handle,
			stmt_start,
			stmt_end

    INTO    #t
    FROM    ( SELECT    标志 = '死锁的进程' ,
                        spid ,
                        kpid ,
                        a.blocked ,
                        dbid ,
                        uid ,
                        loginame ,
                        cpu ,
                        login_time ,
                        open_tran ,
                        status ,
                        hostname ,
                        program_name ,
                        hostprocess ,
                        nt_domain ,
                        net_address ,
                        s1 = a.spid ,
                        s2 = 0,
						sql_handle,
						stmt_start,
						stmt_end
              FROM      master..sysprocesses a
                        JOIN ( SELECT   blocked
                               FROM     master..sysprocesses
                               GROUP BY blocked
                             ) b ON a.spid = b.blocked
              WHERE     a.blocked = 0
              UNION ALL
              SELECT    '|_牺牲品_>' ,
                        spid ,
                        kpid ,
                        blocked ,
                        dbid ,
                        uid ,
                        loginame ,
                        cpu ,
                        login_time ,
                        open_tran ,
                        status ,
                        hostname ,
                        program_name ,
                        hostprocess ,
                        nt_domain ,
                        net_address ,
                        s1 = blocked ,
                        s2 = 1,
						sql_handle,
						stmt_start,
						stmt_end
              FROM      master..sysprocesses a
              WHERE     blocked <> 0
            ) a
    ORDER BY s1 ,
            s2

    SELECT  @count = @@rowcount ,
            @i = 1

    IF @count = 0
        AND @show_spid_if_nolock = 1 
        BEGIN
            INSERT  #t
                    SELECT  标志 = '正常的进程' ,
                            spid ,
                            kpid ,
                            blocked ,
                            dbid ,
                            DB_NAME(dbid) ,
                            uid ,
                            loginame ,
                            cpu ,
                            login_time ,
                            open_tran ,
                            status ,
                            hostname ,
                            program_name ,
                            hostprocess ,
                            nt_domain ,
                            net_address,
							sql_handle,
							stmt_start,
							stmt_end
                    FROM    master..sysprocesses
            SET @count = @@rowcount
        END

    IF @count > 0 
        BEGIN
            CREATE TABLE #t1
                (
                  id INT IDENTITY(1, 1) ,
                  a NVARCHAR(3000) ,
                  b INT ,
                  EventInfo NVARCHAR(2000)
                )
            IF @kill_lock_spid = 1 
                BEGIN
                    DECLARE @spid VARCHAR(50) ,
                        @标志 VARCHAR(50)
                    WHILE @i <= @count 
                        BEGIN
                            SELECT  @spid = 进程ID ,
                                    @标志 = 标志
                            FROM    #t
                            WHERE   id = @i
                            INSERT  #t1
                                    EXEC ( 'dbcc inputbuffer(' + @spid + ')'
                                        )
                            IF @标志 = '死锁的进程' 
                                EXEC('kill '+@spid)
                            SET @i = @i + 1
                        END
                END
            ELSE 
                WHILE @i <= @count 
                    BEGIN
                        SELECT  @s = 'dbcc inputbuffer('
                                + CAST(进程ID AS VARCHAR) + ')'
                        FROM    #t
                        WHERE   id = @i
                        INSERT  #t1
                                EXEC ( @s
                                    )
                        SET @i = @i + 1
                    END
            SELECT  标志 ,
			 数据库名 ,
			  进程的SQL语句 = b.EventInfo,
					sqlText=substring(qt.text,a.stmt_start/2, 
 					(case when a.stmt_end = -1 
						  then len(convert(nvarchar(max), qt.text)) * 2 
						  else a.stmt_end end -a.stmt_start)/2
					) ,
					 进程状态,
            用户名,
            累计CPU时间,
            登陆时间  ,
            工作站名 ,
            应用程序名,
			 进程ID ,
            线程ID  
            FROM    #t a
                    JOIN #t1 b ON a.id = b.id
					OUTER apply sys.dm_exec_sql_text(a.sql_handle) as qt
        END
GO


SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO




--==========================
--创建人: zxs 20110728

--==========================
CREATE       PROCEDURE [dbo].[p_cpu]
@TopNum INT =NULL,
@Last_Execution_Time VARCHAR(50) = NULL
AS
SET @TopNum = ISNULL(@TopNum,50) --默认前50条
SET @Last_Execution_Time = ISNULL(@Last_Execution_Time,'2012-01-01') --默认前50条
/*
SELECT TOP (@TopNum)
    qs.total_worker_time/qs.execution_count/1000. as [平均消耗CPU 时间(ms)],
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1,
        (case when qs.statement_end_offset = -1
        then DATALENGTH(qt.text)
        else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1)
    as [查询语句], qt.text [所在存储过程],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
 
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE qs.last_execution_time >=@Last_Execution_Time --限定时间
ORDER BY
        [平均消耗CPU 时间(ms)] DESC
*/

	SELECT SUM([平均消耗CPU 时间(ms)])[平均消耗CPU 时间(ms)],
	SUM([总消耗CPU 时间(ms)]) [总消耗CPU 时间(ms)],
	SUM([运行次数])[运行次数],[查询语句],[所在存储过程],
	dbid, dbname,objectid,ObjectName
	FROM (
	SELECT TOP (50)
		qs.total_worker_time/qs.execution_count/1000. as [平均消耗CPU 时间(ms)],
		total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
	    
		dbo.SQLSig(SUBSTRING(qt.text,qs.statement_start_offset/2+1, --利用sqlsig函数进行参数化,以屏蔽传来的参数,使之标准化
			(case when qs.statement_end_offset = -1
			then DATALENGTH(qt.text)
			else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1),4000)
		as [查询语句], qt.text [所在存储过程],
		qt.dbid, dbname=db_name(qt.dbid),
		qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
	FROM sys.dm_exec_query_stats qs
	cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
	WHERE qs.last_execution_time >=CONVERT(VARCHAR(10),GETDATE(),120)
	ORDER BY [平均消耗CPU 时间(ms)] DESC
	)M
	GROUP BY [查询语句],[所在存储过程],dbid, dbname,objectid,ObjectName
	ORDER BY [平均消耗CPU 时间(ms)] DESC

GO


SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO

--==========================
--创建人: zxs 20120420 
--查询当前的锁,及相关sql语句
--==========================
CREATE       PROCEDURE [dbo].[p_curwaitdropindex]
	AS
Select d.database_id,d.name,t.object_id, ix.index_id,user_updates, (user_seeks+user_scans) AS use_Count,t.name AS tb_name,ix.name AS ix_name,sc.name AS col_name
    from sys.dm_db_index_usage_stats ius
	JOIN sys.databases d ON d.database_id=ius.database_id
    JOIN sys.tables t ON ius.object_id = t.object_id
    JOIN sys.indexes ix ON t.object_id = ix.object_id AND ix.index_id = ius.index_id
    JOIN sys.index_columns ixc ON t.object_id = ixc.object_id AND ix.index_id = ixc.index_id
    JOIN sys.columns sc ON t.object_id = sc.object_id AND ixc.column_id = sc.column_id
    where user_updates > 10 * (user_seeks+user_scans)
    and ius.index_id > 1
    AND CHARINDEX('merge',t.name) <1
    order by user_updates / (user_seeks+user_scans+1) DESC

GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO



--==========================
--创建人: zxs 20110728

--==========================
CREATE       PROCEDURE [dbo].[p_curRun]
AS 

	SELECT DB_NAME(r.database_id) DB,s.session_id,
	substring(qt.text,r.statement_start_offset/2,
	(case when r.statement_end_offset = -1
	then len(convert(nvarchar(max), qt.text)) * 2
	else r.statement_end_offset end -r.statement_start_offset)/2
	) as 'SQL statement',
	qt.text batch,s.status,c.client_net_address,s.login_name, s.program_name,s.host_name,
	s.login_time,c.connect_time,s.last_request_start_time, s.last_request_end_time,s.session_id,
	s.host_process_id, s.client_version, s.client_interface_name,c.net_transport, c.net_packet_size,
	r.request_id, r.start_time, r.status, r.command, r.user_id, r.blocking_session_id, r.wait_type,r.wait_time,
	r.last_wait_type, r.wait_resource, r.open_transaction_count,r.transaction_id, r.percent_complete, r.cpu_time, r.reads, r.writes,r.granted_query_memory
	FROM Sys.dm_exec_connections c
	JOIN Sys.dm_exec_sessions s ON c.session_id = s.session_id
	JOIN Sys.dm_exec_requests r ON s.session_id = r.session_id
	outer apply sys.dm_exec_sql_text(r.sql_handle) as qt
	WHERE r.status = 'running' AND s.session_id != @@SPID
	ORDER BY c.client_net_address,s.login_name


GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO





--==========================
--创建人: zxs 20120420 
--查询当前的锁,及相关sql语句
--==========================
CREATE       PROCEDURE [dbo].[p_curlock]
	AS
SELECT TL.resource_type AS ResType
,TL.resource_description AS ResDescr
,TL.request_mode AS ReqMode
,TL.request_type AS ReqType
,TL.request_status AS ReqStatus
,TL.request_owner_type AS ReqOwnerType
,TAT.[name] AS TransName
,TAT.transaction_begin_time AS TransBegin
,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura
,ES.session_id AS S_Id
,ES.login_name AS LoginName
,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName
,PARIDX.name AS IndexName
,ES.host_name AS HostName
,ES.program_name AS ProgramName
,REQ.command AS ReqCommand
,SUBSTRING(EST.text
,1 + REQ.statement_start_offset / 2
,(CASE WHEN REQ.statement_end_offset = -1
THEN LEN(convert(nvarchar(max), EST.text)) * 2
ELSE REQ.statement_end_offset END - REQ.statement_start_offset) / 2
) AS SqlStatement
FROM sys.dm_tran_locks AS TL
INNER JOIN sys.dm_exec_sessions AS ES
ON TL.request_session_id = ES.session_id
LEFT JOIN sys.dm_tran_active_transactions AS TAT
ON TL.request_owner_id = TAT.transaction_id
AND TL.request_owner_type = 'TRANSACTION'
LEFT JOIN sys.objects AS OBJ
ON TL.resource_associated_entity_id = OBJ.object_id
AND TL.resource_type = 'OBJECT'
LEFT JOIN sys.partitions AS PAR
ON TL.resource_associated_entity_id = PAR.hobt_id
AND TL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT')

LEFT JOIN sys.objects AS PAROBJ
ON PAR.object_id = PAROBJ.object_id
LEFT JOIN sys.indexes AS PARIDX
ON PAR.object_id = PARIDX.object_id
AND PAR.index_id = PARIDX.index_id
LEFT JOIN sys.dm_exec_requests AS REQ
ON TAT.transaction_id = REQ.transaction_id
outer APPLY sys.dm_exec_sql_text(REQ.sql_handle) AS EST

WHERE TL.resource_database_id = DB_ID()
AND ES.session_id <> @@Spid
AND TL.request_mode like  '%x%' 
ORDER BY TL.resource_type
,TL.request_mode
,TL.request_type
,TL.request_status
,ObjectName
,ES.login_name;

GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO





--==========================
--创建人: zxs 20120420 
--查询当前事务
--==========================
CREATE       PROCEDURE [dbo].[p_curtran]
AS
		
	SELECT CASE WHEN TDT.database_id = 32767
	THEN 'MSSQLSystemResource'
	ELSE DB.name END AS DatabaseName
	,REQ.start_time AS ReqStart
	,TAT.transaction_begin_time AS TransBegin
	,TAT.name AS TransName
	,CASE TDT.database_transaction_type
	WHEN 1 THEN N'Read/Write'
	WHEN 2 THEN N'Read-only'
	WHEN 3 THEN N'System'
	ELSE N'Unkown' END AS TransType
	,CASE TAT.transaction_state
	WHEN 0 THEN N'Not initialized'
	WHEN 1 THEN N'Not started'
	WHEN 2 THEN N'Active'
	WHEN 3 THEN N'Ended'
	WHEN 4 THEN N'DTC active'
	WHEN 5 THEN N'Preparing'
	WHEN 6 THEN N'Committing'
	WHEN 7 THEN N'Being rolled back'
	WHEN 8 THEN N'Rolled back'
	ELSE N'Unkown'
	END AS TransState
	,REQ.[status] AS ReqStatus
	,TDT.database_transaction_log_record_count AS LogRec
	,TDT.database_transaction_log_bytes_used AS LogBytes
	,SES.login_name AS LoginName
	,REQ.wait_type AS ReqWaitType
	,REQ.percent_complete AS [ReqCompl%]
	,REQ.command AS ReqCommand
	,SUBSTRING(EST.text
	,1 + REQ.statement_start_offset / 2
	,(CASE WHEN REQ.statement_end_offset = -1
	THEN LEN(convert(nvarchar(max), EST.text)) * 2
	ELSE REQ.statement_end_offset END - REQ.statement_start_offset) / 2
	) AS SqlStatement
	FROM sys.dm_tran_active_transactions AS TAT
	INNER JOIN sys.dm_tran_database_transactions AS TDT
	ON TAT.transaction_id = TDT.transaction_id
	INNER JOIN sys.databases AS DB
	ON TDT.database_id = DB.database_id
	LEFT JOIN sys.dm_tran_session_transactions AS TST
	ON TAT.transaction_id = TST.transaction_id
	LEFT JOIN sys.dm_exec_requests AS REQ
	ON TAT.transaction_id = REQ.transaction_id
	LEFT JOIN sys.dm_exec_sessions AS SES
	ON REQ.session_id = SES.session_id
	CROSS APPLY sys.dm_exec_sql_text(REQ.sql_handle) AS EST
	WHERE TAT.transaction_id > 255
	AND ISNULL(REQ.session_id, -1) <> @@SPID
	AND TDT.database_id <> DB_ID(N'tempdb')
	ORDER BY DatabaseName
	,TransBegin
	,TransName;
GO


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

需求:文件内容比较多,审查发现js函数后,要找到它所引用的js文件
方法:chrome下在console里录入函数,不要(),点链接就能直接到所引用的js文件定义处。


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

dbcc checkprimaryfile('E:dba1.mdf',2)

Syntax:DBCC CHECKPRIMARYFILE ({'PhysicalFileName'} [,opt={0|1|2|3}])

PhysicalFileName is the full path for the primary database file.

opt=0 - checks if the file a primary database file. 
opt=1 - returns name, size, maxsize, status and path of all files associated to the database.
opt=2 - returns the database name, version and collation.
opt=3 - returns name, status and path of all files associated with the database.