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

learn more… | top users | synonyms (2)

8
votes
2answers
3k views

Do indexes consume memory?

I am just starting to learn about memory usage on SQL Server. When using the query in the answer to the question SQL Server 2008 R2 "Ghost Memory"?, I discovered that a single database is ...
5
votes
2answers
585 views

Can I prove my database does not have memory issues?

(This is a continuation of do indexes consume memory) A fellow database developer believes we are having memory issues. He has seen increased run times in some standard queries - going from under 10 ...
2
votes
1answer
364 views

Reconciling dm_os_windows_info windows_sku values, gathering same info from pre SQL Server 2008 R2 instances

I'm developing an inventory system for RDBMS deployments. I'm starting with SQL Server, and later will be pulling in info for Oracle & other RDBMS deployments. When you query [dm_os_windows_info] ...
12
votes
3answers
8k views

SQL Server 2008 R2 “Ghost Memory”?

We have a dedicated SQL Server 2008 R2 machine that is experiencing some strange memory issues.. The machine itself has plenty of resources including two quad-core processors, 16gb of RAM and 64bit ...
9
votes
5answers
224 views

What is the best way to find out how many records do not have a proper phone number in SQL Server?

I have a field that people historically could use to freely supply a value. I need to identify the records which do not have an appropriate value so I can clean them. I've been looking around in SSIS ...
4
votes
2answers
220 views

Checking a wide table for nulls [duplicate]

Possible Duplicate: Test if any fields are NULL I am new here. I have a table with 70-odd columns. Is there a way I can check the number of rows with at least one null value in any column ...
3
votes
1answer
182 views

sql server active active configuration with same database names

I have a SQL Server 2008 Enterprice in a clustered environment and in active-active mode. By active-active mode i mean that both instances are active and serving requests to their databases. ...
1
vote
1answer
205 views

Updating a table based on condition

I have a database having a staging_table. From this table, entries are added to other table named Profile. Whenever staging_table is updated, it checks whether all its entries are present in Profile, ...
8
votes
1answer
556 views

What is the actual lowest possible positive REAL number

MSDN says that the range of REAL numbers is - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38. Apparently the true lower limit is much lower. The following script populates a REAL column ...
4
votes
1answer
1k views

Daily database maintenance using SQL Server 2008 and a stored procedure

I have created a database maintenance plan using wizard though that link for SQL Server 2005. I have created a Maintenance Plan for SQL Server 2008 and it works fine. Now I required to complete the ...
2
votes
1answer
179 views

Optimising a query using hint locks

I have a stored procedure that is being called very frequently. For some database servers it is as often as 20 times per second. Unfortunately because different clients call this procedure, I can't (...
2
votes
1answer
3k views

Can't create table or database in sql server as permission denied in database master . (Logged in with windows authentication )

Can't create table or database in sql server as permission denied in database master. Log in authentication is Windows authentication (./SQLEXPRESS)
6
votes
1answer
2k views

Cross Database Chaining vs. Trustworthy Option

Recently, we had a stored proc request that required access across multiple databases. Of course this is not uncommon to have but in our case, earlier we used service accounts and this service account ...
3
votes
1answer
708 views

EXECUTE AS does not appear to be changing security context

We have legacy code that still makes use of xp_cmdshell calls. When we migrated to SQL Server 2008, we created a stored procedure that used code after the following pattern: EXECUTE AS 'DOMAIN\ID2' ...
0
votes
2answers
3k views

Is it possible to join derived tables to actual in Access 2007

Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008? I have an example below: SELECT * FROM --MyOtherTable is a "real" table MyOtherTable ...
2
votes
1answer
36 views

How to set the language of a doc to a DB field

Is there a way to set the language of a document body to a value fetch from the DB in SSRS? I tried with a simple expression and I got this error. I wonder what the best workaround is
1
vote
2answers
563 views

Merge Replicate and Mirrors

I am preparing some scripts to setup merge replication in our production environment. After completing the export from UAT, I've come across something that doesn't appear like it would work in the ...
4
votes
1answer
1k views

can I create an SSRS group without adding the group to the box itself and without using Active Directory?

I want to know if it is possible to create a group "internal" to SSRS without having to add the group to the ReportServer box itself. I do not have permissions to the box to create groups and/or add ...
2
votes
1answer
2k views

Looking for a Trigger to change inserting strings?

I have an MSSQL Server 2008 Database. There are some tables in my Database like below : table1, table2, table3, .... each table has a NVARCHAR(50) field (Name) Now, I wanna change the strings that ...
8
votes
3answers
11k views

When should indexes be dropped and recreated?

We are constructing a data warehouse that initially will be 1 TB and will grow around 20gigs every month. For certain tables we are doing daily ETL processes and others we are doing weekly/monthly. ...
2
votes
1answer
691 views

MS SQL 2008 Express - Database Tuning Advisor

I want to use DTA, but on my website I have an Express Server version. Can I backup my DB on Express server, restore it on my personal computer with Enterprise Version, make some tuning by DTA and ...
2
votes
1answer
1k views

How can I check the integrity of disabled foreign key relationships for each individual table? (SQL Server 2008)

I'm porting a huge legacy database that many different legacy frontends depend on. When I'm finished, a new frontend will be running alongside all the old legacy frontends minus the frontend I ...
1
vote
1answer
15k views

Installing SQL Server 2008 Express Edition in quiet mode issue?

I want to install SQL Server Express 2008 in quiet mode. I know that we can use /ConfigurationFile="ConfigurationFile.INI" parameter to install SQL Server from a configuration file. The following is ...
3
votes
1answer
372 views

Why CDC records nothing while being enabled?

I enabled Change Data Capture on some tables in a Microsoft SQL 2008 database. Both of the following queries return 1: select top 1 is_cdc_enabled from sys.databases where name = N'<DatabaseName&...
2
votes
1answer
4k views

Cannot fetch the rowset from OLE DB provider “SQLNCLI” for linked server X

I am getting this error while doing concurrent read from same table through linked server. Time out setting is alright. When this error happens, I also start getting the following error for the same ...
6
votes
1answer
2k views

NESTING_TRANSACTION_FULL latch

Production SQL Server 2008 performance is far below expectations. Simple single-table queries sometimes are working for 5 seconds in the mid-day compared to 250ms after hours. We cannot find which ...
7
votes
1answer
2k views

Msg 2534 : DBCC CHECKDB allocation errors

We recently moved our intranet production database from an SQL Server 2008 to an SQL Server 2008 R2 brand new server. The backup Job have been failing since migrating, due to DBCC Errors (Check ...
-1
votes
1answer
82 views

Index Proposals

As a theoretical database question, if you are asked 'which indexes would you propose and why?' are the answers from primary, clustering and secondary index? How would we know if we should introduce ...
4
votes
1answer
4k views

Database file size from sys.master_files and sys.dm_io_virtual_file_stats are different

Im using query to find out database file size. I'm using system view and DMV, because i do want to find out both- actual file size and theoretical (in case of sparse files) file size. Select ...
3
votes
1answer
369 views

Microsoft SQL Server 2008 R2 Developer Edition Data Tuning Advisor tool

I have Microsoft SQL Server 2008 R2 Developer Edition installed on my machine which has Win7 64 bit OS. I am not able see Data Tuning Advisor tool in my SSMS. Before this installation my machine ...
3
votes
1answer
575 views

why do SSRS reports run longer for a particular user

We have been receiving complaints from a particular SSRS user that her reports run slow. I investigated in the ExecutionLog table in the ReportServer database and I observed something strange. ...
2
votes
0answers
652 views

SSIS - Derive column values based on join/subselect from a non-table source

I have a flat file source that contains a lot of duplicate records, which have the same key fields but differ by their "changed date". I need to split this up into two outputs, one for the "latest" ...
1
vote
1answer
1k views

Merge multiple table output but indicate which tables the records came from

I have an application with a function that uses a stored procedure (SQL Server 2008 R2) to pull in and arrange data from one table. Now, I’ve been asked to make it pull from two tables, but it’s not ...
1
vote
2answers
261 views

SQL Trigger to verify account, if not verified dont process stored procedure/transactions

Is there a way to create a trigger or function to check a user against the database and if not verfied, the following stored procedure is not ran. I would like this option for verification check ...
0
votes
1answer
79 views

View in SQL Server 2008

I am in need of getting data from multiple select queries. So I thought of creating two views and to inner join them to get my data. My questions is: which option is better creating a view, ...
5
votes
1answer
364 views

Database structure, relational or data warehouse?

Good morning, Im having some issues with how my database should be laid out and would appreciate some guidance. I have several tables (rainfall data), each containing the following columns: Date (...
4
votes
2answers
1k views

replication monitor: why is current average performance poor?

I am a replication rookie. We have a very simple transactional replication set up in SQL Server. I am looking at the Replication Monitor on the subscriber and one of my subscriptions show Poor ...
1
vote
1answer
844 views

replication warning: transactional replication latency alert not working

I have set up a predefined replication alert (as outlined here) in SQL Server 2008 and the alert for Replication Warning: Transactional replication latency does not appear to work. This is triggered ...
4
votes
4answers
2k views

What options exist for copying data between servers on a regular basis?

My apologies if this has already been answered. I've searched SO and of course here on DBA, and am surprised to find no close matches. Specifically, I'm looking for a solution which survives schema ...
3
votes
1answer
2k views

creating an index for varchar column greater than 900 bytes

I have a var char column in SQL Server 2008 which is usually less than 100 bytes but in some cases can be huge up 5607 bytes. I need to index it, and I can't because the max index size in SQLServer is ...
3
votes
1answer
593 views

Restore SQL Server database backup to other directory than root

I have a database backup file (db.bak) which I want to restore and import in my SQL Server 2008 R2 instance. However, the database takes approx 1 GB of space, and my C: partition is really strained at ...
1
vote
1answer
97 views

SQL Server Agent

hi i need to run a procedure every first day of month. And the procedure should automatically run at every month.So with out using timer i have to use it. So i Think Better to go for SqlServer agent ...
1
vote
1answer
2k views

Why is a Login losing a User mapping?

Is there any way to determine why is a Login is losing a User mapping? I've created them with the following SQL ... CREATE LOGIN [qauser] WITH PASSWORD = 'xxxx', DEFAULT_DATABASE = [app_tst], ...
34
votes
7answers
24k views

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?

I am writing the schema for a simple bank database. Here are the basic specifications: The database will store transactions against a user and currency. Every user has one balance per currency, so ...
2
votes
0answers
372 views

going from heirarchical XML to heirarchical SQL tables then SQL to c# objects to go to user view, how should I store the data in the databases?

Hi I'm getting my data from a web service using their getData call and it comes in as fairly chunky heirarchical XML, first everything all at once, like <allData> <product1> <...
1
vote
1answer
345 views

Importing large file while keeping table available for other operations

We need to import millions of rows in a quite big table. Say like 5 million rows into a table with more than 1 billion rows. After preprocessing the import file we will have update operations and ...
4
votes
1answer
3k views

UPDATE Statistics with full scan

Does running UPDATE Statistics on SQL 2008 r2 with full scan on a large table 50 million rows (non partitioned)? I am trying to confirm the following behavior. a) takes any locks during the entire ...
7
votes
1answer
179 views

Disallow new UDFs that contain a cursor

Is there a way to prevent developers from creating new user-defined functions that use a cursor? Would a database trigger that can read the code of a UDF do?
1
vote
0answers
1k views

Server Collation Change Error [closed]

I am using following command to change the collation of my SQL Server R2 (10.50.1600): setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLServer /SQLSYSADMINACCOUNTS=sa /SAPWD=**** /...
3
votes
1answer
318 views

SQL Server: Duplicate Index Question

We recently has a massive performance problem on our system, which we narrowed down to a problem with I/O on the LUN. IOPS increased twenty-fold during the period when the problem was occuring. The ...