ITPub博客

Use Master Alter Database YOURDB SET SINGLE_USER With ROLLBACK IMMEDIATE RESTORE DATABASE YOURDB FROM DISK = 'C:Program FilesMicrosoft SQL ServerMSSQLBACKUPYOURDB.bak'use Master ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE; [@more@]

/*--调用示例exec p_lockinfo--*/--create proc p_lockinfodeclare@kill_lock_spid bit, --是否杀掉死锁的进程,1 杀掉, 0 仅显示@show_spid_if_nolock bit, --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示--as @count int,@s nvarchar(1000),@i int select @kill_lock_spid=0 select @show_spid_if_nolock=1select 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, 进程状

the issue is appeared afer we install the wintel os VTMand our REG dba found the DB cannot be accessed I login the server find that the MSSQL service si shutdown I try to startup the service with serivce account , but it show error initerrlog: Could not open error log file ''. Operating system error = 3(The system cannot find the path specified.).follow the error message, I check the service account privilege on log file.it have the full control.hence eems the issue is not caused by filesystem

显示某个Sql Server某个数据库中所有表或视图的信息 sql server 2000 与 2005 不同 差别在于 红色字部分以下语句为获取所有表信息，替换绿色黑体字"U"为"V"为获取所有视图信息。Sql Server 2000 版本SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH, sysproperties.[value] AS COLUMN_DESCRIPTION, syscomments.text asCOLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumnsINNER

EXEC master.dbo.sp_configure 'show advanced options', 1RECONFIGUREEXEC master.dbo.sp_configure 'xp_cmdshell', 1RECONFIGUREEXEC SP_CONFIGURE 'show advanced option', '1'; RECONFIGURE WITH OVERRIDE; EXEC SP_CONFIGURE 'xp_cmdshell';[@more@]

when I try to create a Maintenance Plan through the Wizard, I got error as belos:TITLE: Microsoft SQL Server Management Studio------------------------------Exception has been thrown by the target of an invocation. (mscorlib)------------------------------ADDITIONAL INFORMATION:Cannot find folder "Maintenance Plans".------------------------------Cannot find folder "Maintenance Plans".------------------------------BUTTONS:OK------------------------------then I open profile and f

1.通过系统的“性能”来查看：开始->管理工具->性能（或者是运行里面输入 mmc）然后通过添加计数器添加 SQL 的常用统计 然后在下面列出的项目里面选择用户连接就可以时时查询到数据库的连接数了。不过此方法的话需要有访问那台计算机的权限，就是要通过windows账户登陆进去才可以添加此计数器。2.通过系统表来查询：SELECT * FROM [Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT [DBID] FROM [Master].[dbo].[SYSDATABASES] WHERE NAME='databaseName')databaseName 是需要查看的数据库，然后查询出来的行数

http://support.microsoft.com/kb/811889/zh-cn本文分步介绍如何排查“Cannot generate SSPI context”错误消息最常见的根源。在下列情形中，您可能会收到此错误消息： 正在与 SQL Server 建立连接。 正在使用集成安全性。 使用 Kerberos 执行安全委派。回到顶端了解 Kerberos 术语和服务主体名称客户端计算机上的 SQL Server 驱动程序利用集成安全性来使用用户帐户的 Windows 安全令牌，以便成功连接到运行 SQL Server 的计算机。Windows 安全令牌从客户端委派到运行 SQL Server 的计算机。当使用下列某一配置将用户的安全令牌从一台计算

SQL Express by default has 'AUTO_CLOSE' database option set to true. This means when your database has no users connected, the database status will show 'Shutdown Normal'. When a user connects to the database, it is started up and status is changed to 'Normal'. To leave the database on and available all the time, set the 'AUTO_CLOSE' option to FALSE. It can be done by running this script against each user database upon creation or after the fact: Code Block ALTER DATABASE SET AUTO_CLOSE OFF[@mor

/*--获取连接SQL服务器的信息 所有连接本机的:操作的数据库名,计算机名,用户名,网卡物理地址,IP地址,程序名 -*/ /*--调用示例 --显示所有本机的连接信息 exec p_getlinkinfo --显示所有本机的连接信息,包含ip地址 exec p_getlinkinfo @includeip=1 --显示连接指定数据库的信息 exec p_getlinkinfo '客户资料' --*/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_getlinkinfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_getlinkinfo] GO create proc p_getlinkinfo @dbname sys

1. 了解SQL Server Profiler SQL Server Profiler 是图形化实时监视工具，能帮助系统管理员监视数据库和服务器之行为, 比如死锁之数量，致命之错误，跟踪Transact-SQL 语句和存储过程。可以把这些监视数据存入表或文件中，并在以后某一时间重新显示这些事件来一步一步之进行分析。 通常我们使用SQL Server Profiler 仅监视某些插入事件，这些事件主要有：登录连接之失败、成功或断开连接；DELETE、 INSERT、 UPDATE 命令；远程存储过程调用（RPC） 之状态；存储过程之开始或结束，以及存储过程中之每一条语句；写入SQL Server 错误

今天连db结果遇到如下错误：SQL SERVER – FIX : Error 945 Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details第一直觉就是dbf的权限问题，一查果然。。。。由于空间不够还顺便把dbf文件移动了下再attach上sql模板如下：CREATE DATABASE [ALS_LFE] ON PRIMARY ( NAME = N'DXXXXXXXXXXXXXXXXX', FILENAME = N'DXXXXXXXXXXXXXXXXX.mdf' , MAXSIZE = XXXXKB, FILEGROWTH = XXXXKB )LOG ON ( NAME = N'DXXXXXXXXXXXXXXXXX_log', FILENAME = N'DXXXXXXXXX