Tag Info

New answers tagged

0

Partitioning is ideal for this sort of thing. For that you would need to define a key, however. If you really just want to slice a big pile of rows into small piles how about something like select top 1000000 <your columns> into dbo.SubTable1 from dbo.ReallyBigTable; delete dbo.ReallyBigTable where ID in (select ID from dbo.SubTable1); Repeat ...


0

It turns out that changes in table structure (schema) is automatically handled, so changes in the primary database should be also reflected in the secondary ones. Schema changes and data changes are essentially the same. It works like traditional mirroring today : what happened in the log on the primary happens on the secondary. Not everything that ...


0

First, do your have columns of data type TEXT or NTEXT in your database? If select c.TABLE_NAME,c.COLUMN_NAME from INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.tables t where c.DATA_TYPE in ('ntext','text') and t.table_name=c.table_name and t.table_type='BASE TABLE' returns any rows, you probably lost a lot of data during this attack, as the ...


1

Ok I got the answer. I used the following: EXEC sys.sp_cdc_add_job @job_type = N'cleanup' ,@start_job = 0 ,@retention = 52494800; This adds the cleanup job but does not start it immediately i.e. start_job set to 0 does the magic. Retention can be set accordingly (100 years in my case as we do not want data to be deleted)


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


1

My first thought was to restore the backup. At 1TB this becomes impractical, however. Log shipping would not allow you to write to the pre-prod instance either. I think it should be possible to engineer something around table partitioning - assign each partition to its own filegroup, perform backups after the nightly batch and restore that to pre-prod in ...


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


0

I did not find the cause for this, but the following while not best solution does enable command. It is my hope it points to the problem and someone can provide a better answer. I use Microsoft SQL Server Management Studio (Administrator) Under localhost Security logins Double click user, select Server ...


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


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


0

You should enable trace flag 1802 on your database servers. This will allow SQL Server to retain inheritable permissions when you detach a database so you won't experience this issue going forward: http://support2.microsoft.com/kb/922804 DBCC TRACEON(1802, -1) Add it to the startup parameters of the SQL Server service ...


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


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


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


0

When reviewing Microsoft's 'High Availability Solutions' (http://msdn.microsoft.com/en-us/library/ms190202.aspx) there's an interesting note toward the bottom: 'If you are running an edition of SQL Server that does not support AlwaysOn Availability Groups, we recommend log shipping.' Note this is even with 'SQL Server 2014' chosen as the version at the ...


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


1

In addition to Tom's and Shanky's answers, if your database contains LOB/BLOB data the DBCC SHRINKFILE may not work. If that is the case, then you have two options, depending on whether you can take the database offline or not. If you can take the database offline, then you will need to copy the data out and copy it back in to remove the empty space. You ...


0

This is covered in Frequently Asked Questions for Replication Administrators: Are tables locked during snapshot generation? The length of time that the locks are taken depends on the type of replication used: For merge publications, the Snapshot Agent does not take any locks. For transactional publications, by default the Snapshot Agent takes ...


1

Trying to guess your requirements here, You want to "remove" rows that are part of div-Purchase pairs. If by remove you mean don't select them, i.e. filter them out in a where clause, then something like this might work: with TestCTE as ( select ID, [Partner], [Type], Amount, LEAD([Type], 1) OVER (ORDER BY ID) NextType, LAG([Type], 1) ...


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


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


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


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


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


0

I don't use Access, but it is likely that it is unable to query extended properties except those under the dbo schema - it must make the assumption that everything is under dbo. I don't really have a solution for you other than not using schemas with your ADP if extended properties are critical.


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


0

I did not see complete answer to your question so I would add an answer. I would like to know if there is a way to prevent SQL Server from maintaining the log file at all. No not possible every transaction in SQL server is logged in some way. Logging depends on recovery model you use. In simple recovery model almost enough information would be logged ...


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


0

You can check the %age of log space used first by running command DBCC SQLPERF(LOGSPACE). The check space used column which seems high percentage, in this case it will be your database Shrink the log file by DBCC sHRINKFILE([dblogfilename],1) and run above command, to check how much it reduced.


0

The answer to my specific instance of this issue was permissions. The DBA had to add datareader rights to the system schema on master for my user profile. Once this was done, everything worked as intended. He tried adding dbo owner rights, and granting view definition to just sys.procedures and that did not work. The bizarre symptom is that I could still ...


1

The components for SQL Server 2012 are divided by server and then management tools. You can get the server level components by using a PowerShell command like: Get-Service *SQL* To get the management tools would require either registry search for the uninstall list or you can query the WMI class win32_product: get-wmiobject win32_product | where ...


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


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


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.


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


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


0

I can reproduce this but I think you have a few options: not worry too much about full-text index bloat. Not many people are going to be searching for 'rtf1', 'pard' or 'wmetafile0' and I don't think it will affect your performance that much. You might get the odd clash if someone is searching for 'red' or 'blue' but I'd say this is fairly low risk. ...


0

In case if you are planing to purchase SQL Server 2014 and the application don't suppport it and needs a downgrade, you can go for SQL Server 2012 or change in application to support 2014 version. I think you can downgrade, how-to-sql-2014-licensing-changes, contact Microsoft support team for more details. also check ...


0

Its best to contact SQL Server licensing expert to get accurate information about SQL server licensing. As far as I am aware with SQL server 2014 Microsoft has introduced downgrade rights but again for more information you need to speak to expert. I am sure you can install SQL Server 2012 with 2014 license but ask Licensing expert how to downgrade legally. ...


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


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.


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


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


1

You can also use T-SQL use master; go alter availability group [ag_name] add database ReportServer backup database ReportServer to disk = N'\\some\shared\location\available\to\all\nodes\ReportServer_HADR.bak' WITH FORMAT, INIT, COMPRESSION, STATS; GO backup log ReportServer to disk = ...


0

That sounds like it is using an inappropriate query plan. There are two obvious paths this query could take and it is presumably deciding between them depending on the values of @row_offset and @row_count: for a small offset it is probably better to scan SomeCol,SomeOtherCol in order and apply the complex filtering clauses (in the WHERE and JOIN ON clauses) ...


1

Okay I found the solution after searching and testing many things. The issue was in the Metadata (the connection between the Source Query and the Data Conversion), the length of the field was not matching the one from the source query anymore, which is to me, very odd as I thought that the Metadata would be automatically refreshed when you save and compile, ...


0

This isn't a solution, but a strange work around. I had exactly the same issue, it was down to just one column that contained a 6 character alpha numeric code in type Char(6). My source was a sql script, my destination was an nvarchar and I had a converter in between. Nulls, where ok so I restricted the import to a single record with a value. I found I ...


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



Top 50 recent answers are included