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)

-1
votes
1answer
700 views

How can I detect which indexes are being rebuilt offline and how long?

The rebuild of indexes takes appr. the same 55 min. with both "Do not rebuild indexes and "Rebuild indexes offline" "For index types that do not support index rebuilds" with "Keep index online while ...
0
votes
1answer
2k views

Why are timestamps not always increasing with concurrent inserts?

I'm seeing some unexpected behavior with timestamp (rowversion) columns . I created a test table: create table Test ( Test_Key int identity(1,1) primary key clustered, Test_Value int, ...
35
votes
4answers
22k views

Clustering vs. transactional replication vs. availability groups

Assuming you need to make sure your application that relies on SQL Server 2012 as its database backend is available around the clock, even if one server machine fails. As a developer and not a DBA, I ...
6
votes
1answer
5k views

Error 18456 error severity 14 state 5 SQL Server 2012 SCCM2012 SP1

We had a primary site for SCCM 2012 SP1 running fine. I then installed CAS for SCCM on another server. The prerequisites required me to change the collation on the existing server. I did this and ran ...
7
votes
1answer
3k views

Release unused space from SQL Server database [duplicate]

We have deleted a large part of the data from a two-digit terabyte sized database, and would now like to release some of the space back to the operating system. The database is currently about 80 % ...
3
votes
1answer
1k views

Why is the “The package can not be saved to the file system since its protection level is server storage” error?

When I execute dtutil /sourceserver localhost /SQL "\Maintenance Plans\MaintenancePlan_daily" /copy file;.\MaintenancePlan_daily.dtsx from the answer to my question "Any way to import-export-...
5
votes
1answer
803 views

Dropping large clustered primary key fills up tempdb

Due to one of our tables growing outside of the range of an INT, something that wasn't predicted when the DB was produced, we're need to change the PK field to a BIGINT instead. We are however ...
1
vote
1answer
2k views

How Should TCP/IP be configured to allow an external application access to SQL via the same port #?

My SQL Server instance is hosted on an internal network server (10.x.x.x address). It's a named instance currently using dynamic ports. An external application at a third-party hosting location ...
6
votes
2answers
11k views

Access Denied error in CREATE DATABASE … FOR ATTACH

I'm attempting to attach a copy of msdb to my workstation SQL Server 2012 Developer Edition on Windows 8.1 in an attempt to mimic the problem detailed in this question where the OP is attempting to ...
7
votes
4answers
1k views

Does restoring database clear buffer cache and stored proc cache

on our development server, we refresh database (restore from production backup) every week. my question is does restore db clear its buffer cache/stored procedure cache from sql memory? Does it have ...
1
vote
1answer
339 views

Recover Jobs from a MSDB

I need to recover the jobs from a MSDB file. I had to fully reinstall a new SQL Server and Windows Server, but apparently I forgot to ever do a MSDB backup. Yup, big blunder. Now I need to restore it ...
6
votes
2answers
30k views

How to hide rows in SSRS report?

I'm trying to figure out, how I can hide those rows in my report where Total Allocation and Total Cost IN BOTH COLUMNS TOGETHER are 0 in SSRS 2008. For example: Total Allocation Total Actual ...
0
votes
1answer
327 views

Shrink Database Did Not Reclaim Much Space

I have a SQL Server (2005 Developer Edition) database that has grown too large for it's drive. Yesterday evening I dropped a large table that was no longer needed and last night I set up a ...
0
votes
1answer
4k views

SQL Query with multiple conditions on date

I want to write a query with condition on dates for below table and sometimes it requires great thinking. StartDtm StopDtm 2013-11-03 00:00:00.000 NULL 2013-...
-1
votes
0answers
13k views

Errors trying to install SQL Server 2012 on Windows 7 [closed]

I am getting the above error message when i try to run the setup file. Microsoft SQL Server 2012 Service Pack 1 Setup The operating system on this computer does not meet the minimum ...
1
vote
1answer
29 views

Are files and file groups included when doing a full backup?

I have a database with 4 files and 2 file groups. According to the size of the files, it's 1TB. However, the backup (non-compressed) is only 350GB. Is this typical? Or, do I need to specifically ...
0
votes
1answer
1k views

