I recently came across a Sql Server performance problem. It was caused by TokenAndPermUserStore taking too much of cache. Server specifications: Windows 2003 R2 SP2 Enterprise b4-bit, Sql Server 2005 Enterprise 64-bit SP2, 2 Quad-Core Dell server with 32 gig of memory Symptoms that I have experienced: 1. Over time, the application got timeout messages.…

In Sql Server 2000, there is an option to script out all jobs in an instance. That option disappeared in Sql Server 2005. You can still do it. In Object Explorer, click on Jobs folder. Then go to the Object Explorer Details window. All jobs in this instance will be listed there. Select all, and…

When you use DMO to connect to a Sql Server 2005 instance, you will likely get the following error: Microsoft SQL-DMO (ODBC SQLState: 42000) error ‘800ad903’ [Microsoft][ODBC SQL Server Driver][SQL Server]To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO). This is because in Sql Server 2005,…

In Activity Monitor (Sql Server 2005) or Current Activities (Sql Server 2000), if you suspect a job has been running unusually long, all you see is something like this: SQLAgent – TSQL JobStep (Job 0x1D8274F1H00B7D44912BD78DCADB1AD0 : Step 1) Use this statement to find out the job name: select name from msdb.dbo.sysjobs where job_id = cast(0x1D8274F1H00B7D44912BD78DCADB1AD0…

Say you are migrating databases from one server to another. You don’t want users connect to the old instance by mistake. You can do that by putting them all offline. Below is the script t set nocount on select ‘alter database ‘ + name + ‘ set offline’ from sysdatabases where name not in (‘master’,…