Wednesday, July 17, 2013

[SQL Server 2008 issues] PAE

[SQL Server 2008 issues] PAE

PAE

Posted: 16 Jul 2013 06:44 PM PDT

can we use both \3GB and PAE together ?

Issue with DATEDIFF returning Negative Values

Posted: 16 Jul 2013 08:12 AM PDT

I have written the following query and it is returning negative values for some records when calculating the number of days between two dates:set dateformat mdyselect b.VINTAGE,FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30), b.CR_SCR_BAND, case when a.DAYSPD < 1 then 'CURRENT' when a.DAYSPD between 1 and 30 then '<30DPD' when a.DAYSPD between 31 and 60 then '30DAY' when a.DAYSPD > 60 then '60+DAY' else 'ERROR' end as 'ME_DQ', count (a.account), sum(a.currtotalbal-a.CurrDisc) as 'BALANCE'from dbo.ME_MASTER a left join #temp b on a.ACCOUNT = b.ACCOUNTwhere a.OpenDt >=1110101 and VINTAGE is not nullgroup by b.VINTAGE, FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30), b.CR_SCR_BAND, case when a.DAYSPD < 1 then 'CURRENT' when a.DAYSPD between 1 and 30 then '<30DPD' when a.DAYSPD between 31 and 60 then '30DAY' when a.DAYSPD > 60 then '60+DAY' else 'ERROR' end order by b.VINTAGE, FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30), b.CR_SCR_BAND, case when a.DAYSPD < 1 then 'CURRENT' when a.DAYSPD between 1 and 30 then '<30DPD' when a.DAYSPD between 31 and 60 then '30DAY' when a.DAYSPD > 60 then '60+DAY' else 'ERROR' end set dateformat mdyselect top 10 b.VINTAGE, FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30) as 'MTH_ON_BOOKS', b.CR_SCR_BAND, case when a.DAYSPD < 1 then 'CURRENT' when a.DAYSPD between 1 and 30 then '<30DPD' when a.DAYSPD between 31 and 60 then '30DAY' when a.DAYSPD > 60 then '60+DAY' else 'ERROR' end as 'ME_DQ', a.account, (a.currtotalbal-a.CurrDisc) as 'BALANCE', cast(right(a.OpenDt,6)as date) as 'Book Date', CAST(right(a.ASOFDATE,6)as date) as 'Month End'from dbo.ME_MASTER a left join #temp b on a.ACCOUNT = b.ACCOUNTwhere a.OpenDt >=1110101 --and b.VINTAGE is null and FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30) < 0order by b.VINTAGE,FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30), b.CR_SCR_BAND, case when a.DAYSPD < 1 then 'CURRENT' when a.DAYSPD between 1 and 30 then '<30DPD' when a.DAYSPD between 31 and 60 then '30DAY' when a.DAYSPD > 60 then '60+DAY' else 'ERROR' endI would rather be using datefiff by months, but no matter which I choose I get negative values on some records. This sometime occurs on accounts where the two dates do not cross a year threshold. I have read several potential solutions but none seem to make sense. Any help would be greatly appreciated.Thanks!

Find the host name

Posted: 16 Jul 2013 06:46 PM PDT

Dear,I require to know the host name from which my database has been accessed I mean performed DDL,DML operations within a daterange. Say, I want to know the host name within the range of 16/07/2013 13:00:00 to 16/07/2013 14:00:00I don't have any trace file.Please help me to find out this. I am in stack.Regards,Akbar

SQL

Posted: 16 Jul 2013 06:28 PM PDT

I searched in sites but unable to find the exact document on sql architecture , can any one send the architecture of sql ?

Shrink database while restoring

Posted: 02 Aug 2011 08:03 PM PDT

Can we shrink the database files while restoring from backup?

Time-Out Occured while estimating the compress savings with Huge Table (100Gigs)

Posted: 16 Jul 2013 03:29 PM PDT

Hi,While checking the estimated compression saving with one of our huge table with size 100 Gigs+, I ran into Time-Out buffer latch error and not able to proceed further, even I am facing the same issue while rebuilding the database where this table exists and not able to rebuild the database. Appreciate any advice.Environment Details===================Windows 2003 serverSQL Server 2008RAM: 128 GBCPU: 24===================[b]sp_estimate_data_compression_savings 'Export','SE_VARCS_DBF',NULL,NULL,'PAGE'Msg 845, Level 17, State 1, Line 1Time-out occurred while waiting for buffer latch type 2 for page (1:24887741), database ID 12.[/b]Thanks,Nagarjun.

looping through all user databases

Posted: 16 Jul 2013 02:45 PM PDT

Hi Guys,Quite new with SQL Server commands..How can i enhance the below script so that it will run against all users databases if executed in master?thanks for ur help!SET NOCOUNT ON;DECLARE @objectid int;DECLARE @indexid int;DECLARE @partitioncount bigint;DECLARE @schemaname nvarchar(130);DECLARE @objectname nvarchar(130);DECLARE @indexname nvarchar(130);DECLARE @partitionnum bigint;DECLARE @partitions bigint;DECLARE @frag float;DECLARE @command nvarchar(4000);DECLARE @dbid smallint;-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function-- and convert object and index IDs to names.SET @dbid = DB_ID();SELECT [object_id] AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag, page_countINTO #work_to_doFROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')WHERE avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentationAND index_id > 0 -- Ignore heapsAND page_count > 25; -- Ignore small tables-- Declare the cursor for the list of partitions to be processed.DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;-- Open the cursor.OPEN partitions;-- Loop through the partitions.WHILE (1=1)BEGINFETCH NEXTFROM partitionsINTO @objectid, @indexid, @partitionnum, @frag;IF @@FETCH_STATUS < 0 BREAK;SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)FROM sys.objects AS oJOIN sys.schemas as s ON s.schema_id = o.schema_idWHERE o.object_id = @objectid;SELECT @indexname = QUOTENAME(name)FROM sys.indexesWHERE object_id = @objectid AND index_id = @indexid;SELECT @partitioncount = count (*)FROM sys.partitionsWHERE object_id = @objectid AND index_id = @indexid;-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.IF @frag < 30.0SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';IF @frag >= 30.0SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';IF @partitioncount > 1SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));EXEC (@command);PRINT N'Executed: ' + @command;END-- Close and deallocate the cursor.CLOSE partitions;DEALLOCATE partitions;-- Drop the temporary table.DROP TABLE #work_to_do;GO

Doing Insert/Update/Delete from Oracle Source to SQL Server Dest.

Posted: 16 Jul 2013 08:36 AM PDT

Hello,I have an Oracle database located on the remote machine and can conenct thru ODBC conenction and I have a SQL Server 2008 Standard edision on another server.Now, I have to Insert or update or delete the records in SQL Server destination, by comparing with Oracle source. There is a unique key column in that table and last_update column (in both source and destination) which I can use it for comparision.Is there any method for doing the Incremental load from Oracle Source to SQL Server destination.Thanks & Regards

Finding the Top X Values Each Day from History Table

Posted: 16 Jul 2013 04:53 AM PDT

I am trying to find the top 750 balances on each day from a history table of accounts on file in a consumer credit portfolio. Clearly this is easy if I did each day by itself, but I don't know how to find it for each day in a single query. I will need to further confine my results by the days past due of each record each day.Data Table:ASOFDATE (the effective date of the record in the history table)ACCOUNTBALANCEDAYSPDThere are more data elements in the table but these are the only ones I need.Thoughts?

Get total of field values

Posted: 16 Jul 2013 08:50 AM PDT

HiI am a newbie and need to get the grand total of the new_TotalNoofPlanMembers fieldand am having some difficulty as this isn't working for me.SELECT new_MembershipNumber AS 'Membership ID', FullName AS 'Member Name', new_SalesChannelOnlyforPrivateMembersName AS 'Bll To Corp. Name', new_TotalNoofPlanMembers AS 'Total # Plan Members' sum(new_TotalNoofPlanMembers) as 'Grand Total'FROM dbo.FilteredContact

Finding Missing values

Posted: 16 Jul 2013 04:49 AM PDT

