[SQL 2012] NUMA and PLE on SQL Server 2012

---

• NUMA and PLE on SQL Server 2012
• Page life expectancy diving to sub-10 on a 128 GB server
• Replication to Oracle 11g, Help!!!
• SQL Server 2012 Import Export Wizard (32bit) - xlsb file format ????
• Powershell 3.0 provider?
• Very high CPU usage when processing a database in SSAS 2012 (process msmdsrv)

NUMA and PLE on SQL Server 2012 Posted: 04 Feb 2013 11:26 PM PST I've read both Paul and Jonathan's blogs regarding this issue (http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/) and started looking at the PLE counters for each individual NUMA node. I can't seem to wrap my head around why there is such a widespread discrepancy between the NUMA nodes. We are running SQL Server 2012 Enterprise Core. Any insight would be greatly appreciated.Thanks,Tommy[url=https://skydrive.live.com/redir?resid=EB98D18648791013!7197&authkey=!AN6DKRDzgyJlarU]https://skydrive.live.com/redir?resid=EB98D18648791013!7197&authkey=!AN6DKRDzgyJlarU[/url][img]https://skydrive.live.com/redir?resid=EB98D18648791013!7197&authkey=!AN6DKRDzgyJlarU[/img]

Page life expectancy diving to sub-10 on a 128 GB server Posted: 27 Feb 2013 09:36 AM PST Last weekend we migrated our primary ERP system from a Windows 2003, SQL 2008R2 server to a windows 2008R2, SQL 2012 server.The new servers are much beefier than the old ones, including an extra 32 gig of ram. The ERP database is ~500 gig, of which a good amount is audit data. On the old server I generally had page life expectancy values around 1000 or more for normal daily activity.On the new server I have been capturing perfmon stats for the past week and I see the PLE jumping all over the place. It climbs up into the several hundred but then suiddenly dives to ridiculously low values like 12 or even 7. The server usage profile has not changed, it's still the same users and applications doing the same things they were doing last week.The instance is set to have min 70 gig, max 110 gig allocated to SQL.The service account has lock pages in memory permission.Can anyone think of some setting I might have missed during migraiton, or some new option to be set in SQL2012 that I've overlooked, that could explain this?

Replication to Oracle 11g, Help!!! Posted: 10 Apr 2013 02:10 PM PDT I would like to replicate transaction changes from sql server (2005 & 2012) to oracle 11g, I want the transaction changes (insert, update, delete) on tables to replicate to oracle tables fast, what kind of tools in sql server can I use? How can I just use the transaction log from sql server and apply the delta changes to oracle database, thanks?

SQL Server 2012 Import Export Wizard (32bit) - xlsb file format ???? Posted: 10 Apr 2013 07:03 AM PDT Hi Really could use some guidance here... I have a very large xlsb file(s) which I need to import So I am using SQL Server 2012 Import Export Wizard (32 bit)1.Choose a Data SourceI set the Data Source : Excel from the dropdownThen browse to my xlsb file which automatically changes the Excel version dropdown to Microsoft Excel 2007Then the Wizard has a little think and comes back with a message saying[b]Operation could not be completeExternal Table is not in the expected format [/b]Is there something else that I am supposed to do... cant seem to find anyone with the same issue online:crazy:

Powershell 3.0 provider? Posted: 10 Apr 2013 05:49 AM PDT When running a Powershell step in a SQL Agent job, the environment is 2.0. Is there a new 3.0 provider for SQL Server? Seems strange that PS 3.0 wasn't either shipped with SQL 2012 or at least that there wasn't a way to update it with a new provider when 3.0 became generally available.I know you can run a cmdexec step and execute a PS command shell there but that's kind of a kludge in my book.

Very high CPU usage when processing a database in SSAS 2012 (process msmdsrv) Posted: 10 Apr 2013 05:00 AM PDT Hi,Is there a way to limit the CPU usage of a process? When we process one of our SSAS database, the process (msmdsrv) the CPU is spiking to 85-90%, causing the rest of the processes to be unresponsive, this include one of our SQL 2012 server (on the same box).I had a look at WSRM, but unfortunately this is doing the opposite I need to do for CPU. The target is the percent of available CPU bandwidth that is guaranteed as a minimum to the process group.Any help will be appreciated.Rem

You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] Handling SQL Server Errors

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com

[T-SQL] Not able improve the performace of the query used in SSRS report

---

• Not able improve the performace of the query used in SSRS report
• How to script table data with either TSQL or Powershell?
• Migration from MSDE (SQL2K) to SQLEXPRESS (SQL2K8) - Query Performance Degradation
• DATABASEPROPERTYEX and the IsSubscribed property
• Replace Data in a String Based on Data in Table In line Query
• t-sql trimming before and after stored proc name
• inbuilt function : replicate, len, ltrim, rtrim and convert
• How to Calculate a column value, based on previous column value

Not able improve the performace of the query used in SSRS report Posted: 10 Apr 2013 11:20 PM PDT I have used the below query in SSRS report but it is not at all rndring data.Can you plese help/guide me to improve the prformance of the query:-- Snapshot ValidationWITH Snapshot_CTE( flk_leadid , flk_leadsnapshotid , flk_fiscalperiodstart , flk_fiscalperiodend , flk_subject , flk_name , flk_companyname , flk_campaignid , flk_reportingproductfamilyid , flk_countryid , flk_stateid , flk_statuscode , flk_pricenettotal , flk_salesteamterritoryleadid , flk_territoryid , flk_leadownerterritoryid , ownerid , flk_createdon , flk_closeddate , snapshotcount , leadcount)As( select fleadsnapshot.flk_leadid , fleadsnapshot.flk_leadsnapshotid , fleadsnapshot.flk_fiscalperiodstart , fleadsnapshot.flk_fiscalperiodend , fleadsnapshot.flk_subject , fleadsnapshot.flk_name , fleadsnapshot.flk_companyname , fleadsnapshot.flk_campaignid , fleadsnapshot.flk_reportingproductfamilyid , fleadsnapshot.flk_countryid , fleadsnapshot.flk_stateid , fleadsnapshot.flk_statuscode , fleadsnapshot.flk_pricenettotal , fleadsnapshot.flk_salesteamterritoryleadid , fleadsnapshot.flk_territoryid , fleadsnapshot.flk_leadownerterritoryid , fleadsnapshot.ownerid , fleadsnapshot.flk_createdon , fleadsnapshot.flk_closeddate , SnapshotWithMaxCreatedOn.snapshotcount , 1 as leadcount from Filteredflk_leadsnapshot fleadsnapshot inner join ( select fleadsnapshot.flk_leadid , max(fleadsnapshot.createdon) as maxcreatedon , COUNT(fleadsnapshot.flk_leadid) as snapshotcount from Filteredflk_leadsnapshot fleadsnapshot where fleadsnapshot.flk_leadid is not null group by fleadsnapshot.flk_leadid ) as SnapshotWithMaxCreatedOn on SnapshotWithMaxCreatedOn.flk_leadid = fleadsnapshot.flk_leadid and SnapshotWithMaxCreatedOn.maxcreatedon = fleadsnapshot.createdon)SELECT SnapshotValidation.* FROM(-- No Snapshotselect 'No Snapshot' as [Error Type] , 1 as [Error Sequence] , LeadWithoutSnapshot.leadcount as [Lead Count] , flead.leadid as [Lead ID] , flead.[subject] as [Lead Name] , flead.createdon as [Lead Create Date] , flead.modifiedon as [Lead Modified Date] , flead.flk_closeddate as [Lead Close Date] , 0 as [Snapshot Count] , NULL as [Snapshot ID] , NULL as [Snapshot Name] , NULL as [Snapshot Fiscal Start] , NULL as [Snapshot Fiscal End]from FilteredLead flead inner join ( select COUNT(1) as leadcount, flead.leadid from FilteredLead flead where flead.leadid not in ( select distinct flead.leadid from FilteredLead flead inner join Filteredflk_leadsnapshot fsnapshot on fsnapshot.flk_leadid = flead.leadid ) group by flead.leadid ) as LeadWithoutSnapshot on LeadWithoutSnapshot.leadid = flead.leadid UNION ALL-- Fiscal Period Mismatchselect 'Fiscal Period Mismatch' as [Error Type] , 2 as [Error Sequence] , fsnapshot.leadcount as [Lead Count] , flead.leadid as [Lead ID] , flead.[subject] as [Lead Name] , flead.createdon as [Lead Create Date] , flead.modifiedon as [Lead Modified Date] , flead.flk_closeddate as [Lead Close Date] , fsnapshot.snapshotcount as [Snapshot Count] , fleadsnapshot.flk_leadsnapshotid as [Snapshot ID] , fleadsnapshot.flk_subject as [Snapshot Name] , fleadsnapshot.flk_fiscalperiodstart as [Snapshot Fiscal Start] , fleadsnapshot.flk_fiscalperiodend as [Snapshot Fiscal End]from FilteredLead flead inner join Snapshot_CTE fsnapshot on fsnapshot.flk_leadid = flead.leadid inner join Filteredflk_leadsnapshot fleadsnapshot on fleadsnapshot.flk_leadid = flead.leadidwhere -- lead close date is null but snapshot fiscal end date has value (Lead E, G) (flead.flk_closeddate is null and fsnapshot.flk_fiscalperiodend is not null) or -- snapshot fiscal start date is not correct saturday depending on lead created on (Lead F, M, N) (fsnapshot.snapshotcount = 1 and (convert(date, fsnapshot.flk_fiscalperiodstart) <> convert(date, dateadd(dd, -datepart(dw, flead.createdon), flead.createdon)))) or -- lead close date is not null but snapshot fiscal end date has no value (Lead K) (flead.flk_closeddate is not null and fsnapshot.flk_fiscalperiodend is null) or -- snapshot fiscal end date is not correct friday depending on lead closed on (Lead M, O) (convert(date, fsnapshot.flk_fiscalperiodend) <> convert(date, dateadd(dd, 6 - datepart(dw, flead.flk_closeddate), flead.flk_closeddate))) UNION ALL-- Attribute Mismatchselect 'Attribute Mismatch' as [Error Type] , 3 as [Error Sequence] , fsnapshot.leadcount as [Lead Count] , flead.leadid as [Lead ID] , flead.[subject] as [Lead Name] , flead.createdon as [Lead Create Date] , flead.modifiedon as [Lead Modified Date] , flead.flk_closeddate as [Lead Close Date] , fsnapshot.snapshotcount as [Snapshot Count] , fleadsnapshot.flk_leadsnapshotid as [Snapshot ID] , fleadsnapshot.flk_subject as [Snapshot Name] , fleadsnapshot.flk_fiscalperiodstart as [Snapshot Fiscal Start] , fleadsnapshot.flk_fiscalperiodend as [Snapshot Fiscal End]from FilteredLead flead inner join Snapshot_CTE fsnapshot on fsnapshot.flk_leadid = flead.leadid inner join Filteredflk_leadsnapshot fleadsnapshot on fleadsnapshot.flk_leadid = flead.leadidwhere (fsnapshot.flk_subject <> flead.[subject]) or (fsnapshot.flk_name<>flead.fullname) or (fsnapshot.flk_companyname <>flead.companyname) or (fsnapshot.flk_campaignid <>flead.campaignid) or (fsnapshot.flk_reportingproductfamilyid <>flead.flk_reportingproductfamilyid) or (fsnapshot.flk_countryid <>flead.flk_countryid) or (fsnapshot.flk_stateid <> flead.flk_stateid) or (fsnapshot.flk_statuscode <> flead.statecode) or (fsnapshot.flk_pricenettotal <>flead.flk_pricenettotal) or (fsnapshot.flk_salesteamterritoryleadid <>flead.flk_salesteamterritoryleadid) or (fsnapshot.flk_territoryid <> flead.flk_territoryid) or (fsnapshot.flk_leadownerterritoryid <> flead.flk_leadownerterritoryid) or (fsnapshot.ownerid <> flead.ownerid) or (fsnapshot.flk_createdon <>flead.createdon) or (fsnapshot.flk_closeddate <> flead.flk_closeddate) UNION ALLselect 'Multiple Snapshots' as [Error Type] , 4 as [Error Sequence] , 1 as [Lead Count] , flead.leadid as [Lead ID] , flead.[subject] as [Lead Name] , flead.createdon as [Lead Create Date] , flead.modifiedon as [Lead Modified Date] , flead.flk_closeddate as [Lead Close Date] , flsnapshot.snapshotcount as [Snapshot Count] , fleadsnapshot.flk_leadsnapshotid as [Snapshot ID] , fleadsnapshot.flk_subject as [Snapshot Name] , fleadsnapshot.flk_fiscalperiodstart as [Snapshot Fiscal Start] , fleadsnapshot.flk_fiscalperiodend as [Snapshot Fiscal End] from FilteredLead flead inner join ( select fsnapshot.flk_leadid, COUNT(1) as snapshotcount from ( select fsnapshotouter.flk_leadid , ( select COUNT(1) from Filteredflk_leadsnapshot fsnapshotinner where fsnapshotinner.flk_leadid = fsnapshotouter.flk_leadid and convert(date, fsnapshotinner.flk_fiscalperiodstart) = convert(date, fsnapshotouter.flk_fiscalperiodstart) ) as SnapshotCount from Filteredflk_leadsnapshot fsnapshotouter ) as fsnapshot where fsnapshot.SnapshotCount > 1 group by fsnapshot.flk_leadid ) as flsnapshot on flsnapshot.flk_leadid = flead.leadid inner join Filteredflk_leadsnapshot fleadsnapshot on fleadsnapshot.flk_leadid = flead.leadid UNION ALLselect 'Incomplete Snapshot' as [Error Type] , 5 as [Error Sequence] , 0 as [Lead Count] , flead.leadid as [Lead ID] , flead.[subject] as [Lead Name] , flead.createdon as [Lead Create Date] , flead.modifiedon as [Lead Modified Date] , flead.flk_closeddate as [Lead Close Date] , 0 as [Snapshot Count] , fleadsnapshot.flk_leadsnapshotid as [Snapshot ID] , fleadsnapshot.flk_subject as [Snapshot Name] , fleadsnapshot.flk_fiscalperiodstart as [Snapshot Fiscal Start] , fleadsnapshot.flk_fiscalperiodend as [Snapshot Fiscal End]from( select fleadinner.leadid, COUNT(fleadinner.leadid) as snapshotcount from Filteredflk_leadsnapshot fsnapshotinner inner join FilteredLead fleadinner on fleadinner.leadid = fsnapshotinner.flk_leadid where fsnapshotinner.flk_leadid = fleadinner.leadid and ( (fleadinner.flk_closeddate is null and fsnapshotinner.flk_fiscalperiodend is null) or (fleadinner.flk_closeddate is not null and fsnapshotinner.flk_fiscalperiodend is null) ) group by fleadinner.leadid having COUNT(fleadinner.leadid) > 1) as fleadouterinner joinFilteredLead flead on flead.leadid = fleadouter.leadidinner joinFilteredflk_leadsnapshot fleadsnapshot on fleadsnapshot.flk_leadid = fleadouter.leadid) as SnapshotValidationorder by SnapshotValidation.[Error Sequence], SnapshotValidation.[Lead Name], SnapshotValidation.[Snapshot Fiscal Start]

