SQLServerCentral / SQL Server 2005 / Administering

Connections

Nita Reddy — Fri, 16 May 2008 06:28:18 GMT

I am getting this error 'cannot generate SSPI context'When I connect through Remote desktop to this server I can successfully able to log in to SQL server, when I log in from my desktop I am getting this error 'cannot generate SSPI context'.I did connect other server from my desktop succefully and they all are in same domain. I don't know why I am geeting error for this server only.Please help

How to prevent a user from restart the server - sql server 2005

fabiano_pereira — Fri, 16 May 2008 15:30:51 GMT

I created a simple sql server user with access to a little database for permission testing purposes. I just granted access to "mydatabase" by assigning datareader and datawrite rights to it. It belongs to dbo schema and public server role. Now i need to prevent/deny it from restart the server.Any idea?

Getting error when updating stats of a system table

Marios Philippopoulos — Fri, 16 May 2008 07:43:49 GMT

When querying [b]sys.sysindexes[/b] of my user database, I see a bunch of system tables. One of them is [b]sysrowsetcolumns[/b].I run the following to update its statistics:[quote]USE dbName;UPDATE STATISTICS sysrowsetcolumns WITH FULLSCAN;[/quote]I get this error:[quote]Msg 2706, Level 16, State 6, Line 2Table 'sysrowsetcolumns' does not exist.[/quote]Anyone know why this is? I guess I should not be updating the stats of system tables?

Separate databases or multiple named instances?

Steven Swenson — Fri, 16 May 2008 12:20:53 GMT

My company is hosting multiple clients on a db server. It will be using windows authentication for access to the db. Clients are not expected to be logging directly on the server but some may have access to management tools. So I am curious what are the pros, cons, and caveats involved in implementing each customer database under a default instance of server vs installing each client on an independent named instance? It seems to me that from a management standpoint a single default server would be easiest... but are there security concerns here that might not be easily dealt with?

adding secondary files

schep021 — Fri, 16 May 2008 16:18:33 GMT

Currently, one of our databases uses only one data file (mdf). I am considering adding to it multiple secondary files (ndfs) in hopes to avoid contention. I would add 7 more files because we have 8 CPU (read from Microsoft it is best practice to have one file for each CPU). I am trying to visualize how data will be distributed once the new files are added. Would the new files be used until they are the same % full as the mdf and then it would start using the mdf again at that time?I don't plan on creating a new filegroup -- they will all be on the same SAN array. (RAID 5)Thank you in advance!

Slow server in general

ReganIT — Fri, 16 May 2008 10:00:49 GMT

Hello,I'm wanting some general guidelines for troubleshooting a slow server. We have an HP DL380 G5 server. Windows Server 2003 x64 Standard, SQL Server Standard 64-bit (9.0.3054). 2 - 2333 Mhz HT processors, 4 gigabytes of RAM. There are 6 - 142 gig internal drives split in half giving us 2 RAID 5 partitions, one for Data and one for Logs. -- Call it server A.We have another very similar server, call it server B. The differences are 3 of the internal drives are 72 gig used for Logs (R5) and the processors are 3000 Mhz.The lock pages in memory privilege has been given to the account running SQL on both servers. No other configurations have been made to either server other than the defaults. That I'm aware of. The OS on each are mirrored on separate drives.In general, queries running on server A are slow. For example when nothing else is running on the server I can run a simple Select count(*) from tbTable with(nolock). A table with 3,869,319 records took 2:23 on server A while a table with 4,116,259 records on server B took 1:08. The query plans for this are pretty straight forward and look the same.The network admins have run some health checks and found one drive reported errors on server A. That has been replaced but we get the same results.Any help would be appreciated. Thank you.

Disk Defrag Tools on SQL Server system

s43s — Fri, 16 May 2008 08:54:02 GMT

I'm not sure I am in the right location so if not I apologize.I am looking for opinions on using disk deframentor (windows standard) or a 3rd party utility to run disk defrag while SQL Server 2000/2005 is running and the databases are in use. Do you know of any pit falls to doing such activity.Thank you

transfer all objects & data from 2k5 to 2k?

escaleraroyal — Fri, 16 May 2008 17:44:24 GMT

I tried everything but no success. Will anyone successfully done this help me out

Moving Model Database

Sugesh Kumar — Fri, 16 May 2008 14:13:05 GMT

Hi,We are trying to move model database from one drive to another. We have done this in past and today when we tried to do this i am getting the following error.[b]"Could not find database ID 3. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred."[/b]The steps we followed were.1. Add the -c,-m,-T3608 in the sql service startup parameter.2. detach the model databaseimmediately after doing the the sql service abruptly stopped and hence after if we try to start the service it fails with the error given above. Any help here to solve the problem will be greatly appreciated.

capturing timeout queries

KATHLEEN Y ZHANG — Wed, 14 May 2008 11:24:46 GMT

Hello!I am trying to run profiler to capture the queries that time out. But how do I specify the events? Using 'SP:completed' ,'SQL:batch completed', 'SQL:stmt completed' wont do it because the timeout queries are not considered 'completed', right?Thanks in advance for any insight!Kathleen

DTSrun/DTExe in sql server 2005

sunshine — Wed, 30 Apr 2008 12:21:29 GMT

I've tried running both in sql server 2005 to run a migrated dts package with sql server agent scheduled job and no luck. Can this be done?

Question about the various cache hit ratios

webrunner — Fri, 16 May 2008 13:49:33 GMT

Hello,I have found at least 10 different performance counters referring to cache hit ratios - each one seems to be accompanied by a "base" counter. I found these conveniently listed at this page[b][url=http://www.grumpyolddba.co.uk/monitoring/sql2005PerfCounters.htm]http://www.grumpyolddba.co.uk/monitoring/sql2005PerfCounters.htm[/url][/b]The URL says grumpyolddba, which I think is the same handle of the person who posts on these forums. If so, hi there, and thanks for the page above!I have read about the Buffer cache hit ratio and know that it should be as close to 99% as possible (and preferably averaging over 90% for an OLTP database server). I have a couple of questions, though.1. What are the other cache hit ratios, what do they mean? And I have noticed sometimes people discussing cache hit ratio (not the buffer cache hit ratio) - which of the following counters are they most likely referring to if not the buffer cache hit ratio? My guess is the Plan Cache hit ratio, but that's just guesswork on my part.2. Also, do all of these cache hit ratios need to average over 90%?SQLServer:Buffer Manager - Buffer cache hit ratioSQLServer:Buffer Manager - Buffer cache hit ratio base SQLServer:Cursor Manager by Type - Cache Hit RatioSQLServer:Cursor Manager by Type - Cache Hit Ratio BaseSQLServer: Databases - Log Cache Hit RatioSQLServer: Databases - Log Cache Hit Ratio BaseSQLServer:Catalog Metadata - Cache Hit RatioSQLServer:Catalog Metadata - Cache Hit Ratio Base SQLServer: Plan Cache - Cache Hit RatioSQLServer: Plan Cache - Cache Hit Ratio Base One funny thing - I had to add a space to a few of the counters above, because otherwise they come out with emoticons, like this:SQLServer:Databases - Log Cache Hit Ratio:)Thanks for any help,webrunner

How to purge sysxmitqueue from msdb

shah_mm — Fri, 16 May 2008 08:31:30 GMT

I have msdb size grown to 5+GB, with the table sysxmitqueue using 4.4+GB. I found that previous dba used service broker feature and it is not used at present. I have dropped the service and queue. The problem is I am not able to clean this table. As this msdb database resides on c drive, I would like to clean this table. If any one knows how to truncate/delete rows please help.thanks in advance. I am running sql2005 64bit sp2 on WINDOWS SERVER 2003 AMD64.

Migration Testing and Replication

jim.powers — Fri, 16 May 2008 08:14:44 GMT

We will be migrating from our 2000 server to 2005 in the next couple of months. One of our applications I'm a bit worried about and would like to set it up in a testing environment. My goal is to keep the current version live with all of the work being done as it is now but have the 2005 server running for testing purposes. Ideally, I would like for the 2005 database to be updated from changes made in 2000 (the current application), thus my question.I know there are a number of different replication technologies do to this. Ideally, once we prove the 2005 installation to be sound, I would like to simply turn off the replication and the 2000 server and then point the application to the 2005 server. Given the scenario, what would be the best technology use?

merge replication or not?

New_to_being_DBA — Fri, 16 May 2008 08:54:22 GMT

I have two databases that I need to merge. One is parent and other child. The issue I have is I have the same primary key on both databases but the keys pertain to two totally different records. How I merge the two databases (move parent data into child) and keep all the info from both databases? When moving the data from parent into child I want to keep the primary keys from parent as is but change the primary key on the child if it already exists and perform a cascading update on all the tables that use this key that is being changed. Can this be done and if so how?

DBCC SHOW_STATISTICS WITH HISTOGRAM_STEPS run on remote linked servers

KATHLEEN Y ZHANG — Fri, 16 May 2008 12:13:04 GMT

I am seeing a lot of like thisDBCC SHOW_STATISTICS(N'"myDB"."dbo"."myTable"',"Index4") WITH HISTOGRAM_STEPSrunning on remote linked servers and it seems to be causing some performance problems. We have a lot of remote queries. Does anyone know what this does?Thanks!

Audit for SP and DTS jobs

Ramesh Lende — Fri, 16 May 2008 08:34:47 GMT

Hello:I want to find out any changes done to any DTS jobs or Stored Procedures after a specific date. I can find out all new Created DTS by going through sysdtspackages table or any newly created SP’s by going through sysobjects table and searching on type=’P’ having crdate>’mydate’. However, if someone modifies the SP, crdate is not updated. Any help would be appreciated. Thanks,Ramesh.

Data read/write permission

Jimmpy Gill — Thu, 15 May 2008 13:01:47 GMT

I’ve 40 SQL 2005 DBs – I need to grant data read/write permission to windows users on all the user databases. Any such command exist ex. Grant select TO [domain\user] on ALL databases? Thanks~J

Stored Procedure with Parameters

smthembu — Fri, 16 May 2008 07:54:37 GMT

Dear AllI'm a new dba in training, i was asked to write a procedure that will take parameters also to update a column with a NULL for a specific staff, using a parameter of a staff_numb. Please help. Tx!!

Creating a maintanance plan using a script

Nili S — Wed, 14 May 2008 06:08:21 GMT

Hello.I'm trying to find information on the web on how to write a script that creates a maintenance plan for my database. I want to create a script that will create a maintenance plan and will run with the rest of my schema creation scripts at the client site.It seems like MS did not aim for that possibility, and I'm having troubles finding the way to create that sort of script. Can anyone help?ThanksNili

Job Status

Bilichi — Wed, 14 May 2008 22:44:07 GMT

How to check the Status of the Job whether its running or not?

Add partitioning on exisiting table?

Davy Van Laere — Fri, 16 May 2008 02:55:21 GMT

Hi,I'm reading though the partitioned tables section of the books online, but i can't figure out if it is possible to make an already exisitng table partitionned. We have big table at the client that we need to partition, and if possible, we would like to avoid swapping all the data from table to table.Any idea if i can ALTER the table to use the scheme/function? I'm starting to suspect this isn't possible but i hope somebody can prove me wrong :)

T-SQL to Perform Backups

monkster — Wed, 14 May 2008 06:25:13 GMT

Does anyone know of a good tutorial or manual that explains how to use T-SQL to backup an SQL Sever db? Thanks!M

sqlserver 2005

rajuwgm — Thu, 15 May 2008 01:06:09 GMT

In my database I would like to create 10 tables(suppose) ,in that 1. sutdent details 2. student marks 3.student attendence 4. student feesDetailsNow I would like to update or delete a record from base table , the effects should be taken in child tables. for that how could I write a single query which is effecting on more tables . please help me Thanks in advance

FAST audit of SQL Server licensing?

UDBNT — Fri, 16 May 2008 05:05:56 GMT

We are trying to assess on our infrastructure the counts of the various versions of SQL Server that we have.The tool used will be able to look for executuables by name, e.g. SQLSERVER.exe, but that doesn't necessarily clarify what specific version of SQL Server is on a given Server/PC.Is there any way to look for some identifiable file or executable to then better determine this sort of information? It can differentiate on executable size as well.We are trying to distinguish between SQL Server 2000 and 2005 as well as Express, Standard and Enterprise editions.Does anyone have any gem that might help here?Thanks

connection between visual fox and sql server database

trysorel — Wed, 14 May 2008 05:20:33 GMT

i must build a database in sql server 2005 and to access it or complete it using visual fox forms... i am a beginer... what is the connection i must make between them? theoretical...maybe some examples..please

Copy database table with data from one database to another empty data base

manaprime — Fri, 16 May 2008 04:46:07 GMT

I am using sqlserver2005 express and i need to Copy data base table with data from one database to another empty data base.

Linked Server

balamurugan.ganesan — Fri, 16 May 2008 05:27:45 GMT

Hi,I have added a server usingEXEC sp_addlinkedserver 'bala_linked', '', 'SQLOLEDB', '', NULL, 'DRIVER={SQL Server};SERVER=xxxxx;UID=xxxx;PWD=xxxx;'but when trying to fetch from the linked server it givesLogin failed for user 'sa'.[OLE/DB provider returned message: Invalid connection string attribute]any suggestions would be greatly appreciated.Thanks,Balamurugan

Must declare the scalar variable

etcellm — Fri, 16 May 2008 00:03:14 GMT

I am relatively new to sql and may be "biting off more than I can chew"I would like to give the variable @sunday - last sundays date relative to todays date. But it keeps reporting an error that I must declare the scalar variable @sunday. I have checked some previous posts and found that collation played a part in other errors. My collation is SQL_Latin1_General_CP1_CI_AS.DECLARE @sunday datetime;SELECT @sunday = (Select DATEADD(week, DATEDIFF(week, 6, getdate()) -1, 6));Even if I apply a date directly to @sunday I get the error.Any suggestions would be appreciated.Thanking you in advance

xml indexes

Samrat Sen — Fri, 16 May 2008 01:15:45 GMT

if we use xml indexes on a xml column can it dgrade performance if insert,update and delete is more on that column.

Owner of a domain account

fosco — Fri, 16 May 2008 04:48:57 GMT

Good morning, I am doing some database maintenance on a server, I executed: 'sp_helpdb' and was given results of all the databases, one of the columns is 'owner' and I have some owners in the normal format: domain\user name, my question is: How can I find the owner of that account?Many thank

how to restrict user access of tables in sql server 2005

jananik — Fri, 16 May 2008 04:12:22 GMT

Hi ,Would appreciate if someone could let me know how we can restrict access such as read-only/full access to the tables in the database Thanks

SQL Server 2005 service restarting itself

Denise McMillan — Thu, 15 May 2008 11:06:49 GMT

We have a SQL Server 2005 cluster set up, with 2 nodes, on Windows 2003. Lately there has been two times where SQL Service has just restarted itself. I'm having trouble figuring out why. I don't see any errors before it happened either time.We have been getting this message periodically though: A significant part of sql server process memory has been paged out. This may result in a performance degradation. Could this be causing the restart of SQL Services? What else should I look for?Thanks very much for any ideas.

ERROR IN CONFIGURATION OF REPORT SERVER

patr — Thu, 15 May 2008 02:52:12 GMT

HOW TO SOLVE THIS ISSUE IN SQL SERVER REPORTING SERVICESHTTP:/LOCALHOST/rEPORTSERVER IS NOT WORKING INSTEAD IT IS SHOWING THE BELOW ERROR MESSAGEThe report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportServer\RSReportServer.config' is denied. --------------------------------------------------------------------------------

SQL Server 2000 installation

Raghavender — Wed, 14 May 2008 21:13:16 GMT

Hi All,I have installed SQL Server 2000 standard Edition but while installing I gave Per Seat Licence number wrong. Is there any possibility to change the per seat number after installing SQL Server 2000 without reinstalling the Software.Thank youRaghavender

SQL Server Management Studio Pakage failed to load

Martin Vrieze — Thu, 24 May 2007 17:12:00 GMT

I am unable to open SQL Server Management Studio.

The following error message is encountered...

"Package 'Microsoft SQL Management Studio Package' failed to load."

There is no documented errors of this kind in Technet but there were some notes on this problem in obscure internet web sites.

This was a new install of just the Management Studio...client utilities. No betas or anything like that. SQL Server 2000 client utilities are also on this machine. It worked for awhile before this error began. I am unable to do any work in SQL Server 2005 until this problem is solved.

Restore MSDB

prabhakarannsn — Wed, 14 May 2008 22:25:38 GMT

Dear All,I have installed sql server 2005 standard edition , now i'm going to install(not upgrade) sql server 2005 Enterprise edition. I have to copy all the jobs that i created on standard edition , so that i planned to take the backup of msdb from standard edition and restore that on Enterpriseedition.Is this possible.Thanks and Regards,N.Prabhakaran

SQL Server 2005 Not Recognizing a newly added SAN Drive

gina_larose — Thu, 15 May 2008 10:33:42 GMT

We have an active/passive SQL 2005 cluster in which the physical drives are on a SAN. The cluster sees the drives as a physical resource without issue, however, SQL Server does not see a newly created drive at all. i went to modify my maintenance job to write the backups to the new drive and it is not available for selection at all. I have not yet rebooted nor failed over the cluster. Any suggestions?

SQL Trace Server Side for Named instance

HKwai — Thu, 15 May 2008 14:15:30 GMT

I have a server Side Trace and ran it as a job from my named sql server 2005 instance.However, the Server Side Trace is Tracing for the Default instance.What parameters or values must I enter in the Server Side Trace script so it will trace the correct sql server instance? I google all over and nothing is found for this topic.

SQL Migration and MSDN Access

Sanjeev Jha — Thu, 15 May 2008 12:29:27 GMT

Hello friends,Just brought to my attention with the following:"The MSDN subscription we need is xxxxxxxx and the cost will be $xxxx.xx. We need this to move forward with the enhancements for existing MS Access database migration to migrate to ASP .net 3.5 and Visual Studio 2008. We are currently at ASP .net 1.1 and Visual Studio 2005 which is about 3 years old and much more has come out as far as enhancements in 2008."My questions is migrating the Access DB into SQL 2005 is really the old thing already? What are the business implications behind this may be or any technical compliance issue? Please share your knowledge and experience. I am trying to educate myself in this. I appreciate your time.