cleanup the tempdb

Is there currently a way to cleanup the tempdb?
Tried dbcc shrinkfile , but no luck.
Can't restart the server ,because it's on production.
What makes tempDB big ?
and tempDB is in Simple mode , but not truncating it .
ThanksDid you post to enough groups<g>? Tempdb is used for all sorts <pun
intended> of things during the normal operation of the database. What makes
it so big (by the way how big is it?) depends on what your doing but it is
most likely the result of a large join or sort operation. You might want to
check to see if you have a long running open tran as well. If it got that
big once it is most likely going to get that big again (unless it was a
mistake) so you shouldn't bother to shrink it until you know for sure. Next
time you see a lot of activity in it you can investigate to see what is
happening at the time with profiler, sp_who etc.
--
Andrew J. Kelly
SQL Server MVP
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:eADnDxBQDHA.1556@.TK2MSFTNGP10.phx.gbl...
> Is there currently a way to cleanup the tempdb?
> Tried dbcc shrinkfile , but no luck.
> Can't restart the server ,because it's on production.
> What makes tempDB big ?
> and tempDB is in Simple mode , but not truncating it .
> Thanks
>

Clean up replication data

I have a develop machine for our web site. On this machine I have a copy of a database from the production site. The database on the production site is being replicated. When I copied the database to the develop machine and started testing some new developments I got the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sysmergearticles'

This tells me that some replication data is also copied to the develop database.

Can anyone tell me how I can clean this data, or if I should copy the databse in a different way. (Now I create a new databse and do an import of the tables and stored procedures)You could try to remove replication by going under Tools(in EM)-->Replication-->Disable publishing,Distri...If it allows you to do so then it's fine.Or else you could try to first set the replication on your Test system and then remove ot by using the Tools-->Replication.
Or you may try the script below:

USE [DBNAME]
GO

-- Drop all replication triggers from the database
PRINT 'Drop all replication triggers from the database'
SELECT trigs.name AS TriggerName,
trigs.id AS TriggerID,
tables.name AS TableName
INTO #Triggers
FROM sysobjects trigs
INNER JOIN sysobjects tables
ON trigs.parent_obj=tables.id
where trigs.category=2 and trigs.xtype='TR'

DECLARE @.TriggerName varchar(100), @.TriggerID INT, @.TableName varchar(100)

DECLARE cur CURSOR for SELECT * FROM #Triggers
OPEN cur
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP TRIGGER ' + @.TriggerName)
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
END
CLOSE cur
DEALLOCATE cur
GO

-- Drop all replication constraints from the database
PRINT 'Drop all replication constraints from the database'
DECLARE @.ConstName varchar(100), @.ConstID INT, @.TableName varchar(100)

SELECT CONST.name AS ConstName,
CONST.id AS ConstID,
tables.name AS TableName
INTO #Constraints
FROM sysobjects CONST
INNER JOIN sysobjects tables
ON CONST.parent_obj=tables.id
where CONST.xtype='C'

DECLARE cur CURSOR for SELECT * FROM #Constraints
OPEN cur
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER Table ' + @.TableName + ' DROP CONSTRAINT ' + @.ConstName)
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication User tables
PRINT 'Drop all replication User tables'
DECLARE @.TableName varchar(100), @.TableID INT

SELECT Tables.name AS ConstName,
Tables.id AS ConstID
INTO #Tables
FROM sysobjects Tables
where Tables.xtype='U' AND Status < 0 AND category=2050

DECLARE cur CURSOR for SELECT * FROM #Tables
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.TableID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Table ' + @.TableName)
FETCH NEXT FROM cur INTO @.TableName, @.TableID
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication User procedures
PRINT 'Drop all replication User procedures'
DECLARE @.ProcName varchar(100), @.ProcID INT

SELECT Procs.name AS ConstName,
Procs.id AS ConstID
INTO #Procedures
FROM sysobjects Procs
where procs.xtype='P' AND Status < 0

DECLARE cur CURSOR for SELECT * FROM #Procedures
OPEN cur
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Procedure ' + @.ProcName)
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication User Views
PRINT 'Drop all replication User Views'
DECLARE @.ViewName varchar(100), @.ViewID INT

SELECT MyViews.name AS ConstName,
MyViews.id AS ConstID
INTO #Views
FROM sysobjects MyViews
where MyViews.xtype='V' AND Status < 0 AND Name NOT LIKE 'sys%'

DECLARE cur CURSOR for SELECT * FROM #Views
OPEN cur
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP View ' + @.ViewName)
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication rowguids, defaults and indexes
PRINT 'Drop all replication rowguids, defaults and indexes'
SELECT tables.name as TableName,
defaults.name AS DefaultName,
Indexes.IndexName,
cols.name AS ColumnName
INTO #Defaults
FROM sysobjects defaults
INNER JOIN syscolumns cols
ON defaults.ID=cols.cdefault
INNER JOIN sysobjects tables
ON tables.id=cols.id
INNER JOIN
(select sysindexes.name AS IndexName,
Tables.Name AS TableName,
Tables.id as TableID,
cols.Name AS ColumnName
from sysindexes
INNER JOIN sysobjects Tables
ON sysindexes.id=tables.id
INNER JOIN sysindexkeys k
on sysindexes.id=k.id
AND sysindexes.indid=k.indid
INNER JOIN syscolumns cols
ON k.id=cols.id
AND k.colid=cols.colid
where cols.name='rowguid') Indexes
ON Indexes.TableID=tables.id
where cols.name='rowguid'

DECLARE @.DefaultName varchar(100), @.IndexName varchar(100), @.TableName varchar(100), @.ColName varchar(50)

DECLARE cur CURSOR for SELECT * FROM #Defaults
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.DefaultName)
EXECUTE ('DROP INDEX ' + @.TableName + '.' + @.IndexName)
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP COLUMN rowguid')
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
END
CLOSE cur
DEALLOCATE cur
GO|||I've been trying to delete the conflict tables left over after EXEC sp_removereplication

Your script did it!

Checklist to TroubleShoot SQl Server 2005 Connection Problem

I am trying to connect a production SQL Server 2005 which is under firewall through an Application written in ASP.Net 1.1 and I get error "SQL Server not available or Access Denied".

I am able to connect to the Server through SQL Mgr also using the same Code and Web.Config file I am able to Connect to the SErver using ASP.Net 2.0.

I am able to run the application for a SQL Server 05 which is in the same domain as of the Application Server.

My Application is not working only when I use ASP.Net 1.1 code for the Remote SQL Server 05 which is under Firewall. But for the same Server ASP.Net 2.0 works fine.

I have searched a lot for the problem and port No, DBNETLIB update is also tried.

Please let me know if anyone has came across such problem or knows the possible cause of the problem.

Sunil.

Can you post your connection string for both ASP.NET 2.0 and ASP.NET 1.1 app?|||

Sure, I am using the same connection String for both versions.

In Web.config I have added the following Connection String :

<add key="CnString" value="data source=<IP of Server>;User Id=sa;password=sa;Initial Catalog =<Database>"/>

Code from aspx file :

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["CnString"]);

conn.Open();

Response.Write("Connection Opened");

conn.Close();

When the same code is written in ASP.Net 2.0 it gets executed but through Asp.Net 1.1 its throwing exception as "Sql Server does not exist or Access Denied"

|||

Hi Sunil,

Is the ASP.Net 2.0 and ASP.Net 1.1 both run on the same machine? Is there more error message text available? Also, the obvious question is are you using the same IP address in both applications?

Another thing you can try is to force the app to TCP and see what happens. You can do this by prefixing the server name with "tcp:". FYI, .Net 2.0 will always try TCP first for remote connections while this isn't always the case for 1.1.

Il-Sung.

|||

Hi, Thanks for the "tcp" suggestion. I will check whether it works or not.

I have two applications both having the same code which I have posted above. One is developed in VS2003 and other in VS2005.

Both these applications are using same connection string and talking to same SQL 2005 server.