How to script table data with either TSQL or Powershell? Posted: 10 Apr 2013 05:34 PM PDT Hi,I can use GUI of the MS to script out data from a table. Is this possible using either Powershell or TSQL?Thanks.

Migration from MSDE (SQL2K) to SQLEXPRESS (SQL2K8) - Query Performance Degradation Posted: 10 Apr 2013 02:31 AM PDT I have migrated some databases from MSDE (SQL2K) to SQLEXPRESS (SQL2K8)Just using the backup and restore functions provided and made no other changes.I notice a query in particular has gone from taking 2 seconds in MSDE to taking 988 seconds in SQLEXPRESS. Hmmmm!Can anyone advise on why this would be happening?Query below... can't provide any sample data unfortunately but if you need any more info please let me know - not sure what would be needed?!?!(TBL_INSTANCES contains 12946 rows and TBL_RELATIONSHIPS contains 18137 rows)SELECT [Build1].dbo.TBL_TYPE_DEF.TYPE_NAME, [Build1].dbo.TBL_INSTANCES.DOC_NAME,[Build1].dbo.TBL_INSTANCES.DOC, [Build1].dbo.TBL_INSTANCES.SEC, [Build1].dbo.TBL_INSTANCES.VER,[Build1].dbo.TBL_RELATIONSHIPS.JUSTIFICATION FROM [Build1].dbo.TBL_INSTANCES INNER JOIN [Build1].dbo.TBL_TYPE_DEF ON [Build1].dbo.TBL_INSTANCES.TYPE_ID = [Build1].dbo.TBL_TYPE_DEF.TYPE_ID LEFT OUTER JOIN [Build1].dbo.TBL_RELATIONSHIPS ON [Build1].dbo.TBL_INSTANCES.DOC = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_DOC AND [Build1].dbo.TBL_INSTANCES.SEC = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_SEC AND [Build1].dbo.TBL_INSTANCES.VER = [Build1].dbo.TBL_RELATIONSHIPS.PARENT_VER WHERE (([Build1].dbo.TBL_INSTANCES.DOC + [Build1].dbo.TBL_INSTANCES.SEC + ISNULL(CONVERT(VARCHAR(10), [Build1].dbo.TBL_INSTANCES.VER), '')) NOT IN (SELECT PARENT_DOC + PARENT_SEC + ISNULL(CONVERT(VARCHAR(10), PARENT_VER), '') FROM [Build1].dbo.TBL_RELATIONSHIPS AS TBL_RELATIONSHIPS_1 WHERE ((PARENT_DOC + PARENT_SEC + ISNULL(CONVERT(VARCHAR(10), PARENT_VER), '')) IN (SELECT DOC+SEC+ISNULL(CONVERT(VARCHAR(10), VER),'') FROM [Build1].dbo.TBL_INSTANCES AS TBL_INSTANCES_1 WHERE (TYPE_ID IN (SELECT TYPE_ID FROM [Build1].dbo.TBL_TYPE_DEF AS TBL_TYPE_DEF_1 WHERE (TYPE_ID NOT IN (SELECT CHILD_TYPE FROM [Build1].dbo.TBL_RELATIONSHIPS_ALLOWED)))))) AND (CHILD_DOC IS NOT NULL))) AND ([Build1].dbo.TBL_INSTANCES.TYPE_ID IN (SELECT TYPE_ID FROM [Build1].dbo.TBL_TYPE_DEF AS TBL_TYPE_DEF_1 WHERE (TYPE_ID NOT IN (SELECT CHILD_TYPE FROM [Build1].dbo.TBL_RELATIONSHIPS_ALLOWED AS TBL_RELATIONSHIPS_ALLOWED_1)))) ORDER BY [Build1].dbo.TBL_TYPE_DEF.TYPE_ID,DOC,SEC,VER I am a total newbie to query optimization, so any help, guidance or direction appreciatedThanks in advance!Stacey

DATABASEPROPERTYEX and the IsSubscribed property Posted: 10 Apr 2013 02:15 AM PDT Hi All,When trying to examine if a database is a subscriber, I am trying to use DATABASEPROPERTYEX like:select DATABASEPROPERTYEX ( 'SubscriberDB' , 'IsSubscribed' )but it always returns 0, regardless of if the DB is a subscriber or not.Is there something i'm missing?Cheers.Rin

Replace Data in a String Based on Data in Table In line Query Posted: 10 Apr 2013 04:29 PM PDT Hi All,I trying to update a String based on the Inline View.String:-1 * [SAL] - [BON] - [COMM]In Line Query:Select * from TABLE Values ( SAL , COMM ) in tableNew String (Updated): -1 * [SAL] - 0 - [COMM]Please need help.

t-sql trimming before and after stored proc name Posted: 10 Apr 2013 04:02 AM PDT Hi, I have requirement to get only stored proc name and trim everything before and after stored procedure name. Here is the code i tried. so output only need to show "abc_SessionValuesUpdate"Any help will be great...thanks...:-)declare @col varchar(20)select @col = 'CREATE Procedure abc_SessionValuesUpdate AS SET NOCOUNT ON BEGIN UPDATE SessionValues SET Value = @Value WHERE ID = @ID AND Name = @Name IF @@ROWCOUNT = 0 INSERT INTO SessionValues values (@ID,@Name,@Value) END 'Select substring( LEFT(@Col,charindex('abc',@Col)+1),charindex('abc_',@Col)+1,len(LEFT(@Col,charindex('abc',@Col)-1))+1 )

inbuilt function : replicate, len, ltrim, rtrim and convert Posted: 10 Apr 2013 07:47 AM PDT can someone explain me why we use replicate in our query and also len, ltrim, rtrim and convert with useful example

How to Calculate a column value, based on previous column value Posted: 10 Apr 2013 06:18 AM PDT Hello All, Could you please help me here with this sql query, drop table #tempo1 create table #tempo1 (ActNo int, PlanName varchar(20), ProcessMonth varchar(8)) insert into #tempo1 values (100,'Keeper','2012-Jul') insert into #tempo1 values (101,'Keeper','2012-Jul') insert into #tempo1 values (102,'Valuer','2012-Jul') insert into #tempo1 values (103,'Keeper','2012-Jul') insert into #tempo1 values (104,'Valuer','2012-Jul') insert into #tempo1 values (105,'Keeper','2012-Jul') insert into #tempo1 values (110,'Keeper','2012-Jul') insert into #tempo1 values (100,'Keeper','2012-Oct') insert into #tempo1 values (101,'Valuer','2012-Oct') insert into #tempo1 values (102,'Valuer','2012-Oct') insert into #tempo1 values (103,'Keeper','2012-Oct') insert into #tempo1 values (104,'Keeper','2012-Oct') insert into #tempo1 values (105,'Valuer','2012-Oct') insert into #tempo1 values (106,'Valuer','2012-Oct') output will show below threecolumns, first two is straight , but the fourth column shoud be calculate based on second column (if secondvalue is same as fourth then just add literal 'Cnt 2' otherwie 'Cnt 1' so that i.e., if secondcolumn value is 'Keeper' and 4th column value is also 'Keeper' then show as 'Keeper Cnt2' sameway with Valuer as well, if it is different in july and oct then just oct column value with 'Cnt 1' literal ), for exmple AcctNo, JulyPtcpt, OctPtcpt 100, 'Keeper', 'Keeper Cnt 2' ---> (Both July and Oct same so added Cnt 2 at the end ) 101, 'Keeper','Valuer Cnt 1' ---> (Both July and Oct not same, so added Cnt 1 at the end ) 102, 'Valuer','Valuer Cnt 2' 103, 'Keeper','Keeper Cnt 2' 104, 'Valuer','Keeper Cnt 1' 105, 'Keeper','Valuer Cnt 1' 106, '' ,'Valuer Cnt 1' ---> because Not exist in prior month just leave blank or just leave with Cnt1 110, 'Keeper','' --> not exist in oct so leave blank or Just leave it as Cnt1 please help me here, i am stuck at the processmonth separation for jul/oct Kindly assist me here, milan

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] Using Dynamic SQL in Stored Procedures

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com

[SQL Server 2008 issues] SQL Server Error Log stops logging activities of the server

---

• SQL Server Error Log stops logging activities of the server
• How to Put results of FISQL in file
• SSRS Paramaters
• Multiple tables in a Report
• SQL Server and RAM utilisation
• Logging on with a DSN using a different schema.
• Convert IP address to Binary (32)
• SQL 2008 - Resource Governor - CLBackup.exe
• Sql Jobs steps
• Problems with file permissions
• DBCC on a detached MDF file
• Extract from xml stored as a varchar in a database
• simple count in sql?
• Replication - Subscription marked inactive and must be reinitialized
• Remote / Local node page looups/sec both zero
• How to join these two queries?
• How to find common tables between two databases?
• access denied message while executing xp_cmdshell
• Restoring database
• In linked server, which server will process data?
• Add user to sql server with the required permission
• Developers track changes
• Error Message SqlDateTime
• Finding DROPPED Trigger
• TempDB activity after inserts

SQL Server Error Log stops logging activities of the server Posted: 10 Apr 2013 06:24 PM PDT Hi Guys.Is it normal that SQL Server Error Log stops logging server activities like backup operations, errors? Though server activities are functioning well like scheduled backups. Is there a way to find out what might cause this to stop logging? We have a monitoring tool that's basically getting most of its data in the error log.Thank you.

How to Put results of FISQL in file Posted: 10 Apr 2013 04:31 PM PDT Hi ,I need help :I have two machines: one with my sql server and the other with a CENTOS.In CentOS I installed fisql.I would like to know how to put the result of several request to a file?With the -o option of fisql, I have two problems:1) I can only record the results of a query in a file2) I have prompts that appears (1 >> 2 >> ...)Someone know a solution ?

SSRS Paramaters Posted: 10 Apr 2013 06:57 AM PDT Hi everyone I have a report in SSRS which contains the paramater @Region. The paramater allows for multiple selections and is set automatically to show all of the regions. Within one of the text boxes within the report I have the following expression: ="Report showing sales at: " & Fields!Region.Value My problem is that there are too many regions so too much text in the text box of thr report when it initially renders. I'd like the expression to say "Sales for all Regions" when the report is initially rendered but the actual specific regions if and when they have been selected. Thanks in advance. BO

Multiple tables in a Report Posted: 10 Apr 2013 11:40 AM PDT Hey guys,I have a challenge in displaying the database server health statistics with a minimum refresh rate (about 5-30 seconds). There are about 6 datasets for which I will be using 6 different tables(which have linear gauges) for dispaly. 4 of these 6 datasets take quite a bit of time to query(about 2mins). Is there another efficient way of doing this as the report is becoming slower and slower as I keep adding more tables into it.Would dashboarding be faster? I have never done dashboarding before and I am just assuming that a dashboard drives the datasets as different reports hence faster rendering time.I would appreciate you guys taking time to answer my questions.Thank you.

SQL Server and RAM utilisation Posted: 10 Apr 2013 11:12 AM PDT Hi All, I am loading a table of data(1TB) from one server to other, I am loading it through SSIS using execution utility. Initially when i started the process i noticed that the load take 60 Secs to load 100K records, gradually the time keep on increases to load same 100K records. Now it is taking 560 Secs(and still increasing....) to load 100K records. Can you Please let me know how can i make it to run fast. without stop and start the process or restart the windows server. Can i run DBCC DROPCLEANBUFFERS command is it is advisable ? Thanks in advance!

Logging on with a DSN using a different schema. Posted: 10 Apr 2013 11:28 AM PDT Here are the facts.Tools:MS SQL 2012 Express MS SQL 2005 Native Driver (because not all of our computers are up to date obviously)MS Access 2010.This is an Access db with a default form that calls an ODBC recordsource.My [u]File[/u] DSN works OK when I connect to tables using the default dbo schema. It bring the MS SQL Server dialog box.If I try to connect to tables in a different schema (with all public privileges denied), it will raise an error message but my problem is that it will NOT prompt the normal log in dialog box. After the error message, there is nothing.If I use my Windows profile as identification with sa privilege, again it will just go through (no error) but no log in dialog box whatsoever.How do make sure that the dialog box is always present?

Convert IP address to Binary (32) Posted: 10 Apr 2013 03:56 AM PDT Hello,I am trying to convert an IP address from regular format (24.55.187.203) to binary 32-bit format (00011000001101111011101111001011).Can someone please help me with SQL script? I have over 35 million ip addresses that I need to convert?Thanks

SQL 2008 - Resource Governor - CLBackup.exe Posted: 10 Apr 2013 06:54 AM PDT Hello,We are having issues with a backup system (CLBackup.exe) taking too much CPU when it does a disk backup. It is being used by our hosting company to perform a daily backup of specified files and directories. The time of the backup run and the amount of files backed up have all been modified but still seeing too much CPU pressue when it runs.It averages about 35% CPU but can take as much as +50% CPU which is putting too much pressue on the Database server.Can the Resource Governor be used to throttle the CPU usage of this exe? Thanks,Daniel

Sql Jobs steps Posted: 10 Apr 2013 08:50 AM PDT Hello I have a several SQL JObs I need to run in sequence. For some reason when I run a job which I already setup with all jobs I need is not running correctly.And SQL Server is not waiting for the job to be completed before continuing to the next job.How can I accomplish this?Any help is appreciated!! thanks

Problems with file permissions Posted: 10 Apr 2013 08:55 AM PDT This is for sql server express 2008 r2. I am installing an .mdf file on users machines using a simple .msi installer created in vs2010.I really need an overall philosophy check on where to install .mdf files. My shrink-wrap end user app that uses this database will attach the database file, or connect to it as needed - this is not the problem.I chose to install the .mdf file in the folder users\public\documents\somecompany\database, thinking that this is the safest and most permissive location on everyone's machine. Security is not a big issue, I just want the database to work correctly every time. The end users have no technical ability to fix anything whatsoever.I am finding that on some installs, that sql server is throwing the error 5 permission denied when trying to mount the database. Using windows explorer, I see that the troubled system has a completely different set of ACL permissions on the .mdf file from mine. Their system is vista-32bit, and my test setup is a vanilla standard vista-32bit in a virtual machine. I have no idea how their public documents permissions got this way.My first idea for a fix, is to change the file permissions during the msi install to allow "Everyone" full permission on the .mdf file - I know how to do this. I know this would work, but it smells funny. In explorer, I tried setting the permission for the SQLServerMSSQLUser..., but that alone didn't work for some reason. I need to know the exact permission for sql server 2008 that will work every time. Or better than that, need guidelines for foolproof installs of .mdf files.

DBCC on a detached MDF file Posted: 10 Apr 2013 07:05 AM PDT Hello,I am by no means above a SQL novice, but I am trying to figure out if there is a way to run DBCC commands (DBCC fileheader in particular) on an offline/detached MDF? Is this possible? If so, any help pointing me in the right direction? Thank you!

Extract from xml stored as a varchar in a database Posted: 10 Apr 2013 07:50 AM PDT Hello everyone, I need to extract information form xml stored in a database and store the extracted information to another database. The xml is stored as varchar. Is there any good way to extract the information from the database ?

simple count in sql? Posted: 10 Apr 2013 07:01 AM PDT I have a table structure as follows:guidA, market, isInMarket, guidB (lets just say all of these columns are strings)I would like to get a count grouped by market, where guidA == guidB and isInMarket == 1for example:row 1: 23458-293-392-29182, AR, 1, 23458-293-392-29182 <-- want this to add 1 to countrow 2: 19383-958-958-95832, AR, 1, 39201-394-999-84757 <-- this is not a matchoutput example:Market, MatchCountAR, 467This seems so simple, however, I'm having trouble with it.

Replication - Subscription marked inactive and must be reinitialized Posted: 10 Apr 2013 02:28 AM PDT I have transactional PUSH replication and recently got the following error:"The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated."I manaully ran job "Reinitialize subscriptions having data validation failures", but the problem still persists. When I created this replication, I set it without synchronization from snapshot because the database is rather large, it's about 200 GB. I restored backup and set it with initilization from backup. There were couple of arrors for violation of PK, I cleared them, and now there are no PK violations.Now, what options I have in order to fix above error? My version is: 2008 R2 Enterprice on both servers.Replication: transactional, push.Size of publication database: close to 200 GB.Thanks.

Remote / Local node page looups/sec both zero Posted: 12 May 2012 09:13 PM PDT When I try to monitor the sys.dm_os_performance counters "SQLServer:Buffer Node\Local node page lookups/sec" and "SQLServer:Buffer Node\Remote node page lookups/sec" they consistently show up as zero on all our servers. (2008 R2 SP1):object_name counter_name instance_name cntr_value cntr_typeSQLServer:Buffer Node Local node page lookups/sec 3 0 272696576SQLServer:Buffer Node Remote node page lookups/sec 3 0 272696576SQLServer:Buffer Node Local node page lookups/sec 2 0 272696576SQLServer:Buffer Node Remote node page lookups/sec 2 0 272696576SQLServer:Buffer Node Local node page lookups/sec 0 0 272696576SQLServer:Buffer Node Remote node page lookups/sec 0 0 272696576SQLServer:Buffer Node Local node page lookups/sec 1 0 272696576SQLServer:Buffer Node Remote node page lookups/sec 1 0 272696576Now, clearly, they shouldn't BOTH be zero! Is anyone else having the same issue? I can't find any documentation / blogs / cries for help out there from any other sufferers.

How to join these two queries? Posted: 10 Apr 2013 03:31 AM PDT Hello Everyone.I have 2 separate queries that I would like to join together, but have been unable to.I would like to run a single query that would return3 columns 'Name0', 'Agenttime' and 'LastBootUpTime0'Does anyone know how I can accomplish this?Thanks!------------------------------------------------------------------select Name0,Agenttimefromv_AgentDiscoveries AGD,dbo.v_GS_COMPUTER_SYSTEM CSWhere CS.ResourceID = AGD.ResourceIDand AGD.AgentName = 'Heartbeat Discovery'order by Name0 -------------------------------------------------------------------- SELECT distinct sys.Name0, os.LastBootUpTime0 FROM v_R_System sys join v_GS_OPERATING_SYSTEM os on sys.ResourceID=os.ResourceID join v_FullCollectionMembership fcm on fcm.ResourceID=os.ResourceID

How to find common tables between two databases? Posted: 09 Apr 2013 07:13 PM PDT Hello All,Can someone tell me how can we find common tables between 2 databases? Any scripts or querry plzz.thanks in advance...

access denied message while executing xp_cmdshell Posted: 09 Apr 2013 11:35 PM PDT Hi,I am trying to execute below simple copy statement using xp_cmdshellEXECUTE xp_cmdshell 'copy C:\test\*.txt C:\"Program Files\"'it gives an error as 'Access is denied'even I get same error while creating sub directory or creating text files in program files directory.but if same command is run by providing any other directory name like-EXECUTE xp_cmdshell 'copy C:\test\*.txt C:\"New folder\"' it executes.Can any one explain why is it so, and how it can be achieved.

Restoring database Posted: 09 Apr 2013 11:59 PM PDT Hello,I am trying to automate my code by restoring once a week database from one server to another, this is my plan:1.Create back up,2.copy back up from one server to another3.restore backup to the server.How can I do it,can any one recomend it any expereince with it?Thank you

In linked server, which server will process data? Posted: 10 Apr 2013 12:57 AM PDT In ServerA, I created a linked server to ServerB. In ServerA, there is a query like below. select * from [ServerB].order.dbo.allorder where city='london'Question:Which server will process data? 1) processing in B and then pass the result to A2) pass all data in A from B, process in A

Add user to sql server with the required permission Posted: 09 Apr 2013 10:24 PM PDT In my sql server express 2008 r2 i have about 15 login account. Now i want some about 5 users have full permission while some other 5 users with limited access to some database, and other users only read permission. In future i may add other users also. So i was thinking of doing it in simple way than giving permission for every user as it requires giving permission to each database every time a new user is created. So can i create a set of rules or group and then add a new user to that group or rules? (So new user who gets added that group gets the permission automatically from that group). Do i have any kind of facility in sql server which will make this happen?

Developers track changes Posted: 10 Apr 2013 12:06 AM PDT Hello,I am trying to find out how can I track changes in my database, how deleted table or updated table etc, how can I track this?Thank you

Error Message SqlDateTime Posted: 09 Apr 2013 10:25 PM PDT Please, When I run a Select on my table i receive this message: "Une erreur s'est produite lors de l'exécution du lot. Message d'erreur : Dépassement SqlDateTime. Doit être compris entre 1/1/1753 12:00:00 AM et 31/12/9999 11:59:59 PM." and It's not possible to read data in my table. WHat can i do to fix this problem without loosing data in my table?NB: I am using sql server 2008 and the command is "Select * from mytable"Thanks in advance

Finding DROPPED Trigger Posted: 09 Apr 2013 10:23 PM PDT Hi Team,I've 100 of triggers in my database, today i found that some triggers are dropped from my database.How to find the dropped trigger names.Please help me...

TempDB activity after inserts Posted: 09 Apr 2013 11:12 PM PDT I am running into issue doing a insert into fact table. Essentially i can see that insert is done and i can query table by using nolock. But job keeps on running (insert done with in 40 min and job ran for 3 hours) and i can see tempdb read and write activities in resource monitor. There is no disk disk activity on datafiles or logfiles of database i am trying to insert. Is sql server trying to process foreign keys to table? since its fact table i have lot of dimension keys but i am not sure. Also there is no other process running on server at that time. Any help is appreciated

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server Data Warehousing] Where to find best practices for tuning data warehouse ETL queries?

Hi Everybody,

Where can I find some good educational material on tuning ETL procedures for a data warehouse environment?  Everything I've found on the web regarding query tuning seems to be geared only toward OLTP systems.  (For example, most of our ETL queries don't use a WHERE statement, so the vast majority of searches are table scans and index scans, whereas most index tuning sites are striving for index seeks.)

I have read Microsoft's "Best Practices for Data Warehousing with SQL Server 2008R2," but I was only able to glean a few helpful hints that don't also apply to OLTP systems:

• often better to recompile stored procedure query plans in order to eliminate variances introduced by parameter sniffing (i.e., better to use the right plan than to save a few seconds and use a cached plan SOMETIMES);


• partition tables that are larger than 50 GB;


• use minimal logging to load data precisely where you want it as fast as possible;


• often better to disable non-clustered indexes before inserting a large number of rows and then rebuild them immdiately afterward (sometimes even for clustered indexes, but test first);


• rebuild statistics after every load of a table.

But I still feel like I'm missing some very crucial concepts for performant ETL development.

BTW, our office uses SSIS, but only as a glorified stored procedure execution manager, so I'm not looking for SSIS ETL best practices.  Except for a few packages that pull from source systems, the majority of our SSIS packages consist of numerous "Execute SQL" tasks.

Thanks, and any best practices you could include here would be greatly appreciated.

-Eric

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Field Value in Report Header

Hi to all;

I created a report using Report Builder 3.0, with row groups

A

   B

      C

         D

There are columns for B, C, D; A is not visible in the report. And I set Page Break option for group A. My issue is : How can I view the value of A in a textbox or in the header (with different values of A on each page) ? Something like this:

Page 1

Value of A  : USA

    B                           C                                     D

Food                       Meat                              1,000

Beverage               Tea                                     10

Page 2

Value of A  : Canada

    B                           C                                     D

Food                       Meat                              3,000

Beverage               Tea                                     40

I am new at reporting. It might be a simple issue for you but I couldn't solve it. Thanks in advance.

.

social.technet.microsoft.com/Forums