Tuesday, May 7, 2013

[MS SQL Server] Row Versioning and SharePoint Foundation 2010

[MS SQL Server] Row Versioning and SharePoint Foundation 2010

Row Versioning and SharePoint Foundation 2010

Posted: 07 May 2013 12:29 AM PDT

Is it possible to enable row versioning for your SharePoint Foundation 2010 databases? We are running SQL Server 2008 R2 SP2 x64. Is this something that is supported?

Uninstalling an Instance of SQL failed

Posted: 22 May 2012 04:19 AM PDT

Good day,I have two instances of SQL 2008 installed on a single server, and I need to uninstall the one instance.When I run the Uninstall (using the programs add remove feature of windows 2008), I get the error:[b]The CPU architectures of feature(s) you selected to removed are different then Setup. To remove these features, Setup architecture must be the same as the features being installed.[/b]Any ideas?thanks

How to track SP

Posted: 06 May 2013 06:54 PM PDT

Hi All,as a newbie database admin I am facing challenge. I need to track an SP. the SP name is structuralcheck_mfs and the database name is AP_Bureau. I want to check who has run this SP for last 7 days. what is the creation date and the modification date. I also want to track their IP address.Please help!!

optimize for ad hoc workloads Question

Posted: 06 May 2013 11:59 AM PDT

I noticed that one query was always having a high compilation time and this led me to what I believed to be a lot of adhoc plans in the plan cache.The queries are sent through from an external app.After some research and testing I have turned on "optimize for ad hoc workloads" and added some Plan Guides for the offending queries.This has stopped the compilation time being excessive but now I notice:1. The space usage of the adhoc plans has gone down from about 1400mb to a few MB[b]Prepared [/b]Total Plans 2338 Total MBs 205Total MBs - USE Count 1 100Total Plans - USE Count 1 1370[b]Adhoc[/b]Total Plans 5633 Total MBs 40Total MBs - USE Count 1 13Total Plans - USE Count 1 51562.I notice the Adhoc plan count is going up quite fast now -- roughly one line per query -- the size of each plan is 200 bytes - 300 bytes (rather than 24K).So is this normal? and will some rows be removed or will it now just keep on adding rows until the memory fills up and the server crashes??Maybe a bit paranoid as I am not sure if this is normal.Extra Note: I have removed my Plan Guide and the records are still being inserted quite fast into the Plan Cache (though the query times have now increased to the previous times as you would expect).It looks like the Plan Cache will keep on growing until it reaches a system limit and then entries will be removed??? Not 100% sure though about that.thanks

How to know the SQL statements causing the Deadlock from this XML output?

Posted: 18 Jun 2012 09:58 PM PDT

I can figure the stored procedures names using object ID's. But i want to determine the actual SQL statements that are involved. since there are too many lines of code inside SP's.Note: The SP's are created "WITH ENCRYPTION" option<deadlock> <victim-list> <victimProcess id="processcdeb048" /> </victim-list> <process-list> <process id="processcdeb048" taskpriority="0" logused="0" waitresource="OBJECT: 5:1288651934:14 " waittime="4097" ownerId="12406020" transactionname="INSERT EXEC" lasttranstarted="2012-06-19T13:33:47.680" XDES="0x2db8e6b60" lockMode="IS" schedulerid="15" kpid="312" status="suspended" spid="114" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-06-19T13:33:47.680" lastbatchcompleted="2012-06-19T13:33:47.680" clientapp=".Net SqlClient Data Provider" hostname="APP02" hostpid="6548" loginname="ppp" isolationlevel="read committed (2)" xactid="12406020" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128024"> <executionStack> <frame procname="" line="32" stmtstart="2772" stmtend="6064" sqlhandle="0x03000500d5a6b850a53afc0043a000000100000000000000" /> <frame procname="" line="39" stmtstart="2692" stmtend="3102" sqlhandle="0x030005009577e2790a332101739f00000100000000000000" /> <frame procname="" line="75" stmtstart="5000" stmtend="5536" sqlhandle="0x030005007472ea73a6610c01b29f00000100000000000000" /> <frame procname="" line="57" stmtstart="3550" stmtend="4104" sqlhandle="0x030005002136e5687d2ecc006ea000000100000000000000" /> </executionStack> <inputbuf>Proc [Database Id = 5 Object Id = 1759852065] </inputbuf> </process> <process id="processc5b6988" taskpriority="-10" logused="24484" waitresource="OBJECT: 5:1320652048:14 " waittime="686" ownerId="12390056" transactionname="user_transaction" lasttranstarted="2012-06-19T13:33:26.567" XDES="0x34792f030" lockMode="Sch-M" schedulerid="13" kpid="2768" status="suspended" spid="112" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-06-19T13:33:26.620" lastbatchcompleted="2012-06-19T13:33:26.620" clientapp=".Net SqlClient Data Provider" hostname="APP01" hostpid="17608" loginname="sa" isolationlevel="read committed (2)" xactid="12390056" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128024"> <executionStack> <frame procname="" line="1" stmtstart="182" stmtend="418" sqlhandle="0x02000000257f05128745b0e6d8b16a322fb2b916c3f504ea" /> <frame procname="" line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000" /> <frame procname="" line="29" stmtstart="4034" stmtend="4096" sqlhandle="0x0300050057d57c49ba1af400739f00000100000000000000" /> <frame procname="" line="47" stmtstart="3100" stmtend="3302" sqlhandle="0x030005007b27f310547bdd0074a000000100000000000000" /> </executionStack> <inputbuf>Proc [Database Id = 5 Object Id = 284370811] </inputbuf> </process> </process-list> <resource-list> <objectlock lockPartition="14" objid="1288651934" subresource="FULL" dbid="5" objectname="" id="lock2ed997c00" mode="Sch-M" associatedObjectId="1288651934"> <owner-list> <owner id="processc5b6988" mode="Sch-M" /> </owner-list> <waiter-list> <waiter id="processcdeb048" mode="IS" requestType="wait" /> </waiter-list> </objectlock> <objectlock lockPartition="14" objid="1320652048" subresource="FULL" dbid="5" objectname="" id="lock1ada50e80" mode="IS" associatedObjectId="1320652048"> <owner-list> <owner id="processcdeb048" mode="IS" /> </owner-list> <waiter-list> <waiter id="processc5b6988" mode="Sch-M" requestType="wait" /> </waiter-list> </objectlock> </resource-list></deadlock>

Logshipping + Database snapshot combination

Posted: 06 May 2013 06:29 AM PDT

I set up log shipping on both servers and they are running fine. Due to a recent requirement, they want a database snapshot on secondary server/database to run report. I create snapshot database, but log shipping starts failing until I drop the snapshot database. Is there a way to go around it? I don't want database mirroring in this case. Thanks,Minh
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog