SQL 2005
DBCC HELP(‘?’)
DBCC TraceOn(2580)
DBCC HELP(‘?’)
SQL 2008 and above
DBCC HELP(‘?’)
DBCC TraceOn(2588)
DBCC HELP(‘?’)
Archive for July, 2014
DBCC help(‘?’) want to see all hidden DBCC command
Posted by Simon Cho on 07/22/2014
Posted in Common | Tagged: DBCC, DBCC HELP, DBCC HIDDEN COMMAND, DBCC TraceOn | Leave a Comment »
DBCC SHRINKFILE isn’t working even simple recovery mode.
Posted by Simon Cho on 07/15/2014
Log file Shrink has several dependency.
1. Log backup(or Checkpoint in simple recovery mode)
2. Replication
3. Mirroring
4. VLF
#1,2,3 can be check by this script
Declare @DBName varchar(255) = ‘test’
select log_reuse_wait_desc
from sys.databases where name= @DBName
So, most case, it’s ok.
But, only #4 can’t be checked by above script.
Please use this file to be able to check “StartOffset” for second VLF since LDF required minimum 2 VLFs.
DBCC LOGINFO
If it’s too big, need to refresh VLF.
But, unfortunately, refresh VLF isn’t simple.
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
http://technet.microsoft.com/en-us/library/aa933049%28v=sql.80%29.aspx
http://www.brentozar.com/blitz/high-virtual-log-file-vlf-count/
http://sqlstudies.com/2013/08/26/the-effect-of-vlf-size-on-shrinking-the-log/
Ref. Log_reuse_wait (http://msdn.microsoft.com/en-us/library/ms178534.aspx)
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
5 = Database mirroring
6 = Replication
7 = Database snapshot creation
8 = Log Scan
9 = An AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database.
10 = For internal use only
11 = For internal use only
12 = For internal use only
13 = Oldest page
14 = Other (transient)
Posted in Common | Tagged: dbcc shrink, Log_reuse_wait, transaction log, VLF | Leave a Comment »
SQL Agent Job server.(Centralized job server)
Posted by Simon Cho on 07/03/2014
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=315
http://msdn.microsoft.com/en-us/library/ms180992(v=sql.120).aspx
Automated Administration Across an Enterprise
Automating administration across multiple instances of SQL Server is called multiserver administration. Use multiserver administration to do the following:
- Manage two or more servers.
- Schedule information flows between enterprise servers for data warehousing.
To take advantage of multiserver administration, you must have at least one master server and at least one target server. A master server distributes jobs to, and receives events from, target servers. A master server also stores the central copy of job definitions for jobs that are run on target servers. Target servers connect periodically to the master server to update their schedule of jobs. If a new job exists on the master server, the target server downloads the job. After the target server completes the job, it reconnects to the master server and reports the status of the job.
The following illustration shows the relationship between master and target servers:
If you administer departmental servers across a large corporation, you can define the following:
- One backup job with job steps.
- Operators to notify in case of backup failure.
- An execution schedule for the backup job.
Posted in Common | Tagged: centralized job server, JOB, Job server, Master job server, master server | Leave a Comment »
Drop certain query plan
Posted by Simon Cho on 07/02/2014
DECLARE @ObjName varchar(255)
SET @ObjName = ‘abc’
SELECT c.usecounts
, c.cacheobjtype
, c.objtype
, c.size_in_bytes
, t.text
, p.query_plan
, DB_NAME(p.dbid) AS DBNAME
, p.encrypted
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
CROSS APPLY sys.dm_exec_query_plan (plan_handle) p
WHERE usecounts > 1 and t.text like ‘%’+@ObjName+’%’
AND t.text not like ‘%sys.dm_exec_cached_plans%’
GO–DBCC FREEPROCCACHE (plan_handle);
–ex) DBCC FREEPROCCACHE (0x05000C00F01C44754021DDDA040000000000000000000000);
GO
Posted in Common | Tagged: drop query plan, FREEPROCCACHE, query plan | Leave a Comment »
Simon's SQL 