SqlSpy

Script to Change the db owner of all databases

2011-08-11T12:45:00.000-05:00

I was in need of a script to change the owner of all the database in a server. I checked in the internet but couldnt find any thing so I made this by myself :)

DECLARE curDBNames CURSOR READ_ONLY FOR SELECT name FROM sys.databases where

name NOT IN('master', 'model', 'msdb', 'tempdb','resource') and state = 0

DECLARE @dbName as varchar(1000)

DECLARE @Sql as varchar(1000)

declare @dbOwnerName as varchar(100)

set @dbOwnerName = 'sa'

OPEN curDBNames

--PRINT @@ROWCOUNT

FETCH NEXT FROM curDBNames INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

set @Sql = 'use [' + @dbName + ']'

exec (@Sql)

set @Sql = 'sp_changedbowner ' + @dbOwnerName

exec (@Sql)

FETCH NEXT FROM curDBNames INTO @dbName

END

CLOSE curDBNames

DEALLOCATE curDBNames
Hope you like the script. Let me know if something doesnt work. As usual pls double check before you try this in your production environment.

SQL Server Training Videos

2011-01-01T11:40:00.000-05:00

This website is having some nice training videos classified on skill level.

Funniest Poll Result

2010-12-06T10:24:00.000-05:00

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

2010-11-16T09:55:00.000-05:00

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

2010-06-05T22:28:00.000-05:00

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

2010-06-03T21:46:00.003-05:00

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

2010-02-28T02:05:00.003-05:00

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

2010-02-17T11:15:00.001-05:00

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

2010-02-16T11:09:00.004-05:00

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

2010-02-08T13:47:00.003-05:00

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

2010-02-02T13:43:00.002-05:00

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

2010-01-01T08:30:00.001-05:00

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

Death of DBA Via SQL Server 2010

2009-07-02T22:49:00.005-05:00

It has been a long running rumor that Microsoft wants to eliminate the DBA layer rather the management of databases should be as easily like any other application. With the features list in SQL Server 2010 it sounds like they are trying to acheive that.

* Self tuning with the help of DMVs
* Best Practices policies enforced by DMVs

But the DBAs across the globe are hoping things will not get changed drastically due to these features. Lets hope for the best.

For More Details

1. Peterdoblers site

VS 2008,SQL Server 2005, Windows 2003 Free for Students

2009-01-17T02:28:00.001-05:00

As a part of its Dream Spark program Microsoft is giving away VS 2008, SQL Server 2005, Windows 2003 & Expression Studio for free. The terms & conditions include you should have a valid windows live id(if not you can get a one) & Microsoft has some checks to confirm that you are a student and you should pass those checks. You can very well download them here.

SQL Server 2005 SP3 + SQL Fest

2008-12-20T02:08:00.003-05:00

First of all thanks for all the visitors who have been patiently visiting my pages even though they have not been update for a while. Things were really hectic all these days. But the real reason would be obviously my lazyness and my learning curve has gone negative now a days :(.. Any have I need to kick off my learning curve hopefully will keep this blog updated.

Atlast the long awaited SP3 has been released. It was in CTP for a while and now it has been released to public. It can be downloaded from the following link. Click here to download

Recently attended the SQL Fest conference it was nice to share the thoughts with the sql development team. They have promised that they will try to conduct this event atleast once in a 6 months. They have also extended the venues from Hyd, Bangalore to Chennai and Delhi. You can get details about this at sqlcommunity.com or sqlfest.com

Dumbest SQL Server Interview questions

2008-04-29T03:31:00.001-05:00

Im attending interviews for the past month or so.. I had to face a couple of bookish dumb asses as interviewers(really i mean it), lets see why i mean so. It was a telephonic interview. lets see some of those

1. explain me the architecture of sql server,indexes, blah blah?
guys how do u want me to explain a product architecture over the phone...btw i have one doubt am im going to rewrite or code the SQL 2010, why the heck shd i know the architecture? have some sense guys.

2. what is ACID properties, codds rules, boyds rule, normalization rules, how many of these rules does sql server abide blah blah..?
come on guys have some limit to your bookish questions, tell me the names of any one data architect who designs the database using normalization rules, i challenge if some one in the world can say that okey this model is in the 2nd normal form or blah blah.. come on guys business rules are much more important than these..

i dont give a damn if sql server follows or abides 12 of codds rules.. its not my business or i cant do anything to make it abide all the rules you cant either.

3. how do you establish a one to one relation?
what they heck do you mean by how do you establish a one to one relation? is it like u try to ask me what is one to one and what is one to many? be clear.. when i asked back what do you mean by this.. he skipped to next questions..

4. what is a clustered index, non clustered index, how many of them you can have in a table, max columns, max tables, max indexes etc etc..?
thank god they didnt ask what is a table, column,row etc..(im MCITP with almost 7 yrs of exp in sql server.

so wt am i going to know the max tables, max indexes, max columns? i know sql server support decent enough to keep your business up and running. so wt r u going to by knowing all these things.. design a table with 1024 columns or try to run ur business critical database with max dbs, max tables, max columns, max indexes.. ask questions that make sense dont be a question thief from websites like this.. http://www.techinterviews.com/?p=42

5. how do i read the tlog using sql server native tools etc etc..?
oh so r u trying to test my confidence levels.. hmm i pity on you...

i can recollect jst the above ones.. the below are the examples to show cause their real time efficiency.

1. Im supporting the sql server of nasdaq something has happened and the traffic to the db has increased 10times. what will you do with out restarting and no spl hardware available?

I said u have to pre plan u cant do any thing dynamically.. then he told no do something.. i blabbed something like disabling unwanted jobs, if possible take other non critical dbs in that servers offline, make sure ur SAN subsystem doesnt get traffic from some other system blah blah.. I asked him back what would you do...

He told I would add an Microsoft Queue or will try to add a SQUID.. I asked so your are going to add a Queue to a highly volatile system like NASDAQ.. you are THE MAN, THE MACHINE, THE DBA.. and I give you a Hi5 * 100 for this wonderful reply.I pity on your client if you have designed any solutions to them.

2. one fine day you find out that one table is having performance issue in the production, what will you do.. i said index, locks blah blah..

he was not satisfied and he keep asking me wt else wt else.. at one point i gave up and asked what will you do.. i said the table design is wrong need to redesign the data model.. i was like whatttttttttttttt.. so u mean to say ull scrap ur data model and redesign.. WOW... you are THE SUPER MAN, THE SUPER MACHINE, THE SUPER COMPUTER, THE SUPEREST DBA and THE CLEAR WINNER.. may god bless you, your system, your client..

probabl you guys would have faced similar dumbest questions... comment if any such..

SQL 2008 Beta Website

2008-03-10T00:17:00.002-05:00

Im back to technical blogging after a very long gap. In the mean while SQL 2008 is out, most of the guys should be aware of that fact. I was just going through the mails from the atlanta.mdf(believe me its a very active sql server group that I know, they have monthly sessions and speeches, believe me they are wonderful.) you can find some good presentations of those sessions here .

From "atlanta.mdf" I happened to find very good website for SQL 2008 beta . I just tried to use it, its really nice. probably you guys can try to have a peek look into that website.

Beware while creating Maintenance plans using SP2.

2007-05-28T21:44:00.000-05:00

I created maintenance plans from my laptop which has got SP2 to the servers that doesnt have SP2 installed. I observed that the old backup files didnt get deleted, I was wondering if that is a bug too so I started to create the maintenance plans from the server itself. so guys beware of this fact. I dont even know if that is a bug or not. Need to verify the same in other servers.

The another glitch is when you create a maintenance plan using a machine with SP2 we cant edit it from a non sp2 client or server, this is the way SP2 has been designed to work so guys beware of this fact too.

Any thoughts or insights in this? Did some one else found out similar issues?

Critical Hot Fix on SP2

2007-05-28T21:42:00.000-05:00

The earlier release on SP2 had a maintenance plan bug (if you have downloaded the SP2 before March 5th), which did an early delete of the backup files, then MS released a hot fix then later on seems like they have found out another bug, which prevents from the maintenance plans to execute the checkdb. They have released another hot fix (It is supposed to be of a critical hot fix type.). The details of this can be read from .
this
I am thinking that MS is a bit hassle in all the releases of this SQL 2005 and that too with the SP2 release its not even couple of months they have released back to back hot fixes on this SP2. Now Im a bit scared to apply SP2 in my servers, since SP2 has no come back once we apply it.

log_reuse_wait_desc

2007-03-18T21:24:00.000-05:00

Last week I got a production call on transaction log full. hmmm there was no chance for that database log to get filled. I was wondering and when I looked into the database it said the transaction log is waiting for the replication to complete. I was like whatttttttttttt, since I had a replication build earlier and dropped it as it didnt work out good.

Then after doing some research I found out like there is a bug with the SQL 2005 in which even if we remove the replication, it doesnt remove it clearly. This issue was discovered in CTM and we are currently in SP1, the issue exists till now. It is being discussed in this MSDN form . It was really too scary ouchhhhhhhh.

Bug in SQL 2005 SP2

2007-03-07T10:35:00.000-05:00

SSUG.org has discovered a bug in the maintenance plan clean up schedule, which does the clean up a bit earlier than expected. Seems they have fixed the bug in the latest sp2 i.e the sp2 that is currently in the Microsoft site. If you have downloaded the sp2 prior to March 5th. Please download the latest fix pack or else Microsoft says that it might have some potential data loss.

You can download the latest fix pack here . The associated KB article is here

What Do I love in SP2

2007-03-02T00:20:00.000-05:00

Microsoft has atlast released its SP2, it has been awaited for so long and it went through a long testing cycle. The CTP was relased some where during mid of October. Lets see what is there in SP2, Now a days the trend as become such that they started releasing enhancements in service packs rather fixes. As you all know SP1 got mirroring similarly SP2 has tons of new features. I just give some of the features that I love it in SP2.

1. Maintenance Plan - The best thing in this and I love it. Now the plans look like our old 2000 ones, we can have multiple schedulers and bye bye to the shit of creating each plan to each job. The only glitch here is in the wizard we have only one maintenance clean up task, so we need to edit it to add the clean up task of the transaction logs :((.

2. There is no need to install business intelligent studio to edit/create the maintenance task hurrahhhhhhh!!!

3. The next best think I love is the reports oh man they are wow... now you can open them in their own window and the best thing is you can add custom reports in side your management studio. Come on guys its time to rock and role and build custom reports :).

4. Mann this feature you all will love it oohh earlier if you are generating scripts, you need to run the boring wizard twice if at all you need to create a script to create object and then drop object. Guess what now it has been changed you can generate scripts for both at the same time. Itsnt good guys you rock.

5. The another thing i have noticed is earlier when you run the sp_helpfile sp on a sql 2000 database, the result will come along with tons of spaces, I noticed it till sp1 but recently after installing sp2 i noticed that the spaces have goneeeeeeee. I surely give a wowooo to SP2 team for this. Its saves me from the pain of removing the unwanted spaces.

Word of Caution : Once SP2 is applied there is no come back path. You need to remove the whole instance and then do a fresh installation to remove the SP2, which is the biggest nah from me. While SQL 2005 was released the MS assured that there it will be easy to remove the service packs but not this time :((. I wouldnt dare to install it in my production. Just install the tools guys and have fun with them and wait for the screams of ohhh this broke this to settle down and then we can install it in our production.

You can read abt the features of SP2 here . you can download the SP2 here . The guys who developed/manage SQL Server blog here just in case if you guys need to give a howl at them :).

SQL 2005 SP2 CTP Released

2006-11-09T15:50:00.000-05:00

The CTP for the SQL 2005 SP2 has been released. Hope are like this SP2 will be available to public during the first quarter of 2007. Check out the Microsoft site for more details on the CTP.

Back to the days of CUI

2006-10-22T12:21:00.000-05:00

We are managing the users using Active directory groups for easy managability. Couple of days back one of my developer came to me complaining(come on give me a break the normal developer stuff) saying "oh man you revoked my access, I cant edit a stored procedure, I cant modify a table definition, what is going on?" I said "Dude I got tons of other stuff waiting on my mail box and you didnt mess me up too :)) why shd I revoke your access?"

He came back and with this error "Property DefaultSchema is not available for Database . This property may not exist for this object, or may not be retrievable due to insufficient access rights. (SQLEditors)"

oh gash what a hell this is? I started verifying his access rights every thing is perfect. Googlified and found out the following link. In this one of the microsoft accepts the bug in the SQL Server Management Studio, in the following conditions.

If you have users who are having logins based upon an Active Directory group.i.e they dont have access to the database directly rather, they will be part of an active directory group and that group will be having access to the database. In this case you cant specify a default schema to that group. In this case they cant use the management studio to create a table or a stored procedure.

So the solution would be use the t-sql. wow what a solution? Welcome back to the CUI world(Character user Interface).

Good Article on database files and file groups

2006-09-24T20:51:00.000-05:00

There are lots of articles, which gives tons of suggestion on designing the placement of the database and log files. In this article the author describes the advantages of placing the file groups separately and also describes the procedure to do that. In most of the articles the authors either do explain the advantages or just explain how to create a new file group, In this the author explains the both, in both SQL 2000 and 2005.