Saturday, August 24, 2013

[SQL Server 2008 issues] Full and transaction log backup chain breakup

[SQL Server 2008 issues] Full and transaction log backup chain breakup

Full and transaction log backup chain breakup

Posted: 10 Aug 2013 06:27 AM PDT

Hi ,let us suppose i take full backup f1 and now suppose i take 3 transaction log backup t1,t2,t3 .Now i can restore this transaction log backup provided i had restored full backup f1. But suppose before T3 i take full backup F2 then i cannot restore T3 untill F2 is restored.But why this doesn`t happens in log shipping .I mean in log shipping even if we take full backups and if u have all transaction log backup`s provided there where no breakup`s in log backup`s chain, then to log shipping continues and it doesnt required to apply latest full backup on server(dr server)

Installing Cumulative update needs SQL Server Restart or Windows Restart?

Posted: 23 Aug 2013 02:44 PM PDT

Is it mandatory to restart windows server when updating cumulative update for SQL Server 2008 & 2005? I am going to install a cumulative update in a clustered server. Please let me know if its enough to failover the services or do I have to go for restart of a windows server?Thanks...

Product key

Posted: 23 Aug 2013 12:53 PM PDT

Hi Everyone,I know that this post is not related to this forum. But i have used SQL Server in my project.I have created an .net application. i need to deliver this product with an exe file. Before that, i need to set up a Product key for this product. Can any body tell how to set up a product key for an windows application.Please help me, if any one knows about this. Or suggest me any links or blogs to get this information.Once again sorry to ask this quest in this blog.Regards,S.Karthikeyan.

DBCC CheckDB ('MYDB') WITH NO_INFOMSGS, ALL_ERRORMSGS aborted

Posted: 23 Aug 2013 08:38 AM PDT

We lost our SAN today while EMC was configuring Recoverpoint to our DR location. SQL just dropped, OS lost the drives...everything went down. The tech Services guys got the SAN back online and were able to get all of the LUNs back but one...The DB's that came back up went into recovery with the message:Error: 824, Severity: 24, State: 2.SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:29898565; actual 0:0). It occurred during a read of page (1:29898565) in database ID 15 at offset 0x00003906e8a000 in file 'K:\MYDB.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.I tried:DBCC CheckDB ('MYDB') WITH NO_INFOMSGS, ALL_ERRORMSGSBut immediately get this error:Msg 7929, Level 16, State 1, Line 1Check statement aborted. Database contains deferred transactions.Any "expert" suggestions on getting these DB's back online without losing data (or than a complete restore of Full, Diff, and log shipped transaction logs?Please help!!!

Failback and Restore

Posted: 23 Aug 2013 09:40 AM PDT

We are setting up Database Mirroring in our lab without a Witness. We are able to get the principal and mirror servers synchronized. When we manually failover, through their UI, everything works smoothly. Failing back, again through the UI, works.When we simulate a complete Principal shutdown, our Mirror stays in the Disconnected/In Recovery state. In order to get it out of that state and usable we need to run the following two commands:[code="sql"]ALTER DATABASE <database_name>SET PARTNER OFFRESTORE DATABASE <database_name> WITH RECOVERY[/code]Now to restart mirroring we have to go through the whole process of creating a full backup, tail backup, copy that over to the mirror (the original principal), apply the backup, and go through the Mirroring Wizard, start mirroring, and then, finally, failover to the original primary.I am just wondering if, in the above, is how it is supposed to be?

MS Word Mail Merger from SQL view over SSL VPN

Posted: 23 Aug 2013 09:35 AM PDT

Hello,I am trying to mail merge a document over an SSL VPN connection. I can open a Word doc that is on our server, but when I try to mail merge the document, it freezes, or takes about 30 seconds to search 200 records. We have 16,000 records... When I'm in the office it take 2-3 seconds to mail merge.I am using a word data source that connects to a view on our SQL server. I have tried recreating the mail merge with Schema binding so I could create a unique Clustered Index on the base table's primary key, which is also the field we use to search for our records.This they anything I can do to speed things up? I'm open to idea's...As always any help is greatly appreciated,David92595

Log for login privileges

Posted: 23 Aug 2013 03:43 AM PDT

Hi Friends,Let us assume that I have created a login named XXXX and given db_owner privileges a month ago. The user complained that he is unable to create the table or drop the table in that particular database. When I cross-checked, login privileges was changed from db_owner to db_datareader. Is there any way to find which user has changed the privileges?Thanks in advance.

Query Help

Posted: 23 Aug 2013 04:20 AM PDT

HelloI need one help to develop query[code="sql"]CREATE TABLE #Student( StudentID varchar(09), Race varchar(2), CurrentSchoolCode int, CurrentGradeCode varchar(2), SchoolYearCode int)INSERT INTO #Student VALUES ('001233069','W',909,'11',2013)CREATE TABLE #CurrentSchedule( StudentID varchar(09), CourseID varchar(10), SchoolYearCode int)INSERT INTO #CurrentSchedule VALUES ('001233069','001113',2013)INSERT INTO #CurrentSchedule VALUES ('001233069','009999',2013)INSERT INTO #CurrentSchedule VALUES ('001233069','544024',2013)INSERT INTO #CurrentSchedule VALUES ('001233069','550054',2013)INSERT INTO #CurrentSchedule VALUES ('001233069','560012',2013)INSERT INTO #CurrentSchedule VALUES ('001233069','580070',2013)INSERT INTO #CurrentSchedule VALUES ('001233069','580064',2013)CREATE TABLE #LastYearMark( StudentID varchar(09), CourseID varchar(10), SchoolYearCode int, Mark varchar(02))INSERT INTO #LastYearMark VALUES ('001233069','520018',2013,'E')INSERT INTO #LastYearMark VALUES ('001233069','550031',2013,'A')INSERT INTO #LastYearMark VALUES ('001233069','586603',2013,'B')INSERT INTO #LastYearMark VALUES ('001233069','000116',2013,'B')INSERT INTO #LastYearMark VALUES ('001233069','550001',2013,'A')SELECT S.StudentID,S.Race,S.CurrentSchoolCode,s.CurrentGradeCode,c.CourseID AS [Current Year Schedule Course],LM.CourseID AS [Last Year's Course],LM.Mark AS[Last Year's Mark] FROM #Student sJOIN #CurrentSchedule CON S.StudentID = C.StudentIDJOIN #LastYearMark LMON LM.StudentID = S.StudentIDWHERE C.CourseID = '001113'[/code]I got output as below[code="plain"]StudentID Race CurrentSchoolCode CurrentGradeCode Current Year Schedule Course Last Year's Course Last Year's Mark001233069 W 909 11 001113 520018 E001233069 W 909 11 001113 550031 A001233069 W 909 11 001113 586603 B001233069 W 909 11 001113 000116 B001233069 W 909 11 001113 550001 A[/code]desired output[code="plain"]StudentID Race CurrentSchoolCode CurrentGradeCode Current Year Schedule Course Last Year's Course Last Year's Mark001233069 W 909 11 001113 520018 E001233069 W 909 11 009999 550031 A001233069 W 909 11 544024 586603 B001233069 W 909 11 550054 000116 B001233069 W 909 11 560012 550001 A[/code]is that possible?if Yes, please help me to do so.Thanks

Sync tables

Posted: 23 Aug 2013 07:59 AM PDT

Hi,what are the best options to sync one table between two separate SQL Server databases(Same domain). Thanks.

Index defrag doesn't seem to do anything

Posted: 23 Aug 2013 05:19 AM PDT

I just got an email from Idera pitching their free fragmentation analyzer, so I downloaded it and tried it out. Several tables report high levels of index fragmentation, 50%, 67%. But when I rebuild an index, some of them don't change, at all. Some do, and go all the way down to 0%, but some don't change at all. I've seen several articles stating that index fragmentation isn't often a performance issue, and worrying about rebuilding them is usually pointless.That may be, and I certainly don't intend to run defrag scripts every night, but I will likely run them whenever I deploy a new version of the DB, since that involves copying over ALL the data from EVERY table in the old DB into EVERY table in the new DB. The order of such records getting imported is whatever SQL Server decides, based on my import queries, so some indexes will certainly be completely trashed. That seems like an appropriate occasion to do a complete rebuild of all indexes.In any case, I'm curious why a rebuild doesn't defrag an index, even if a fragmented index isn't necessarily a problem. (It's not just Idera's tool, BTW, SSMS also reports the same unchanged level of fragmentation. Idera's tool just presents lots of information in a nicely formatted table.) Can anyone shed some light on the subject?

TempDB issue

Posted: 23 Aug 2013 01:44 AM PDT

Hi AllI'm having an issue with the tempdb, after running an intensive insert script (with simple insert statements)The total number of inserts to be done is 2800. For each record a several select statements are performed to obtain values.The error message is: "An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."Then I try to see the tempdb properties and it is not possible.Anyway the tempdb is configured with 12 data files each with 256MB and an autogrowth of 60MB.The tempdb log file is 2GB with 1GB of autogrowth.I must restart the instance in order to see the tempdb properties.The system is 24 core with 32 GB RAM, of which 28GB are dedicated to sql server.There are no missing indexes in the script.Can anyone suggest something...Thank youIgorMi

Why id GO not a valid keyword in Visual Studio?

Posted: 23 Aug 2013 05:22 AM PDT

Hi,I am a complete beginner developing with MSSQL.I have just created my first stored procedure in Visual Studio. If I try to use the keyword 'GO', I get a syntax error. If I save the stored procedure and then view it in Microsoft SQL Management Studio using the 'Modify' menu selection, I get a query windows which has added extra stuff before my stored procedure and also makes use of the keyword 'GO'.I am trying to create a table and then add some data to it. The INSERT fails, and I was thinking it might be because I had not put 'GO' between the CREATE TABLE and the INSERT, but, as I said, Visual Studio won't let me do this.I'm obviously being a bit thick about something here. Any help would be gratefully received.Kind wishes, Patrick

query

Posted: 23 Aug 2013 01:38 AM PDT

Hi,I have 2 master-details tables : Category and Item I want to group them to show in drop-down like:Category1Item1Item2Item3Category2Item4Item5I will disable selection of Category in drop-down.Please let me know which query I should use considering performance issues.

SQL Agent Operator Issue

Posted: 23 Aug 2013 04:07 AM PDT

I was wondering if anyone else has seen this issue. A couple of weeks ago, I renamed my SQL Agent Operator account on many machines (SQL 2005, 2008, 2008R2 and 2012 machines) and from that point on, the SQL Agent stopped sending out notifications from JOBS, maintenance plan reports, etc.FYI, I did the rename via right click rename, not T-SQL.I deleted that Operator, created new one (same name) and re associated all Jobs and maintenance plans and all is working fine now.Weird huh?

DBNETLIB Error on Client PCs

Posted: 23 Aug 2013 03:16 AM PDT

We run an Service Ticket Application that uses SQL Express 2008 as the DB Engine. We have 4 PCs that run a Client App that connects to the SQL Server. On 3 of the Clients, if you minimize the Client App for a couple of hours or if you leave it minimized over night, the next time you pull up the Client and do any action at all, you get an error of "[DBNETLIB][ConnetionWrite (WrapperWrite()).]General network error. Check your network documentation.". 1 PC NEVER gets this error. All PCs are unning XP PRO SP3. This can be immediately duplicated at the PCs by having the Client up, unplug the Network Cable and let the connection die, plug the cable back in and allow the Connection to come back LIVE and then do someyjing in the Client. The very first action causes this error to come up and the Client to shut down. Like I stated, 3 PCs do this but 1 NEVER has a problem even using the "TEST" above. Any ideas???

Alert Based on User Connections

Posted: 22 Aug 2013 10:51 PM PDT

HiHas anyone ever created an alert based on connections to a database? For example, I want an email sent out via SQL Server when a threshold is breached in terms connections and I'd want the check to run at specific times throughout the day.I know I execute an sp_who to get a number of current connections to the database but I would want an alert triggering if a results returned are >= X.Thanks.

Need a help in Stored Procedure execution Plan

Posted: 22 Aug 2013 11:00 PM PDT

If we call more SP inside SP, Does execution plan will create plan for the SP which we called inside main SP as well.What would be good idea, whether to call another SP or writing logic in the Same SP as well?

Upgrading SQL

Posted: 22 Aug 2013 10:17 PM PDT

How to upgrade the SQL 2005 to SQL 2008 r2 when the databases are configured in Mirroring and in cluster environment ?

Need a help in case statement...

Posted: 22 Aug 2013 09:59 PM PDT

Hi, I need a help in case statement. I want to know whether we can set value for two fields in case statementfor exampleSelect (CASE WHEN SUnits> 0 THEN 'xxx' WHEN RUnits > 0 THEN 'yyy' END)Here I need to set another value as well in Then statementI want the above to be like this Select @data= (CASE WHEN SUnits> 0 THEN 'xxx' ,@dsr=1WHEN RUnits > 0 THEN 'yyy', ,@dsr=0 END)Is it possible?Else the below statement is the only waySelect @data= (CASE WHEN SUnits> 0 THEN 'xxx' WHEN RUnits > 0 THEN 'yyy', END),@dsr= (CASE WHEN SUnits> 0 THEN 1WHEN RUnits > 0 THEN 0 END)Here I need to

CU1 updated Failed on SQL2K8 x64-SP2?

Posted: 22 Aug 2013 09:59 PM PDT

Hi,Pl. find the attached summary log..version - SQL SERVER 2008 R2 - SP2 - 64 bitI am trying to update CU1 of SP2, It was failed...But Instance version display 10.50.4260..I think version header information only updated but not full.Could you suggestion me, what could be reason for failure?Thanksananda

always on Option in mirroring

Posted: 22 Aug 2013 09:58 PM PDT

can any one explain this feature on sql 2012 and how can we implement this on sql ?

How to create database dynamically

Posted: 22 Aug 2013 07:36 PM PDT

Hi, Following is the SP to rename the existing database and then create a new database ,but,I am getting the error as,Msg 102, Level 15, State 1, Line 8Incorrect syntax near 'D:'.Msg 132, Level 15, State 1, Line 10The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.I am not able to understand the following error.Please help me.Thanks in Advance!!USE masterGOIf Object_Id('SP_DataBaseCreation_00') Is Not NullBegin Drop Procedure SP_DataBaseCreation_00End GoCreate Procedure SP_DataBaseCreation_00As Begin Declare @Sqlstr As Varchar(8000) Declare @Backup_DBname Varchar(200) Declare @Prev_QtrDt Varchar(20) Declare @AsonDate Varchar(20) Declare @DBName As Varchar(20) Select @Prev_QtrDt = Convert(varchar(8),LastNpaDate,112) + '_' + Convert(varchar(8),GetDate(),112), @AsonDate = Convert(varchar(8),AsonDate,112) + '_' + Convert(varchar(8),GetDate(),112) --,@DBName = NPAEXEC_DBNAME from SHFC_NPA..IGen_Settings Set @Backup_DBname = 'CoreDB_' + @Prev_QtrDt Select @Backup_DBname Select @AsonDate Set @Sqlstr = '' Set @Sqlstr = 'ALTER DATABASE CoreDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE EXEC master..sp_renamedb ''CoreDB'',' + @Backup_DBname + ' ALTER DATABASE ' + @Backup_DBname + ' SET MULTI_USER' Print @Sqlstr Exec (@Sqlstr) Declare @DB_MPath As varchar(100) Declare @DB_LPath As varchar(100) Declare @DB_LName As varchar(100) Set @DB_MPath = 'N''D:\Test_DataBase\CoreDB_' + @AsonDate --Set @DB_LPath = 'N''D:\Test_DataBase\CoreDB_' + @AsonDate + '.ldf' Set @DB_LName = 'N''CoreDB_' + @AsonDate Set @Sqlstr = 'if db_id(''CoreDB'') is not null begin drop database CoreDB end CREATE DATABASE [CoreDB] ON PRIMARY ( NAME = ' + @DB_LName + ', FILENAME = ' + @DB_MPath + '.mdf , SIZE = 515072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = ' + @DB_LName + '_log, FILENAME = ' + @DB_MPath + '.ldf , SIZE = 568896KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) --GO ALTER DATABASE [CoreDB] SET COMPATIBILITY_LEVEL = 100 --GO IF (1 = FULLTEXTSERVICEPROPERTY(''IsFullTextInstalled'')) begin EXEC [CoreDB].[dbo].[sp_fulltext_database] @action = ''enable'' end --GO ALTER DATABASE [CoreDB] SET ANSI_NULL_DEFAULT OFF --GO ALTER DATABASE [CoreDB] SET ANSI_NULLS OFF --GO ALTER DATABASE [CoreDB] SET ANSI_PADDING OFF --GO ALTER DATABASE [CoreDB] SET ANSI_WARNINGS OFF --GO ALTER DATABASE [CoreDB] SET ARITHABORT OFF --GO ALTER DATABASE [CoreDB] SET AUTO_CLOSE OFF --GO ALTER DATABASE [CoreDB] SET AUTO_CREATE_STATISTICS ON --GO ALTER DATABASE [CoreDB] SET AUTO_SHRINK OFF --GO ALTER DATABASE [CoreDB] SET AUTO_UPDATE_STATISTICS ON --GO ALTER DATABASE [CoreDB] SET CURSOR_CLOSE_ON_COMMIT OFF --GO ALTER DATABASE [CoreDB] SET CURSOR_DEFAULT GLOBAL --GO ALTER DATABASE [CoreDB] SET CONCAT_NULL_YIELDS_NULL OFF --GO ALTER DATABASE [CoreDB] SET NUMERIC_ROUNDABORT OFF --GO ALTER DATABASE [CoreDB] SET QUOTED_IDENTIFIER OFF --GO ALTER DATABASE [CoreDB] SET RECURSIVE_TRIGGERS OFF --GO ALTER DATABASE [CoreDB] SET DISABLE_BROKER --GO ALTER DATABASE [CoreDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF --GO ALTER DATABASE [CoreDB] SET DATE_CORRELATION_OPTIMIZATION OFF --GO ALTER DATABASE [CoreDB] SET TRUSTWORTHY OFF --GO ALTER DATABASE [CoreDB] SET ALLOW_SNAPSHOT_ISOLATION OFF --GO ALTER DATABASE [CoreDB] SET PARAMETERIZATION SIMPLE --GO ALTER DATABASE [CoreDB] SET READ_COMMITTED_SNAPSHOT OFF --GO ALTER DATABASE [CoreDB] SET HONOR_BROKER_PRIORITY OFF --GO ALTER DATABASE [CoreDB] SET READ_WRITE --GO ALTER DATABASE [CoreDB] SET RECOVERY FULL --GO ALTER DATABASE [CoreDB] SET MULTI_USER --GO ALTER DATABASE [CoreDB] SET PAGE_VERIFY CHECKSUM --GO ALTER DATABASE [CoreDB] SET DB_CHAINING OFF' Print (@Sqlstr)Exec (@Sqlstr)End

FOR XML RAW - With invalid XML data

Posted: 22 Aug 2013 07:40 PM PDT

Is there any data scenario , the output of FOR XML RAW has illegal characters and not a valid XML Some Sample [code="sql"]DECLARE @TSTXML XML SELECT @TSTXML = ( SELECT Column1 , Column2 FROM SomeTableFOR XML RAW)[/code]XML parsing: line 1, character 170, illegal xml character, I have a code in customer base with assign a Select statement with FOR XML RAW to XML variable.But that throws error , dont know the exact data on that database
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog