Saturday, April 20, 2013

[SQL Server 2008 issues] Normalization

[SQL Server 2008 issues] Normalization

Normalization

Posted: 19 Apr 2013 01:44 PM PDT

Is It possible To Identify A Table By Seeing it Whether It is Normalized Or Denormalized?

Master Database

Posted: 19 Apr 2013 01:06 AM PDT

Hi, I found that some developers created user created tables in master database recently. Can we create user created tables in master database. Incase yes, in what situations we need to create tables in master database. As far as i know we should not create tables in master database.This is the script i used to find tables in master database.SELECT * FROM sys.tables GO

Database Virtual Log File count

Posted: 19 Apr 2013 05:32 PM PDT

Hi,I am using sql server 2008 r2 express.I heard virtual log file count may be the problem for server performance.I searched sometime but i didn't come up with optimum VLF count.Some says it depends.Can anyone please help me to find out optimum VLF count for my databasesThank you

Updation using fuction

Posted: 19 Apr 2013 12:43 AM PDT

Hi I want to perform updation using fuction but I am not able to do it and it throw following error:Msg 443, Level 16, State 15, Procedure GetSTNValue, Line 21Invalid use of a side-effecting operator 'UPDATE' within a function.CREATE FUNCTION dbo.GetSTNValue(@FromStore int, @ToStore int)RETURNS varchar(10)ASBEGINDeclare @STN varchar(10)=0,@count int=0,@AWBNo varchar(10)=0SET @AWBNo = (SELECT ISNULL(gs.AWBNo,0)FROM GV_STNDetails gs WHERE gs.From_StoreCode = @FromStore AND gs.TO_StoreCode = @ToStore)SET @count = (SELECT gsv.CountValue FROM GV_STNCountValues gsv WHERE gsv.StoreCode = @FromStore)IF @AWBNo = 0BEGINIF NOT EXISTS (SELECT ISNULL(gs.STNNO,0) FROM GV_STNDetails gs WHERE gs.From_StoreCode = @FromStore AND gs.TO_StoreCode = @ToStore)BEGInSET @STN = CONVERT(varchar,REPLACE(STR(@FromStore,4),' ',0)) + CONVERT(varchar,REPLACE(STR(@count + 1,5),' ','0'))--SELECT @STN--RETURN (@STN)UPDATE GV_STNCountValuesSET CountValue = @count + 1WHERE StoreCode = @FromStoreENDELSEBEGINSET @STN = (SELECT gs.STNNO FROM GV_STNDetails gs WHERE gs.From_StoreCode = @FromStore AND gs.TO_StoreCode = @ToStore)--RETURN (@STN)ENDENDELSEBEGINDECLARE @oldSTN varchar(10)SET @oldSTN = (SELECT gs.STNNO FROM GV_STNDetails gs WHERE gs.From_StoreCode = @FromStore AND gs.TO_StoreCode = @ToStore)SET @STN = CONVERT(varchar,REPLACE(STR(@FromStore,4),' ',0)) + CONVERT(varchar,RIGHT(@STN,6)+ 1)--RETURN (@STN)END RETURN (@STN)END

Help with Idea on Round Robin TSQL solution

Posted: 19 Apr 2013 01:58 PM PDT

Hi SQLServerCentral,Here is what I am trying to accomplish and am trying to come up with an approach.I have a table called "RoundRobin" and this holds the round StaffID and StudentCount.What I would like to do is, using the current student count per staff member distribute additional students to each staff member based on load.So for example I would like to redistribute 50 students to the staff members based on load.What I am hoping to get is a number of students that should be redistributed based on the input number (say 50 in example). RoundRobinID StudentCount Student New Count Number of new students1 1 20 192 4 20 163 5 20 154 25 25 0 35 85 50Any help would be appreciated.IF OBJECT_ID('dbo.RoundRobin', 'u') IS NOT NULL DROP TABLE dbo.RoundRobincreate table RoundRobin( StaffID INT IDENTITY(1,1) PRIMARY KEY ,StudentCount INT)INSERT INTO dbo.RoundRobinSELECT 5UNIONSELECT 25UNIONSELECT 1UNION SELECT 4select *from dbo.RoundRobin

SQL 2008 R2 Backup Question - Log File

Posted: 19 Apr 2013 10:27 AM PDT

I'm helping a friend troubleshoot an issue with a SQL 2008 R2 database that is sitting behind SharePoint. Currently, SQL is loaded on a server that is using RAID 1 for the OS (13GB space free) and RAID 5 for data (O GB free). There are 8 log files totaling approximately 100 GB (6-11 GB per file). The automated backups stopped running over a month ago b/c the log files kept growing and maxed out the space on the RAID 5 array. We tried to shrink the logfile, but that option is grayed out in SSMS (possibly b/c the log file has not committed?). The drive bays in the server and motherboard will not support additional drives. Can I plug in a USB drive and backup the log file to the USB drive?I'm looking for options to backup, shrink, the log file so that we can get SharePoint, SQL backonline, and then adjust the backup strategy longer term to prevent this from happening again.Appreciate your feedback!Michael

Recommend Specs for Reporting Server

Posted: 19 Apr 2013 10:49 AM PDT

I've been tasked to figure out the specs for the reporting server. I was thinking of loosely basing it off our production server, which has the following specsProcessor: Intel Xeon X7350 @ 2.93 GHz (2 Processors)Memory: 32 GBOperating System: Windows Server 2008 R2 Server Standard (64 Bit).Storage: 1.3 TBAny suggestions would be great. The reporting database is going to be pretty small, but I want a copy of production database, in case I ever have to do ad hoc reporting.

help searching multiple words

Posted: 19 Apr 2013 01:08 AM PDT

hello, i am trying to make a procedure that returns values if and only the query matches all words .. EX:[code="sql"]declare @Names TABLE( name varchar(100))DECLARE @Searchs TABLE( word varchar(100))insert into @Searchs (word) select items from dbo.Split('John Andrews', ' ')insert into @Names values ('John Andrews')insert into @Names values ('John Adams')insert into @Names values ('John Matthews')insert into @Names values ('Mark Snow') select name,word from @Namesinner join @Searchs on name like '%' + word + '%' [/code]this produces the following result [code="other"]name wordJohn Andrews JohnJohn Adams JohnJohn Matthews JohnJohn Andrews Andrews[/code]my goal is trying to return on 1 result - John Andrews.what do i need to do to achieve this ?PS: Is this "procedure" advisable to search multiple words ?Thank you in advance.

What causes timestamp/rowversion to update?

Posted: 19 Apr 2013 06:23 AM PDT

We've got transactional replication setup on a number of tables in a database from one SQL 2008 server to another. Many of the tables have a timestamp/rowversion column defined, and in our table replication options, we have "convert timestamp to binary" selected to make sure the values stay consistent between the two databases.Periodically, I've noticed that there will be a large backlog of replicated transactions to deliver, and looking through at what the replication account is doing, the vast majority of the operations are just updating the timestamp column to a new value.We're trying to figure out what might cause just the timestamp/rowversion column to update on the source db, and sadly I'm not finding a ton of info. It appears that standard maintenance operations (index reorgs/rebuilds, etc) don't update it, anyone have any other suggestions?

Transactional replication

Posted: 19 Apr 2013 06:01 AM PDT

today we lack of disk space for distribution database primary file and we added spacebut during no space replication failed, agent thrown errorafter some log reader and dist agent started their own and running finemy question is will agents picksup where they left off?

Script Table As -> ALTER To - grayed out??? Why?

Posted: 14 Jul 2010 06:52 AM PDT

I am using SSMS 2008 and went to add some new columns to a table.I can do this in the design view, but why can't I script an ALTER statement?Just adding new columns shouldn't require the table to be dropped or suffer any "massive changes"?I Googled a bit and it said if I use design it will copy all the data to a temp location, drop, then recreate the table.So I assume it will preserve all PKIDs, but what if I had a 16 TB table, that would be a MASSIVE disk I/O operation...Am I missing something here?

Revoke Grant permission to DB_OWNER

Posted: 19 Apr 2013 03:37 AM PDT

Hi,How can I 'REVOKE', the 'GRANT' permission to DB_OWNERS members on SQL SERVER 2008 R2 ?

You will not be able to switch out or switch in data in this table since the table has non storage aligned indexes on it.

Posted: 19 Apr 2013 04:19 AM PDT

html,body{padding:0;margin:0;font-family:Verdana,Geneva,sans-serif;background:#fff;}html{font-size:100%}body{font-size:.75em;line-height:1.5;padding-top:1px;margin-top:-1px;}h1{font-size:2em;margin:.67em 0}h2{font-size:1.5em}h3{font-size:1.16em}h4{font-size:1em}h5{font-size:.83em}h6{font-size:.7em}p{margin:0 0 1em;padding:0 .2em}.t-marker{display:none;}.t-paste-container{position:absolute;left:-10000px;width:1px;height:1px;overflow:hidden}ul,ol{padding-left:2.5em}a{color:#00a}code, pre{font-size:1.23em}Hi,I did partitioning on a table on date column asFILEGROUP= APPLE.ndf FILES= APPLE_2010, APPLE_2011, APPLE_2012, APPLE_2013now i am trying to implement sliding window partitioning with partitioning wizardwhen i tried opening manage partition i was not able to select the option for sliding windowYou will not be able to switch out or switch in data in this table since the table has non storage aligned indexes on it.can you please help me in making sliding window partitioningThank all

reason for this???

Posted: 19 Apr 2013 03:39 AM PDT

I've recently run across this structure in a database that I'm migrating.[code="sql"]create table fktest ( dept int not null, dps int not null, constraint [pkfktest] primary key clustered (dept,dps), constraint [fktester] foreign key (dept, dps) references fktest (dept,dps) ) on [Primary] [/code]Is there a logical reason to have a foreign key that references itself when it's also the primary key in a table? Is this used for some reason? It just doesn't make sense to me.Thoughts?

Questions about extended events

Posted: 01 Oct 2012 11:30 AM PDT

Hey all,I am trying to learn more about extended events. I have been using SQL Profiler to help me troubleshoot SQL performance issues and errors in my Visual Studio projects. I learned about extended events recently and would like to use them instead of SQL Profiler. Is this an appropriate use of extended events? So far, I've been having some trouble using it.My understanding is that you can only view results while the extended events session is running, and that once the session is stopped, the results are discarded. How can I prevent this from happening? I would like to be able to view the events after the session is stopped.Is there a way to do this? Otherwise, while the session is running, is there a way to export the results? Seeing as how the results will be in XML, I was thinking of using the Excel XML Source Pane to parse them. What do other people use? I would greatly appreciate any advice that anyone can provide. Thanks.

Deadlock on update command

Posted: 18 Mar 2013 10:33 PM PDT

I have the following dead lock happening quite frequently at 3-4 times a day. The deadlock seems to happen on 2 things one being an index on the status column keylock hobtid=72057605790367744 dbid=9 objectname=dbname.dbo.orderha indexname=IX_status id=lock2189e7200 mode=S associatedObjectId=72057605790367744the other on the primary clustered keykeylock hobtid=72057602492792832 dbid=9 objectname=dbname.dbo.orderha indexname=PK_orderha id=lock1399f2200 mode=X associatedObjectId=72057602492792832DEADLOCK INFO:Node:1 KEY: 9:72057602492792832 (7900b77d3449) CleanCnt:2 Mode:X Flags: 0x1Grant List 1:Owner:0x00000001A34042C0 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:67 ECID:0 XactLockInfo: 0x00000002643C19B0SPID: 67 ECID: 0 Statement Type: UPDATE Line #: 1Input Buf: Language Event: [b]update orderha set status=2 where sessionid='7560129' and orderha=1[/b]Requested by:ResType:LockOwner Stype:'OR'Xdes:0x00000001A183B780 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy:(0x00000000D7EAC538) Value:0x118db7c0 Cost:(0/0)Node:2 KEY: 9:72057605790367744 (7a00de2866cc) CleanCnt:2 Mode:S Flags: 0x1Grant List 0:Owner:0x00000002E14CBCC0 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:64 ECID:0 XactLockInfo: 0x00000001A183B7C0SPID: 64 ECID: 0 Statement Type: SELECT Line #: 1Input Buf: Language Event: [b]select rsn from orderha where sessionid='7558101' and status < 3[/b]Requested by:ResType:LockOwner Stype:'OR'Xdes:0x00000002643C1970 Mode: X SPID:67 BatchID:0 ECID:0 TaskProxy:(0x0000000281984538) Value:0x42de2bc0 Cost:(0/456)Victim Resource Owner:ResType:LockOwner Stype:'OR'Xdes:0x00000001A183B780 Mode: S SPID:64 BatchID:0 ECID:0 TaskProxy:(0x00000000D7EAC538) Value:0x118db7c0 Cost:(0/0)Should I use a Lock hint to force a block rather then encounter a deadlock , such as UPDLOCK? or HOLDLOCK?

Parent-Child But Not All Have Children...?

Posted: 19 Apr 2013 12:46 AM PDT

Here's a tricky one:I have to show values for each of our customers. Data comes from a table something like this:[font="Courier New"]Customer_id Value1 1002 1503 10004 300...10 12011 15012 100013 200[/font]etcMost customers just have one account, but some have multiple "sibling" (or "child") accounts. Those with "children" are mapped in a table like:[font="Courier New"]Customer_id (Parent) Child_id1 101 111 122 133 143 155 16[/font]etcWhat I need to do is display a list showing all accounts and their own values, but if an account is a Parent it needs to show the value of itself and all its children added together:[font="Courier New"]Customer_id Value1 1370 (100 + 120 + 150 + 10002 350 (150 + 200)3 1000...10 12011 15012 100013 200[/font]If a Parent doesn't exist in the Parent-Child mapping table all we show is its own value[font="Courier New"]Customer_id Vaue4 300[/font]This seems like it requires a convoluted query!Any ideas?Thanx!

Advice on problems with "Client Processing Time"

Posted: 19 Apr 2013 12:34 AM PDT

Hello all,I've encountered a strange problem on one of my test SQL Servers.It is a VM box on a SAN, with Windows Server 2008, SQL Server 2008 R2, 16GB RAM and 4 CPU cores allocated.The hardware allocation is more than sufficient for the database that is running on it, and is similar to the current production server.When I run a test query (which is essentially returning the entire results of a view), it takes 9-10 seconds to return the results.However, when I run the same query on the production server, it takes 1-2 seconds.I've monitored the hardware while the query was running and also enabled execution plan and client statistics.I/O is less than the production box and the memory utilisation anc CPU utilisation is next to nothing.The only difference I can see between the queries run on each, is that the CLIENT PROCESSING TIME is 3 times as long on the test box.Interestingly, to make matters even more murky, I can run the query from my own PC (within Management Studio) and the query returns the results in 4-5 seconds (less than half the time it takes to run directly on the server, but still twice the time in production).Can anyone recommend some good places to start looking, to uncover what the underlying problem could be?Many thanks in advance for any suggestions...:)

Comma separated lists

Posted: 18 Apr 2013 11:37 PM PDT

Hi allI've got a couple of queries that use quite large comma separated lists in the WHERE clauses.I've seen (somewhere) a way of putting these into some sort of variable and using the variable instead of the entire list in the WHERE clause.Would someone be kind enough to point me in the right direction?

Sandbox environment for customers

Posted: 19 Apr 2013 01:12 AM PDT

I am looking for ideas on how to solve few problems we have with our customers creating sandbox environments. We are running an ASP.NET web application along with a windows server and a SQL Agent job configured to run against a production SQL Server database. Customers can create a parallel setup and restore the production database to a new instance. Once this is complete everything works great and they get full functionality as planned. Our problems start when the sandbox application starts performing actions that should only happen in the production DB. So we may have schedule tasks that could export to the same folder location and file, send emails and or transfer data to other production sites. All of these should be functional need to be functional in the sandbox application but it should only be running newly created tasks or configuration that had been set up after the restore took place.I can come up with a unique solution for each of the areas where I have problems. I can write a script that could be run to disable or change all of these areas, but it is a manual step that would depend on the customer running when the environment is restored. I am hoping for a more global solution of preventing or disabling things in a sandbox environment. Even something like a restore trigger that could automatically run a script.How do others handle such issues?

Moving data from one mdf to multiple ndf files

Posted: 19 Apr 2013 12:35 AM PDT

I have a database with one 110g mdf file. I would like to stripe the I/O across five 25g files and purge the data out of the 110g file so theyare all the same size. What is the easiest way to accomplish this task, if possible?

SQL disk config

Posted: 19 Apr 2013 12:04 AM PDT

Hello - I need to approve a SQL disk configuration - I know it is best to put data, logs and tempdb on seperate drives but not sure how to determine size.This is what was suggested by project manager...E:\ Data - 10 TBF:\ Logs - 5 TBG:\ TempDB - 5 TBThe software is websense and tracks we activity so it will be writing a lot of records.What is the best way to determine how to size the drive for logs and tempdb?Thanks in advanceDave

Strange datetime conversion issue

Posted: 16 Apr 2013 04:36 PM PDT

I have been using a query that joins several raw tables (nvarchar datatypes), has several conversions, nested queries, CASE WHEN statements. In other words it is messy, but I didn't create it nor do I have time to recreate it properly. It has been generating a very strange conversion error (the cause not the error itself is strange) where there is some kind of datetime conversion error when converting a string datatype. I have debugged every single field from every table in the query that is either converted explicitly or implicitly (these have all been updated to explicit conversions), with no problems present.The error is very strange because if I change the select fields (none of which are converting to a datetime implicitly or otherwise) to * the error message goes away, in fact if I simply add ,* after the last field in the select list it also goes away. However I can't use this because don't want every single field from every table in the query.Further, if I change the Select statement to SELECT TOP XXX where XXX is greater than the number of rows returned the error still does not occur. But if I use SELECT TOP 100 PERCENT the error occurs again.This made me think that it might be an error in the query execution cache. Which I cleared and tried again with the same errors still occurring. The database is set up to use Korean_Wansung_CI_AS on SQL Server Enterprise Edition v10.50.1600.1This problem is not critical as I can set it to use SELECT TOP XXX such that XXX is far larger than will ever be actually required. It was just very strange behavior that I had never seen before.Unfortunately, I can't provide the code or data both are quite confidential, however I'm quite sure that this is not directly related to the code itself, as the code is working using the two scenarios mentioned.Has anyone seen this behavior and/or know of the cause?

how i can store marathi font in sql server

Posted: 18 Apr 2013 10:29 PM PDT

I am going to design a application having front end with VB6.0 and Back end with SQL SERVER in. And I want the complete interface of that application in marathi language.when I enter the marathi character in data base and execute it, it shows "????????" like this.What I do to enter and store the marathi character in Database?

Warning message for user having no db_owner permission but having full permission for the table

Posted: 18 Apr 2013 09:16 PM PDT

I am accessing my sql server 2008 r2 express server using SSMS. I have about 5 logins. For a particular login i hav all server roles other than sysadmin.In user roles it has only "public" permission for a database.For that database in properties->permission i gave all values 'grant '. Then for some table i restricted the access in table properties.Now when i login by using this user i can access the database and access all tables other than for which i have completely restricted access. So it is what has to be according what permission i gave.only problem is when i access other tables and open designs i get message like"You are not logged on as the database owner or system administrator. You might not be able to save changes to tables that you do not own."But i am able to edit design. And after editing design when i save i again get message like "Warning were encountered during the pre-save validation process, and might result in a failure during save. Do you want to continue attempting to save?" .Now on clicking save the design gets saved with the changes i have done. It is ok that it got edited because the user has permission for that table.But why does those message come even if i have full permission to edit database and design.(even though i don't have db_owner) ?Can i avoid those messages? Or is there any problem to database if i edit in user with no db_owner permission?

logical and physical file names of all databases

Posted: 03 Apr 2011 01:00 PM PDT

Is there a query or sp that I can pull all databases on the server along with their logical file names and physical file names? Thanks

Convert file from ANSI to UNICODE

Posted: 05 Apr 2013 01:10 AM PDT

Currently, we generate file in ANSI. Is there a setting we need to change to generate files in UNICODE? or how can we convert ANSI files to UNICODE?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog