We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.

Filter by
Sorted by
Tagged with
3
votes
2answers
3k views

In Microsoft SQL Server's linked server configuration, what does the “RPC” setting do?

I'm not talking about "RPC Out". That setting works as I expect it to: when "false", I can't call stored procedures on my linked server. When it's "true", I can. The specific option I am referring to ...
4
votes
2answers
4k views

How can I get a list of column names and types from a resultset?

Say I get a resultset back from the following query: select * from sys.database_files; For any given resultset, I want to be able to query the column names and types so I can then create tables to ...
39
votes
6answers
11k views

Why do sequential GUID keys perform faster than sequential INT keys in my test case?

After asking this question comparing sequential and non-sequential GUIDs, I tried to compare the INSERT performance on 1) a table with a GUID primary key initialized sequentially with newsequentialid()...
5
votes
3answers
16k views

How to reduce the log file size without shrinking it in SQL server

In SQL server, how do you reduce the log files size without (DBCC) shrinking it. I know shrinking the log file will free up some space, but will also cause fragmentation. Doing a checkpoint in simple ...
23
votes
3answers
5k views

Justify NOT using (nolock) hint in every query

Have you ever had to justify NOT using a query hint? I am seeing WITH (NOLOCK) in every single query that hits a very busy server. It is to the point that the developers think it should just be on ...
20
votes
1answer
4k views

When should extended events be used instead of SQL Profiler/perfmon?

The extended events seem like a better technology and less stress on the server, but the SQL Profiler/perfmon has better tooling. Also the extended events seem to have a steeper learning curve. In ...
4
votes
1answer
1k views

Sql 2000 attach Error 5172

When I attach a database file from SQL Server 2000 Standard Edition to SQL Server 2000 Database on another machine , I am receive the following error. "Error 5172: The heather for file 'D:...' is ...
16
votes
5answers
11k views

When should nonclustered indexes be stored on separate filegroups?

I have heard that storing indexes on a different filegroup and drive increases performance in a database because the drive doesn't have to go back and forth between the index and the data to which the ...
6
votes
2answers
8k views

What is the best practice for storing image varbinary(max) data in a database table?

I have a table that stores images that range in size between 16-100 KB each. Since the images are so small, I've taken Microsoft's advice and not used the FILESTREAM data type. The table is ...
2
votes
1answer
2k views

How to ensure that SQL Jobs run in the correct order within a minimal maintenance window?

From this question I learned about a script which creates jobs for backing up databases, checking database integrity, optimizing indexes, and cleaning files. According to the documentation, this ...
4
votes
3answers
1k views

How bad is a tempdb drive failure (with multiple tempdb files on different drives)?

I'm getting a new 16 core server for my SQL Server 2005 database. I would like to create 16 files for my temp DB, and put each one on a separate drive. If one of these drives fails, can SQL Server ...
4
votes
1answer
2k views

What is the best practice for backing up a database in Simple mode?

I am trying to create a SQL Job for backing up my database. I want to have: A weekly full backup A daily differential backup Something that will clean up backups that are older that 14 days I ...
2
votes
1answer
109 views

Help on writing server-side trace

I need help on writing a server-side trace to check logins to try to determine if and when vendor accounts are used. The purpose is to remove sa access. These accounts sure have sa but I need to ...
25
votes
6answers
89k views

How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist

I have a SQL server 2008 instance running. Unfortunately during testing I deselected the sysadmin rights for my login and now can't readd it (because I don't have sysadmin rights). There are no other ...
40
votes
4answers
96k views

How do I assign an entire Active Directory group security access in SQL Server 2008?

I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange ...
12
votes
5answers
7k views

Group By hour over large dataset

Using MS SQL 2008 I am selecting an averaged field from 2.5 million records. Each record represents one second. MyField is an hourly average of those 1 second records. Of course the server CPU hits ...
4
votes
3answers
1k views

Should I create a maintenance plan for my database or use jobs?

I tried creating a Maintenance Plan on my SQL Server 2008 database and was impressed by some of the things that are available out of the box. However, after it was created, it seemed difficult to ...
3
votes
2answers
6k views

Stored procedure parameters using tsql functions?

Environment sql server 2005 sp3 I have a stored proc that takes an INT as input. I want to CAST a CHAR to an INT during the call to the stored proc. it seems I cannot do that. I get a syntax error ...
7
votes
4answers
4k views

Duplicate database and keep it up to date - Replication or SSIS?

I would like to duplicate a database, on the same server, and keep it up to date either by running a scheduled service once a day or having SQL Server 2008 take care of this internally. We don't need ...
2
votes
2answers
6k views

Sp_helptext Error

I am using sp_helptext to list some stored procs instead of going through the ui. I am having a problem with the naming convention we have chosen to adopt. I have a stored proc nameed [Test.FooBar]. ...
32
votes
6answers
22k views

Make SqlClient default to ARITHABORT ON

First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection. For performance reasons I've created ...
3
votes
1answer
224 views

Is there a way to load a sql script with a specified encoding into a running SQL Server Management Studio (SSMS)?

Just as the question says. I have scripts with 3 different encodings and feel offended by having to manually select of about 200 possibles I was never interested in. ( I didn't count them ).
10
votes
4answers
2k views

What is the best way to monitor long running queries in SQL Server?

I have to run quite a few long-running queries (rebuilding indexes, updating large sets of data) for my database. Is there an alternative to running the query in SQL Server Management Studio and ...
5
votes
1answer
1k views

What are the common reasons for a Subscription to be deactivated in SQL Server 2005?

I am trying to manage database replication in SQL Server 2005. I have set up my publications, distributors and subscriptions and it seems to work properly at the start. However, after a period of ...
3
votes
2answers
294 views

Report model data source

Does anybody know how i could use a linked server as a data source for a report model in SQL server 2008 r2? or how i could make this linked server a data source on a report model? Please help. Thank ...
8
votes
1answer
1k views

B-tree node split strategy in SQL Server for monotonically increasing value

Consider a B-tree index on a value that will always increase monotonically, e.g. a column of type IDENTITY. With a conventional B-tree implementation, whenever a node is full, it will be split 50%/50% ...
5
votes
1answer
709 views

Which is the most performant way to copy a number of SQL Server tables to a readonly SQLite database?

I have a database with about 50 tables with use together about 1 GB space. The SQLite database will be readonly. This copying will be done once each month. EDIT: I see one possible solution using ...
1
vote
3answers
255 views

DBCC CheckDB Consistency error on a empty table

What could be the reason behind a DBCC CheckDB reporting a consistency error on a empty table in sql server 2008? This DB is restored from sql server 2000. DBCC CheckDB on sql server 2000 doesnot ...
3
votes
2answers
3k views

Sql Server output parameters

Is there a way to use named parameters when getting the output parameters of a stored proc? Currently my knowledge of output parameters is very limited. It looks like I have to use them in order of ...
7
votes
2answers
2k views

SQL Server - RangeX-X and RangeI-N locks

I came to a dead point in a deadlock analyze. According to msdn: RangeX-X are Exclusive range, exclusive resource lock; used when updating a key in a range. RangeI-N are Insert range, null resource ...
1
vote
1answer
4k views

“Request to run job dbxxxxx refused” error in merge replication

I'm doing merge replication whole of my database but when I create the subscription I encounter this error: SQLServerAgent Error: Request to run job DBXX-XX--bazresiostani2-2 (from User sa) refused ...
1
vote
1answer
187 views

MySQL queries in SQL-Server?

How can we best use our existing MySQL queries with SQLserver? Alternately, what type of changes do we need when we are converting our database server from MySQL to SQLServer?
1
vote
2answers
1k views

maintenance plan executing by agent

I have a built-in, regular maintenance plan scheduled to run weekly. The databases are SharePoint databases and a snap manager (netapp snap manager) backs up these databases. The rebuild ...
4
votes
2answers
2k views

return scope_identity() question

environment sql server 2005 In my stored proc i am calling another stored proc to insert several rows into several tables. The last row inserted i want to pass the identity of that row to the ...
73
votes
6answers
172k views

SQL Server Cannot drop database <dbname> because it is currently in use… but no sessions displayed

When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database. ...
2
votes
2answers
7k views

Risks of Switching Between Simple & Full Recovery in SQL Server 2005

Ok, so we have several instances on several nodes in a clustered environment. One of them is for JD Edwards stuff and has had an issue lately with a job failing because there was not space in the ...
7
votes
6answers
2k views

How do I rule out SQL Server as the reason for a slow website

A website we host has recently been experiencing slow load times. It's been ruled out that the problem lies with the web server and it maybe database related. What steps or procedures do I take to ...
2
votes
2answers
2k views

SQL datatype choice for flagging a read/ unread message?

I have a table where I want to track Boolean type operations. in addition, a business requirement is that I know the date of the action and not just the Boolean value. for example if a message is ...
5
votes
4answers
9k views

SQL Server IDENTITY column automatic re-seed

I work with huge datasets. Many of the transactions that take place in my database are enormous - trillions of rows, and more. Some of the tables use IDENTITY columns, not for unique IDs, just ...
1
vote
1answer
148 views

Sql DateDiff Question

Select distinct DATEDIFF(ww, GETDATE(), siparis_test) AS Hafta The first day of week is sunday in the above function. How can I change it so that monday is the first day? Thanks in advance.
4
votes
2answers
4k views

Can I have different publishers and one subscriber in replication?

I have the same databases in three cities which each database have three tables: 1- tbl_worker 2-tbl_workshop 3-tbl_users and cities are:city1,city2,city3 city2 and city3 insert informations in ...
9
votes
3answers
382 views

Any pointers on reducing my privileges in production but not making my job excessively difficult

Running SQL Server 2005 and 2008 on Windows 2008 R2. We're going to be reducing privileges in production for developers - and I'd like to do the same for myself as a DBA, limiting rights to ...
2
votes
1answer
728 views

SQL Server 2000 Enterprise Manager - “Navigation to the webpage was canceled” error when trying to design a table

While working in SQL Server 2000 Enterprise Manager on a remote server today, I wanted to look at a table's columns, so I found the table, right-clicked on it and selected "Design Table". Instead ...
2
votes
2answers
739 views

Determining sizes for Separated Partitions of programs, data and logs in SQL Server

As per the recommendations of the CIS benchmarks for SQL Server, I want to create separate partitions for the data, the programs and the logs on my SQL Server dedicated server. Question is, what is ...
9
votes
1answer
3k views

What is a residual predicate?

Although I was able to find the definition of predicates online at MSDN: Is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE ...
11
votes
5answers
12k views

How to monitor deadlocks

When do you start troubleshooting a SQL Server 2005/2008 deadlocks and how? The alert is turned on SSMS via SQL Server performance condition alert, objects->SQLServer:Locks, Counter->Lock Waits/Sec, ...
5
votes
2answers
2k views

SQL Server Wildcardless LIKE Optimization

In a web application using SQL Server 2008 R2 as the backend, I have several fields, that if present, add WHERE clauses to the query. If I was to have all queries generated as like clauses, such as: ...
7
votes
2answers
428 views

In Oracle, can we restore a database onto the same instance with a different name?

In SQL server I can restore a database onto the same instance with a different name. Eg: If there is a database DB_prod, I can take a full backup and restore it on the same instance with a different ...
5
votes
3answers
10k views

Query to normalize table/combine row text

I have a table (call it oldTable) with columns like so: ID (int),Rank (int),TextLineNumber (int),SomeText (varchar) The primarykey is multi-part: ID+Rank+TextLineNumber. I'm trying to transform/...
12
votes
4answers
9k views

Dropping a constraint (index) on a column

How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had ...