ITPub博客

SELECT SERVERNAME = CONVERT(NVARCHAR(128),SERVERPROPERTY('SERVERNAME')) ,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer',CLIENT_NET_ADDRESS AS 'ClientIPAddress',* FROM SYS.DM_EXEC_CONNECTIONS 

-----创建审核对象之前需要切换到master数据库--USE [master]--GO--CREATE SERVER AUDIT MyAudit TO FILE(FILEPATH='D:\SqlAudits') --这里指定文件夹不能指定文件，生成文件都会保存在这个文件夹--GO--实际上，我们在创建审核对象的同时可以指定审核选项，下面是相关脚本--把日志放在磁盘的好处是可以使用新增的TVF:...

select a.name,a.[type],b.[definition] from sys.all_objects a,sys.sql_modules b where a.is_ms_shipped=0 and a.object_id = b.object_id and a.[type] in ('V') and b.[definition] LIKE N'%[吖-咗]%' COLLATE Chinese_PRC_CI_ASorder by a.[name] asc

SELECT TOP 1 b.type, b.first_lsn, b.last_lsn, b.checkpoint_lsn, b.database_backup_lsn FROM msdb..restorehistory a INNER JOIN msdb..backupset b ON a.backup_set_id = b.backup_set_id WHERE a.destination_database_name = 'TMSTAMP_test' ORDER BY restore_date DESCuse tmstamp_te...

创建SP点击(此处)折叠或打开create procedure sp_maintain_partion_fg (@tableName varchar(50),@inputdate datetime )as begindeclare@fileGroupName varchar(50),@ndfName varchar(5...

ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON--查询数据库状态select name,user_access,user_access_desc,    snapshot_isolation_state,snapshot_isolation_state_desc,    is_read_committed_snapshot_onfrom sys.databases   --设置数据库为SINGLE_USER模式,减少...

https://social.technet.microsoft.com/Search/en-US?query=2671387&Refinement=44&ac=4

SELECT  [Spid] = session_id ,            ecid ,            [Database] = DB_NAME(sp.dbid) ,            [User] = nt_username ,            [Status] = er.status ,  &nbsp...

select a.table_name,a.max_tmstamp-b.max_tmstamp,a.row_count-b.row_count from [DBCenter].[dbo].[viewMaxTMtamp] a with (nolock) join [DBCenter].[dbo].[viewMaxTMtamp] b on a.databse_name=b.databse_name and a.table_name=b.table_name and cast(a.record_time as date) = cast(dateadd(day,-1,getda...

点击(此处)折叠或打开--CREATE PROCEDURE gen_createtbl @tbl_name varchar(100) --AS set nocount on    --声明游标需要的变量     declare @tblname varchar(100), &nbsp...

点击(此处)折叠或打开USE [DBCenter]GOCREATE PROCEDURE [dba].[sys_tmp_monitor_85]ASbegintruncate table dbcenter..tmpdbmonitor_SQL insert into dbcenter..tmpdbmonitor_85 ...

察看AG group的面板，察看AG组是否正常，如果面板显示正常执行2的步骤，面板显示异常的请根据异常提示来执行操作，本篇内容不涉及处理这些异常执行以下语句点击(此处)折叠或打开select n.group_name,    n.replica_server_name, &nbsp...

USE master;GODECLARE @SQL VARCHAR(MAX);while 1=1SET @SQL=''SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)FROM master..sysprocessesWHERE dbid=DB_ID('datayesdb');EXEC(@SQL);waitfor delay '0:0:1' GO

use tempdb                                                               -- 每隔1秒钟运行一次，直到用户手工终止脚本运行while 1=1  ...

网上通常的做法USE master;GODECLARE @SQL VARCHAR(MAX);SET @SQL=''SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)FROM master..sysprocessesWHERE dbid=DB_ID('datayesdb');EXEC(@SQL);GOALTER DATABASE [datayesdb] SET MULTI_USER 上面的方法不好用,然后求助lg帮忙终于弄好了,吓死吓死我了明天得找阿三...

上周末发现db02组的alwayson 同步异常，所以权衡了各个方法后决定采用重新加载DYY数据库的方式来修复异常。但是当在主库上执行全备和log备的操作时，发现以下的错误信息。The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or ...

备份时报错 

远程副本没有恢复到足以启用数据库镜像或将其联接到可用性组的程度。您需要通过从主体/主数据库还原当前日志备份，将缺失的记录日志应用于远程数据库。  (Microsoft SQL Server，错误: 1408)

use tempdbCHECKPOINT;GODBCC DROPCLEANBUFFERS;GODBCC FREEPROCCACHE;GODBCC FREESYSTEMCACHE ('ALL');GODBCC FREESESSIONCACHE;GODBCC SHRINKFILE (TEMPDEV2, 1024);   --- New file size in MBGOhttps://sqlsunday.com/2013/08/11/shrinking-tempdb-without-restarting-sql-server/...