Thursday, October 3, 2013

[MS SQL Server] sqlserver connection problems

[MS SQL Server] sqlserver connection problems

sqlserver connection problems

Posted: 03 Oct 2013 04:18 AM PDT

we had a problem yesterday with all jobs skipping their schedule on a server for a small time frame at regular intervals. No information in the windows logs or event logs. We had restart the server to fix it. I was not able to connect to the sql server thru ssms. [i]A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)[/i]I am trying to find out what exactly the issue is?

Transactional Replication. snapshot Agent not picking up Creation Script

Posted: 03 Oct 2013 02:08 AM PDT

I'm working on some custom transactional replication as part of a partial database/application upgrade. Table definitions have changed in the publisher, but not in the subscribers. I need to run a custom creation script to modify the subscriber and the distribution agent is not picking it up. Here's the article creation script:[code="sql"]EXEC sp_addarticle @publication = N'WB_to_opls_character', @article = N'Employee', @source_owner = N'dbo', @source_object = N'Employee', @type = N'indexed view logbased manualboth', @description = N'', @creation_script = 'F:\Data\Cleanup tbl_Employee.sql', @pre_creation_cmd = N'delete', @schema_option = 0x00, @identityrangemanagementoption = N'manual', @destination_table = N'tbl_EMPLOYEE', @destination_owner = N'dbo', @status = 0, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboEmployeeCustom]', @del_cmd = N'CALL [sp_MSdel_dboEmployeeCustom]', @upd_cmd = N'SCALL [sp_MSupd_dboEmployeeCustom]', @sync_object = N'vEmployee', @sync_object_owner = N'sync'[/code]And yes I'm jumping through some hoops here to make replication work. I was given the option of replication (which was already being used) or linked servers, synonyms, and other hoops and chose replication. SSIS may have been a better choice, but they want near real-time and once I learned I could jump through some hoops with replication to make the source look like the destination I decided to go with it.Thanks.P.S. I had it working, forgot to save the script and now for some reason the creation script is not getting picked up by the snapshot agent.

Alter Partition Function with existing data problem

Posted: 03 Oct 2013 01:59 AM PDT

I have inherited a datamart on SQL 2008 R2 that has an existing partition function in place for our Fact data broken up by months. It was setup to go out as far as the end up 2012, but was never extended out past that. This wasn't discovered until about a month ago and by then the last file group (12/20012) had the data for 1/2013 through the current day. My previous attempts to resolve this using the split range option has resulted in a some pretty large tran logs which ultimately ended up in me running out of disk space. I'd be okay with leaving the large partition in place that contained a data for 12/2012-10/2013 if I knew I'd be able to roll 11/2013, 12/2013, etc in their correct partitions going foward. My partition schema/functions look like this:CREATE PARTITION SCHEME [ds_partition_fact_scheme_month] AS PARTITION [ds_partition_fact_fn] TO ([fact_2003_1_partition_filegroup…..[fact_2012_12_partition_filegroup]]CREATE PARTITION FUNCTION [ds_partition_fact_fn](int) AS RANGE LEFT FOR VALUES (1127, 1155……4718) (where each range value represents the first day of every month in our time dimension table and 4718 is 12/1/2012)I've added all the files/filegroups going out for 12 months in the future and tried running the following commands:ALTER PARTITION SCHEME ds_partition_fact_scheme_month NEXT USED [fact_2013_11_partition_filegroup]and ALTER PARTITION FUNCTION ds_partition_fact_fn() SPLIT RANGE (5054) (where 5054 represents 11/1/2013 in our time dimension table)When I run the alter partition function command, my tran log grows until I run out of disk space once again (over 200 GB in tran log) despite the fact that I haven't accumulated any data for Nov 2013 yet. I wouldn't have thought there should have been any data to be moved. Maybe I'm misunderstanding what's going on. Based on what I'm trying to accomplish, am I doing this right? I seem to be missing something.ThanksDave

Memory threshold for SQL server 2008

Posted: 02 Oct 2013 09:50 PM PDT

Hello,I am building a new virtual server - Windows 2008 R2 DataCenter.Installed SQL Server 2008 R2 Standard Edition - 64 bit.The "Maximum server memory" is set to 4096 MBThe server is having 6GB RAM.Why is SQL server crossing the threshold memory and utilizing most of the available 6GB RAM. With no other client connection yet and in idle server condition, currently I have got only 400 MB available. Why is it eating up most memory? Thanks,Bij
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog