VMware KB: Troubleshooting transaction logs on a Microsoft SQL database server

VMware KB: Troubleshooting transaction logs on a Microsoft SQL database server: Troubleshooting transaction logs on a Microsoft SQL database server
Prevent logs from filling up the server:

1. Log in to the Microsoft SQL 2005/2008 Server as an administrator.
2. Open up SQL Management Studio.
3. Right-click the database that VirtualCenter is using.
4. Click Properties.
5. Click the Options link.
6. Set the Recovery Model to Simple
7. Click OK.
8. Once this is complete, right click on the database again.
9. Click Tasks>Shrink>Files.
10. On the Shrink Database window select the file type as 'Log' . The file name appears in the filename drop down as databasename_log
11. The space used versus the space allocated displays. After you set the recovery model to Simple, the majority of the space in the transaction log released.
12. Ensure that the Release unused space radio button is selected.
13. Click OK on this window to shrink the transaction log.

Performance::MSSQL

Quote from: Buck WoodyThese objects and counters serve as a demonstration of the types of things I monitor for a SQL Server application I use:

Object	Counter	Meaning	Notes
Cache	Data Maps Hits %	NTFS Processing Efficiency	Should be better than 90% or may need to defrag Hard Drive
Cache	MDL Read Hits %	Cache Hits for IIS	Should be better than 90%
Memory	% Committed Bytes	Shows memory use	Should be under 70
Memory	Available MBytes	How much unallocated RAM is left on the server	Should be greater than 50MB
Memory	Cache Faults/Sec	Physical Disk Read I/O Operation	The fewer the better
Network Interface	Bytes Total/sec	Shows network activity	Network Monitor Driver must be installed for accurate measurement of networkitems - Measure against network bandwidth availability
Network Segment	% Network Utilization	Total Network segment use - not from this server alone	Depends on many networking variables, but can be useful to a Networkspecialist
PhysicalDisk	Current Disk Queue Length:_Total	Shows physical drive activity	Less than 3 per physical disk is acceptable
PhysicalDisk	% Disk Read Time	How much time spent doing reads	Combine with Writes to see if Index usage is correct - may need to adjustfillfactor
PhysicalDisk	% Disk Time -- _Total	Shows drive activity	diskperf -yv is required for proper logical disk counters to operate. Shouldbe less than 55% - watch for increase
PhysicalDisk	% Disk Write Time	How much time spent doing writes	Combine with Reads to see if Index usage is correct - may need to adjustfillfactor
Process	% Processor Time	Pick Specific Object	Will explain how much that object is taking on the processor
Processor	% Total Processor Time	Shows the CPU activity being taken by all processes	Should not exceed 80% for continuous periods with high Proc Queue Length.NOTE: W2K measures non-busy time and subtracts it from 100%
Server	Bytes Received/Sec	Data received by the server NIC
Server	Bytes Transmitted/Sec	Data sent by the server NIC
SQL Server:Access Methods	Full Scans/Sec	Table scans	For the entire server - not just one database
SQL Server:Access Methods	Page Splits/Sec	Splits happen when data or indexes span a page (8k)	Fillfactors may not be correct if this is high
SQL Server:Buffer Manager	Cache Size (pages)		Multiply x 8192 for RAM amount, should be close to the RAM in yoursystem.
SQL Server:Locks	Average Wait Time (ms)	Time processes spend waiting on a lock release	Should not be high
SQL Server:Locks	Number of Deadlocks	Number of processes deadlocking each other	Measurement is by the second
SQL Server:Memory Manager	Target Server Memory	How much RAM SQL wants
SQL Server:Memory Manager	Total Server Memory	How much RAM SQL is using
SQL Server:SQL Statistics	Batch Requests/Sec	Bow many batches sent to SQL Server	Over 1000 indicates a busy SQL Server - May indicate CPU bottleneck. A 100Mbnetwork card can handle 3000 per second.
SQL Server:SQl Statistics	SQL Compilations/Sec	How many compiles SQL has to do	Over 100 may indicate a SQL problem
SQLServer:Buffer Manager	Buffer Cache Hit Ratio	Shows how much data is found in the Buffer	Should be 99% or greater
SQLServer:General Statistics	User Connections	Shows the number of connections taken on the SQL Server	Varies
System	% Total Privileged Time	Kernel mode operations	Should be less than 20% or may be IO bound. Pair with %Disk time counter tosee if greater than 50%. Can also indicate driver or NIC.
System	Context Switches/Sec	Server thread switches	Should not exceed 8000 per second, per processor
System	Processor Queue Length	Shows the amount of processes waiting for CPU time	Pairs with % Processor Time counter - Should not be greater than 2 perproc.
Web Service	GET Requests/Sec	Number of GET requests	Each GET connection attempt
Web Service	POST Requests/Sec	Number of POST requests	Each POST connection attempt
Web Service	Total Method Requests/Sec	Hits on the Web Server	Service Request rate