Wednesday, June 26, 2013

[SQL Server 2008 issues] configuring Mirroring problem

[SQL Server 2008 issues] configuring Mirroring problem

configuring Mirroring problem

Posted: 25 Jun 2013 05:44 PM PDT

Hi... when i am configuring Mirroring i am getting tcp\ip port problem..my 1st server dafault Instance[MSSQLSERVER] run under Local system service account..2nd server named Inst[pcname\shiva] run under \.shiva service account..1st server backup and 2nd server restore completed...but wen i am configuring mirroring i got problem like tcp\ip port problem, database space prob..what is the solution for ths prob??thanks...

Transactional replication fails with no meaningful error message

Posted: 20 Jun 2013 11:58 AM PDT

Hi,I am trying to setup replication between two clustered instances belonging to the same 3 node cluster. The distributor is a separate instance from publisher and subscriber.When I re-initialise a publication I get this error message:The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed.Neither job history nor SQL Server logs provide any other meaningful messages.When I try to run the snapshot from command prompt it just works fine:"C:\Program Files\Microsoft SQL Server\100\COM\SNAPSHOT.EXE" -Publisher [sqlcluster1\publisher] -PublisherDB [MyDB] -Distributor [sqlcluster2\distributor] -Publication [MyPublication] -DistributorSecurityMode 1 Log reader and distributor agent fail with the same generic message.SQL Server agent account is a local admin on each cluster node and a member of sysadmin role on each instance.I am running SQL Server 2008R2 SP2.Any ideas?Thanks.

What to do with test Data from PROD?

Posted: 25 Jun 2013 05:13 PM PDT

Dear All, After every release into production, we are doing a rough testing in production, then deleting all those test transactions from it.is it a good practice? or is there anyAppreciating your help.

Data export where am i going wrong

Posted: 25 Jun 2013 04:43 PM PDT

Hi ExpertsI have a procedure as follows[code]USE [TestData]GO/****** Object: StoredProcedure [dbo].[exporttocsv] Script Date: 06/26/2013 15:31:34 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[exporttocsv]asBEGIN DECLARE @sql varchar(8000)SELECT @sql = 'bcp "select * from dbo.newtable" '+ 'queryout "C:\inetpub\wwwroot\uploads\cleansed.csv" -c -t, -T -S'EXEC master..xp_cmdshell @sqlend[/code]when i run this using "exec exporttocsv" it produces the following output even though the table does exist[code]SQLState = S0002, NativeError = 208Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'dbo.newtable'.SQLState = 37000, NativeError = 8180Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.NULL[/code]any ideas

Is it possible to make Outer join with old style code

Posted: 25 Jun 2013 04:08 PM PDT

Hi,I'm dealing with old style code that do joins without JOIN keyword like in a piece below, in such case is it possible somehow to implement OUTER functionality ? [code]select t1.c1, t1.c2, T2.COL3 from t1, T2 where t1.c1 = T2.Col1 [/code]

Problem

Posted: 24 Jun 2013 07:16 PM PDT

SELECT eid,ename,remarks,CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS SpendtimeFROM(SELECT g.eid,e.ename,remarks,DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedateFROM attend_log g left join employee e on g.eid=e.eidwhere g.eid=17090 and date >'20130101' and date <'20130131'group by g.eid,e.ename,g.remarks)ti want this type of resulteid------ename----------late---------spendtime17090----abc-------------2-----------163:54if remark show 2 lates then late column show 2

Script all stored procedures modified after a given date? ...

Posted: 25 Jun 2013 02:38 PM PDT

Hello,I can get a list of all the stored procedures modified since a given date -- but now I want to script all of those procedures (so I can port changes over to another server) ...Does such functionality exist in SQL Server Management ?? Thanks in advance.

Why is my TLOG growing

Posted: 24 Jun 2013 07:25 PM PDT

SQL Server 2008R2We have an application (on a DEV box) that I am told [b]they are not using yet [/b]but the Transaction log for this Database grows in excess of 18GB everyday. Since this is a Dev box with limited resources, I am forced to shrink the log on an almost daily basis because of drive space issues. Use MYDBGO-- Perform a Full DB backup --Perform a Transaction log backup-- shrink the Transaction log file DBCC SHRINKFILE(MYDB_LOG, 1)-- backup and truncate log BACKUP LOG MYDB TO DISK= 'NUL:'-- shrink the Transaction log file DBCC SHRINKFILE(MYDB_LOG, 1)--Perform a Full DB backup Right now, I have change the recovery mode to simple because even with doing TLOG backups every 15 mins in Full Recovery Mode, the log can grow as much as 4 gb in just 15 mins. I will see how the DB and Tlog files handle being in simple recoery mode (how much growth) One thing that I need some help analyzing is the Profiler Trace that I did while the transaction log was in a 'growth spurt' there are 'RPC: Completed' messages in the profiler trace - one after he other - like this: exec sp_execute 988,511,35230,990,N'myapplication.POL_SUPRES_ISS_IND',14,N''exec sp_execute 988,511,35230,576,N'N'myapplication.FREE_LK_INCR_CD',14,N''exec sp_execute 988,511,35230,662,N'N'myapplication.INIT_PAC_REDRW_IND',14,N''These 'RPC' calls are being made from the application (hostname is app server name, login is sql login that was setup for the app to access the database) Hope that's not too vague. Any help or guidance would be appreciated.

query

Posted: 25 Jun 2013 06:06 AM PDT

dear all,i have follwoing tableREGION date_apprvd sala 20130101 1000a 20130201 2000a 20130301 3000b 20140101 4000b 20140201 5000b 20140301 6000c 20130101 7000c 20130201 8000c 20130301 9000d 20130101 1100d 20130201 2100d 20130301 3100e 20140101 4100e 20140201 5100e 20140301 6100pls help me to create a queryinput is 20130101region sal counta 1000 1b 0 0c 7000 1d 1100 1e 0 0thanksnick

Partitioning Existing table with Non clustered index on Date column

Posted: 25 Jun 2013 05:40 PM PDT

Hi All, I have one table with 10 lacks records. I partitioned that table on CreatedDate column with non clustered index( i am not removing clustered index on ID column, It is as part of primary key).It is inserting to data into relevant partition only. But i am verifying is that table partitioned or not by using below steps, in object Explorer-Database-->TestDB-->tables-->select partitioned table and Right click on table select properties --Storage [b]File Group= PrimaryTable Partitioned = False[/b]If create Partitioned with Clustered index , it is showing correctly [b] Table Partitioned = True[/b] But i am creating with non clustered.Can any one explainIs it table partitioned or not? and how to know data is coming from which partition (with out using ($partition)below are example table partition script which i followed steps for original table.CREATE TABLE tblPartition(ID int primary key identity(1,1),Name varchar(30),CreatedDate Datetime)insert into tblPartition(Name,CreatedDate)SELECT 'Name1','2013-05-26 13:53:47.650'union allselect 'Name2','2013-05-26 13:53:47.650'union allSELECT 'Name1','2013-06-26 13:53:47.650'union allselect 'Name2','2013-06-26 13:53:47.650'union allSELECT 'Name1','2013-07-26 13:53:47.650'union allselect 'Name2','2013-07-26 13:53:47.650'goCREATE PARTITION FUNCTION [PartitionFunction](datetime) AS RANGE RIGHT FOR VALUES (N'2013-05-31 23:59:59', N'2013-06-30 23:59:59', N'2013-07-31 23:59:59')CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunction] TO ([FGNdf10], [FGNdf11], [FGNdf12], [PRIMARY])CREATE NONCLUSTERED INDEX [IX_PartitionScheme_CreatedDate] ON [dbo].[tblPartition]( [CreatedDate])WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartitionScheme]([CreatedDate])goselect * from tblPartitionwhere $partition.PartitionFunction(CreatedDate)=1goThanks,PRR

Replicating db

Posted: 25 Jun 2013 11:47 AM PDT

Hi,I have a database of 200 GB. I need to replicate this db to 5 different servers and make them online? Is Transactional replication only my option? WIth such a huge DB, and replicating all the tables, I am notsure how the performance would be. DO I have anyother option? LogShipping or DBMirroing would work?

unexpected Error occured in the database server Time out expired

Posted: 25 Jun 2013 06:14 AM PDT

Hi all ,Application team mail me this error...I checked the no timeout error in SQL server error log...No I/O related error , even eventvwr not showing any thing...but this is second time application team saying they are getting this error , plz suggest how to monitor it.As i m checking with monitor disk counters and sql buffer counter as it is also normal

SSIS package in msdb & sql server agent

Posted: 25 Jun 2013 09:06 AM PDT

I have a sql agent job that calls the SSIS package stored in the same sql server in database msdb.Sometimes after I import a newer version of SSIS package to msdb and try to adhoc run a sql agent job, the job still picks the older version of the SSIS package.Is anyone seen this issue? If so how to make it right every time I import a SSIS package?Thanks in advance.

How to prevent SQL Injection Attack from SQL server side

Posted: 20 Jun 2013 05:05 AM PDT

Hi Everyone,Is it possible to stop SQL Injection Attack at SQL server level?I have gone through some posts and articles that suggest all the checks at application level so that only authentic data can be entered into database.My client has a travel portal and facing SQL injection attack. My knowledge is limited in this topic. Please can anyone help and let me know in case we can do something at SQL server level so that it can be stopped.An early response would be highly appreciated.thanks in advance.

how to restore a stored procedure from a backup

Posted: 25 Jun 2013 02:30 AM PDT

The db is too big to restore fully, can't even just restore the primary filegroup anywhere. How can I get a single stored procedure back?

Replication

Posted: 25 Jun 2013 12:21 AM PDT

can we get any faqs on sql replication as i did not configured replication as of now ?

How much Ram required.

Posted: 25 Jun 2013 02:33 AM PDT

Hi Team,I've installed sql server standard edition 2008R2, and my database size is 30GB, Harddisk is : 500GB,How much of RAM is required to overcome High physical memory utilisation issue.please suggest..!

display specic char in sql server

Posted: 25 Jun 2013 02:31 AM PDT

Hi friends i have small doubt in sql server plese tell me how to solve this issue.table data contains like name sas programing ssis programingbased on this table data i want display only 'gr' in output like name gr gri tried like this select substring(name,charindex('gr',name),len(name))its show only position of number its not display 'gr'plese tell me query how to solve this issuse in sql server.

Snapshot Replication

Posted: 24 Jun 2013 11:08 PM PDT

Hi guys.I wanted some clarification about snapshot replication. What happens when the snapshot replication is now in the middle of transferring data to the subscriber when the distribution agent job suddenly stops? What happens when I try to restart the synchronization again? Will it continue to transfer the data where it stopped? Or will it retransfer all of the data to the subscriber again?I'm asking this since we have a snapshot replication that runs for a long time and in between that time, there's another job that will run but it is suspended maybe due to resources availability. I thought we could try to stop the replication process and let the other job complete. And then rerun the distribution agent again to continue with the replication.Thank you

?? on creating a temp table for a date range

Posted: 24 Jun 2013 11:58 PM PDT

Hi,Can someone pint me in the direction of where I can see some examples on creating a temp table for a date range?What I need to do is use SSRS with parameters for a month and year o create report. I then need to choose data within that month and create a matrix report.Problem I find is some dates don't have data but I still want to show the date as null so I show all dates in the month. I assume I have to create a temp table using a dimdate table?ThanksJoe

Matrix Report in SQL

Posted: 25 Jun 2013 12:28 AM PDT

Hi i have master table called "pending_List" . ID Pending Name 1 Reason12 Reason23 Reason3in Page i will select the any of the data from pending_list and i ll save it my histoty tablenow my requirment is wants to show the past 9 days pendinglist count in Report. see the below sample.-----------------------------------------------------Pending Name 25/06 24/06 23/06 22/06-----------------------------------------------------Reason1 1 3 NULL 2Reason2 NULL 10 5 1Reason3 4 NULL 3 1Please find the attachement for more clarifications and help me for this..

Mirroring Issue

Posted: 24 Jun 2013 07:21 PM PDT

Hi all, Today i started to setup a mirror between two instances which are in same domain. The mirroring setup is failed in the final step. i got this error message from error log. Can any one help me out.2013-06-25 11:48:16.070,spid38s,Error: 1474, Severity: 16, State: 1.2013-06-25 11:48:16.070,spid38s,Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://QBSRECPU04.QBSTRUST.COM:5023'.2013-06-25 11:48:35.980,spid36s,Error: 1443, Severity: 16, State: 2.2013-06-25 11:48:35.980,spid36s,Database mirroring has been terminated for database 'qbs'. This is an informational message only. No user action is required.

help with indexing a table used for reports

Posted: 24 Jun 2013 07:48 PM PDT

HelloI have this table in sql server 2008 r2 which is populated periodically by a job. This table is used in a stored proc called by about 40 reports in ssrs.I am in the process of creating indexes on this table. Any suugestions / help would be valuable. Table structure (just including the bare minimum columns out of about 40)Deptid productid customerid territoryid salespersonid areaid areamanagerid netsales netsalesqty yearmonth1. Sales person, area managers. Dept heads and above log in to view reports.2. Reports have filters like dept, territory, customer, area, territory, product, month and year.3. Reports are grouped by dept / area / product4. Users can see data only relevant to them or further on the filters they selectThanks

Logshipping Issue

Posted: 24 Jun 2013 07:25 PM PDT

Hi all,I setup a log shipping between databases across two instances. the setup was complete. But when i see the error log i got this message.Progress,1,QBSRECPU04,LSBackup_IPE_QUOTE_OCT_17,Log shipping backup log job step.,,2013-06-25 10:00:13.19 *** Error: Backup failed for Server 'QBSRECPU04'. (Microsoft.SqlServer.SmoExtended) ***<nl/>2013-06-25 10:00:13.22 *** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***<nl/>2013-06-25 10:00:13.22 *** Error: Cannot open backup device 'd:\Log_shipping_shared\IPE_QUOTE_OCT_17_20130625043012.trn'. Operating system error 3(The system cannot find the path specified.).<nl/>BACKUP LOG is terminating abnormally.(.Net SqlClient Data Provider) ***<nl/>2013-06-25 10:00:13.26 ----- END OF TRANSACTION LOG BACKUP -----<nl/><nl/>Exit
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog