Tag Info

New answers tagged

2

You're on the right track - the XML index is the problem. Obviously, there's a primary as well as a secondary XML index. When performing a DELETE against the base table (ETLHeaders) the data have to be deleted from every index of this table, too. This overhead can be significant, especially for XML indexes. The index causing the long duration is the ...


5

The top levels of the plan are concerned with removing rows from the base table (the clustered index), and maintaining four nonclustered indexes. Two of these indexes are maintained row-by-row at the same time the clustered index deletions are processed. These are the "+2 non-clustered indexes" highlighted in green below. For the other two nonclustered ...


0

The solution in http://dba.stackexchange.com/a/80959/52708 is easy but if the tables are large, adding and dropping a column can be expensive. So this may be a case where a stored procedure works better and faster. The first loop iterates over the neworders table, then within that iterate over the neworderitems that match the neworders and finally iterate ...


0

I always favor PowerShell when it comes to needing OS level information such as file system info or manipulation, and even WMI calls to get hardware info of a server. So this is just my version using PowerShell...Oh and in the event you ask I learned how to do it this way via Laerte Junior's article The PoSh DBA: Solutions using PowerShell and SQL Server. ...


0

You would have to check running queries on the remote server. You could isolate linked server queries by filtering on your linked server name with the following dmv query. SELECT [s].[session_id] AS [spid] ,[s].[status] ,[s].[login_name] AS [loginName] ,[s].host_name AS ...


8

rowversion is just an alias for timestamp. Under the covers, they are the exact same thing. If you're using the GUI (prior to 2012), you have to use timestamp. (Peripheral hint: please stop using the GUI.) But it doesn't stop there. Even if you generate this table using DDL: CREATE TABLE dbo.x(y ROWVERSION); The data type in the metadata will be ...


0

Timestamp is a synonym for the rowversion datatype. You can find it in this MSDN article. Microsoft has marked timestamp as deprecated as you can see in the article mention above, but still in SQL Server 2012 Mgmt Studio uses timestamp even if you create the table with the rowversion datatype.


5

E-mail address: I go with NVARCHAR(320) - 64 characters for local part + @ + 255 for domain name. You may also consider normalizing the domain away, since it's pretty wasteful to store hotmail.com 500,000 times when a much smaller INT or even SMALLINT is probably capable (I don't know how many domains you plan to support). I used to be against NVARCHAR but ...


0

Irrespective of your BACKUP and RESTORE statements not matching to think you actually restored what you thought your issue is that the Table_1 is apparently in the file group you tried to restore. The syntax for your RESTORE command should include in the WITH clause the command NORECOVERY or RECOVERY. The later tells SQL Server to complete the rollback ...


0

The quick and dirty way: Select * into [dbo].[NewTable] from [dbo].[ExistingTable] Inserting Rows by Using SELECT INTO via Technet


0

This is the solution I propose, If you have more columns to test differences you just have to union another select in the same manner. As I previously commented, in the sample date you provide for id =3 both [Address] and [Address Code] have different values, for these situations two rows are given one showing that column [Address] is different and another ...


1

Thank you for posting detailed logs As far as I can read logs and with my experience with SQL Server installation the error here is because your account does not have necessary rights to perform some registry actions. My first question would be did you made sure you are installing SQL Server with administrator account. Even if account is domain admin make ...


1

I came across this old post looking for the same answer. There is an easier way without torpedoing all replication. After running sp_dropsubscription you must run a cleanup stored procedure at the subscriber. So, after running this: USE PublisherDB GO exec sp_dropsubscription @publication = N'MyPublication', @subscriber = N'SubscriberServer', ...


0

Basically if you manage the contents of the files you select from and include the file size, it should not be too hard to do. (But there are issues.) Here is a greatly edited script that may help you. -- Gather the full names of Database Files SET NOCOUNT ON create table #os_files(fullfilename varchar(2000)); insert into #os_files exec xp_cmdshell ...


0

Have you checked your actual rows vs estimated rows in the actual execution plan? Also, have you checked the reads from your query compared to the reads from a table made up of just the fields you are selecting on? Without even seeing any of that I would say you definitely need a non-clustered index. My guess is either one or both of the following is ...


3

Personally I would create another job and schedule it to run each morning at 7am. A job step within that job could execute something like USE msdb ; GO EXEC dbo.sp_stop_job N'<Your job name>' ;


0

But of course. I suspect that you want to upgrade to the SQL Server 2014 Business Intelligence edition, since that is more frugal than Enterprise. But the BI edition does include the SQL Server database also in the license. Are you wanting to upgrade the BI server in order to get the latest BI features? SQL Server 2014 can certainly pull information from ...


0

Edit If your setup involves your BI server being standalone and the report server database is being hosted on the BI server and not the production database then yes upgrade to 2014. You should consider looking at the new deployment methods with SSIS, very cool stuff. If your setup involves: your BI server only houses the Integration Services and Report ...


0

A transaction log contains transaction details for many parallel both transactions that are (a) completed or (b) still processing. A transaction log backup is intended to harden the log file with all data needed to restore the database to the point in time that includes the last completed transaction. This also includes all the transactions still in ...


0

LastLSN + 1 of the Full database backup will fall in between the FirstLSN and LastLSN of its subsequent transaction log backup. (http://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/)


0

/********************************************************************/ SELECT tst.[session_id], s.[login_name] AS [Login Name], DB_NAME (tdt.database_id) AS [Database], tdt.[database_transaction_begin_time] AS [Begin Time], tdt.[database_transaction_log_record_count] AS [Log Records], tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used], ...


1

Try converting the datetime column as follows: UPDATE [TableName] SET DateCreate = CAST(LEFT(CONVERT(NVARCHAR(25), DateCreate, 120), 10) AS DATETIME)


1

For SQl Server 2008 you can use convert function like below. --For output format mm-dd-yyyy declare @d datetime = getdate() select convert(varchar(10), @d, 110) --For output format yyyy-mm-dd declare @d datetime = getdate() select convert(varchar(10), @d, 120) If you are using SQL Server 2012 and later, the new format function works fine too. Example ...


1

You should use the same database for UCP and MDW. Actually, you can't use a different database. UCP is in fact a specialized collection set that uploads its data to a MDW database. The MDW database is unique for a SQL Server instance and Data Collector cannot upload different collection sets to different MDW databases. The database name for UCP must be ...


7

This isn't as easy to achieve as you might think. One way is to create a new user-defined database role, give all the permissions needed to that role, then add users to the new role. This at least makes it easier to give users (or other user-defined roles) this set of permissions in future. The following steps are a good start: -- The user-defined role ...


1

am continuously getting the alerts for OS paging High. Memory is basically two types the physical random access memory (RAM) and virtual memory. A process has to be first mapped to Virtual memory and then to physical memory. All programs use RAM, but when there isn't enough RAM for the program you're trying to run, Windows temporarily moves information ...


2

Views do not have datatypes; they reference columns in tables that do have datatypes. You can do an explicit CONVERT() in a View, but just like any query, it will return the underlying type. Now, doing what amounts to a "default" (especially inline with your request to use DATETIME2 even if someone tries to use another type) is a bit complicated and shown ...


2

No, there isn't a way to automatically change a datetime field to a datetime2 field if the former is specified in a table creation. To do it manually: ALTER TABLE [table] MODIFY COLUMN [column] DATETIME2; If the issue pertains to end users, education is your best bet to make the creation of tables more uniform and compliant with using datetime2.


0

Would never be possible as I was applying it as: SELECT [...] CROSS APPLY dbo.Function(Dat, Hor) [...]


3

Define all the foreign keys as CASCADE UPDATE If you have not done this, then you'll have to Create a new row with new primary key Update all child tables Remove old row .. in a transaction of course and watching out for other constraints that could fail


3

If you have defined the Foreign Key constraints as ON UPDATE CASCADE then the Primary Key value that was changed should cascade down to all the Foreign Keys with that constraint. If you do not have the ON UPDATE CASCADE constraint, then you will need create scripts to complete the update. EDIT: Since you do not have the ON UPDATE CASCADE constraint, but ...


4

You sure can. ON UPDATE CASCADE is what you are looking for. Here's a small how-to: http://sqlandme.com/2011/08/08/sql-server-how-to-cascade-updates-and-deletes-to-related-tables/ Basically, when you modify the PK, the cascade will go out and update all the FKs that reference it. This can be done in your CREATE statement, same as if you were doing a ...


4

Have you installed .net framework 2 (part of 3.5) from Programs and Features? This is separate to .net 4.5...


1

You currently have a client and a server on one computer, you need to separate that. You will need to install the program (client) on both computers (A,B) and setup the database on one computer (A). Configure the program other computer (B) to connect to the database on computer A. Computer A will be used as server for the clients on computer A and B. ...


2

No you cannot have two sets of backups otherwise you mess up the backup chain. Think of this occuring: Day one 1. Person A does a full backup Day two 2. Person B does a full backup 3. Then an hour later Person A does a diff backup That diff backup that Person A does will be based off the Full backup that Person B just did, not the Full backup that Person ...


3

Yes, it is possible, but it requires some agreement between both parties. If the "sysadmin" (it does not need to be a sysadmin by the way) wants his own backups, does he want all the transaction logs and differentials in addition to a full backup? If the "sysadmin" only needs full backups, he can do his own backups WITH COPY_ONLY and use them without ...


4

You can have sysadmins doing COPY_ONLY backups and DBA's doing regular FULL, DIFF and/or Transaction log backups. The key is COPY_ONLY backups for allowing ad-hoc backups. But they cannot be used for point in time recovery. Note that taking a random Transactional log backup (without using COPY_ONLY) will break the log chain and you wont be able to do a ...


0

A worked example of what Mark's talking about, using IsDescendantOf: DECLARE @t TABLE ( hId hierarchyId PRIMARY KEY ) INSERT INTO @t VALUES ( '/1/' ), ( '/1/2/' ), ( '/1/2/1/' ), ( '/1/2/2/1/' ), ( '/3/' ), ( '/3/4/' ) DECLARE @hId hierarchyId = '/1/2/' SELECT *, hId.ToString() hId_string FROM @t WHERE hId.IsDescendantOf(@hId) = 1 ...


1

How about trying using a little recursion. No need to keep track of the depth of the node with some clunky code, just let the stack do that for you with a clean and simple procedure. First, define your procedure: CREATE PROCEDURE USP_DEL_NODES @inNode INT AS BEGIN DECLARE @delNode INT --If there exists a child who's parent is being deleted, do a ...


2

We've used Change Tracking to handle this. We've found it lightweight and easy to set up and administer. The largest system's moving tens of thousands of rows (from a table containing millions) per day without signs of stress. Of course your configuration will dictate your throughput. It's big cousin - Change Data Capute - will handle the case where you ...


0

The "best way" is a matter of opinion, but I've previously created separate logging tables on which I've built custom reports. The logging tables can be populated by the application or using triggers. If your application (app or stored procedures) does the logging you will have to identify and modify each and every bit of code that performs those updates ...


0

You cannot see the conflict tables from within Replication Monitor. However, you can view the Conflict Viewer by right-clicking your Merge Publication in Object Explorer -> View Conflicts. Alternatively, you can directly query MSmerge_conflicts_info. For more information, see View and Resolve Data Conflicts for Merge Publication (SQL Server Management ...


0

The text value shows the procedure in which the statement that is being executed resides. The Command references the action of the executing statement within the procedure To find the exact statement within the procedure that is being called, you'll need to use the statement_start_offset and statement_end_offset from dm_exec_requests. Add the following to ...


0

A good place to start would be to identify and disable (remove at a later stage) unused indexes, you can do this by using the standard SQL Server reports in SSMS. Another option is to investigate if the Server have enough memory (RAM), a qualitatively cheap improvement now a day. Like Brent Ozar said; the correct amount of memory for SQL Server is MORE!!!


1

This will list explicitly granted permissions on table types, but not those granted implicitly through role or group membership, or permissions granted against the containing schema. SELECT [schema] = s.name, [type] = t.name, [user] = u.name, p.permission_name, p.state_desc FROM sys.database_permissions AS p INNER JOIN sys.database_principals ...


2

Your best bet is to create three separate jobs with the same schedule to kick the jobs off at the same time. Depending on what the jobs are doing you should be careful to monitor blocking and deadlocking. Edit 1 : Another option is create an SSIS package with N number of operators to call the SPs in parallel


-1

Identify the Cache memory required for each database in your server and set Max. Server Memory higher than its cumulative value(not necessary to configure the value greater than twice of cumulative cache memory). By doing this, you can allocate the memory needed for MSSQL server and free up the RAM unnecessarily occupied by the buffer cache. Note: Max. ...


1

This is a permissions issue and you can find more details in the verbose agent log. Verify that your replication agent process accounts have the necessary permissions listed in the section Permissions That Are Required by Agents in Replication Agent Security Model. Note that Microsoft recommends that you use a Windows accounts for replication agent process ...


0

Check this: DECLARE @jobId BINARY(16) SELECT @jobId = CONVERT(uniqueidentifier, job_id) FROM msdb.dbo.sysjobs WHERE name = 'Your_Job_Name'


0

SQL server will use as much memory as possible. You can and you should allocate limited memory in the SQL server settings (properties) to leave enough for OS and IIS. How much memory foe each depends on what each does, load and expectations. This article explains this in depth: ...



Top 50 recent answers are included