Tag Info

Hot answers tagged

14

If you are referencing the actual database file consumption on the volume, then SQL Server doesn't handle that automatically. Just because you removed data from the database doesn't mean the database files will shrink to fit only the existing data. What you'd be looking for, if you have to reclaim space on the volume, would be shrinking the particular file ...


10

The detailed behaviours of ISNUMERIC are not documented, and probably not fully known to anyone without source code access. That said, it may be that interpretation depends on the Unicode categorization (numeric or not). Equally, the weird cases you mention may be bugs that are preserved for backwards compatibility. Yes I know that sounds crazy, but it does ...


8

The ASCII backslash (code point 5C) happens to share the same code point as the yen sign (¥) in the Shift-JIS encoding used by the Japanese version of Windows, and the won sign (₩) in Korean EUC-KR. Hence, it is very likely just a continuation of the currency sign theme.


6

Determining precision and scale resulting from expressions is a rat's nest and I don't think anyone understands the exact rules in every scenario, especially when mixing decimal (or float!) and int. See this answer by gbn. You can of course tailor the expressions to give you what you want by making much more verbose explicit conversions. This is probably ...


5

To ensure you can see the objects you are looking for: Make sure you are in the right database / right instance - seems simple and silly but it happens all the time. This also includes not using things like AttachDbFileName in your connection string - this means that two different programs will actually have two separate copies of your database, so if one ...


5

Have your DBAs create Adam Machanic's - sp_whoisactive Brent's - sp_AskBrent and grant execute permission to you. That should be a good starting point to find out why your sql server is slow. Note: Brent has already mentioned on How to Grant Permissions to Non-DBAs. You can follow that method to ask your DBA to grant permissions to you.


5

The clr enabled server configuration option only controls whether user assemblies can be run by the SQL Server instance. The hierarchyid, geometry and geography types are system CLR types. These are contained in system assemblies, so are available regardless of the clr enabled setting. Similarly, other system features that rely on CLR integration, like ...


5

This is normal behavior when you truncate table and which involves removal of more than 128 extents as Per Books Online When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after a transaction commits. This implementation supports both ...


4

Here is a rewrite of what you already have. No shredding necessary on root node. Specify the text() node for the parameter name and parameter value. In my limited tests that made it significantly faster. I look forward to see what if any performance gain you see on your side. select T.TestId, T.TestData.value('(/root/@name)[1]', 'varchar(max)') ...


4

I appreciate that you are looking for anecdotes about the experience of using mirroring; however this site is not really the place for opinion. Having said that, you may find the following excerpt from Microsoft.com regarding database mirroring helpful: Database mirroring is a simple strategy that offers the following benefits: Increases availability ...


4

As is the command is trying to execute on your LOCAL instance and not REMOTE. You need to have it execute through the linked server itself. You can build the query as a dynamic statement and then simply execute the query against sp_executesql on the linked server. So something like this should: DECLARE @qry varchar(2000) SET @qry = 'BACKUP DATABASE ...


4

As Aaron Bertrand mentioned, expressions are very tricky to predict. If you dare go there, you could try to gain some insight using the following snippet: DECLARE @number SQL_VARIANT SELECT @number = 0.15 / 360 SELECT @number SELECT SQL_VARIANT_PROPERTY(@number, 'BaseType') BaseType, SQL_VARIANT_PROPERTY(@number, 'MaxLength') MaxLength, ...


4

Here's my go-to query to pull the events that Aaron mentioned (availability_replica_state_change). This script also takes the liberty of converting the UTC time to your local time: declare @xel_path varchar(1024); declare @utc_adjustment int = datediff(hour, getutcdate(), getdate()); ...


4

The answer is right there on the last line of the error message: Sql client not registered You need to install the SQL Server Client Library on the machine in question. You can obtain that package from http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409 Also, Isolation Level = ReadUncommitted may be problematic if you don't fully understand ...


3

Thomas Stringer has answered a very similar question at: Transaction Log Maintanance While Using AlwaysOn Availability Group The answer is that the Availability Group replicas are aware of each other and when either the primary or secondary does a backup, both logs are able to free log space. The article you referenced also offers the explanation that Log ...


3

There is no way to prevent SQL Server from writing to the transaction log. In the simple recovery mode, as soon as a transaction finishes its log records are marked so that they can be overwritten but the transaction is still written to the log. This means that a large transaction can still expand the log, if the current size of the log is too small. You ...


3

Yes. Transparent Data Encryption doesn't change any of the internal processing of queries. Database pages are transparently encrypted/decrypted during I/O (i.e. when they are read from or written to disk). In-memory query processing is therefore unaffected, so indexes continue to function exactly as they did without TDE. TDE is designed to negate the need ...


3

I see you're combining Failover Cluster Instances (FCIs) and Database Availability Groups. That's fine, except that the installer won't allow you to set up an availability group on failover cluster instances that can be owned by the same servers, just like the error message says. In other words, the set of servers that can run HRITS\BAK can't overlap with ...


3

If this is a PRODUCTION environment, definitely DO NOT CANCEL recovery. This will only prolong your pain. DO NOT RESTART SQL SERVER. Wait until recovery is finished, no matter how long it takes. Ensure you have a good backup. Reduce the Virtual Log File count on databases that have an unreasonably high number of them. Set autogrowth on the database to ...


3

If your current database is made up of one data file and one log file then that is how it will be restored. What purpose does the network admin in doing this if the current database is one single data file already? Does network admin understand how file structure works in a database? If you just wanted to split the tables and indexes between multiple ...


2

It would be impossible to say for sure without examining your web application and the SSMS query. My guess, however, is that the long query is locking tables which the web page wants to read, so the page has to wait for the query to finish. The page's timeout occurs before this happens and the page fails to load. The concepts your are dealing with are ...


2

From my post about this issue here (see #1): DECLARE @Search1 NVARCHAR(4000) = N'%a03dc6109c6f53ce93203f1b85c7d31d%', @Search2 NVARCHAR(4000) = N'%a03dc610-9c6f-53c-e932-03f1b85c7d31d%', @s NVARCHAR(MAX) = N''; ;WITH t AS ( SELECT t.[object_id], [table] = t.name, [schema] = s.name FROM sys.tables AS t INNER JOIN sys.schemas AS s ON ...


2

From reading the BOL article on sp_add_alert [ @severity = ] severity - The severity level (from 1 through 25) that defines the alert. Any SQL Server message stored in the sysmessages table sent to the Microsoft Windows application log with the indicated severity causes the alert to be sent. severity is int, with a default of 0. If message_id is used to ...


2

Just tested out running setup in Quiet mode on a Windows 2012 R2 Azure VM in Core mode to get the discovery report and it worked. Steps to reproduce: exit sconfig with option 15 (Exit to Command Line) Navigate to SQL Server installation media, in my case (and other Azure VMs from the gallery with SQL Server installed) it's "cd c:\SQLServer_12.0_Full" run ...


2

It's really hard to say how long your rebuild will take, as SQL itself doesn't really know in advance and cant give you an estimate. You can use the following query to use the dm_exec_requests dmv to view how long your index rebuild has been going on for, and to verify that SQL doesn't really have an estimate: SELECT ...


2

If one database successfully restored, it certainly does not mean that the next database has no problems. (E.g. I once, many years ago now, had some disk array problems that resulted in about 20% of the databases on a server manifesting corruption.) I run a weekly process that RESTOREs and runs DBCC CHECKDB on all databases from over 70 servers. This is ...


2

LOG_REUSE_WAIT_DESC will show LOG_BACKUP any time there has been at least one transaction that has not been backed up (when the database is in Full Recovery mode). You can see active sessions, requests, and transaction count for each session, by looking at the results of this query: SELECT SessionID = s.session_id , HostName = s.host_name , ...


2

When using the backup files please make sure to use the logical filename. When using multiple backupsets within the same backup files, please make sure to check that the file being used is correct. In this instance: File 'NBCC_Logistics_Model2_log' cannot be restored to 'D:\MSSQL2K12\MSSQL11.DEV\MSSQL\DATA\NBBC_Logistics_log.ldf'. Use WITH MOVE to ...


2

I'm not certain about what you are seeking. You want a "health check" but that may not always tell you why this particular query (whichever query you're running at the moment) is suddenly exhibiting performance issues. There is a system_health event session (here's a set of queries to shred the contents) which will give you all kinds of information that you ...


2

Are you familiar with the SELECT .. INTO syntax? It's a useful trick for deconstructing situations like this because it creates a table on the fly with just the right data types for the given SELECT list. You can break up your calculation into its constituent steps, applying SQL Servers' precedence rules as you go, to see how the definition changes. ...



Only top voted, non community-wiki answers of a minimum length are eligible