What is blocking 'SELECT… WITH(NOLOCK)'?

While following BrentOzar's "How Much is Offline During an Index Rebuild?" I was curious to run: SELECT Quantity FROM [Production].[TransactionHistory] WITH(NOLOCK) while executing a rebuild ...
0
votes
3answers
2k views

SQL Server, how to execute a job against all databases on server?

I'm trying to find the best way to execute a query against all databases on the server using SQLServerAgent or maintenance plan. I have a stored procedure that i'd like to run on these databases, but ...
0
votes
1answer
63 views

Allocating Filegroups and Storage

I have upto 10 databases running on a single SQL Server instance on a single server. I have read about best practice for storage and concluded the following... 1) Transaction Log on own RAID array (...
5
votes
2answers
354 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 ...
1
vote
2answers
79 views

What does enabling containment on server incur?

Having read in a book of Patrick LeBlank "Microsoft® SQL Server® 2012 Step by Step": "Benefits and limitations of contained databases As with most features, there are benefits and ...
3
votes
1answer
3k views

VS2012 setup spoils SQL Server 2012 Data Tools for everybody or just me?

On Windows 7 Prof I have installed SQL Server 2012 R2 with, as I am pretty well remember, client tools. Then, I tried to install Visual Studio 2013 Ultimate but failed. Then I've installed VS2012 ...
0
votes
1answer
350 views

Implementing foreign keys on generalized data model

Suppose the ASCII tree below is a UML generalized concept model. To represent these entities/classes in UML, lets assume [mammal] a super class, and the level 1 classes, [wild] and [domestic] are sub ...
1
vote
1answer
310 views

How is parallelism threshold (seconds) used if query CPU cost is actually unitless

How does the optimizer decide whether or not to use parallelism when the parallelism threshold is specified in seconds (on specific hardware, see below) but the estimated CPU costs are supposedly unit-...
2
votes
1answer
508 views

SQL Server Replication - setting up scheduler issue

We have tasked to set up a scheduler to synchronize data between a publisher and subscriber on daily basis. The subscription was set using windows sync manager and it runs just fine when run manually. ...
0
votes
1answer
1k views

Trigger After Insert is preventing insertion

I have two tables that must have similar information, and one of them can be considered the base table for to other, so in order to have the info in the two tables, I create this trigger: ALTER ...
0
votes
0answers
224 views

Minimizing possible trigger impact

I'm trying to make sure a trigger doesn't cause trouble. (Quixotic, I know!) Is there more I can do to the trigger below to make it bullet-proof? Details: We have a huge table we don't control (...
1
vote
2answers
424 views

Table not dropped correctly in SQL Server Express 2008

I've dropped a table but it is still listed in sys.tables. What's more confusing is that I can create the table again as one user, but if I log in as another user with the same permissions, I can't (I ...
0
votes
3answers
119 views

SQL Server RAID Storage [closed]

I have been reading about recommended SQL Server storage and RAID 5 or RAID 10 controllers have been recommended. On my existing Database server I can see 4 Storage controllers under Device Manager. ...
0
votes
1answer
52 views

Having Issues Exporting to online SQL Server database [closed]

I successfully initiated the export wizard of Microsoft SQL Server 2008 Studio and it was exporting fine from the database on my PC to online server database. But suddenly my internet network went out....
0
votes
2answers
979 views

SQL Server 2012. Update second table based on input from first table

I have two tables in two databases: Table01 in Database01 and Table02 in Database02. Both tables have a column called UserID. The UserID column is created when the user creates a new account on the ...
1
vote
2answers
483 views

What are the primary architectural differences between the backup formats of some of the more common RDBMS' and what advantage do they serve?

Of the systems with which I am familiar, I have noticed that SQL Server's native database backup format is binary and proprietary. PostgreSQL and MySQL allow for a simpler approach, dumping the ...
0
votes
1answer
445 views

sys.dm_os_volume_stats return no result

Version - SQL Server 2012 SP1 CU3. I have been using sys.dm_os_volume_stats for a while and it was working. Recently I have not been able to return any result from this DMF on one SQL Server 2012 ...
10
votes
1answer
46k views

Setting user permissions for different SQL Server schemas

I need to limit access to a particular user, but they still need to be able to see the data in tables owned by dbo. I'm trying to do the following: dbo schema functions as it would normally, has ...
3
votes
4answers
1k views

How to rollback changes made to the database by a specific user?

In mssql, is it possible to rollback changes made by a specific user to the database? For example if that user ran an update query and did not write begin transaction before, is it possible to revert ...
1
vote
2answers
824 views

Snapshotting Database Tables

I have a reporting database (in SQL Server) that takes nightly snapshots of the production data (Oracle) for users to query against for the next day, but now the requirement is getting to be that they ...
3
votes
2answers
387 views

Using Files and Filegroups

I am updating a database to use multiple filegroups. As it stands, it uses just the PRIMARY filegroup. I have read that it is recommended to create a second Filegroup (called 'Data') and mark that ...
4
votes
1answer
552 views

Suggest better design pattern for reverse DNS lookups

I'm working on a project where I need to look up the hostnames associated with IP addresses who were logged making HTTP requests. The lookups currently happen as part of a daily ETL job. The current ...
2
votes
1answer
768 views

Checkdb giving error for master database

When i run dbcc checkdb on master database i get error like Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to perform the operation. Msg 7926, Level 16, State 1, ...
4
votes
1answer
307 views

Memory Utilization

I faced a strange memory issue. Buffer cache hit ratio was at 100%, Lazy writer/sec was at 0 Available Physical memory 112 GB Max memory set 112 GB out of which I could see 85 GB being used from ...
5
votes
3answers
670 views

How do I find all tables in a DB that have no explicit primary key?

A Google search spewed forth millions of hits on how to find tables without clustered indexed, the PK normally being the clustered index of a table. However, a table could easily have a natural key as ...
2
votes
1answer
5k views

Which user does Windows Authentication of a maintenance plan authenticate? before whom, why and for what?

I am having a maintenance plan configured with Windows Authentication being run by an SQL Server Agent job with owner sa Whom does Windows Authentication of a maintenance plan authenticate, ...
2
votes
1answer
527 views

SQL Server Linked Server error and Query Problem

I have two servers running SQL Server 2000 and SQL Server 2005. There is a database A,B in SQL Server 2000. Similarly I have the same in A,B in SQL Server 2005. A,B are name of the database. I have a ...
2
votes
1answer
576 views

Should I run a delete first before I execute index rebuild to a large table about 15 Million records

I need help for a better approach on how to approach this. Currently we have a table that is huge about 15 million records. We plan to create an index on this table however due to large size, we ...
0
votes
0answers
2k views

SQL Server 2012 - The application has failed to start because its side-biy-side configuration is incorrect

When opening Sql Server Management Studio I'm getting the following error message: Then when I have a look at the Event Viewer the following message is displayed related to this: Activation ...
2
votes
2answers
1k views

How to give the least permissions for CRUD-managing and running SQLServerAgent jobs as well as Maintenance Plans?

Follow up to my question "What's the owner of SqlServerAgent job for?" Basically, I am trying to understand how to create a user-login with least privileges for CRUD-maintaining SQLServerAgent jobs ...
0
votes
0answers
791 views

Snapshot transaction throws random conflict. Should I choose something else? [closed]

I've got a problem with my transaction. Currently it runs in IsolationLevel.Snapshot. The action it does, can take some time depending on the amount of data, it has to import. What it does: Create ...
3
votes
2answers
4k views

What's the owner of SqlServerAgent job for?

What is the possible use of owner of an SQL Server Agent job, I wonder? Removed, and left it blank and the job still succeeds
2
votes
1answer
300 views

SQL Server 2005 and Standalone VB application

I'm new to this forum and just want to ask some questions/suggestions on how to manage my database.. I'm having an application with sql server 2005 database as a backend. I want make the ...
12
votes
3answers
1k views

Optimizing a CTE hierarchy

Update below I have a table of accounts with a typical acct/parent account architecture to represent a hierarchy of accounts (SQL Server 2012). I created a VIEW using a CTE to hash out the hierarchy, ...