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)

0
votes
1answer
61 views

Harden a public login to only let it retrieve a list of other logins, and do nothing else

I'd like my app's login dialog to give users a drop-down-list of login names on the server. So I'm wondering about the most secure way to retrieve this. The best I can come up with is a login with a ...
2
votes
1answer
1k views

Can geography datatype Lat/Long properties be returned using a view in SQL Server 2008?

Hopefully the question is fairly self-explanatory. I've been ferreting through the, what appears to be quite minimal/poor microsoft documentation regarding both views and geography datatypes in the ...
0
votes
1answer
545 views

Drop & re-add subscribers to transactional replication

I'm trying to get Stored Procedures to execute on the subscribers, as seems to be best practice. I already have transactional replication running happily. I add a new sproc, and set properties of ...
3
votes
0answers
46 views

Any checklist/guide when transferring a database to a new server? [duplicate]

I'm looking for steps that I need to follow to transfer a database to a new server. So far I have: I restored the current database that I have in SQL Server 2005 instance to another SQL Server ...
5
votes
1answer
515 views

What is the limit on the number of INSERT statements you can paste into SSMS and run?

I am currently creating a stored procedure that populates a table with a list of insert statements so that my DBAs can export the contents of the table to a text file and then paste the statements ...
5
votes
1answer
60 views

Database Log file Backup

I was wondering if the Database Backup .bak Contain the Log file. i found that the space different between the database and backup is form the locked space for the database. but i didn't found any ...
3
votes
2answers
40k views

Windows could not start the SQL Server (mssqlserver) service on local computer error 17058

I'm working with virtual machine on the windows 8. There is a SQL Server 2008 on the virtual machine. And in the services; when I try to start the SQL Server service getting an error like this: Can ...
3
votes
1answer
3k views

Get identity value of all rows inserted

I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement? Example insert ...
4
votes
1answer
363 views

Why clustered index scan?

I just started to learn to optimize my queries and to analyze their query plans. I thought this query would generate nonclustered index seek + key lookup. SELECT ct.* FROM Person.ContactType AS ct ...
0
votes
1answer
459 views

SQL Server Reporting Services: How to check who accessed certain report?

I am in process of eliminating who should have access to SQL Server Reporting Services reports that I have created. Is there a way to find out who has accessed certain reports during past month? My ...
0
votes
0answers
379 views

Replication Monitor Missing Subscriptions in Watch List

In Replication Monitor, for one of our publications (transactional replication), none of the subscriptions are showing up in the Subscription Watch List. This is new behavior that just started ...
8
votes
2answers
4k views

What are the possible causes for sp_reset_connection taking a long time to execute?

Why would the sp_reset_connection system stored procedure be taking longer than a few milliseconds to execute, as viewed through SQL Server Profiler? I took a simple trace from a production system ...
6
votes
1answer
363 views

SQL Server 2008R2 minimum RAID/Disk config

I want to take advantage of the great price/performance offered by the new Intel E5-2620 (v2) machines being offered by some dedicated server webhosts. Unfortunately most of these E5-2620v2 dedicated ...
1
vote
1answer
348 views

Where can I check my permissions as SQL Server user?

Everytime I have to do a Restore from a backup, I have to log out from my personal user account from the SQL Server instanc and log back in using our main group user account, which I manage it too as ...
3
votes
1answer
121 views

Understanding SQL Server plan cache

I've been reading about the SQL Server plan cache and optimizing for adhoc workloads, etc. I'm fairly new to the topic and have a few general questions to help paint a better picture: Suppose I have ...
6
votes
2answers
516 views

Data Flow Task Produces No Rows with Declare Statement

I have a SSIS package that is doing a simple SELECT statement and then loading into a table. I have anonymized the sql statement which should be painfully obvious when you look at the table name. I ...
1
vote
1answer
785 views

How to isolate that network is issue of bad query performance [closed]

I would like to know how to isolate SQL Server bad query performance is caused by NETWORK and not the SQL Server. Are there any specific tips and tricks in SSMS, wait types, or specific PerfMon ...
6
votes
4answers
29k views

