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 ...


8

As far as I know, trace flags 8722, 8755, and 8602 were never officially documented. The last time I remember them being effective was in SQL Server 2000, so it is not terribly surprising that you find they are ignored in SQL Server 2012. For specific query patterns, it is often possible to remove the FAST n hint using plan guides. Even so, the best fix is ...


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

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 ...


5

You could also solve this with a trigger that populates the copy of the table on insert/update/delete. It wasn't clear in the question that these tables are actually on different servers, and that the subscriber was unreliable. In that case you could simply log ship to the subscriber - you can get pretty close to real time here, though you will have to kick ...


5

Well, you could consider a filtered index - if you're always looking for rows where IsSynchronized = 0 and this number should be relatively small, then instead of those two indexes, consider this instead: CREATE NONCLUSTERED INDEX [IX_NotSynchronized] ON [dbo].[PackageEvents] ([PackageID]) INCLUDE ([EventDate], [EventDescription], [EventID], ...


5

Even if you have plenty of DRAM, tempdb may still be used. This happens in a few situations: Snapshot isolation: Using this feature can create a lot of tempdb activity. Hash and sort Spills: When the optimiser creates a query plan, it will try to estimate the total amount of memory it needs to run the query. Before the query runs, the estimated memory is ...


4

Looks like you're using a tool not capable of identifying benign waits. See Wait statistics, or please tell me where it hurts. There is no impact, just bad monitoring. I recommend you read Filtering out benign waits. The engine has certain background tasks and when these background tasks don't have any work they simply sit idle, waiting for work. while ...


4

You could use DATETIMEFROMPARTS (Transact-SQL) select datetimefromparts( datepart(year, getdate()), datepart(month, getdate()), datepart(day, getdate()), datepart(hour, @testTime), datepart(minute, @testTime), ...


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

I think this does what you need. USE 'yourDB' GO SELECT OBJECT_NAME(p.[object_id]) BlockedObject FROM sys.dm_exec_connections AS blocking INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id ...


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

In my case I was able to narrow down my trouble by working with my sysadmin about how the group was defined. Knowing that the SQL Server was hosted on a machine that had joined another domain and was running with a user account on that other domain, we looked at the Group Scopes in active directory. My user could authenticate properly after the scope was ...


3

I believe you could use database snapshots. Though snapshot can not replace full database backup, since it contains only amended pages from the moment the snapshot was created. Essentially, you restore the production database onto the development server, take a snapshot, perform whatever test work you need to. When you need to return to the original state ...


3

Without seeing the actual query and plan, we are shooting in the dark. Depending on how the query is actually written, it might benefit from an index such as: CREATE NONCLUSTERED INDEX [IX_IsSynchronized_PackageID_etc] ON [dbo].[PackageEvents] ( [IsSynchronized] , [PackageID] ) INCLUDE ( [EventDate] , [EventDescription] , [EventID] ...


2

From what I can see you have two problems. The first is the doubling. At a guess it is because of the SELECT statement in your INSERT statement. SELECT @NewMetricID, NULL, sm.sortorder, ISNULL(sm.MetricOrder, 1), ISNULL(sm.CategoryOrder, 1), sm.RptCurrentGroup, 'System', ...


2

I'm answering my own question here because we finally figured out the problem. Short Version: We added a third column to the nonclustered index. Deadlocks disappeared. Long Version: First, check out James Rowland-Jones' dynamite blog post about lock hashing collision (My explanation will be nowhere close to the quality of his). From the blog post: ...


2

Perhaps something in their documents\user data profile got corrupted. If you've fixed the space issue, then I suggest running a repair on the application (or installing a newer version - you can manage SQL Server 2012 with SSMS 2014, and it's free). However it may still end up being hosed because of that user's profile - if so, as a next step, you could back ...


2

During Hurricane Sandy (natural disaster) and recently when moving our data center (managed failover), I was in your situation. I have implemented Logshipping and was able to efficiently failover during the above scenarios. The technique that I used was reverse logshipping (swapping roles between primary and secondary) - which allows you to swap the log ...


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. ...


2

Notwithstanding the excellent answers already added to this question, there is an explicitly defined order of precedence for conversion of data types in SQL Server. When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the ...


2

Considering that: REPLACE, PATINDEX, CHARINDEX and even LIKE have not been updated to fully and properly deal with LOB/MAX types; and, you need to repeat this process across hundreds of databases and against multiple instances I would strongly recommend you look at doing this outside of SQL Server, probably in PowerShell or C# (not CLR inside SQL Server, ...


2

I don't have hands-on with this but have observed someone else go through the same thing. To keep the old history you might want to migrate it to a separate table. When the jobs get created, the default retention period is 72 hours. Unfortunately I don't know how to change that because the system procedures also run the job for you, before you have a ...


2

Yes, it's possible: select Id, -- Remains as in table cast(MandatoryIntCol as int) as [MandatoryIntCol], -- Makes nullable isnull(OptionalIntCol, 0) as [OptionalIntCol] -- Makes mandatory from dbo.MyTable; Got myself the hassle of creating similar wrappers recently to stop Entity Framework messing with views' keys...


2

The supported upgrade path does not go directly from 2000 to 2012. You will have to have an intermediate step where you upgrade or restore those DBs to a 2005 or 2008 R2 instance. Once you've done that, you can upgrade to 2012 or take a backup of the DBs in 2005, then restore them on to 2012. We are migrating to 2014 as my company is on 2005 SP3 right ...


2

Seems like an appropriate way to do it. Create a logging table: CREATE TABLE dbo.LogSpace ( dt DATETIME NOT NULL DEFAULT SYSDATETIME(), dbname SYSNAME, log_size_mb DECIMAL(22,7), space_used_percent DECIMAL(8,5), [status] BIT ); Do this before and after your load: INSERT dbo.LogSpace(dbname, log_size_mb, space_used_percent, [status]) EXEC ...


2

The WRITE method is minimally logged. If you use regular UPDATE statement, it would result in overwriting the entire string using FULL LOGGING. This would become inefficient when dealing with large updates. To support update for large value data types, the UPDATE syntax supports .WRITE method. This will result in less Transaction log due to its nature of ...


1

You can create a simple SSIS package: Here's a high-level how to: Create a OLEDB connection to the database in the connection manager Drag a Data Flow Transformation into the Control Flow and then click on it to get to the data flow. Drag a OLEDB Source from the toolbox into the data flow, and edit it so it connects to your desired table using the ...



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