This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (1)

3
votes
2answers
14k views

SQL Server 2008: The database [dbName] is not accessible

I got this error from another person and I've run out of ideas. I can access this database without any problems, the database is in MULTI_USER mode, the person receiving the error is a user, etc. The ...
-1
votes
1answer
17 views

SQL statement for comparing actual products and plan products

I need some helps for writing SQL statements to compare count of actual products and plan by like below 2 Input tables: CREATE TABLE PRODUCT_PLAN ( PRODUCT NVARCHAR(50), START_TIME ...
0
votes
1answer
37 views

Saving SQL Server DMV's for later use [closed]

Is it possible to save DMV's in SQL server database backup, which can be restored on different server, I mean with master db details( views and all)? If not, what's the best way to maintain 2 ...
0
votes
1answer
108 views

Migration script fires 'The data definition language (DDL) command cannot be executed at the Subscriber'

Yesterday I had run a migration script, produced by Visual Studio 2008 schema compare option, on a SQL Server 2008 database. It had run correctly. But today, when I executed the same script, it threw ...
0
votes
2answers
76 views

How to create a VIEW in SQL Server [closed]

I have a user table and I need to create a VIEW so a user can only see their own data/row. How could I achieve this? I have been searching online but I can't find anything useful User with user_id, ...
0
votes
0answers
33 views

Strange SSIS-Package problem ( ASYNC_NETWORK_IO)

I've read a lot of posts here about this warning. Have been searching on the Internet, but not like this case. We are having problem with integration services packages. When I run the query (that ...
0
votes
1answer
263 views

Found errors in table after executing DBCC CHECKTABLE command

I am trying to alter table to add primary key ALTER TABLE GeoCity ADD CONSTRAINT PK_GeoCity PRIMARY KEY(locId) and got this error message Msg 1750, Level 16, State 0, Line 1 then I run ...
1
vote
2answers
368 views

After Restore Log_reuse_wait_desc of Replication

I'm wondering if anybody has ran into this issue before and has any suggestions on what's causing it/how to fix it. We are restoring a database into lower Dev environments and after the restore the ...
1
vote
1answer
26 views

can log shipping to log shipping?

I have one production SQL Server 2008 Database with log shipping set up for that server in US. I have a account in the log shipping database so I could sometimes run query on log shipping database. ...
0
votes
0answers
16 views

Identify Purchases or Redemptions with the Same Single Trade Profile but Attributed to Multiple Channels

I want to identify Mutual Fund Purchases or Redemption with the same system source ('System'), firm ('Firm Id'), CUSIP ('Product Cusip'), trade date ('Trade Date'), settle date ('Settled Date'), trade ...
0
votes
0answers
15 views

Does splitting to multiple databases has performance impact? [duplicate]

I am scaling an old application which was designed to have ten databases. These databases are related to each other. The database is SQL Server 2008. The databases combined size is less than a GB. I ...
2
votes
0answers
27 views

How long does the final phase of an online index creation take?

In reading the whitepaper: Online Indexing Operations in SQL Server 2005 it says: The online index build process can be roughly divided into three phases: Preparation Build Final ...
5
votes
1answer
2k views

SSRS Merge Data Across 2 Group Values

I have created a report for SSRS 2008 which produces the results below. What I am looking to achieve is for those results in pink, AD, to be under both AM and PM with the text centered i.e. merged ...
1
vote
1answer
241 views

SQL Server replication conflicts after migration from 2000 to 2008

I got a suggestion over at Stackoverflow to post here....greatful for any and all help. Please bear with me I think this might take a while to explain. For many years now my company has hosted a ...
0
votes
1answer
35 views

Creating database with a relative path

I am supposed to be testing a piece of software that uses mssql express as the backend for data storage. When it is creating its databases, it tries to create them in my home directory that is on a ...
6
votes
2answers
6k views

The database principal owns a schema in the database, and cannot be dropped message

I am trying to delete a principal from the database but can't because it owns a schema. When I go to edit the user, however, the box to uncheck schemae is blue and unremovable. How can I remove the ...
1
vote
1answer
41 views

Backup SSRS Encryption Keys Using Powershell

I have made the following script from google for backing up the SSRS Encryption keys: cls $pwd = "sa@123@123" $SSRSClass = Get-Wmiobject -namespace ...
4
votes
1answer
43 views

How to tell if a backup file contains CDC?

Using SQL Server 2008 through to 2014, I know how to backup and restore a database with Change Data Capture enabled (CDC), but what I am looking for is a way to query a full backup file to determine ...
10
votes
5answers
14k views

Save Results as CSV *with* headers in SSMS

With SQL Server 2008 R2, when I save the results as a CSV there are no headers. I can work around this by copying and pasting with the "Copy with Headers" , or just grab the headers and paste them in ...
0
votes
0answers
54 views

Problem running SSIS package remotely using PoSH

For various reasons I'm trying to run an SSIS package remotely using Powershell. I've enabled remote scripts on my server using the following command. Set-PSSessionConfiguration -Name ...
0
votes
0answers
40 views

Decimal Data Type as Primary Key

One of my tables is set up like this: create table dbo.Source ( testID decimal (10,3) PRIMARY KEY , column 2 somedatatype , column 3 somedatatype ) Whenever I perform SSIS into this table ...
2
votes
2answers
800 views

Table with 4 million records - different size and performance in development and production

At my company we use SQL Express 2008 R2, which supports databases up to 10GB. We have 2 databases with almost the same number of rows in all tables. But, one of them is 4.5 GB in size (which is in ...
3
votes
2answers
392 views

Migration to databases Domain Users do not have access

I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be ...
5
votes
3answers
67 views

Is there an advantage to not partition aligning an index?

I have the privilege of managing a large partitioned OLAP table. While reviewing this table I noticed one of the indexes is not aligned with the partitioning scheme. Since the author is not ...
0
votes
0answers
29 views

SQL Server Windows authentication: VMs versus others

This really seems like a weird one to me. I have two VMs of interest, one is running Win2k8 R2 x64 and the other is running Windows 7 X64. The server is hosting SQL Server 2k8 while the latter has SQL ...
1
vote
1answer
16 views

How to manually invalidate a pull merge replication snapshot from the publisher

XY Problem background info: I have a pull replication publisher in which I want to add or alter a index and have those changes be applied to the subscriber. The solution to that problem I would like ...
6
votes
2answers
6k views

How to get last identity row Inserted when using instead of trigger

When I insert into tables using instead of triggers, @@Identity, IDENT_CURRENT('Table') and SCOPE_IDENTITY() return null. How I can get the last identity of inserted row?
0
votes
1answer
1k views

SQL Server to MySQL sync, using Workbench, FreeTDS, and iODBC on 10.9

I've been struggling to get the MySQL Workbench to behave properly to complete the following: With Mavericks (OS X 10.9), connect to a SQL Server and create a migration job to a MySQL server. The ...
1
vote
1answer
43 views

Identity seed increased when using IDENTITY_INSERT

Running this snippet on SQL Server 2008 produces different result than on SQL Server 2014. I need them to produce the same result. My scenario is that I am writing unit tests (using tsqlt) and this ...
7
votes
2answers
11k views

Sql Server - Best Practices for Growing Database Files

I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 ...
23
votes
2answers
54k views

How to identify which query is filling up the tempdb transaction log?

I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
2
votes
2answers
48 views

idera - why it restores databases faster

We have a 2gb live database that takes forever restore whenever I need some live data to test something. If I use idera it takes 5 mins at most. When I use the restore database command it can take ...
5
votes
3answers
1k views

On SQL Server Backup: Msg 3271, Level 16, State 1, Server Line 1 A nonrecoverable I/O error occurred on file

I am trying to back up a pretty big database and I keep getting this error: Msg 3271, Level 16, State 1, Server {Server Name}, Line 1 A nonrecoverable I/O error occurred on file "{Backup File ...
12
votes
3answers
2k views

Implications of using a Unique NonClustered Index with Covering Columns instead of a Primary Key

We have a large table [MyTable] which currently has both a Primary Key, and a Unique Non Clustered Index on the same column ([KeyColumn]). The U NC index also has additional covering columns. Having ...
1
vote
1answer
437 views

Going from separate databases to shared databases and schemas (Multi Tenant Data Architecture)

I am eagerly trying to design the perfect multitenant data architecture for my database. I am getting inspiration from an article posted by Microsoft, based on scenario 3. I have been given all my ...
4
votes
1answer
52 views

SQL Server : parsing with XPath Performance

I have a UI that allows a user to select certain data points they want to filter the results by. There are things such as location, department etc. Once they have selected all of the options, I ...
10
votes
1answer
4k views

Deadlock error isn't returning the deadlock SQL

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I am getting this error randomly when one of my ...
1
vote
1answer
94 views

high PAGEIOLATCH_SH waits on server [closed]

EDIT: I know this question is closed, but I hope it will help someone. The problem is on our disks ( cluster ). Using PerfMon, I could create some counters ( disk reads and writes ) and the reads ...
-1
votes
1answer
27 views

Substract dates from previous row

I couldn't see any question matching this one so I made this question. I have this result from my code and the last thing I need is to substract the dates so I get the real dates: | EmployeeId ...
1
vote
1answer
59 views

Error when trying to access linked server from another machine

I have created a linked server from a Excel source and when I query or test the connection from the locally from the server everything is good but if I try the same from another machine with DBMS I ...
0
votes
1answer
47 views

SQL Server 2008 R2 error on database engine

Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)
2
votes
2answers
903 views

Need advice on abnormal login auditing

Recently i am facing some slow issue for my web application. And i have check my visitor report and it shows normal traffic rate as usual. And then i check my db login auditing and find below login ...
1
vote
1answer
134 views

SQL Server 2014 .bak file error - The media family on device 'D:\..' is incorrectly formed

Worst case scenario - the server crashed and the 2014 SQL Server .bak file had to be recovered from the failed disk. The .bak file I have is over 20gb in size but is unusable when trying to restore ...
3
votes
2answers
67 views

What happens with statistics when reverting to a database snapshot

Lets say I have a database FOO for which I created a snapshot (named FOO_snap). Now I'll run thousands of queries on this DB. After that I will revert DB to FOO_snap. Will I lose all of the statistics ...
5
votes
2answers
3k views

What can I do to speed up this SQL Query?

I've created this SQL query with the help of @Dems :-) Here is some detail, I tried to make a SQLFiddle but I kept getting errors with my variables... This works in Sql Server 2008... My question ...
32
votes
2answers
103 views

SQL Server unpredictable select results (dbms error?)

Below is simple example, which returns strange results, that are unpredictable and we cannot explain it in our team. Are we doing something wrong or is it SQL Server error? After some investigation ...
0
votes
1answer
83 views

same query different servers,different execution plan and time

I have this query and it runs less than 2 minutes on Test but on Prod it takes 10 minutes. Both are the same version,CPU,RAM,OS the same. I have four tempdb files on Prod but one one Test. I ...
3
votes
1answer
230 views

Updateable Subscriptions: Rows do not match between Publisher and Subscriber

I have transactional replication with updatable subscribers set up in SQL Server 2008. It has 3 subscribers and 1 publisher. I had to setup replication again due to some errors related to the database ...
0
votes
1answer
30 views

Analyse and report disk usage growth historically

Is it possible to use historic information to show disk usage growth? I haven't keep much in the way of database setup and disk usage information. So I can't say how much disk growth has occurred in ...
2
votes
1answer
330 views

What means Command column in sp_who2 and text column in sys.dm_exec_sql_text?

I have a script that shows commands being fired in SQL Server 2008 database as follows: --shows who is active and executed commands declare @SPID int=null, @Status varchar(max) = null, @LOGIN ...