Tuesday, August 13, 2013

[SQL Server 2008 issues] DatabaseOptimization - job failed

[SQL Server 2008 issues] DatabaseOptimization - job failed

DatabaseOptimization - job failed

Posted: 11 Aug 2013 08:00 PM PDT

My Optimization job failed with below error.Edition: Enterprise Edition (64-bit) Procedure: [master].[dbo].[IndexOptimize] Parameters: @Databases = 'USER_DATABASES', @FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE', @FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE', @FragmentationMedium_LOB = 'INDEX_REORGANIZE', @FragmentationMedium_NonLOB = 'INDEX_REORGANIZE', @FragmentationLow_LOB = 'NOTHING', @FragmentationLow_NonLOB = 'NOTHING', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @LOBCompaction = 'Y', @StatisticsSample = NULLDateTime: 2013-08-11 01:00:00 Database: [allmed] Status: ONLINE Updateability: READ_WRITEDateTime: 2013-08-11 01:01:15 Database: [archsys] Status: ONLINE Updateability: READ_WRITEDateTime: 2013-08-11 01:01:48 Database: [cs3live] Status: ONLINE Updateability: READ_WRITEDateTime: 2013-08-11 01:02:10 Command: ALTER INDEX [i_1985510508x0] ON [cs3live].[scheme].[slxrefm] REORGANIZE WITH (LOB_COMPACTION = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 2763, Fragmentation: 5.13934Outcome: Succeeded Duration: 00:00:02 DateTime: 2013-08-11 01:02:12DateTime: 2013-08-11 01:04:38 Command: ALTER INDEX [i_1764282674x2] ON [cs3live].[scheme].[opheadm] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 3282, Fragmentation: 92.8702Msg 50000, Level 16, State 1, Server UKDXSDBCVTC016, Procedure CommandExecute, Line 95 Msg 1222, Lock request time out period exceeded.Outcome: Failed Duration: 01:00:12 DateTime: 2013-08-11 02:04:50DateTime: 2013-08-11 02:04:55 Command: ALTER INDEX [i_1764282674x4] ON [cs3live].[scheme].[opheadm] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 3238, Fragmentation: 92.0012Msg 50000, Level 16, State 1, Server UKDXSDBCVTC016, Procedure CommandExecute, Line 95 Msg 1222, Lock request time out period exceeded.Outcome: Failed Duration: 01:00:05 DateTime: 2013-08-11 03:05:00DateTime: 2013-08-11 03:05:01 Command: ALTER INDEX [i_1764282674x6] ON [cs3live].[scheme].[opheadm] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 3928, Fragmentation: 62.4745Msg 50000, Level 16, State 1, Server UKDXSDBCVTC016, Procedure CommandExecute, Line 95 Msg 1222, Lock request time out period exceeded.Outcome: Failed Duration: 01:00:20 DateTime: 2013-08-11 04:05:21DateTime: 2013-08-11 04:05:21 Command: ALTER INDEX [i_1764282674x8] ON [cs3live].[scheme].[opheadm] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 1923, Fragmentation: 85.5434Msg 50000, Level 16, State 1, Server UKDXSDBCVTC016, Procedure CommandExecute, Line 95 Msg 1222, Lock request time out period exceeded.Outcome: Failed Duration: 01:00:03 DateTime: 2013-08-11 05:05:24DateTime: 2013-08-11 05:05:25 Command: ALTER INDEX [i_1764282674x12] ON [cs3live].[scheme].[opheadm] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 2494, Fragmentation: 57.5782Msg 50000, Level 16, State 1, Server UKDXSDBCVTC016, Procedure CommandExecute, Line 95 Msg 1222, Lock request time out period exceeded.Outcome: Failed Duration: 01:00:04 DateTime: 2013-08-11 06:05:29DateTime: 2013-08-11 06:05:29 Command: ALTER INDEX [i_6928d132002dd31e] ON [cs3live].[scheme].[opheadm] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 6266, Fragmentation: 43.2333Msg 50000, Level 16, State 1, Server UKDXSDBCVTC016, Procedure CommandExecute, Line 95 Msg 1222, Lock request time out period exceeded.Outcome: Failed Duration: 01:00:04 DateTime: 2013-08... Process Exit Code 1. The step failed.Please help me to rectify it.advance thanks

Update the column with names

Posted: 12 Aug 2013 05:14 PM PDT

I have a table with column and data is [u]blp_proposalno blp_documentattach blp_fund blp_branch[/u]0002755746 201,202,203,204,211 116 KA010002997284 201,202,203,204,214,211 116 KA010003234742 201,202,203,204,211 116 KA010004046205 201,202,203,204,211 116 JH730003659836 201,202,203,204,211 116 JH730004029283 201,202,203,204,211 116 JH730002975418 201,202,203,204,214 116 AL1390003176467 201,202,203,204,211 116 AL170003365092 201,202,203,204 116 BT010003450570 201,202,203,204 116 AS640004056265 201,202,203,204,214 116 JM420007359820 NULL 116 JA070007415964 NULL 116 NO130007244229 NULL 116 AL280007490863 NULL 116 MO06And the other table with data is [b][u]BLD_Fund BLD_DocumentID BLD_Document[/u][/b]116 201 Benefit Illustration116 202 Age proof116 203 Address Proof116 204 Proof of Identity116 205 Income proofs116 206 Risk Appetite awareness form116 207 ECS Mandate116 208 CC Mandate116 200 Application Form116 209 Employee Documents116 210 Occupation Related Documents116 211 Cash Authority Documents116 215 Single Name Documentation116 212 NRI Documents116 213 PEP Documents116 214 Female Life Guidelinesso i need the first table column update like the given below..[b][u]bld_Document attach[/u][/b]Benefit Illustration,Age proof,Address Proof,Proof of Identity,Cash Authority DocumentsBenefit Illustration,Age proof,Address Proof,Proof of Identity,Female Life Guidelines,Cash Authority Documentsand so on....Help me...

Replace NUll values with Space in SSIS 20008

Posted: 12 Aug 2013 07:29 AM PDT

HI, I am dumping data from source to destination but ..problem is some values in source table have null values but in the destination table, it does'nt allow null values. So for those values, i need to put space for those values.. Any idea how to do it and where to put it.regardsshaun...

Can a table have two primary keys?

Posted: 06 Aug 2012 07:47 PM PDT

Can a table have two primary keys

Tool to search, list out distinct .sql files

Posted: 06 Aug 2013 05:31 AM PDT

Is there any tool which can scan through each script and spit out list of scripts which are same?

GETDATE() returns null in production

Posted: 12 Aug 2013 01:43 AM PDT

I have an Access application as a front-end to an SQL Server 2008 backend. There is one field (dtDateEmission) on one table that has a default value of (getdate()). This is on the SQL Server side when I look at the table's properties. When I manually enter a table directly using SSMS the default works and the field gets the current data and time.In production, the Access application inserts a record in this table by calling a stored procedure. The stored procedure does not reference the field dtDateEmission, only other fields are given values and the record gets added to the table. Yet, when this is happenning, the dtDateEmission field is given a value of NULL. I traced the execution using SQL Profiler and there is nowhere in the application that assigns a null value to this field. Yet when the user clicks the button in the app to enter a new record, the field gets a NULL value, even with a default of GETDATE() at the table level.I copied the database over to our development environment, and I ran the application myself. With me, the field gets the current date and time and everything works. But in production, the user ends up saving a NULL value for this field.Very strange!Can anyone explain this or has something like this happen to them?Thanks!

Excel Source unable to read alphanumeric values.

Posted: 12 Aug 2013 07:19 AM PDT

I'm creating a simple source--Destination Excel load from a an excel file to SQL server Table using SSISOne of the column getting nulls where the data is alphanumeric. Example:Source DestinationCol A -----> Col A nvarchar(255)12321 ------> 123211D2DE34 -----> NULL -- Here is the issue I have tried loading it to a flat file destination and the it is sending null values where there is alphanumeric, from this I understood there is no issue on SQL server side but the excel source was not reading the alpha numeric values from the Excel. The excel column format is TEXTDo anyone have seen this issue before, ? Please advise.

Replication DB migration on new sql server 2008R2

Posted: 08 Aug 2013 07:49 AM PDT

Hello,We will be planning to migrate sql 2005 and sql 2008R2 on new windows 2008R2 server, we have one Replicated DB which ones we are subscribing and we don't have any control over publisher.Could you please guide me that how can we handle it?We are planning to do side by side installation and will be using Backup and Restore method but some worries how we can handle it for Replication DB and which is the biggest DB almost more then 300GB.Thanks,

Merge Replication merging thousands of records each time

Posted: 12 Aug 2013 09:31 AM PDT

I have SQL 2008 Standard on two servers. Testing merge replication between the servers on two tables. 1. Both tables have about 500k records. 2. These servers are not in use currently, just being tested for replication, so there is very little if any changes to the data.3. Set up and initialization seem fine. 4. The databases were identical when we started. Tried several methods, attach/detach, backup, etc...5. When the Merge Agent runs the first time, there are 150,000 updated records. Next time, 42,810 records updated and 42,800 conflicts. Third time, is running now. Looks like 200k plus updates and conflicts. Taking about 90 minutes.I first noticed this because the log file was getting huge when i had the timing on continuous. Now I have it on manual so i can track what is happening.Any ideas what may be happening ?Thanks in advance.

Indexing a view

Posted: 12 Aug 2013 05:20 AM PDT

I'm using a SQL view to retrieve data from 8 different tables without having to do 8 select statements.This has sped my project up quite a bit over the 8 selects.But I still have some users complaining that it is slow.Is it possible to index a view to improve its performance?

SSRS Only Returning First Row

Posted: 12 Aug 2013 04:24 AM PDT

Hi all,I am trying to get SSRS to display unique data for a unique ID. It's fairly straightforward, and I've done it before. But for some reason now it doesn't work. For example, I have an id which the user can choose, and a textbox that displays the name for that id. It should change with each different id, as should the other columns in order to display customized data, but they don't -the data matches the data returned from the first row and first row only. Any help or advice?

Intermittent pre-login handshake error

Posted: 05 Aug 2013 03:24 PM PDT

Environment:SQL 2008 R2 ClusterRunning fine for over 6 monthsEvery once in a while I get some time-outs from our web apps with a SQLAsync Exception:Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=15003; Anyone get this before? Again the server is listening on port 1433 and the connection strings are using TCP\IP. This is a intermittent error. I remember something like this that had to do with multiple instances and Named Pipes and TCP or the client is trying to connect via TCP\IP v6 first then 4 but I have checked all that and it seems fine.thanks in advance

SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR

Posted: 04 Aug 2013 12:26 PM PDT

If I set the SSIS package to ignore the error this column pulls in null values instead of real values. If it's not set to ignore the error this is the error message. The data type is INT. It's failing in the ADO NET Source task. Any ideas? [ADO NET Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "ADO NET Source" (1)" failed because error code 0x80131937 occurred, and the error row disposition on "output column "XYZ" (749)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

password

Posted: 12 Aug 2013 01:35 AM PDT

How to the retrieve the password for SQL login, if forgot the password?

Managing duplicated code?

Posted: 12 Aug 2013 03:01 AM PDT

I have some jobs running a few scripts that populate several reports. I've run these reports reports two ways:1. Each report has their own procedure (around 25 procedures).2. All of the reports share a few procedures (around 5 or 6).I'm actually finding it optimal to run them from their own procedure. (This particular project will not get any bigger). However, in many of the same places the code is basically the same. Is there a good (or easy) way to keep up with the general source code (I'm not sure if that is the right term). Basically, I'm hoping for an easier way to manage updates to the general code that is duplicated across the procedures. Any help is appreciated.

using in with SP

Posted: 11 Aug 2013 09:59 PM PDT

i have a SP which has a param id as intand the where claus ein SP had where id=@IDbut now instead of a single vlaue i get id as multiple valuesso i need IN operatorredefined ID as varchari used where id IN (@ID) and when executing SP as EXEC usp_itemdeatils '45,72' i get error Conversion failed when converting the varchar value '45,72' to data type int.

Port numbers 1433 and 1434

Posted: 07 Aug 2012 04:05 AM PDT

Hi DBA'swhy there are two ports 1433 and 1434 for SQL Server.whts the purpose of two?how are they different?I came to know 1433 is for TCP\IP1434 for UDP what does this mean ?Thanks in advance!

Cross Tab or Pivot - Need Help

Posted: 12 Aug 2013 01:06 AM PDT

I have the following tables.TabParent========id item1 item2-- ------ ------AA 2 2TabChild======id f1 f2 f3 f4 f5-- -- -- -- -- --AA 1 2 3 4 1AA 1 1 1 2 2AA 1 1 1 3 3Now what I want is (i.e each row from child becomes appended as column sets in single row)-----------------------------------------------------------------------------------------id item1 item2 f11 f12 f13 f14 f15 f21 f22 f23 f24 f25 f31 f32 f33 f34 f35-- ------ ------ --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---AA 2 2 1 2 3 4 1 1 1 2 2 2 1 1 1 3 3Please let me know,1. Is it possible to do?2. If yes how a static as well as dynamic (if no of rows in child table for a key increases) solutions could be implemented through T-SQL code?Thanks and Advance

How to Compress files in a folder

Posted: 12 Aug 2013 12:05 AM PDT

I have a folder in D:\Payslip with a set of files.I have a table which stores the file name. Based on stored procedure inputs i need to create a zip file with the file names based on table in the same folder. Please suggest which command can be used without using tools like winrar, 7zip, winzip

Null Value column names

Posted: 11 Aug 2013 07:53 PM PDT

hi Team,Am having a table with 100 columns, and 500 records,in that some records values are NULL, i want to find out how many column are there with NULL records.i want all NULL value column names.eg :col1 col2 col3a NULL cityb YOK BHKc LOK BTLOutputcol2, col1, and col3 contains no NULL values, only col2 contains NULL, hence i want col2 as output. Please help...

Replication topology question

Posted: 11 Aug 2013 10:23 PM PDT

HiI'm planning to add a couple of new SQL 2012 subscribers to a transactional replication setup that has a 2008R2 publisher/distributor and several subscribers that include two SQL 2000 machines. According to [url=http://msdn.microsoft.com/en-us/library/ms143241%28v=sql.105%29.aspx]this link[/url], that should work as[quote]For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version.[/quote]However, I was just wondering if anyone had actually tried having 2000 and 2012 subscribers in the same replication topology?Duncan

SP Output Parameter Issue (across ODBC Connection)

Posted: 11 Aug 2013 08:04 PM PDT

Hello,I am having an issue retrieving a SP's Output parameters over an ODBC connection (Driver: SQL Server Native Client 10).When the SP is not attempting any inserts/deletions or updates, the Output params are returned across the ODBC connection fine. However, as soon as I add in a simple update statement (updating one field in one record), it is suddenly unable to retrieve the Output params.I have tried this on other SPs (with different update statements) and the same is happening? [u]HOWEVER[/u], when I capture the ODBC call to the SP in 'SQL Server Profiler' and run in the Management Studio then it returns the Output params fine which is why I am thinking it is an ODBC issue/restriction? All accounts used are SysAdmin so cannot see any permission issues? I have also removed all triggers on the tables being updated incase these were interfering at all?Will paste the SP script/call if required, but any pointers first would be greatly appreciated, :-)

SQLServer Error: 15404

Posted: 06 Aug 2013 07:34 PM PDT

It appears to me that SQL Server do not support hostname over 8 characters. When I enter my hostname to schedule an SQL maintenance plan and save it, it will automatically rename my account from: SOLOMON2009\Administrator to SOLOMON2\Administrator. Backup will fail with the below error log:[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'SOLOMON2\Administrator', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

Sql cluster8 r2 with out msdtc

Posted: 06 Aug 2013 10:07 PM PDT

One of our client want dba to install sql cluster 2008 r2 on single cluster node.(maybe in the future a second node will be add to the cluster).Can any one clarify the below things.IS it possible to configure msdtc with out secodary node.? Is it possible to install sql cluster on 2008R2 on sinlge node with out configuring MSDTC? orcan we create MSDTC after adding the 2nd node to the existing single node cluster?Advance thanks,
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog