Tag Info

Hot answers tagged

4

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


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


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


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

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


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.


2

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


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


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


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


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


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

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


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



Only top voted, non community-wiki answers of a minimum length are eligible