Hello everyone,My name is Thomas and I am brand new to T-SQL. I was thrown into writing our company reports a little over two months ago. I am completley self-taught so forgive any ignorance I may show. I was throw into this out of necessity,we are a non-profit and cant afford any more IT staff. However have found that I really like reports! My question is how do I find a missing value?We want to know what clients who have had a visit greater than 01/01/2013 are also missing their Client Intake Forms with a form create date also greater than 01/01/2013. This information will be pulled from two tables. The table containing the document information and the table containing the visit information. They are joined on "PatientID".The client intake form is text value under the "Document Description Column" along with several other Document's such as referals, ROI's ect. I can easily pull the information on who has a Intake Form but struggling on who does not. Please see my "who does query"Select Distinct dbo.DocumentList.id AS 'DoucumentID',dbo.DocumentList.patientId AS 'ClientID',dbo.DocumentList.createdDT AS 'Document Date',docTypeDescription AS 'Document Name',visitdateFrom dbo.DocumentList Inner JOIN dbo.dsCHCChargesAndPayments ON dbo.dsCHCChargesAndPayments.[Patient: Patient ID]=patientIdWhere docTypeDescription IN('Client Intake Information Form')and visitdate > '2013-01-01 00:00:00.000'and createdDT >'2013-01-01 00:00:00.000'Thanks any help is appreciated!

latch

Posted: 16 Jul 2013 01:02 AM PDT

can i know the difference b/w latch vs lock ?

A produce that reports versions, editions and product name of SQL Server installs

Posted: 16 Jul 2013 03:31 AM PDT

Hello,I am looking for a product that reports versions, editions and product names of SQL Server installs and instances in my network. Is there something out there on the market? Any free tools out there or very inexpensive?Thanks.

Query The start up type of a service

Posted: 16 Jul 2013 01:53 AM PDT

I am just putting together a list of security checks which will eventually have a front end to them for audit purposes.Does anybody know a script which will return the start up types of the services? SQL, Agent, Browser? I want to ensure that SQL is automatic and browser is disabled on each instance.Any help is much appreciated

How to measure the SQL Server MTL Memory Usage.

Posted: 16 Jul 2013 04:12 AM PDT

Hi ,Having issues with the following scenario, pls help if anyone can Measure the MTL Usage.[b][u]DB Environment Details[/u][/b]Windows/SQL Server 2008 R2 with 64 BitSQL Max Memory 12 GB ( BPool Area )3 GB for MTL ( Non-Bpool )Max Worker Thread 512[b][u]Application Environment Details[/u][/b]Solaris Server with 64 Bit 32 GB RAM4 Instance Application with 20 SQL Connection eachApplication use the ODBC Drivers for connecting SQL Database• Application getting restarting when allowing with default memory setting due to insufficient memory• Application working fine when default MTL setting changed to 3GB using –g switch during SQL Server Startup.In the test lab server, we did not have any issues after allocating the Custom Memory while startup using –g ( 3GB Size )By using below Query, MTL not crossing more than 100 MBselect type, sum(single_pages_kb) BPool, sum(multi_pages_kb) MTL from sys.dm_os_memory_clerkswhere multi_pages_kb > 0 group by type order by 3 descQuestions: • Why the application restarting at the time of default Memory setting, I hope by default It suppose to have atleast 1 GB and my application not even consuming ¼ of the 1 GB but still application restarting.• Similarly, why the application not restarting when memory increased for the MTL Portion to the higher value.• Which query will help me for measuring the MTL Usage for the allocated 3 GB. Output like,Allocated MTL Area : 3 GBConsumed MTL : 250 MBFree MTL Space : 2822 MBBecause, based on the SQL Script I will scale up the Memory usage by the C/C++ Applications.For easy understanding I have attached the App and DB Layer. Please share your thoughts for measuring the memory usage.

reasons

Posted: 16 Jul 2013 12:07 AM PDT

what are the possible reasons when a query is timed out ?what are the possible reasons for not truncating the log file ?i have Log file of 5 GB how many VLF"s will be present ?

Need to restore one file into a database

Posted: 16 Jul 2013 03:13 AM PDT

Hi all,First of all, this is development server. We have a database of a size of over 1 TB which have partitioned tables and it was restored several months ago.Now, if I select from any table which are partitioned, it gives an error message recommending to run DBCC checkdb, and points to particular file, which I guess may be corrupted. For now I am running DBCC checktable against table in question. But my question is not about this.I have fresh production backup. Can I restore just one file into our database? If it is possible, how should I do this restore? Thanks.

SSMS can't connect to SQL Server, no error, no end.

Posted: 16 Jul 2013 02:46 AM PDT

Hi All,I really need help on this. One QA's computer can't connect to any SQL Server instances using SSMS. When I connect using SQL login or windows login, it just runs forever, no errors show up, just keep on running.Except this computer, other computer in the same domain have no proplem to connect to the servers.The firewall are turned off on this computer. The other applications except SSMS and OSQL can connect using both SQL and Windows login.Anybody can shed a light?Thanks,

LS

Posted: 16 Jul 2013 01:12 AM PDT

i added one secondary database file on the primarydatabase where the db is configured in logshipping on E drive , how can i restore the file to the secondary server where E drive is not present ? what happens my logshipping

service accounts

Posted: 16 Jul 2013 01:31 AM PDT

what are the difference between configuring the accounts as network and local accounts while installing sql ?

service pack on cluster

Posted: 15 Jul 2013 08:55 PM PDT

I have 2node cluster and when we are applying the service pack on passive node suddenly active cluster was down and it failed over to passive.Will SQL instance failover?If it is Can you please tell me will that service pack applied to my sql instance?Please tell me the best way to ptach on sql cluster?Thanks,Santosh.

Does compressed data stay compressed in transactional replication SQL 2008 R2

Posted: 15 Jul 2013 10:58 PM PDT

Does compressed data stay compressed in transactional replication SQL 2008 R2?We are planning on using transactional replication to replicate SQL 2008 R2 (sp2) compressed data to another SQL 2008 R2 (sp2) sql server.Are there any settings I need to make on the published article to retain the compressed data?

Obtaining SSMS errors when a SSRS report returns an error, without logging on to SSMS each time.

Posted: 15 Jul 2013 11:33 PM PDT

Hello people,I have a problem in that if some SSRS report runs a procedure which returns an error, the message displayed at the SSRS end is very inadequate. For various reasons, we have been advised not to turn on remote errors to display the SSMS error message which we need to see in order to diagnose the problem.Therefore, I wonder whether there is any way to make a query that will return the procedure error message returned, given some information about the user / procedure / report or whatever (we normally get told about errors pretty soon after they happen, so we may be able to just order the error messages by time, if only we can find the error messages).We cannot simply log into our live environment for every error to look at the error in SSMS. Unfortunately there are a lot of procedures already existing and we would rather avoid using CATCH commands to capture errors on every single one, which would be a large amount of work.I have had a bit of a fumble around the trace logs and things, but have not found the error messages that will be helpful... any ideas - are the error messages stored somewhere?Example:I make a report based on this procedure:[code="sql"]ALTER PROCEDURE [dbo].[RETURN_AN_ERROR]ASCREATE TABLE #TestMeUp( Id INT ,Value VARCHAR(10)) INSERT INTO #TestMeUp SELECT 1,'this' UNION SELECT 2,'that' UNION SELECT 3,'the' UNION SELECT 4,'other' UNION SELECT 5,'and' UNION SELECT 6,'look' UNION SELECT 7,'another' UNION SELECT 1,'one'SELECT Id ,(SELECT B.Value FROM #TestMeUp AS B WHERE B.Id = A.Id) AS ValueFROM #TestMeUp AS ADROP TABLE #TestMeUp[/code]the report gives this unhelpful error:[quote]An error has occurred during report processing. (rsProcessingAborted)Query execution failed for dataset 'Error'. (rsErrorExecutingCommand)For more information about this error navigate to the report server on the local server machine, or enable remote errors [/quote]whereas SSMS gives this much more useful error message (which we would like to see from outside of SSMS):[quote]8 row(s) affected)Msg 512, Level 16, State 1, Procedure RETURN_AN_ERROR, Line 26Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.[/quote]Thanks for looking!Mark

dynamic index rebuild based on fragmentation level

Posted: 15 Jul 2013 09:00 PM PDT

Hi Guys,Anyone have a dynamic script which will list and rebuild indexes > 30% avg fragmentation.Seen it somewhere but can't recall....thanks

Scan count vs logical reads

Posted: 15 Jul 2013 10:25 PM PDT

I have two versions of a query.1. Scan count 136, logical reads 2776907,CPU time = 230848 ms, elapsed time = 55753 ms.2. Scan count 0, logical reads 89225788, CPU time = 437696 ms, elapsed time = 46195 ms.The only difference I can see is 2 query it taking lesser of 10 minutes. Which is good here? Both Execution plan has lots of hash joins. Thanks

Foregin Key

Posted: 15 Jul 2013 11:28 PM PDT

Hi,If I disable a Fk and then renable it with Check Check; will this lock the table out while the data is being validated please?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog