Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

7
votes
4answers
6k views

Do SQL Server stored procedures cache data results?

I have heard this from friends, but I never investigated whether this is true. Is it true that the data results of an executed query are stored in cache? I mean, if I have a stored procedure like : ...
7
votes
4answers
3k views

SQL Server Partitioning - what to use for partition key?

I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued ...
7
votes
4answers
365 views

What are the performance implications of running multiple smaller DBs instead of a single larger DB on a server?

Our database architecture allows multiple 'customers' to exist in the same database, yet we split them into mulitple database for administrative reasons [patching, backup, etc.] Question 1 What ...
7
votes
1answer
435 views

Full Plan Cache Behavior

In SQL Server 2005, what happens when the plan cache fills up? I have done some research, and from bits and pieces gathered it seems that cached plans are given an "age" that consists of it's weight, ...
6
votes
2answers
438 views

Using indexed views for aggregates - too good to be true?

We have a data warehouse with a fairly large record count (10-20 million rows) and often run queries that count records between certain dates, or count records with certain flags, e.g. SELECT ...
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, ...
6
votes
1answer
9k views

SSIS File System Task Copy File Could not find part of the path error

I have created an SSIS package that uses a File Systen Task (copy) that copies a file from 1 folder to another. The package runs fine when I run it from my local computer but when I import the package ...
6
votes
2answers
7k views

SQL Server 2008 R2 SP1 - Tempdb “Available free space” showing negative value

Please help as this issue is driving me nuts... Tempdb has four data files & one log file. They are all located on the same dedicated drive. When I right click on tempdb in SQL Server Management ...
6
votes
2answers
172 views

Most effective way to determine the minimum set of permissions for user

I have a database that an application connects to using db_owner permissions. How do I effectively determine the minimum set of requirements actually needed by this user (application) to run without ...
6
votes
1answer
1k views

How to get online the database which has missing/corrupted FILESTREAM files?

I've corrupted the files related to FILESTREAM in a database. The .mdf and .ldf files are still intact, but when I try to get the database online, it complains, saying that the files related to ...
6
votes
2answers
714 views

Is it possible to give the optimizer more or all time it needs?

Given that the optimizer cannot take al the time it needs (it has to minimize the execution time and not contribute to it) to explore all possible execution plans it sometimes get cut off. I was ...
6
votes
5answers
6k views

Does SQL profiler affect server performance?

I am facing a problem where the sql server 2008 fails for some high load. I need to find that load case and need to optimize the code so that it can handle the load. I have found on the internet that ...
6
votes
2answers
541 views

Why select max partitioned column is much slower than indexed one?

I have a big partitioned table (300+M rows, 70 active partitions). The table has clustered PK of (K0, K1) and it's partitioned on column K1. And K1 has very low selectivity. However the following ...
6
votes
2answers
3k views

SQL Server Profiler is available for SQL Server 2008 Dev Edition?

I just upgrade from SQL Server 2008 Express with advance services to Dev Edition. I wonder whether SQL Profiler is available for the Dev Edition? Please let me know how to access that if it is ...
6
votes
3answers
1k views

Does the PCI standard forbid the use of Windows Authentication?

We currently use Mixed Mode for our SQL Server Authentication. I've tried to convince our DBA to allow us to use Windows Authentication so we can use Team Foundation Server, however he absolutely ...
6
votes
2answers
2k views

Shared Lock issued on IsolationLevel.ReadUncommitted

I read that if I use IsolationLevel.ReadUncommitted, the query shouldn't issue any lock. However, when I tested this, I saw the following lock: Resource_Type: HOBT Request_Mode: S (Shared) What is ...
6
votes
3answers
1k views

Save Connections in SSMS?

Can I save some sort of session or profile in SSMS (2008 r2) that will open up connections to a saved list of database servers when I open SSMS? So for example I frequently will open up SSMS and ...
6
votes
1answer
476 views

Does sql server calculate views every time when someone query the views?

I wonder whether sql server calculate views every time when someone query the views. It is because everytime I query the views, it take such a long time. And when I tried to shorten the time by ...
6
votes
6answers
7k views

CXPACKET Waits performance tune for SQL Server 2008

I have a SQL Server 2008 query that is working on millions of records. The query is w/in a proc that is run nightly by a job. The query can take a full day to run when I first put it on the server, ...
6
votes
2answers
3k views

How do I clear a table from SQL Server's cache?

I have a few tables in my database that should not be cached. How do I tell SQL Server not to cache a table's pages or how do I flush a single table from the cache? Flushing all the cache is not an ...
6
votes
2answers
9k views

How can I backup & restore a single FILEGROUP in Sql Server 2008

Previously, on ServerFault I asked a question about backing up and restoring a Sql Server 2008 Filegroup. Today, when I tried to RESTORE one of these FILEGROUP backups, I got the following error:- ...
6
votes
1answer
4k views

Database Users and Server Logins and the Mapping between them

I have an instance of SQL Server 2008 R2 Express Edition at home and another on a server at a clients premises which I am using to develop an application using Access as the front end. This ...
6
votes
5answers
10k views

User Permissions Messed Up following a Backup -> Restore Operation

I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management ...
6
votes
3answers
25k views

Read XML file in SQL Server 2008

How can I read an XML file and store the data in XML to our table in SQL Server 2008?
5
votes
2answers
111 views

How do I merge similar records with different validity dates?

The table I am working on has three components: An ID column (primary key in another table) Some data columns Date valid from/to columns. Values: ID Data From To 1 a ...
5
votes
1answer
82 views

existing foreign key that references fields with different datatype (char(8) and varchar(8))

This is more a quest for information that a request for help... I'm upgrading some old databases (running on sql 2005 and compatibility level 70, I suspect they are online since sql 6.5) to get rid ...
5
votes
1answer
1k views

Why not rebuild indexes with page count <1000?

I use Ola Hallengrens script for Index maintenance. Before I did that, I used the following query to see which indexes are fragmented most: SELECT dbschemas.[name] as 'Schema', dbtables.[name] as ...
5
votes
3answers
15k views

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes

I try installing SQL2008R2 - SQL2012 - sql2014 2014-07-17 16:31:16.00 spid14s Error: 17190, Severity: 16, State: 1. 2014-07-17 16:31:16.00 spid14s Initializing the FallBack certificate failed ...
5
votes
2answers
3k views

Does the Sql Server Agent account need sysadmin permission?

I ran Brent Ozar's sp_blitz script, and one of the things it's complaining about is that my SQL Server Agent account has sysadmin permission. I removed the sysadmin permission, but then the agent ...
5
votes
1answer
1k views

Optimising a query against sys.fn_dblog

Looking for assistance improving a query. This query is used to review the last N transactions in the LDF file and then add some useful information like the user, start time, query text, etc.. Here ...
5
votes
2answers
224 views

In the SQL Server RESTORE statement, in what time zone is the STOPAT argument?

SQL Server can perform point-in-time restores using the STOPAT argument. In what time zone is that date and time value? Is it UTC? Is it local for the server the backup was taken? Is it local for ...
5
votes
3answers
10k views

What permissions are necessary for truncating a table?

I have a sql account with the following permissions on a database: The db_executor role you see this account being a member of was created by this script: CREATE ROLE [db_executor] AUTHORIZATION ...
5
votes
2answers
3k views

Is it viable to attribute permissions to SQL Server virtual accounts (NT Service\SQLSERVERAgent, etc)?

Using MS SQL Server 2012 in Microsoft Windows Server 2008. I am somewhat confused by the New Account Types Available with Windows 7 and Windows Server 2008 R2 virtual Windows accounts [NT ...
5
votes
2answers
1k views

Dividing DBCC CHECKDB over multiple days

I'm working on implementing Paul Randal's method of manually spreading DBCC CHECKDB over several days for very large databases, which basically consists of: Dividing the tables in the database ...
5
votes
1answer
1k 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
1answer
261 views

SA permissions issues with many nested objects

I have a broker application that's relatively complicated. Today, after I made some changes, I started getting the error: The server principal 'sa' is not able to access the database 'XYZ' under ...
5
votes
1answer
3k views

How do I shrink the physical Transaction Log file when it's the principal in a mirror?

We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking ...
5
votes
1answer
1k views

Referencing system views in SSDT?

I've imported a database into SSDT containing a reference to a system view (specifically, sys.columns). The problem is, that I then get warnings about unresolved references when I build the project ...
5
votes
2answers
4k views

SQL Server 2008 can't repair consistency

I have a problem with a SQL Server 2008 database. Launching DBCC CHECKDB I get this error: SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: ...
5
votes
1answer
528 views

TFS 2012 - Database Project - User with NO LOGIN map to Windows - best practices

I have a SQL Server Database Project (.NET 4.5) in TFS 2012. As a DBA, i'm looking for a mechanism whereby the developers can create 'CREATE USER' sql scripts and assign their permissions within the ...
5
votes
1answer
1k views

Query in procedure occasionally does not finish

I have a question about how to debug very strange behavior (nested query getting stuck) in SQL Server 2008 R2 (Standard Edition) running a query. Sometimes (unfortunately not always) when I run a ...
5
votes
1answer
461 views

Why am I missing some output?

Archiving to avoid ID Exhaustion We are about to run out of IDs in one of the tables of our OLTP logging system. It's my job to find a way to 'archive' the existing row data in the full table and in ...
5
votes
2answers
1k views

Get a record's current partition

In SQL Server 2008 R2, how would I determine what partition a record currently lives in?
5
votes
1answer
11k views

Repairing corrupt SQL Server database backup?

I'm looking at a problem for someone who was backing up to a cloud-based service. Apparently the customer's server crashed while it was performing some maintenance on the cloud-based service's ...
5
votes
1answer
1k views

How can dm_db_index_usage_stats show 0 for user_seeks and user scans but a large number for user_lookups?

I'm probably not understanding something but here is the question. I have done a query on dm_db_index_usage_stats. I've noticed that a couple of my indexes have large numbers (6.5 million and 20 ...
5
votes
1answer
2k views

Performance difference between SQL Server Linked Server and direct query

A Management Studio query pane connected directly to a remote sql server executes and displays a simple table query of (SELECT * FROM table) in just under 2 seconds. The same Management Studio but ...
5
votes
2answers
15k views

Why does simple ALTER TABLE command take so long on table with full-text index?

I have a large (~67 million rows) name-value table that has full-text indexing on the value column (DataValue). My question is: If I try to run the following sql command: ALTER TABLE VisitorData ...
5
votes
2answers
573 views

How can I run multiple concurrent versions of my application against the same database?

Background: I have multiple concurrent versions of my application running in my production environment. Depending on the user account that's used, the user will have access to a different version of ...
5
votes
3answers
7k views

How to make conditional ordering for two or more columns

IN MSSQL 2005 I am writing one query with conditional sort and my problem is I do not know how I can sort condidional using two columns. if i wrte code like this it is working normaly select * from ...
5
votes
5answers
3k views

How much memory is needed to keep a table cached? (SQL Server 2008)

I run long and complex queries which mostly use one big table - 8GB, 40M rows. AFAIK, all/most rows are used in each query. I'm seeing lots of IO in the activity monitor - for the first query and for ...