Tag Info

New answers tagged

1

One thing to note is that RESTORE is a command, not really a permission that can be granted. Which is why your GRANT failed. In the SQL Server Books Online at https://msdn.microsoft.com/en-us/library/ms186858.aspx has pretty much said the same thing from SQL Server 2000 until now, namely: RESTORE permissions default to members of the sysadmin and ...


1

Without knowing details about exactly what type of queries are accessing that data and the indexes you are dealing with a precise answer is not possible. However, in general an index rebuild drops and then re-adds that index and locking needs to occur for that to happen. If you have queries that are attempting to access this table while the rebuild is going ...


0

Few questions to ask and start troubleshooting 1.How many databases are you mirroring 2.How much RAM is available for sql server 3.What is the mirrored database size Below are the rough estimates of mirroring requirements when mirroring is enabled on Prinicipal: 4 MB per database for DBM send buffers plus 4 MB for log write buffers On Mirror: 1 MB ...


1

Note that regardless of whether you trigger a table lock, a 1000+ record INSERT transaction will lock many pages in your indexes, and has a high risk of causing deadlocking or other problems. The good news is that your post does not seem to provide a business case for taking on a such a large transactional operation. The data source table is not going ...


1

The only correct answer here is "It Depends". There are so many factors that come into play that you will just have do many runs with different parameters try to find what are the optimal settings for your system, and you can also play with locking hints if your table has indexes that are optimal for the search condition. There are some rules of thumb.. if ...


2

Here is sample code I used to get this working, hope this helps somebody! Linked Server set from 2000 to 2012. USE master GO -- Drop Existing LinkedServer [CARME]: EXEC sp_dropserver @server=N'CARME', @droplogins='droplogins' GO -- Re-create LinkedServer [CARME] by using the ODBC connection: EXEC sp_addlinkedserver @server = N'CARME', ...


1

If I'm understanding this correctly, you're dropping the current symmetric key (why?) and creating a new one. When the new one is created, since you're not using KEY_SOURCE, IDENTITY_VALUE, and only supplying the same ALGORITHM you're going to get a different symmetric key each time. If you want the data to be the same, use the same values for ALL of those ...


1

Since you are optimizing for reads you could simply add one optimal index per query. So if your query is select price_per_unit from Sales where exclude = 0 and partner = 42 Create the index (exclude, partner) include (price_per_unit). You say that writes and disk spaces are not of significant concern. So add the optimal indexes to support your reads.


0

Have you tried to detach it from the 2008 R2 server, copying the .mdf and .ldf files over to the 2012 server and re-attaching them to it? It doesn't solve the backup error I know. It would get the database over to the 2012 server and if it attaches successfully would tell you that its good.


0

Try running a RESTORE FILELISTONLY or RESTORE HEADERONLY. If these run successfully, that would prove that SQL Server is successfully accessing the .bak file. (And rule out some sort of permission issue.) If that is successful, try running your RESTORE VERIFYONLY and RESTORE DATABASE with STATS=1. If it continues to fail in the same spot each time, it would ...


0

Looks fairly ominous for you I'm afraid. Likely an issue with data integrity or the validity of your drive. http://www.sqlserverf1.com/tag/restore-database-is-terminating-abnormally/


2

I would suggest NOT to use Designer in creating key constraints or doing any DDL / DML operations. Best is to use T-SQL - much flexible, more options, more powerful and you can automate many things with it. There is a learning curve, but its worth learning TSQL rather than keep using GUI. Also, make sure you are using the latest version of SSMS - sql ...


1

If you run this: select dbname, AVG(usage), STDEV(usage) from <your table> group by dbname You'll notice that while the average is the same for both databases, the one you'd be interested is the one where the standard deviation is lower. So what you'd want to do is then filter this by a certain threshold average usage or higher and a certain ...


0

Well I figured it out. Log4Net's AdoAdapter was keeping a prepared statement saved through the duration of it's connection, and my plan cache was flushing them out sometimes.


0

An alternative way that may be faster is to use the STIntersects and BufferWithTolerance methods to check if one point is within a certain distance of another. SELECT DISTINCT DS1.[ID] FROM DataSource DS1 INNER JOIN DataSource DS2 ON DS1.[ID] = DS2.[ID] WHERE DS2.STIntersects(DS1.BufferWithTolerance(200, 0.9, 0)) = 1 Do note though that ...


1

I got this same error a few weeks ago, despite the fact the connection was fine; it was actually due to an authentication issue. If you don't have domain connectivity between the two machines, you can use certificates to create credentials that can be used for mirroring endpoints. To do this, you create a certificate on the database on each end, create an ...


0

to grant use privliges to create database you can use the following command: use [master] GO GRANT CREATE ANY DATABASE TO [LoginName] GO To drop it you need to make sure the user has owner privileges on the database, you can use the command ALTER AUTHORIZATION ON DATABASE::db TO [LoginName];


0

I've discovered what the issue was. 2 of the parameters were connection strings and if a connection string contained an Application Name then the value of the preceding parameter was not being stored. This worked fine from my machine so I'm assuming a difference in the version of SSMS client is to blame. For the time being I have simply removed the ...


4

You CANNOT do this - you cannot attach/detach or backup/restore a database from a newer version of SQL Server (2014) down to an older version (2012) - the internal file structures are just too different to support backwards compatibility. You can either get around this problem by using the same version of SQL Server on all your machines - then you can ...


0

Use this T/SQL : DELETE FROM dbo.YourTable WHERE ( ID NOT IN ( SELECT MAX(ID) FROM dbo.YourTable GROUP BY road -- <List of columns that define -- duplicate's condition -- for exemple road> ) )


-2

I had similar problems, and this solved it ALTER DATABASE dbname SET HADR OFF DROP DATABASE dbname


2

Balu, the answer you posted will truncate the value to 30 characters because the no length was specified for the CAST and CONVERT declarations of the varchar and nvarchar data types. Consider using STUFF instead: --nvarchar example UPDATE dbo.YourTable SET UnicodeColumn = STUFF(UnicodeColumn, 1, 1, '') WHERE CAST(LEFT(UnicodeColumn, 1) AS binary(2)) ...


0

Finally, got it: select *, SUBSTRING(CAST(CAST(stringValue AS varchar) AS nvarchar), 2, LEN(stringValue)) from test_table where fileNumber=102 and CAST(left(stringValue,1) AS varchar)='?'; update test_table set stringValue=SUBSTRING(CAST(CAST(stringValue AS varchar) AS nvarchar), 2, LEN(stringValue)) where fileNumber=102 and CAST(left(stringValue,1) AS ...


0

I had the same issue with configuring mirroring as my partner server was located in a different hosting center with no cross connect. What I had to do was monitor my database usage. This then showed me the quietest time of day to do the Full and log backup. My company generally has about 20000 transactions every min. I ended up having to configure my ...


1

You have different delimiters but I'm sure you have a small set of them. In your sample data the two delimiters are "space" and "comma-space". You can amend one of the linked solutions to work with multiple matching conditions. This will deliver unpivoted values. The data will then look something like 1 All 1 of 1 R489804 1 R489805 ... 2 SP 2 fr 2 ...


2

Here is a sample of how I would do this: select a.id , substring(a.value,T.N,7) from ( values (1,'All of R489804 R489805 R489806 R489807 R489808'), (2,'SP fr R308999 '), (3,'R308777, R104621, R708931 ') ) a(id,value) cross apply (select N from ...


1

After you take your transaction log backup and restore it on the mirrored pair, you might have taken more transaction log backups. If so, try to catch up on the backups then init the mirror. At that point all the old transactions that are not replayed with be queued up and you will endure a DB_MIRRORING event on your log file which will stop the ...


0

Since Mirroring operates by shipping over a live transaction log to the secondary servers, you'll need to start a new mirroring session. Your secondary database has been out of sync for a week and unless you've managed to maintain a transaction log on the primary that spans that entire time (hint: you haven't), you'll need to start with a fresh backup on ...


0

It seems Windows Firewall blocks DTC packages. To allow it to communicate through Windows Firewall, go to Control Panel > Windows Firewall > Allowed Apps and from list of apps, check Distributed Transaction Coordinator for both Private and Public networks. If it doesn't works for you check out these solutions: Unable to begin a distributed transaction No ...


1

Install with the pre-SP1 SQL 2012 media. Then, after your cluster is up, follow the procedure to apply SP1. It appears the 11/2012 SP1 slipstream installer was not tested for this component when being installed from scratch. This media worked: en_sql_server_2012_enterprise_edition_x86_x64_dvd_813294.iso This did not: ...


9

I think my understanding of the second estimate is incorrect and the differing numbers seems to indicate that. What am I missing? Using the SQL Server 2012 cardinality estimator, the selectivity of the join drives the estimated number of rows on the inner side of the nested loops join, and not the other way around. The 11.4867 number is derived (for ...


1

Just take a full backup (with compression, if possible), restore it on the other server, and then you can select into or insert/select without having to worry about what happens to carriage returns, line feeds, and other characters that cause problems for files. You want to move (parts of) a database, not files. Trying to just extract some 120 million rows ...


0

I personally DO NOT recommend setting up merge replication under such conditions. But, as there is no way for me (other than make this work or develop my own replication solution), I spent two weeks experimenting and found a solution. Disable Precompute partitions option on Publication Properties -> Subscription Options -> Filtering window. This makes ...


1

That looks like a partition_id to me, not "a row." You can start to get an idea of what values might be stored in that partition through the following: SELECT partition_number FROM sys.partitions WHERE partition_id = 72057600379912192; Then you'll have to look at the partition function / scheme to tell where the boundaries are for that specific ...


0

Low Selectivity Here is a good quote from SqlServerCentral: In general, a nonclustered index should be selective. That is, the values in the column should be fairly unique and queries that filter on it should return small portions of the table. The reason for this is that key/RID lookups are expensive operations and if a nonclustered index is ...


2

You will want to use dynamic SQL to change the DB you are executing the query in: DECLARE @dbname AS VARCHAR(100) DECLARE @query AS VARCHAR(MAX) SELECT @dbname = name FROM sys.databases d WHERE /*enter criteria here to get DB*/ SET @query = ' use ' + @dbname + ' /*enter your query here*/ ' PRINT @query EXEC(@query)


0

I had an Availability Group with the same symptoms. For maintenance reasons 1 node was down. Quorum remained from 2nd node and file share. After 7 hours the alive node has (probably) lost the connection to file share and as last man standing has stopped serving clients - has lost connectivity to major part of quorum. When the file share become available ...


3

how to stored the actual IP address - in text or bytes format. Which is going to be better? Since "text" here refers to VARCHAR(45) and "bytes" refers to VARBINARY(16), I would say: neither. Given the following information (from Wikipedia article on IPv6): Address representation The 128 bits of an IPv6 address are represented in 8 groups of 16 ...


0

Smaller is always going to be faster. With smaller values you can fit more of them into a single page, therefore less IO, potentially shallower B-Trees etc. All other things (translation overhead, readability, compatibility, CPU load, index sargability etc.) being equal, of course.


2

Today, I think you would have to add logging to the stored procedures (or to the calling application). Trace / profiler are not supported on Azure SQL Database, and never will be. At some point in the future, you should be able to use database-scoped Extended Event sessions. Bob Beauchemin talked about this here and here, and there is an official marketing ...


0

I know my answer might be late. Yes, this used to happen when SQL server is restarted. See here for more information (as given in other answers). With SQL Server 2014, it has been fixed (identity wont increased by 1000) only if there was a planned SQL Server restart. I have solved in another way by adding a start up stored procedure in SQL Server 2012. ...


2

Check MS documentation. Specifically the top line: The THROW statement honors SET XACT_ABORT RAISERROR does not. New Applications should use THROW instead of RAISERROR. I believe that is your problem, right there. https://msdn.microsoft.com/en-us/library/ms188792.aspx


0

I would prefer to rebuild the indexes where the fragmentation percent of the indexes is greater than 30% and a Re-org of indexes where fragmentation percent is in between 9% and 30%. Coming to update stats, I would prefer to do it with Full Scan depending on the size of the databases. While these are resource intensive database operations, I would prefer ...


0

I would agree with Aron's idea here.... Extended Events can get you all the information you needed. or Querying from DMV's directly would be an alternative. DMV's have the information from the last time when sql server was restarted...


2

You have installed a new instance of SQL Server, so you need to change the way you connect to that new instance. Previously, you had a default instance, so connecting using any of these in the Server name: box would work fine: localhost . 127.0.0.1 <any_local_IP> <your_machine_name> <your_FQDN> Now that you have installed a new instance ...


2

Using select * from vManyUnions where p = 1 will already avoid having to access the other tables, because of the contradiction optimisation. If you look at the actual execution plan, you should see that only one of the external tables is actually accessed. Any others would have the NumberOfExecutions property set to zero. You can see me demonstrate this in ...


-2

You can setup the sql profiler and start recording all your queries into a table. Filter the table for the host and see which host, and possibly which users are doing this.


7

Queries aren't logged by SQL Server, unless they meet some criteria that puts them into the default trace or system_health Extended Events session (some details on what they collect here). If you can control data access via stored procedures, you can easily add your own logging to those. But that doesn't sound like the case. Otherwise you will have to rely ...


0

So, after a bit of thought, I realized that I can just treat my User Information as another piece of data. So I can add to each table a "ChangedBy" and a "ChangedWhen" column. Whenever my service updates a record, it will update the ChangedBy and ChangedWhen. Then in the Change Data Capture history, the changes include needed data.


2

What you need to do is use the LEAD windowing function to look at one row down, two rows, three rows, etc for however long your pattern is. Example: LEAD(gradeissued,1) over (partition by studentid order by whenissued) as one_row. Put that in a CTE or subquery and then in the next query do a where criteria on your pattern (one_row = 'D' and two_row = 'C' and ...



Top 50 recent answers are included