Tagged Questions

This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx

learn more… | top users | synonyms

1
vote
2answers
300 views

Clustered vs Nonclustered Index

My database currently has a primary Key/Clustered index on the ID column for each table. However, the application that connects to the database is always looking at views defined as: SELECT * FROM ...
2
votes
1answer
3k views

How Should I Configure Memory for Multiple Instances of SQL Server?

We have a set of SQL Server instances (2008 R2) that run on the same virtual server. Each instance represents a stage in our development cycle (Dev/Test/Stage/etc.). The instances all need to be ...
0
votes
2answers
141 views

Server side trace to database

How can I set up a server side trace that dumps to a database instead of a trace file? I set-up and tested a trace configuration I want with Sql Server Profiler that dumps only needed info straight ...
1
vote
3answers
132 views

Proper way to export database to other servers

I am using SSMS (I am a software developer, not a DBA) and in each application release I need to release the database with its tables (some with data some with scheme only). I tried doing 3 things ...
2
votes
2answers
160 views

Moving one TempdB on 3 instance server

I have a large virtual SQL Server (Full 2008R2). I run 3 SQL instances and would like to relocate the TempdB database file to another location, splitting off from the TempdB log file. The trouble I ...
2
votes
1answer
3k views

Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box

We currently have a SQL Server 2005 instance hosting a lot of application specific databases (ArchiveManager, SolarWinds, Kaspersky etc). We are planning to deploy a new SQL Server 2008 R2 instance on ...
0
votes
1answer
38 views

Can I rely on the order of colums on an index generated by Database tuning advisor

I got the following recommendation on a table: CREATE NONCLUSTERED INDEX ...
0
votes
1answer
184 views

viewing the population? (sql server full text search)

I'm thinking that when we are working on full text search, we might want to view how sql server FT engine breaks the data and indexes it. For my case the column being indexed are simple nvarchar ...
2
votes
1answer
596 views

Moving DBs (including System DBs) to new LUNs on a single node cluster

We have a single node cluster (SQL2K8 R2) currently utilizing storage on our old SAN. We'd like to migrate to storage on the new SAN without rebuilding or moving the user DB. The infrastructure ...
0
votes
1answer
275 views

BUILTIN\Administrators allowing access via linked server but not locally

We have two servers: LYNDB01 & LYNDB02 On LYNDB01 we have a database called db_ExampleDatabase We have created a Linked Server on LYNDB02 to LYNDB01 using no mappings and the "Be made using the ...
3
votes
2answers
1k views

Reducing high VLF count

I have been using Brent Ozar's sp_blitz script to review our database setup and have found that the VLF count is high (13562!) on the primary DB since changing to Full recovery model. I ran a full ...
3
votes
1answer
623 views

How to Return XML Node Ordinal, or delete node based on element value?

I have an XML document similar to this: <Root> <Sub> <Record> <Guid>aslkjflaksjflkasjfkljsd</Guid> </Record> <Record> ...
2
votes
1answer
185 views

Updating column in underlying table causes massive transaction log growth

I have an underlying table ~14k records and an indexed view that contains ~11 million records. When updating a column in the small underlying table this seems to cause a massive transaction log entry ...
0
votes
1answer
184 views

Object name 'Clean Up History' in use when trying to complete the maintenance plan wizard in SQL Server 2008 R2

