Tag Info

New answers tagged

7

To answer the specific question of running a SQL Agent job on multiple servers (definitely a good question) and, for the moment, ignoring the particular thing that you want the job to do (which could be another question): SQL Server / SQL Server Agent actually comes with a facility that makes this type of thing very easy: multiserver administration. You set ...


0

This is likely due to a corruption in your file system. The best answer is to restore from a good backup and reapply the transaction logs. For a brief explanation see: http://belajarvps.com/error-3403-in-sql-server/ "After the SQL crash, SQL Server automatically tries to recover. While it occurs, it scans the complete transaction log till the last ...


1

If you have time to plan ahead, there's a much easier solution... (usually) The long locks are almost certainly caused by page splits at the storage layer. So force them on your own schedule. Add a NULL-able temporary column with datatype VARBINARY(8). Find available slack time in the database to update batches of the existing records with a valid ...


1

The solution to this issue was due to the fact that the way the code was being pulled from the database. In coldfusion if you are going to query the numeric value from the database, you must use a sql query param and specify the return type of the data.


0

We implemented redgate sql monitor, and a custom monitor in that, with a tsql statement that grabs the counter for that object (table, index, whatever) waits a minute, grabs counter again and subtracts. Works v nice!


0

Quick and dirty example for smaller data sets. For larger data sets I definitely recommend checking out the link Aaron Bertrand provided: http://www.sqlservercentral.com/articles/paging/69892/ declare @pagewidth int, @pagenumber int select * from ( select *, row_number() over (order by PAGINGCOLUMN/S) as cnt ...


3

Instead of 7 Subqueries, you can just use one query. This should be a large performance gain: SELECT owner [User], SUM(CASE WHEN OrderType = 1 THEN 1 ELSE 0 END) [First], SUM(CASE WHEN OrderType = 2 THEN 1 ELSE 0 END) [Second], ... COUNT('x') [Total] FROM MyTable mt WHERE status NOT IN (2,3) AND ActionDate >= CAST(GETDATE() AS ...


0

For me, using sp_replicationdboption did not solve the problem. However, this statement did clear the log_reuse_wait value from 6 (REPLICATION) to 0 (NOTHING). USE master EXEC sp_removedbreplication @dbname = 'your db name' GO


2

One approach would be to use optimistic locking. Before displaying the update form, you retrieve all the original values from the database. Or, at a minimum, a column that is guaranteed to change on update, such as last_update_date or similar. When updating you check that all values are still the same in your where clause. If the update fails to update any ...


0

Case: Accidently When u run below command on Principal server instead of mirror server: alter database 'Database_Name' set partner off After running above command your DB goes in [mirror, disconnected] State on mirror server. Resolution: Step 1:Run below query on mirror exec sp_resetstatus 'Database_Name' Step 2: After running below command your DB will ...


0

I have used a merge statement to do this in the following format: merge into [dbo].[table] using [dbo].[Stage_Table] on Stage_Table.pk = table.pk when not matched then insert (val1) values (1234); Re-reading your question, this probably wouldn't have efficiency you are looking for, but may be useful information for other readers.


0

You can also use the EXCEPT clause in your insert statement. It will most likely be faster than the IGNORE_DUP_KEYS option. See here for details and a benchmark: http://www.sqlservercentral.com/Forums/Topic856838-392-1.aspx A third option is to use the MERGE command. A fourth option is to use the where not in trick


-1

It will give you the latest refresh date for your DB: select top 1 * from restorehistory where destination_database_name='DB_NAME' order by restore_history_id desc


0

This would be an unusual deployment pattern: normally you'd want to have 1-to-1 correspondence between an application and a database. This mainly is because you'd want your development environment to mirror your future production environment as much as possible. If you cannot create databases on the server you could still look at pre-creating a set of ...


2

There are many ways, you could: 1) Dettach the database and copy the database files (mdf and ldf) over to the other computer then attach it. 2) Backup the database to a .bak file, copy that over and restore on the other computer. 3) Use the Import Export wizard to move just the table into a new database on the other computer. 4) If you need to do this ...


1

This actually wasn't nearly as hard as I initially thought, but I am putting this up here so that others can find this. The first step is to create a data source that connects to a server that already has the stored procedure/s on it. You can't skip this step, otherwise you won't be able to easily build your tables, matrixes, etc. because Visual Studios ...


0

You can use the following to list orphaned sql users: USE foo go EXEC sp_change_users_login 'Report'; and USE foo go EXEC sp_change_users_login 'update_one', 'foouser', 'foouser'; to fix each one.


0

I was trying to Shrink the files, but only had 10GB's of space left on the Array. Get another LUN assigned to you. Extend the LUN. Buy a bigger disk. Add an external drive. Do something other than compromise your data because of disk space shortages. This is a problem that can be solved without ever touching your database in ways it doesn't like. I ...


4

You can discover all of these events that are in the current event log cycle using sp_readerrorlog: EXEC sys.sp_readerrorlog @p1 = 0, @p2 = 1, @p3 = N'OFFLINE'; You can cycle through values of @p1 if you don't find it in the current event log. By default you should be able to read the current and prior 6 error log files, so use 0-6 as the arguments there ...


2

If the database was set offline: i.e. alter database AdventureWorks2012 set offline; go You would indeed see a logged message in the SQL Server error log: Setting database option OFFLINE to ON for database 'AdventureWorks2012'. select * from sys.messages where language_id = 1033 and text like '%setting database option%for database%'; Message ID ...


1

This is a perfectly valid practice, though it's more commonly used for inserts than updates. This is discussed elsewhere on Stack Exchange (e.g., http://programmers.stackexchange.com/a/78598/25946). It's worth confirming, though, that the whole time required for "create index, update, drop index" really is less than making the update without indices. You ...


6

Building temporary indexes for ETL jobs is not necessarily bad practice, as the index builds are fairly quick. Where it might not be so efficient is if you have relatively small incremental updates on very large tables, but it sounds like this is not the case here. The only caveat is if you expect the tables to grow substantially with time. If they are ...


2

The only thing which I can think of which could be bad is that while building indexes new inserts/updates on the relevant tables could be problematic. But if there is no use-case for changing the data while building indexes it is a fast and viable approach. You save time and space.


7

If it works for you then it's a good practice. There are basically no hard rules for databases.


2

