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)

8
votes
2answers
10k views

Transaction and Try-catch in SQL Server Job

We have DML operations in each step of a SQL Server job. To ensure the update/insert will be rolled back in case something goes wrong, I have wrapped the data modifications of each step in TRY CATCH ...
8
votes
3answers
764 views

Create function in central database or repeat in each database?

One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database ...
8
votes
1answer
714 views

Why is the page life expectancy 0 (zero) on SQL Server 2005

We have been wanting to know the page life expectancy for our SQL Server. So we look at the counter using Perform. The value is ZERO and never changes. I thought there must be some bug, so I checked ...
8
votes
3answers
8k views

Performance Hit using CAST() in TSQL (SQL Server)?

We have a SQL generator that emits SQL conditional statements generically for specified fields (which for the sake of discussion: we will label as myField). So, if myField is of type NVARCHAR, we ...
8
votes
5answers
1k views

Does SQL Sentry Plan Explorer work?

Does SQL Sentry Plan Explorer work as advertised and is it legitimate? Are there any gotchas or something to be concerned about? It looks like it shows the hot path in color commpared to SSMS's ...
8
votes
6answers
5k views

Pros and Cons of SQL Server back up strategies and their appropriate usage scenarios

From what I can tell, there are three possible ways of backing up your SQL Server database Full backup Differential backup Log Shipping What are the pros and cons of each strategy and in what ...
7
votes
2answers
90 views

Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?

I was hoping to get a granular view of which database files contained which allocation units for the various HoBTs (both aligned and non-aligned) living in a database. The query I've always used (see ...
7
votes
2answers
372 views

Average of last 4 runs of a product

I need to show the average weight of a product over its last 4 production runs. I'm not sure how best to describe it other than an example: Let's imagine I have the following table that lists a ...
7
votes
6answers
918 views

Fast reads without paying the price on writes

We have a large append-only table containing financial transactions. On average 1000 transactions are inserted per minute. Since there are now more and more usecases where we actually want to read, ...
7
votes
2answers
5k views

An efficient way to compare two large data sets in SQL

Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations. The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and ...
7
votes
2answers
243 views

'Cleanse' a SQL Server database file created with Instant File Initialization enabled?

I have Instant File Initialization enabled on our SQL Server, so the 'empty' part of the database file is not zeroed before being allocated. My understanding is that this means that the file could ...
7
votes
1answer
489 views

Strange behaviour DBCC Shrinkfile

I am attempting to run a dbcc shrinkfile in chunks of 1GB against a database where 95% of the data has been archived and deleted. I'm leaft with a 235GB file where 9GB is data/indexes. I want to ...
7
votes
1answer
11k views

Why does CREATE INDEX … WITH ONLINE=ON block access to the table over a period of minutes?

