New answers tagged

1

Because SQL Server released the memory back to the OS, this means the memory pressure was external, so you aren't going to find the reason within SQL Server. Your answer is going to come from outside SQL Server and I know of one tool that can track the memory allocations of the OS. Rammap https://technet.microsoft.com/en-us/sysinternals/rammap.aspx


1

Your plan sounds OK to me. I have written a detailed answer describing each step at upgrading from lower version of sql server to higher version. To help automate most of the process use PowerShell dbatool written by Chrissy LeMaire [migrates over 25 components including databases, jobs , logins, etc]. Here is a small video as well to get you started and ...


4

Will changing isolation level to Read Committed Snapshot Isolation (RCSI) help here? This is not a straight forward change and it comes with additional tempdb penalty. I would not suggest you to just change the isolation level to RCSI without properly testing and seeing benefits in your environment. Trust me, this is a sledge hammer approach. We ...


0

Use Kenneth Fisher's scripts to identify which roles have these rights. I assume once you identify the roles in question, you want to get a better idea of what they're doing. To script them out, feel free to use a script I put together, here, which will fully script out the database role definition.


3

We now have a table with over two billion rows and 700GB of data. It's a small number of columns, one being a 256 char varchar used to store serialised blob data. This is now of a size that when people try to insert, delete and update to it (via the app) users occasionally lock each other out. This can only get worse as time goes on. Why do ...


-2

Take Backup of your database. Change Recovery Model of database to Bulk_Logged Identify Identity or datetime value, before which you want to delete data SET ROWCOUNT 10000 -- SET TO DELETE SMALL CHUNK OF DATA AT A TIME WAITFOR DELAY '' --DEFINE DURATION TO AVOID INTERUPTED/SLOW PERFORMANCE OF APPLICATION QUERIES DELETE FROM TABLE_NAME WHERE ...


6

To understand the jargons, Aaron Bertrand has a #BackToBasics blog post Also, refer to this decision matrix for TLS 1.2 support or MS KB - TLS 1.2 support for Microsoft SQL Server 10.50.6220 => SQL Server 2008R2 SP3 GDR 10.50.6529 => SQL Server 2008R2 SP3 QFE From the decision matirx, if you dont want TLS 1.2 support, dont install 10.50.6542. TLS 1.2 ...


1

In order for SQL Server services to start as part of a Windows Failover Clustering Service, quorum needs to be attained by at least one node in the cluster. If a two-node cluster has one node down, that node cannot contribute to the quorum, so some other method is necessary for the node that is up to attain quorum. Quorum is essentially a set of votes to ...


1

This is a bit of a fishing expedition but here would be my troubleshooting process assuming you don't have your own stats to help clear things up. Check the history of database size and compressed backup size from your msdb catalogs. Have they changed over the past few weeks. Sometimes an application will go bezerk and fill a database with garbage; what ...


1

Put this at the top of your script: Add-Type -AssemblyName System.Data Yes it's part of the OS, and no this is not normally required, but there must be some issue from the combination of your OS version + PowerShell version + SQL 2008 R2.


