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

learn more… | top users | synonyms (2)

3
votes
2answers
774 views

Can 2 tables lock each other if concurrent read/update do not affect same rows? pagelocks off

Can 2 tables cause a deadlock, if the pagelocks are off, and the rows are not related. E.g. Query 1 runs for a few seconds Select * from Orders where CustomerID = 1 and DateOrdered between @D1 and ...
7
votes
3answers
16k views

SQL update satement taking a very long time / high disk usage for hours

Yes it sounds like a very generic issue, but I have not been able to narrow it down very much yet. So I have an UPDATE statement in a sql batch file: UPDATE A SET A.X = B.X FROM A JOIN B ON A.B_ID = ...
8
votes
2answers
199 views

What is the effect of replacing indexes with filtered (non-null value) indexes?

Our project runs a very large, very complicated database. So about a month ago, we noticed that the space used by indexed columns containing null values was getting too large. As a response to that, I ...
1
vote
2answers
7k views

Getting SQL Server query history

I have used below query to get the history queries on ran SQL Server but it's not giving me the whole history it just returns queries ran on nearly about last 1/2 an hour. I want to get the queries ...
7
votes
4answers
1k views

Returning rows where all non-NULL values are equal

Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say: WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL this ...
7
votes
1answer
3k views

Is “Estimated Number of Rows” always per execution within SSMS execution plans?

When I look at execution plans, I often compare "Estimated Number of Rows" with "Actual Number of Rows", looking for discrepancies. For example, when I hover over the bottom arrow in the picture ...
6
votes
2answers
38k views

Primary filegroup is full SQL Server 2008

I have a large table (~50 million rows) I'm trying to bulk insert into SQL Server and I get the error: Could not allocate space for object 'myDB' in database 'I 3 Stroke' because the 'PRIMARY' ...
4
votes
1answer
6k views

How to get a list of global temporary tables in the current database?

I am trying to get a list of the global temporary tables that are currently active. There seems to be an issue with them bumping in to each other (same name). As the 'acting' dba, trying to sort out a ...
3
votes
1answer
321 views

How to recover data around corrupted data in table? [closed]

I have a SQL Server database with some corrupted data unrepairable via DBCC CHECKDB in a table. I can get many of the rows before the corruption out - but how do I get the rows after the corruption ...
18
votes
6answers
58k views

How to query last restore date in SQL Server?

Is there a TSQL query that shows the last restore datetime for a certain database?
1
vote
2answers
846 views

How to link each column in sql?

I have been working around on a query given by one of my developers who works on crystal reports as a junior DBA, I always like to help them even though I am busy with other things as it will increase ...
5
votes
2answers
133 views

Relationship that are optionally more specific