Using recursive CTE. Is this what you wanted? sample data: /* CREATE TABLE #Table ( CategoryID INT , Name VARCHAR(MAX) , IsParent BIT , PCategoryID INT ); INSERT INTO #Table (CategoryID, Name, IsParent, PCategoryID) VALUES (1, 'IIT', 1, 0), (2, 'IIT Maths', 0, 1), (3, 'IIT Chemistry', 0, 1), (4, 'IIT Physics', 0, 1), (5, ...


0

I resolved the issue - but do not understand it. The system has a View corresponding to each Table. One of the Views was used in my FOR XML PATH view. I substituted the no-permission Table for the View, and it works. The View has Public SELECT permissions. The Table has no named permissions. I think the SQL Server Agent Job should be able to SELECT ...


0

If i am getting you right and what i also witnessed when the job did not ran an update code because of the limit of the text size with hold by sql server agent job: Therefore you can try something like limiting the amount of data returned via sql agent job as The default TEXTSIZE for SQL Agent is 512. You can specify the text size as per output you get ...


1

What a brittle solution. Does the return value of sfn_GetSecondDbName() really change that often? Where does its value come from? Would be much easier to hard-code the database in the trigger, and change the trigger when that changes, rather than change the function and try to "save work" by not having to update the trigger. Your current approach will ...


2

I looked into doing this years ago and looked into both BCP and SSIS. Neither one really provided for great flexibility. BCP needed format files to get the text-qualifiers, etc. But this was for a dynamic backup solution for a subset of tables to get archived nightly. SSIS seemed a bit more dynamic (though not fully), but couldn't do a few of the things that ...


0

Not sure what your problem is... the three views solution sounds best. If consistency is your problem with that then automate the creation of the views. Use a function to return the common "logic" Create function fn_GetStaticViewElement() return CONVERT(VARCHAR(MAX),',SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as ...


3

It seems you just need to use a derived table or common table expression with a new grouping level: DECLARE @FROM datetime = CONVERT(datetime, '12-30-2013 03:00:00', 101), @TO datetime = CONVERT(datetime, '12-28-2014 03:00:00', 101); WITH Combined AS ( SELECT DivisionName = d.DivisionName, [CC w/o Tips] = (SUM(PaymentAmount) ...


6

This is a bug, though one that only arises in very specific circumstances. SQL Server uses a component called Distributed Query (DQ) to build T-SQL commands to run against the linked server. The optimizer transforms a CASE expression including a sub-select to a form that works perfectly well on local objects, but which is not translated correctly to remote ...


2

You need to grant rights to use the function. GRANT EXECUTE ON Function to UserOrRole


0

I was having this same issue using SQL server 2008r2. I was trying to duplicate a database (versioning foo) and ended up confusing SQL server. SQL Server Management Studio thought that the database existed (the MDF and LDF did) but the master database didn't think the database existed (didn't show up in sys.databases) The trick was to rename the MDF and ...


13

A few suggestions: Remove the DISTINCT as the ID is the primary key. There is no way you'll get duplicate rows in the result. Do not convert the datetime columns. This makes your conditions non-sargable and the query will always do a table scan. The variables would need no conversion either if they are declared as dates but that is not a problem for ...


5

Remember that S: is relative to the server, not necessarily where you're running SSMS from. I've seen plenty of people query the location of files and find they're not actually accessible from where SSMS is running. If S: is part of a user profile, then it could be associated with the service account even.


-1

Microsoft error no 1418:- this error occurs due to communication problem between endpoints.first of all you have to check TCP/IP for the instance was enabled or not,if any duplicate endpoints exists,in case login was not created inn the other instances of mirroring pair,check for the edition of principal and mirror servers.


2

It is difficult to tell just from examining the code if it is running forever because of an infinite loop or blocking - my guess is the latter. But here is a slightly different approach that avoids some of the things that could be problematic: CREATE PROCEDURE [dbo].[pr_BuscaLog] @qtdDias INT AS BEGIN SET NOCOUNT ON; DECLARE @cmd NVARCHAR(MAX) = N'', ...


1

The select statement Select count(1) from #tmp in the while loop is effectively a constant value. In other words, it doesn't change each time round the loop. I'm guessing you really want to loop once per database, so you'll need to modify your while loop.


-2

SELECT * FROM msdb.dbo.restorehistory WHERE destination_database_name = 'YOUR-DATABASENAME';


1

Querying the plan cache to look for the indexes involved in the calculation of finding missing indexes DMV's can be looked from below pasted link: http://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/ and refer to link as well for more explanation on the same ...


1

That's pretty much all there is to it. You may want to backup or transfer your database files/backups before the un-installation process, but getting rid of the application is that simple. As per Mirosoft Support KB 955499, Open the Programs and Features item in Control Panel.To do this, click Start, type appwiz.cplin the Start Search box, and then ...


4

Short answer: you can't with 100% accuracy. Long answer: you can query the plan cache to identify plans with missing index warnings and compare the results with what you find in sys.dm_db_missing_index_* DMVs. Here's a script that you could use to query the plan cache. If the plan doesn't get cached or gets pushed out the cache for any reason, you won't ...


4

Assuming your table and sample data look like this: CREATE TABLE dbo.Item ( Item char(1) NOT NULL, RecordTime datetime2(0) NOT NULL, Value decimal(5,3) NOT NULL, CONSTRAINT PK_dbo_Item_Item_RecordTime PRIMARY KEY (Item, RecordTime) ); INSERT dbo.Item (Item, RecordTime, Value) VALUES ('A', ...


2

I had to do this some years ago for a SQL Server 2005 DB. It was awful. I've not had to do it on 2008, thankfully, but as far as I know it still works the same way. Unless specified explicitly, the collation is copied from the next higher object when an object is created. So creating a table will copy the database's collation. Collations are not ...


3

No, there is no knob for clearing just missing_index DMVs. You'll have to restart the service or, if you want to exclude specific tables, you could create a view against the DMVs with a filter.


2

To get the information for a single instance you would use DaysUntilExpiration value for the LOGINPROPERTY function. This will give you the days until the password expires. You can then cast that as an integer and use the DATEADD function to get the actual date. SELECT name AS LoginName, DATEADD(DAY, CAST(LOGINPROPERTY(name, 'DaysUntilExpiration') AS ...



Top 50 recent answers are included