Questions tagged [sql-server]
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.
34,097
questions
1
vote
0
answers
84
views
How do I manage environment specific connections for SSAS projects?
I am using SQL Server 2012 and I am responsible for implementing release automation for our SQL, SSIS, and SSAS projects.
SSIS projects have project parameters and you can link those to SSISDB ...
0
votes
1
answer
2k
views
Backup SQL Server database on UNC path using SA
We need to backup some database to an unc path, the problem it's that it's a SQL Server instance that has to execute with SQL Server autentication (sa user).
How can we configure the plan task in ...
0
votes
1
answer
92
views
Restore databases from prod to test server with data over X days
We've got a list from app owners to restore say 20 databases from a total of 100 available production server databases onto a test server on weekly basis.
Moreover, they require it in a way that ...
4
votes
1
answer
4k
views
ADSI Linked Server: Why can I query AD in VBA, but not as a Linked Server?
After all the reading and research I've done, this seems like the most logical place to post this question:
Why can I query Active Directory using VBA, but not a Linked Server in SQL Management ...
6
votes
1
answer
2k
views
DBA Tasks ( SQL Server 2008 ) - Recommendation - Maintenance plan
I'm a JR DBA on a big company. I was hired 6 months ago, and I'm learning things really fast (I'm the only DBA here).
The old DBA left servers and SQL Server instances for me, to take care of it.
It'...
7
votes
1
answer
829
views
SQL Server: Create default stored procedure as and when a new database is created
I am using SQL Server 2008 R2. I need one simple stored procedure to be created by default, as and when a new database is created in SQL Server. How can I achieve this? Assume the stored procedure to ...
1
vote
1
answer
365
views
TempDB Best Practices For Shared Storage [closed]
Microsoft recommends separating the SQL Data Files from the Temp Files to limit device contention but what about cases involving shared storage. I have a shared environment where the data and temp ...
0
votes
1
answer
8k
views
How to change the database mode to STANDBY?
I have a "log shipping" from server A(Database Name = main) to server B(Database Name = LS_main) .
I want to take a Snapshot Database From the secondary Database (Database Name = LS_main), but he id ...
4
votes
1
answer
3k
views
How to perform server-side transaction timeout if COMMIT TRANSACTION will never arrive?
How can SQL Server terminate one of its transactions if it exceeds preset timeout?
Imagine the following transaction performed by traveling salesman's SQL client application, which is submitted ...
5
votes
2
answers
2k
views
How can I speed SQLPS connection when I don't have WMI permissions at the server
When connecting SQLPS to a SQL Server, it will try to run some WMI service status queries. In an environment where one does not have permissions for those, PowerShell issues a bunch of warnings, and ...
0
votes
2
answers
248
views
How to list LOGIN + ROLE in SQL Server 2008?
I'm looking for an answer but I can't find it. Just a lot of long scripts.
I need to list Login | Role (sysadmin, dbcreator, etc ) but I can't find a table to join.
I have this for now:
SELECT
...
1
vote
1
answer
121
views
Order of Columns in Index
I have following query which has ON clause on different column, Where clause on different column and Selection on different columns so my question is what should be Order of Columns in Index as ...
5
votes
4
answers
3k
views
How to check if Alter view/stored proc script has run successfully
For the database deployments, I call all the db schema changes in a batch file and run them on the target database.
Sometimes we have scripts for altering the views or stored procs. I'm wondering ...
0
votes
1
answer
132
views
From PostgreSQL to SQL Server (No User/Pass)
Some of you MAY be able to crack a really tough nut...
I have to move all the data from a PostgreSQL (9.1 - Windows) database to SQL Server because the company I am contracting with is upgrading the ...
1
vote
1
answer
189
views
Trigger to apply user mapping to logins on database creation [closed]
Does anyone have any example triggers that apply user mapping on database creation?
0
votes
2
answers
176
views
Can I re-replicate a transactional publication? Should I?
We use a cloud-based SAAS provider as one of our line-of-business applications. An on-site SQL Server 2008 R2 instance in our DMZ is a transactional replication subscriber to a number of publications ...
1
vote
1
answer
150
views
Can I enforce one field being dependent on another?
I would like to know if it's possible to have two fields (in this case bits) in the same table where one is dependent on another. For example Check2 may only be true if Check1 is true.
Note: I'm ...
1
vote
1
answer
604
views
Database which was set up for logshipping is dropped
I dropped an old unused database. Then I found it was set up to be the source database of a logshipping setups. And there are a lot failing jobs in our job running status report now.
How to remove the ...
4
votes
1
answer
489
views
Where has STATMAN gone in SQL Server 2014?
So we've recently upgraded our environment to SQL Server 2014 (Enterprise on CU5) and are having some problems diagnosing performance issues, compounded by the problem that we no longer seem to be ...
2
votes
1
answer
992
views
Update taking too long
I am updating a table with 250 million rows in SQL Server, and it's been running for 4.5 days. I'd like to use the computer it's running on for other memory-intensive work, so I'm torn about whether ...
4
votes
1
answer
5k
views
MS SQL Server CPU load goes up dramatically when turning on 2014 features by setting compatibility level
I'm running some Microsoft SQL Server 2014 load testing on a decently spec'ed machine. I'm certainly not an expert on the SQL Server tuning specifics, but with the bits an pieces I've gathered from ...
1
vote
3
answers
8k
views
Run a script for all databases
I need help what statement or function i can use to run a query in all database using one script , i want to run the below script at the same time to retrieve output in all databases
select
...
2
votes
3
answers
9k
views
Tempdb growing, however it has a lot of free space
On a sql server dedicated to a Dynamics CRM database, I've been seeing some odd behavior.
While monitoring growth using the following query as basis, I've noticed tempdb has grown quite frequently ...
0
votes
1
answer
380
views
How to fix my Merge Replication upload order?
I have several Merge Replication instances in production on SQL 2008 R2. One of them has started to behave oddly in that it appears to attempting to upload the children tables before it uploads the ...
1
vote
1
answer
742
views
Alter column opreation failed due to disk space in SQL Server
I am attempting to alter a column on a specific table; however the process is failing because the log file runs out of space once the disk runs out of space.
The size of the table is less than the ...
0
votes
2
answers
585
views
Cannot connect in SSMS with Windows Authentication
I found and added the server name through <Browse for more...> -> Network Servers so it exists and is correct.
What seems weird is that the Password field is empty and I cannot modify it...?
...
0
votes
2
answers
7k
views
Change Tracking cleanup
Is there a way to know when cleanup of change tracking tables occurs? How to check the last occurrence of a cleanup process?
BACKGROUND:
I have Change Tracking on database with retention set to 1 ...
-1
votes
2
answers
7k
views
Incremental backup in SQL Server [closed]
I got a task for backing up my clients data through internet. Client has about 50 GB of data there. So I need to back up the data and pass through internet, but it takes about 8 to 9 hours for this ...
1
vote
1
answer
328
views
Restore database from tape error 15105, cannot open backup device [closed]
I'm using HP Data Protector software to backup my SQL databases. When i try to restore a database from tape i get the following error.
When i click on script, it says this:
RESTORE DATABASE [...
5
votes
1
answer
9k
views
Ola Hallengren Index Optimise Script not Rebuilding Indexes [closed]
I have downloaded Ola Hallengren's script and deployed to master database.
I run it using the below...
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@...
0
votes
0
answers
38
views
Windows Administrator doesn't have permission on SQL Server [duplicate]
My previous system administrator created a SQL Server 2008 instance on my local machine with his domain User.
Now, I cannot create, delete, or view any databases or tables on that instance. Even with ...
3
votes
1
answer
2k
views
Cannot SUM datetime field in SQL Server migrated from Access
I am new here and am new to SQL Server (Express). I used the SSMA tool and it converted Access date/time fields to datatime2. The data contains duration data so there is no need for a date. I want to ...
1
vote
2
answers
981
views
Can't connect to SQL Server
I'm trying to set up Peer-to-Peer Transactional Replication and I'm stuck at "Add a New Peer Node" step - I can't connect to another server.
In server name field I specify serverIP\DBNODE2 ...
4
votes
1
answer
2k
views
Long running DBCC CHECKDB
I am on SQL 2014 Standard doing offline DBCC CHECKDBs (so on a box other than production). For the most part, my process is going pretty quick but for some reason I have this small DB (6gbs) and its ...
5
votes
1
answer
1k
views
Subselect of MAX(Version) takes many minutes, though there are only ~20K records
I have a pretty complicated data model. I can't understand most SQL examples without an explanation of what's being modeled, so I'll try to explain.
Mainlines -> Releases -> Overlays -> Calibrations &...
1
vote
1
answer
3k
views
Error with FUNCTION ( SQL Server 2008 ) - ''Cannot find either column...'
I have this Function:
CREATE FUNCTION [dbo].[fn_xxx]
(
@Nome VARCHAR(100),
@Descricao VARCHAR(500)
)
RETURNS @tbDados TABLE
(
Qtd INT
)
AS
BEGIN
DECLARE @CMD AS ...
2
votes
0
answers
1k
views
When does SQL Server follow the Minimum Password Age policy?
According to this SQL Server 2008 whitepaper, SQL Server should only pay attention to the Minimum Password Age policy when the Check_Expiration (Enforce Password Expiration) option is turned on. Page ...
2
votes
1
answer
120
views
When does a port get assigned if SQL Server is set to TCP Dynamic Ports
I'm trying to determine when a dynamic port is assigned to SQL Server. If the TCP/IP Properties have the TCP Dynamic Ports enabled, does that port get assigned at the time the Server is installed or ...
1
vote
1
answer
83
views
Best place to keep backup scripts
Where is the best place to leave the Database Backup scripts, currently I let them on the Backup Job itself, however I wonder if it's best to leave on a Database
4
votes
1
answer
496
views
SSMS returns results immediately; ADO takes longer
I am running a query that returns ~250k results.
It is basically SELECT * FROM my_schema.my_view (and the view is somewhat complex, and all table references in it are two-part)
I am not passing any ...
0
votes
1
answer
322
views
Join to a Culture Table vs Select Culture ID
I have a query that is executed several hundred thousand times a day. Due to legacy support, the incoming variables on this SP cannot change. Currently it looks like this:
Select id,
name,
...
20
votes
2
answers
24k
views
Why adding SQL Server user to "Perform volume maintenance tasks" improves the speed of database resizing so much?
If I want to create 5GB database with
CREATE DATABASE [test]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'test', FILENAME = N'E:\2012\test.mdf' , SIZE = 5529600KB , FILEGROWTH = 1024KB )
LOG ON
( ...
11
votes
2
answers
92k
views
Optimizing queries for 25+ million rows
I am using MS SQL, and I have to run several queries on the same table on different criteria. At first I ran each query on the original table although they all share some filtering (i.e Date, status). ...
1
vote
1
answer
7k
views
SSIS Merge Join Error [closed]
I have a simple data flow. I'm trying to join two sets of data from two different databases. The TXT database is collation SQL_Latin1_General_CP1_CI_AS. The other dataset (Cost) is coming from ...
5
votes
1
answer
1k
views
Should I use Database Mirroring?
So I have the following scenario on my hands:
I have a product database that runs for a specific app, all inserts into the database come in through three separate connections.
Recently I have ...
2
votes
2
answers
256
views
Agent service account shows as not clustered
One of the SQL Server 2008 R2 instances I administer is running on a two node Windows Cluster (Windows Server 2008 R2 Enterprise)
When I run the DMV sys.dm_server_services, SQL Server Service Account ...
1
vote
1
answer
2k
views
Peer-to-Peer Transactional Replication Load Balancing in MS SQL Server
I'm looking at Peer-to-Peer Transactional Replication and it looks like a perfect fit for my requirements, but there's one unclear part - load balancing (see picture below).
How it is implemented in ...
1
vote
1
answer
105
views
Return list of columns in user-provided subquery
I am trying to fill a ComboBox of all columns from results of a query. That query is submitted by the user on the form, so the program needs to run the query then have a procedure run that extracts ...
1
vote
0
answers
53
views
How to architect Service Broker with three or more systems?
I'm trying to implement a Service Broker architecture for a business process involving three systems and I want to do it in the spirit of using SB as designed.
System 'A' is an accounting system. ...
1
vote
1
answer
154
views
Restoring SQL Server database on another box
I have a SQL Server database that is running these scripts to create backups. It's running full backups once a week, daily differentials, and hourly logs. These are being FTPd to another box where I ...