I have an existing table: CREATE TABLE dbo.ProofDetails ( ProofDetailsID int NOT NULL CONSTRAINT PK_ProofDetails PRIMARY KEY CLUSTERED IDENTITY(1,1) , ProofID int NULL , ...
7
votes
1answer
772 views

How much memory is a table taking up?

Is there a way to find out how much memory a table is taking up in SQL Server (2005 and greater)? For example, suppose I have a table with 30 million records. I'd like to know how many pages ...
7
votes
2answers
12k views

SQL 2008 R2 creates user/schema when Windows user creates tables

We added a server login and database user that map a Windows Group to a SQL 2008 R2 instance using the following script, with the names changed for anonymity: USE master go CREATE LOGIN ...
7
votes
4answers
2k views

Slow temp table drops in sql 2005

I've run into a problem on our production sql server where temp table objects take a long time to drop (obvious when small and synchronous drop is used). I can't reproduce this on other sql servers ...
6
votes
2answers
341 views

Insert only deadlocks

We have an application inserting data into a table. Unfortunately, we are getting deadlocks and the deadlocks are coming from inserts only. We are seeing the inserts take key locks in a different ...
6
votes
1answer
91 views

Giving server-role to login breaks stored procedures

I have a login called user1. user1 can execute storedprocedure [Test].[SP1] EXEC [Test].[TestSortation] EXEC [TestSortation] Both of the above commands work. Now if I give the user1 the server ...
6
votes
3answers
11k views

How to export all the security-related information from a SQL Server database

As you guys probably know, SQL Server does not provide an out-of-the-box solution to export all the security related statements that are declared for each element in the database (both at the database ...
6
votes
2answers
7k views

In SQL Server, how do read locks work?

Suppose I have the following long-running query UPDATE [Table1] SET [Col1] = 'some value' WHERE [Col2] -- some clause which selects thousands of rows and suppose the following query is executed ...
6
votes
2answers
3k views

Linked Server Risks

I'm implementing a new feature which requires data from databases on multiple servers. I just need to union data from all these servers and sort it. The two options that come to mind are: Use linked ...
6
votes
2answers
36k 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' ...
6
votes
3answers
3k views

Can I get SSMS to show me the Actual query costs in the Execution plan pane?

I'm fixing performance issues on a multistatement stored procedure in SQL Server. I want to know which part(s) I should spend time on. I understand from How do I read Query Cost, and is it always a ...
6
votes
2answers
1k views

Parameter Sniffing: Why Does This Become An Issue?

Today, I had an issue with a SPROC Timing Out (took longer than 30 seconds) when it was run from an ASP.NET webpage, but executed quickly when run from SSMS (took 5 seconds). After suspecting ...
6
votes
1answer
9k views

How can I monitor the call count per stored procedure per timespan?

In order to diagnose some performance issues I would like to get a better understanding of the number of times certain procedures are called compared to the system performance. Is there a way to get ...
5
votes
2answers
469 views

Should we create a schema per “project”?

This is a database where users create a 'project' and work on it. We have a table for project and various other tables that contain different attributes for project (multiple rows for each project ...
5
votes
2answers
276 views

Copy data from multiple databases to a central database for reporting and analysis

I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting ...
5
votes
4answers
28k views

Displaying the list of all tables in all database

How can I display a list containing all the tables of all the database in a SQL Server 2008 R2 server?
5
votes
2answers
13k views

Separate stored procedures for inserts and updates?

I have a table in Microsoft SQL Server. Sometimes I need to update, and sometimes I need to insert. I could write 2 stored procedures: InsertNewPerson UpdatePertsonById But I was thinking to write ...
5
votes
3answers
1k views

How to store many smallish time-series in a relational DB?

Input Data Multiple Tests Beds generate measurement data of various complexity. In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few ...
5
votes
4answers
3k views

CTE Running in Infinite Loop

My CTE runs in an infinite loop for a specific customer and I am not able to find out why. Here is the query: ;WITH ClassTree AS (SELECT ID, NAME, Parent_ID FROM ...
4
votes
3answers
95 views

Collect data with central management servers and keep them in local table

I have 50 servers. I manage them from a central management server on SQL Server 2014 Enterprise Edition. I don't use repository. I could open a query and get result for each one at the same time. But ...
4
votes
1answer
329 views

Launching SQL Agent Job from stored procedure not on [msdb] database, or, How Necessary is SET TRUSTWORTHY ON?

(With apologies in advance since I've been asked to pick up a project written by someone with more skills than me but who I can no longer ping for questions. If it sounds like I may be using ...
4
votes
1answer
3k views

Storing Japanese characters in a table

I am using SQL Server 2008 R2 and I want to store Japanese characters in one of the columns of my table. Say I want to store Japanese names, how can I achieve that? Is there an easy way?
4
votes
1answer
342 views

How do I create a partition using a bit field in SQL Server 2014

I have a table where data is archived. I need to keep this data as it is rarely viewed. Instead of moving archived data to a separate table, I wanted to create a partition on the Archived field. When ...
4
votes
1answer
116 views

Proving the usefulness of a specific index

I'm looking for a way to prove that some recently added indexes are positively impacting a production system. I've collected some dmv statistics to show that the indexes are indeed being used ...
4
votes
3answers
7k views

how to create backup for stored procedure,trigger and functions of a database in sql server 2008?

I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in ...
4
votes
2answers
9k views

Publishing database changes with SSDT that include altering data types of columns

I have a SQL Server Data Tools (VS2012) project that's published automatically during the build process. A column was recently updated from an int to decimal(18,4). As a result of this change, the ...
4
votes
2answers
220 views

BACKUP LOG cannot be performed because there is no current database backup

I have a SQL Server 2005 database using the full recovery model. If I execute the following commands: ALTER DATABASE test SET RECOVERY SIMPLE; ALTER DATABASE test SET RECOVERY FULL; or BACKUP LOG ...
4
votes
1answer
1k views

Running functions in parallel

This is for SQL Server 2012. We have some import processes for FTP files that are picked up and read into a staging table, from there we massage/check the data before moving into production. One of ...
4
votes
2answers
3k views

Can I optimize this MERGE statement?

I am attempting a single column merge between two tables. The first table (VisitorSession) has 40,000,000 rows. The second (ShoppingCart) has 9,000,000 rows. In my development environment, the query ...
4
votes
2answers
28k views

How to Find the Foreign Key Associated with a Given Primary Key

All, I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via SELECT * FROM ...
4
votes
1answer
3k views

Generate insert statements for table AND related data

I'm looking to move data from tables and related tables between 2 databases. The trouble is the primary keys are out of sync. Is anyone aware of a tool which can generate insert statements for a ...
3
votes
1answer
2k views

Multiple IPs registered for listener when using AlwaysOn AGs w/ multi-subnet clustering

We are testing AlwaysOn Availability Groups on a WSFC multi-subnet cluster and using dynamic DNS registration. Our issue is that sometimes the IP for the Availability Group Listener (AGL) in the ...
3
votes
2answers
741 views

How to identify unused databases on my sql server

I am looking for the best practices or analysis someone would have followed to identify the unused or inactive databases on my SQL Server. I went through many articles, which say possibly to go for ...
3
votes
1answer
3k views

Why dropping all foreign keys from a script is not working? [closed]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
3
votes
1answer
2k views

CREATE DATABASE permission denied for sysadmin

I'm attempting to create a stored procedure that can be used as a proxy for creating databases where users do not have rights for creating databases. See Restricting Database Access/Functions to User ...
3
votes
2answers
5k views

App domain is marked for unload due to memory pressure

In my sql server log (2005 SP4) I have this message every couple of seconds : "AppDomain XXX [DataBase.dbo[runtime], YYY] is marked for unload due to memory pressure" Our server : Windows Server ...
3
votes
4answers
2k views

Working Linked Server Query fails in sp_send_dbmail

Take the following example: EXEC msdb.dbo.sp_send_dbmail @recipients = 'me@whatever.co.uk' , @query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' , @attach_query_result_as_file = N'True' ...
3
votes
2answers
4k views

Partitioning on a single filegroup

I have some very large tables in my database, but a substantial chunk of this data is "old". Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other ...