I am trying to create a maintenance plan on a instance running SQL Server 2008 R2 SP1 (no CU's installed). When completing the wizard I get the following error: The object name cannot be changed ...
3
votes
4answers
1k 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 ...
-1
votes
1answer
444 views

dead lock when updating [closed]

Update Operation.TrTable Set RecordId = RecordTABLE.newRecordId From Operation.TrTable tr Inner Join ( SELECT r.Id AS newRecordId, r.KeyM AS MappingKey From ...
2
votes
1answer
764 views

Database locked

I canceled a query which took too long time. After that, the SQL-Server database was locked. After I restarted SQL Server, the mode of my database was "in recovery". Now I can't access my database ...
1
vote
0answers
1k views

connect sql server using windows authentication in another pc

I am having sql server 2008 r2 express installed in a PC. I have some other PC which are connected to this pc in a local network.Now those PC connect to my pc's sql server using sql authentication. ...
1
vote
1answer
310 views

Moving model database

I have moved the model database by using following method For each file to be moved, run the following statement. ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = ...
3
votes
2answers
1k views

sql server full text search - fuzzy searching?

Is there a way to configure fuzzy searches in sql server full text search. Meaning if I search for a term called POWDER, I must get matches (i.e. strings) which contain any variations of it within an ...
0
votes
1answer
124 views

How to deny access to see the logins in a database

I create a login in SQL Server 2008 R2, and when that user connects to a database, it can see all the logins in the security logins. Can I deny access to watch the logins of the database?
3
votes
1answer
287 views

While restoring copy of SQL Server 2008 backup file in SQL Server 2008 R2 degraded performance

I am trying to upgrade our SQL Server version from SQL Server 2008 to SQL Server 2008 R2. But when I restored a SQL Server 2008 backup file in SQL Server 2008 R2, it's significantly degraded the ...
0
votes
4answers
130 views

Shortest method to upload database to server

I have a SQL Server script file which ranges size of 3gb and I am in need to upload the script file to an external server. i generated script for my 6gb database by following these steps ...
1
vote
1answer
2k views

Changed server name, now maintenance plan fails

I am using SQL Server 2008 R2. When I try to execute a maintenance plan I get the following error: Message [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user ...
0
votes
1answer
407 views

SSIS Raw Data Import Into SQL Server 2008 R2 - Truncation and Condensed Data Type

[Intro] I have written a two pack SSIS solution to perform a scheduled hourly update of data from multiple tables in a single db (SRC) to single table on another db (TRG). I get the following errors ...
4
votes
3answers
751 views

How to add additional hard drive(s) to existing database

I have a database that is close to filling the hard drive that it lives on. I'd like to expand the database on to another hard drive to deal with this. I see there's a Files option in the database ...
3
votes
1answer
342 views

SSIS Design consideration with object variables

I have an SSIS package for SQL Server 2008 R2. In the package I am required to work with an Access database (one to many) and iterate through it running validation checks with the data. The validation ...
1
vote
2answers
205 views

SQL Server database backup on Remote site

We have 4 different sites and each site has a SQL Server. our requirement is to have a SQL Server backup or database(Mdf files) of site 1,2,3 in site 4. In case of disaster on any of the site we ...
4
votes
1answer
141 views

How to reinsert corrected rows from the conflict table?

I have a bidirectional merge replication. I had failure constraints because the primary key was just integer. I change the primary key to the old primary key + a location identifier.The problem is how ...
1
vote
4answers
1k views

Automated SQL backup on a timely fashion, & cleaup the database for the backed up data

I need to back up SQL database (historian), on a timely fashion, and then clean up the database by removing the backed up data. I am using MS SQL 2008 (R2), on a Windows XP machine. The biggest issue ...
0
votes
2answers
135 views

Can I use replication/mirroring to have a “Current” database and a “Full History” copy?

I have a database that is used for current operations. The amount of data is starting to get a bit to much for the applications using it. Most of the data is older and is very rarely needed. I am ...
2
votes
1answer
327 views

sql server partitioned view execution plan questions

I have created a partitioned view for 3 tables tb_sales2010,tb_sales2011,tb_sales2012 using check constraint (respectivly for each table) datepart(year,[Date])=(2010) datepart(year,[Date])=(2011) ...
0
votes
1answer
358 views

Executing sys.dm_fts_parser without sysadmin server role

A web application needs to execute sys.dm_fts_parser to discover which words SQL Server Fulltext Search used to match documents for the purpose of syntax highlighting. That stored procedure can only ...
5
votes
2answers
646 views

Recreate Indexes on 1 billion record table

I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space ...
3
votes
1answer
664 views

Automatic database file creation based on database file size in SQL Server 2008 R2

Now i am using SQL Server 2008 R2 database for data logging. The name of the database profile is MASTERDB. That profile has one data file called MASTERDB.mdf and one log file called MASTERDB_log.ldf. ...
1
vote
1answer
2k views

How can I skip reboot check and remove command line appearing during install of SQL Server?

While i am installing SQL Server 2008 R2 using the configuration file method I sometimes get an error in installation due to system reboot checking. After restarting my PC, SQL Server installs ...
1
vote
1answer
136 views

Would I lose data if I change a column from varbinary(8000) to varbinary(max)?

Is there any data loss if a table is being altered to change an existing column from varbinary(8000) to varbinary(MAX)?
3
votes
2answers
13k views

Login failed for user Error: 18456, Severity: 14, State: 11

I have an AD group XYZ that I have added to SQL Server security with data_reader permissions. The XYZ group has around 10 users in there who are successfully able to access the SQL Server database. I ...
1
vote
0answers
196 views

Why Am I Getting Deadlock - Please Read Trace

can anyone help me? i'm getting deadlock on this trace, i'm using symmetricds and i can't understand why i'm getting a deadlock on a certain table. I'm using sql server 2008 r2, ...
6
votes
2answers
947 views

Generate script to automate renaming of default constraints

Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D I would prefer to ...
1
vote
1answer
956 views

Error creating scalar function “Invalid Object Name dbo.fnMyFunctionName'”

I am using SQL Server 2008 R2. I tried creating a new scalar function. The function gets created and I see it in the list of scalar functions. When I try to modify function, function name is ...
3
votes
1answer
2k views

SQL Server 2008R2 DBCC CheckDB never finishing

I'm running SQL Server 2008 R2 on Windows server 2008 R2 and up until recently we were having no problems. After windows updates recently (no sql server updates) we cannot run step 1 of our ...
4
votes
1answer
163 views

How can I rename an auto-named constraint when I get this error 15248?

Method: Open SSMS. Find table. Open constraints. Find constraint name. Right click, rename. Give new name. EXPLODE. (well, it just fails, at least)
4
votes
1answer
233 views

Item Index for all items in nested XML query

I have a requirement to build an XML package with a parent record, and 1..N related child records. The child records are to be a nodes under the parent node, and there may be one child record or ...
5
votes
2answers
120 views

Retrieving cached plans fails due to cached objects of database Mirror

I'm trying to retrieve cached execution plans for a specific database object, but this fails because of cached plans which belong to a mirrored database in MIRROR state. The below query, without any ...
0
votes
3answers
817 views

Merge Replication identity field issues

One of our clients is using our software with merge replication of a database on a SQL Server 2008 R2 machine. There are two production environments in separate geographical locations only one of ...
0
votes
0answers
121 views

SQL Server in a state of confusion: Does the database exist or doesn't it? [duplicate]

Got a really weird, annoying problem.. Somehow the instance of SQL Server 2008 R2 running on our server has gotten somewhat corrupted. First, we noticed that the database we created yesterday was ...
6
votes
1answer
939 views

UNIQUE index key violation

I have a table with a PK and a unique non-clustered index, as follows: CREATE TABLE Table1 ( Id INT IDENTITY(1,1) NOT NULL, Field1 VARCHAR(25) NOT NULL, Field2 VARCHAR(25) NULL, ...
1
vote
1answer
384 views

SQL Server 2008 R2 database mirroring - is it possible to have a slave server for multiple master servers?

Scenario is that I have two servers running SQL Server 2008 R2 (64-bit), let's call them DB1 and DB2. I have set up database mirroring for all the databases on these servers so that DB1 is the ...
1
vote
2answers
2k views

Issue with ROW_NUMBER and DISTINCT

I have following query SELECT Dept ,ID FROM (SELECT TOP (@RowTo) ROW_NUMBER() OVER (ORDER BY Dept) AS ROWID, Dept ,ID FROM Department ...