Tuesday, April 9, 2013

[MS SQL Server] Max server memory for second instance

[MS SQL Server] Max server memory for second instance

Max server memory for second instance

Posted: 08 Apr 2013 10:07 AM PDT

We have a SQL instance with max memory set to 20 GB out of total 24 GB.Now I would like to install another instance on the server.Will the install process ask about how much I will grant memory to the new instance.If not, I will need to manually change the max server memory after it is done, correct?I will probably take 8 GB from the 20 GB, and make the original instance max memory to 12 GB, does it sound reasonable?Also since the exiting instance is sql 2008 sp3, for the new instance we want the same sp, when I install SQL server and servie pack, do I need to restart the server?We don't want to , because osmeone is using the existing instance.Thanks

Can not read 'Sql Server Agent logs' on node 2 of a cluster

Posted: 09 Apr 2013 01:21 AM PDT

Clustered 2008 R2 SQL Server Enterprise Edition (64-bit) SP1 10.50.2500Both nodes are running under the same account which is an local admin and both computers(nodes) are an admin on the other computer. [Node1 is an admin on Node2; Node2 is an admin on Node1]While the cluster is running on node 1 you can browse the SQL Server Agent Logs, (It CAN read the SQL Server logs) but once I failover to node two I get the following error message when I try to browse the Agent Log:xp_readerrorlog()( returned error 3, 'The system cannot find the path specified.' (Error:22004)

Obscure Replication Error

Posted: 09 Apr 2013 01:26 AM PDT

I've had multi-terabyte transactional replication going for a couple years now, for the most part things flow quite smoothly with the occasional slowdown when massive updates are performed. However since March 22nd, 2013 I've had the log reader agent blow up and stop responding.There's not much in the way of a descriptive error, nor much to read in the log/dump files other than it appears to point out an issue with the EXE itself...perhaps a memory leak or a bug somewhere in the distribution agent executable?In the dump file, this is basically the only useful information I can see - hoping experts around here have experienced something similar and can offer suggestions:[quote]Process Name: DISTRIB.exe : C:\Program Files\Microsoft SQL Server\100\COM\DISTRIB.exeProcess Architecture: x64Exception Code: 0xC0000005Exception Information: [b]The thread tried to read from or write to a virtual address for which it does not have the appropriate access[/b].[/quote]Here's the output from the Replication Agent job:MessageThe replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed.[quote]Date 3/23/2013 1:34:26 AMLog Job History (MYSERVER-CA_TABLES-SUBSCRIBER-22)Step ID 2Server DISTRIBUTORJob Name MYSERVER-CA_TABLES-SUBSCRIBER-22Step Name Run agent.Duration 17.06:24:48Sql Severity 0Sql Message ID 0Operator Emailed Operator Net sent Operator Paged Retries Attempted 0Message2013-04-09 12:26:55.798 88 transaction(s) with 232 command(s) were delivered.2013-04-09 12:26:55.798 Delivering replicated transactions2013-04-09 12:27:11.251 Delivering replicated transactions2013-04-09 12:27:21.751 114 transaction(s) with 321 command(s) were delivered.2013-04-09 12:27:32.673 101 transaction(s) with 206 command(s) were delivered.2013-04-09 12:27:37.938 100 transaction(s) with 168 command(s) were delivered.2013-04-09 12:27:50.814 Delivering replicated transactions2013-04-09 12:27:58.907 Delivering replicated transactions2013-04-09 12:28:00.142 107 transaction(s) with 288 command(s) were delivered.2013-04-09 12:28:19.376 Delivering replicated transactions2013-04-09 12:28:24.564 101 transaction(s) with 256 command(s) were delivered.2013-04-09 12:28:35.783 100 transaction(s) with 216 command(s) were delivered.2013-04-09 12:28:54.658 Delivering replicated transactions2013-04-09 12:29:01.751 Delivering replicated transactions2013-04-09 12:29:01.783 101 transaction(s) with 253 command(s) were delivered.2013-04-09 12:29:14.377 100 transaction(s) with 162 command(s) were delivered.2013-04-09 12:59:14.404 HYT00 Query timeout expired 02013-04-09 12:59:14.404 <stats state="2" fetch="1543" wait="71597" cmds="1252" callstogetreplcmds="519922"><sincelaststats elapsedtime="1838" fetch="0" wait="1838" cmds="1252" cmdspersec="0.000000"/></stats>************************ STATISTICS SINCE AGENT STARTED ***********************04-09-2013 07:59:14Total Run Time (ms) : 1491874890 Total Work Time : 70358184Total Num Trans : 1282424 Num Trans/Sec : 18.23Total Num Cmds : 3599854 Num Cmds/Sec : 51.16Total Idle Time : 1375300094 Writer Thread Stats Total Number of Retries : 4 Time Spent on Exec : 4520346 Time Spent on Commits (ms): 149885 Commits/Sec : 1.26 Time to Apply Cmds (ms) : 8228658 Cmds/Sec : 437.48 Time Cmd Queue Empty (ms) : -1297083607 Empty Q Waits > 10ms: 24054 Total Time Request Blk(ms): 78216487 P2P Work Time (ms) : 0 P2P Cmds Skipped : 0Reader Thread Stats Calls to Retrieve Cmds : 519922 Time to Retrieve Cmds (ms): 70358184 Cmds/Sec : 51.16 Time Cmd Queue Full (ms) : 2784738 Full Q Waits > 10ms : 5585#1 Num Cmds : 774239 Exec (ms) : 4520346 Commit (ms) : 145004 Process (ms): 7831768 Last xact : 0x00068c0e0001f56e000c#2 Num Cmds : 802248 Exec (ms) : 4539826 Commit (ms) : 149885 Process (ms): 7910496 Last xact : 0x00068c0e0001f5fb000b#3 Num Cmds : 779073 Exec (ms) : 4451619 Commit (ms) : 131556 Process (ms): 7929972 Last xact : 0x00068c0e0001f62a000b#4 Num Cmds : 1244294 Exec (ms) : 6389841 Commit (ms) : 146076 Process (ms): 8228658 Last xact : 0x00068c0e0001f59d000dLast global update to sub xact : 0x00068c0e0001f62a000b*******************************************************************************2013-04-09 12:59:14.404 Delivering replicated transactions2013-04-09 12:59:14.404 Delivering replicated transactions2013-04-09 12:59:14.404 Delivering replicated transactions2013-04-09 12:59:14.404 <stats state="1" work="70358" idle="1375300"><reader fetch="1543" wait="71597"/><writer write="8228" wait="2997883"/><sincelaststats elapsedtime="1947" work="109" cmds="2146" cmdspersec="19.000000"><reader fetch="0" wait="1947"/><writer write="169" wait="1863"/></sincelaststats></stats>********************************************************************************Microsoft (R) SQL Server Replication AgentA replication agent encountered a fatal error and was shut down. A mini-dump has been generated at the following location:C:\Program Files\Microsoft SQL Server\100\Shared\ErrorDumps\ReplAgent20130409075914_0.mdmp[/quote]

grant access to sp_who3

Posted: 09 Apr 2013 12:29 AM PDT

We have deployed on a dwh server the sp_who3 SP to look at user activity. WOuld like to give developers a way to run this without sys admin rights (below is the sp). Tried to give them read rights to the tables in the sp but that would not work. Thought of making a compiled exe with the command and a password encrypted in it to run from a command prompt, the issue is the window returned from the DOS prompt is almost impossible to read. ANy other ideas? THanksUSE [PBI_CMS]GO/****** Object: StoredProcedure [dbo].[sp_who3] Script Date: 04/02/2013 10:25:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[sp_who3] ASBEGINSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT SPID = er.session_id ,BlkBy = er.blocking_session_id ,ElapsedMS = er.total_elapsed_time ,CPU = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,Executions = ec.execution_count ,CommandType = er.command ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,SQLStatement = SUBSTRING ( qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2 ) ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,DBName = DB_Name(er.database_id) ,LastWaitType = er.last_wait_type ,StartTime = er.start_time ,Protocol = con.net_transport ,transaction_isolation = CASE ses.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_schemeFROM USE AdventureWorks2012;GRANT SELECT ON OBJECT::Person.Address TO RosaQdMgrant select on object::sys.dm_exec_connections to [CORIZONHEALTH\DOsdieck]LEFT JOIN sesON ses.session_id = er.session_idLEFT JOIN sys.dm_exec_connections conON con.session_id = ses.session_idCROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qtOUTER APPLY ( SELECT execution_count = MAX(cp.usecounts) FROM cp WHERE cp.plan_handle = er.plan_handle) ecORDER BY er.blocking_session_id DESC, er.logical_reads + er.reads DESC, er.session_id ENDFROM sys.dm_exec_requests erLEFT JOIN sys.dm_exec_sessions sesON ses.session_id = er.session_idLEFT JOIN sys.dm_exec_connections conON con.session_id = ses.session_idCROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qtOUTER APPLY ( SELECT execution_count = MAX(cp.usecounts) FROM sys.dm_exec_cached_plans

Deadlock Graph from the xml from system_health extended event

Posted: 29 Jan 2013 07:55 AM PST

How to get the Deadlock Graph from the xml extracted from system_health extended event.I'm using SQL Server 2008R2 SP2, and when I try to open the xdl file it says Failed to initialize deadlock control, key cannot be nul, Parameter name:KeyThanks

Moving data file

Posted: 08 Apr 2013 06:20 AM PDT

For a db involved in db mirroring, what steps do I take to move its data and log file to another drive?

how to enable back database mirroring

Posted: 08 Apr 2013 01:46 PM PDT

Hi Everyone,I Have disabled database mirroring on principal server using below command for restoring database over a mirrored database because it was not allowing me to restore.ALTER DATABASE AdventureWorks2012 SET PARTNER OFF;after executing above command i was able to restore the database , now i want to enable back database mirroring can some please help me with the steps.Thank you in advance for looking into my post.

uninstall an instance from a server that has two instances

Posted: 08 Apr 2013 10:51 AM PDT

If we have installed two instances on a server, and for some reason, maybe resources, we would like to unintall one of the instance, is it a pretty safe and easy operation without affecting existing one?Thanks

Version Store isolation levels confusion

Posted: 09 Oct 2012 09:38 PM PDT

Hi AllI used the below query to assess which databases have produced records in the version store [code="sql"]select distinct db_name(database_id) from sys.dm_tran_version_store[/code]The result shows me one database.Then I executed the below script to check the settings for this database[code="sql"]select name, snapshot_isolation_state_desc, is_read_committed_snapshot_on from sys.databases order by name[/code]The problem is that this particular database has both snapshot_isolation_state_desc andis_read_committed_snapshot_on turned off.How else could this database be producing Version Store records?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog