Tag Info

New answers tagged

3

When comparing values of different datatypes SQL Server follow the Data Type Precedence rules. Since nvarchar has higher precedence than varchar SQL Server has to convert the column data to nvarchar before comparing values. That means applying a function on the column and that would make the query non-sargable. SQL Server does however do it's best to ...


3

Identity values are not affected by ROLLBACK operations, so if you insert rows in your table and then issue a ROLLBACK, the identity column will be incremented anyway. Even if your UNIQUE constraint causes the INSERT commands to fail, the identity values are consumed anyway. Example: IF OBJECT_ID('tempdb..#test_identity') IS NOT NULL DROP TABLE ...


0

Martin Smith's comment ("Does the subquery itself work?") was ultimately the answer and highlighted my insufficient forensic work! ;-) The subquery was returning ROW COUNTS from tables: ... INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID ... The fixed roles I was using don't give VIEW DATABASE STATE permission (which is ...


-1

Check your initial db size. May be your initial db size hasnt crossed yet. In that case reinitialize db size and perform db shrink again.


0

In shrink action, you need to choose: "Reorganize pages before releasing unused space" And choose desidered size. Actually you don't have released space because you have still used pages ad the end of your file.


0

You have to remember that the leaf nodes of a Nonclustered Index consists of Index pages which contain Clustering Key or RID to locate Data Row. In your where clause you state VeryRandomText = N'111' Since there is a Non clustered index on VeryRandomText (create index will create non clustered index unless you explicitly tell it to create a clustered) the ...


-3

I am currently facing the same issue. If you keep the restoration mode to NO RECOVERY, this should solve the issue. If you change the restoration mode back to STANDBY you will start getting the same error again.


2

You could try an indexed view. I know you said you couldn't add an index to the table but this would be creating a view and then indexing it. CREATE VIEW vw_Message WITH SCHEMABINDING AS SELECT month_date= month(SendTime) ,year_date =year(sendtime) ,MessageType, COUNT_BIG(*) AS MESSAGE_COUNT FROM dbo.Message where ...


1

Had a situation where a production environment had DB schema updated The developer executed an alter on the base procedure, with no changes, and the execution time returned back to normal and they asked why. Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL ...


3

Oh Boy! System Databases on C Drive are recipe for disaster ! Change the default database location away from C:\ drive. You can do it using ALTER DATABASE .. MODIFY FILE Change the db file location ALTER DATABASE db_name MODIFY FILE (NAME = logical_data_file, FILENAME = 'D:\data\physical_file.mdf'); ALTER DATABASE db_name MODIFY FILE (NAME = ...


0

Do I need to run a command to set them back to Restoring or do I leave it and the log shipping will automatically sort it out? The answer to this question is: it depends... The process that you go through to fail over to the Secondary will directly impact whether or not you need to refresh the Secondary from a full backup. A key question is whether ...


6

;WITH x AS ( SELECT ID, VAL, Prev_VAL = LAG(VAL, 1) OVER (ORDER BY ID) FROM dbo.your_table ) SELECT ID, VAL, Prev_VAL, Cumulative = VAL + Prev_VAL FROM x WHERE VAL < Prev_VAL ORDER BY ID; If you need to support versions older than SQL Server 2012 (when LAG() was introduced), you can do this, but by casual and hardly-scientific observation, it is ...


1

I did this once as an exercise in dynamic SQL. It's NOT a good idea for any production system. Primarily you add a huge risk of SQL Injection. This is where someone would pass an actual SQL command in as part of one of the parameters and you end up running it without realizing it. You can minimize this but even so there is the risk. Also there is a ...


5

Making the assumption that you are using SQL Server (because the RDBMS is going to matter here) you can do the following ALTER TABLE tablename REBUILD That being said you can read this article by Paul Randal as to why you shouldn't. Unless you are using your table as a staging table where you want a quick import but then clean the table out later anyway ...


4

This should be good up to about 2,500 values (depending on version): ;WITH x(n) AS ( SELECT TOP (@variableNumberOfIdsNeeded) (ROW_NUMBER() OVER (ORDER BY number)-1) * CONVERT(BIGINT, @SeqIncr) + CONVERT(BIGINT, @FirstSeqNum) FROM master.dbo.spt_values ORDER BY number ) --INSERT @newIds([NewId]) SELECT n FROM x; If you need more, or ...


-2

Yes, you can. See: Set Up FILESTREAM on a Failover Cluster, https://msdn.microsoft.com/en-us/library/cc645886(v=sql.110).aspx


0

Indeed, that was the answer. I set the db recovery model to "Simple", waited a minute for the filestream data to clear up, and then I could remove the filestream file and filegroup.


1

This is still a string, regardless of how it looks to you when you copy the output: '0x' + CAST(...) AS varchar(4000)) AS plan_handle Try using a style parameter on the CONVERT, so that the string isn't converted to a binary representation of the string, but rather converted to the string's literal binary value: CONVERT(VARBINARY(32), '0x' + CAST(...) AS ...


3

You can use sqlmigration - Start-SqlServerMigration.ps1 This script provides the ability to migrate databases using detach/copy/attach or backup/restore. SQL Server logins, including passwords, SID and database/server roles can also be migrated. In addition, job server objects can be migrated and server configuration settings can be exported or ...


0

Log shipping will best meet your criteria. It is supported in standard edition, has the least level of complexity, and can easily be configured to meet your needs around allowable data loss and overall downtime. It's really important to perform DR tests on a regular basis. This ensures not only that the steps work as expected each time, but also that you ...


1

You can setup 2 different availability groups and add databases accordingly, so that way you can manage it separately.


3

Below a couple of techniques that should coerce partition elimination while providing the same functionality as the IN or OR methods. These both use a JOIN instead, one with a row constructor and the other a table variable. The table variable (which could be passed as a table-valued parameter) allows a variable number of container values without changing ...


1

In terms of wanting to get input from other people about their experiences, I can share about our use of Log Shipping in our Production environment. We use native Log Shipping and perform DR tests once a quarter for our critical systems. This involves failing over to the Secondary, pointing the application(s) at it, and testing that everything works as ...


0

If "watch the history log to see if the job completed" can be accomplished by using either a stored procedure, scalar function, or table-valued function (as opposed to direct SELECT access to the msdb.dbo.sysjob* tables), then you can get away without granting any permissions at all to any real Login/User that will be logging in. You will still create at ...


0

To be able to check all jobs you would need go add the user to the SQLAgentReaderRole in the msdb database which also gives that user permission to create jobs. To minimize the access granted you can also grant the user select permission on the tables used by the sql server agent


2

You want to use the three-part naming syntax: <database>.<schema>.<object> wwtest . data . Mobiles ...however you seem to be confused between your schema naming. In the table data.Mobiles, data is the schema, not dbo, so the naming should be wwtest.data.Mobiles. dbo is also a schema, so there is no need to include it in your query ...


1

Try with [wwtest].[data].[Mobiles]


2

That is not a supported syntax as a stored procedure can return multiple result sets or do other data manipulations and return nothing. You would need to convert your stored procedure to a table valued function or insert the results into a temp table.


0

create table #tmp1 ( name varchar(100), email varchar(10),phone varchar(10)) insert INTO #tmp1 EXEC [sp_Test] select * from #tmp1


7

Could you please let me know the best option to proceed and least complexity to maintain Logshipping is your best option for Disaster Recovery since you are on standard edition. Make sure you test the disaster recovery - simulate it so that you are confident when a real disaster strikes. Note: In my company, we use LogShipping as a primary DR solution ...


0

connect your Named SQL Instance 2 listener like below "listener name\static port" or "listener name\Namedinstance" then Instance 2 will point your named instance database.


4

Your query is looking for values between 2015-04-25 AND 2015-05-30. Of which it looks like you have about 30 million. Unfortunately there is a bug with the cardinality estimations where DATEDIFF is involved where the components get reversed. SELECT DATEADD(WK, DATEDIFF(WK, GETDATE(), 0) - 4, -30) , DATEADD(WK, DATEDIFF(WK, GETDATE(), 0) - 4, 0) ...


-1

ImageDate in the index is not used because your condition over ImageDate concerns a function ("DATEADD"). In general systems do not use indexes over attributes if the condition contains a function which is evaluated at query execution time.


1

You need to create the type inside the database where you will declare it - data types can not be reached across databases or servers. This is no different from creating a local variable using an alias type - it has to exist in the context of the current database (or your code has to execute in the context of the database where the type exists, e.g. EXEC ...


3

Even if you configured RBS to use FILESTREAM, TDE does not apply to FILESTREAM: Transparent Data Encryption and FILESTREAM DATA FILESTREAM data is not encrypted even when TDE is enabled.


3

The files are stored in a blob in the sharepoint database and as TDE encrypts all the pages in the database all the files will be encrypted there within. It's important to notice that while the database is mounted on the server the database server will serve the files unencrypted to the Sharepoint application server and it's clients. The Sharepoint ...


2

CREATE TYPE [tvf_id] AS TABLE ( [id] [int] NULL ,[OrdCol] [INT] NOT NULL ) GO CREATE PROCEDURE [sp_Test] @pID tvf_id READONLY as begin set nocount on SELECT a.* FROM datatable a inner join @pID b on a.id = b.id ORDER BY b.OrdCol ASC end GO -------------------------------------------------------- DECLARE @data tvf_id ...


3

What you should check first is your server or client DNS settings It's not the rare that your SQL Server have the problem connecting to Active Directory. If you try with local Windows account I am sure that you will not have the at issue. It's not unusual that server is configured with public internet DNS and when SQL Server connects to DC to check the ...


1

Well I don't wonder about the restoring time. I wonder about your file amount. 15k files for 112MB? What the h...? Your symptom is nearly the same as during file copying. If you have two disc (disc1, disc2) and you try to copy 100.000 files each 4KB in size from disc1 to disc2 it may take hours. If you copy one zip file without compression (containing those ...


3

The old index stays in place, is maintained and being used by queries. Only at the end of the index build does SQL Server alter the metadata in such a way that the old index disappears and the new one is set in. There is not a single millisecond without an index. (But there is a short-lived Sch-M lock twice during the online build.)


3

NOTE TO READERS: Please read the entire question, including the example code (i.e. not just the title). This question is not about how to best cycle through databases, nor is it about why does [tempdb] receive this error. The O.P. is already trying to avoid executing the ALTER statements on all system databases (well, the 4 visible ones) and is asking why ...


3

Apart from the fact that you cant change the recovery option for tempdb, You dont need a loop for what you are doing : Run in SSMS by pressing CTRL+T select 'alter authorization on database::' + quotename(name) + ' to [sa];' + char(10) + 'alter database ' + quotename(name) + ' set recovery simple;' from sys.databases where database_id > 4 and ...


3

Msg 5058, Level 16, State 1, Line 5 Option 'RECOVERY' cannot be set in database 'tempdb'. It does the job it's supposed to do. But I am unable to think of a reason for the error. First of all there is no need to use ms_foreachdb its undocumented and way bad you can loop in using simple cursor. Regarding error you are trying to change recovery ...


5

The article written by Paul regarding backup internals is excellent one and you must read it. Adding to what others have said and emphasizing on specific part of your question Also I heard that backing up is single threaded meaning it uses only one core, assuming you backup to a single file. Also assuming you have a multicore machine, for example 16 ...


6

Your all points are covered in backup myths - by Paul Randal 30-01) backup operations cause blocking No. Backup operations do not take locks on user objects. Backups do cause a really heavy read load on the I/O subsystem so it might look like the workload is being blocked, but it isn't really. It's just being slowed down. There's a special case where a ...


4

Basically, SQL Server does a dirty copy of all pages on disk. Those pages are likely inconsistent if there is concurrent activity or if there previously way non-checkpointed activity. Then, SQL Server also copies the necessary part of the transaction log that is needed to bring the out of date pages to the latest version and make everything consistent on ...


0

2015-04-08 15:40:46.82 spid11s Error: 17190, Severity: 16, State: 1. 2015-04-08 15:40:46.82 spid11s Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0. 2015-04-08 15:40:46.82 spid11s Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a ...


7

Try running a trace with SQL Profiler while you, and then your coworker, connect to the server. Select RPC, SQL Statement & PreConnect - Starting/Completed. Select Save Results To Table option, then compare the 2 tables to find the bottleneck. Or, since you're connecting by IP, it could be doing a Reverse DNS lookup. If so, add a entry in your hosts ...


1

Below is a complete script that works as expected without the error. :setvar IsArchive "" :setvar Suffix _Partitioned CREATE SCHEMA Orders; GO CREATE SCHEMA ArchivedOrders; GO CREATE SEQUENCE Orders.NextOrderId; GO CREATE PARTITION FUNCTION OrderIdPartitionFunction(bigint) AS RANGE RIGHT FOR VALUES(); GO CREATE PARTITION SCHEME ...


1

You have three options. Use the gui of SQL Server Management Studio. Use BCP to export your data in a loop (see here) Use Powershell and script your export in a loop for example. Here an example in PowerShell: $Tables = invoke-sqlcmd -query "SELECT name FROM sys.tables WHERE name LIKE 'list[_]%'" -database "ETTEDB_list" -serverinstance "YOURSERVER" ...



Top 50 recent answers are included