Also earlier we were using SQL 2000 server which was also under Firewall but it use to work with the above mentioned Connection String very well.

About error message dont have details but error Source is System.Data.SqlClient.

I will try the "tcp" option and will get back soon.

Thanks.

Sunil.

|||

Hello,

Our problem is solved. It happened because the user who shifted Databse from Sql 2000 to Sql 2005 was not DBA so direct procedure names were not working for the Database in 2k5. We gave DBO rights to the user and problem was solved.

Thanks to all of you for your time and suggestions.

Sunil.

|||

Following is the checklist we have prepared to be considered if anyone came across Connection problem with Sql Server 2005.

1) Is the Database is properly shifted from SQL 2000 to SQL 2005.

While shifting the Database from SQL 2000 to SQL 2005, strictly Administrator login tote used i.e. using "sa" login only. If this is not done then the schema

definition of the underlying Tables and Procedure changes from [dbo].Tablename to [LoggedInUserName].TableName and which will give error while using the

Database Objects.

2) Get confirmed from Client whether SQL Server is in same Domain as of Application Server or Not

If Application Domain and Sql Server Domain are different in that case there are Few things to be checked/enabled on SQL Server

Check whether Server is working on Windows Authentication / Sql Authentication

Is remote access allowed for Sql Server

Is the default ports 1433/1434 are opened

Is protocols TCP/IP and Named Pipes Enabled under Sql Configuration --> Protocols

Is Sql Browser service is running

Is proper permissions are granted for the Database to be used, for the user used in connection string ( if Sql Authentication is used )

If Windows Authentication is used the ASPNETUSER must be added to Logins in SQL Server Logins and must be given proper permissions to access the Database.

Checking to see if values are in a table or not -- if not then inserting the values.

I'm trying to checking my production table table_a against a working table table_b (which i'm downlading data to)
Here are the collumns i have in table_a and table_b
Description | FundID (this is not my PK) | Money
I'm running an update if there is already vaule in the money collumn. I check to see if table_a matches table_b...if not i update table a with table b's value where FundID match up.
What i'm having trouble on is if there is no record in table_a but there is a record in table_b. How can I insert that record into table_a? I would like to do all of this (the update and insert statement in one stored proc. if possible. )
If anyone has this answer please let me know.
Thanks,
RB

You'll have to check this before you use it, but this should give you all rows in b that are not in a:
select b.FundID, a.FundID
from b
left join a on a.FundID = b.FundID
where a.FundID is null
Then if this is right, you can write a simple insert statement before it, like this:
INSERT INTO a
select b.Description, b.FundID, b.Money
from b
left join a on a.FundID = b.FundID
where a.FundID is null

|||The select statement looks good however I'm trying to insert into table b so wouldn't it be
INSERT INTO b
...
Just clarify for me if you could.
Thanks for the help
RB|||Oh, if you are inserting INTO b, then you'll just have to reverseeverything I wrote. I thought you were inserting into A what wasin B but not in A.

Checking for replication when applying schema changes

