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)

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
254 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
3k 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
520 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
967 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
449 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
5answers
5k 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 ...
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
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 ...
5
votes
2answers
562 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
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 ...
5
votes
3answers
6k 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
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 ...
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 ...
5
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:- ...
5
votes
3answers
743 views

Why is Row Overflow not working on SQL Server 2005

I have a SQL Server 2005 database with a table that has around 860 columns. Aside from the ID column all other columns are varchar(N) where N is either 100 or 500. Upon inserting a new row (via an ...
5
votes
2answers
594 views

SQL Server configuration / specification recommendations and advice!

I am about to deploy a new server at work as follows: Dell T610 2 x Xeon 2.53 Quad core 24 GB RAM 1 x Perc H700 RAID controller with 1GB Non Volatile memory 8 x 300GB 15k SAS Hard drives I am looking ...
5
votes
2answers
675 views

Resources for SQL Server developer migrating to Oracle

What resources can you recommend for a SQL Server developer wanting to learn Oracle basics? I am looking for a comprehensive whitepaper or blog post describing differences between these systems and ...
4
votes
1answer
112 views

Preventing full backups for unmodified/slightly modified databases

Is there a way to either: ensure that unmodified databases are skipped during SQL backups, or even better choose between DIFF or FULL backup depending on the percent database changed since last ...
4
votes
1answer
84 views

Tempdb change settings and recommendation

In my production database I have below configuration in TempDb and needless to say I am facing a lot of IO issues on the drive . So, as per recommendation I am planning to change it Change #1 ...
4
votes
1answer
215 views

SQL Server 2008 R2 Readable Mirror

I know that SQL Server 2012 and 2014 offer AlwaysOn Availability Groups which provide this functionality, but I'm still stuck on SQL Server 2008 R2 for a while. I recently came across this AWS ...
4
votes
2answers
3k views

Migrate all databases from one SQL Server 2005 to another

We have a production SQL Server 2005 server on which we are hosting 200 databases. We want to host these databases on our new server. On new server SQL Server 2005 is installed and is ready. I want ...
4
votes
1answer
431 views

Differential/T-Log LSN question

So, this is now my third question on this topic. I just keep finding more and more than i want to know and understand about how FULL recovery works. We transitioned a database to FULL yesterday by ...
4
votes
2answers
10k views

What is the use of “Backup set will Expire:” option while creating Backup Database task in Maintenance plan

While backing up the database, I would like to use "Backup set will Expire:" option to delete/overwrite old database backups. I am not able to use this option. Any advice on how to use "Backup set ...
4
votes
1answer
166 views

Splitting SQL Server tempdb to use a second drive when first drive is full

My SQL Server instance has a small 32 GB SSD which is mostly unusedw while the remaining drives are SAN storage. I considered moving my tempdb to the SSD but my fear is the odd occasion when the ...
4
votes
1answer
650 views

Do SQL Server Views update their data types automatically based on what tables feed them?

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside. In my ...
4
votes
4answers
12k views

How to view an encrypted view or stored procedure

I am working on a third party database. When I try to view the definition of a view by right clicking, CREATE TO and then to NEW QUERY EDIT WINDOW, I am getting an error: This property may not ...
4
votes
3answers
398 views

SQL Server- Table partitions on SSD

I have a table in my database that has about 250m rows. I'm new with databases but I have read up about partitioning the table to increase performance. One of the important points that I seem to ...
4
votes
1answer
6k views

Is ASYNC_NETWORK_IO wait type anything to worry about?

In looking at the list of sprocs that take long time to execute, one stands out as causing the most wait. However, most of that wait (81%) is ASYNC_NETWORK_IO and I know why: the sproc transfers ...
4
votes
1answer
2k views

SQL Server 2008 R2 Upgrade (Express to Standard) fails. No “Select Instance”

Hello there, i got stuck in the progress of upgrading 3 systems from SQL Server 2008 R2 Express Edition --> SQL Server 2008 R2 Standard Edition Problem: The Option "Select Instance" in the ...
4
votes
3answers
823 views

Why is SQL Server 2012 Recommending This Index Be Created?

I have the following query: Select SubId, Max(ReadTimeLocal) From dbo.PanelWorkflow Where ProcessNumber IN ...
4
votes
2answers
3k views

Log sizes unexpectedly large: log_reuse_wait_desc == “log_backup”

I've been reading posts and manuals all day, I need help. My problem: transaction log sizes after data and transaction log backups will not shrink, and grew to be unexpectedly large. The system is ...
4
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 ...
4
votes
1answer
505 views

Cannot Delete FileStream Group from Sql Server 2008

We have migrated all our varbinary(max) column data to Azure Blob Storage and so we want to remove the old filestream columns that remain in our Sql 2008 database and the filestream filegroup but when ...
4
votes
2answers
1k views

Is there a difference in performance between @date and getdate()?

Usually I use the getdate() function in my where clauses to go back in time. Something like: DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE()) Will SQL Server 2008R2 perform faster ...
4
votes
1answer
449 views

SQL Server 2012 indirect checkpoint

I am trying to understand where to draw a fine line for Indirect Checkpoint that was introduced in SQL Server 2012. Based on my understanding SQL Server does checkpoint to flush dirty pages out to ...
4
votes
1answer
997 views

SQL Server 2008 query planner failing after index drop & recreate

Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
4
votes
1answer
4k views

ASPState database locking and growth problems

We use an ASPState database to persist .NET Session state on a SQL Server 2005 cluster. We are seeing some strange behavior during peak periods The DeleteExpiredSessions proc is run every minute ...
4
votes
2answers
8k 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 ...
4
votes
1answer
5k views

Analysing SQL Server Network I/O

A frequent request I get from clients is to help figure out what queries or clients are making the largest contribution to SQL Server saturating an Ethernet link. Ideally I'd look at some counters ...
4
votes
1answer
929 views

Where are queries against a linked server actually processed?

How is a query against a linked server processed? Does the linked server use its optimizer when it receives a query from a remote source? There are a few scenarios about which I'm wondering: Scenario ...
4
votes
2answers
196 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 ...
4
votes
2answers
9k views

A Script to insert dummy Data in all tables of Database

I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ...
4
votes
5answers
3k views

Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

Is it possible to have SQL Server 2008 R2 Standard automatically execute a stored procedure in any given database that is restored or attachced to the instance? I've got close to a solution by ...