List table sizes for all tables on all databases

Is there a simple way of listing the size of every table in every database on a MSSQL server? I have used a query on sys.tables to get results for a single database, but we have >100 databases per ...
1
vote
0answers
98 views

What to look for in query execution plans? [closed]

Assuming the query execution is slow, I have collected the xml plan. What should I look for in the actual execution plan. What are the top 10 things I need to watch out for? I know this is a broad ...
3
votes
2answers
2k views

Missing Transaction log in chain, Possible to skip

We have had one of our clients SQL Servers crash and need to restore it to the most recent possible time. We have a full backup from Monday and transaction logs that run every hour up until this ...
4
votes
1answer
2k views

SQL Server 2008 R2 Upgrade (Express to Standard) fails. No “Select Instance”

Hello there, i got stuck in the progress of upgrading 3 systems from SQL Server 2008 R2 Express Edition --> SQL Server 2008 R2 Standard Edition Problem: The Option "Select Instance" in the ...
4
votes
2answers
3k views

How can I change the number of @schedule_uid=N'????' to re-create a SQL Server Agent Job?

I have a SQL Server 2005 job in the SQL-Server Agent Jobs that I need to move/transfer to another updated SQL Server 2008 R2 instance. I was able to script it out and then use it as a new query window ...
1
vote
1answer
10k views

Lazy Writes Performance Issues

My SQL Server sometimes reports high Lazy Writes e.g. "Lazy Writes per second is 119 Writes/sec" What should I be checking to find out why this is high? What are potential causes? Thanks.
2
votes
3answers
2k views

Display table names in titles in SQL Server Management Studio

Does anyone know how to get SQL Server Management Studio to display the Table names in the Title. Right now they are next to useless and every time I need to look at a table I have to reopen it. ...
1
vote
1answer
113 views

Multi tenancy by schema - Deny access to another tenants [closed]

In a multi tenant environment, where the multiple tenants are in the same database and a schema is used to separate each one of them, how can I create a database user (MS SQL SERVER) for each schema, ...
5
votes
1answer
853 views

SQL Server 2012 Publisher SQL Server 2008 Subscriber version issue

So I have a setup where one virtual machine is a SQL Server 2012 publisher and I need to setup merge replication on a SQL Server 2008 subscriber on a different vm. When setting up the subscription ...
2
votes
1answer
645 views

XML queries with case insensitive tag- which is the best query

I had asked question about XML queries with case insensitive tag earlier and i found a solution too. But i also found some other solution. So table was something like DECLARE @myTable TABLE ( yourXML ...
2
votes
3answers
14k views

How to move a SQL Agent Job to another Server?

I'm moving from SQL Server 2005 to SQL Server 2008R2, and I see there is one job in the SQL Server Agent Jobs that I need to move but I see it also in the SSIS---> Stored Packages\MSDB\File ...
7
votes
1answer
298 views

Moving primary key constraint from one index to another

I have a SQL Server database, and have run sp_blitz against it. This pulled out that there are a couple of heaps on moderate size tables (a few hundred thousand rows in one case). Most of these ...
1
vote
0answers
576 views

CDC with SSIS: process all changes in order of occurrence

I have a SSIS package that I'm using to sync (in an on going basis) all changes from one table to another. Note that I need to sync ALL changes and not just NET changes. My problem is that I cannot ...
3
votes
1answer
167 views

How to archive a transactional table on another server?

(Feel free to tell me the correct name for this... it is a common scenario!) We have a production SQL Server 2008 box. One table is heavily transactional. It is also purged every few days (old ...
0
votes
1answer
420 views

Memory is not released after the execution stored procedure followed by a process end

I am running a stored procedure in query builder and closing query builder. However, the memory allocated doesn't get released. The stored procedure uses temp tables and drops them immediately after ...
0
votes
0answers
477 views

Database backuped, but “Last database backup” property shows bad date

I have this procedure below to backup database(procedure is in backuped database). Procedure is called by external program. Procedure runs fine, backups are created. But when I want to find out last ...
3
votes
1answer
782 views

Utilizing a single core to 100% doesn't show in perf mon as single cpu being fully utilized

Ever since Windows 2008 I've noticed the following behavior: I'm using a windows 2008R2 VM with two vCPUs but this also works on a physical server. I'm using SQL server 2008 R2 for this example. ...
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 ...
3
votes
2answers
1k views

XML queries with case insensitive tag

I have a requirement where i want XML tags to be case insensitive in my xml queries. For example: DECLARE @myTable TABLE ( yourXML XML ) INSERT INTO @myTable SELECT ...
1
vote
1answer
1k views

Prefix full text search with numbers in SQL Server

If I create the following table in SQL Server 2008 R2: CREATE TABLE test( id BIGINT IDENTITY NOT NULL, value VARCHAR(10), CONSTRAINT test_pk PRIMARY KEY (id) ); CREATE FULLTEXT CATALOG ...
2
votes
1answer
205 views

Can SQL Server 2008 R2 get email name resolution from Office 365?

We just moved from an on premise Exchange server to Office 365's Exchange Online. My question is, is there any way to still get email name resolution from Office 365? I have many stored procedures ...
0
votes
0answers
112 views

SQL Server Reporting Services Upgrade/Migration

I couldn't find any documentation on this. I'm currently doing a dry run of SSRS 2005 to SSRS 2008 R2 upgrade/migration. I've backed everything up (encryption key and configuration files) and did a ...
18
votes
1answer
408 views

Weird SQL Server instance crash on casting to numeric

While working with C# Entity Framework I noticed a crash of my SQL Server instance. I was able to track it down to this statement: SELECT * FROM dbo.[TestTable] where mpnr in (1099059904, ...
4
votes
2answers
262 views

Minimum memory required for SQL Server and database [closed]

I use SQL Server 2008 R2 Enterprise on my server. The minimum required memory for installing SQL Server is 4GB. I have several databases on my server. How can I determine the minimum required RAM on ...
-2
votes
1answer
240 views

Maximum dataset size for a well-performing SQL Server 2012? [closed]

We're planning a project using SQL Server 2012 for storing about a billion records over less than 50 fields. Assuming good hardware, is SQL Server up to that task? Roughly how many request/second can ...
8
votes
1answer
2k views

How accurate is the sys.partition.rows column?

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...
1
vote
1answer
4k views

SQL Server 2012 Express : How do you limit a database's size and retention policy?

I don't work with databases very often and I want to make sure I am going about this the right way. I want to configure a database's file size to not exceed 9 gigs. At the point the database reaches ...
1
vote
1answer
72 views

Patterns/Designs to help with concurrency/performance [closed]

I'm working on a SQL Server 2008 R2 DB and I'm experiencing some performance/concurrency issues with probably the main table in the DB. Problems like slow reads, deadlocks, poor performance in ...
0
votes
0answers
33 views

SQL Server Problems [closed]

I have recently installed SQL Server 2012. I have the Management Studio and the configuration manager. When I open the Management Studio, I have no object to connect to though. I have no SQLExpress to ...
14
votes
2answers
3k views

How to move a database from SQL Server 2012 to SQL Server 2005

What are my options if I need to move a database from SQL Server 2012(32bit) to SQL Server 2005(64bit)? I know I cannot: restore a backup of the database on SQL Server 2005 detach & attach I ...
2
votes
1answer
190 views

SQL Server data files and RAID

I have a server with a RAID1 volume (OS + apps) and a RAID10 volume (database). The RAID10 volume has 6 physical disks. Should I split the SQL Server database into 6 datafiles (one for each physical ...
10
votes
1answer
830 views

In case of multiple updates of the same record in a single transaction, how many versions are being stored?

According to MSDN article by Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation "... All the earlier versions of a particular record are chained in a linked ...
6
votes
1answer
480 views

SQL Server: How to get the responsiveness of a trigger, without blocking the transaction that fires the trigger?

On a thread today I posed a desire to get code to execute immediately when a record is inserted rather than resorting to polling. Triggers are powerful tools, yet dangerous places to put real code, ...