Tag Info

New answers tagged

0

Please show the queries and the execution plans.


0

You can check for orphan logins (i.e. a DB user with the same name as an instance login, but a different SID) with this: -- List orphaned users USE <db name> GO sp_change_users_login @Action='Report' If this returns any users then use this to fix them up --assign orphan user to login USE <dbname> GO sp_change_users_login ...


-2

SQL Server Integration Services (SSIS) would be a good choice for getting data out of a SQL Server database and loading it into an Oracle database. SSIS was first released with SQL Server 2005; prior to that SQL Server included a tool called Data Transformation Services (DTS) which would also work. Since DTS is a tool that was included with SQL Server 7 ...


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


3

Only you can answer if the jobs can be dropped completely. If they are not jobs like from the import/export wizard I would probably script them out. This can be done easily with PowerShell. A quick way of dropping them is via Object Explorer details pane, it allows multiple selections and then just hit the delete key or right click and delete.


0

As mentioned by Aaron, you're login 'say' X would be pointing to a different database rather than NordenWeb, when it throws error with state 38: You need to check the login for any other mapped databases. To find correct error you need to enable the trace when above error is created: To dig in deep do nslookup 82.71.5.169 from windows to find the host ...


0

Create two template databases - once - one with the schema, and one with schema + data - populated with the manual generate scripts method. Take a backup of each. When you want to create a new database, instead of a manual CREATE DATABASE and then manual population, restore the backups as new names and the WITH MOVE options to move the data/log files. ...


0

Short answer is you can’t see historical data for updates unless you already had some auditing system in place at the time change happened. What you can do is to try reading transaction log using some third party tool like ApexSQL Log and checking if there is info like this written somewhere in t. log. I know there is a lot of historical data written in ...


-3

Use OPTION ( OPTIMIZE FOR ( '@Your_XML_Variable' = NULL ) ) It will avoid compilation of new execution plans, thus making your query faster. Read here: http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx


1

But my question here would be how can i predict or calculate what size of drive would be require to accommodate the backups From Books Online Document. If you see the last part it says that For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. ...


1

You can't completely predict it, but you can backup smaller databases with compression and if the data sets are similar (based on the factors listed below), you can predict a ratio that you may be able to apply to the larger databases. It's easiest to just quote the MSDN on compression here: To calculate the compression ratio of a backup, use the values ...


0

There's a simple answer to this. If the relationship between the tables does not exist to give you the results you want, you need to CREATE your own relationship. I used ROW_NUMBER OVER (ORDER BY PALLETID) to give me unique ID's for both datasets. THen simply join (make sure you check different joins to make sure you don't exclude rows from one side or ...


0

I believe this has already been answered and would be helpful for you if you can refer to below discussion:Also, what action where clause is performing needs to be looked into: Why so many logical reads? Since Logical Reads are from the data cache (memory) I would think the fact that there is a lot of reads will make little difference and it would seem ...


0

That is really the purpose of a role in SQL Server. CREATE ROLE Test GRANT EXECUTE TO Test The role Test now has permission to execute any stored procedure or function on the current database (where the role is created). This is true in every version of SQL Server as far as I know (2000+ at least). Also yes if you backup your database and then restore ...


0

I usually use this script: SELECT r.session_id as spid, r.[status], r.command, t.[text], r.blocking_session_id as blocked, r.start_time FROM sys.dm_exec_requests AS r LEFT OUTER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t WHERE r.session_id <> @@SPID AND r.session_id > 50 ORDER ...


1

It appears Microsoft forgot to include the geometry and geography datatypes in the SQL Server Import and Export Wizard. There is an excellent explanation and fix here which I will summarize below: Open the SQL mapping file. In my case I was doing am Export from SQL Express 2014 to SQL Express 2008R2 using the SQL Client method. So the file I had to edit ...


3

The decision to use a table spool or not depends on the query optimizer's cost assessments. These in turn are influenced by the specific values of parameters when the query plan is compiled (or recompiled). So, this is just a regular case of parameter sensitivity. The chosen (and cached) plan depends on the parameter values sniffed when a compilation or ...


0

in SQL management 2012, under Database Design menu... select "Copy Diagram To Clipboard"... paste into any program which will let you print or save to PDF.


0

In SSMS, go to SQL Server Agent Click on Jobs Now Click on View->Object Explorer Details You will have a list of all jobs on your right window. This way you can select multiple jobs that you want to script, right click and select "Script Job As" and then the options that you want.


0

Case: When you run below command on Principal 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 be available. restore ...


11

Because dropping of a NCI is already as much online as it gets. Is a metadata only operation. There is not even data deletion, a dropped index rowset is simply deallocated, ie. the same operation as truncate does. Dropping a clustered index, on the other hand, implies a rebuild and is a size-of-data operation, so it does make sense to have an online ...


1

Your first test should be to just try and connect via telnet to port 1433 of the ip in question (assuming it isn't a named instance in which case check the start of your SQL error log to get the dynamic port number). This will at least prove the server is listening correctly and you dont have any network/firewall issues.


0

One way to do this is to set Blocked Process Report to 60 seconds, then an event will be raised by SQL. This event can be picked up in a number of ways. 1) Via SQL Profiler, this isnt a great way as you have to run trace etc to find them 2) Via Extended Events, again not great you would have to have a job constantly polling the target. 3) Via Event ...


0

I have seen this issue when the default database for your login is set to the wrong database.


0

We have run into issues when servers were re-named and we had to set the DTC security properties to "No Authentication Required" on all servers involved in the transaction. This is not a best practice but works for internal network only servers. Directions to the DTC security settings can be found here: ...


0

There are lots of methods you can use to find the same: You can use the DMV's like sys.dm_exec_requests DMV and Sys.dm_os_waiting_task SQL Server Management Studio Reports SQL Server Profiler Also, you can download Adam Mechanics stored proc SP_WHOISActive and use that in sql job as required to find necessary info. ...


0

I would use something like the following. This is pretty much what I have been using for years, with your error message logging worked into the CATCH block. CREATE PROCEDURE [SchemaName].[ProcedureName] ( @Param DataType ... ) AS SET NOCOUNT ON; DECLARE @InNestedTransaction BIT; BEGIN TRY IF (@@TRANCOUNT = 0) ...


2

A simpler way to write the MERGE is as follows: MERGE #target /* WITH (SERIALIZABLE) */ AS T USING #source AS S ON S.Id = T.Id WHEN MATCHED AND T.IsDeleted = 0 AND S.Modified > T.Modified THEN UPDATE SET Modified = S.Modified, NoteText = S.NoteText WHEN NOT MATCHED THEN INSERT (Id, Modified, IsDeleted, NoteText) ...


0

I believe i have discovered the problem. One TXT type column as appeared with 67285Bytes and every time i select them with an where clause the SQL Server Engine lock the entire Table because the data volume on the select.


2

While it depends on how many jobs and your backup methods, one of the simplest options is to have the job use retry attempts and intervals to re-run the job. You can set the intervals at a high amount to account for a full system reboot and use these jobs specifically during reboot windows. Just go to Job Properties -> Steps -> Advanced (Job ...


0

You can use the below query to determine the Memory usage at database level: SELECT (CASE WHEN ([database_id] = 32767) THEN N'Resource Database' ELSE DB_NAME ([database_id]) END) AS [DatabaseName], COUNT (*) * 8 / 1024 AS [MBUsed], SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty] FROM sys.dm_os_buffer_descriptors GROUP ...


0

If you want to monitor memory utilized by SQL Server 2008 and above below is simple and accurate query which would give you memory utilized by SQL Server. Dont refer to task manager for SQL Server memory utilization it wont show you correct value if SQL Server Service account has locked pages in memory privilege. select ...


0

I don't know SQLServer specifics, but usually this sort of thing is due to table statistics being different in the two databases. Look at the query plans to see if they are different. Run the SQLServer version of "analyze table" or "analyze schema" commands. If these things don't help, check how the databases are set up. Is it possible that the data is ...


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


1

I tried to put together an approach that would work on 2008 and is more set-based. Here is what I came up with. It did end up being more complex than I had hoped, but it might be an interesting approach for you to benchmark against your current approach on larger data sets. For what it's worth, this script runs in about 15ms for the provided data set on ...


1

As no one got back to me (fair enough - maybe you've got jobs too!) I have tested this. We didn't use this method in the end for the production task because the powers that be decided it was ok/best to use 7-Zip to split the single .bak file. However, the answer is - you only need to specify the a file of the set and it works fine (I did this on my test ...


0

To answer my own question to an extent. Commands appear to be written to the command buffer as soon as sp_MSget_repl_commands makes them available. If the writer thread is unable to write the command to the subscriber, sp_MSget_repl_commands spins until the writer has written the command. Once written and reader notifies the writer which frees one of the two ...


1

You can't recycle the SSAS log in a similar manner to SQL Server. You need to do this manually by building a script and then scheduling it to run either inside of SQL Agent or Windows Task Scheduler. Here is a sample Powershell script you can use: [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") #Get a list of installed SSAS ...


8

when does SQL Server notice the new indexes and determines whether they actually help the query? Immediately. Creating an index causes a recompile because of a schema change. Here's an illustration of this. First to setup the objects in the test database: use TestDatabase; go if exists (select 1 from sys.tables where object_id = ...


4

Your prolem occurs because of the different steps involved in query execution. First comes parsing, then binding and finally execution. In parsing the submitted text is verified as valid SQL and converted to an internal representation. Binding resolves the names from the submitted SQL to objects inside SQL Server's catalogue. Unfortuately ...


1

My experience is that a useful index gets used almost immediately. It does change the information available to the optimizer after all. However, a new index may never get used if the optimizer rejects its value. You should note that a stored procedure may hold onto an existing plan for some time. If so, you can use exec sp_recompile procedurename to ...


1

I think the easiest thing to do is to create a process that creates a log of how much stock was bought in a day and how much was left at the end of the day. You can then run your analysis on this log. The analysis could look at the last week or however long you want to determine what the average number of items sold were. You could then compare that to ...


0

Its possibly due to the way you created your SSIS Package. In order for my log file backups to be removed after a certain date I needed to create 1 maintenance plan with 2 sub plans. Each subplan had a mainentance Cleanup task; One for BAK files and the Other for TRN files. Because the Maintenance Plan has two sub plans, in order to test you must execute ...


0

I had the same problem, and it seems to be a problem if you restore a database, then try a use [myDatabase] statement, all in the same batch. Seems the parser is being overcautious in this case. The trick is to use the Go statement to separate out a script into smaller pieces. You can use SQLCMD Mode to use variables for things like the database name which ...


0

You cannot restrict it, but you can turn logging into msmdsrv.log off completely (or change location for this file). In server properties, choose "Show Advanced (All) properties", and then change the Log \ File one. Or a batch file to move and delete it (daily or monthly etc).


0

Microsoft support Multiple instances on single windows machine so it is fully supported when you want to install another version of SQL Server same machine having other versions. But have you made sure that windows server/machine would perfectly support all four instances. Is the hardware resource optimum for your testing won't you face hardware crunch if ...


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.


2

You can use the following MSDN_DBCC CHECKIDENT Hope that helps! DBCC CHECKIDENT (Transact-SQL) You can use DBCC CHECKIDENT to manually set a new current identity value for the identity column. DBCC CHECKIDENT ( table_name [, { NORESEED | { RESEED [, new_reseed_value ] } } ] ) [ WITH NO_INFOMSGS ] Example USE AdventureWorks2012; ...


6

You can use DBCC CHECKIDENT to reseed the IDENTITY column. Here is a sample you can run: SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.foo(ID INT IDENTITY(1,1)); GO INSERT dbo.foo DEFAULT VALUES; GO 100 -- note: set it to ([the next value you want] - 1) DBCC CHECKIDENT(N'dbo.foo', RESEED, 499); GO INSERT dbo.foo DEFAULT VALUES; GO 3 SELECT ID FROM ...


4

First and foremost thing, SQL Server in picture is SQL Server 2008 R2 RTM which is not supported by Microsoft in any way please apply SQL Server 2008 R2 SP3 to at least get extended support I analyzed and found that one of the User DB A was using 8 GB RAM I don't think there is any issue because of this, this is totally normal looking at this you ...



Top 50 recent answers are included