SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

8
votes
4answers
21k views

Get Job id OR Job name from within executing job

I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at ...
7
votes
1answer
3k views

How to migrate between schemas in bulk in SQL Server?

We currently have multiple databases but would like to combine them and, instead, separate our domain contexts using schemas. In MS SQL Server 2008 R2, how can I relocate all the contents of one ...
6
votes
2answers
828 views

Does .bak file also contain backup of ldf file?

I am running MS SQL Server, which is holding mdf and ldf files of sizes 110 GB and 245 GB respectively. My question is, if I take .bak backup of said database, will it result in 355 GB (110+245) ...
5
votes
2answers
5k views

Best value for fill factor in index

I use SQL Server 2008 R2 and want to set value for fill factor property in each index. I look for recipe to calculate best value for this property (have better performance when insert record) . Also ...
4
votes
1answer
2k views

Execution Plans Differ for the Same SQL Statement

As a background to this question the details can be found at this link. In a nutshell: I have 2 SQL statements that are almost identical except for the RIGHT table. The LEFT JOIN is the same and the ...
3
votes
1answer
19k views

How to find which tables and views a user has access to?

We have a SQL Server 2008 database and have restricted all tables and views away from a specific user ID. Over the years we have granted back tables and views one at a time based on user need. We ...
3
votes
2answers
39k views

Cannot connect to my local instance

When I connect to my local server, I get this message A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not ...
2
votes
3answers
5k views

How can I reduce the Initial Size of a LOG file?

I've inherited a SQL Server 2008r2 database; the Data file was created with an initial size of 1,000 mb, but the Transaction Log has an initial size of 28,016 mb. No backups were being performed. I'...
22
votes
1answer
17k views

SLEEP_TASK Wait Type in SQL Server - What does it indicate?

I haven't seen the SLEEP_TASK wait type before, and today I seem to be getting a ton of them. I'm not the official DBA, just a SQL Server developer who knows some DBA stuff. We upgraded our servers ...
10
votes
1answer
952 views

Determine the SQL Server version and edition from the available set up, before actually installing it?

We are going through a downgrade process from SQL Server 2008 R2 Enterprise to Standard edition. To kick off the above activity, we were looking for available Standard Edition of SQL Server. We found ...
7
votes
1answer
1k views

Why does the order of parameters matter for sp_trace_create?

The first batch of the following script calls stored procedure sp_trace_create with parameters in documentation order; the second batch swaps the positions of parameters @tracefile and @options: ...
6
votes
1answer
24k views

QUERY - pivot multiple columns, variable number of rows

I have a table that looks like this: RECIPE VERSION_ID INGREDIENT PERCENTAGE 4000 100 Ing_1 23,0 4000 100 Ing_100 0,1 4000 200 ...
5
votes
1answer
2k views

Mitigate negative effects of shrinking

Closely related to this question from a few weeks ago, but the answers didn't discuss how to do the dirty deed. Here's the situation: We now 15 GB data in a database, but a logging bug in an ...
5
votes
6answers
3k views

Backup very large table

I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
5
votes
1answer
4k views

Attaching a database in SSMS gives error

I am accessing SQL Server using SSMS, and a user with adequate permissions. When this user tries to attach a database by using SSMS (by right clicking on database node and then clicking on attach), ...
5
votes
1answer
561 views

Disabling SET Statements / Forcing SET Options in SQL Server 2008R2/2012

I am currently working with a MSSQL 2008R2 Database which uses the following SET-Commands frequently: SET ANSI_NULLS { ON | OFF } SET QUOTED_IDENTIFIER { ON | OFF } SET ANSI_PADDING { ON | OFF } ...
5
votes
2answers
185 views

ProcessID Owner with third party app

I am having problems trying to audit a third party app with a SQLServer backend. The app manages users and user access via a SQL table, using a single SQL login to access the databases. I am trying ...
5
votes
5answers
4k 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 ...
5
votes
1answer
12k views

How to keep the structure of the Tablix when there is no data to show

We have a SSRS report which shows the result of a dataset in a tablix. If the query in the dataset returns no data, the header of the Tablix gets displayed but not the tablix cells below the header. I ...
5
votes
1answer
293 views

Is it “legal” to CREATE and DROP #SomeTable more than once?

I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this: CREATE TABLE #WidgetSetting (...
4
votes
1answer
883 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
115 views

Is there a way to grant SELECT for every database (including new ones) at a server level?

I have a unique (well, I think it is unique) situation where databases (yes, entire databases) can be created more or less dynamically. Ignoring the obvious issue with that, I need to be able to ...
4
votes
1answer
10k views

Join tables in two databases make query slow? is better to partition db?

I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2. If I make a select query joining both tables, it's really slow to get the results. A simple query like SELECT * FROM db1.dbo.table1 ...
4
votes
2answers
3k views

How do I rescue SQL Agent jobs from a set of detached master, model, mdb files?

I had a MSSQL (MSSQL 2008 R2) cluster that failed in a way that I couldn't bring it back up (long story, servers ended up in BSOD loops, I'm blaming Broadcom and myself). I've SQL backups, and even ...
4
votes
5answers
30k views

Convert varchar column to datetime

In SQL Server, I have a varchar column with value such as: 07/May/2012:00:06:22 07/May/2012:00:06:22 07/May/2012:00:06:22 07/May/2012:00:06:22 07/May/2012:00:06:22 QUESTIONS How do I convert ...
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 ...
3
votes
4answers
7k views

Can I force a user to use WITH NOLOCK?

Can I force a user's queries to always run with the hint NOLOCK? e.g. they type select * from customer But what is executed on the server is select * from customer with (nolock) This question is ...
3
votes
3answers
2k 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 cast(left(cast(...
3
votes
1answer
2k views

Multi-tenant database using SQL Server 2008?

I am designing a database using a shared schema containing the "security views" to which all logged tenants will be directed. I read the msdn article on Multi-tenant Data Architecture. I understand ...
3
votes
1answer
853 views

Searching SSIS packages for the use of specific tables

I have a number of SSIS packages, how can I search them to find the use of specific tables? I need to make tables changes and I am trying to assess the impact of these.
3
votes
1answer
24k views

distributed queries are configured to run in single-threaded apartment mode

I have sql server 2008 R2 64bit Developer's Edition Installed on my machine. And Microsoft Office 2010 Professional 32bit. I have been trying to import some Excel data from an Excel sheet into a sql ...
3
votes
1answer
1k 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
10k views

Dynamic Number of Columns in Pivot Table

I have a table to store StudentID, SubjectID, ObtainedMarks. Now I have to create a pivot table to show studentID and obtainedMarks in different subject. Number of subject may differ. I could generate ...
3
votes
1answer
28k views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed

As the title states, I'm getting the following error when attempting to use a ASP.NET webapp: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed ...
2
votes
1answer
58 views

Large update taking too long; looks like it is updating more records than I have

I am trying to do an update on a table that has 4 million records. The main table is indexed on SerNum a unique field. I use a temp table to update from that also has a unique index on SerNum. I am ...
2
votes
3answers
754 views

Should I shrink my database?

I have a 1.2Tb SQL Server 2008 R2 database which is more than 50% empty, because I migrated a lot of functionality to PostgreSql. The database is still in use, and still somewhat growing, but I do not ...
2
votes
2answers
161 views

force to forget logins

How can I force SSMS to forget the recently used logins? Of course I use "SQL Server Authentication" with Login/Password fields. (It get's annoying, as everytime I open SSMS it shows the "Connect to ...
1
vote
1answer
2k views

Unable to drop non-PK index because it is referenced in a foreign key constraint

I have a table named MyTable. The primary key is an identity int column named MyTableID. There is a unique clustered index on the PK column MyTableID named PK_MyTable. I noticed there is an ...
1
vote
2answers
13k views

Sql Server agent can not start

I can not start Sql Server agent to create job. Can anyone help me? . I'm using Windows 7 Ultimate , Sql server 2008 Rc2 See this images:
1
vote
1answer
3k views

Strategy for Repair Database on SQL Server 2008 R2 Standard

(one guy pushed green button instead of blue, three racks left as was his original target, probably green is nicer then blue, on this SAN are saved database files) after this accident I'm run ...
23
votes
3answers
35k views

Execution Plan Basics — Hash Match Confusion

I am starting to learn execution plans and am confused about how exactly a hash match works and why it would be used in a simple join: select Posts.Title, Users.DisplayName From Posts JOIN Users on ...
16
votes
9answers
4k views

Version control for database objects

The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be ...
31
votes
5answers
6k views

Why is there still a varchar data type?

Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem) After working with ...
17
votes
1answer
10k views

How to write a query which finds all circular references when a table references itself?

I have the following schema (names changed), which I cannot change: CREATE TABLE MyTable ( Id INT NOT NULL PRIMARY KEY, ParentId INT NOT NULL ); ALTER TABLE MyTable ADD FOREIGN KEY (ParentId)...
15
votes
2answers
6k views

Changing a column from NOT NULL to NULL - What's going on under the hood?

We have a table with 2.3B rows in it. We'd like to change a column from NOT NULL to NULL. The column is contained in one index (not the clustered or PK index). The data type isn't changing (it's an ...
14
votes
4answers
10k views

SQL Server database on an SSD - any advantage to a separate file for every table?

I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
14
votes
1answer
24k views

Parent-Child Tree Hierarchical ORDER

I have to following data in SQL Server 2008 R2. SQLFiddle Schema: CREATE TABLE [dbo].[ICFilters]( [ICFilterID] [int] IDENTITY(1,1) NOT NULL, [ParentID] [int] NOT NULL DEFAULT 0, [...
16
votes
2answers
6k views

Restore database excluding FILESTREAM data

Context We are developing a system with a large-ish database in the bottom. It is an MS SQL database running on SQL Server 2008 R2. The total size of the database is about 12 GB. Out of these, ...
15
votes
2answers
6k views

Can you explain this execution plan?

I was researching something else when I came across this thing. I was generating test tables with some data in it and running different queries to find out how different ways to write queries affects ...
14
votes
3answers
3k views

How often to backup the master database?

The suggestion in BOL is fairly vague: Back up master as often as necessary to protect the data sufficiently for your business needs. We recommend a regular backup schedule, which you can ...