New answers tagged

0

Here's where I found it: C:\Windows\SysWOW64\SQLServerManager10.msc https://technet.microsoft.com/en-us/library/ms174212(v=sql.130).aspx NOT pleased with this note: "Because SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager does not appear as an ...


2

The script provided by Microsoft in the Knowledge Base article: How to restore the missing Windows Installer cache files and resolve problems that occur during a SQL Server update ...found the missing files and allowed me to advance a bit: Save the script to your desktop Run it using CMD Cscript scriptname.vbs scriptoutput.txt Find all the copy ...


1

The sys.servers data dictionary view will give you the date that the Linked Server entry was modified/created (modify_date column), but not who created it.


4

Given that the script works without the header comment block but fails with it (but only for remote / TCP connections), the issue is most likely due to a parsing error. For example, SQLCMD and SSMS do their own pre-parsing of the submitted script (to break it into batches), and sometimes that pre-parsing doesn't work correctly (e.g. "GO" in 2nd half of ...


4

There are three parts to this Question, so let's start with the "Block on Data Loss" option, dropped Tables, and then dropped Columns. "Block on Potential Data Loss" Option If you enable the "Block incremental deployment if data loss might occur" option: on the "Debug" tab of "Project Properties" using the /p:BlockOnPossibleDataLoss=True command-line ...


1

for exact duplicates I have been using the script below: -- find duplicate indexes -- http://www.sqlservercentral.com/Forums/Topic1359723-392-1.aspx -- marcelo miorelli -- 31 jan 2013 set transaction isolation level read uncommitted ;WITH IndexColumns AS ( SELECT DISTINCT a.object_id, a.name, a.type_desc, b.column_id, ...


0

resolved my problem through a internet script and some self effort. Using Below query, I stored all duplicate Indexes in #Duplicate_Indexes as it was about 1600. SET NOCOUNT ON; DECLARE @First [smallint] ,@Last [smallint] ,@DBName [nvarchar] (128) ,@...


2

sp_BlitzIndex from Brent Ozar will help you find and identify those: https://www.brentozar.com/blitzindex/sp_blitzindex-duplicate-indexes/ After that You will have to check those indexes on your own and decide which of them are obsolete. This isn't a task I would try automating with any kind of script.


0

I found the issue after a long time. It was due to 1 million+ files in the \MSSQL\log folder. After setting up a job that cleared that folder. The failover after SP install worked fine. The solution was confirmed on both this 2008R2 cluster, and the 2012 cluster we had the same problem on.


8

Why are you not getting minimal logging? I have found the Data Loading Performance Guide, which you reference, to be an extremely valuable resource. However, it is also not 100% comprehensive, and I suspect that the grid is complex enough already that the author did not add a column Table Partitioning to break out differences in behavior depending on ...


0

You would have to do some digging but one way I can imagine is the SELECT is pulling data pages into cache from disk and subsequent SP runs are just getting their data straight from the buffer pool without having to hit disk.


2

Create a function like this in your DB: create function dm_db_index_physical_stats_tvf ( @db_id int ,@object_id int ,@index_id int ,@partition_number int ,@mode int ) returns @results TABLE ( [database_id] [smallint] NULL, [object_id] [int] NULL, [index_id] [int] NULL, [partition_number] [int] NULL, [index_type_desc] [nvarchar](60) NULL, [...


2

If you are in 80 compatibility mode, you need: DECLARE @dbid int = DB_ID(); SELECT t.name, i.name, ps.avg_fragmentation_in_percent FROM sys.tables AS t -- why A??? INNER JOIN sys.indexes AS i -- why B??? ON t.[object_id] = i.[object_id] CROSS APPLY sys.dm_db_index_physical_stats (@dbid,NULL,NULL,0,DEFAULT) AS ps -- why C??? WHERE ps....


0

Different servers, different solutions. You should clarify the database system, version and the error message you got. You may have a look at the merge command depending on the database system you use.


1

I've had that problem for only on query: 1. Scheduled job was looking in query cache for particular query. 2. When query is found it looked for bad pattern in plan. For instance: Index Seek for index XYZ. 3. If bad pattern is found, that query will be automatically deleted from query cache by it's plan-handle using DBCC FREEPROCCACHE


0

Maybe, instead of creating the new database, restore the real database to a new database and delete the newest, 12-13 month data. Then in your real database delete that data that isn't contained in your just created archive area. If large deletes are a problem, maybe you can just delete 10K or larger sets by way of script to do it. Your partitioning tasks ...


3

I would not worry about the 100% The big number are the big number A lot repeats so start optimizing just one This is just a subset of your query DECLARE @date SMALLDATETIME SELECT Reffd AS NAME , ( SELECT ( ( SELECT count(*) FROM [cal_reg].[dbo].[customer] WHERE upper(Reffd) = upper(main.reffd) ...


7

The sum of operator costs is more than 100% in execution plan is a known bug and is closed as by design ! Also, AaronBertrand filed a similar bug - SSMS : Execution plan sometimes exceeds 100% If you want to understand how plan costing works .. Paul White explains it at his best here. From the query processor team - What’s this cost? General guidelines ...


0

There is but it would be a bit painful to implement and might be granting more access than you think. It will depend on what level of access you want (read only, or ability to manage services). This is mostly a guess for SSCM but in Window Server 2003 you used to use "SUBINACL" command line to grant access. I believe this is still availble in higher OS but ...


1

sa and .\SQLAdmin were linked. No they are not linked. sa - is sysadmin which is a special high privileged SQL login and is disabled by default. .\SQLAdmin - this means that you want to log in to your local machine having database engine installed. . means local machine and \SQLAdmin is the instance.


5

You just need SELECT Location, Reference, Year, Int1, Int2, ROW_NUMBER() OVER (PARTITION BY Year, Reference ORDER BY Int2 DESC) AS [Id] FROM Value To give your desired results (online demo). In the event of tied Year,Reference,Int2 this will arbitrarily assign a sequential numbering ...


2

I have three suggestions Look into your Data Warehouse designs. It should be optimized to let ad-hoc queries run relatively quickly. Analysis Services can help with this. Off load DW queries into no busy hours. I find this works really well for companies I have worked at. One should not run "big" queries when most of your users are accessing the system. ...


0

to decide whether a full backup needs to be taken or a differential backup can be initiated, I would like to know exactly if the db uid and the backup's uid match - only then I want to create a differential backup, otherwise first create a full backup. The closest you can get is to use sp_SQLskillsDIFForFULL - from SQLSkills.com which will tell how much of ...


1

The core problem is that your query can potentially match on two different procedures in two different databases that happen to have the same object_id. While there isn't a reliable way to reproduce it on demand, I've seen it enough times to know it is common, and your query needs to protect against it. You can do that with a WHERE clause: WHERE s....


8

We have recently implemented TDE along with AlwaysON in Production running SQL Server 2014. In our application, we noticed a slight (1-3%) increase in CPU utilization. Your environment is different, so do a thorough load testing with some realistic PROD datasize. Takeaways from embracing TDE : Backup compression and TDE does not go hand in hand i.e. You ...


2

We have all been there. It sounds like you don't have a specific question, but just need some help getting started. You need to learn about the sys tables available in SQL Server and the DMV's. They are insanely useful. Ones that will be helpful in your case: sys.procedures - gets you a list of your sprocs sys.object - gets a list of all objects ...


6

What would the syntax be to write an update statement to update the null values for fields power1, power2, power3 with the values already listed in the table? I take this to mean that each company's nulls should be populated based on the values taken from the one populated entry that, as you say elsewhere in your post, each company has. So, in pseudo-code, ...


1

This may be a good candidate for Biml. One approach would be to create a reusable template that would migrate data for a single table in small date ranges with a For Each container. The Biml would loop through your table collection to create identical packages for each qualifying table. Andy Leonard has an intro in his Stairway Series.


2

OK, Here's my update based on your newly added info. The merge statement in the trigger will insure that newly added records will be inserted into hydrants. Otherwise, records that exist between inspections and hydrants will be updated in hydrants--when inspections is updated. You can see when you run the insert and update (after the table and trigger ...


5

SSMS always truncates long strings unless typed as XML in which case you can set it to allow unlimited. The workaround I usually use (from here) is below SELECT (SELECT YourTraceDataColumn AS [processing-instruction(x)] FOR XML PATH(''), TYPE) FROM YourTraceTable This adds a few leading (<?x) and trailing (?>) characters but otherwise leaves the ...


-1

If I understand what you're wanting to do correctly, I believe about the easiest way for you to go about it would be the UPDATE statements below. At least assuming that you want the same values for power1, 2, & 3 in every row. If you have a variety of combinations that you want to use and have a script for your inserts handy, I would just DELETE FROM #...


5

You cannot restore a SQL Server 2000 database onto a SQL Server 2014 instance. You need to restore to SQL Server 2008 or 2008 R2 instance first, then take a backup of that database which you can then restore to 2014. There a lot of features from SQL Server 2000 that have been discontinued in SQL Server 2014. See this list for the details about what ...


1

Follow this article and use upgrade advisor.


1

This will be faster than setting by hand if you have many companies. If there were more fields than 3 or 4 I would consider building a dynamic loop. Update R1 Set R1.power1 = R2.power1 From #Rentarious R1 Left Join (Select * From #Rentarious Where power1 Is Not Null) R2 On R2.C1 = R1.C1 Where R1.power1 Is Null Update R1 Set R1....


1

You can SET multiple fields by delimiting each with a comma. You'll just need to specify the record based on some unique pattern, in this case c1 and yryryryr. UPDATE #Rentarious SET power1 = 'Red' , power2 = 'Blue' , power3 = 'Green' WHERE c1 = 'Build Blocks' AND yryryryr = '2012';


1

To contribute a little more to this, there is a post on dba.stackexchange.com that explains this situation a little more: SQL Server Max Memory not limiting use of RAM. Also, in the answers on that question, Mark Storey-Smith recommends reading Brent Ozar's article "A Sysadmin's Guide to Microsoft SQL Server Memory" to further one's understanding on how ...


0

Fix for my this question was to apply .NET framework 4.6.1 ADO.NET - SqlClient (.NET Framework 3.5/.NET Framework 2.0 SP2) from this link https://support.microsoft.com/en-us/kb/3135244. These fixed the local login and SQL agent service issues. Then the new issue surfaced i.e. unable to SSMS this server from other servers. To fix this I had to ...


2

The problem with using UNION to include the column names is that column names are strings but the fields in the unioned query are not necessarily strings (as you discovered with that error posted in the Question). And you cannot convert names into other types such as INT, DECIMAL, MONEY, DATETIME, etc. I did not have to provide any cast/convert when I ...


6

I had to finally call Microsoft support and just a simple command called DBCC LOG INFO on publisher revealed a possible root cause. I saw more than 8600 VLFs! and this was the cause of that latency. Also, our log file is preallocated to 538GB. Replication was out of sync by almost 19 hours by the time I got follow up call from Microsoft help 4:00pm ...


4

Presumably you are expecting the .csv file to be opened using Excel or some equivalent. I'd recommend wrapping the outputted text columns in double-quotes, such as: declare @sql VARCHAR(1000); Select @sql = 'bcp.exe "SELECT ''"userid"'',''"name"'',''"data"'', ''"data1"'',''"data2"'' UNION ALL SELECT CHAR(34) + CAST([userid] As VARCHAR(MAX)) + CHAR(34) , ...


3

Q. So, my question is, how can I figure out what the root cause of this blocking is? You may find it's one of the old standards: A concurrency issue with the code. A maintenance job doing a rebuild. A user connecting directly to the database for reporting (or Excel!) causing blocking. But it's going to be difficult if not impossible to trace this down ...


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 ...


6

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 recently ...


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.



Top 50 recent answers are included