Tag Info

Hot answers tagged

5

Making the assumption that you are using SQL Server (because the RDBMS is going to matter here) you can do the following ALTER TABLE tablename REBUILD That being said you can read this article by Paul Randal as to why you shouldn't. Unless you are using your table as a staging table where you want a quick import but then clean the table out later anyway ...


3

It could happen that a small amount of data reaches a certain limit in the SQL Server to force another plan or something like that. This is not unlikely. But the fact that your disc seems to be heavily under duty takes me to another conclusion. There are 2 possible base reasons for your slow down. You upgraded your system and rebooted it You load a bunch ...


3

When comparing values of different datatypes SQL Server follow the Data Type Precedence rules. Since nvarchar has higher precedence than varchar SQL Server has to convert the column data to nvarchar before comparing values. That means applying a function on the column and that would make the query non-sargable. SQL Server does however do it's best to ...


2

SSIS code behind is actually a XML value and all components of package will be represented as XML node values within it.This XML can be parsed to identify if it has a reference for our searched table or column. Finding SSIS Packages having References to a Table or Column will help you.


2

This should give you a start. It assumes that you've already cloned all the relevant objects and that all of your permissions are at the object or schema level (if you have column-level permissions, the script gets a little more complex, but not too bad). DECLARE @oldschema SYSNAME = N'schema1', @newschema SYSNAME = N'schema1_dev'; DECLARE @sql ...


2

Check this article. According to that article the transaction log backup is the only way how to reduce the size of the transaction log in the FULL recovery mode. Shrinking will help only in a situation when you for example have forgotten to take transaction log backups and your transaction log file grows and then you take the backup and you need to shrink ...


2

If you are sure that everything is replicated to all subscribers and you really need to shrink it right now, you can try this. Execute the following snippet inside your replication database. It will mark all replications as successful and your replication queue will be empty. Be sure that this is really the case! Otherwise you may loose some elements which ...


1

You can, but you don't want to. Growing the transaction log is an expensive operation because not only does it need to allocate the space on disk, but it needs to write zeros to all of the space allocated. Any operation that needs space in the log and doesn't have it will need to wait for that zero filling to complete. Which kills performance. Shrinking ...


1

Here is your answer: MSDB on C: 86% of 32GB io_stall_write_ms : 192217 If MSDB is on c: master is on c: as well and I'm almost willing to bet that tempdb is still in the default location as the average I/O stall on c is 19 seconds! First, check if tempdb is on c:\ or has any file on the c:\ partition and if so move it away Check if the master database ...


1

You can easily change the schema ownership to dbo, then you should be able to drop the user, and add them back using ALTER AUTHORIZATION: ALTER AUTHORIZATION ON SCHEMA::[schema_they_own] TO dbo; DROP USER [user_name]; ALTER AUTHORIZATION ON DATABASE::[database_name] TO login_name; Now, they will be the dbo user in the database (and will have automatic ...


1

An owner of a database object has full rights on it automatically and unrevokably. But of course you can give an user all rights to a schema without making him the owner.



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