Friday, October 11, 2013

[SQL Server 2008 issues] Storage migration for 2 node sql cluster.

[SQL Server 2008 issues] Storage migration for 2 node sql cluster.

Storage migration for 2 node sql cluster.

Posted: 10 Oct 2013 07:07 PM PDT

Hi,We have active acitvie sql cluster 2008 running on win 2008 cluster.On this server storage migration is planning and for the DBA Prospect is there any impact on the DBAs , and also advise me as DBA what are precautions and steps need to be taken.Reply is much appricated.Advance thanks.....

Restore Database From Overwritten Old Backups

Posted: 10 Oct 2013 05:59 AM PDT

Hi Every one.. Like to take advise on this. Have one backup file which overwritting everyday full backup on that file Suppose ADW.Bak overwritting Full Backup every day .I want to restore Database with 5 day old backup file how i can do that?When i am trying to restore it showing me most current backup file only?

Need Scripts

Posted: 10 Oct 2013 12:03 AM PDT

Need Scripts to identify below...1) Script to Identify what Indexes are set on tables.2) Script to Identify unwanted Indexes are set on tables.3) Script to Identify what new recommended Indexes needs to be set on tables.4) Script to Check fragmentation of DB & tables & how to resolve the issue..please give an understanding for this.. coz google search gives hell lots of scripts..:w00t:

How to update one column if second column matches

Posted: 10 Oct 2013 07:21 AM PDT

Hi,I have 2 tables, from which i have to update as belowtable Source: col a, col btable destination: col d, col eif source.b = destination.e then update destination set d = aAny clue how to do...Shaun

Issue with Sp_executeSql table variable output

Posted: 10 Oct 2013 01:15 AM PDT

My requirement is i need to populate @tblAmount dynamically.Here is the code [code="sql"]DECLARE @AdjustmentBatch_ID INT DECLARE @Platform VARCHAR(20), @Type INT, @lStr NVARCHAR(1000),@TableName VARCHAR(20),@ColName VARCHAR(20) ,@parameters NVARCHAR(30) SELECT @Platform = '',@lStr = '',@TableName = '',@ColName = '', @AdjustmentBatch_ID = 15 --DECLARE @tblAmount TABLE(LegalEntity VARCHAR(10), Amount MONEY ) --BEGIN TRY SELECT @Type = type,@Platform = Platform FROM dbo.Adjustment_Batch ab(NOLOCK) WHERE ab.AdjustmentBatch_ID = @AdjustmentBatch_ID AND ab.Status = 1 SELECT @TableName = CASE WHEN @Type = 1 THEN 'GLEntry' WHEN @Type = 2 THEN 'GrossPremium' END, @ColName = CASE WHEN @Type = 1 THEN 'BaseAmount' WHEN @Type = 2 THEN 'GrossWrittenPremium' END SELECT @lStr = N'DECLARE @tblAmount TABLE(LegalEntity VARCHAR(10), Amount MONEY );INSERT INTO @tblAmount (LegalEntity, Amount)'+ 'SELECT LegalEntity,SUM(CAST('+ @ColName +' as MONEY)) AS Amount FROM dbo.' + @TableName + ' t(NOLOCK)'+ 'WHERE t.AdjustmentBatch_ID = ' + CAST(@AdjustmentBatch_ID AS VARCHAR(20)) + ' GROUP BY t.LegalEntity ' PRINT @lStr EXEC SP_executeSql @lstr ,@parameters = N'@tblAmount(LegalEntity VARCHAR(10), Amount MONEY ) TABLE OUTPUT' ,@tblAmount[/code]Error : [quote]Msg 137, Level 15, State 2, Line 45Must declare the scalar variable "@tblAmount".[/quote]

help count that involves mulitple table joins with foreign keys

Posted: 10 Oct 2013 02:11 PM PDT

I am trying to get a count from two separate tables that are joined via foreign key, it only seems to be counting from the HIS table I need results from both the HIS and GRD[p][/p][code="sql"]DECLARE @ID INT = 4043300;SELECT HISTORY, ELA, MATH, SCIENCE, FL, VA, Prep,CASE WHEN HISTORY >= 0 AND ELA >= 1 AND MATH >= 1 AND SCIENCE >= 1 AND FL >= 1 AND VA >= 1 AND Prep >= 1 THEN 'Yes' ELSE 'No' END AS [On Target?] FROM (SELECT COUNT(CASE WHEN CRS.U1 = 'A' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS HISTORY,COUNT(CASE WHEN CRS.U1 = 'B' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS ELA, COUNT(CASE WHEN CRS.U1 = 'C' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS MATH, COUNT(CASE WHEN CRS.U1 = 'D' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS SCIENCE, COUNT(CASE WHEN CRS.U1 = 'E' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS FL, COUNT(CASE WHEN CRS.U1 = 'F' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS VA, COUNT(CASE WHEN CRS.U1 = 'G' AND (HIS.MK NOT LIKE '%FD%') OR (GRD.M1 NOT LIKE '%FD%') THEN STU.ID END) AS Prep FROM CRS INNER JOINHIS ON CRS.CN = HIS.CN INNER JOINSTU ON HIS.PID = STU.ID LEFT OUTER JOIN GRD ON CRS.CN = GRD.CN AND dbo.GRD.SN = dbo.STU.SNWHERE STU.ID = @ID) AS derivedThanks in advance[/code]

Using One Composite Foreign Key Value As Foreign Key In Another Table

Posted: 10 Oct 2013 02:55 AM PDT

[img]http://www.adukinfo.co.zw/Untitled-1.jpg[/img]Good day all!!! I have the tables, as in image above, and I'm trying to create the relationships. I got stuck at creating a relationship between tables 'member_provider' and 'dependant_provider'.As you can see, member_provider has a composite primary key that is made up of to foreign keys 'Member_RowID' and 'Provider_RowID'. I want to maintain this composite primary key and then create a relationship with dependant_provider using Member_Provider_RowID. I have tried adding Member_Provider_RowID to the composite key in member_provider but when I try to create the relationship I get the following error:"The columns in table 'member_provider' do not match an existing primary key or UNIQUE constraint"How would I go about maintaining the the uniqueness of the tables two columns.I'm using MSSql Server 2008...Thanx in advance.

Create Function

Posted: 10 Oct 2013 07:51 AM PDT

HelloI need to Create a Function who pulls data from string and load to the table. here is sample string'CHG_FEP_SVC_GRP_ID(1) :: Expected Value = 1 :: Actual Value = 20'now I need to load to Expected Value in Amt1 column and Actual Value in Amt2 Column which is as in below table[code="plain"]Error Amt1 Amt2CHG_FEP_SVC_GRP_ID(1) :: Expected Value = 1 :: Actual Value = 20 1 20[/code]Please help me to thatThanks

SqlDependency performance

Posted: 10 Oct 2013 07:19 AM PDT

hi all,I'm using SqlDependency (sql server 2008 R2) to get notification when data in some tables changed. The query notificatin is for cache invalidation.There are three large tables and I want to get changes on a small subset of these tables. These three tables are changing frequently, but the subset is not changing frequently. I have a question about the performance. How does the query notification track changes? Does it effect every query againsts these three tables (not only for the subset)? Performance is a major concern in our system, if the sql dependency impacts the performance, we need to find another approach. best wishesWilliam

Getting Database Names from Maintenance Plan

Posted: 10 Oct 2013 06:45 AM PDT

I have some maintenance plans that backup, cleanup, check integrity etc. for all user databases in one maintenance plan. I have since created a database where we want to track each action per database. I have created a stored procedure that takes two parameters: a database name and a comment/action. How would I go about doing this?Thanks in advance.

need help with log shipping error

Posted: 10 Oct 2013 04:15 AM PDT

hi all I am getting this error *** Error: Access to the path '\\sqltest\d$\logshipping' is denied.(mscorlib) ***the backup job runs but the copy job is failing

Replicating a large replicated database

Posted: 10 Oct 2013 04:00 AM PDT

We're starting to hit a wall with our in-house skill set, and hopefully someone in the community can provide some input, or point to some additional online resources to give us some insight. First some background.Our source systems are written on a proprietary platform, and cannot be accessed on an ad-hoc basis via standard tools. The vendor essentially replicates the proprietary source data to a SQL Server database so that the data is available to query. The problem is that poorly written queries can effect the replication service, and degrade performance on the source system, and so we made the decision to replicate the data that we need to another server.At the beginning, transactional replication worked just fine. Now, however, the business requirements have become more complex. We need to take a subset of the replicated data, and replicate it again to another server. We filter the data via indexed views, and then replicate the indexed views to the destination server as tables. SourceDB --> Repl_DB1 --> Repl_DB2Here is where the real trouble starts...the article properties on Repl_DB1 are set to "Drop Existing Object" when the destination object name is in use. Because the destination object is now referenced via a view, we can neither drop nor truncate the destination object. Deleting the data is also not an option, because serveral of these tables contain hundreds of millions of rows, likely overwhelming our transaction log.So far, it's looking like we need to perform the following steps whenever we have to regenerate a snapshot:1. Drop publication from Repl_DB1 to Repl_DB22. Drop all views on Repl_DB1 that reference replcated tables.3. Regenerate snapshot of Repl_DB1 using the truncate destination object option.4. Recreate views on Repl_DB15. Recreate publication from Repl_DB1 to Repl_DB2 using the truncate destination.This is an option that we would like to avoid, due to the size of the databases. Potentially, given the number of publications we have, it could be days before the data on REPL_DB2 is back to the state it was before the snapshot was recreated on REPL_DB1.What advice/experience can anyone offer?

SQL reindexing

Posted: 09 Oct 2013 07:31 PM PDT

I am going to incorporate into my reindexing script that any index under a certain size should be ignored.Whats the rule of thumb for this? There must be a size where reindexing\rebuilding an index under a certain size just becomes inefficient.

TRYCATCH/SAVEPOINTS INSIDE TRIGGERS

Posted: 10 Oct 2013 12:52 AM PDT

Hello all,Like always, your ideas/suggestions will be greatly appreciated.I have created a trigger. The idea is that when there is any DML operation on Table A, changes should be reflected on Table B. So far so good.Before I continue, here is the code[code="sql"] ALTER TRIGGER [dbo].[tr_ComponentOnLines] ON [dbo].[tblCFGLine] AFTER INSERT, UPDATE, DELETEASBEGINBEGIN TRY DECLARE @temptblInsertUpdate TABLE ( ID INT, Line VARCHAR(20), LineTypeID INT, Activity VARCHAR(20) ); DECLARE @Activity AS VARCHAR(20); DECLARE @RowCounter AS INT INSERT INTO @temptblInsertUpdate (ID,Line,LineTypeID,Activity) SELECT I.LineID,I.LineDesc,(SELECT LineTypeID FROM DhubOEE.dbo.LineType WHERE OlympusID=I.LineTypeID),CASE WHEN EXISTS(SELECT * FROM deleted) THEN 'UPDATE' ELSE 'INSERT' END AS Activity FROM INSERTED I UNION ALL SELECT D.LineID,D.LineDesc,(SELECT LineTypeID FROM DhubOEE.dbo.LineType WHERE OlympusID=D.LineTypeID),'DELETE' FROM DELETED D WHERE NOT EXISTS (SELECT * FROM INSERTED) --Check if the Line already exists in Assets. SET @ROWCOUNTER=( Select Count(*) FROM @temptblInsertUpdate Temp INNER JOIN DhubOEE.dbo.Asset A ON Temp.Line=A.AssetName ) SET @Activity=(SELECT TOP(1) Activity FROM @temptblInsertUpdate) SAVE TRANSACTION Tr BEGIN TRAN DECLARE @ErrorValue INT=0 IF(@RowCounter=0) BEGIN --Create The Line INSERT INTO DhubOEE.dbo.Asset(ParentID,AssetName,Path) SELECT (Select TOP(1) AssetID from DhubOEE.dbo.Asset),temp.Line,NULL FROM @temptblInsertUpdate temp --Update the path of the newly created Asset UPDATE DhubOEE.dbo.Asset SET Path='.1.'+CONVERT(VARCHAR(MAX),SCOPE_IDENTITY()) WHERE AssetID=SCOPE_IDENTITY() --Construct the path by updating Line INSERT INTO DhubOEE.dbo.Line (OlympusID,Linedesc,LineTypeID,State,AssetID) SELECT 30,Asset.AssetName,LineTypeID,1,SCOPE_IDENTITY() FROM DhubOEE.dbo.Asset INNER JOIN @temptblInsertUpdate Temp ON Temp.Line=Asset.AssetName END IF @Activity='UPDATE' BEGIN UPDATE L SET L.LineDesc=Temp.Line,L.LineTypeID=Temp.LineTypeID FROM DhubOEE.dbo.Line L INNER JOIN @temptblInsertUpdate AS Temp ON L.OlympusID=Temp.ID END IF @Activity='DELETE' BEGIN UPDATE L SET L.State=0 FROM DhubOEE.dbo.Line L INNER JOIN @temptblInsertUpdate AS Temp ON L.OlympusID=Temp.ID END SET @ErrorValue=@@ERROR COMMIT END TRY BEGIN CATCH IF (@ErrorValue>0) BEGIN ROLLBACK TRAN TR print @@TRANCOUNT END END CATCHEND[/code]The task is that even if smtg happens during the operations on the Table B i.e foreign key violation), the operations on Table A should continue and complete. The problem is that if smtg goes wrong on table b , the operations on table A are aborted as well.I understand that when a trigger executes, an implicit transaction starts. Therefore, even if you create another transaction, inside your trigger, that would be considered as a nested transaction , THEREFORE, if an error occurs, theory says that the entire batch will rollback. I thought, that a way to battle this, is by bringing savepoints on board. However, there is smtg I am missing/doing wrong and it doesn't work and the entire set of operations is aborted.Any ideas?Thanks

Backup - Veeam + DPM

Posted: 10 Oct 2013 12:42 AM PDT

Hi,The company i work for currently use Veeam to replicate an SQL Server instance every morning at 3 am (this involves taking a full backup of each database).They also use DPM to backup the databases, an express full backup takes place at 2 am and then synchronization takes place every 15 miuntes.I was concerned that the backup chain might be broken by the two products running full database backups on the same databases. To test this i executed a full backup using DPM; made some changes to a database and then ran the replication job within Veeam (which takes a full backup). I then allowed a number of DPM synchronization jobs to run (log backups) before restoring the database from DPM.The restore was successful despite the fact that the log chain should have been broken (as far as i can tell).If anyone is able to make sense of what i have written and can offer and suggestion as to how this restore was able to succeed i would be very grateful to know.Kind Regards,DBANewbie.

Consistency Checking on a VLDB with Partitioning and a Read-Only Filegroup

Posted: 10 Oct 2013 02:06 AM PDT

The background:I have a VLDB, 1-2 TBs, that I need to backup and replicate over to a reporting server for internal resources. Within this database there are basically two categories of tables: staging and prod. The staging tables include a bunch of raw, client data tables which make up roughly half the size of the database. The business has mandated we keep this data indefinitely and that it accessible for validation/auditing purposes. The data in these tables is historical but since we're only ever working with the last couple months, my first thought was to partition these tables by years. My next thought was to split those partitions onto read-only and read-write filegroups. This affords me the ability to shorten up my backup window by doing filegroup backups and makes the backup file size I need to copy over much smaller.The issue:How do I run consistency checks against the live database. DBCC CHECK[fill in the blank] doesn't seem to like read-only filegroups so what I'm envisioning is running integrity checks on the "replicated" database by making the read-only filegroup read-write.Make sense? Am I missing something? Is there a better way to do this (without considerable architectural changes)? Am I totally off base? Any input would be appreciated. Thanks.

Compare Tables

Posted: 09 Oct 2013 08:42 PM PDT

HiI have this data first, before I explain my query and my issue:create table staging(customer_id int not null unique, customer_name varchar(20), customer_lname varchar(20), [status] int)insert into staging(customer_id, customer_name, customer_lname, [status])values(1, 'James', 'Brown', 1), (2, 'Thabo', 'Kgosi', 1), (3, 'Horse', 'King', 0), (4, 'Tom', 'Smith', 1)create table final(customer_id int not null unique, customer_name varchar(20), customer_lname varchar(20))insert into final(customer_id, customer_name, customer_lname)values(1, 'James', 'Brown'), (2, 'Thabo', 'Kgosi'), (3, 'Horse', 'King'), (4, 'Tom', 'Smith')create table error(customer_id int not null unique,customer_lname varchar(20))Let me explain my data first, I have Staging table, all the records gets validated and get signed a [status]. if a record fail verification it gets [status] = 1, otherwise it get passed staright to Final table.Records in Staging will be validated again if they pass they go through Final table.In my query below, I want to check data in Staging with status = 1, then check if that record is also there in Final, which it would mean it once failed verification. If I find that record I write it to Error table. I want to end up with all the records that once failed Validation.insert into errorselect fn.customer_id, fn.customer_namefrom final fnleft join staging ston fn.customer_id = st.customer_idwhere in (select * from staging stg where stg.[status] = 1 and fn.customer_id = stg.customer_id)I'm struggling with a concerpt but I think it should be along the code I wrote, please help.

Semi-hypothetical backup question

Posted: 09 Oct 2013 11:43 PM PDT

So I've been commenting in a topic over on Technet, trying to help a guy who needs to ship a copy of his DB to a vendor. Said DB has some sensitive information in it, which he doesn't want the vendor to get. He's working with a copy of the production DB, updated the sensitive info to "placeholder" data, and is now concerned that someone may be able to retrieve the sensitive data from the Transaction Log.His initial procedure was:1. Backup the DB2. Backup the Log3. Shrink the Log (remember, this isn't the production data!)4. Backup the DB again (this is the one he'd ship to the vendor)Now, I didn't think that the backup he'd ship would have anything in the TLog that could potentionally be "read" with a log reader program to recover the sensitive data. Is this incorrect?If it would be possible to "read" the sensitive information, would something like this avoid the issue:1. Make the changes2. Flip the DB to Simple Recovery (he's working with a copy, after all)3. Issue a CHECKPOINT4. Wait a while for the lazy writer to flush the log5. Flip back to Full Recovery6. Backup the DB (ship this one to the vendor)I'm not out to prove the other commenters wrong on Technet, just looking to (try to) increase my knowledge.Thanks,Jason

edit SQL tables directly from Excel

Posted: 10 Oct 2013 12:21 AM PDT

Hi, simple question:Is it possible to connect to a SQL 2008 Db table with ms excel 2010, and edit the fields directly from excel? Like the same way as when you edit a table in ssms. Thanks in advance.

High memory utilization

Posted: 09 Oct 2013 10:11 PM PDT

Hi,In one of our server, SQL server is consuming most of the memory(seen from task manager). Apart from setting the memory limit, is there any way we can reduce memory utilization of SQL server? I believe Setting memory limit may degrade performance as SQL server will have to manage with less memory. How to deal with the situation? Client is chasing as memory utilization is high. Please advice.

Help

Posted: 09 Oct 2013 10:19 PM PDT

Created a Linked server.below is the errorThe operation could not be performed because OLE DB provider "SQLNCLI" for linked server "EMPLOYEE" was unable to begin a distributed transaction.

Replacing the first character in a column containing values

Posted: 09 Oct 2013 09:38 PM PDT

Hi All,I have a table like this CREATE TABLE [dbo].[template_practice]( [value1] [char](10) NOT NULL, [value2] [char](9) NOT NULL, CONSTRAINT [PK_template_practice] PRIMARY KEY CLUSTERED ( [value1] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOand data inserted in the above table as INSERT INTO [practicedb].[dbo].[template_practice] ([LTM_INV_CD] ,[LTM_TMP_CD]) VALUES ('AVINASH','ANAND')GOINSERT INTO [practicedb].[dbo].[template_practice] ([LTM_INV_CD] ,[LTM_TMP_CD]) VALUES ('AKASH','AKHILA')GOINSERT INTO [practicedb].[dbo].[template_practice] ([LTM_INV_CD] ,[LTM_TMP_CD]) VALUES (' ',' ')GOINSERT INTO [practicedb].[dbo].[template_practice] ([LTM_INV_CD] ,[LTM_TMP_CD]) VALUES (' ',' ')GOINSERT INTO [practicedb].[dbo].[template_practice] ([LTM_INV_CD] ,[LTM_TMP_CD]) VALUES ('KRISH','KRISHNA')GOINSERT INTO [practicedb].[dbo].[template_practice] ([LTM_INV_CD] ,[LTM_TMP_CD]) VALUES ('KRUPA','KANNA')GOso i want to replace the first character with 'M' whose letter is starting with 'K' only remaining values starting with other characters or null values are don't want to change.I WRITE A QUERY LIKE THISuse practicedbgoDECLARE @find varchar(20)SELECT @find='K'UPDATE template_practiceSET VALUE1=Stuff(VALUE1, CharIndex(@find, VALUE1), Len(@find), 'M')BUT WHEN I EXECUTE THIS IT SHOWS ERROR LIKEMsg 515, Level 16, State 2, Line 4Cannot insert the value NULL into column 'VALUE1', table 'practicedb.dbo.template_practice'; column does not allow nulls. UPDATE fails.The statement has been terminated.please tell me the stored procedure to clear my problem

Best Index Strategie...

Posted: 09 Oct 2013 09:16 PM PDT

Is it correct if I use a composite noneclusterdindex like this:[b]FirstName + LastName + BirthDate[/b]i doen't need another index like this. The first index is enough?First Name + Last NameFirst Nameor it has huge advantages for the performance?Best RegardsNicole ;-)

Restore Information SQL DB

Posted: 09 Oct 2013 09:13 PM PDT

One of the User have started a Restoration of DB & left home..When we check we see that the DB is in restoring state..can any query help to identify how much percent is complete or how much percent left for complete restoration

SQL DB Architect Requirements

Posted: 09 Oct 2013 07:16 PM PDT

Hi,I'm new to this forum and need help from seniors to help me out on the below requirement. Thanks in advance.I have around 8+ yrs of experience in sql server db that includes t-sql experience, migration, ssrs 4 yrs development, data modeling, etc. My clarification, though i have 8+ yrs of experience in sql. I dont have a full or partial list of things which architect should know. I need a list something like below, - Should have detail knowledge in data modeling - Strong experience in ssrs with tabular, matrix, chart reports, - SSIS packaging experience with.....If seniors in this forum help me on this, it will be a great help to me. I'm planning to learn and get expertise in the things which i dont have experience.Thanks in advance for the help.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog