Sql Server cache

To clean cache:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

To view cache list (SQL 2005):

SELECT objtype, p.size_in_bytes, t.[text], usecounts
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) t
WHERE objtype IN ('Prepared', 'Adhoc')
ORDER BY usecounts DESC

Sql Server 913 error: Could not find database ID

Strange behaviour of sql server I found.
When databaseid is changed in the sysdatabases, but name of database is same I get 913 error on some queries:
Could not find database ID %d. Database may not be activated yet or may be in transition.
It is totally thrue, my database have another id after series of drop/create, but somehow .NET SqlConnection remember it. There is no matter, if I am closing and reopen connection. I even did SqlConnection.ClearAllPools()
Even creating new connection with same connection string doesn't help. And it is not bug referencing by MSDN if I am using user-defined function. Nope, I don't have UDR in query.. But error appears in some specific queries, like Create mydb.dbo.mytable, or update, or insert..
One note.. I always use connection string with default database master
After drop/create database, I changed connection string and set default database name to the just created.

Bingo!!! It works!
But why?? Why connection of master, even with clearing pools, remember obsolete information from sysdatabases, if I even recreate new connection on it?
Didn't found any reasonable answer.

list of table indexes

I was need to drop all indexes for specific table excluding primary key indexes.
Following example demonstrates how to get list of indexes.

USE mydb;

SELECT indexes.name, * FROM sysindexes indexes
INNER JOIN sysobjects objects ON indexes.id = objects.id
WHERE indexes.indid>0 AND objects.name='mytable'
and indexes.name not in
(select constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = 'mytable' and constraint_type = 'primary key')

if table doesn't have clustered index, then result set of sysindexes will contain heap with the field indid=0.. Because it is not real index, and I cannot drop it, I am filtering it out by the indid>0

Lately I found that table may contain statistic indexes, which cannot be dropped as well directly with DROP INDEX, but through "drop statistics" statement

To filter them out I added following part to WHERE:
and INDEXPROPERTY(indexes.id, indexes.name, 'IsStatistics')=0
and INDEXPROPERTY(indexes.id, indexes.name, 'IsHypothetical') = 0
and INDEXPROPERTY(indexes.id, indexes.name, 'IsAutoStatistics') = 0

Foreign keys for the referenced table

Let's say, I have a database table "SingleTable" and I want to get list of all tables which are referencing to this SingleTable and names of foreign keys...

(I need this, for example, if I am going to drop SingleTable but I need drop all dependencies from this table first )

I have option to use undocumented stored procedure sp_MStablerefs, however I am trying to avoid using undocumented feature. Never know when and where Microsoft will put stick in my wheels :)
So, there is alternative way I found by experiments:

USE mydatabase
SELECT name as fkname, OBJECT_NAME(parent_obj) as fkTableName
FROM sysobjects
INNER JOIN sysreferences
ON sysobjects.id=sysreferences.constid
WHERE name like 'FK%' AND xtype='F' -- foreign key only
AND rkeyid=object_id(N'SingleTable')

Result is set of forein key names and table names referencing to my table..
Bingo. Now we can drop them one by one using cursor, or DataSet on the upper code level..

depending on loading Sql Server 2000

My C# Service depends on MS SQL 2000 service and my service is starting to use database immediatly during loading. Everything ok, service is starting and stopping untill... restart.
On some computers, my service is not run automatically after computer restart because database not ready yet.
(I got error: Could not find database ID 2. Database may not be activated yet or may be in transition)

After investigation of the sql server I understand that server starting listening clients before, than starts up my database... How it can be??? Ok, there are milliseconds difference, but my service already tried to access db, and exited because it cannot. On other computers, where service is starting, I see in the log that my database was started before than server starts listening clients.

My database is not marked "Auto Close". Sql Server configuration is exactly same on computers, SP4 installed. So I don't have idea, why some sql servers change their loading priority...

Well, I found some things that may fix this.
1.
I unchecked autostart MSDTC service. Anyway this service is loading when DTC transaction initiated by somebody. And it helps


Interesting, service started, but it is not solution, I don't know, what settings user will have.. I And I suspect, it is just loading time issue.. Because log still shows starting database only after starting listening, not before.

2. I found at microsoft if I will use trace flag 3614, the startup sequence of SQL Server 2000 will be slightly different. And they rules the world with such phrases like "slightly different"?
I checked checkbox "Autostart MSDTC" back and added parameter -T3614. Ooops, service started... Let's see sql server log.Yes! My database is loaded before than service started to listen clients.



Nice. Solution may be used for workaround in the case of problem. But for my serenity, I added to OnStart method of my service check for accessibility of my database, and in the negative case sleep service for some time before continue..
Now it works.

However, I don't have real solution or understanding, why it is happens when Sql Server put user databases to load after starting to accept requests.. Question is opened for while.
It looks like this article have similar situation, but it is not enough to understand.

Drop default constraint

Somebody created table like this:

CREATE TABLE [dbo].[MyTable] (
...
[MyField] [bit] NOT NULL DEFAULT (1),
...
) ON [PRIMARY]

I need to do some changes, but first I should drop constraints and after changes, create again. Oops, I don't know constrain name, because somebody used bad style in creation tables.
I need to execute SQL query like following
ALTER TABLE [dbo].[TreeNodeRelation] DROP CONSTRAINT [DF__MyTable__MyField__2B3F6F97] - but we cannot be sure that name will be same on all installations because it is autogenerated during "create table".

Solution. Following example receives name of default constraint for the specific field, and if it is exists, drop it.

declare @cnstname sysname declare @sql nvarchar(4000) select @cnstname = [name] from sysobjects as so where xtype = 'D' and parent_obj = object_id('dbo.MyTable') and col_name(parent_obj, info) = 'MyField' if @cnstname is not null begin set @sql = N'alter table dbo.MyTable drop constraint ' + @cnstname execute sp_executesql @sql end