Tag Info

New answers tagged

0

After 2 days of my hard times, I found that I have problem in "Permission". Since I am using "xp_cmdshell" which use window log in permission to delete the folder. I ask my server guy to give me "Modify permission" which can delete the folder. It solve my whole problem. Thanks guys


0

This MSDN page states the memory settings only affect query memory: https://msdn.microsoft.com/en-us/library/hh510189.aspx MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant ...


1

When you have a stored procedure that takes a lot of time to retrieve the data in the production phase and you need to make a performance tuning. This specific stored procedure is part of critical function for a webpage. The stored procedure is used daily. My questions are: Is it recommended to do a performance tuning in the production ...


1

"Performance" is a much broader topic than is being assumed here. There are quite a few factors that influence performance: Number of rows in the table(s): All things being equal, the Query Optimizer can choose to do things differently depending on the overall row-count of the table(s) involved in each query. Hence, testing in environments with radically ...


0

@somevalue does not exist in the context of the linked queries. It has been declared above in the main session. Exec or OpenQuery does not see it. Then Your EXEC query will just output the data from the main select. If you want to get the value back in your variable you can update @sql and use sp_executesql: SET @Param = N'@returnvalue nvarchar(16) ...


3

The answers from @Kin, @AaronBertrand, and @DBAFromTheCold are great and were very helpful. One important piece of info I found during testing that the other answers left out is that you need to use the index that is returned from sys.partitions for the given HOBT_ID when looking up the %%lockres%% (via an index query hint). This index is not always the PK ...


3

I would recommend to Update statistics as well as compatibility level to the latest version of sql server (provided your application supports it and you have done proper full business life cycle testing). I have written about full list of steps when moving from lower version to higher version As a side note, please don't use sp_MSforEachtable - Its not ...


1

One more very simple solution: use notepad++. Inside there is very good plugin called Poor man's sql formatter. The drawback is that you have to paste SQL inside the notepad++. But it is free. There is also a web page, but I did not try it.


5

SORT_IN_TEMPDB means that SQL server will use tempdb to allocate the temporary space as opposed to allocating space in the user database whose index is being rebuild. This means you will need less free space in your user database during an index rebuild operation and more free space in tempdb. It gives you better advantage when tempdb is on a different set ...


1

Right off the bat, if you're targeting a column insert ( insert table ( col_1, col_2 ) ), you need to specify an insert value for both columns. From the posted syntax, it appears you're targeting 3 columns ( user_id, group_id, default_group ) but only inserting 1 explicit value ( user_group.user_id ). To start, I recommend commenting out the insert portion ...


2

Your INSERT statement is malformed; you may want to try something like: Insert into usergroup (user_id, group_id, default_group) Select ug.user_id, 1234 AS group_id, ug.default_group from [user_group] ug join [group] g on ug.group_id = g.id where g.name = 'someName'; Replace 1234 in the query above with the id of the group you wish to add for each user ...


0

I do not believe that there is a way to rename it (safely). You will need to rebuild the AG. If you do it during an outage window where you can eliminate or minimize data changes then you should be able to do it without having to perform new backups / restores.


-2

In this scenario system table has always been useful. Please use this query. Select * From SYS.Comments Where Text = 'Stored Procedure Name' Although this table as multiple rows for a single object_id but putting a concat function on the table helps to get a single ROW for a single object_id.


4

The problem mentioned on MSDN has nothing to do with sys.sql_modules or OBJECT_DEFINITION(); they've misinterpreted the problem. What actually happened is they were thwarted by an output limitation in Management Studio, which by default will only show 255 characters and at most 8192 in any output tuple in Results to Text. So first, make sure you change this ...


2

I would use the Object Explorer in SQL Server Management Studio and go from the top, one procedure at a time. Right click on the procedure. Select Modify. Edit the procedure code. List item Press F5 to execute the modification of the procedure. Close the tab. Go to 1. Makes it easy to divide the work between you and your friend that does the same going ...


0

To get a notification when a job fails you can set the notifications from the job itself. Go to the job, then: Open job properties. Check for Notifications options. You will find options like Email, Page etc. Check the email option and set the operator to be emailed. Make sure you create the operator and set its email in the SQL Server Agent. ...


0

You can use DBMail whenever you need to. For example you can create SQL Server Job and send emails to persons which is responsible for the results from that job. Or you can receive alerts from SQL Server using DBMail. From my point of view in general: if you want to make some work inside SQL server and send mail for that you will use DBMail.


0

Given the above comments, I'd recommend going with an SSIS package. If you go with the linked server option you are injecting database dependency between the replication and the MSSQL server objects. Linked servers are also a security liability, anyone with sufficient permissions on the MSSQL database can access the target database on the linked server. ...


3

I believe its just a message and not an error unless and until the task is getting impacted when this message is logged in the error log: We also encountered the same for one of our production server, where windows admin team enabled the SQL Server backup through VSS (Volume Shadow Copy Services). And when this process runs it freezes I/O temporarily to ...


3

1. Why Statistics IO show higher reads than Profiler? No idea, sorry. There are often differences because they are measuring different things as alluded to in the knowledge base article. There is no additional documentation on this that I am aware of. You might be able to infer some things through detailed testing, but there's really no guarantee whatever ...


0

Got all the answers to my question mentioned above. If anyone facing the same problem I would like to point them to the answer : Here's the answer to all the questions why explicit commit takes time : [A commit transaction (FlushToLSN/StartLogFlush) can trigger similar behavior, even when the block is not full, so a commit transaction does not have to wait ...


0

Use this DOS command with OS greater than 2007: ->> WMIC CPU Get DeviceID,NumberOfCores,NumberOfLogicalProcessors For Physical servers DeviceID ~ Socket, NumberofCores ~ CPU's, NumberOfLogicalProcessors ~ Threads. MS now uses a core-based license model. CAL's are out of scope. Search "Microsoft SQL Server 2012 Licensing Guide" if further ...


2

(yeah, I added a new column to a multi-billion row table.) Adding a column to a very large table can have implications but there is a clever way of adding column as well. From : Adding NOT NULL Columns as an Online Operation Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when ...


0

In the scenario of adding a column to a VLDB-class table, it may be worth exploring creating a new table with the new structure and moving records from the old table to the new table in small ranges. It'll keep the individual transaction size small so the high-water mark for the Tlog in Simple recovery would be relatively low. You can't avoid ACID ...


1

A quick-and-dirty way to get this information is to run something like this query while the query you wish to observe is still running. select st.text, r.wait_type, r.wait_time, r.wait_resource, qs.creation_time, qs.* from sys.dm_exec_query_stats qs join sys.dm_exec_requests r on r.sql_handle = qs.sql_handle cross apply ...


-1

The problem is apparently not related to just SQL 2012; it has been reported on older versions also. One guess is that your copy of SQL Server is probably corrupt. You could try downloading from a different source and checking if the issue repeats. The new SQL Server download might have all the required files in good condition, and save you the headache of ...


0

In place of your query, try this: SELECT * FROM dbo.Analysis where FREETEXT(dbo.Analysis, 'historic year-on-year exchange rates forecast yearon-year exchange rates'); or this: SELECT * FROM dbo.Analysis where CONTAINS(dbo.Analysis, "historic year-on-year exchange rates forecast year-on-year exchange rates"); Also, the hyphens may be causing ...


1

Just add it as you would normally do on the primary node. Make sure that all the nodes have the same disk configuration and enough disk space.


1

And, I was wrong. There was a connection open to the database. I thought that switching to "master" and exiting all other queries would close the connections to the database, but I thought wrong. Even though I had just attached the database by the MDF file, generating the script (or something else) created another connection. I used the following commands ...


4

The script is blocked because it cannot obtain exclusive access to the database (technically it is not executing, it is waiting). I think it's in Microsoft's best interests to not allow you to drop a database that is currently being used, so I wouldn't really blame them for making this hard. What you need to do is not only ensure you run your drop from ...


2

Decimal and whole numbers are encoded very differently in varbinary. Decimals need more space. Try: SELECT CONVERT(VARBINARY(32), 1), CONVERT(VARBINARY(32), 1.0); As for your ultimate goal, storing whole numbers as varbinary to save space, I think you've answered that question yourself - not worth it.


3

I think you're trying to solve for the wrong problem. As I suggested above, I think you're getting different results simply because you currently have different plans (one for the literal and one for the variable). The plan you're currently getting that causes an error is attempting the cast before the rows are filtered out, but both methods could cause an ...


1

Consider using a table-valued parameter to pass many rows of data as a single proc call, or alternatively, bulk copy directly into the table. These techniques will improve insert throughput by orders of magnitude compared to singleton inserts, even if those are multi-threaded. An incremental clustered index will provide the best insert performance against ...


1

Not sure if you have checked this; but there is an option in sp_addarticle called @identityrangemanagementoption. If set to manual, then it "Marks the identity column using NOT FOR REPLICATION". Pls check if this is the case. Ref : https://msdn.microsoft.com/en-us/library/ms173857(v=sql.110).aspx


0

This is easiest to explain if you think about having multiple Availability Groups in your environment rather than just one. Each AG listener would have its own name and IP, and it could be failed over independently to either physical node. In that environment, there are a few different parts: The physical nodes - each of which has its own IP address Each ...


2

Sadly, sys.dm_hadr_availability_replica states is not a reliable indicator of replica health. Here's the Connect item on one of the bugs we've run into where that DMV stops refreshing - note in the comments that log_send_queue_size in the DMV sys.dm_hadr_database_replica_states shows 0 even when there's log data to be sent. Note that the Connect item is ...


0

If you have access to SQL Server Management Studio, then attempt a logon to MIKE-LAPTOP with your provided credentials. Can you connect? Any errors thrown? That will eliminate the possibility of password/permissions. You mentioned that there could be multiple SQL instances. Depending on configurations, you may need to specify the instance name and/or ...


0

I found this on stackoverflow with the same issue. The solution there solved it for me. (Rename the Filestream Directory Name to something unique.) http://stackoverflow.com/questions/29777357/sql-server-2012-filestream-an-error-occurred-during-the-drop-table-operation-o


3

Alternatively, SELECT MAX(LEN(column)) FROM table; would work equally as good (if not better as it should eliminate the Sort operator)


4

You can achieve this with this code snippet: SELECT TOP 1 LEN(column), column FROM yourTable ORDER BY LEN(column) DESC Just replace column with your column which holds the nvarchar(100)


1

Try this: INSERT dbo.newtable( name, department, Salary ) SELECT name, FirstName, Lastname FROM ( DELETE dbo.oldtable OUTPUT DELETED.name, DELETED.department, DELETED.Salary WHERE ID IN ( 1001, 1003, 1005 ...


0

Sorry - don't have enough rep yet to comment, hence the answer: Surrogate keys by definition have no meaning - see wikipedia and here Also the book Pro SQL Server 2012 Relational Database Design and Implementation by Louis Davidson etc. says "A surrogate key can uniquely identify a row in a table, but it has no actual meaning with regard to that table ...


0

Jon, One possible option with the statement you posted would be to put an explicit begin transaction before the first delete statement and a commit statement right before the set of the @LargestKeyProcessed variable. This would commit the delete before you move to next loop iteration and should help with the log file as it's breaking the batch up into small ...


2

What I've been using is Adam Machanic's Who_IS_ACTIVE to track the stats for slow running queries. The prod uses the DMV's and will track the stats as mentioned. One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect ...


2

I can confirm that as SQL 2012 SP2 CU7, which has the TLS 1.2 support for SQL 2012 from CU6, you cannot disable TLS 1.0 at the server level and be able to connect to SQL server using an un-encrypted management studio connection on an instance which does not force client encryption. This is on an instance that does not use TDE or other certificates. I will ...


5

Microsoft has recently revealed (without a lot of fanfare) that they will be investing in TLS 1.2 and phasing out SSL. It should be relevant to all editions of SQL Server. Whether this move will affect all existing versions, just 2014 and above, or just 2016, remains to be seen. The quote below seems to imply at least 2014 will be part of the work - and I ...


0

Any solution to this yet? I'm hitting the exact same problem on a couple of servers, but not on others. I can restore the log backups "with norecovery" just fine, but if I try to do them "with standby=..." it fails on the 2nd or 3rd log.


2

You are doing the entire delete in one transaction that will possibly bloat your transaction log and it will crawl like a sloth. There is a much better way of doing a delete - break it into chunks or you can do deletes using Careful Batching. Also, its always a good habit to refer objects with their schema.


2

You can't parameterize column and table names, sorry. You'll need to do this the old way: SET @tableCreateSQL = N'SELECT ' + QUOTENAME(@ID1) + N' INTO dbo.' + QUOTENAME(@StageTable1) + N' FROM dbo.' + QUOTENAME(@StageTable2) + N';'; Also see: Protecting Yourself from SQL Injection in SQL Server - Part 1 Protecting ...



Top 50 recent answers are included