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

learn more… | top users | synonyms (2)

6
votes
2answers
372 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) ...
6
votes
1answer
2k 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
854 views

Is there a limit to how many credentials SSMS can remember?

Using SQL Server Management Studio that came with SQL Server 2008 R2, I make connections to the same server using a number of different sets of SQL Server Authentication credentials. On my development ...
6
votes
3answers
16k 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 ...
4
votes
1answer
1k 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 ...
2
votes
1answer
148 views

how to get the information about sleeping processes that are causing blocking?

I have a procedure that allows me to see what is currently running in my sql server. This procedure does not use sys.sysprocess because it has been deprecated. However, I am struggling to find ...
2
votes
1answer
16k 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 ...
1
vote
1answer
34 views

Trace flag is running, but not message when deadlock happens

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior More information on the trace flags are here DBCC TRACESTATUS this above, is ...
21
votes
1answer
14k 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 ...
8
votes
5answers
15k views

Add article to transactional publication without generating new snapshot

Using SQL 2008 R2 transactional replication with pull subscribers, when we add an article, I'd like to avoid having to create an entire snapshot (the db is ~80 GB, so this takes hours). From this ...
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: ...
5
votes
1answer
489 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
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
1k 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
2answers
184 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 ...
4
votes
2answers
107 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
2answers
23k views

How can I dynamically alias columns?

I have a table (not designed by me) which has 20 variably named columns. That is, depending on what type of record you are looking at, the applicable name of the column can change. The possible ...
4
votes
1answer
10k 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 ...
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
767 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
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
1answer
945 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 ...
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
1answer
998 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
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
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
1answer
1k 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
2answers
664 views

Performance gain by moving a large table from a database and referencing with a synonym

We have SQL Server 2008 R2 database which is currently about 80Gb. About 10Gb of this is down to one table, and I wondered if there would be a performance gain if I moved this table into it's own ...
2
votes
3answers
1k 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. ...
2
votes
1answer
50 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
2answers
170 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
1answer
24k 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
2answers
151 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 ...
2
votes
2answers
5k views

Attach SQL Server 2008 R2 on SQL Server Express 2008

I have a database on SQL Server 2008 R2. I want to attach it on a SQL Server Express 2008 instance running on Windows 2003. I set the SQL server compatibility level on the database to SQL Server ...
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
1answer
11k views

How to fix “The OLE DB provider ”MSOLAP“ has not been registered”?

Here's the code I'm using to register a linked server to SSAS: USE [master]; GO EXEC master.dbo.sp_addlinkedserver @server = N'ServerAlias', @srvproduct = N'', @provider= N'MSOLAP', ...
1
vote
2answers
10k 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:
-3
votes
2answers
5k views

Microsoft recommends using Rowversion over timestamp , but i can not find “rowversion” data type inside my sql server 2008 R2 [closed]

MS on this link http://msdn.microsoft.com/en-us/library/ms182776.aspx mentioned that we should use rowversion data type over Timestamps. Now inside my sql server 2008 R2 I want to create a column with ...
16
votes
9answers
3k 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 ...
22
votes
3answers
28k 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 ...
15
votes
1answer
8k 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 ...
14
votes
3answers
5k views

SQL Server database synchronization

Problem definition Our users need the ability to query a database that is mostly up to date. The data can be stale up to 24 hours and that is acceptable. What would be the lowest cost approach to ...
13
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 ...
15
votes
2answers
5k 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 ...
13
votes
2answers
2k 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 ...
13
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 ...