查看temp db
use tempdb -- 每隔1秒钟运行一次,直到用户手工终止脚本运行while 1=1 ...
数据库readonly的解决方法
网上通常的做法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帮忙终于弄好了,吓死吓死我了明天得找阿三...
log备份异常 错误码 4215 及解决方法
上周末发现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 ...
4215 The log was not truncated because records at the beginning of the log
备份时报错
远程副本没有恢复到足以启用数据库镜像或将其联接到可用性组的程度。
远程副本没有恢复到足以启用数据库镜像或将其联接到可用性组的程度。您需要通过从主体/主数据库还原当前日志备份,将缺失的记录日志应用于远程数据库。 (Microsoft SQL Server,错误: 1408)
shrink temp db without reboot mssqlserver
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/...
抓取最大的tmstmp
alter procedure dba.GetMaxTMstmp @tbname nvarchar(100)asdeclare @Max_TMstmp bigintdeclare @sql nvarchar(4000)declare @sql2 nvarchar(4000)declare @i int=0--set @tbname='bond'--print @tbnamebegin--print @tbnameset @sql2='select @i=count(1) from sys.columns col join sys.tables tbl on ...
Log backup for a secondary replica failed because last backup LSN
https://www.sqlshack.com/understanding-backups-on-alwayson-availability-groups-part-1/Log backup for database "test1_ag2" on a secondary replica failed because the last backup LSN (0x00000020:000000ad:0001) from the primary database is greater than the current local redo LSN (0x00000020:000000ac:0...
查看信息
SELECT SERVERPROPERTY('MachineName') AS ComputerName, SERVERPROPERTY('ServerName') AS InstanceName, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, ...
关于监控
https://docs.influxdata.com/influxdb/v1.2/guides/writing_data/http://1316478764.iteye.com/blog/2100778
SQL数据库发mail
一、先启动邮件服务SQL SCRIPTS exec master..sp_configure 'show advanced options',1 goreconfiguregoexec master..sp_configure 'Database mail XPs',1goreconfigurego二、创建发邮件时所需要的account 点击(此处)折叠或打开exec msdb..sysmail_add_account_sp @account_name = 'OPS...
用MSSQL发邮件
exec master..sp_configure ,1 go exec master..sp_configure ,1 go
alwayson的备份还原脚本
1、 备份数据库在主副本上,将需要做AlwaysOn的数据库做一次全备和日志备份(NOTE:禁用事务日志备份作业,如果有的话)替换参数,执行如下脚本生成备份语句,然后执行:DECLARE @DBNameNVARCHAR(255)DECLARE @SQLNVARCHAR(MAX)DECLARE @BackupToPathNVARCHAR(500) SET @DBName='dat...
ApplicationIntent=ReadOnly
ApplicationIntent=ReadOnly
MSSQL修改已有数据的表的自增列
use demogocreate table testid([ID] [bigint] IDENTITY(1,1) NOT NULL,col1 varchar(100),[TMSTAMP] [timestamp] NOT NULL)insert into testid(col1) values ('a')insert into testid(col1) values ('b')insert into testid(col1) values ('c')create table testid2([ID] [bigint] IDENTITY(1,1) NOT ...
AGStatus SQL
n.replica_server_name, rs.role_desc, DB_NAME(drs.database_id) as 'database_name', CASE WHEN drs.is_suspended=0 THEN 'RESUMED' ELSE WHEN drs.suspend_reason_desc='SUSPEND_FROM_PARTNER' THEN '在强制故障转移后挂起数据库副本' WHEN drs.suspend_reason_desc='SUSPEND_FROM_APPLY' THEN '在将日志写入文件...
查看各个表所用空间大小
CREATE PROCEDURE [dbo].[sys_viewTableSpace]ASBEGINSET NOCOUNT ON;CREATE TABLE [dbo].#tableinfo( 表名 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, 记录数 [int] NULL, 预留空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, 使用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NU...
sp_replflush
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 0exec sp_replflush
phpmyadmin 的配置 可实现记录即使操作,登陆管理多个DB等
点击(此处)折叠或打开
Ubuntu和Debian的phpmyadmin的配置文件路径在/etc/phpmyadmin上
and may differ in some ways from the official phpMyAdmin documentation. Specifically it does: