I am using transaction replication between a transaction and reporting database server. When I use a snapshot to initialize my subscribers, I currently get a lot of deadlocks during the snapshot creation. I am considering using a database backup instead. Can anyone tells me how to reduce the table locks that I am getting during snapshot creation or advice on using database backups?
I'm just wondering what any pros and cons of using multivalue columns in a database are.
I'm designing a database which will have a column for FABRIC_TYPES_AVAILABLE for a certain FURNITURE_ITEM. Each FURNITURE_ITEM can have multiple FABRIC_TYPES_AVAILABLE of course. So I was just going to store a 2 or 3 digit number of the FABRIC_TYPES_AVAILABLE in that row. So I would have something like....34,24,453,32,23,45,67,65,43,21,21,45.
Anyway....thanks in advance for any information. Links I could read would be great too...b/c I did do a bit of searching, but didnt find much.
Can someone provide information or a link to information regarding the pros and cons of saving files directly into a SQL 2005 database?
I'm actually for saving files to a database (cleaner implementation then just saving the location then having to get the file, etc), but my project manager is not convinced so I need to make an argument for (or against depending on what I actually find out) using varbinary data type.
Thanks.
My company is thinking about moving to a product that uses Microsoft SQL Server and I have been asked to find out what are the Pros and Cons of the product, if any.
Any feedback is appreciated.
Thank You
Pam
Anyone know where I could find some good articles about pros and cons of upgrading SQL 7 to SQL 2K?
Our boss suddenly got this bug up his rear that we need to upgrade. :(
Probably because we are to a point where everything is running smoothly, and he needs to throw a monkey wrench in it.
We are a very small shop, and are not stressing SQL 7 in the least. I am interested to read some articles about SQL 2K, because right now, I can't see any possible reason an upgrade would be worth doing.
Thanks.
I have a requirement where I need to perform a query for positioninformation. But for some types of entries, I need to "expand" the rowto include additional position rows. Let me explain with an example:An index is a security that is made up of components where eachcomponent has a "weight" or a number of shares. So if I have 1 share ofthe index, I have X shares of each component.AAPL is an Equity, CSCO is an Equity, SPY is an Index. Lets say thatSPY has one component, AAPL, with shares being 10. (1 share of SPY = 10shares of AAPL).So, I do some trading and I end up with positions as follows:+10 AAPL-5 CSCO+2 SPYThe query I need returns:+10 AAPL-5 CSCO+2 SPY+20 AAPL (from 2 SPY * 10 shares)which becomes (after grouping):+30 AAPL-5 CSCO+2 SPY-----------------------------------------Based on that criteria and the following schema (and sample data):-- Drop tablesDROP TABLE [SecurityMaster]DROP TABLE [Position]DROP TABLE [IndexComponent]-- Create tablesCREATE TABLE [SecurityMaster] ([Symbol] VARCHAR(10), [SecurityType] VARCHAR(10))CREATE TABLE [Position] ([Account] VARCHAR(10), [Symbol] VARCHAR(10), [Position] INT)CREATE TABLE [IndexComponent] ([IndexSymbol] VARCHAR(10), [ComponentSymbol] VARCHAR(10), [Shares] INT)--Populate tablesINSERT INTO [SecurityMaster] VALUES ('AAPL', 'Equity')INSERT INTO [SecurityMaster] VALUES ('MSFT', 'Equity')INSERT INTO [SecurityMaster] VALUES ('MNTAM', 'Option')INSERT INTO [SecurityMaster] VALUES ('CSCO', 'Equity')INSERT INTO [SecurityMaster] VALUES ('SPY', 'Index')INSERT INTO [Position] VALUES ('001', 'AAPL', 10)INSERT INTO [Position] VALUES ('001', 'MSFT', -5)INSERT INTO [Position] VALUES ('001', 'CSCO', 10)INSERT INTO [Position] VALUES ('001', 'SPY', 15)INSERT INTO [Position] VALUES ('001', 'QQQQ', 21)INSERT INTO [Position] VALUES ('002', 'MNTAM', 10)INSERT INTO [Position] VALUES ('002', 'APPL', 20)INSERT INTO [Position] VALUES ('003', 'SPY', -2)INSERT INTO [IndexComponent] VALUES ('SPY', 'AAPL', 25)INSERT INTO [IndexComponent] VALUES ('SPY', 'CSCO', 50)INSERT INTO [IndexComponent] VALUES ('QQQQ', 'AAPL', 33)-- *****************************-- Based on the rules:-- 1) Index positions appear like other positions (account /symbol) pair, but-- its components show up as new rows of account (of index),symbol (equal--to component symbol), position (equal to shares * index position)-- 2) One row for each account / symbol pair (GROUP BY account andsymbol, SUM position)-- Expected output (without grouping) (sorted by account / symbol)-- 001 AAPL 10-- 001 AAPL 375 (component shares * index position) (25* 15) (SPY)-- 001 AAPL 693 (component shares * index position) (33* 21) (QQQQ)-- 001 CSCO 10-- 001 CSCO 750 (component shares * index position) (50* 15) (SPY)-- 001 MSFT -5-- 001 QQQQ 21-- 001 SPY 15-- 002 AAPL 20-- 002 MNTAM 10-- 003 AAPL -50 (component shares * index position) (25* -2) (SPY)-- 003 CSCO -100 (component shares * index position) (50* -2) (SPY)-- 003 SPY -2-- Expected output (with grouping account / symbol) (sorted by account/ symbol)-- 001 AAPL 1078-- 001 CSCO 760-- 001 MSFT -5-- 001 QQQQ 21-- 001 SPY 15-- 002 AAPL 20-- 002 MNTAM 10-- 003 AAPL -50-- 003 CSCO -100-- 003 SPY -2---------------------------------------------Is a UNION the best way to perform the query. What are the pros andcons? What, if any, is a better way?SELECT[Account], [Symbol], SUM([Position]) AS [Position]FROM(SELECT[Account], [Symbol] , [Position]FROM[Position]UNION ALLSELECTP.[Account] , IC.[ComponentSymbol] AS [Symbol] , (P.[Position] *IC.[Shares]) AS [Position]FROM[IndexComponent] ICJOIN[Position] PONP.[Symbol] = IC.[IndexSymbol]) DGROUP BY[Account], [Symbol]ORDER BY[Account], [Symbol]
View 5 Replies View RelatedHi alli am building a SQL 2000 database that it is proving a littlechallenging, i have companies with multiple addresses, phone numbers,owning mine sites etc and also joint ventures so maybe you get thepicture with a few design issues that i ma encounteringMy queriy is about a primary key identity, and which one to use withrespect to either the identity data type or the unique identifier ,I am aessentiall building an address table to hold all multipleaddresses as well as phone numbers etc, so my desire to have a uniqueidentity for each record is very important.My view is i will run in to violation errors by just using the tableidentity data type, i could i suppose use composit primary keys butthat may have a performance impact, although thiis will not be a hightransaction database.Does anyone know about performance issues regarding each identitysolution, by using a generated 16 bit identifier there are going to behuge numbers for the DB to verify. or am i worried about nothing?any views greatly appreciatedregardsGreg
View 17 Replies View Relatedcan anyone explain the Pros and Cons of Stored Procedures ??
thanks
Im about to start converting code to Stored Procedures for all my reports in Reporting Services. I was wondering what the pros and cons of this may be.
View 17 Replies View RelatedThanks for the help on the previous thread.
It seems to me that either by accident or design, SQL Server tends to steer you away from backing up directly over the network.
Are there reasons for not doing this because you obviously don't want to leave your backups on a local drive in case the drive fails?
Some possibilties that I can think of are:-
1. Local drives have faster access times and SQL backups can get quite large. I did a quick test and found that a netwrok backup takes 2 to 3 times longer than it does on a local drive.
2. Backing up on the netwrok could hog too much bandwidth. I haven't tested this and would be surprised it it's true.
3. There could be some reason that you don't want the Server and Agent services running under a domain account but want to leave them on the Local System account. I am not aware of any such reasons by the way.
4. Local drives persumably have a slightly higher availability than network drives. If the server is running, the drive should be available.
Like in the subject: What are the cons and pros of using nvarchar(max) versus ntext?
Does it have something to do with having to enable full text search perhaps in the latter case?
I have a main database...for this large Web site...and Im wondering
what would be the PROS and CONS of using another database (located on
the same, or on another SQL Server). Im just thinking this would be
good incase we ever needed to take some load off one of the servers.
Also, we will be integrating Community Server into this Web site. Of
course you know CS adds its own database objects which crowd up our
main database objects.
We were thinking of giving CS its own database also; bad practice, or....it doesn't matter much?
Thank you
I was wanting to know. I am making a site that might be come big. And me and this dude are considering pickering abotu GUID. I don't want to use them but he does. And I was wondering what should we do? I know nothing about guids
View 1 Replies View Relatedvinod writes "Q1>Should I apply filter in sequence(based on primarykey,not null,comparision,between clause) ?
-How to apply filter in correct format,and SQL server internally execute it.
Q2>Should I use 'is not Null' to be put at the last?
Q3>Should I use 'between clause' rather than relation operator i.e(empid>10 and empid<200)
Q4>Does filters of sequence has any impact on the query execution process
table1[pkey1,col1,col2,col3] -->pkey is pkey1
table2[pkey2,pkey1,col11,col12,col13]--->pkey is pkey2, and pkey1 is foreign key
CaseI->select table2.pkey2,table1.col1 ,table2.col12
from table1 inner join table2
on table1.pkey1=table2.pkey1
where (table1.col3>100 and table1.col3<300 ) and table2.pkey1=2020 and table2.col13 is not null
CaseII->select table2.pkey2,table1.col1 ,table2.col12
from table1 inner join table2
on table1.pkey1=table2.pkey1
where (table1.col3 between 101 and 299 ) and pkey1=2020 and table2.col13 is not null"
Are there any vices to using default constraints on all columns in your table.
For example an Int that defaults to 0
or a char or varchar that defaults to ''
I know that 0 and Null are not the same thing. But if your programs don't have the concept of NULL then you have to convert the NULL to zero.
So, DEFAULT CONSTRAINTS on every column. Is it good or Bad?
Thanks
Darin Clark
Hi All!We are doing new development for SQL Server 2000 and also moving fromSQL 7.0 to SQL Server 2000.What are cons and pros for using IDENTITY property as PK in SQL SERVER2000?Please, share your experience in using IDENTITY as PK .Does SCOPE_IDENTITY makes life easier in SQL 2000?Is there issues with DENTITY property when moving DB from one serverto another? (the same version of SQL Server)Thank you in advance,Andy
View 49 Replies View Relatedhi all,
I was wondering if anyone knows of any pros/cons on storing SSIS (2005) packages within SQL Server 2005 SP2.
We're contemplating the migration/storage of a large number of packages, a minority of which designed in dts (SQLServer 2000) and using third party activx components.
thanks much for any feedback,
Cosmin
We are in the process of replacing our primary production server. In the process of determining how SQL server is going to be structured, it has been suggested that I place all current and new indexes on a separate file group. These filegroups would then reside on a separate shelf on the server. What are the pros and cons of doing this?
View 2 Replies View RelatedIs it possible to load both the SQL 7 database and transaction logbackups to SQL 2000 ? I assume it will perform the upgrade during theload.Thanks,James
View 4 Replies View RelatedThe space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies View RelatedHi,
We had a healthy 2-way merge replication set up with a SQL 2005 publisher/distributor and a SQL 2000 subscriber which had been running for about a week. I re-created the publication so I could add automatic identity range management to an article. I sucessfully re-created a push subscription to a SQL 2005 subscriber but when I tried to create one to the same SQL 2000 machine that had been quite happy a few hours earlier, I kept getting this error during initialization: "bulk data stream was incorrectly specified as sorted". Nothing much has changed as far as I can see. Can anyone give me some pointers?
Thanks,
Pete
when have had a problem in the initialization of a Merge replication of 3.600.000 rows on SQL Server 2014.The sp_MSmakegeneration was generating a large number of X locks and the replication process was unable to start.Finally we solved the issue modifying thegeneration_leveling_threshold parameter to 0..But, by knowledge this was a known issue with older SQL Server Version.if is mandatory to set it to 0 due to ininizialize large Relicas?
View 3 Replies View RelatedI have configured three transactional replication one from DC - Data center & DB for database
DC1-DB1 to DC2-DB1 -- 1
DC2-DB1 to DC2-DB2 --2
DC2-DB2 to -DC2-DB3 --3
Before synching the data on DC2 databases I wanted to truncate / drop the objects on 1 and 2 setup as these are already participating in replication. Setup 1 and 2 are publisher as well as subscriber. I am not able to reinitialize these tables on these setup. I cannot use SSIS.
During merge replication initialization from SQL Server 2005 to SQL Server CE 3.5 on Windows Mobile 5.0
I am getting the following errors:
On Device
{"The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 21,Table name = COLLECTION ]"}
base {System.SystemException}: {"The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 21,Table name = COLLECTION ]"}
HResult: -2147217900
Message: "The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 21,Table name = COLLECTION ]"
NativeError: 26100
Source: "Microsoft SQL Server Compact"
{System.Data.SqlServerCe.SqlCeError: The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [ SQL statement = ALTER TABLE [dbo].[COLLECTION] ADD CONSTRAINT [PK_COLLECTION] PRIMARY KEY CLUSTERED ( [SERIAL_NUMBER], [STORE_CODE], [COLLECT_DATE] )WITH PAD_INDEX , FILLFACTOR = 90 ]}
Source: "Microsoft SQL Server Compact"
Server Logged:
...
2008/04/20 20:03:16 Thread=EA0 RSCB=5 Command=SCHK Hr=00000000 SyncCheck responding 0
2008/04/20 20:03:17 Thread=B90 RSCB=5 Command=OPNR Hr=00000000 End Of Data Set 1
2008/04/20 20:03:17 Thread=B90 RSCB=5 Command=OPNR Hr=00000000 Responding to Fetch, bytes = 50259
2008/04/20 20:03:17 Thread=970 RSCB=5 Command=CLOS Hr=00000000 Total Compressed bytes out = 50259
2008/04/20 20:03:17 Thread=970 RSCB=5 Command=CLOS Hr=00000000 Total Uncompressed bytes out = 390254
2008/04/20 20:03:17 Thread=970 RSCB=5 Command=CLOS Hr=00000000 Removing this RSCB 0
2008/04/20 20:03:19 Hr=00000000 Compression Level set to 1
2008/04/20 20:03:19 Hr=80004005 ACK:Error for DistributorSessionID = 80
2008/04/20 20:03:19 Hr=80004005 The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [,,,SQL stateme 28560
The same tables in a different on a different SQL box (test) replicate fine, but on this box (production) they do not. Any idea what would be the difference in the two environments?
I suspect that the "dbo." part in the "alter table" is what it is messing up on... how can I tell SQL CE not to include that schema prefix?
Thanks
We have a filtered publisher (SQL Server 2012) with several pull and push subscriptions that have been functioning fine for years.
We have just added a new pull subscriber from a remote server. We configured an alternate folder location on the subscriber (pointing to existing path on a remote server). We generated new snapshot and partitioned snapshot, and moved to subscribing server at alternate path.We're seeing a problem we've never seen before:
When the merge agent runs, it applies the schema and performs a few hundred bulk inserts, but then proceeds to 'download changes to subscriber' as though the snapshot is old (it is brand new and should have all transactions). We have never seen this before, i.e. all data is usually applied in bulk when creating a new or reinitializing an existing subscriber.
we marked the subscriber for re-initialization (from both the publishing and subscribing server) with a fresh set of snapshots. The same problem occurred.We have also tried in our test environment (which mirrors production) and the snapshot applies as expected (via bulk inserts).
(It might be relevant to note the publisher was recently upgraded in-place from 2005 to 2012.)
Getting below error most of the times .
"Server was unable to process request. ---> The transaction log for database 'xxxx' is full due to 'REPLICATION'."
I am doing below steps to clear the log everytime.
1.EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,@time = 0, @reset = 1
2.checkpoint
I am about to apply DBCC DBREINDEX to a large database that is part of transactional replication and synchronised every 3 minutes. What are the likely implication and what precautions I must take.
Thanks in advance
Regards
R Suresh
I setup a maintenance plan to create full backups every night and to backup the transaction logs every hour. In the backup log, it always fails on backing up the transaction log for Master and MSDB. I believe this is because the recovery model for these two databases is set to "SIMPLE". Is it ok to change the recovery model on these two system databases to "FULL"?
Thanks,
Ken Nicholson
I am trying to create a backup for the transaction logs on one
of the databases I support. The problem is in EM, the only
options it gives me are a Full DB Backup or a Differential DB
Backup. I have other DB's with TL backup's, why can't I on this
particular one?
I have 5 databases on my sql server and they are taking backups on everyday twice. ANd we are not taking any transactional log backups.Now i have very larger log files which are more than 6 GB each.And the databases are less in size compared to the logfiles.
Should I take transaction log backups with truncate option...
PLease suggest me the best stratagy i should follow to overcome the problem.
Thanks.
Yesterday I received a message from the Trans Log backup job that it could not complete because a full backup wasn't completed. I checked and a full backup was completed that morning 5:00 AM. This happened during the 4 PM run and again at 5 PM. There was enough space to do the backup, even though I did a shrink file on the log. I, also, ran another backup between 5-6 PM. Between both of these activities, the job completed during the 6 PM run. Is this a common occurance? Has anyone else seen this happen. I am curious. Any light that can be shed on this, would be greatlt appreciated. Tom.
View 6 Replies View RelatedWe had a siutation last night in our production environment that forced us to revert back to an earlier version of the database (before a major code rollout that failed). After restoring the days full backup (with NORECOVERY), and then restoring a DIFF backup (FULL RECOVERY and had checked Preserve Replication Settings)...the transaction replication failed.
Message #1
The replication agent has been successfully started. See the Replication Monitor for more information.
Message #2
2011-03-04 15:07:17.566 Copyright (c) 2008 Microsoft Corporation
2011-03-04 15:07:17.566 Microsoft SQL Server Replication Agent: logread
2011-03-04 15:07:17.566
2011-03-04 15:07:17.566 The timestamps prepended to the output lines are expressed in terms of UTC time.
2011-03-04 15:07:17.566 User-specified agent parameter values:
[code]....
I've tried reinitializing the publication/subscription and while that took brand new snapshots and copied it over to the replicated data server, it did not fix the problem.I read from a different post that I could try running "sp_replrestart" but that ran for about a half an hour and didn't appear to do anything but fill up our log files...did I not wait long enough?
The only thing I know to do at this point is to drop the publication on the production server and rebuild it completely (and with all the tables we're replicating that would take quite a bit of time.
I have a backup regime where i take a full backup of my production database once a day and then 3 min log backups, I backup my msdb and master once a week.
I need to write a script to scroll through a given directory and restore the log backups in date/time order(with no restore) until the last file and then restore.
I know this is availible in the backup history but if my SQl box crashes and i cant get a uptodate master and msdb the backup history will not be uptodate to restore the database to it's current state.
Can anybody help with a script? or give me any pointers of how to read in filenames in SQL (please remenber i am a SQL virgin).
Thanks
Barry.