Troubleshooting Error: 18452 Login failed for user

Error: 18452 Login failed for user ‘null‘, … •          “Null” or ‘’ means that client windows token is not trusted •          Kerberos authentication is not available, fallback to NTLM (not in a domain, SPN not registered) •          Solution: Create the same account, password on the SQL Server •          Delegation may be required (at least one…

3

Do we need to run UPDATE STATISTICS WITH FULLSCAN

  Auto-Update Statistics   For a large majority of SQL Server installations, the most important best practice is to use auto create and auto update statistics database-wide. Auto create and auto update statistics are on by default. If you observe bad plans and suspect that missing or out of date statistics are at fault, verify…

2

Troubleshooting Transactional Replication Distribution Agent Latency

Here is a tip I found while troubleshooting a Distribution Agent latency problem.  We were trying to see why on some days the Distribution Agent was “getting behind”.  We suspected it was volume related.  Below are a few queries to help uncover workload in the Distribution database. The query below returns count of transactions.  It’s…

0

Query to return list of Dynamic Management Views

I don’t always remember names of the SQL Server Dynamic Managment views.  Books Online is great, but here is a simple query to get a list of DMVs   SELECT * FROM sys.all_objects    WHERE [name] LIKE ‘%dm_%’                 AND [type] IN (‘V’, ‘TF’, ‘IF’)                 AND [schema_id] = 4 ORDER BY [name]  

1

Performance Dashboard error "Difference of two datetime values caused an overflow at runtime"

  Have you tried using SQL Server Performance Dashboard?  We’re using this tool on SQL Server Escalation team with customers to troubleshoot real-time performance problems.  It is a great way to quickly find queries with high-CPU, high-reads, waits, etc.   SQL Server 2005 Performance Dashboard Reports http://www.microsoft.com/downloads/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc&DisplayLang=en   We discovered a problem with one of the stored procedure when…

2

I/O requests taking longer than 15 seconds to complete on file

What does the “I/O request” error below represent?   2008-04-21 13:26:42.480 spid364      Microsoft SQL Server 2005 – 9.00.3177.00 (Intel X86) 2008-04-22 16:30:02.140 spid6s       SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\sql data files\xxx.MDF] in database [xxx] (5).  2008-04-22 16:32:08.780 spid6s       SQL Server has encountered…

2

Script to purge TokenAndPermUserStore

  Here is a script that purge the ‘TokenAndPermUserStore’ every time it reaches 100MB.     DECLARE @CacheSize int DECLARE @CurDate varchar(30)   WHILE (1=1) BEGIN SET @CurDate = CONVERT (varchar, GETDATE(), 126) SELECT @CacheSize = SUM(single_pages_kb + multi_pages_kb) FROM sys.dm_os_memory_clerks WHERE name = ‘TokenAndPermUserStore’   — Greater than 100MB IF @CacheSize >= 100000 BEGIN…

0