Tag Info

New answers tagged

2

I would change your table layout. If you used something like: (LineId int, BallSequence tinyint, BallVal int) The combination of (LineId, Ballsequence) would be your PK. Then your query would be: SELECT COUNT(*) FROM dbo.Lines GROUP BY LineID HAVING SUM( CASE WHEN BallVal IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) ...


0

If the table doesn't have many inserts or updates, then it's basically just wasted space (+ maybe longer maintenance / backup time) But for every insert and update in case of the indexed fields, then each of the indexes must be maintained, and that's then slowing down the operations. At least I would recommend looking first at the index usage statistics ...


1

Probably some of those indexes are used, so deleting them all is not a good idea. You can review index usage stats and find bad NC indexes with this query (taken from Glenn Berry's Diagnostic Queries): -- Possible Bad NC Indexes (writes > reads) (Query 47) (Bad NC Indexes) SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], ...


0

It turns out that the CLR was not enabled and for some reason the exception wasn't being trapped and SQL Server took about 15 seconds just to say that the CLR was not enabled but during that time the instance is being kept at 100% CPU and hogged down. In case anyone happens to have the same problem, you can turn the CLR on by running this: sp_configure ...


1

You can map a local drive letter to your UNC folder using xp_cmdshell like: exec xp_cmdshell 'net use X: \MyServer\MySharedFolder\' Then you can issue your backup statement to use the X: drive as if it was local. You may have to run sp_configure to enable xp_cmdshell.


3

It's a good pick to use it? If I was hired on a new company, it's a good pick to use maintenance plan ( for me, looks like too "simple", don't want to look a bad DBA). Maintenance plans are not bad, but when your environment grows, the limited flexiblity and functionality that maintenance plans provide wont be sufficient. For e.g Maintenance plans ...


0

I reproduced this using the code below, and essentially, yes, if you wait long enough, and auto cleanup is on, it will clean itself out. It appears to be bound the a minimum - that is, the change_retention setting is more of a suggestion than a command. Admittedly, I haven't tested with real-world table activity, etc., but the links below contain more ...


11

Create the procedure in the system database model. From model Database The model database is used as the template for all databases created on an instance of SQL Server.


0

There is no a definitive answer. Anyway I suggest, if possible, to store tempdb files in different LUN. But it is worth only if tempdb is under pressure. In my environment I use shared storage also for tempdb for performance reasons: my local disks are slower then shared storage via EMC.


1

I Thought that I can change the DatabaseLS_Main from Standby mode to bring it online, take the snapshot and bring it back to STANDBY mode...is it right to do ? What you are telling is not possible using logshipping. There are limitations of database snapshot In a log shipping configuration, database snapshots can be created only on the primary ...


1

The underlying OS is capable of detecting all the above mentioned possible disconnects and break the communication channel (TCP, net pipe etc). This will result in the rollback. Don't try to outsmart this. Of course, one could ask why not use a reliable queueing communication channel (eg. MSMQ) submit the entire order in one call rather than 100 calls ...


0

Please find dbvis.vmoptions in c:\Program Files\DbVisualizer and add following option there -Djava.library.path="c:/ms_jdbc_41/sqljdbc_4.1/enu/auth/x64" just after the line -Dsun....


0

You can get the server principles through the following query: SELECT name, principal_id, sid FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE' This works in every database, so you do not need to target a specific database.


1

No, don't use sys.syslogins - this is a deprecated compatibility view that is only there so people have time to change their existing code based on SQL Server 2000. Also, I'm completely unclear how you think a CASE expression will help you. You need to join to sys.server_role_members to find out which logins belong to which roles, and then back to ...


1

Columns specified in WHERE and JOIN clause predicates are the candidates for index keys. ORDER BY columns are also candidates if it can avoid an expensive SORT operator for many rows. Columns specified only in the SELECT clause are candidates for non-clustered index INCLUDED columns when the cost of the additional storage and maintenance outweigh the ...


1

You could use a DDL trigger to print the output of the object that was affected, or you could use the trigger to automatically log the change in an audit table so that you can view DDL change history. Here is an example http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/


2

Use :on error exit and/or -b. This will cause sqlcmd to exit after the first batch that hits and error, and set the %ERRORLEVEL%: -b Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than ...


0

After analyzing the Execution plan in detail, it turned out that SQL Server (Query Optimizer) doesn't generate an optimal plan. The cause of poor performance is Hash Match that had a huge 'estimated number of rows.' The solution to be implemented is using LEFT MERGE JOIN.


0

I think currently you are not able to log into the postgresql database? If this is the case you can get access of the database by changing the access authorization. Please open pg_hba.conf and change the following line: local all postgres md5 to: local all postgres ...


-2

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; BEGIN select @@SERVERNAME as ServerName, DB_NAME() as DatabaseName, Object_ID = (id), OBJECT_NAME (id) as StoreProc, Text from syscomments where text like '%Janice\SQL%DTS%'. END


0

This is possible. I have seen it in production, enterprise level. Cons: The subscriber has to be initialized and completely caught up with undistributed transactions before it can be set it up as a publisher. Initializing the second subscribers must be done via backup/restore. The re-distributor can block the first distributor if you create a snapshot and ...


3

Check sys.objects for the row that has the right value in name and see if the modify_date column is newer than when your script started. SELECT SYSDATETIME() AS ScriptStart INTO #scriptstart; GO ALTER VIEW ... GO ALTER PROC ... GO SELECT name FROM sys.objects WHERE modify_date > (SELECT ScriptStart FROM #scriptstart); This will list all the objects ...


1

May be this example ,will help you: ALTER VIEW dbo.vTest AS SELECT TOP(10) ProductName FROM [dbo].[TestData] GO IF @@ERROR <> 0 print 'ERR' ELSE print 'ok' GO ALTER VIEW dbo.vTest AS SELECT TOP(10) PoductName FROM [dbo].[TestData] GO IF @@ERROR <> 0 print 'ERR' ELSE print 'ok' GO And instead of printing the message, ...


2

Here's a very quick example that will create a user blat given an existing login blat in all new databases created (this won't work for attach/restore). You could extend this to adding them to roles, granting permissions, even building the list based off of some query from sys.server_principals in master, sys.database_principals in another database, or your ...


1

There are cases in Merge Replication when parent and child rows can be processed out of order on synchronization resulting in constraint violations and conflicts. To understand how and why this can occur and how it may apply to your specific scenario, I recommend reading through: How to understand Merge Replication article processing order Merge Article ...


2

In order to fix alert issues use database: msdb and First of all find out details on Primary Server by executing following.. PRIMARY SEVERE SELECT * FROM dbo.log_shipping_monitor_primary where primary_database like 'CHANGE_DB_NAME%' SELECT * FROM dbo.log_shipping_primary_databases where primary_database like 'DB_NAME_HERE%' SELECT * FROM ...


1

The solution heavily depends on how much work you are willing to put in your initial query and what is going to consume the data (and how). -- Option 1 -- If this is a one-off requirement, you can use variations of sp_MSForEachDB to retrieve a separate data set for each database. Personally, I don't like sp_MsForEachDB for a number of reasons, mainly ...


0

This ended up being a Kerberos problem. We were able to identify through some of the security logs and some network monitoring, that the request was coming in as anonymous. Once we applied the relevant SPNs for MSSQLSVC for the account that was being used to kick off the process, it worked.


0

My understanding of your situation is that you are backing up the database, and then transferring the file via the internet. I'm assuming that this is being done for troubleshooting or finding issues. (If these assumptions are wrong, that may change things depending...) You could try backing up the database across multiple files. To do that, you simply ...


1

I still see StatMan operations captured if I use SQL Sentry Plan Explorer PRO against SQL Server 2014: click to enlarge So, not exactly sure what sp_whoisactive is doing differently in this case (I've never used it to analyze statistics creation), but I can assure you that SQL Server 2014 still uses StatMan.


1

What is the autogrow model for the TempDB files? Tiny increments will incur many small autogrowths whereas large increments will incur fewer, but more intensive, growth routines. Presumably TempDB is growing because queries that are running on the server are constructing a lot of temporary tables, table variables, or cursors and so on? Is it known what ...


3

Numeric(9, 10) is not a valid data type specification. The scale must be less than or equal to the precision. I'll assume the actual column has a smaller scale specification. When you change a column from a precision of 1-9 to one of 10-19, the storage size increases from 5 to 9 bytes. This is a fully logged operation regardless of your recovery model ...


0

To fix this problem is very simple Fix/Workaround/Solution for Microsoft SQL Server, Error :4064 # First click on Option>> Button of “Connect to Server” Prompt. Now change the connect to database to any existing database on your server like master or msdb. Now click on connect and it will successfully log you in. Once you login in the system run ...


0

No need to refactor the single query into two separate ones. SQL Server should be able to figure out the most optimal way to execute the query on your behalf. Make sure the Culture table has a unique index on CultureTag and, in the case of a non-clustered index, add all of the columns needed by the query as INCLUDED columns.


0

In windows authentication, you don't have to type the password, it will validate by AD. The reason for Login error could be your login does not have access to the database server. Please ask the database administrator to grant access to your login.


3

I started writing this in comments, but quickly ran out of space so I'm putting in this answer. The way backups work with FULL recovery in SQL Server you can do what you want with transaction and differential backups (as said in the comments). A transaction log has all the statements since the last linked backup (be it a full, differential or trn backup). ...


2

Your index only has 679 pages. Ola's solution is set to ignore indexes with less than 1000 pages (see the @PageCountLevel parameter). You can override that so that it cares about indexes with fewer than 1000 pages, but why? Wasted effort IMHO. I would stop worrying about small tables like this - let Ola's solution do its job, and worry about fragmentation ...


4

Presumably your new column has values which are a certain datetime relative to 1-Jan-1900, or some other date. ...in which case, you could rename your column, and create a calculated column with your original column name, which is the number of second (or minutes or whatever granularity you want), so that you can SUM that instead. Example: SELECT t = ...


1

If your server's ip address is static, you can modify your hosts file with a reference to DBNODE2's ip address and specify DBNODE2 in your Connect to Server Server Name file.


0

As Pablo Montilla mentioned in a comment on the question, I should have added a code sample to indicate that I was using Dapper. (I have since added the sample.) The solution was to pass an argument to Dapper's Query method to tell it not to buffer all of the results before letting me iterate over them. The default behavior of Dapper is to buffer the ...


3

If your CHECKDB operations are getting blocked by FT CRAWL, a reasonable solution would be to turn full-text search off for the database after it has been restored. After all, it's not as if you are using DBCC CHECKDB to validate that full-text search is doing the right thing, or that you are restoring this copy of the database in order to facilitate FT ...


0

I think there is a case for changing the indexes here because a) you have a task to do (these multiple queries) b) data warehouse volumes (25+ million rows) and c) a performance problem. This would also be a good use case for non-clustered columnstore indexes introduced in SQL Server 2012, ie summarise / aggregate a few columns on a large table with many ...


1

Dates in Clarion TPS files are stored as a Clarion Long, which is an integer. They are defined as the # of days elapsed since December 28, 1800. The valid Clarion Standard Date range is January 1, 1801 through December 31, 9999 February 27, 2015 - the date Nimoy died - is Clarion date 78,223. A Clarion String is a CHAR. A Clarion Cstring is a VARCHAR - ...


15

A full analysis would require access to the execution plans, table and index definitions, and database statistics (or a copy of the database itself). That's possibly unrealistic, so here's some general observations, and a possible solution for you to try. (Strictly, this question is probably beyond this site's remit.) General background The SQL Server ...


3

You can't use dynamic SQL inside a function (well, aside from this awful hack). The main reason is that functions can only return data and can't have side effects. There is no way for SQL Server to prevent you from passing an insert or an update to sp_executesql (as an aside, that looks like a SQL injection attack vector no matter how you end up implementing ...


0

I cannot figure out how to get the Windows Owner associated to that PID. You can get the owner using PowerShell: # Identify the name of the remote computer you want to query $computerName = 'SomeRemoteComputerName' # Invoke the command on the remote system to get the information that is necessary Invoke-Command -ComputerName $computerName -ScriptBlock ...


3

Source control. This way you have a centralized store for all of your scripts so that any DBA/Developer can access them and use them if needed. Tracking changes to core scripts used day to day in your environment is critical. You could convert them to stored procedures so that they are included in the database backup it's self but this doesn't provide an ...


4

The question is mainly about how to optimize the select statement: SELECT [TABLE], [FIELD], [AFTER], [DATE] FROM mytbl WITH (NOLOCK) WHERE [TABLE] = 'OTB' AND [FIELD] = 'STATUS' Removing the redundant projections and adding the presumed dbo schema: SELECT [AFTER], [DATE] FROM dbo.mytbl WITH (NOLOCK) WHERE [TABLE] = 'OTB' AND FIELD = 'STATUS'; Without ...


0

I suppose your table or its index is corrupt. If so, then you need to create a query into the corrupted table. Next try to exclude all the damaged rows. For example, if the corrupted row is between ID 13 and 15, try criteria of: <= 13 Or >= 15 When you get the complete set of the undamaged records you can restore them, change it to a make table on a ...


17

That's because of Instant File Initialization. In short, SQL Server can take advantage of this privilege for database data files (not transaction log files). What this means is that SQL Server does not have to zero out the data file(s) when initializing. Without the "Perform volume maintenance tasks" privilege granted to the SQL Service account, upon the ...



Top 50 recent answers are included