Tag Info

New answers tagged

1

Dirty pages are written directly to the data files. There is no need to write them to the log because their modifications have been logged already. A dirty page never hits disk until all log records that record its modification have been written already. After the log has been hardened SQL Server is at leisure to write dirty data pages whenever it wants (or ...


0

Rght click on SSMS, Run as administrator, sometimes this error appears. Once i couln't attach mdf file to database due this error.


0

It might be worth looking at XACT_STATE instead of @@TRANCOUNT. From BOL: Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. So there may be some cases ...


0

The following will work: sqlcmd -U user -P pass -S Server -Q "sp_who" -r1 1> test.log The output from the query will be put into the log and nothing is printed on the screen. In the case of an error, it will run like this: sqlcmd -U user -P pass -S Server -Q "sp_notreal" -r1 1> test.log Msg 2812, Level 16, State 62, Server XXXX, Line 1 Could ...


1

As Kin noted above, it is not possible to get this information since it is not stored by SQL Server. Please refer to this article on technet. Since you are using Ola's scripts, unless you changed the defaults, the output of the IndexOptimize job should go to the same location as your error log.


0

I write this query and this work. USE DestinationDatabase DELETE sysDiagrams WHERE name IN ( SELECT name FROM SourceDatabase.dbo.sysDiagrams ) SET IDENTITY_INSERT sysDiagrams ON INSERT sysDiagrams ( name , principal_id , diagram_id , version , definition ) SELECT name , ...


4

SQL Server does not maintain when an Index was last rebuild, instead it keeps information when stats were last updated. That can be found using the STATS_DATE function. You can use Ola's Index maintenance solution or Michelle Ufford's - Index Defrag Script. These scripts are widely tested in the community and are much flexible so that you can adapt as per ...


8

No. This hard-coded restriction exists for good reasons related to possible excessive query plan compilation time. You can workaround it by listing the VALUES clauses in a CTE, then INSERTing from the CTE, but I do not recommend it. Break the INSERT statement up into VALUES clauses of <= 1,000 lines each as a workaround, or use an alternative data ...


2

From the SQL Server query optimizer's point of view, there is not much to choose between the parallel and serial execution plans in this case. In general, the optimizer's cost model reduces the CPU cost (not the I/O cost) of operators in a parallel plan in proportion to the estimated degree of parallelism available. This CPU adjustment explains why the ...


1

I believe you are running into the issue described in Subqueries that you define on merge articles are not re-evaluated. From the article the cause is: This behavior occurs because the query is not re-evaluated and the row is not propagated as part of replication when you update a row in a table that is referenced by a subquery. Although you can ...


2

The process is simply to launch SQL Server setup from your DVD or ISO, go to the Maintenance tab, and perform an "Edition Upgrade" (please ignore the fact that this screen shot is from SQL Server 2014): You'll need to enter a different product key. Please also make sure you install the latest service pack (SP3; 10.50.6000). As for impact, you ...


3

Just following on from @usr's answer (and similar question here), indexed views could work for this, or more (precisely a covering non-clustered index on the index view), as long as you're aware they can destroy INSERT / UPDATE / DELETE performance on the underlying tables and (from experience) can contribute to deadlocks. If neither table is that dynamic ...


0

select * from ( select SubBrand,Month,Saless,Variable from (select * from DataTable )as p unpivot (Saless for Month in(January,February)) as u ) as p pivot ( min(Saless) for Variable in ([Sales],[Promotion]) )as PP order by SubBrand,Month


3

This is a classic performance problem. You are ordering on the columns of one table and are filtering on the columns of another. SQL Server can either scan Data and filter out rows by seeking into the other table (the current plan). Or it can access Permission to determine the Data rows that pass the filter, and then sort this set. Both plans require a lot ...


2

Your log backups are being done by the Log shipping process, that is after all what is there to do. So, you will generally not include log backups in your backup strategy but take into account how often your log shipping process runs. You would only worry about taking full and/or differential backups against the databases involved in log shipping.


0

Just make sure you are opening the right SQL Server configuration manager software, if you're opening the wrong SQL Config Manager on your machine it will not show the SQL server service, because you are looking at a viewer and not the actual config manager. I had the same problem with SQL 2014 and when browsing again for the Config Manager software on my ...


5

IDENTITY is not a column type in its own right, it is a property that can be apply to any integer column type. It is usually applied to an INT and in that case there is no difference (the column is an INT column, with the IDENTITY property defined) though it can be applied to any type that is internally an integer (BIGINT, DECIMAL, MONEY, ...). While your ...


1

You have a few options, some of which were already pointed out. First, though, if these events haven't been captured so far there is no "historic" way of obtaining the data. There may be ways to get some of the data but chances are you won't get all or even most of it. Going forward, however, it seems you only want those who successfully connect to the ...


0

As an addition to the above answer. Another solution that was easier to use is this : Importing registered servers On the SQL Server 2005 system, shut down SSMS and copy out the files in this location: “%appdata%\Microsoft\Microsoft SQL Server\90\Tools\Shell\RegSrvr.xml” Now copy those files to the same location where you installed SQL Server 2008. Open ...


1

As Cougar9000 found out himself the usage of ephemeral (dynamic) ports is not managed by the application but by the operating system. You can only change it for the whole server. The answer to this question does provide you with the information and links you need. In short you could do netsh int ipv4 set dynamicport tcp start=50000 num=1000 Please note ...


0

My first question where are you looking for SQL Server memory consumption. Please don't use task manager or process monitor, these do not show correct value when SQL Server service account has Locked pages in memory privilege. just use below query to see memory consumed by SQL Server select (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB, ...


0

Start by looking at the min server memory and max server memory configuration options detailed at http://msdn.microsoft.com/en-us/library/ms178067.aspx. You can pull them up by running: exec sp_configure 'min server memory' exec sp_configure 'max server memory' Ultimately, you have to decide if performance is being affected by these settings or not.


0

For a default instance (MSSQLSERVER), you only specify the computer name when connecting; the instance name (which is actually a ficticious placeholder) is implied. Also, if you're connecting from another machine now, you may need to configure the Windows Firewall to allow connections through. The error message you get will be the same as the one you're ...


0

Open SQL Server Configuration Manager. That will likely show you the answer to your problem. In SSCM you should be able to see what database instances are installed, what their instance names are, what ports they run on, and whether the services are up. There could be a number of different reasons you're getting this such as the server is not started or ...


0

If you are trying to restore db from a server to another server and db is not exist on restore server, you can try method below. That problem may occur depends on this reason; DB files could be locate on different disk names which is not exist on restore server. To solve the problem on restore server; create a db and db files which are attached with the ...


2

You don't say what DB you are using, but that syntax looks like MS SQL Server. Also you do not properly state the table structure - the function refers to VehicleID which doesn't appear in you sample data and you don't say what the data types or what indexes and keys are present. All of this could be significant. You are pulling a bunch if data into a ...


0

I experienced the same error recently with upgrading SQL 2012 to SP2: 2014-11-08 23:49:55.49 spid5s Checking the size of MSDB... 2014-11-08 23:49:56.13 spid5s Error: 5041, Severity: 16, State: 1. 2014-11-08 23:49:56.13 spid5s MODIFY FILE failed. File 'MSDBLog' does not exist. 2014-11-08 23:49:56.13 spid5s Error: 912, Severity: 21, State: ...


3

Use module signing. Sign your procedures with a certificate and then grant the required permissions to a certificate-derived user. The link contains a full example.


4

NO, With just Execute permission doesn't allow a user to execute a stored procedure successfully if it contains some DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME) statements.


9

Don't. While is possible to use context_info, that is a really really really bad choice for a password. All users with VIEW SERVER STATE will see it in sys.dm_exec_sessions. SQL Profiler will not know what you're doing when you set it and will not obfuscate it in produced events. And more similar badness. Instead use proper encryption hierarchy. Do not ...


0

SQL Server has no global variables. If you want to make something available to all sessions, use a table and write the data there. Another option is persisting the data in a global temporary table (prefixed with two # signs). It could get tricky to detect whether the table got out of scope though. If you want to make a value available throughout the whole ...


0

Without commenting on your encryption solution per se, storing a string or password in the database can be as trivial as creating a single-row, single-column table and properly restricting the permissions on that table. Another approach would be to create an encrypted view with a hard-coded column. CREATE VIEW dbo.mySecret WITH ENCRYPTION AS SELECT ...


0

Context_info acts like a global variable. It's binary so will require some work to extract the password each time it is used. BOL has details and examples.


0

Consider this as answer SP3 is new and thoroughly tested its highly unlikely it would create any issue but again I would recommend you not to proceed without any testing and that too when Cluster is involved. Also asking whether anybody faced issue will lead you in problem, what if somebody writes 'Yes I have done and its working fine' yes they are correct ...


3

Yes, this is possible by creating a user with 'securityadmin' server role and then if required Deny his access to SQL Server Query Window through LOGON Trigger USE [master] GO ----Create Login CREATE LOGIN [DBA_ErrorLogUser] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO --- ...


1

Yes, you can query the database to find out the version via sqlcmd. The actual SQL you'll want to use is just SELECT @@VERSION but to do it via sqlcmd, you can use: sqlcmd -S[server] -U[user] -P[password] -q "SELECT @@VERSION" Also check the MS sqlcmd reference for further info on flags you can use with sqlcmd.


1

The package created by the copy wizard can be modified to bring a little better performance possibly but your log growth is expected. Every bit of data changed for the table and indexes is being modified, that has to be logged no matter what recovery model you are in. I would go with the restore from backup as this will be a bit cleaner. You can pull the ...


1

CHAR(0) appears to be converted to a space, or CHAR(32). The following demonstrates the problem: DECLARE @Data NVARCHAR(255); SELECT @Data = 'this is a test' + CHAR(0) + 'of null'; DECLARE @i INT; SET @i = 1; DECLARE @txt NVARCHAR(255); WHILE @i < LEN(@Data) BEGIN IF SUBSTRING(@Data, @i, 1) = CHAR(0) BEGIN SET @txt = 'found a null char ...


0

Microsoft gives Downgrade Rights to Volume Licensing customers and, to some extent, even to OEM and retail customers. However, in general, if you don't have a Volume License, you can't have the bits and the product key, which are necessary to excercise your downgrade rights. Long story short, if you want 2008 R2, you'll have to buy it. Here's a blog ...


2

Since you stopped the index creation, the index was not created. However, the space it had reserved already (10GB) was still added to the database size. SQL Server will not automatically shrink the database file size back down unless you explicitly instruct it to (which is not recommended unless you are about to run out of space). You can add up to 10GB of ...


0

Yes, you are able to restore a database with NORECOVERY and then perform more restore produces within one script, allowing you to recover a database to a point in time. When you restore full and differential backups you should have a MOVE statement for both the data and logs. I would recommend moving the backups you want to restore to a directory which ...


1

In addition to aasims' answer, the only major difference here being a Unique Index is not a database object. Whereas a unique Constraint is an object. However other than that there are no other major differences: They both achieve the same performance They both achieve the same end goal. EDIT: Why would you use Unique Index vs Constraint, what's the ...


2

Fact is that there is no practical difference between a unique constraint and a unique index. Querying sys.objects, you will find unique constraint is listed as a constraint object and its related index can be found while querying sys.indexes, where it is marked is_unique = 1 as well as is_unique_constraint = 1. On other hand, for a unique indexes ...


2

There are other methods as well. Perhaps you can use two DMV's. Please note that both will only work for SQL Server 2008 and above A non zero value for locked pages allocation KB would tell that SQL Server account has Locked pages in memory privilege select osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb from ...


8

If xp_cmdshell is an option, here is a script making use of whoami: DECLARE @LockPagesInMemory VARCHAR(255); SET @LockPagesInMemory = 'UNKNOWN'; DECLARE @Res TABLE ( [output] NVARCHAR(255) NULL ); IF (SELECT value_in_use FROM sys.configurations c WHERE c.name = 'xp_cmdshell' ) = 1 BEGIN INSERT INTO @Res EXEC xp_cmdshell 'WHOAMI ...


0

but wouldn't they therefore create the same level of fragmentation on each server? No. Fragmentation is physical, replication is logical. The same sequence of insert/delete/update may produce different physical indexes, but transnational replication may not even produce the same sequence of insert/deletes/updates. Even with non-filtered publications, ...


2

Variable length character types only store actual content put into it and odn't keep the extra space allocated (thus the name "variable length"). As a result, when you INCREASE the size of a variable length column, the data storage doesn't actually change (in the table or the index). The SQL engine is smart enough to know that and doesn't do anything to ...


0

You can turn off the system stoplist and create your own, eg ALTER FULLTEXT INDEX ON dbo.yourTable SET STOPLIST = OFF CREATE FULLTEXT STOPLIST myCustomStoplist; ALTER FULLTEXT STOPLIST myCustomStoplist ADD 'someWord' LANGUAGE 'English'; ALTER FULLTEXT INDEX ON dbo.yourTable SET STOPLIST = myCustomStoplist If you create an empty stoplist, it means all ...


0

:) If you are interested for the detailed instructions please see below. It works instantly without the need for SQL restart! http://msdn.microsoft.com/en-gb/library/ms178067.aspx Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL Server Memory Manager for an ...


1

Did similar thing (without applications, purely within DB) using Service Broker queues. Lightweight, fully ACID-compliant, can be scaled out almost infinitely. Transparent row locking (or "hiding", rather) is built-in. Available from version 2005 and on. In your case, the overall architecture could be like this: some process sends messages into Service ...



Top 50 recent answers are included