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)

4
votes
2answers
825 views

SQL Server 2012 CPU spike due to LinkedServer - how to find the offending query?

As part of another question (SQL Server 2012 performance issues (maybe due to linked server)) I've identified a CPU spike to be directly related to a spike in Bytes Received, which leads me to believe ...
4
votes
4answers
6k views

SQL Server Min Server Memory

using SQL 2008 R2 with SP2 with a server with 64 GB of RAM. I have set max server memory to 58GB considering SQL Server is limited to only database engine related service only. I have a question ...
4
votes
1answer
3k views

Real Time SQL Server Analysis Services Cube Processing Report

I would like to know if there is a way to generate a report to determine which SQL Server Analysis Services cubes are processing using SQL Server Reporting Services? I would like to be able to run ...
4
votes
2answers
2k views

Restore all applicable files in a backup device in T-SQL

I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a ...
4
votes
1answer
766 views

Transaction log growing on a mirrored principal

I have a pair of SQL Server 2008 R2 servers hosting a synchronously mirrored database with no witness. This weekend, we had to do some network maintenance so I paused the mirroring. Upon resuming, the ...
4
votes
2answers
3k views

Recover deleted records [duplicate]

Possible Duplicate: How do I get back some deleted records? Our C#.NET application mistakenly deleted all records from all tables of its database. The application automatically takes ...
4
votes
5answers
3k views

Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

Is it possible to have SQL Server 2008 R2 Standard automatically execute a stored procedure in any given database that is restored or attachced to the instance? I've got close to a solution by ...
4
votes
2answers
4k views

Cross-database call fails in a job but succeeds in SSMS

I create two databases, a table in the second database and a stored procedure in the first database. The stored procedure cross-database accesses the table. I create a sql server login and I also map ...
4
votes
1answer
1k views

Are resource_semaphore_query_compile waits causing 100% CPU on server?

Of late, the cpu usage on the db server is consistently at 100%. Even at 2 in the night when there is almost no traffic on the website and the load is negligible. While trying to find the root ...
4
votes
3answers
4k views

Why might Sql Server Agent's Next Run Time value not be what I expect? And is this definitely the next time my job will run?

At times, I'm seeing some inconsistencies with sql server agent's value in the sysjobschedules.next_run_date and time fields. I have a web interface that interacts with the sql scheduling system. ...
4
votes
1answer
944 views

SQL Server Alert Based On Severity Level Not Working

I am new to SQL Server alerts, and I am trying to setup an alert That will run a job and send a notification, based on severity level. In order to test if the alert works correctly I have downloaded ...
4
votes
1answer
3k views

Attaching a MDF with a missing LDF [duplicate]

Possible Duplicate: How do I attach a database in SQL Server? I was given an MDF for a database, no backups and not LDF that I was asked to attach the database to a SQL server. I get the ...
3
votes
1answer
77 views

Tool for quickly backup and restore a table

Is there a tool for quickly backup and restore a table's data, preferably to a csv file? It would be best if I could do it using batch. Run a .cmd file and data is backed up into a file with datetime ...
3
votes
2answers
7k views

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values

One of INSERT INTO script is written as following. INSERT INTO tableName (Column1, Column2,....) VALUES (value1, Value2,...), (value1, Value2,...),.... Following is the error we are facing on ...
3
votes
2answers
4k views

SQL Server Database is corrupted

Unfortunately hard disk of my system is damaged, now I recovered my database. But it cannot be attached again to SQL management studio 2005. The error is: myfile.mdf is not a primary database file. ...
3
votes
1answer
142 views

How to back out of mass update in SQL Replication

We have a database which is set up in Merge Replication. We accidentally updated hundreds of thousands of records in a large published table on the publisher database. We did something like this: ...
3
votes
1answer
8k views

Writing a query to find all employee salaries that are higher than their managers

I have two tables with some dummy data. I need to find all employees that make more than their managers. I was led to believe the point of the exercise is to learn how to do subqueries. I can isolate ...
3
votes
1answer
995 views

How to pinpoint root cause of excessive log file growth

The log file stays pretty small (~500MB) until about 11PM, and at that point it begins growing until no disk space is available. The log file reached it's maximum size at 12:39AM, so the growth took ...
3
votes
1answer
545 views

SQL Server DMV to show if database is used

What is the best indicator in finding out whether a database is utilized or accessed? I tried index statistics and based on I/O utilization but none seem to be a good indicator. Problem: I have a ...
3
votes
3answers
2k views

Best practices for configuring the Drive/Filegroup setup for Microsoft SQL 2008

I am a software developer for a small company. I wish we had a dba but not in the cards at this point for this company. I was working on migrating our 75gb SQL Server 2000 database to SQL Server 2008 ...
3
votes
3answers
1k views

Is there a way to execute sql statement in sql server based on sql server version?

I would like to create computed index on a table if sql server is 2008 or newer and a simple index if sql server is 2005 or older: -- check for sql server version if (select ...
3
votes
1answer
216 views

join syntax / style performance consideration

Edit: I've added a follow up question based on comments by @ypercube about semantic equivalence and my findings of the query behavior changes when you have a mix of outer/inner nested joins. We ...
3
votes
0answers
1k views

Once more about SQL Server virtual accounts

Let me reboot my question on SQL Server virtual accounts permissions The (same) MSDN article on the same version 2012 of SQL Server "Configure Windows Service Accounts and Permissions" states: ...
3
votes
2answers
4k views

Snapshot for transactional replication taking too long for large database

I want to establish a transaction replication of a large database (200GB). But when I create the publisher and the snapshot agent starts, it takes very very long time and it sticks on the following ...
3
votes
2answers
3k views

Table data transfer using SSIS and triggers on destination table

I'm a novoice in db operations. SQL Server 2008 R2 is the database I'm using. I need to move selected rows from a staging database table to production database table. I'm using SSIS package to do ...
3
votes
1answer
2k views

How does Log shipping knows to keep track

I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012). At first i took a full backup of the first one and restored it on the other one and than i set up the log ...
3
votes
3answers
2k views

Does Change Data Capture (CDC) work in a SQL Server 2012 AlwaysOn Failover Cluster Instances setup?

Does Change Data Capture (CDC) work in a SQL Server 2012 AlwaysOn Failover Cluster Instances setup? There would be two nodes in the AlwaysOn Failover Cluster Instances setup and two Domain ...
3
votes
2answers
478 views

Migration to databases Domain Users do not have access

I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be ...
3
votes
2answers
6k views

Disk space full but logical space available in database

We have a fairly big MS SQL 2008R2 database that resides on a SSD drive. The drive itself only has ~110Gb of space, and the database files are the only files on the drive. The database is in "Simple" ...
3
votes
4answers
2k views

What is the best permissions to set for only creating and managing own databases?

I want to create a login for a new user who could only create and manage their own databases. Other databases on the server should be read-only to that user. What would be a good set of ...
3
votes
3answers
288 views

Providing access to the SQL server database for outside developers

I am beginning a project that will involve insert and updates to a sql server database from a website. I maintain the SQL server database and am providing access to the sql server db. My plan is to ...
3
votes
2answers
995 views

Save history editable data RDBMS

I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
3
votes
1answer
3k views

Import CSV File

Currently, we have a DTS package on SQL Server 2000 that imports a file called suicoweb.csv. The DTS csv file properties pages look like this. Here is a sample line from the CSV file ...
3
votes
1answer
913 views

Wrong behavior in Backups

Checking my Native backup this morning something wierd happened. I will describe the problem here. 1) The msdb table backup set shows me the backup was done successfully. 2) The errorlog shows me ...
3
votes
5answers
6k views

How to get the total days per month between two dates?

Given two dates like 20120302 and 20120605 I need to get a list of Months and the total days in those months that fall between those two dates, like so: March 28 April 30 May 31 June 03
3
votes
2answers
480 views

Distribution database compatibility level

Using SQL Server transactional replication with a distributor on 2008R2, and publishers/subscribers all on SQL 2005. Does the compatibility level of the distribution database matter? Should it be ...
3
votes
2answers
3k views

ALTER TABLE SWITCH statement failed

ALTER TABLE SWITCH statement failed because column does not have the same ANSI trimming semantics in tables... Does anyone knows how to fix that on already existing table full of data? Well, I've ...
3
votes
3answers
276 views

Is it possible to have 2 or more databases active, and synchronized between them?

edit I'm testing possible solutions for database redundancy and high availability,I tested mirroring it worked fine, but lacks scalability plus there will be a lot of work in one server. Now I ...
3
votes
3answers
665 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 ...
2
votes
2answers
84 views

Trigger to create a variable Table to be sent to service broker

I've setup a service broker for the first time and tested it... Looks good. Everytime a DML operation happens on a specific table; Let's say the Price Table, a trigger calls a stored procedure passing ...
2
votes
2answers
168 views

What SQL Server database version do I need?

I am not a database experts but part of the project I am currently interested in accepting requires to do a DB replication. The system will have many outlets... says Site A, Site B, Site C, etc.. ...
2
votes
4answers
485 views

Getting new and updated data with an archive column

I am trying to optimize an SSIS package that retrieves data from a very large transaction table that gets millions of new records daily. The data is being collected from several hundred field servers ...
2
votes
2answers
90 views

SQL Insert Statement Persists Two Records When I Need Just One

I have altered it to include hardcoded variables when I pulled this chunk of code from the stored procedure. I ran it independent of the application with the same result. I need one insert statement ...
2
votes
1answer
454 views

Need help with SQL Server PIVOT

I know about the PIVOT feature in SQL Server and I've read a few other questions and answers on here but I just can't seem to grasp it in order to do what I need. I'm trying to run some calculations ...
2
votes
1answer
211 views

MS Access + SQL Server for 1160 users - Is it worth it? (Please Help!) [closed]

Our school in Delhi (India) wants to develop a professional and robust in-house database (SIS) that should handle vast amount of student information, including admissions, registration, re-enrollment, ...
2
votes
2answers
1k views

Improve Performance -Update query with OR in WHERE

How do we improve this Update query Performance as currently it is taking 1 Minute to update 17643 Rows. Query -> Declare @date datetime = '09-Dec-2013' update #tmp_sp_abc set ...
2
votes
5answers
5k views

Autogrowth - Percentage Use?

Is it best not to use Percentage file growth for autogrowth settings? The below recommends using percent value growth for databases under 500GB but is this recommended? ...
2
votes
1answer
22k views

How to extract the last inserted row in SQL Server?

When an insert statement is executed one or more rows is inserted into the table, is there any way to extract the last inserted row in SQL Server?
2
votes
1answer
2k views

A network-related or instance-specific error occurred

I have been using a local database on my laptop for the last view weeks. But today I cannot access it. I get the error: A network-related or instance-specific error occurred while establishing ...
2
votes
1answer
4k views

scramble the value of one column and unscramble it?

I want to change all the values in one column, as it is a production database and I don't want to give the data to testers without obfuscation. Example: Name Name ---- --> ...