Tag Info

New answers tagged

0

In a general sense, fragmentation is caused by: Data inserted out of order (the order of the index in question). Using ever-increasing values (e.g. an IDENTITY field) is one way to mitigate this as rows will always be created at the end of the index (assuming an ASC sort order). FILLFACTOR is another means of reducing fragmentation when not using an ...


-5

select m.id, min(m.code) as code, m.descriptor from @t as m group by m.id, m.descriptor order by m.id, code The order by is only there to produce the output table in the same order as your example, but it is entirely unnecessary. Revision to include the comment I added below - handling duplicates: select a.id, a.code, b.descriptor from ( select id, ...


3

Of course you could also write this using a CTE: ;WITH t AS ( SELECT id, code, descriptor, min_code = MIN(code) OVER (PARTITION BY id) FROM @t ) SELECT id, code, descriptor FROM t WHERE code = min_code; You cannot say: SELECT id, code, descriptor FROM @t WHERE code = MIN(code) OVER (PARTITION BY id); Because: Msg 4108, Level 15, State 1, ...


1

From at least SQL Server 2008 R2 SQL Server comes with an understanding of HTML. http://msdn.microsoft.com/en-us/library/ms142499.aspx says: " SQL Server installs its own XML and HTML filters. In addition, any filters for Microsoft proprietary formats (.doc, .xdoc, .ppt and so on) that are already installed on the operating system are also loaded by SQL ...


4

Yes but not with row_number(). You can use either the window aggregate min(): select id, code, descriptor from ( select id, code, descriptor, min_code = min(code) over (partition by id) from @t ) as t where code = min_code ; Or the rank() window function (or the dense_rank() one, they'll both work the same for the rnk = 1 check): ...


0

Assuming the availability of an index to provide the requested sort order (SomeColumn ASC) the sample query you posted will in all likelihood take a row lock, even where the index in question is not covering for the query. The query processor is smart enough to know that reading a single row from one end of an index (possibly followed by a lookup to ...


0

Enabling TLS1 using the same IISCrypto GUI resolved it for me


0

The code below will pull back only records matching values in @NumIntAut or field NumIntAut in table variable @TAB_NumIntAut. @NumIntAut will be evaluated first then NumIntAut if the variable is NULL. INNER JOIN ( SELECT ISNULL(@NumIntAut,NumIntAut) NumIntAut FROM @TAB_NumIntAut ) NA ON NA.NumIntAut = ...


1

I just came across a KB article for a bug that may be related to this issue. In FIX: Performance problems occur when database lock activity increases in SQL Server (KB 2926217), one of the symptoms described is that sp_reset_connection may take a long time to complete. The hotfix is included in the following updates: Cumulative Update 17 for SQL Server ...


0

DECLARE @NumIntAut INT = 123 --filter by only one NumIntAut... DECLARE @CodUsu INT = 789 --or more than one NumIntAut from User DECLARE @TAB_NumIntAut TABLE (NumIntAut INT) INSERT @TAB_NumIntAut SELECT [...] INSERT @TAB_NumIntAut SELECT @NumIntAut [...] WHERE CP.NumIntAut IN (SELECT NumIntAut FROM @TAB_NumIntAut)


1

I think you are looking to do something like this WHERE CP.NumIntAut IN ( SELECT CASE WHEN @NumIntAut IS NOT NULL THEN @NumIntAut ELSE NumIntAut END FROM @TAB_NumIntAut ) Assuming that I understand your question correctly. ...


1

To add to Assim's solution, you want to check your SQLAgent and stop and disable all of the jobs. I've had to go in and do this for DBAs who were killing processes only to have another one spin up right behind it. Don't forget to reenable the jobs!


3

update a set a.CHK = 1 from tableA a join tableB b on b.IDA = a.IDA and b.IDB = a.IDB


0

It seems I have found the issue. It was System Center Operations Monitor trying to log into SQL via the cluster name however it was set to use Local System and didn't have access. I changed this to a specific monitoring account and its working now.


0

Replication will automatically move data from one instance to the other and leave both in a read/ write state. Replication will resume if the connection is dropped. Of course when you delete the data from the remote site you don't want to replicate that action to the central site, too! If you choose to roll your own a SQL Server Integration Services ...


5

First thing never set Max Number of Concurrent Connections on server properties page to 1, as this option can even stop administrators to login but still in any case Dedicated Administrative Connection can have access. But if you have changed Max Number of Concurrent Connections value to 1 and now unable to login 1-Restart services using SQL Server ...


0

The solution seem to be to first execute BCP manually, set prefix to 0 in the user dialog. And then use the generated .fmt-file as in this command: EXEC master..xp_cmdshell 'BCP "SELECT my_column FROM my_table WHERE my_id = 11 " queryout "C:\bcpdir\bcpout.xml" -T -f "C:\bcpdir\bcp.fmt'


1

Show Variables is not ANSI SQL, it's MySQL specific, so it won't run across database providers. Reading through that code it looks like it's trying to determine if the database provider will support UTF8. If you Google it, the Show Variables is a MySQL command which returns information about the current database. I think you will have to replace this ...


0

show variables LIKE '%_database' in MySQL return like following +------------------------+-------------------+ | Variable_name | Value | +------------------------+-------------------+ | character_set_database | latin1 | | collation_database | latin1_swedish_ci | | skip_show_database | OFF | ...


2

Sorry for waking up this dead thread, but I hate it when questions remains inconclusive. Anyway, I had the same problem. Apparently SQL server replication saves foreign keys in "dbo.MSsavedforeignkeys", which is where I found my blocker FK. A quick delete dbo.MSsavedforeignkeys where constraint_name = N'FK_TableOnlyInDestDB_MyReplicatedTable' solved my ...


1

Not a complete answer by any means, but couldn't fit in a comment: 1st: Don't stop the service, don't detach the db, don't take it offline. 2nd: Try to bcp the hell out of it: follow @Kenneth's advice for the schema, and for the data too, if it's only a small db; if it's a big one, don't script the data, create files by exporting to them with bcp ...


1

Two thoughts, both revolving around Impersonation: From the description of the issue as well as the error messages, they all deal with errors related to attempts to Impersonate / EXECUTE AS. This attempt to switch the execution context could be the entire problem. Why are you using EXECUTE AS LOGIN = N'CrossDbCertLogin'? That login (and the associated ...


0

MySQL Perspective I once had a client that had a CRM aaplication with this layout 780 databases 162 tables per database See my full description in an old post : More table or more database for better performance? The challenge I faced doing database maintenance was the following 5 procedures: troubleshooting and optimizing slow queries add supportive ...


1

Having been on the receiving end of this multiple times from COTS applications, I would choose multiple databases almost every time. In this case, I would still choose multiple databases over any other, and here is why: !This is for the SQL Server related tag and is not transferrable in logic to other RDBMSs! Standards - each database is setup the same ...


0

You can choose the third option, A table with all records, make a column as CustomerId with bigint datatype and auto-incrementing(IDENTITY), and make that columns as PRIMARY KEY. Now you can think indexes later depending on how you want to fetch data from table and will be fine in terms of performance. This first two options will make too many objects ...


5

Just a word about nested CTEs, they get evaluated each time they are referenced. To put it another way, they do not behave like temp tables, the data is not cached in some special way. So for a CTE referenced multiple times as yours are ( ie once in the UNION, once in the RunningTotal subquery ), there can be a performance penalty. I set up a simple rig ...


0

According to Publish Data and Database Objects in the section Considerations for Publishing - Limitations on Publishing Objects: XML schema collections can be replicated but changes are not replicated after the initial snapshot. I agree, this appears to be a deficiency. As a workaround, you could perform the XML schema update on the Publisher and ...


2

You will get that any time your input file has duplicates. In theory the MERGE command is doing everything in a batch so if you have the same row in the input data more than once it will try to insert it into the output table more than once giving you the error. Either dedupe your input file or use a CTE at the beginning of the MERGE to take care of it for ...


0

To answer your questions in order: You can check for the physical fragmentation of the disk by going to the drive, right clicking on it and selecting tools, then defragment. Or close to that, depending on which version of the OS you are using your specific steps may be different. But you will only be able to see the fragmentation on a volume level, not ...


2

There is one more answer which could be helpful in this case so adding it. Did you tried taking backup of database with continue_after_error Query would be something like BACKUP DATABASE db_name to disk='location' with continue_after_error If it completes successfully its great now restore this backup on DIFFERENT server again using option ...


3

It could also be that you applied the update to SQL Server but didn't apply it to other services (SSAS, SSRS, etc.) or to other components (like Management Tools). Run SP2 again (or SP3!) and see which components it finds eligible for an update - I bet there is at least one.


3

You have two problems. First you need commas between the CTEs. Second you can only have one ORDER BY for a group of UNIONed statements. Just use one ORDER BY at the very end. WITH [ProgressInvoicesPeriodCost] AS ( SELECT ProgressInvoices.ProjectId , ProgressInvoices.PeriodId , SUM(ProgressInvoiceDetails.TotalThisInvoice) AS ...


0

Space Report if object_id('tempdb..#drives') is not null drop table #drives SET NOCOUNT ON declare @ServerName sysname set @ServerName = @@SERVERNAME DECLARE @hr int DECLARE @fso int DECLARE @drive char(1) DECLARE @odrive int DECLARE @TotalSize decimal DECLARE @MB bigint ; SET @MB = 1048576 if @ServerName ...


0

With a standard edition you basically have two options, log shipping and mirroring. Unfortunately, neither of these solutions gives you an automated or managed solution for your third requirement of migrating connections. However, you could base connections on a set of configuration files and swap them out for a "failover" scenario. If you can swing an ...


0

I've seen inserts and updates bog down when they are not batched. Could be a trigger, could be a table variable. The fact you seem to have a tipping point indicates you would do well to just batch your insert at or below your tipping point. Loop while loopCntr < recordCount Begin Insert top 10000 Increment loopCntr End SSIS could also be an ...


2

By any chance are multiple instances of SQL Server running on this host machine? Check the Services console to see if more than one service of SQL Server is running. If so, the newly discovered one may be running the older service pack.


8

Does the backup process USE only the memory that Has been assigned to the sql server (instance)? The memory that you assign with MIN and MAX memory is just for Buffer Pool and for SQL Server 2012 and up, the memory manager has changed. When a backup starts it creates a series of buffers, allocated from the memory outside the buffer pool. The ...


0

kill all the connections to the database (sa) , if you don't have the permission , connect to your DB admin and he will do it .(importent to remember to do the kill statment from "master" database). execute this query: USE master go ALTER DATABASE [Your_database_In_Single_user_mode_state] SET MULTI_USER GO it should solve it . ItzikPaz


0

Bulk writes indeed are fast but will not be logged. If that is not what you want, consider procedure calls with table valued parameters. That way we have realised significant write improvements.


2

Given that you can connect to the database you may actually be in pretty good shape. There are a number of methods you can use that might work but here is one. Create a new database preferably on another disk because the possibility is that your disk is corrupt. Use generate scripts to generate a script for your existing database. Include all database ...


2

The only isolation level that influences writes is SNAPSHOT (and the READ_COMMITTED_SNAPSHOT). Snapshot isolation requires row versioning and row versioning requires extra writes. Read Understanding Row Versioning-Based Isolation Levels. Now about the 'super-fast' part of the question: the 'super-fast' option for INSERT is the bulk insert path. This ...


0

I tried Microsoft SQL Server Migration Assistant v6.0 for MySQL which did most of the work for converting the schema and migrating the data from MySQL to SQL Server. SSMA download page SSMA team blog


0

If you use TRY/CATCH in the calling code and trigger, then the error handling process becomes a lot more predictable and reliable. Without this, an error in the trigger (here caused by RAISERROR) aborts the batch. So you'll never see the error from RAISERROR. See Why TRY CATCH does not suppress exception in trigger for more Note: a trigger is always in a ...


3

An ORM requires information about the first write (SCOPE_IDENTITY or such) to complete the second write. This means 2 (with an OUTPUT clause) or 3 database (with SELECT SCOPE_IDENTITY) calls in general in a client side transaction. No amount of tinkering with isolation levels will eliminate these 2 or 3 calls. If you want more performance, then the best ...


-2

Just enter: SELECT db_name(), name FROM sys.tables; Copy and paste the list.


1

I have now found the problem thanks to SQL Profiler, update statistics with fullscan and your help :). It was caused by a bug in the script that used the built in functions in the ecommerce software to update products but it didn't reused the deliverydates why 545 deliverydates were added every hour and together with bad indexes on that table it slowed down ...


4

There are a couple of possibilities. First if the orphaned user is a windows login/group (type U or G) then no problem. -- If the login doesn't currently exist on the server CREATE LOGIN [Windows Account] FROM WINDOWS; -- Fix the user USE dbname ALTER USER [user name] WITH LOGIN = [Windows Account]; If it's a "SQL Login" (type S) and the login exists ...


-1

If you execute ROLLBACK TRANSACTION within a trigger it raises the error. Why are you rolling back when you have no transaction? I believe that you are doing an INSERT which creates a transaction that lasts the span of the trigger's action. If so, with an INSERT there is an open transaction in your trigger. You can determine this by adding the ...


2

I ended up solving my own question with the below code: --Using the AdventureWorks2012 database in Full mode with log shrunk to 64MB --setup test table USE AdventureWorks2012 go IF OBJECT_ID('TestTbl') IS NOT NULL DROP TABLE TestTbl GO CREATE TABLE TestTbl ( RowID INT IDENTITY(100,100) , ...


0

I had run CheckDB against a 32GB db, and got this error. There were plenty space on the HD. So I increased the size of the tempdb log file to 8MB from 1MB, and closed some applications. Reran CheckDB, and it worked.



Top 50 recent answers are included