All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

17
votes
2answers
28k views

Is it useful to have the SQL Server instance root directory on a separate drive?

I know it's possible to change many of the default paths when installing SQL Server, and generally when I do an install I change the data and log folders to be on separate drives (typically D and E), ...
0
votes
1answer
103 views

Setup logical Database model for measurements

I'm struggling to set up a logical model for the following spreadsheet: (See a close-up of the table here.) My problem is that it is not clear to me how to deal with the "gap" values where each ...
2
votes
2answers
1k views

SQL Server imports data in wrong charset with SSIS

I have a flat file which I am importing into a SQL Server table using a SSIS package. The code page is set to 65001. However, when I run a SELECT * FROM tableName the data appears mangled. For ...
3
votes
2answers
7k views

Restore msdb from SQL Server 2008 R2 to SQL Server 2012 instance

I'm moving from a SQL Server 2008 R2 instance to a SQL Server 2012 instance on a separate box. I want to restore msdb to get all my agent jobs and the like and I'm getting the following error: ...
2
votes
2answers
15k views

how to calculate difference of first row and last row fields value in each group

I have table with structure like this: +-------+------------------+ | Value | Date | +-------+------------------+ | 10 | 10/10/2010 10:00 | | 11 | 10/10/2010 10:15 | | 15 | 10/10/...
4
votes
1answer
13k views

Is there a backup operator role in Microsoft SQL Server 2008?

I have a Windows Server 2008 with Microsoft SQL Server 2008 and VERITAS NetBackup installed. Basically, this NetBackup requires a valid logon ID into the SQL Server that is powerful enough to do a ...
1
vote
2answers
283 views

Implications of Changing to a Static Port

I currently have an SQL Server hosting two production instances which have numerous web apps accessing them. As far as I know, none of these apps are using a specific port in their connection strings, ...
2
votes
3answers
8k views

How do I modify the user interface language in SSMS?

How do I modify the user interface language in SQL Server Management Studio? For instance, if I installed in English, but wish to view in Japanese (to name but one possible example). I am running ...
4
votes
2answers
10k views

How to recover a corrupted msdb database in SQL Server 2008?

One of our clients' SQL Servers got corrupted. we were forced to uninstall all instances and all SQL Server programs in add/remove programs and then reinstall SQL Server because their backups were ...
12
votes
1answer
16k views

Why do we need to rebuild and reorganize Indexes in SQL Server

After searching the internet i couldn't find the reason for Why do we need to rebuild and reorganize indexes in SQL Server ? what does internally happens when we rebuild and reorganize ? An ...
7
votes
1answer
960 views

TempDB Contention with 24 physical tempdb files on 48 core AMD Opteron box

I just received the following notification about an instance of SQL Server 2005. The instance has a core-to-tempdb-file ratio of 2:1 for a total tempdb file count of 24 files. Contention should not ...
4
votes
2answers
4k views

Deadlock on insert for a table with an insert trigger that updates column in same triggered table

By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the ...
1
vote
1answer
59 views

Merge Replicate, reinitialise with a different part of the data

I have a set of database with merge replicate configured between them. The publisher is an aggregate of three sets of data. Call them set 0, 1, and 2 There are subscribers that grab one set of the ...
18
votes
3answers
4k views

What are valid usage scenarios for HEAP tables?

I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and ...
12
votes
4answers
55k views

SQL Server database size didn't decrease after deleting large number of rows.

I'm no good in SQL, but I've got a database to maintain. There's almost no place left for it, so I've decided to delete all the data for, let's say, year 2008. After executing delete query (had ...
6
votes
1answer
344 views

AUTO_UPDATE_STATISTICS_ASYNC ON?

I have been advised that setting the AUTO_UPDATE_STATISTICS_ASYNC ON may be a good idea. I have been reading a couple of ideas and still not sure as this is a non default setting. Any further advice?...
1
vote
1answer
1k views

Audit 'sa' login in SQL Server 2005

I have a requirement where I need to audit (and potentially be notified) whenever the 'sa' user account logs into an SQL Server 2005 instance. Additionally, there is a preference that the database ...
0
votes
1answer
982 views

Is there a way to list all tables and when they were last accessed?

I'm wondering if there's a way in SQL Server to make a list of all tables and when they were last accessed (select / update / insert or delete) either by a user, view or stored procedure?
4
votes
1answer
724 views

Query to check where database growth was?

I have a database that grew very significantly overnight and I'd like to find out why. I've restored yesterdays backup to another location. Is there a query I can run to query the size of tables or ...
1
vote
0answers
55 views

Merge Replication Finding Session Id

I am using merge replication with SQL 2012. I am writing a custom business logic handler, and would like to know if there is a way to get a session id? For instance if the custom business logic ...
1
vote
1answer
25k views

user “sa” cannot connect to SQL Server Express 2008 R2

Goal: connect to SQL Server Express 2008 database using SQL Server authenticated account "sa", rather than my Windows authenticated account. When installing SQL Server 2008 Express, I took all ...
9
votes
2answers
22k views

SQL Server backup failing. Error: 3041, Severity: 16, State: 1

First of all, I'm very new at stackexchange, so please bear with me. I'm running a SQL Server 9.0.4060. My problem is: my SQL Server backup keeps failing on a lot of the databases. The Maintenance ...
7
votes
2answers
1k views

Adding a Clustered index to a HEAP table on someone else's app

We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% ...
1
vote
1answer
150 views

a question in tables relations

Table A is linked to Table B , and Table B is linked to Table C using primary and foreign keys My questions: Should i link Table A to Table C too?? Or Table B can act as intermediate link between ...
2
votes
2answers
1k views

Executing query is too slow when accessing SQL Server located in an other country

I'am doing an investigation about a slow running query (see below info) when accessing our production SQL Server located in an other country. Now I'm looking for tweaks on how to solve this and make ...
12
votes
6answers
72k views

Download SQL Server Profiler for SQL Server Management Studio

How can I profile a SQL Server 2008 database to see code that's being executed on a particular database? I remember using the SQL Server profiler, but I don't see it in SQL Server Management Studio ...
5
votes
1answer
3k views

MERGE with OUTPUT doesn't seem to be doing the right thing

I'm adding a foreign key to a table, and removing any rows that violate the FK, copying them into a ModifiedTable_invalid table. As part of the script, I've got the following MERGE command: MERGE ...
0
votes
1answer
802 views

Average, between specific date/time, of the difference between current field and previous field

Im trying to calc the average, between a specific date/time, of the difference between the current field and the previous field. At present ive got a query which displays this but im unsure how i can ...
3
votes
2answers
1k views

Save history editable data RDBMS

I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
16
votes
7answers
57k views

How can I get the correct offset between UTC and local times for a date that is before or after DST?

I currently use the following to get a local datetime from a UTC datetime: SET @offset = DateDiff(minute, GetUTCDate(), GetDate()) SET @localDateTime = DateAdd(minute, @offset, @utcDateTime) My ...
5
votes
3answers
883 views

Database Indexing - Maintenance Jobs

I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are re-...
4
votes
2answers
828 views

Best practise for copying a database from production

Currently after releasing a new major or minor version of our application, we take a copy of the production database and perform the below process: Copy production database to test server Obfuscate ...
1
vote
1answer
91 views

“max_packet_size” variant in SQL Server

I am wondering if there is a SQL Server "version" of the MySQL max_packet_size parameter. Is there a way to configure this setting in SQL Server?
0
votes
3answers
640 views

Pivoting row contents to produce one dimensional result

I have a table with a varchar column containing pipe-delimited values For example: Row 1 Column 1 = a|b|e|gg|foo Row 2 Column 1 = oV|foo|do Row 3 Column 1 = boop How can I query this to return ...
3
votes
3answers
385 views

PostgreSQL equivalent of SQL Server SQL_VARIANT_PROPERTY

Does PostgreSQL have an equivalent function for determining column metadata? Reference: SQL_VARIANT_PROPERTY pg_typeof() appears to be close, but it does not show the precision and scale. Looking up ...
6
votes
2answers
1k views

SQL ANSI JOIN precedence

I have a query that looks like this: SELECT * FROM TBLA A LEFT JOIN TBLB B ON A.Col1 = B.Col2 RIGHT JOIN TBLC C ON B.Col3 = C.Col4 JOIN TBLD D ON C.Col5 = D.Col6 In which order ...
5
votes
3answers
425 views

Storing date increments in tables

I'm working on a ASP/MS SQL project that performs a lot of calculations based on dates and time. In one case, the system will lookup a value based on the nearest half hour increment throughout the ...
1
vote
2answers
2k views

sql server removing service_queues

When I query select * from sys.service_queues in SQL Server 2008 sp3, I am getting: InternalMailQueue ExternalMailQueue _LakeSide_DbTools_DeadlockQueue QueryNotificationErrorsQueue ...
4
votes
1answer
5k views

SQL Server upgrade single instance

I have a server that is running SQL Server 2005 Express. It currently has 2 instances of SQL Server running. Instance 1 runs some old legacy software that is not supported by me. Instance 2 runs my ...
5
votes
1answer
550 views

SQL Server 2008 upgrade advisor tool

We upgraded to SQL Server 2008 from SQL Server 2000. But due to some legacy scripts/queries in our health care application, that run only in 2000, we had to run 2008 version on backwards compatibility ...
1
vote
1answer
6k views

Create a linked server with SQL Server 2012 x64

How can I create a linked server to a folder having fox pro dbf files? I can access the dbfs inside Visual Studio 2010, creating a connection with this properties Connection string Dsn=Legacy;dbq=\\...
5
votes
2answers
15k views

Restore Backup Fails - File 'Db' cannot be restored to '{…}.mdf'. Use WITH MOVE to identify a valid location for the file

I am getting the following errors trying to do a restore using the restore command on a backup file I recieved from a customer. RESTORE DATABASE SFDB FROM DISK = N'C:\Backup\Backup.bak' Any ...
0
votes
2answers
461 views

SQL Server 2005 replication for a data warehouse

We are trying to implement some kind of Data Warehouse at my current employer. Most of our data is in a SQL Server 2005, but we might also integrate some Access databases and some databases in a SQL ...
3
votes
2answers
348 views

Why is my CPU usage increase after setting database to read-only on SQL Server 2008 R2 [closed]

I administrate a reporting database. We run all the de-normalization process on a database DB1 daily, then we copy all the data to an another database DB2 on the same server which is queried by web ...
0
votes
1answer
338 views

How to understand what Columns Have Changed in Merge Replication

I am using merge replication in SQL 2012. Some of my merge articles use column level tracking. I am trying to understand how to use the 'lineage', and 'colv1' fields in the MSmerge_contents table to ...
5
votes
1answer
11k views

How to grant a user UNSAFE ASSEMBLY permission

How can we assign a user , e.g.: the database owner (DBO) the UNSAFE ASSEMBLY permission? I'm asking it to enable a user to create an assembly and don't know the SA password, so EXEC sp_changedbowner ...
2
votes
2answers
2k views

Converting ZIP code string to integer in SQL Server

I have a SQL Server table pairing zipcodes and lat/long geometry, such as below ID ZIP5 geom 1 356HH 0xE610000001044E00....FFFFFFFF0000000003ID 2 35677 0xE6100000010404000000068....000003 ...
4
votes
1answer
123 views

What is a good auto-generated key for a federated database?

I am about to split a large database into a bunch of federated instances. Currently, most of the primary keys are auto-generated identity ints. Obviously, that's not going to work in a federated ...
3
votes
2answers
2k views

Does adding page compression to a table also compress existing indexes?

I am creating a series of new tables that will have page level compression enabled. Will this affect the indexes as well, or will I have to also specify compression when creating the indexes? create ...
4
votes
1answer
712 views

TempDB size issue after applying Indexes

I added several indexes to a SQL Server 2012 database to help with performance a couple of days ago. Today our partition with TempDB on it ran out of space. TempDB went from less than 10 GB to 38 GB. ...