Wednesday, July 3, 2013

[SQL Server 2008 issues] Logshipping Alerts

[SQL Server 2008 issues] Logshipping Alerts

Logshipping Alerts

Posted: 02 Jul 2013 05:43 PM PDT

Hi all,I am getting this message in error log frequently. Actually there is no database with name test in our environment.The log shipping primary database QBSECPU01.Test has backup threshold of 20 minutes and has not performed a backup log operation for 70399 minutes. Check agent log and log shipping monitor information.How to suppress or workaround for this issue.

Restoring failed

Posted: 02 Jul 2013 04:43 PM PDT

Hi all,i am trying to restore a database to point in time in sql server 2012. so i want to test that one with a sample database... i have taken fullbackup ..and then entered some data..then taken differential backup and entered some data and taken log backup... when i am trying to restore, the full backup was successfully restored. when i am trying the differential backup it is saying unable to create restore plan due to break in LSN chain. i am not able to find what wrong i have done...

CPU Flat lines and numerous Time Outs

Posted: 02 Jul 2013 01:55 AM PDT

Sorry for the long post... but here goes:We have recently migrated from a physical 2 node cluster environment to VM Ware.Ever since that day we have an increased number of time outs being reported from the client application (almost 300 per day!) and almost weekly the CPU is flat lining and keeps flat lining for hours…When it flat lines it shows SQL Server using all 100% of the CPU resources.We have made no major changes in SQL procs or queries since the migration and we never had these problems before the migration (when it was on the physical cluster).Here is the spec of the VM host environment:Production3x BL460cG6 Blades with 2x Hex core 2.9GHz CPUs (12 CPU per hots) and 96GB RAM2x BL460cG6 Blades with 2x Quad Core 2.5GHz CPUs (8 CPU per host) and 96GB RAM2x BL490cG6 Blades with 2x Hex Core 2.9Ghz CPUs (12 CPU per Host) and 96GB RAM - Still to be added (Old SQL Cluster)DR3x BL460cG6 Blades with 2x Hex core 2.9GHz CPUs (12 CPU per hots) and 96GB RAM2x BL460cG6 Blades with 2x Quad Core 2.5GHz CPUs (8 CPU per host) and 96GB RAM2x BL490cG6 Blades with 2x Quad Core 2.5GHz CPUs (8 CPU per host) and 96GB RAMNetwork Uplinks from VM to Core 3x 10GB4x 1GBFibre Uplinks to SAN6x 8GBThe sysadmin is not really a VM admin and so cannot assist me much…Any ideas why our server is flat lining and why we are getting so many time outs all of a sudden?One very strange thing I did see was this:The current MaxDop setting is 8. When the CPU flat lines, I change the MaxDop setting to something different like 4. There is no change and the CPU still flat lines. When I change it back to MaxDop of 8, the CPU recovers and runs on average 70% - I have done this 3 different times - just to check it was not a fluke….same result each timeHow can this be?How do I troubleshoot this?

summary

Posted: 01 Jul 2013 10:54 PM PDT

alter procedure [dbo].[pocc](@empid nvarchar(10),@department varchar(10))asbegincreate TABLE #TempEmployees(date datetime,eid int,remarks varchar(50))-- Insert result from the SP to temp tableINSERT INTO #TempEmployeesEXEC dbo.at @empid,@department--Verify the Insert recordsSELECT *FROM #TempEmployees where eid=@empidendexec [pocc] 17074,''when i exceute its giving me that resultdate----------------------------eid--------remarks2013-01-06 00:00:00.000-------17074---OFF DAY 2013-01-07 00:00:00.000-------17074---ABSENT 2013-01-07 00:00:00.000-------17074----Late2013-01-08 00:00:00.000-------17074----HALFDAYhow i make a summary result like this-----eid---offday-----absent-----late---------halfday--------17074--1----------1--------1----------1------

Problem in executing bunch of Insert statements

Posted: 02 Jul 2013 04:33 AM PDT

Hello friends,I am executing bunch of Insert satements as follows:[b]use TestinggoINSERT INTO Table_Update(TableName,StartlastWritten) SELECT 'Students'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten FROM [10.198.68.39]..dbo.Students;.....INSERT INTO Table_Update(TableName,StartlastWritten) SELECT 'teachers'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten FROM [10.198.68.39]..dbo.teachers;[/b] I have 1450 insert statments, which I am running in SQL SERVER to insert the data from "Linked Server" to "SQL Server".[b]when I run them in one go ..it give me few successfully inserted records. around 250 out of 1450 and following error[/b][b]The OLE DB provider "OraOLEDB.Oracle" for linked server "10.198.68.39" supplied inconsistent metadata for a column. The column "NOTESTEXT" (compile-time ordinal 3) of object ""dbo"."Notes"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.[/b]But, When I run them individually or in samll bunch around 23-30 at a time it success fully executes.I don't know how to get rid of this, because, every time it is very costly to run them individually or in small bunch.please help.Thanks.

Using snapshots for reporting on historical data?

Posted: 02 Jul 2013 12:58 PM PDT

I've been using timestamps and status dates on tables to do historical reporting (e.g. today last year, quarter over quarter, etc). But I've been thinking that using snapshots might simplify life a little. Are snapshots generally the best practice for historical reporting? I really on have 15 or so tables (out of the hundreds) that I query a bunch for history, but I read snapshots run against the entire instance. So I'm just looking for ideas and general advice. As always, thanks a bunch for any help.

help with sp_MsForEachDb

Posted: 02 Jul 2013 10:20 AM PDT

Hi everyone. I have created a table using the following code in sql2008:[code="sql"]USE Dba_AdminGOCREATE TABLE dbo.databaseDataFileSize ( rowId INT IDENTITY(1,1), dbName sysname, dbSize BIGINT, sampleDate DATE, sampleTime TIME)GO[/code]I am trying to alter a script to run which should populate the table using the Sp_MsforeachDb. I am running the follwoing code to try this :[code="sql"]EXEC master.sys.sp_MSforeachdb INSERT INTO Dba_admin.dbo.databaseDataFileSize SELECT ''?'', SUM(size) * 8, cast(getdate() as date), cast(getdate() as time) FROM [?].sys.database_filesWHERE type = 0;[/code]but i am getting the following error:[quote]Msg 102, Level 15, State 1, Line 3Incorrect syntax near '?'.[/quote]Can anybody help me modify this so it itterates through each database and populates the table so i can keep track of my datafile sizes ?Thanks

How to Check Change in the Status

Posted: 02 Jul 2013 06:34 AM PDT

I have a table for instructors which contains subjects, date and time and Status information about their classes.Once they register on the website they get the pending status, and somebody from administration either Approve, deny or Cancel their class.so the status changes from Pending to either Approv, deny Or cancel.I want to write a query that to piush the information to instructors if their Status gets changed from Pending to Approve, Deny Or CancelHow do I do that.Thaks,Blyzzard

Unable to use a variable in the @body parameter of sp_send_dbmail

Posted: 02 Jul 2013 06:51 AM PDT

I am trying to build a variable to be used with the @body parameter of sp_send_dbmail. Here is the code I am trying to run: DECLARE @v_body varchar(MAX) DECLARE @v_business_name varchar(75) = 'Hollies Gift Shop' DECLARE @v_owner_first_name varchar(30) = 'Bernice' DECLARE @v_owner_last_name varchar(30) = 'Goodly' DECLARE @v_profile varchar(30) = 'testprofile'set @v_body = 'Business Name: ' + @v_business_name + char(13) +char(10) + 'First Name : ' + @v_owner_first_name + char(13) +char(10) + 'Last Name : ' + @v_owner_first_nameUSE msdbGOEXEC sp_send_dbmail @profile_name = 'testprofile',@recipients = 'example@yahoo.com',@subject = 'Test message',@body = @v_body I receive the following error: Must declare the scalar variable "@v_body".What is it that I'm doing wrong

table creation on one table referenced by many tables

Posted: 02 Jul 2013 07:15 AM PDT

hiHere a requirement that I am trying to implementOne table Address is used by many tables. like member, provider and sales representative.While creating Sales Order, User would entry information for member, provider and sales representative.and address for each entitymember, provider, sales representative tables has reference to sales order tableWhich is best way to create tables1. member, provider and sales representative have FK to Address table2. Address table has 3 columns for member, provider and sales representative.3. Instead of address table, add columns to each tablemember, provider and sales representative to address is one-one.

Calculating date in time.

Posted: 02 Jul 2013 06:56 AM PDT

How I create a function to get all policies that expire a 120 days from now.Eg If the policy effectivedate is November 1 2012 and expiration date is November 1 2013 then on july first when I run a query this policy should be selected.