All
Is there some simple way to check if a database is being replicated that I
can use in a script? I have 2 copies of a production database, one under
replication and one not, and I would like to have any schema change scripts
check for replication to see which action to take. E.g.
<script>
If <database is replicating>
sp_addreplcolumn ...
Else
Alter Table...
</script>
TIA
Ron Lounsbury
sp_dboption 'pubs','published'
GO
sp_dboption 'pubs','merge publish'
GO
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:uY5tItmUGHA.4764@.TK2MSFTNGP11.phx.gbl...
> All
> Is there some simple way to check if a database is being replicated that I
> can use in a script? I have 2 copies of a production database, one under
> replication and one not, and I would like to have any schema change
> scripts check for replication to see which action to take. E.g.
> <script>
> If <database is replicating>
> sp_addreplcolumn ...
> Else
> Alter Table...
> </script>
> TIA
> Ron Lounsbury
>
|||Hilary
Thanks for the reply. Unfortunately, when I run that against a database
that is participating in replication and one that is not, both give me a
result for the "CurrentSetting" of OFF. This occurs for both "Merge
Publish" and "Publish". Is there something I am missing? I am running SQL
Server 2000, SP 3a (with some hotfixes).
Thanks,
Ron Lounsbury
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:edTAbLnUGHA.4740@.TK2MSFTNGP14.phx.gbl...
> sp_dboption 'pubs','published'
> GO
> sp_dboption 'pubs','merge publish'
> GO
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ron L" <ronl@.bogus.Address.com> wrote in message
> news:uY5tItmUGHA.4764@.TK2MSFTNGP11.phx.gbl...
>
|||If it returns off it means these databases are not enabled for replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:ODmoO2pUGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hilary
> Thanks for the reply. Unfortunately, when I run that against a database
> that is participating in replication and one that is not, both give me a
> result for the "CurrentSetting" of OFF. This occurs for both "Merge
> Publish" and "Publish". Is there something I am missing? I am running
> SQL Server 2000, SP 3a (with some hotfixes).
> Thanks,
> Ron Lounsbury
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:edTAbLnUGHA.4740@.TK2MSFTNGP14.phx.gbl...
>
|||Hilary
When I run this script in Query Analyzer:
USE NITSS2kDev
exec sp_dboption 'pubs','merge publish'
GO
use NITSS2kdeployed
exec sp_dboption 'pubs','merge publish'
GO
I get these results:
OptionName CurrentSetting
-- --
merge publish off
OptionName CurrentSetting
-- --
merge publish off
NITSS2kDev is actively replicating with 2 other databases, and one of the
replication jobs ran 3 minutes ago. NITSS2kDeployed is not replicating.
The script was run on the Publishing server.
Thanks,
Ron L
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eKM%23DAqUGHA.1444@.TK2MSFTNGP11.phx.gbl...
> If it returns off it means these databases are not enabled for
> replication.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ron L" <ronl@.bogus.Address.com> wrote in message
> news:ODmoO2pUGHA.4452@.TK2MSFTNGP12.phx.gbl...
>
|||Oh Man, My Stupid. I don't know where my brain was yesterday. When I put
the right database name in the call it works fine.
Thanks,
Ron L
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:OtXBmGqUGHA.5148@.TK2MSFTNGP12.phx.gbl...
> Hilary
> When I run this script in Query Analyzer:
> USE NITSS2kDev
> exec sp_dboption 'pubs','merge publish'
> GO
> use NITSS2kdeployed
> exec sp_dboption 'pubs','merge publish'
> GO
> I get these results:
> OptionName CurrentSetting
> -- --
> merge publish off
> OptionName CurrentSetting
> -- --
> merge publish off
> NITSS2kDev is actively replicating with 2 other databases, and one of the
> replication jobs ran 3 minutes ago. NITSS2kDeployed is not replicating.
> The script was run on the Publishing server.
> Thanks,
> Ron L
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eKM%23DAqUGHA.1444@.TK2MSFTNGP11.phx.gbl...
>

checkboxlist and SQL search using AND/OR on selected checkboxlist items.

I have a checkbox list like the one above. For example, Training OR Production – should include everyone with an Training OR everyone with a Production checked OR everyone with both Training and Production checked. If service AND technical support – just those two options will show – the customer can only have those 2 options selected in their account and nothing else. Is there an easy way to build the SQL query for this scenario? Any suggestions or tips?Thank you for any help

You can create a procedure that accepts a parameter ('AND' or 'OR), which perform a query based on the parameter:

create proc sp_testQuery @.logicOP varchar(3)='AND',@.id int,@.name varchar(20)
as
if @.logicOP='AND'
select * from t1 whereid=@.id and name=isnull(@.name,name)
else if @.logicOP='OR'
select * from t1 whereid=@.id orname=@.name
else raiserror('You must choose a logic operator ''AND'' or ''OR''',16,1)
go
sp_testQuery 'AND',1,null

Then what your code need to do is just call the stored procedure with providing all required parameters that come from your website.