Forgive my title, I couldn't think of anything that accurately describes what I'm talking about. I currently have the following relationship. CREATE TABLE Events ( ID INT IDENTITY(1,1) NOT ...
3
votes
1answer
627 views

Need help with long running query

UPDATE: Adding a clustered index to each work table covering all fields reduced the query run times to su-bsecond. I consider this question closed. Thanks for taking the time to read this post - ...
5
votes
1answer
486 views

Why did my transaction log grow so much? [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? About a month ago I migrated my SQL Server 2008 R2 database to a new schema. I ran scripts to migrate the ...
1
vote
1answer
10k views

To drop a table, do I have to drop all foreign key constraints or can I just NOCHECK them?

I know you cannot drop a table that has active foreign key constraints. So I was wondering is it safe to: ALTER TABLE myTable NOCHECK CONSTRAINT all DROP TABLE myTable Rather than dropping the ...
2
votes
2answers
122 views

Index included columns

After running a fairly hefty query, the execution plan gave me a missing index suggestion which was of the form: (Timestamp) INCLUDE (CustomerID, EventID, ID, EmployeeID) Which seems to be a ...
2
votes
2answers
2k views

Conditional WHERE clause

I have, from what I can tell, a unique question. I would like to know if there's a way to construct a query in which the existence of the actual where clause itself is conditional. I'm writing a ...
1
vote
1answer
2k views

Missing 'Microsoft Office 12.0 Access Database Engine' in SSMS

I've got several users that need to import / export ACCDB Access files into out of SQL Server. Unfortunately, I have been unable to get 'Microsoft Office 12.0 Access Database Engine' to show up as a ...
1
vote
3answers
5k views

Transaction log for SQL Server is too big

I have a database that has a 33 gb transaction log. It is caused by the fact that the database is in full recovery mode has been used for over 5 years prior to the first backup. I want to truncate ...
5
votes
1answer
4k views

How can I fix “Cannot perform a differential backup … a current database backup doesn't exist”?

We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day. The problem is, the differential backups don't always seem to ...
2
votes
0answers
713 views

How much disk space needed for 100GB of DBs?

Hard to explain in the title, but I'm planning a migration from SQL Server 2000 databases to SQL Server 2008 R2. The databases are under 100GB total space, but this is only with the Simple recovery ...
3
votes
1answer
429 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
8
votes
1answer
8k views

Can I retrieve all database objects owned by a particular user?

We have a user who is leaving and I need to know every database object that he owns. Is there a query that will provide this information?
8
votes
1answer
1k views

Is that a good idea to use one database for 50.000+ shops?

I know Shopify use only one database for all shops. But how can they handle their database with such a big data? Is that a good idea to use single database for 50.000+ shops?
1
vote
1answer
550 views

Set based solution to group multiple rows within a sequence

Using SQL Server 2008 and a set based solution, how could I manipulate the following data: GroupID Code StartSeq EndSeq StartDayNo EndDayNo 1755549 3506 0 0 59442 ...
16
votes
2answers
13k views

How to keep history of SQL Server stored procedure revisions

Note: I am not asking about full version control. Is there any way automatically to keep a history of stored procedures on SQL Server. Similar to how Google Docs automatically keeps a history of ...
8
votes
1answer
2k views

Warning in query plan “Cardinality Estimate”

create table T(ID int identity primary key) insert into T default values insert into T default values go select cast(ID as varchar(10)) as ID from T where ID = 1 The query above has a warning in ...
2
votes
1answer
7k views

Active/active SQL Server Clustering in Installation Center (setup.exe)

I like to know which option to choose in the setup.exe in SQL Server 2012/2008? What I have here is Windows Active/Active Cluster setup with one quorum and one msdtc configured. I installed my first ...
3
votes
1answer
19k views

invalid object on a table that exists

I am getting an "invalid object" warning error in the Management Studio query. Any query against the table works and it links correctly to another table. I am investigating an error that I'm getting ...
9
votes
2answers
989 views

Increased RAM, Worse Performance

Setup: Windows Server 2008 R2 SQL Server 2008 R2 SP1 240GB RAM TempDB is 8x16GB data files w/out auto-grow (128GB total) Physical/Stand-alone Server This server is used for ETL processing. We ...
4
votes
1answer
82 views

SP for Restoring Database and performing other tasks on it

I am creating a stored procedure for our QA dept that will do the following: Accept specific inputs to generate a consistent name, restore a database (giving it the generated name,) set the db up for ...
2
votes
0answers
779 views

Importing Excel files with SSIS

I'm trying to import an Excel spreadsheet into SQL Server 2012 using SSIS. There is only one sheet in the file. The file is a SalesForce export. I believe the Sheet name changes depending on when the ...
5
votes
1answer
1k views

Enable TDE for existing AlwaysOn databases

I need to do a little benchmarking of AlwaysOn, both with and without TDE enabled. All the instructions I see cover adding encrypted databases to an availability group, but I see no mention of ...
5
votes
2answers
2k views

Why can't I read my transaction log backup file using fn_dump_dblog?

I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery. After backing up the transaction log, I attempt to read it ...
7
votes
2answers
7k views

Splitting TempDB into multiples files equal to number of CPUs

The article SQL Server tempdb Best Practices Increase Performance suggests that I should split tempdb into a number of files equal to the number of cores. So for 4 cores you get 4 files. By ...
4
votes
3answers
288 views

SQL Server Database Synchronization

I have a database in a server in Africa, and need a daily sync\restore to a server in Europe. My first idea was to sync the data between the 2 databases, but the database has more than 1000 tables, ...
2
votes
0answers
233 views

Local users access SQL Server correctly, but domain users don't return all the data

We have just changed from a workgroup to a domain running SBS 2012 and SQL Server 2008R2. The problem I have is when logged in as a domain user my application connects to the server but only returns ...
4
votes
1answer
117 views

How to get the information of the optimizer of sql server?

The info like total optimization time, the time spent on each stage of optimization, the number of alternative plans and the cost of each alternative plan. Is there any way to get these info using sql ...
6
votes
5answers
15k views

Use Older Versions Of SQL Server Databases With 2012?

We have been asked to move from SQL Server 2008 R2 over to 2012, the installation completed a few hours ago. Ever since I've been trying to attach databases from 2008 over to 2012. It seems to be ...
0
votes
1answer
69 views

View server login permission

I've been working on giving a development team the ability to have read-only access to a SQL environment, I'm at the last step. I need them to be able to see users/logins and roles. I noticed that ...
2
votes
1answer
1k views

SQL Server Error 833 On LDF File

I'm getting error 833 on my ldf file and i'm getting it a lot. Please see the following. When this happens, my application is hang already. I know error 833 has been reported a lot, but what does it ...
6
votes
1answer
6k views

How to simplify SSAS deployment from DEV to PRO environment

Here is my issue: In my company, there is a team of developper who creates cubes, dimensions(...) in SSAS in a DEV environment (let's call it SSASDEV). This environment is bounded to a SQL Server ...
1
vote
1answer
6k views

SQL Query Killed, remains in Kill/Rollback with estimated time increasing

I killed a transaction and now it remains in Killed/Rollback status and whenever I run Kill 64 with statusonly I get this output: SPID 64: transaction rollback in progress. Estimated rollback ...
3
votes
1answer
123 views

How widespread is the adoption and usage of database snapshots?

After reading up on database snapshots, it seems that they are useful as a backup and quick restore method for small DDL and DML changes. Additionally they are useful for large scale dev/test ...
3
votes
2answers
134 views

SQL server 2000 transactions with the same time

I have a BIG problem here. I'm creating a program that can be looping if the connection to the server is broken. But when the program is running for a few months, I found a bug on SQL server 2000. I ...
1
vote
1answer
205 views

Ensuring Only Fresh Data Gets Accessed on SQL Server 2008/2012 Replication For Reads

I am designing a scale out solution for a globally dispersed company that has data centers on all continents. Over 10,000 servers report to a SQL Server cluster in Los Angeles, and also query it to ...
3
votes
2answers
3k views

SQL Server Agent - Run Query at the end of each month with date variable

I am running SQL Server 2008 R2, and I need to create a new job that will basically run a query at the start of each month (at the 1st of each month at 01:00 AM). Here is the query: INSERT INTO ...
2
votes
1answer
834 views

SQL Server manage indexes

Is it possible to automate the managing of indexes on my database? I'm looking to see if there are any scripts or programs that can automate this process. Would it be possible to use stats generated ...
1
vote
1answer
685 views

Replication and linked server security

I have three servers: P1, A1, and R1. P1 and A1 are production oltp servers. On each there are two databases that are being replicated down to R1 for olap (mostly SSRS reports). I am working on ...
10
votes
1answer
1k views

Why don't SQL Server queries use more than 7MB/sec of disk I/O

I have an SSD that, using IOmeter test, shows performance over 200MB/s. However when I run any SQL query from local machine the windows resource monitor never shows disk IO above 7MB/sec. This holds ...