SELECT scheduler_id, cpu_id, current_tasks_count, runnable_tasks_count, current_workers_count, active_workers_count, work_queue_count, pending_disk_io_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255;
Source: blog.sqlauthority.com
Determine CPU Pressure
Nov 8
Query Agent Job History
Oct 11
To pull a list of the SQL Server Agent job history, use the following query:
USE msdb
SELECT
j.name,
endTime = DATEADD
(
SECOND,
jh.run_duration,
CAST
(
CONVERT(VARCHAR, jh.run_date)
+ ' ' + STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')
AS DATETIME
)
)
FROM
msdb..sysjobhistory jh
INNER JOIN
msdb..sysjobs j
ON
j.job_id = jh.job_id
WHERE
jh.step_name = '(Job outcome)'
Source: aspfaq.com
Windows Max TCP Ports
Sep 17
The default range for dynamic ports is 1024-5000. The following registry setting can adjust the range.
Hive: HKEY_LOCAL_MACHINE Key: SYSTEM\CurrentControlSet\Services\Tcpip\Parameters Name: MaxUserPort Type: REG_DWORD Value: 65534valid range 5000-65534
Source: WindowsNetworking.com
Offline Full Text Catalogs
Sep 11
Sometimes backup jobs will fail due to a full text catalog being offline. To retrieve a list of the full text catalogs that are in offline status, run this query:
DECLARE @command varchar(1000) SELECT @command = 'USE ?; SELECT "?" AS DatabaseName, Name AS IndexName, State_Desc, Physical_Name FROM sys.database_files where Type_Desc="FULLTEXT" and State_Desc="Offline"' EXEC sp_MSforeachdb @command
Command line options -------------------- -exclude : Exclude items from scanning (see section entitled "Operation") -idedir= : Specify an alternative directory for virus identity (IDE) files -include : Include items in scanning (see section entitled "Operation") --stop-scan : Abort scanning of "zip bombs" The following options may be prefixed with 'n' to invert their meaning (for example, '-nsc' is the inverse of '-sc'). [*] indicates the option is the default: -sc [*] : Scan inside dynamically compressed executables -f [ ] : Full scan -di [ ] : Disinfect infected items -s [*] : Run silently (do not list files swept) -c [*] : Ask for confirmation before disinfection/deletion -b [*] : Sound bell on virus detection -all [ ] : Scan all files -rec [*] : Do recursive scan -remove [ ] : Remove infected objects -dn [ ] : Display names of files as they are scanned -ss [ ] : Don't display anything except on error or virus -eec [ ] : Use extended error codes -ext=XXX, .. : Specify additional extensions to scan -v : Display complete version information -vv : Display complete version and IDE information -h : Display this help and exit -p= : Write to log file -mbr [ ] : Scan master boot records on all hard disks -bs=X,. [ ] : Scan boot sector of each drive listed -mac [ ] : Scan for Macintosh viruses -cdr=X, .[ ] : Scan boot sector in bootable image of each CD drive listed The following options are related to archives and other special file types: -zip [ ] : Scan inside ZIP archives -gzip [ ] : Scan inside GZIP compressed files -arj [ ] : Scan inside ARJ archives -cmz [ ] : Scan inside Unix-compressed files -tar [ ] : Scan inside TAR archives -rar [ ] : Scan inside RAR archives -cab [ ] : Scan inside Microsoft Cabinet files -archive [ ] : All of the above (see below for a full list) -loopback [ ] : Scan inside loopback-type files -mime [ ] : Scan files encoded in MIME format -oe [ ] : Scan Microsoft Outlook Express mailbox files (you must also use the -mime option with this option) -tnef [ ] : Scan inside TNEF files Archive types ------------- The following archive types are supported: Archive name Command line option Extension(s) Arj -arj ARJ Cmz -cmz Z, TAZ Gzip -gzip GZ, TGZ Rar -rar RAR Tar -tar TAR Zip -zip ZIP Lha -lha LHA, LZH MSCompress -mscmp ??_ SfxArchives -sfx EXE MacBinary -mbin BIN BinHex -bhex HQX Uue -uue UUE BZip2 -bzip2 BZ2, TBZ, TBZ2 Compressed help -itss CHM, HXS RPM -rpm RPM Unix archive -uar A Microsoft Cabinet -cab CAB, XSN
Source: laststopcomputerservice.com
Spool File Types
Jul 2
Beginning Character in File Name
| File Name | Description |
|---|---|
| A*.??? | A data file undergoing connection filtering and SPF testing; deleted when message is delivered. |
| D*.??? | A data file in process; deleted when message is delivered. |
| T*.??? | A file that matches the data file while the message is inbound; when the message is fully received, the T file is renamed with a Q. |
| Q*.??? | A file that matches the data file while the IMail server attempts to deliver the message. |
| _*.~?? | A locked file that is being processed; these files also have a tilde (~) in the file extension. (If three characters of the file name are nex, the file is being processed via Web Messaging or imail1.exe.) |
| F*.txt | Mail to Fax file. |
- .SMD, .SMP, and .RMD file extensions indicate regular mail messages being processed by SMTP.
- .FWD and .FWP file extensions indicate forwarded messages.
- .LST file extensions indicate messages to subscribers of a list server mailing list.
- .TMP are Web Messaging, or Mail to Beeper/Pager, or Mail to Fax files.
- .GSE and .GSP file extensions indicate error messages being returned to the senders. These are usually generated by the server (postmaster).
- .GSC file extensions are Web Messaging messages.
- .VAC file extensions are vacation messages and info manager responses.
Source: IMail Knowledge Base
Note: ***This will probably result in data loss. Use only if there is no valid backup***
- Verify suspect status:
SELECT DATABASEPROPERTYEX ('YourSuspectDatabase', 'STATUS') AS 'Status'; GO - Set to Emergency mode
ALTER DATABASE DemoSuspect SET EMERGENCY; GO
- Set to Single-user mode
ALTER DATABASE DemoSuspect SET SINGLE_USER; GO
- Run CHECKDB and allow data loss on the recovery
DBCC CHECKDB (DemoSuspect, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS; GO
Source: Vincent Yang, cnblogs.com
| .NET Framework product version | Service pack level | Version |
|---|---|---|
| .NET Framework 1.0 | Original release | 1.0.3705.0 and 7.0.9466.0 |
| .NET Framework 1.0 | Service pack 1 | 1.0.3705.209 |
| .NET Framework 1.0 | Service pack 2 | 1.0.3705.288 and 7.0.9502.0 |
| .NET Framework 1.0 | Service pack 3 | 1.0.3705.6018 and 7.0.9951.0 |
| .NET Framework 1.1 | Original release | 1.1.4322.573 and 7.10.3052.4 |
| .NET Framework 1.1 | Service pack 1 | 1.1.4322.2032 (if you have the MSI-based 1.1 SP1 installed) or 1.1.4322.2300 (if you have the OCM-based 1.1 SP1 installed on Windows Server 2003) and 7.10.6001.4 |
| .NET Framework 2.0 | Beta 1 | 2.0.40607.16 and 8.0.40607.16 |
| .NET Framework 2.0 | Beta 2 | 2.0.50215.44 and 8.0.50215.44 |
| .NET Framework 2.0 | Original release | 2.0.50727.42 and 8.0.50727.42 |
| .NET Framework 2.0 | Service pack 1 | 2.0.50727.1433 and 8.0.50727.1433 |
| .NET Framework 2.0 | Service pack 2 | 2.0.50727.3053 and 8.0.50727.3053 |
| .NET Framework 3.0 | Original release | 3.0.04506.26 (on Windows Vista) and 3.0.04506.30 (on downlevel operating systems) |
| .NET Framework 3.0 | Service pack 1 | 3.0.04506.648 |
| .NET Framework 3.0 | Service pack 2 | 3.0.04506.2152 |
| .NET Framework 3.5 | Original release | 3.5.21022.8 and 9.0.21022.8 |
| .NET Framework 3.5 | Service pack 1 | 3.5.30729.1 and 9.0.30729.1 |
Source: Aaron Stebner’s MSDN WebLog
How to determine which version of SQL Server 2008 is running
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
The following table lists the Sqlservr.exe version number.
| Release | Sqlservr.exe |
|---|---|
| RTM | 2007.100.1600.0 |
| SQL Server 2008 Service Pack 1 | 2007.100.2531.0 |
How to determine which version of SQL Server 2005 is running
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
The following table lists the Sqlservr.exe version number.
| Release | Sqlservr.exe |
|---|---|
| RTM | 2005.90.1399 |
| SQL Server 2005 Service Pack 1 | 2005.90.2047 |
| SQL Server 2005 Service Pack 2 | 2005.90.3042 |
| SQL Server 2005 Service Pack 3 | 2005.90.4035 |
How to determine which version of SQL Server 2000 is running
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
The following table lists the Sqlservr.exe version number.
| Release | Sqlservr.exe |
|---|---|
| RTM | 2000.80.194.0 |
| SQL Server 2000 SP1 | 2000.80.384.0 |
| SQL Server 2000 SP2 | 2000.80.534.0 |
| SQL Server 2000 SP3 | 2000.80.760.0 |
| SQL Server 2000 SP3a | 2000.80.760.0 |
| SQL Server 2000 SP4 | 2000.8.00.2039 |
How to determine which version of SQL Server 7.0 is running
SELECT @@VERSION
Use the version number in the following table to identify the product or service pack level.
| Version Number | Service Pack |
|---|---|
| 7.00.1063 | SQL Server 7.0 Service Pack 4 (SP4) |
| 7.00.961 | SQL Server 7.0 Service Pack 3 (SP3) |
| 7.00.842 | SQL Server 7.0 Service Pack 2 (SP2) |
| 7.00.699 | SQL Server 7.0 Service Pack 1 (SP1) |
| 7.00.623 | SQL Server 7.0 RTM (Release To Manufacturing) |
Source: KB321185
Fix Orphaned Users
Jul 2
List the orphaned users:
EXEC sp_change_users_login 'Report'
Autofix:
EXEC sp_change_users_login 'Auto_Fix', 'user'
Create new login and password for database user:
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'
Source: FileFormat.info