New answers tagged

1

Msg 19405, Level 16, State 17, Line 3 Failed to create, join or add replica to availability group 'AGName', because node 'Node3' is a possible owner for both replica 'Node3\ReadOnly' and 'Primary/Primary'. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again. This happens for two main ...


-1

Please refere it may help you http://duhjones.com/how-to-reset-lost-sa-password-in-sql-express/


1

"Designing database" exam task solution most probably should show how a student can express domain business rules by means of DB data integrity tools (PK,FK). Your solution covers most of them. Missing rules are: (1) Only one Answer is solution to a Question. Bit flag doesn't help without triggers or extra app code. (2) A Student can choose only one Answer ...


10

Each server is a quad core virtual machine with 16GB of RAM provisioned on a server rack. We populate each server with the maximum of 50 instances allowed by SQL Server. Each instance will be accessed sporadically by a maximum of 10 clients simultaneously, and each database will in general only be around 100MB to 500MB in size. IMHO, your total RAM ...


0

I want to remove all the files from sql server 2012 in my local machine can you please let me know how to Delete all the files from sql server files Best Regards


0

Set a maximum of 300MB per instance and be done with it. Seriously, you can monitor it with something like this to determine which may be candidates for giving a little more or less to. SELECT DB_NAME(database_id) AS [Database Name], COUNT(*) * 8/1024.0 AS [Cached Size (MB)] FROM sys.dm_os_buffer_descriptors WHERE database_id > 4 -- system databases AND ...


7

This works too: SELECT salesid, SUM(num) FROM #BE GROUP BY salesid, CASE WHEN num >= 0 THEN 0 ELSE id END; The assumption is that id starts at 1, hence it can use THEN 0. salesid ELSE salesid+id+1 would work as well. It must be tested with real data and indexes, but with only 1 table scan, performances may be a little better in some cases. The ...


0

You may be able to use the command line options documentation to review your different configuration options: https://msdn.microsoft.com/en-us/library/ms144259.aspx Whether they all translate exactly over to what's in the configuration file (https://msdn.microsoft.com/en-us/library/dd239405.aspx)? That I couldn't answer.


5

I don't expect you to be on 2016 yet as it's been released only very recently (at the time of writing) but if you have a 2016 server and you have Query Store enabled for your database, you can review your queries there. Have a look at the documentation for Query Store which lists the following example use cases: Determine the number of times a query ...


9

Try this: select salesid, sum(num) as num from #BE where num > 0 group by salesid UNION ALL select salesid, num from #BE where num < 0 ; If you want both the sum values in one row then you must create a maxValue (and minValue) function and use this as sum(maxValue(0, num)) and sum(minValue(0, num)). This is described in http://stackoverflow....


0

thanks all for answers..solved my problem created two user accounts with the same name and same password in each server and then used them in sql agent services under "this account" option


1

Ola Hallengren automated backup solution will solve your problem for newly added databases. Check for the MirrorDirectory option: MirrorDirectory Specify one or multiple directories to perform a mirrored backup. The MirrorDirectory option in DatabaseBackup uses the MIRROR TO option in the SQL Server BACKUP command.


1

I'm not sure I understand, you state you are going to use mirroring, in standard it is still available so why can't you use that? Or is the question that is there anything else available? You can also use Failover clustering in standard edition, the limit is you can only use 2 nodes but I don't see that as an issue for your request. Also, if you are using ...


2

Logic is very simple. We are taking two instance of same table; second in a subquery. We pick first salary of main table and compare it against all salaries in subquery table to get a count of salaries greater than the salary in main table under consideration. If count is N-1; then it implies that salary in main table is Nth max salary because there are N-1 ...


3

I think you've pretty much nailed it on your initial schema - issues about using data types that are too large notwithstanding. The only real change that I would recommend is having a question_id field as the PRIMARY KEY in the question table. I've prepared a schema below - it's MySQL I'm afraid, I don't use Microsoft SQL Server very often and don't have ...


0

