Tag Info

New answers tagged

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

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.


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


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


1

Probably the easiest way would be to maintain the old OrderID. You can do this even if the new table uses an IDENTITY column; you can say SET IDENTITY_INSERT dbo.NewOrders ON; and override. The next OrderID generated by the app will be roughly the highest "old" OrderID + 1 (keeping in mind that IDENTITY does not guarantee a contiguous sequence for a variety ...


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


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


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


1

The roll-back will likely take as long or longer than the amount of time the query was running. Almost certainly, if you restart the instance, whatever database is affected by the ongoing transaction will need to run recovery, which will take as long or longer than the rollback that is currently happening. Advice? Don't kill SQL Server. Also, killing ...


5

A non-clustered index scan may be chosen in this scenario: the optimizer determines that it is cheaper to scan all rows rather than perform seeks/range scans the non-clustered index is "skinnier" than the clustered index the non-clustered still covers the columns needed by the query (or it covers enough of them and a lookup for the remainder is still ...


0

Your binary representation of the mask should be: 1100 0000 0000 0000 0000 0000 0000 0000 Converting that to an unsigned 32-bit integer would equate to your original calculation: 3221225472. But the affinity mask tasked a signed 32-bit value. In that case, the binary to signed 32-bit value would be: -1073741824. Same bits, just different ...


1

A restore failing on the secondary will have no impact on the primary. You want to monitor your secondary DBs to make sure this hasn't happened.


0

I would suggest that @@TRANCOUNT=0 because [@@TRANCOUNT] Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection. If your INSERT or UPDATE statement is not part of explicitly defined transaction, then @@TRANCOUNT is not incremented. Try in SSMS: SELECT @@TRANCOUNT BEGIN TRAN SELECT @@TRANCOUNT ROLLBACK ...


1

Straight to answer your questions : Can I stop, pause, or cancel the backup after it has started if it is taking too long or slowing things down too much? You cannot pause a backup - either running using Tsql or using sql agent job. You can stop it or kill it using the kill spid command. you have to be cautious as if you kill a job that is already ...


0

I will be assuming that you are using native backups as you have not mentioned a specific product. However, depending on what backup product you are using (Litespeed, RedGate Hyperbac, etc) you may be able to stop a backup but it may not be as simple as clicking stop from within management studio for an Ad-Hoc backup command. For a native backup the engine ...


0

When you drop the index, the database is in the same state as before in terms of indexing. If you want to go back to a previous state then you'll need to get a backup for that time range. Depending on the requirements it's either just the FULL Backup or additional DIFFERENTIAL Backups or even a point-in-time recovery using the transaction logs. But the ...


0

@JeremieGrund - If the database physical file architecture is same( data & log file drive names & locations), then if you shrink the log file on the principal, the shrink command will be sent to mirror and do the same on mirror. So in this way your mirror log file can be maintained.. If the files architecture is not identical then you should follow ...


2

From SQL 2012 onwards, there is the WITH RESULT SETS option, which makes it easy to use the output from the OUTPUT clause with no sticky issues in SSIS not understanding the metadata for the resultset, eg you can use the output from an OUTPUT clause in SSIS Source components, eg OLE DB Source. I set up a simple example using a stored procedure with MERGE ...


5

You can't, ish, is the short answer. You have 3 ish components in a data flow task that can modify data. OLE DB/ADO.NET Destination, OLE DB Command and a Script Component. I'm ignoring Script Component for this answer as it's the swiss army knife of components. If you want to do something badly enough, the Script Component will allow you to. Whether ...


2

SQL Server optimizer does constant folding, when possible. But is not a black-or-white issue, there are many shades of gray. See Compute Scalars, Expressions and Execution Plan Performance or Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation. You also need to read Conor vs. Runtime Constant ...


0

I guess this cuts it insert into trip values ('point1', '10/15/2014 09:10:10') ,('point2', '10/15/2014 10:15:00') ,('point3', '10/15/2014 10:20:00') ,('point2', '10/15/2014 11:10:00') ,('point1', '10/15/2014 12:10:00') ,('point1', '10/15/2014 12:15:00') ,('point2', '10/15/2014 13:15:10') ,('point3', '10/15/2014 ...


1

A filegroup restore can be done on a database in FULL recovery model. There are requirements and consequences of the restore. You must be using Enterprise Edition. The primary filegroup must be the first restore. Filegroups that are not yet restored remain offline. Start reading at this topic in MSDN: ...


1

The following code will restore the database to the desired location: RESTORE DATABASE x FROM DISK = 'Filepathofbackupfile.bak' WITH RECOVERY, MOVE 'logicalDatafilename' TO 'Newfilepath.mdf', MOVE 'logicalLogfilename' TO 'Newfilepath.ldf'


0

Here are your duplicates, then everything else is your non-duplicate. with c as ( select *, row_number() over (partition by PostSur, Name order by ID) as rowID from Aron_Reporting.dbo.Customer_NewSort ) select c.* from c inner join c c2 on c.PostSur = c2.PostSur AND c.rowID > 1 AND c2.rowID = 1 as rowID order by PostSur This ...


1

As part of a different issue I had to give my login the sysadmin role. I haven't seen the problem I described above since then. Yes, it may work now, but I am not happy with this solution, partially because I don't quite understand what was going on. Later edit: As pointed out by Thomas in a comment, here's what's going on: If the database exists, ...


0

This KB isn't directly related but the quote in it is. http://support.microsoft.com/kb/2615182 When Windows Server Backup attempts to backup a disk volume, a Volume Shadow Copy Snapshot is created for the volume. When the snapshot is created any VSS writer associated with the volume is called. If there are SQL Server VSS writer backups being taken ...


0

When all windows user accounts with sysadmin role or sa account are disabled in SQL server then how to access the instance? Below are the steps to solve this problem: Stop existing instance of SQL Server on which all windows user accounts or sa account are disabled Start SQL Server instance in single user mode with following steps 2.1 Open command ...


0

Same problem here, this is how I solved it find running job NAME with this somewhere in command 'Replicate_ASG_Reference_Data' use this SQL: USE master GO SELECT job.job_id,name,command FROM msdb.dbo.sysjobs job INNER JOIN msdb.dbo.sysjobsteps steps ON job.job_id = steps.job_id WHERE job.enabled = 1 AND command LIKE '%ASG%' this SQL will show 1 or ...


1

If you need to change the data type of a column, you'll have to change every data type referencing that column, too. At the database level, you'll have to change any column referencing this column. Since you need to change a PK column, this will affect all other tables where this column is used as a foreign key reference. In all of those tables you'll need ...


0

You can use GROUP BY and SUM of particular column value. SELECT EmployeeName, SUM(Salary) FROM YourTable GROUP BY EmployeeName


1

you can create a table variable(or temp table) and insert all the .bak file metadata in that table and then fetch the row with max value of modified time. DECLARE @FileMeta table (columns nvarchar(200)); INSERT INTO @FileMeta EXEC sys.xp_cmdshell 'dir D:\FolderName\*.bak'; SELECT * FROM @FileMeta WHERE columns not like '%volume%' AND columns is not ...


0

main table for this purpose is msdb..restorehistory SELECT [rs].[destination_database_name] , [rs].[restore_date] , [bs].[backup_start_date] , [bs].[backup_finish_date] , [bs].[database_name] AS [source_database_name] , [bmf].[physical_device_name] AS [backup_file_used_for_restore] FROM msdb..restorehistory rs ...


1

Assuming your largest range will be 366 days (and you can adjust that number), I think this approach will scale better than a recursive CTE or a while loop. This only handles days, because it's not quite clear to me from the question what the output should look like, exactly, when the range is months or years. ;WITH x AS ( SELECT TOP (366) n = number ...


2

This will allow the defaults to show up in the lower pane of the table designer: GRANT VIEW DEFINITION ON dbo.tablename TO [username]; Of course this doesn't give them the ability to make any changes to the table, and in general I discourage the use of the designers anyway. They're littered with bugs and inconsistencies. With VIEW DEFINITION rights, your ...


1

OK, I've refactored (hopefully for the last time) for your new data and to use a calendar table. In this simple demo I create and populate the calendar table (for years 2000 to 2030), then do the day|month|year code. This version honours the time component and will scale well. The calendar table is one I use in my dev. Please note this is intended as a ...


0

This should do what you need. DECLARE @Interval NVARCHAR(10), @I DATETIME, @IntervalMin DATETIME, @IntervalMax DATETIME --- Set Interval DAY or MONTH or YEAR ----------------- SET @Interval = 'DAY' ------------------------------------------------------- DECLARE @Records TABLE (StartDateTime DATETIME ,EndDateTime DATETIME) INSERT INTO @Records ...


1

Until we see the actual code, we can only speculate but for what is worth, I'll do a guess. Assuming that your code involves only a table and its columns (no @variables involved) and is something like: UPDATE tablename SET column_a = column_b, column_b = 0 WHERE (condition) ; It is valid SQL and 100% correct. Correct, meaning that ...


0

You can use REPLACE on the date column in the @PivotColumnHeaders string to build the concatenated column. SELECT @PivotColumnHeaders = COALESCE( @PivotColumnHeaders + ',[' + cast(SystemFullName as Nvarchar) + ']', '[' + cast(SystemFullName as varchar)+ ']' ) FROM System WHERE (@SelectedSystemIDs IS NULL OR System.ID IN(select * from ...


1

Microsoft also has a nice TechNet article, Disk Partition Alignment Best Practices for SQL Server, that discusses Disk partition alignment as it relates to SQL Server 2008. In most cases, 64KB is the best choice. Best recommendation would be to make sure your partitions are aligned, test your I/O performance and if Latency and other specs are not to the ...


1

Use your original query as a derived table and outer-join it to a dummy row, referencing only the query's columns in the main SELECT: SELECT q.* FROM (SELECT NULL) AS d (dummy) LEFT JOIN ( your query ) AS q ON 1 = 1 ; This is almost like a cross join with a single row, except in this case the final output will always have at least one row. If ...


1

Using NOT EXISTS should work, eg SELECT * --!!TODO add column list FROM dbo.CompanyDomains cd WHERE CompanyId = @CompanyId AND NOT EXISTS ( SELECT * FROM @domains t WHERE cd.Domain = t.domain )


0

If you don't want cdc clean up to run, do not run the system stored procedure to enable it. Then the sql agent job won't get created. exec sys.sp_cdc_add_job 'capture' GO FYI - I have observed disabling CDC clean up long term may cause latency issues within your database. (I would assume you might have that issue too)


1

What I'm totally stopped on is I don't see any equivalent of a List is SQL. Is there a way to build all this up in a list? So a list in SQL Server is basically a table. You can use table-valued parameters to pass in your complete list from C#. You basically just create a table type to define that list you are passing in, see the example in the MSDN ...


6

Add a persistent calculated field that contains a CHECKSUM on the 5 fields, and use that to perform the comparisons. The CHECKSUM field will be unique for that specific combination of fields, and is stored as an INT that results in a much easier target for comparisons in a WHERE clause. USE tempdb; /* create this in tempdb since it is just a demo */ ...


1

The big reasons for the "Keep the clustered key sequential" advice is all around insert/update/delete operations and fragmentation. As you make changes to the values (insert new ones out of order, updating an old value that causes it to move, deleting a value) SQL has to move data around on the pages and do page-splits to keep the data in order (the whole ...


0

Because your condition not correct. You change the condition + CASE WHEN @Store = 'MA000' THEN '' ELSE 'AND rtiersS.alias = ''''' + @Store + '''''' END + to + CASE WHEN @Store <> 'MA000' THEN ' AND rtiersS.alias = ''' + @Store + '''' END +


0

I solved - add defaults to parameters and Report Builder can detect columns Its a Report Builder bug - it not ask for parameters when refresh fields IF @Store IS NULL SET @Store = 'MA000' IF @Date IS NULL SET @Date = '19092014'


1

Check out SET CONTEXT_INFO and CONTEXT_INFO(). This lets you set and retrieve 128 bytes of connection-specific metadata (as a varbinary(128) value). So, after the connection is opened, you can do this: DECLARE @ci varbinary(128) = CAST('myhostname.domain.com' AS varbinary(128)) SET CONTEXT_INFO @ci Then in your triggers, you can see if the value has been ...



Top 50 recent answers are included