Questions tagged [sql-server-2012]

SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
2
votes
2answers
2k views

Execute `sp_spaceused` for all tables in all databases

How to execute sp_spaceused for all tables in all databases? i.e. how to execute sp_spaceused inside sp_msforeachdb. e.g: sp_msforeachdb 'sp_msforeachtable'sp_spaceused"?"''
4
votes
1answer
109 views

SQL Server Replication

We had an issue with replication, where a large (7.5 million row) update was done on a 33 million row table. Replication converted that to 7.5 million individual update statements. When my alerting ...
6
votes
3answers
836 views

Capture SQL Server queries without third-party tooling and without using deprecated features?

Is it possible to capture all queries sent to an MS SQL Server, without third-party tooling and without using deprecated features? I'm looking for something similar to the "general query log" in ...
4
votes
1answer
942 views

Buffer Cache Size when Page Life Expectancy Drops

I'm troubleshooting an issue with one of our SharePoint databases, essentially huge drops in PLE which I think are caused by increased I/O activity (I see increased reads, writes and lazy writes at ...
2
votes
2answers
143 views

Usage of NON Clustered Index in SQL Server

I have 2 Index: CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA] ( [Column1] ASC, [Column2] ASC ) CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[TableA] ( [Column3] asc ) Now I have 2 ...
0
votes
1answer
54 views

How can I combine these three queries into one result?

I'm still pretty new at SQL, so I apologize if this is a silly question. I took existing queries for three data points, and I'm not sure how to streamline them into a single output. Clunky query: ...
3
votes
1answer
108 views

Available space differs between sys.database_files and database properties dialog in SQL Server Management Studio

I am looking at this for a few minutes now and could not find a thread mentioning this. I wanted to query currently used space and available space from a database on SQL Server. The numbers I query ...
-1
votes
0answers
27 views

New table in AlwaysON Database [on hold]

Am i able to create new table in database which is in AlwaysON and does the same table will be created in secondary node
0
votes
3answers
37 views

Add windows user to a particular database in SQL Server

I have a windows user who I want to add to just one database SalesDeals on the server. I added them in SalesDeals-> Security -> Users section of the Database and gave them db_datareader ...
0
votes
1answer
44 views

How to Query Which Table Is Inserted Mostly In SQL Server?

I have a database and there is not any trigger which follows the transactions in said database. Now, I have to make a report that shows which table has mostly inserted so far. How can I do that?
0
votes
1answer
22 views

Line breaks handled inconsistently by SSMS

Probably more of a SQL Management Studio question than SQL, per se, but when I run the following simple query: SELECT results = 'Foo' + CHAR(13) + CHAR(10) + 'Bar' I appear to get different results ...
0
votes
1answer
21 views

Defaulting a column to the 1st of next month

I'm trying to find the most elegant means of defaulting a column's value to the 1st of next month. The best I've been able to come up with is this: ALTER TABLE Foo ADD Bar datetimeoffset(0) NOT NULL ...
1
vote
1answer
23 views

SQL Server 2016 - Disparity Between View Definition and View Design

I've run into an interesting issue with some databases I've inherited, and could use some help unpacking it all. The Issue After adding a column to a table, views selecting * from that table are now ...
2
votes
1answer
126 views

SQL PROFILER Replay statistics event interpretation

I tried capturing a replayable trace on a SQL Server 2012 production environment and then replay it against a SQL 2016 test environment. After doing so , I got the following results : Can anyone ...
-1
votes
2answers
53 views

Expand range of values in SQL [closed]

I have a table named "codes" where some of the entries will be as shown below. Code Description ----------------------------- 123[10-50]% ABC 433[10-500]% PQR I have to ...
0
votes
1answer
229 views

When is dm_exec_procedure_stats updated?

I just created a new proc and ran it, but it is not showing up in my query: SELECT schema_name(schema_id) SchemaName, name ProcName, last_execution_time LastExecuted, last_elapsed_time ...
0
votes
0answers
4k views

SQL Server Upgrade issue from 2012 to 2016 [closed]

After many successful upgrade from SQL Server 2012 to 2016 on production, QA & Dev etc server. I got a problem on a production environment. I executed setup top upgrade available SQL Server 2012 ...
0
votes
1answer
57 views

Mssql query problem. Column '' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

There is a problem I have experienced in mssql. ( but I don't have this problem in mysql. ) They know two different engines. but I could not find a solution. Do you think I can fix this error? My ...
3
votes
1answer
56 views

Checkpoints on secondary replica AlwaysOn AG

Setup 3 Node Alwayson cluster - 1 sync and 1 async secondary replica - SQL Server 2012 Situation We are witnessing PageIOLatches when reading from the asynchronous secondary replica. This is ...
0
votes
1answer
109 views

Remove Cluster Nodes after Cluster Object was deleted

While building new 2012 servers to move to we had some change in requirements. We are using Availability Groups on the new servers. Here's where we started. Servers: Serv1a Serv1b Serv2a Serv2b ...
1
vote
1answer
56 views

Deny Delete to An Active Directory Account in Sql Server

I want to deny an AD account to delete any record in sql server. How can i do that? DENY DELETE TO [usergorup\username] did not work. Is there another way?
0
votes
1answer
134 views

Text file report is not generating of Availability Databases other Databases during auto backup through maintenance plans in MS SQL Server 2012

I have setup Automated backup in SQL Server 2012 Enterprise Database backup through 'Maintenance Plan'. I have also configure AlwaysOn High Availability to that server .There is 5 databases in the ...
4
votes
1answer
119 views

Is there any reason I shouldn't delete orphaned database users?

I've inherited a business application which has been around for c. 15 years composed of multiple databases that are all hosted on the same SQL Server (2012 SP4) instance. In all of the databases ...
0
votes
1answer
111 views

Log Shipping Server Restart Problem

I have Log Shipping setup from Primary Server A to Destination B. Server B is both Destination and Monitoring server. Yesterday, we restarted both servers around the same time for patching. On the Log ...
1
vote
3answers
2k views

Grant a database role in current database with permission to execute a SP in msdb

I have created a role in my current SQL Server 2012 database with db_datareader, db_datawriter, and execute permissions. A procedure in the current db calls sp_start_job in msdb. How can I grant ...
0
votes
2answers
664 views

Error cs1567 while installing ms-sql on windows 8.1

I tried to install sql server 2008 and 2012 on my windows 8.1 laptop, but at both times I faced with the same error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the ...
0
votes
1answer
109 views

Why do my backup policies fail when a database's guest user is disabled?

I'm attempting to implement Policy Based Management on a SQL Server 2012 instance also acting as a Central Management Server using the Enterprise Policy Management Framework. The job running the ...
1
vote
2answers
104 views

MS SQL Server Performance Issues After Enabling All CPU's [closed]

My system specifications are; Windows Server 2008 R2 Enterprise SP1 128 GB RAM Intel Xeon E5-2620 (3 processors) MS SQL Server 2012 Standard (11.0.2100.60) There is 3 NumaNodes and 12 CPU's in them. ...
0
votes
2answers
26 views

MS SQL - Create trigger to prevent insert if it doesn't match specific parameters

Suppose I have two tables payments with columns payment_id, batch_id and batches with columns batch_id, batch_type Can I add a trigger to prevent inserts into the payments table when the batch_id ...
-3
votes
0answers
16 views

migrate Prodection DB To New Datacenter without downtime [duplicate]

I need to migrate our centralized Production databases from the current datacenter (SQL SERVER 2012 alwayson) to the new Datacenter without downtime , what is the best practices for this migration ? I ...
7
votes
3answers
32k views

Execute stored procedure remotely using linked server

We have 3 SQL Server 2012 instances, 2 of them we're not db_owner. Some of our stored procedures use DMV and sys views and stored procedures that only work when executed locally. I'd like to ...
0
votes
2answers
156 views

How can I decide Rebuilding Index with DETAILED or SAMPLED on SQL Server

I want to rebuild my indexes and statistics on DB. How can I select this option on production maintenance. SQL Server Version : Microsoft SQL Server 2012 - 11.0.2100.60 (X64) (Build 7601: Service ...
3
votes
1answer
2k views

User defined table type with a check constraint

I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on ...
0
votes
2answers
122 views

Options for structuring multiple associations to a record

This is 101 level stuff and I promise to read a book later, but in the meantime, given the following three tables: Product --------- ProductId (uniqueidentifier) User --------- UserId (...
9
votes
1answer
4k views

What permissions does the service account need to use database mail?

I have an SQL Server 2012 instance running under a managed service account. I've configured database mail with one account and am trying to send a test email, but I'm getting some errors in the server'...
0
votes
1answer
118 views

Fastest way to read data from single SQL Server table into java under single transaction

I need to read a large result set from SQL Server into a java program. I need to read a consistent data state, so its running under a single transaction. I don't want dirty reads. I can split the ...
23
votes
4answers
53k views

Check if a user exists in a SQL Server database

I'm working with SQL Server 2012. I want to check if a user exists before adding it to a database. This is what I have tested: USE [MyDatabase] GO IF NOT EXISTS (SELECT name FROM [...
8
votes
3answers
158 views

XML Query Question

I'm trying to generate a SQL query to get the "DATE" values out of the following SQL XML: I've tried something like this but I don't think I'm understanding the concepts. select xConfig.value('(/...
10
votes
1answer
171 views

Incorrect process utilization from System health extended events?

I have been recently working on understanding the data or metrics being stored in system health extended events event file. We are trying to implement the data collection of performance metrics using ...
1
vote
1answer
295 views

I need to figure out the last time that a function was used by SQL Server. How can I do this?

I work with databases that contain thousands of stored procedures and hundreds of functions. I have found on-line methods to track the usage of stored procedures; however, I haven't seen the same for ...
2
votes
1answer
115 views

Can't create an Availability Group with Powershell when instance uses non-standard port

I am trying to create an Always On Availability Group with PowerShell, something I have done many times before but am hitting an issue now that I am using non-standard ports for the SQL Instance I ...
0
votes
1answer
24 views

Restore previous version of database in sql server

I need your help on restoring previous version of database on SQL Server. In last week I restored database 'A' in DEV server with it's production copy and didn't realize that this database 'A' in ...
2
votes
1answer
54 views

Checkdb issue - Consistency error on two tables at critical database

There was a network activity last night and they were upgrading server switch. Whole network was down and we as a DBA had prepared by disabling all the jobs at DB server for replication and backup ...
0
votes
0answers
22 views

Migrating SQL Server 2012 Web Edition to Azure

I am trying to migrate to Azure I have gone through this article Migrate SQL Server to a single database or pooled database in Azure SQL Database online using DMS, However since I am having SQL Server ...
1
vote
1answer
44 views

How to replace multiple unicode characters

I have identified a couple of unicode characters in my database by means of the following query: SELECT name FROM table WHERE name NOT LIKE '%'+CAST(name AS VARCHAR(1000))+'%' ...with which I get ...
3
votes
0answers
43 views

SQL Server database partitioning purge job failed with an error

We have a SQL Server Agent job that executes package to purge partition file and filegroup older than 25 month. The job was running fine however it failed this month with following error: The ...
16
votes
3answers
4k views

Wrapping query in IF EXISTS makes it very slow

I have the below query : select databasename from somedb.dbo.bigtable l where databasename ='someval' and source <>'kt' and not exists(select 1 from dbo.smalltable c where c.source=l.source) ...
0
votes
1answer
162 views

SQL agent jobs performance is very slow in 2012 after migration from 2005

Index rebuilding agent jobs were working fine on 2005 . Recently we have migrated to 2012 . Now it is taking more time . Because of this other jobs are getting affected . What could be the reason for ...
0
votes
1answer
241 views

Is it possible to support multiple snapshots / store a snapshot / restore from a snapshot to a state with another snapshot?

So, I would like to have a snapshot structure like the following: A snapshot 1 is taken. Some code is run, then a snapshot 2 is taken. Then some more code is run, and the database is reverted to ...
1
vote
0answers
9 views

SQL Vulnerability Assessment Error in PowerShell

I am running Vulnerability Assessments against my SQL Servers and databases using the PowerShell cmdlets Invoke-SqlVulnerabilityAssessmentScan and Export-SqlVulnerabilityAssessmentScan, however, I am ...