<br />SELECT *<br />FROM sys.configurations<br /> <br /><br />SELECT DATABASEPROPERTYEX('databaseName','IsAutoClose');<br /> <br /> ValueDescriptionValue returnedIsAutoCloseDatabase shuts down cleanly and frees resources after the last user exits.1 = T

<br />SELECT *<br />FROM sys.configurations<br /> <br /><br />SELECT DATABASEPROPERTYEX('databaseName','IsAutoClose');<br /> <br /> ValueDescriptionValue returnedIsAutoCloseDatabase shuts down cleanly and frees resources after the last user exits.1 = T

<br />http://technet.microsoft.com/en-us/library/cc938567.aspx<br /> <br />Table 5.5 Comparison of Process Data Supplied by Task Manager and System Monitor <br />Task Manager process measure<br /> Description<br />System Monitor process objec

<br />1 Solution: Import/Export (Using SSIS,bulk insert )<br /> <br /> <br />2 Replication Service (e.g: Snapshot Replication : Using BCP)<br /> <br />Workflow: drop the table and recreate and then bulk copy in the data. The level of locking you will see w

<br />http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/09/29/what-session-created-that-object-in-tempdb.aspxThe random ramblings and rantings of frazzled SQL Server DBAAnother use for the Default Trace: Mapping Temporary Tables to Sessions<br />Here

<br />http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/12/05/tracing-trigger-execution-and-code-path-using-sql-profiler.aspxTracing Trigger Execution (and code path) using SQL Profiler <br />Ever wondered how to trac

<br />    WITH abc AS<br />(<br />    SELECT<br />        last_user_seek,<br />        last_user_scan,<br />        last_user_lookup,<br />        last_user_update<br />    FROM<br />        sys.dm_db_index_usage_stats<br />    WHERE<br /

PART 1 :sort in memory or sort split to tempdb, memory allocationSET STATISTICS TIME ONGOmonitor activitySELECT num_of_reads, num_of_bytes_read,num_of_writes,num_of_bytess_writtenFROM sys.dm_io_virtual_file_stats(db_id('tempdb'),1)Memory pres

http://www.mssqltips.com/tip.asp?tip=1338ProblemSome tasks that are run in SQL Sever take along time to run and it is sometimes difficult to tell whether thesetasks are progressing or not.  One common way of determining thatstatus is to look at the

SQLAlso consider some online SQL formatting tools:SQLInform's online SQL formatter - requires Java in your browser.SQLRefactor from Red Gate - ~$300Instant SQL Formatter online at dpriver.com Apparently they have a SQL Management Studio plugin for $5

http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008.Sometime, it looks impossible to shrinkthe Tru

1. Create table with identity columnCREATE TABLE tb(  id INT IDENTITY ,  name VARCHAR(100) );insert into tb values ('aa')GO 102. Insert into table tb (-1,'bb') - ignore idenity --set identity_insert tablename onset identity_insert tb ongoin

bulk insert语句和bcp实用工具http://www.it118.org/specials/c9fba99e-4401-49cf-8256-ac3c1a34c0d9/8bf47df8-cad8-4d97-9a54-ba461a9c017a.htmbulk insert语句和bcp实用工具用于在SQL SERVER 数据库和数据文件之间复制数据.(ps:关于导入和导出数据的更多使用方法,可以参考《sql server联机丛书》的 “管理 SQL Server ”-->“导入和导出数据”章节)bulk

(1) sp_spaceused 'tablename'(2)find out large readsDBCC DROPCLEANBUFFERS   DBCC FREEPROCCACHESET STATISTICS IO ON   SET STATISTICS TIME ON(3) find out indexes spaceSELECT name,used,*FROM SysindexesWHERE id=object_id('tablename')  ;(4) unused inde

http://topic.youkuaiyun.com/u/20080612/22/bb2dfa83-1cf3-4a0b-9bd4-5a39e6193556.html--遠程連接操作/******************************************************************************************************************************************************Tab表:/*ID 自增

-- disable all constraintsEXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"-- delete data in all tablesEXEC sp_MSForEachTable "DELETE FROM ?"-- enable all constraintsexec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

 当用户使用*.bak去还原时候,database 可能带有user,但这个还没有注册到login里面,可以用: EXEC sp_change_users_login Auto_Fix,username 去自动更新Login http://www.seattletech.com/forum/viewtopic.php?t=71 After the use

当用户角色只有public (没有db_datareader 和 db_datawriter) 是看不见表和存储过程的  (1)授予用户 dbUser 对 XX 数据库中所有表的 SELECT 权限 sp_msforeachtable GRANT SELECT ON OBJECT::? TO DbUser;  GRANT INSERT ON OBJE

<br />http://www.mssqltips.com/tip.asp?tip=1475<br /> <br />Problem<br />Very often when performance problems arisethere are a few tools that I always first look at using to helptroubleshoot SQL Server issues. One of these tools is PerformanceMonit

<br />http://blog.sqlauthority.com/2007/08/24/sql-server-2005-t-sql-script-to-attach-and-detach-database/<br />Following script can be used to detachor attach database. If database is to be from one database to anotherdatabase following script can be u

1. SQL Server Management Studio:When we run a long time multiple steps store procedure, we would like to know what is the steps runing, we can add 'RAISERROR ('Start', 0, 0) WITH NOWAIT' into store procduresample:CREATE PROC Test_ProcASBEGINRAISERR

1. SP_WHO2sp_who2 returnsSPID --> which is the process under considerationBLK --> if it has a value represents the SPID of the blocking processEx. SPID =10 BLK=51 --> Process 10 is being blocked by process 51DBCC INPUTBUFFER(SPID) - tell us what is t

<br />Displays the definition of a user-defined rule, default, unencryptedTransact-SQL stored procedure, user-defined Transact-SQL function,trigger, computed column, CHECK constraint, view, or system object suchas a system stored procedure.<br /> <br

<br /><!-- /* Font Definitions */ @font-face {font-family:SimSun; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:宋体; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;}

http://technet.microsoft.com/en-us/library/ms189493.aspxRemarksDBCC SHRINKFILEapplies to the files in the current database. For more informationabout how to change the current database, see USE (Transact-SQL).DBCC SHRINKFILE operations can be stopp

sp_configure 'show advanced options',1 ; GO RECONFIGURE; GO sp_configure 'max worker threads'http://technet.microsoft.com/en-us/library/ms187024.aspxhttp://loadrunnertnt.blogspot.com/2007/05/monitors-ms-sql-server.htmlUser Connections (SQLServer: G

Script :USE [DBNAME]GOSELECT    DB_NAME() as DatabaseName,        CAST(sysfiles.size/128.0 AS int) AS FileSizeMB,         sysfiles.name AS LogicalFileName,         CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name,        'SpaceUsed')

<br />SELECT TE.name AS Event_Name,*<br />INTO Trace_Table<br />FROM fn_trace_gettable('C:/Users/Administrator/XXXXX.trc', default) Trace<br />INNER JOIN sys.trace_events TE ON Trace.EventClass = TE.trace_event_id<br /><br /> 

select b.name, a.last_execution_timefrom sys.dm_exec_procedure_stats a inner join sys.objects b on a.object_id = b.object_id where DB_NAME(a.database_ID) = 'databsename'

<br />http://www.mssqltips.com/tip.asp?tip=1515<br />Problem<br />In a previous tip, "Setting up Performance Monitor to always collect performance statistics"I wrote about how to collect performance monitor data,  but once youhave the data then wha

--返回由备份集内包含的数据库和日志文件列表组成的结果集RESTORE FILELISTONLY    FROM DISK = d:/Northwind.bak--还原由BACKUP备份的数据库RESTORE DATABASE Northwind   FROM DISK = d:/Northwind.bak --指定还原后的数据库物理文件名称及路径 (可用于clus