you like the ( ) SELECT COUNT(1) AS [A1] FROM [dbo].[UmsatzEintrag] AS [Extent1] LEFT OUTER JOIN [dbo].[Artikel] AS [Extent2] ON [Extent1].[Artikelnummer] = [Extent2].[art_nr] AND [Extent1].[Lieferantennummer] = [Extent2].[nummer_lieferant] LEFT OUTER JOIN [dbo].[fachbereiche] AS [Extent3] ON [Extent2].[id_fachbereich] = [...


0

As I review the structure you have now I would change the identity columns from BigInt to Int. I suggest you review how many Students, Questions, Answers and Choices you expect and select data types with appropriate sized ranges. I would also replace nvarchar(max) with a more reasonable sized field. As there are limitations with functions on nvarchar(max). ...


4

You have to start over and restore full and diffs with no recovery and then the tlogs with stand by option. This will allow you to restore tlogs later as well. You can configure logshipping with option of stand by, so you can read from the secondary database. There will be a .tuf (transaction undo file) file created when you restore tlogs with standby ...


0

Yes, if you want to apply additional transaction logs you will need to start back at your full backup. To quote MSDN, The database cannot be recovered until after the final transaction log has been applied. If you recover the database after restoring one of the intermediate transaction log backups, that before the end of the log chain, you cannot ...


3

You should use WITH STATS = 10 so that SSMS can show your the progress. Also, if you restored an older version of backup to a newer version of sql server, sql server will undergo an upgrade process .. something along the lines database 'your db name' running the upgrade step from version 661 to version 668 ..


0

I encountered this problem and, after some frustration, solved it. What had happened is that you have created, most likely by mistake, a user object in the model Database. On Management Studio look under Databases\System Databases\model. This is the template that is used when you create a new database, and is used when importing the bacpac. If there are ...


0

I opened a connect feedback here: https://connect.microsoft.com/SQLServer/feedback/details/2869241 as follow: "A SQL Login, mapped to a database user member of the db_owner role cannot script out the database with "Script Database As" or "Generate Script" task when using SSMS 2008, 2008R2, 2016. It gets the following error message "Could not read metadata ...


-2

WITH Reorg AS (SELECT * FROM DepartmentReorg WHERE GroupName = 'Executive General and Administration') MERGE INTO HumanResources.Department Dep USING Reorg ON Dep.DepartmentID = Reorg.DepartmentID WHEN MATCHED THEN UPDATE SET Name = Reorg.Name, GroupName = Reorg.GroupName, ModifiedDate = GetDate() WHEN NOT MATCHED BY TARGET THEN ...


1

The 64K block size comes from the fact that SQL Server does its I/O in units called "Extents", which are each 8 pages - and a page is 8K so the basic unit of I/O is 8 X 8K = 64K. Paul Randal explains here.


2

Only the timestamp syntax is deprecated, not the whole datatype. According to this, just use the rowversion syntax going forward. For concurrency checks, rowversion is what you're looking for. The timestamp synonym still works, but is just an alternate name for rowversion. There are no other datatypes that behave similarly.


0

If it used to run in 30 seconds, that means that something changed: you have to identify what that something is and fix it. That something is most probably the query execution plan. There must be a better execution plan than the one that you're getting now, but the optimizer doesn't feel like giving you that plan any more. The reason can be only one: ...


3

The method is the one used in the article that you linked in your question: test it. You can capture a representative workload for your application/database and replay that workload on servers that have different cluster sizes. What you will find out is that SQL Sever works best with 64KB clusters, because of the way it reads data from disk (read-ahead). ...


2

My favourite way of achieving this is the OUTPUT clause. Here is an example: SET NOCOUNT ON; -- example setup for the queue table DECLARE @queue TABLE ( transaction_id int PRIMARY KEY, processed bit ); -- some sample data INSERT INTO @queue VALUES (1,0), (2,0), (3,0), (4,0), (5,0); Now the processing thread: -- processing ...


0

Might not be bullet-proof but I hope it's a helpful contribution to this thread. DECLARE @t VARCHAR(50) = 'the quick brown fox jumps over the lazy dog', @i INT = 0 DECLARE @chk VARCHAR(1) WHILE @i <= LEN(@t) BEGIN SELECT @chk=SUBSTRING(@t,@i,1) IF @chk = CHAR(32) BEGIN SET @t = STUFF(@t,@i+1,1,UPPER(SUBSTRING(@t,@i+1,1)))...


4

In the ANSI SQL Standard, there is no TEXT type. There are various string types defined, like CHAR and VARCHAR and many more but no mention of a TEXT type. There is a CHARACTER LARGE OBJECT and a BINARY LARGE OBJECT. The various TEXT types found in various DBMS (Postgres, SQL Server, MySQL) are additions and have small differences between them. Their ...


2

Another option is to handle this via SQLCLR. There is even a method already available in .NET that does this: TextInfo.ToTitleCase (in System.Globalization). This method will Upper-Case the first letter of each word, and Lower-Case the remaining letters. Unlike the other proposals here, it also skips words that are in all upper-case, assuming them to be ...


0

It really depends on what's best for your situation as long as you follow some guidelines. We keep the disks for transaction logs separate because often RAID 6 doesn't give us the IOPS we want during peak usage, but many times it does. It depends on your peak IOPS requirements, future growth, and file management. Note that these best practices were created ...


0

The ANSI SQL standard (which I assume you refer to) will nothing to say on how Microsoft (or Oracle, etc) implement and remove data types to suit their particular flavour of DBMS, and which ones take precedence. The Microsoft standard is to use varchar in place of text, nvarchar in place of ntext, and varbinary in place of image, and this is because of the ...


3

Why doesn't SQL Server honor the default that I placed on my table type? Actually, SQL Server does indeed hono[u]r that default as the following test (using the UDTT provided in the Question) shows: DECLARE @Test dbo.MyObject_TableType; INSERT INTO @Test (Name) VALUES ('ObjectOne'); SELECT * FROM @Test; Returns: Name Index ObjectOne ...


1

This will return the name of the view(s) and the definition (the CREATE statement) for all views that your table is in. SELECT name, definition FROM sys.objects o JOIN sys.sql_modules m on m.object_id = o.object_id WHERE definition LIKE '%<Your table name>%'


0

Paul Randal has some interesting comments on this error here. I encountered this error yesterday - I am currently investigation, will keep you posted.


1

After running an instance repair, the correct version number is now being displayed. The shared features are still incompletely installed but at least querying the @@version view will now return the correct patch level.


0

Because COUNT eliminates NULL values, you can use COUNT on each column: SELECT COUNT([productLine1]) AS [productLine1] , COUNT([productLine2]) AS [productLine2] , COUNT([productLine3]) AS [productLine3] FROM tbl You will get a warning, which is expected. If you'd like to avoid that, you can use SUM with a CASE statement in place of the COUNT.


0

The URL to manage the reports is different than the URL to execute the reports (the RDLs files) You can check it in the Report Configuration. Regards, Martín G


-2

Please, try to analyze your backup strategies. Also, You can analyze your DATABASE MODE (FULL, Bulk-Logged, Simple) (https://msdn.microsoft.com/en-us/library/ms189275.aspx) Also, you can check log space with: DBCC SQLPERF(LOGSPACE); GO check this: https://msdn.microsoft.com/en-us/library/ms190674.aspx


3

When in doubt, read the documentation first: TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log. So it deallocates the pages and make them available for reuse.


2

After working on a similar question (Querying True Physical Database File Sizes), I think a query that display the different file sizes, separated, e.g. by log and database, and the size of the file on disk would be more helpful to most users. This includes the system file size and how much space it's taking on disk, as well as the SQL file size, SQL space ...


0

I would use Visual Studio's Schema Comparison tool, or some third party equivalent; it will visually show you what's different and optionally create a script to sync them. Just beware that depending on the change, it may try to copy the table to a work table and then drop the source and rename the new table, which will fail if the table is replicated.


0

USE test; GO CREATE TABLE LogonAudit ( AuditID INT NOT NULL CONSTRAINT PK_LogonAudit_AuditID PRIMARY KEY CLUSTERED IDENTITY(1,1) , UserName NVARCHAR(255) , LogonDate DATETIME , spid INT NOT NULL ); GO GRANT INSERT ON Test.dbo.LogonAudit TO public; GO create ...


1

On a whim, when double checking permissions, I double clicked on the actual DB mail executable. The result on the SQL Server 2014 machine was an empty command window. On SQL Server 2016 clicking the DB Mail executable presented this message: I cannot find this requirement anywhere in the SQL Server 2016 documentation, yet it is clearly a requirement. DB ...


1

Can I add multiple publishers to a subscriber(which is part of availability group as a primary server)? Yes, this is the multiple publishers model and is supported. In which node can I configure distributer? The distribution database is currently not supported for use in AlwaysOn Availability Groups. The only supported method for transparent ...


0

I found a solution and it works as I expected declare @bid varchar(4) = '1007' declare @pm1 varchar(5) = (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'John') declare @pm3 varchar(5) = (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Jane') declare @pm5 varchar(5) = (select users....


0

What is the best way of achieving this? The best way is to script out replication and edit the scripts. exec sp_addarticle ... ,@destination_table = N'Your_Subscriber_TableName' , @destination_owner = N'NewSchema' Then run the snapshot agent and the log reader agent to sync data.


0

you can use the following script that calculates the volume for each table and another result set of the total per data base SET NOCOUNT ON IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL DROP TABLE #SpaceUsed CREATE TABLE #SpaceUsed ( TableName sysname , [Rows] int , [Reserved] varchar(20), [Data] varchar(20), [...


-3

select * from (select rownum as rn,salary from employees order by salary desc )x where x.rn = 5 -- nth highest salary



Top 50 recent answers are included