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
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 ...
74
votes
6answers
174k 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
730 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
429 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 ...
7
votes
1answer
828 views

Dependant T-SQL comparison collation

Is there any way to set the collation for a string comparison based on an independent variable or column value? Something akin to SELECT COL1, col2 FROM [TABLE] WHERE COL3 LIKE '%string%' ...
40
votes
5answers
74k views

SQL Server “Suspect” database?

What do you do when you have a database that is marked as Suspect? Restore from the last backup? Please advise.
4
votes
2answers
653 views

How can I extract a lessequal char from SQL_Latin1_General_CP1_CI_AS?

When I create a table like this create table char_test( item varchar(10) collate SQL_Latin1_General_CP1_CI_AS ) go I can store varchars containing ≥ insert into char_test values ('≥'); When I ...
2
votes
2answers
1k views

Best index strategies for read-only table?

I have a table in SQL Server which has following characteristics: will contain about 1.2M records. first is empty, will be inserted in batch many times (precisely, 64 times), 20-100k records/each the ...
3
votes
2answers
8k views

datatypes in sql server , difference between similar dataypes, numeric, money , decimal ,float

What datatypes could be given to this value --> 45.58 Could these be given: numeric money decimal float What's the difference then? Which one would be the best to use?
5
votes
1answer
2k views

Why is this rollback needed when using sp_addextendedproperty in a stored procedure?

In enter link description here I showed, how I document databases. To insert the extended properties, initially I uses plan sequences of sp_addextendedproperty calls. But lately I wanted to catch ...
7
votes
4answers
10k views

Property PopulationStatus is not available for FullTextCatalog… How to fix?

I've created a new catalog (case insensitive, make default catalog), when I try to right click > properties to add tables I get this error: Property PopulationStatus is not available for ...
3
votes
1answer
3k views

A nonrecoverable I/O error occurred on file after DB Shrink

After shrinking an SQL Server database & files, I encountered an error message saying "A nonrecoverable I/O error occurred on file" while performing database backup. Why am I getting this error?
18
votes
3answers
1k views

How to store 'n' days of web server logs in Sql Server?

For faster reporting and performance analysis, we want to insert our web server logs into Sql Server. This will allow us to see traffic patterns, issues, slowdowns in near real-time. We have a daemon ...
9
votes
1answer
5k views

SQL Server Deadlock Graph - Table, Page or Row Lock?

Is there any way to decipher if a lock in a deadlock graph is Table, Page or Row level? I have all the information I need from the graph, including the Isolation Level, (2) but I really want to know ...
7
votes
6answers
2k views

SQL Server 2005 Huge DB issue

My DB files are growing very fast ( designer is not me and just a weekly report makes 7 GB growth ). And finally, I don't have enough space in the disk. And number of un-reported weeks counting up. ...
4
votes
3answers
1k views

Most suitable DB Architecture for real-time high-volume processing API

I've got a service that should support real-time interaction between many users (thousands real-time). I use .NET 4 with MSSQL 2008 on IIS 7.5. The DB consists of only a few tables without high usage ...
33
votes
7answers
68k views

How can I tell if a SQL Server database is still being used?

We're looking to decommission a SQL Server instance which has a couple databases still remaining on it. How can I tell if they are still being used by users or a web application? I found a forum ...
9
votes
3answers
926 views

Speed impact of using varchar in SQL Server 2008

In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any ...
2
votes
2answers
527 views

Database Mirroring Failure Reasons

I was wondering if there is any information on what scenarios might cause a DB Mirror to have to be recreated? I am attempting to research how sturdy the technology is and under what scenarios do we ...
3
votes
2answers
345 views

Comparing two XML query plans

Hopefully a simple one, if I have two (fairly complex, 30-40 node) .sqlplan files (from the same SQL text) what is a good way to compare them side by side? Or just have it tell me what it did ...
16
votes
2answers
9k views

SQL Server 2008 - Partitioning and Clustered Indexes

So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario. Comments about how we ...
4
votes
4answers
5k views

SQL Server 2008 R2 Ent trial expired move to express?

So someone let our SQL server 2008 R2 enterprise trial expire on a web server. It was part of a plan to restart a project and move everything we had over to MySql instead. We don't need anything ...
8
votes
1answer
5k views

Print Parameters in Dynamic SQL

I've used dynamic SQL for many tasks and continuously run into the same problem: Printing values of variables used inside the Dynamic T-SQL statement. EG: Declare @SQL nvarchar(max), @Params ...
5
votes
2answers
1k views

DMV Queries as Perfmon Counters

Certain DMV queries that return various statistics that increment since boot would be useful as perfmon counters. For example, take: SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats; Most ...
21
votes
4answers
25k views

How do I switch off SCHEMABINDING for a view without recreating it?

How do I switch off SCHEMABINDING for a view without recreating it?
5
votes
2answers
4k views

tsql: table data size won't shrink after large row deletion

i have a table with 12 months+ data in it. I backup the table then delete all rows keeping only rows inserted in the last month. But the table size won't shrink, nor the underlying physical file. ...
7
votes
1answer
2k views

Nonclustered index column order

I have a nonclustered index (called NCIDX1) on col1, col2 of a table. I included col3, col4, col5, col6 as included columns in this order. If I run the following query: select col1,col2 from ...