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

learn more… | top users | synonyms (2)

13
votes
3answers
6k views

Move multiple databases from C: to D: at once

I have SQL Server 2008 R2 with 323 databases consuming some 14 GB on my C: drive, a fast SSD. Because I want to reclaim some space on my C: drive, I would like to move them to my D: drive. I have ...
4
votes
2answers
2k views

Pulling autogrowth events by file name from default trace

I use below query to get auto-growth event occurred to databases in a sql server. DECLARE @trcfilename VARCHAR(1000); SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1 SELECT COUNT(*)as ...
4
votes
4answers
5k views

SQL Server audit query that fires a trigger

Using SQL Server, does exist a way to audit from inside a trigger the sql that fires it? I need to know the SQL query that fires a trigger over a database without a profiler. Thanks
2
votes
2answers
6k views

Permissions Needed To Attach AdventureWorks Database

I am trying to add the AdventureWorks database in MS SQL Server Management Studio (SSMS) 2008 R2. As a new person to this area, or noob, this seemingly simple task has caused me much hardship and ...
8
votes
2answers
713 views

function hangs with null case operation

I created a function that accepts a start and end date, with the end date being optional. I then wrote a CASE in the filter to use the start date if no end date is passed. CASE WHEN @dateEnd IS NULL ...
3
votes
1answer
1k views

SSIS hangs during For Each File enumeration

While debugging an SSIS package that loops over thousands of files, execution hangs with no errors and no failed tasks. Slightly simplified, the package does this: For Each File ...
1
vote
0answers
81 views

Rows do not match Subscriber and Publisher

I have an transactional replication setup with updateable subscription that is utilized by an Application. When I insert a project into the application, the msrepl_tran_version is different on the ...
6
votes
1answer
2k views

Replicated Database Log File Maintenance

I have a database on the publisher that is involved in replication (publication configured for merge and transaction). Trying to regain control of the log file for this particular database (VLF count, ...
7
votes
1answer
179 views

IN and NOT IN for XML column

I have a table with a xml column. Xml is similar to <Root> <Row> <user>abc</user> <Rowid>1</Rowid> </Row> <Row> ...
5
votes
1answer
142 views

Count Most Recent Browser Used By All Users

I have a table with columns Type, Login, and Timestamp. It has been collecting information about the type of browsers users log into the system with for years. Obviously many of the users have since ...
4
votes
2answers
5k views

Installing Full Text Search to SQL Server 2008 R2

I installed sql server 2008 R2 Express successfully, then realised I need to get Full Text Search. So I downloaded the "Advanced Services Installation Package", but when I run it there is no option in ...
0
votes
1answer
100 views

SQL Server Instance Version [closed]

I'm working with a SQL Server with several instances. Four of my instances are at 10.50.2811.0 and the remaining two are at 10.50.1765.0 I've tried running SQL Server 2008 R2 SP1 and CU 6 to get it ...
2
votes
2answers
2k views

SQL server windows authentication through network

I am using sql server 2008 r2.There are about 50 pc in my office connected in network by using LAN.In a sql server 2008 r2 express(installed in windows 8) i have created all users windows account with ...
11
votes
1answer
9k views

Setting variables in SQLCMD mode

Using SQL Server 2008 R2 Enterprise Edition Consider the following statement: :setvar source_server_name "SERVERNAME\INSTANCENAME" Is it possible in SQLCMD mode to get that value using TSQL ...
3
votes
1answer
2k views

SQL Server 2008 R2 cluster installation error [closed]

I was trying to install SQL Server Cluster (Launch wiard to install single node SQL Server 2008 R2 faliover cluster) .. The setup checks displayed no error and the installation started after all the ...
7
votes
1answer
782 views

SQL Server not using available memory

SQL Server 2008 R2 Standard Edition (64-bit) on Windows 2008R2 Enterprise Server has over 300 GB of memory but total memory use in control panel never goes over 86 GB Have SQL configured to use ...
1
vote
2answers
13k views

What is the most effective way to compress and store a SQL Server backup? [closed]

I've been doing some testing of different methods for compressing and storing SQL Server backups (using SQL Server 2008 R2 Enterprise edition), and I'm wondering what the most effective compression ...
6
votes
1answer
1k views

How can a fill factor of 0 or 100 be the same?

As of my understanding, Fill factor of 80 implies that 20 percentage of each leaf-level page will be empty to enable future growth. I can't correlate how fill factor of 0 and 100 can be the same! Am I ...
6
votes
0answers
439 views

SQL Server Sproc calls from IBM Cast Iron orchestration with XML payload fail

I'm hoping that someone on this forum may have experience with an IBM appliance, Cast Iron (Websphere), and how it interacts with SQL Server. I know very little about the Cast Iron appliance, and its ...
3
votes
1answer
809 views

Why does Log Shipping .TRN file copy just stop

I apologize in advance for a long post but I have had it up to here with this error of having to delete LS configuration and starting it over for any DB thats got this error. I have LS setup on 3 ...
2
votes
1answer
2k views

Invalid object name 'master.dbo.MSreplication_options' when restoring DB

I've tried Googling for an answer to this but have got nowhere. I have one SQL Server instance whose replication is set up to be a publisher and a distributor of FooDatabase. I have a second ...
1
vote
0answers
385 views

Can SQL Server 2008R2 SSIS packages access SQL Server 2012 databases with driver updates only?

We have two products running on SQL Server that we will be upgrading to SQL Server 2012 on a staggered schedule. Product B uses SSIS packages (using ODBC drivers) to extract data from Product A's ...
1
vote
1answer
612 views

Sneaky rollback in SQL Server 2008 R2

Is it possible for an entire set of committed transactions to be rolled back in SQL Server 2008 R2 without any notification or logging anywhere? We had a set of data apparently get rolled back this ...
4
votes
3answers
3k views

How to undo every change made to a database in a controlled environment?

I'm doing system testing of a product, and several tests are severely changing the data of a demo database. The data is quite complex to create queries which will undo the changes done by the tests; ...
6
votes
3answers
8k views

Can a database's files be copied while a database is online?

I'm working on setting up a development copy of a production database on SQL Server 2008 R2 SP1. The live database is getting lightly used by two developers for read only queries currently, but the ...
4
votes
4answers
14k views

Difference between Full backup and Copy-only full backup

I saw in this link that full backup does not truncate the log. So what is the difference between full backup and copy-only full backup? For the log backup there is copy-only backup which prevent the ...
3
votes
5answers
749 views

Extract data to another computer

I have SQL Server 2008 R2 Express edition, and I want to export the data in that database to another computer so I can import it again in that computer. How please? What I have tried Right-click ...
2
votes
1answer
346 views

Import issue with Replication

When I import some data from excel file to a table that is part of a merge replication, the data is imported successfully to the table, but those new records are not synced to subscribers that belong ...
2
votes
1answer
47 views

Removing superfluous tables with a synchronization?

I'm using SQL Server 2008 r2's replication functionality to update my subscriber database through a transactional pull subscription. When I mark it for reinitialization, it does fix the schema and ...
2
votes
1answer
102 views

ALLOW_SNAPSHOT_ISOLATION Duration

I would like to enable ALLOW_SNAPSHOT_ISOLATION on a production database that has ~400 million total records. I understand that 14 bytes will need to be added to each record. If I set ...
1
vote
2answers
3k views

Active Directory group permissions not being applied to user [duplicate]

I have an odd situation. An Active Directory group was recently created for the sole purpose of being a securable in SQL Server. I created a login based on the AD group, a user based on the login, and ...
5
votes
1answer
2k views

Partitioning / indexing an extremely large table

I'm working on indexing and partitioning a single data warehouse table that weighs in around 500 GB. The table is a heap, has over a hundred TEXT columns, and the TEXT_IN_ROW option is enabled. I ...
5
votes
2answers
2k views

Default trace enabled but not active

When I query the configuration of the default trace, it shows enabled: exec sp_configure 'default trace enabled'; --> name minimum maximum config_value run_value default ...
9
votes
3answers
2k views

Dealing with identity ranges for transactional replication

I've noticed that when you set up a transactional replication, SQL Server will set identity range management to manual. What this means is that in my subscription database, when I try to insert a new ...
5
votes
2answers
346 views

One table used in two databases

I have a common product table in db1 and db2. I want to make it one table because it has one inventory (stock of products). What is the best solution to share product table among two different ...
1
vote
1answer
636 views

Migrate database from SQL Server 2005 to SQL Server 2008 R2

DBAs... please be gentle with me, I'm a 'greenhorn' SQL Server DBA, pressed into service because my company's DBA had resigned and we haven't found his replacement, yet. First, my situation: I'm ...
1
vote
1answer
481 views

How to edit publication's generation_leveling_threshold configuration value

I dont know where I put this code: update sysmergepublications set [generation_leveling_threshold] = 0 I only need do it. But I need edited any funcion of agent.
3
votes
1answer
7k views

Login failed for user Error: 18456 State: 10

In one of my vb.net applications I am getting the error Login failed for user 'user'. But the user has full permission (sysadmin). Also this error happens intermittently. Error log has the following: ...
-1
votes
1answer
136 views

Query performance in SQL Server [closed]

What factors should I check when I compare the performance of a query? I saw many articles stating about IO cost, subtree cost (by using Execution plans), statistics such as CPU time, elapsed time ...
1
vote
1answer
635 views

Compatibility level for published database and transactional replication

I am running a database on compatibility level 80 because the application using it does not yet support a higher level. It is my understanding that transactional replication on SQL Server 2005 and ...
4
votes
1answer
3k views

Error using an Execute SQL script to load a date variable in SSIS

I'm trying to populate a variable using an Execute SQL task in SSIS. The table structure is as follows: CREATE TABLE BuildControl (Id INT NOT NULL Identity, BuildDate Date, Country char(2), ...
13
votes
2answers
661 views

Vendor Wants to Run MSDB job every 5 minutes for Business Application

We have a 3rd party vendor trying to integrate 2 different applications where both DBs reside on our SQL Server instance with 150+ other DBs, and they want to create a MSDB job to "synch" the 2 ...
0
votes
1answer
3k views

SQL replication Agent permission

Error messages: The schema script 'teste2_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001) The process could not read file ...
4
votes
3answers
4k views

Running Total which Resets when Customer changes (keeping existing RowNumbers)- attempt included

I have a table where the RowNumber is of essence. It has a range of clients, and a running total for that client - which resets every time the Client changes. e.g. client rownr amount runtotal ...
-3
votes
3answers
5k views

Shrink Database using query [closed]

I have deleted tons of records from my database and now i want to shrink the database. So please give me script which shrinks the database
1
vote
3answers
320 views

Unused Indexes - Consider Primary Key Constraint Supporting Indexes?

I have given a task to find out the unused indexes in my production environment. I'm using a DMV to find the unused indexes should i include the Primary key Clustered Indexes on it? If a Primary key ...
1
vote
0answers
347 views

Transactional Replication times-out with Database Snapshot on subscription

I have a VLDB that is being replicated using transnational replication. The performance has been terrible, and the process frequently times out. After much investigation, including Service Packs and ...
1
vote
1answer
141 views

When importing raw files for internal conversion, should I use a secondary database or just isolate them within the database?

When importing raw files for internal conversion, should I use a secondary database or just isolate them within the database? I've got between 1.25 and 2GB of CSV files to be imported (and already ...
16
votes
1answer
9k 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 ...
0
votes
2answers
17k views

Query to search for a substring in xml

I use below query to search for a substring in whole xml(including node name and node value) SELECT * FROM tablename WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 ) I want an ...