Tag Info

New answers tagged

0

Well you can try to run a trace (if it's possible for you). But you can also try to just write it to your SQL Server Agent Job History instead. I tried to write a little bit of code which can be added as a first step in front of all other steps. It will simply write a line "Executed from [hostname]". I couldn't test it, due to the fact that I have just one ...


-1

Here is a good article that explains what the Transaction Log is, and why do you need one!


1

This is due to the fact you reached the row size limitation. But this should affect your production server too? Are you sure that both server are the same and both server use the same table design? You can try to use nvarchar(max) instead of (e.g). nvarchar(250) for testing purposes. This will use more space and will be slower, but will avoid the 8060 ...


4

Today, without time, is: DECLARE @d SMALLDATETIME; SET @d = DATEADD(DAY, GETDATE(), DATEDIFF(DAY, '19000101', GETDATE()); -- when you move past SQL Server 2005, which you should, you can use the much tidier: -- DECLARE @d DATE = SYSDATETIME(); Then to move to the first day of this month: SET @d = DATEADD(DAY, 1-DAY(@d), @d); Now to get all employees ...


0

If "watch the history log to see if the job completed" can be accomplished by using either a stored procedure, scalar function, or table-valued function (as opposed to direct SELECT access to the msdb.dbo.sysjob* tables), then you can get away without granting any permissions at all to any real Login/User that will be logging in. You will still create at ...


0

To be able to check all jobs you would need go add the user to the SQLAgentReaderRole in the msdb database which also gives that user permission to create jobs. To minimize the access granted you can also grant the user select permission on the tables used by the sql server agent


1

There can be many reasons for that. You're queries run and generate a version, during another query use the same tables. During this phase version 1 will be hold back. If your transaction isn't commited at the end, it may occur that version 1 will last for a long time in your tempdb. Make sure that all transactions are probably commited and closed. Make ...


20

There are three main technical reasons you get the plan you do: The optimizer's costing framework has no real support for non-inline functions. It does not make any attempt to look inside the function definition to see how expensive it might be, it just assigns a very small fixed cost, and estimates the function will produce 1 row of output each time it is ...


18

It looks like this is a cost based decision by the optimizer but a rather bad one. If you add 50000 rows to PRODUCT the optimizer thinks the scan is too much work and gives you a plan with three seeks and one call to the UDF. The plan I get for 6655 rows in PRODUCT With 50000 rows in PRODUCT I get this plan instead. I guess the cost for calling the ...


2

As it does not matter which email shows up. I think that the following one is very direct. Select Person.PersonName, MIN(Email.Email) From person left join email on Person.ID=Email.PersonId group by Person.Id, Person.PersonName


2

I would us an outer apply for this, I find it more readable. Select Person.PersonName, coalesce(Email.Email,'No email found.') as Email From person outer apply ( select top(1) Email.Email from Email where Person.ID=Email.PersonId order by <whatever suits you> ) as Email;


1

select P.PersonID, (SELECT TOP 1 E.Email FROM Email E WHERE E.PersonID = P.PersonID ORDER BY <pick your column here>) from Person P


3

SELECT A.PersonName, A.Email FROM ( Select Person.PersonName, Email.Email ,ROW_NUMBER() OVER(PARTITION BY Person.ID ORDER BY Email.Email) AS RN From person left join Email on Person.ID=Email.PersonId ) A WHERE A.RN = 1


0

There are some great data cleansing products out there, one in particular that is actually top notch and affordable. I've come across DataMatch by Data Ladder, which is an excellent fuzzy matching and address standardization/address parsing tool used across business and would work really well for this situation. They offer a complimentary trial for new ...


3

I would suggest automating generation of the restore script. There are a few versions of this out there but Paul Brewer has a T-SQL and PowerShell version available. I have seen some setups as well that just include generating a restore script when the backup is accomplished. Which, just a note that a new open source backup solution was released by Sean ...


1

Even though this is a pretty old thread, Microsoft has officially published the list of Trace Flags that can be used when running SQL Server 2012 and up for high performance workloads : KB - 2964518 - Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads. Its highly advisable to do a thorough ...


3

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index. Incorrect. An unfortunate overload of the term 'snapshot'... A snapshot read of the index is used, which means row-versioning see How Online Index Operations Work: A snapshot of the table is defined. ...


1

My understanding is the snapshot should reside on the index file drive. Is this correct? It resides on same file where Index file resides unless you use Sort_in_tempdb option during rebuild. From BOL When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the destination filegroup. If the SORT_IN_TEMPDB option is set to ...


0

Use the SQL Server configuration manager to change the SQL Server Agent user account. Change it to run as local system, apply and restart and then back to the correct user, dont add any privileges to the user in the operating system (esp. not add it to the server local administrator group). The Configuration manager will set all the correct permissions for ...



Top 50 recent answers are included