Connection encryption between SQL Server and BackupExec

Posted: 02 Jul 2013 07:38 AM PDT

We are in the mists of securing all internal communications via encryption, a security requirement by law for us...We are using SQL Server encryption from the server to the clients for all sensitive databases, but we have no turned on force encryption yet due to not knowing how some programs might act.One such program is Symantec's BackupExec. We are using BE 2012 and the SQL Server backup agent to backup our database servers. We have contacted Symantec about this and they don't seem to have a clue what we are asking. They keep telling us the agent is encrypted if you have hardware encryption turned on the tape... well that's not what we need to know... we wanted to know if you force encryption at the database server connection level how does the agent react...We need to make sure we are not leaving an unencrypted path between the server and the backup agent. We know from the agent to the media server are encrypted via certificate / key exchanges. We know the media server to the tape are encrypted via a key we provided. We just don't know how or if the data from the server to the agent are encrypted...Anyone have any experience with this or any knowledge of how BackupExec handles a require encrypted connection setting?

How to get total of months, like Year to Day in cross tab query?

Posted: 02 Jul 2013 01:21 AM PDT

I used code below to create cross tab query. It works fine.How to add code to get total of months, like Year to Day?SELECT *FROM (SELECT year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], InvoiceAmount as Amount FROM Invoice) as sPIVOT(SUM(Amount)FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec))AS pivot

Restore Database using backup file

Posted: 02 Jul 2013 03:40 AM PDT

I need help in using the backup file. I am trying to restore the sql database and I want to avoide the date and use astrick so how would I do that?example:MyDb_backup_2013_06_30_231639_7324894.bakWant to use like: MyDb_backup*.bakCan you please help

SQL 2008 - Litespeed backup fails

Posted: 02 Jul 2013 02:14 AM PDT

Out Litespeed backup was running without any prob. Aftre sometime I changed the SQL Server name and that started all the issues. All backups jobs are failing now. Error: Unable to connect 'Local Server Connection'Since the maintenance plan is an ssis package, the 'data source' tab in job step shows a row Local Server Connection with the old server name and other values. How to fix this without creating a new maintenance plan all over again

Help on blocking in tempdb

Posted: 02 Jul 2013 02:08 AM PDT

On one of our 2008 R2 SP2 sql server we are experiencing blocking in tempdb. The blocked requests are Intellisense and Data Collection queries. We have found some stored procedures that create but do not drop temp tables, their connection starts when application begins and ends when application is terminated. We are having these stored procedures changed. Just wondering if anyone else has experienced this. Server has 16 Processors, 65 gb and tempdb has 4 data files of equal size.

Effect of Clustered Index on Non-Clustered Index

Posted: 02 Jul 2013 01:50 AM PDT

Not remotely a DBA so forgive me if this is a novice question, but will having a clustered index in a database improve the performance of non-clustered indexes?Sean

tempdb keeps growing

Posted: 02 Jun 2013 09:08 PM PDT

I have configured my tempdb as follows:8 data files with an initial size of 14GB each. This was based on 2 weeks of monitoring the DB and seeing to what size it grows.I have set the autogrow to 10%.The thing is, the data files have now grown to 22 GB each - making the overall size just over 183 GB. The tempdb is on its own drive and the capacity of this drive is 200 GB.I am afraid that I might be running out of space....I know restarting the SQL service will shrink the files back to its initial size of 14 GB each, but this is a production server and a restart is not possible.Any possible solutions?Will the tempdb not release unused space?

Space Consumed by table

Posted: 02 Jul 2013 12:57 AM PDT

Hi,A table has two VARCHAR(100) column. Number of Records 2100000, Disk Space Consumed 222MB. It is having Varchar data of length 80.I have updated these two column with number 15.So before Update- 80 character data in both columnafter Update- 2 character data in both columnAfter Update Can i aspect less disk consumption by the table.Thanks,Anupam

SqlServer 2008 Processor Affinity

Posted: 02 Jul 2013 12:51 AM PDT

Hello All ... I am looking for some advice on CPU affinity settings in SqlServer 2008. I have a 2 node 3 instance cluster with 16 cpu's and 104 GB's of ram. The OS is 2008 Enterprise.The current setup is:Instance 1 Processor: 0-7IO : 8-15Instance 2 Processor: 8-15IO : 0-7Instance 3Processor: 0-3,8-11IO : 4-7,12-15Currently Instance 1 and Instance 3 are sharing a cluster node. I do see some spikes in CPU activity but those are generally focused during maintenance tasks, so that can be expected. Does anyone know of any best practices or any other recommendations?

SQL 2008 express installation using command prompt

Posted: 01 Jul 2013 11:40 PM PDT

HiI am trying to install sql server 2008 express using command prompt from my .net applicationThis is the command[code="sql"]Arguments='/q /hideconsole /action=Install/TCPENABLED=1/NPENABLED=1/SECURITYMODE=SQL/features=SQL,Tool/SAPWD ="Mydwp2008#"s/instancename=SQLEXPRESS/enableranu=1/sqlsvcaccount="NT Authority\Network Service"/AddCurrentUserAsSqlAdmin/skiprules=RebootRequiredCheck/IAcceptSQLServerLicenseTerms'[/code]installion is working fine..but the authentication mode is changed to WINDOWS authentication mode even though i am choosing mixed mode.How to solve this issue

Log on Index Creation?

Posted: 01 Jul 2013 11:52 PM PDT

I am trying to determine when some indexes were added to a table. Is that logged anywhere? Thank you.

The system has rebooted from a Automatic Server Recovery (ASR) event.

Posted: 01 Jul 2013 11:44 PM PDT

We are facing a frequent restart of our production server once in a month or so. The event log is as below..[i]User ActionDetermine the nature of the Automatic Server Recovery (ASR) event, and take corrective action. WBEM Indication PropertiesAlertingElementFormat: 1 0x1 (Other)AlertType: 5 0x5 (Device Alert)BladeBay: "11"BladeName: "DBServer.histogenetics.com"Description: "The system has rebooted from a Automatic Server Recovery (ASR) event."EnclosureName: "OB-005DD323242"EventCategory: 16 0x10 (System Power)EventID: "1"ImpactedDomain: 3 0x3 (Enclosure)IndicationIdentifier: "{B211AC7F-9D6F-435C-825E-560CB382D0CF}"IndicationTime: "20130701224253.923000-240"NetworkAddresses[0]: "fe80::a8d7:c0f3:ac5f:b15d%10"NetworkAddresses[1]: "xxx.xxx.xxx.xx"OSType: 103 0x67 (Microsoft Windows Server 2008 R2)OSVersion: "6.1.7601"PerceivedSeverity: 5 0x5 (Major)ProbableCause: 111 0x6f (Timeout)ProbableCauseDescription: "ASR Reboot Occurred"ProviderName: "HP Recovery"ProviderVersion: "2.7.0.0"RackName: ""RackUID: ""RecommendedActions[0]: "Determine the nature of the Automatic Server Recovery (ASR) event, and take corrective action."Summary: "ASR reboot occurred"SystemCreationClassName: "HP_WinComputerSystem"SystemFirmwareVersion[0]: "2010.03.30"SystemFirmwareVersion[1]: "2010.03.30"SystemGUID: "33424235-3532-584D-51343-425235235"SystemModel: "ProLiant BL460c G6"SystemName: "DBServer.histogenetics.com"SystemProductID: "785725-K22"SystemSerialNumber: "XDfs898sd"TIME_CREATED: 130172065740131089 0x1ce76cdda1e8b11[/i]Did anybody faced a similar problem and get it solved..

Multiple SQL Standard Instances on 4 Processor/32-core Server

Posted: 01 Jul 2013 09:27 PM PDT

We have a large 4 processor/32-core server with 192GB of memory available in the data center and over twenty small SQL Standard databases to consolidate. They are a mix of SQL 2012 and 2008 R2 for 3rd-party apps.Is there any issue with simply installing two instances of SQL Standard on the server - one for 2012 and one for 2008 R2 ? Each instance will use up to 64GB out of the 192GB and 16 cores. If we did this with Enterprise, the licensing would be a fortune and the Enterprise features are not needed.

Linked server from SQL to Progress?

Posted: 01 Jul 2013 07:59 PM PDT

Hi, What driver do I need to connect SQL via a linked server to a Progress database? This is so we can export data from an application called CCure which monitors building door access. I downloaded the DataDirect driver pack from datadirect.com but none of them work as the SQL ODBC connection wants a numerical port number to connect to the CCure server but the Progress database on it uses (from what I can see) a port number of CFSRV. I have seen online that some people use an old Merant driver but when googling for that I always get pointed to DataDirect which looks like it has superseeded Merant now? Any ideas? Thanks.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog