Funniest Poll Result

Recently SQL Server magazine published the poll result of its poll. "Top 10 places to go for SQL Server Solutions". Lets see the results below:

1. sqlservercentral.com

2. bidn.com (Never heared about this. When I logged in it had 184 users online, lots of individual blogs have more number of people online thank this).

3. sqlserverpedia.com

4. sqlteam.com

5. sqlmag.com

6. sqlblog.com

7. stackoverflow.com

8. simple-talk.com

9. microsoft.com 

10. twitter.com (now you will know why i say this poll result funny. Thank god they didnt put this on the frst place.)
 

Security loop Hole in "Security Admin " Role

Today I found out one interesting article, which describes the loop hole in security admin role, which has an ability to elivate an user to "sys admin". Please find the article here.

How to Grant Access to a Snap Shot

If you try to directly grant access to a snap shot you will get the below error stating that the database is in read only mode.


Msg 3906, Level 16, State 1, Line 1
Failed to update database "" because the database is read-only.
Msg 3906, Level 16, State 1, Procedure sp_MSadduser_implicit_ntlogin, Line 39
Failed to update database "" because the database is read-only


The reason is that the snapshots are in read only mode due to which we cannot make changes to add or remove a user.


If its a windows user then create a login in both the primary and mirror server. Then grant access to this login to the primary database, which will be replicated to the mirror.

If its a SQL Server login then its a bit tricky as the SIDs come into picture. You have to create a login in the primary then script it out with the SID using sp_help_revlogin script and then run the script in the mirror. Then grant the required access in the primary, which inturn will get replicated and the required user will have read access in the mirror and the snap shot.


Thats it for the day folks. Hope to I bump into some similar kind of problem and find a solution and waste google's page by blogging it here again. Until then its me signing off from you. Happy administrating & happy sunday cya..





With Smiles
Santhosh

Removing Identity Column from a large table

To disable an identity column we basically rely on management studio, which intern create temp table transfer the data back and forth for us. But if the table is bit huge with millions of rows, you will get a time out error. Normally most of the solution given by people would be to create a temp table with no identity column transfer the rows into it and then rename the table.



The catch in this is , there is a very bright chance of you missing some relationship as you have to create them manually. So the innovative way invented by meeeeeeeeee is ;) (tanta da tanta da doiiiiii hehe back ground music) is the below


1. Create a teamporary table as shown below


select * into temp from main

2. select count(*) from temp


select count(*) from main

check if the rows are equal just in case




3. Truncate table main

Remove the identity property from the GUI.

4, ALTER TABLE main

NOCHECK CONSTRAINT ALL


INSERT into main 

select * from temp


ALTER TABLE main

CHECK CONSTRAINT ALL






5. select count(*) from main


select count(*) from temp

check if the rows are equal just in case




6. Drop table temp  -- Dont put the main table name then ur inviting a disaster


Thats it for the day folks. Hope to see you all soon with some thing uninteresting ;).Until then its me signing off



With Smiles
Santhosh

Understanding Max DOP Settings

I am back again folks with one more article. Donno now a days I am enjoying writing in english rather than tamil. Did I start thinking in english by any chance? (enof enof frst start thinking dude kind of thoughts).

Today we are going to talk abt MAX DOP and a interesting fact about it.There is a lot of wrong thinking that max DOP is to determine the no.of processors used by SQL Server. Its not basically affinity mask is the one to do that. This one jst to determine the maximum number of threads an operator (or an operation) in a query can use. Even the explanation in BOL is not quite upto the mark :(. Quite confusing isn't then check this out page then :).

This time again with a peculiar problem. I have set max DOP as 4 in one of my server interestingly I am seeing alteast 20-30 parallel threads. Believe me that scared me a lot. After lots of googling, talking to PSS engineer nothing worked out.

Atlast I went to my last resource social.msdn.com(Believe me folks one of the best place to hang around for sql server techies) a couple of MVPs replied back telling dude MAX DOP is not for query rather its applicable for each operator ooch..Linky dinky of this question.so no wonder my queries are executing in 30+ threads. So my recommended recipe to set up max DOP is to set a one for the server level and if you think a couple of your queries are performing worst due to this then set a one in the query level.

Thats it for the day folks. Hope to see you all soon with some interesting problem.Until then its me signing off with one full & one broken leg :)

With Smiles

Santhosh

Expect the UnExpected

I am back blogging buddies in a very short period with yet another expect the unexpected series worth one sleepless night. This time the culprit is me. The application team started reporting that the CPU is hitting 100%.

When I looked into the SQL Server the application was opening up the connections and it was not closing up and it was keep consuming the CPU. We just got that one of the stored procedure was the culprit.But it was doing nothing other than selecting and inserting some rows in a table which you would never doubt believe me. I was unable to run the profiler as it was getting hung due to 100% CPU and the existing connections show nothing in the profiler.

So just in case I updated the stats and it didnt help me in any ways. And we started digging digging diggingggggg nothing helped. We started beating around the bush like checking the dll if its closing the connections properly, checking for MSMQ bugs etc etc..Listening to nonsenses like RDBMS should start terminating idle connections after some time this that.. blah blah..

Atlast one of the developer say hare yaar check the number of rows in that table. I ran just count * and it kept running for hours. Amused by that I tried to kill the connection aww it started rolling back with message 0% completed 0 mins remaining :(. I was shocked to see a select query rolling back.

Amused with that I thought there will be some consistency issue in the table level alas I was right (atleast this time) there were consistency issue crap. The issue basically was the select query from the application keep running running running for days due to this bizzare reason and no errors nothing. I was amused that even the update stats went fine awww..

In fact now a days Im enjoying this expect the unepxected things :)..It was a good learning worth of one nights sleep :) happy administrating.


With Smiles
Santhosh

Expect the UnExpected

In the world of production DBA some great genious (this is called self boasting) said always expect the unexpected..I recently hit that thing again. We started hearing that the application is throwing some error like "No Record found" even though the records exists. Later we found out that in every exception the programmers (of a company Powered by intellectuals hahaha..very funny isint) have given a message "Records Not Found". Initially the application team said like its due time out error and we keep looking for a reason across all our environment.

Atlast I gave the option of profiler to check for errors and the flow of stored procedure. Alas we found out.. they are creating global temp tables on each connection with a fixed name and if two connections are calling that stored proc one is failing with the error temp table already exists.. and they are nicely displaying the message "Record Not Found" sweet isint..tats why i say expect the unexpected always :)..

Any have happy admnistrating..catch on another occassion soon until then its hv fun..its me logging off frm blogger ;)

With Smiles

santhosh

PASS Virtual Summit

PASS is organizing virtual summit starting from Feb 10th 2010. The below are the streams in which its going to happen

Application Development

Business Intelligence

Database Administration

Performance

Professional Development

Virtualization

Women in Technology

Its worth giving a try as you will be discussing with experts across the globe. The only catch here is that all the chapters are going to happen only around noon mountain time, which will be last night for DBAs across Asia Pac. Atleast next time they should consider us while organising these events. You can get more info in the following link.

Interesting Bug with dbcreator role

With a user with dbcreator role when you try to backup or restore the database using GUI you will get the below error.

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.


Looks like a bug as specified here . The work around is also provided there which is to restore using scripts.

Attaching DB with multiple files with out log file

okey its time to blog again after a long time. Thanks for all the folks who have been visiting my blogs even it has got no updates.

I had a weired situation in which one of our servers had a blue screen death following with a OS and disk corruption.So I lost a couple of my log files. Now I got to bring back the database with no backups in place.

I tried single attach file db and it wont work as my database is having multiple files. The veeresh found out a alternate solution as stated in the following link. Hope it will help a little bit. This wont work in SQL 2005 as you cannot update system tables.

It wont look good to end a post with out my usual promise I will try to blog more :).. Have fun, Happy Administration. Wishing you all an Incident Free Happy New year..


With Smiles
Santhosh