16

Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong? At the least it is incomplete and potentially misleading advice (I didn't bother to read the whole article). If you're going to read stuff on the Internet (including this), you should adjust your amount of trust according to how well you already know and trust the author, but always ...


0

Run the following command to track the corrupt pages: SELECT * FROM msdb.dbo.suspect_pages Review the suspect pages. Run DBCC CHECKDB command to check the inconsistencies of the database. Please check the PAGE_VERIFY CHECKSUM option and turn it on. Note: Also check the hardware of your machine.


3

Here is a starting point. The first query will give you a list of roles with either INSERT or UPDATE permission. From there you will need to look at the permissions being at the DATABASE, SCHEMA, or OBJECT level and decide if they cover the objects you are interested in. Check the state_desc column to make sure it is GRANT (it probably will be) as DENYs ...


3

Well, I did something similar to Steve, but with no need for a #temp table or recursive CTE. DECLARE @BigString nvarchar(max) = N' /* CREADO POR : Wxxwww wwwwww */ /* FECHA CREACIÓN : 10/12/2015 */ /* DESCRIPCIÓN : ...


1

I didn't spend a lot of time on performance with this one, but assuming you are ready in some kind of header info, this code should work for you. Parse the string out lines based on a line feed (dump to temp table) Find the largest line with the pattern you are looking for. Read the string from the previous end line position. Code: DECLARE ...


4

Sure, you can generate the list of years in dynamic SQL, but I suggest passing them as numbers (just one less thing that can go wrong if people pass in bad data, and I'm sure you didn't really mean to hard-code the values inside the procedure, defeats the purpose): DECLARE @year1 smallint, @year2 smallint SET @year1 = 2014; SET @year2 = 2016; DECLARE ...


10

I wonder if this imbalance of the number of rows maybe a result of the Timeout Almost without exception, no. An initial cardinality estimation is performed before optimization begins. Subsequent optimizer transformations may require new estimates to be computed. There is no general rule to say which of the estimates will be more "accurate". See this ...


3

Ensure you have a valid backup; hopefully it will be from prior to the corruption, but not so long ago that the data isn't useful. You should set this aside in case direct repair isn't possible and you need to recover data from the backup. The documentation explains what to do to correct the problem - you can try the REPAIR_REBUILD option, and if that ...


0

I've never had a corrupt page before. Can you provide any information from the dbo.suspect_pages table? Please see the below resources: Brent Ozar on Corruption SQL Server Central Corruption


5

Without further info, this is more of speculation but judging on what we have: a table that is quite wide (1.3 to 4.0 rows per page on average) the query that is slow is using: only PWFID on the join condition, two columns Title, SITime on the select list and no other column anywhere (WHERE, HAVING etc.) Then a covering non-clustered index on ...


0

I had the same question, and even though I've been a DBA for a while, I haven't really dealt with replication deeply enough to be completely comfortable with it, so I thought the following resources and guides were helpful: This blog, which provided a good outline of the process. It also reminds us that, if you have a large existing publication, and it's ...


5

Instead of doing an expensive CROSS JOIN plus aggregating a potentially large number of rows you can use this approach: SUM the days including the duplicates and subtract the number of rows with duplicate dates. ;with cte as ( select *, row_number() over (partition by ToolId order by StartDate, EndDate) as rn from ...


5

You can avoid the expensive recursive CTE by using a date table. These are easy to construct and very useful. I used a variation of the code here to generate one. Note: I'm using a temp table for the date here. This is just for the demo code. The permanent solution should use a permanent date table. It should probably also include some/all of the ...


2

I've used Openrowset a number of times for this task. This code will create a table in SQL. SELECT * INTO EXCEL_IMPORT FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Excel\Spreadsheet.xls; HDR=YES; IMEX=1', 'SELECT * FROM [Sheet1$]'); Ideally you want to create the table first and then use INSERT INTO instead of the SELECT INTO. ...


6

I think this will get you what you want. It creates a recursive CTE (common table expression) that generates all the dates between January 1 and March 1. It then joins those dates to the tool rental data, checking to see if each date record falls between the rental dates. This gives you one record per toolid for every day within the range of rental ...


-3

try this SELECT ToolId,SUM(RentalDays) As RentalDaysSum FROM #tmpToolRentalDays GROUP BY ToolId , Cast(StartDate as DATE)


-2

it is better to install the latest SP as it is supposed to include all previous SPs and the CU from the previous SP till this last one Some thoigh, SPs do not include ALL the CUs. If a specific one is fixing an issue you face, then install it. here is a list you may find useful http://sqlserverbuilds.blogspot.ie/


1

You were right to look at the install logs (usually % ProgramFiles%\Microsoft SQL Server\xxx\Setup Bootstrap\Log) and to ignore the name of the source directory; things get mixed up easily. I think you need to dig deeper into the log files there to find something about the version as it installed from the get go. But there's no "Enterprise Evaluation" ...


4

The person is wrong; that is clearly an Evaluation Edition install, not Standard Edition. The SKUs are all on the same ISO typically, so even if you download an ISO that says Standard Edition, you can still choose Evaluation or Express during install. Report builder should not cause this situation; however, it could also happen if you install a Standard ...


1

For me, the solution was that I was using an outdated version of SqlPackage.exe. Old version path: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe New version path: C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe It is installed by Microsoft SQL Server Data Tools (SSDT).


2

If the Database is on the same Instance, then you don't need to use OPENQUERY. But even if the Database is on another Instance, then you still shouldn't need OPENQUERY (as @AndriyM reminded me of in a comment on this Answer): since the query uses JOINs to system Views instead of using the built-in meta-data functions (e.g. OBJECT_ID), fully qualifying the ...


2

Upgrade should not fail (For this reason anyway). As stated in the link you provided, this is an application issue and only if it is directly accessing the Microsoft.SqlServer.Types.dll. The upgrade from 2008r2 to 2014 should work fine.


0

Ideally the mirror db instance acts as the backup for the principal. So you already have a hot standby for the data in your principal in the form of the mirror dbs You cannot take backups of the Mirror databases as they are in restoring mode..obvious..ain't it? As of Principal, you can take continue to take your full backups.


3

No, you still need to take backups including FULLs and LOGs (DIFFs work too, just depends on your set up). Make sure you are taking LOG backups as well, or your log file will get full and things go breaky. All mirroring does, depending on what mode (async or sync), is keep data in sync between your primary and secondary. Synchronous means the ...


6

Yes, given the constraints in the question, particularly that the primary key column is the leading column in the indexes. Also assuming the primary key never changes. Not necessarily. The optimizer can indeed infer uniqueness without marking the nonclustered index unique. Marking the index unique may introduce a Split-Sort-Collapse combination in ...


0

Try to run Repair on Passive node once that come clean try to failover to passive node, once failover test complete successful then try to apply SP3. Also make sure you have SQL Server service account is LOCAL ADMIN on both NODE.


-1

Sub queries can return values from the parent query if no table name is specified for the sub select. If there are nulls in the parent that could be causing you problems. Try changing the select to MIN(notif.notif_dttm)


2

From what I can see you understand the problem perfectly. It sounds like min(notif_dttm) is causing you the grief. You can check this pretty easily by running code like this: SELECT * FROM inc_unit AS notif WHERE notif_dttm IS NULL AND EXISTS (SELECT 1 FROM inc_unit u WHERE notif.inci_no = u.inci_no AND resp_code = 'E' ...


1

So, to test query performance for SELECT statements you'll want to leverage statistics and look at the query plan. Your statistics (using SET STATISTICS IO ON, and SET STATISTICS TIME ON) will show you the number of logical, physical, lob reads and the CPU and elapsed times of the query. When you compare partitioned versus non-partitioned results look for ...


5

Ranges in the pattern syntax use the sorting rules of your collation. Use a binary collate clause so the range is ordered by character code. (I also changed it to LIKE as I find that more obvious than PATINDEX > 0) SELECT * FROM mbrnotes WHERE LINE_TEXT LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_100_BIN2


1

You can't. When you upload data source and report definitions to the report server it does store their information in the dbo.Catalog table which you can see using Convert(Xml, Convert(Varbinary(Max), Content)) and as it's XML you can use XPath to pull out the fields you're interested in. The problems really begin once you modify anything on the SSRS web ...


2

Get SSRS Datasources from ReportServer I would also like to include the 'Connection String' at least How can I display Data Source details with a SQL query? TSQL for getting the Connection String of SSRS Data sources -- Transact-SQL script to get connection string of all SSRS Shared Datasources. /* Let's say you want to move a database to an ...


0

In my case there were two instances of the "SQL server Configuration Manager" in SysWOW64. Only the latest version worked. The other version produced the RPC error message. C:\Windows\SysWOW64\mmc.exe /32 C:\WINDOWS\SysWOW64\SQLServerManager10.msc FAILED C:\Windows\SysWOW64\mmc.exe /32 C:\WINDOWS\SysWOW64\SQLServerManager11.msc WORKED


1

There is corresponding In and OUT for sure and will be in the same day You know that the data is good now .... ;WITH Activity AS ( SELECT serverid ,IN_Time = min(Date_Time) ,Out_Time = max(Date_Time) FROM ( SELECT serverid ,Date_Time , -- pair consecutive rows.. since you know you have ...


1

Row_number() / group by version. Prerequisite: "good" data, every In matches exactly one Out, every pair within the same day. select serverid, dtFistIn = min(dtIn), dtLastOut = max(dtOut), s = sum(datediff(MINUTE, dtIn, dtOut)), dt from ( select serverid, dt, pair, dtIn = min(Date_Time), dtOut = max(Date_Time) from ( ...


1

I tried an analytic function approach. (I don't have a SQL Server instance, but I've used PostgreSQL and basic window/analytic functions - it should work out fairly similarly - I'll try SQLFiddle, if I can get it to work!). It does depend (as do the other solutions) on there being an OFF for every ON. First, I created the table: CREATE TABLE server ...


3

Since you are certain the source data is always reliable, you might use this: ;WITH src AS ( SELECT t1.ServerID , [Date] = DATEADD(DAY, 0, DATEDIFF(DAY, 0, t1.InDate)) , t1.InDate , OutDate = ( SELECT TOP(1) OutDate = al.DateAndTime FROM dbo.tbl1 al WHERE al.Reader = 'OFF' AND al.ServerID = ...


0

The following link should get you R2 Eval: http://www.sqlschool.com/Resources.html It's definitely hard to find, let us know if this doesn't work for you. This has links to 32bit and 64bit versions.


1

Since my comment went unanswered, I have converted it to an answer. I want to do some work if it's an insert and some other work if it is a delete. You could create separate triggers, one for insert, and one for delete: CREATE TRIGGER dbo.Table1_AI ON dbo.Table1 AFTER INSERT AS ... insert-specific actions CREATE TRIGGER dbo.Table1_AD ON dbo.Table1 ...


2

You can use DMV sys.dm_fts_index_keywords_by_document to list the words for a given document, eg something like this: SELECT DISTINCT display_term FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.Users') ) X I had a play with this product a few years ago that does hit-highlighting, it was quite nice but I didn't go past the 30-day ...



Top 50 recent answers are included