Wednesday, September 4, 2013

[MS SQL Server] Kill SQL SPIDs for a certain SPID range, or a Date Range

[MS SQL Server] Kill SQL SPIDs for a certain SPID range, or a Date Range

Kill SQL SPIDs for a certain SPID range, or a Date Range

Posted: 04 Sep 2013 02:40 AM PDT

Hello EveryoneIs there a way to kill SPIDs in a certain SPID range? like all spids 75-100 etc... Or with a date range? Say all SPIDs that are over a day old? I am seeing some stale connections that are not closing.ThanksAndrew SQLDBA

database in simple mode with growing log file into the GBytes

Posted: 04 Sep 2013 12:23 AM PDT

I'd like to get suggestions on how to handle the issue I'm facing. I have a database that sole purpose is to be a staging place for data. Every night thousands of records are re-loaded to transfer new information and refresh the database. I have the database setup to simple mode however the log file grows up to close to 30GB due to the inserts and deletes from the batch scripts. I have a script that shrinks the database log file every day but since the reload is daily is grows up to 30GB again. I'm only responsible for the database maintenance not loading the data however, I'd like to give the people responsible for it a suggestion on how to do it better.What's the best way to handle a process like this?

Merge_Replication_Tables_Not_In_Sync(MSmerge_tombstone)

Posted: 03 Sep 2013 11:07 PM PDT

We've merge replication configured in our environment(1 Publisher, 3 Subscribers) and We know by default merge replication is bi-directional. Its working fine in case of inserts i.e, if a row is inserted at publisher then it's getting updated at subscriber and vice versa. The real issue is, if row gets deleted at publisher then its getting deleted at subscriber but, if a row gets deleted in any of 3 subscribers then its not getting deleted at publisher.When a row is deleted at publisher then it's getting updated in MSmerge_tombstone(Publisher). But, in case of subscriber, the corresponding row(rowguid) is not getting updated in MSmerge_tombstone(Subscriber). I think that might be the issue, even merge delete trigger exist on these tables(Subscriber). Please help me out, as it is a production issue.Publisher(2008R2 sp1-Enterprise), Subscribers(2008R2 sp1-Standard)

Query tuning performance

Posted: 03 Sep 2013 07:19 PM PDT

how can we get execution plan of a query executed on two different days so that i come to know that what is cause of slowing down the query today? i know that query was running fine one week back.what are the steps to find the cause of slowing down the query today which was running very well in past?what could be the possible reason of slowing down the query today which was running very well in past?I have faced this question in interview lot of time could you please help me?

SQLServer 2008 R2 SP 2 confusion

Posted: 03 Sep 2013 06:32 PM PDT

We have three node cluster configuration for SQLserver 2008 R2. We are planning to do upgrade SP 2. We realized that when we did the upgrade on Pre Prod environments our eventviewer started to generate spam message "[sqagtres] CheckServiceAlive: returning TRUE (success)"To overcome that I understand that we need to apply CU 3 after SP2. We decided to take the latest CU (CU 7) at that time. We tried Cu 7 again on the lower environment than Pre Prod. It started throwing errors on change data capture.2013-09-02 02:52:59.16 spid9s DBCC execution completed. If DBCC printed error messages, contact your system administrator.2013-09-02 02:52:59.22 spid9s Unable to upgrade database 'Meta__datat_accounts'. Error '22841' was raised: 'Could not upgrade the metadata for database 'Meta__datat_accounts' that is enabled for Change Data Capture. The failure occurred when executing the action 'sp_cdc_create_change_enumeration_functions'. The error returned was 102: 'line 45, state 1, Incorrect syntax near ')'.'. Use the action and error to determine the cause of the failure and resubmit the request.'. Use the reported error to determine the cause of the failure and then execute sys.sp_cdc_vupgrade in the database context to rerun upgrade.2013-09-02 02:52:59.22 spid9s sp_vupgrade_replication executed successfully2013-09-02 02:52:59.22 spid9s Saving upgrade script status to 'SOFTWARE\Microsoft\MSSQLServer\Replication\Setup'.2013-09-02 02:52:59.22 spid9s Saved upgrade script status successfully.2013-09-02 02:52:59.23 spid9s Database 'master' is upgrading script 'upgrade_ucp_cmdw_discovery.sql' from level 171053246 to level 171053250.I don't see any abnormal activity in the server. My question 1. Will that be a problem in if I upgrade my prod environments?2. What happens If I leave the eventvwr been spammed with messages? 3. When is SP 3 for SQLserver 2008 R2 coming?Can you please help me?FYI. We tried with CU 8 also same result. Thank you.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog