Renaming databases sloooow

We have SSAS 2005 setup which rebuilds our cubes each night on a single server. What we were trying to do is make it so that each night a second database is built and processed for the next days data, and then the previous days database is dropped and the new one renamed to the correct name. This was meant to minimize downtime. The problem is, renaming the database is slow (it takes hours!) .. is this a bug? We've tried renaming it through AMO and SQL Studio, both of which cause the CPU to go up to 50% for more than 2 hours before the rename completes. These databases have approx 8 cubes with many measure groups.

You should be able to change database name by issuing a simple XMLA command like:

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>CountMeasure</DatabaseID>
</Object>
<ObjectDefinition>
<Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ID>MyDb1</ID>
<Name>MyDb2</Name>
</Database>
</ObjectDefinition>
</Alter>

Try running Profiler and see what is happening during database rename.
During rename you should see bunch of Notification events about changes in database objects.

See how long in average each Notification Event takes. Is it possible rename of the database is getting blocked by some other operation?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Renaming a database

I've set my database to single user mode,and run:
sp_rename 'oldname' 'newname'
which works just fine.
Now I want to rename the oldname_data.mdf to
newname_data.mdf
To do that, I'm trying to detach the database, then go to
Explorer, rename the file and then reattach the database.
sp_detach_db 'newname'
returns an error:
Server: Msg 3702, Level 16, State 1, Line 1
Cannot drop the database 'newname' because it is currently
in use.
EXEC SQL DISCONNECT newname <or> 'newname'
returns an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'newname'.
How can I determine what has the database "in use" and
break that connection? (I'm on a 7.0 box in this case)
TIA MikeFirst open a query analyzer and connect to the SQL Server
in question. Then run this command:
SP_HOW2
search in the result and find any spid that currently
connected to the database and use this command ti kill
those users:
KILL <spid number>
That sould resolve your problem.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>I've set my database to single user mode,and run:
>sp_rename 'oldname' 'newname'
>which works just fine.
>Now I want to rename the oldname_data.mdf to
>newname_data.mdf
>To do that, I'm trying to detach the database, then go to
>Explorer, rename the file and then reattach the database.
>sp_detach_db 'newname'
>returns an error:
>Server: Msg 3702, Level 16, State 1, Line 1
>Cannot drop the database 'newname' because it is
currently
>in use.
>EXEC SQL DISCONNECT newname <or> 'newname'
>returns an error:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'newname'.
>How can I determine what has the database "in use" and
>break that connection? (I'm on a 7.0 box in this case)
>TIA Mike
>.
>|||I guess I'm not the only one with typo's today:) I think
that should be SP_WHO2
Vern
>--Original Message--
>First open a query analyzer and connect to the SQL Server
>in question. Then run this command:
>SP_HOW2
>search in the result and find any spid that currently
>connected to the database and use this command ti kill
>those users:
>KILL <spid number>
>That sould resolve your problem.
>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>http://www.microsoft.com/info/cpyright.htm
>
>>--Original Message--
>>I've set my database to single user mode,and run:
>>sp_rename 'oldname' 'newname'
>>which works just fine.
>>Now I want to rename the oldname_data.mdf to
>>newname_data.mdf
>>To do that, I'm trying to detach the database, then go
to
>>Explorer, rename the file and then reattach the database.
>>sp_detach_db 'newname'
>>returns an error:
>>Server: Msg 3702, Level 16, State 1, Line 1
>>Cannot drop the database 'newname' because it is
>currently
>>in use.
>>EXEC SQL DISCONNECT newname <or> 'newname'
>>returns an error:
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near 'newname'.
>>How can I determine what has the database "in use" and
>>break that connection? (I'm on a 7.0 box in this case)
>>TIA Mike
>>.
>.
>|||That is correct ;-) it is SP_WHO2.
Thanks for correction.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>I guess I'm not the only one with typo's today:) I think
>that should be SP_WHO2
>Vern
>>--Original Message--
>>First open a query analyzer and connect to the SQL
Server
>>in question. Then run this command:
>>SP_HOW2
>>search in the result and find any spid that currently
>>connected to the database and use this command ti kill
>>those users:
>>KILL <spid number>
>>That sould resolve your problem.
>>This posting is provided "AS IS" with no warranties, and
>>confers no rights.
>>http://www.microsoft.com/info/cpyright.htm
>>
>>--Original Message--
>>I've set my database to single user mode,and run:
>>sp_rename 'oldname' 'newname'
>>which works just fine.
>>Now I want to rename the oldname_data.mdf to
>>newname_data.mdf
>>To do that, I'm trying to detach the database, then go
>to
>>Explorer, rename the file and then reattach the
database.
>>sp_detach_db 'newname'
>>returns an error:
>>Server: Msg 3702, Level 16, State 1, Line 1
>>Cannot drop the database 'newname' because it is
>>currently
>>in use.
>>EXEC SQL DISCONNECT newname <or> 'newname'
>>returns an error:
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near 'newname'.
>>How can I determine what has the database "in use" and
>>break that connection? (I'm on a 7.0 box in this case)
>>TIA Mike
>>.
>>.
>.
>

Rename database

I have set the database to single user mode and want to rename the database.
However, it shows the following message:
To change the NAME, the database must be in state in which a checkpoint can
be executed.
I have restart the sql server service but it still shows the above message.
What should I do?
Best Regards,
IvanHi Ivan
I get a different error if I am not connected to the single user database in
SQL 2005, but I can rename a database in single user mode if I am connected
to it or if no-one else is connected to it.
I assume you are using SQL 2000? Is anyone else connected to the database?
John
"Ivan" wrote:

> I have set the database to single user mode and want to rename the databas
e.
> However, it shows the following message:
> To change the NAME, the database must be in state in which a checkpoint ca
n
> be executed.
> I have restart the sql server service but it still shows the above message
.
> What should I do?
> Best Regards,
> Ivan
>
>|||In SQL2k with single user I can rename database easily.
Please check other settings also.
Regards
Amish Shah|||The database is in Single User mode and I have restart the SQL Server 2000
to ensure no one is connect to the database.
However, it still shows that error message.
Ivan
"amish" <shahamishm@.gmail.com>
'?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
> In SQL2k with single user I can rename database easily.
> Please check other settings also.
> Regards
> Amish Shah
>|||Is the database READ_ONLY? It must be writable before it can be renamed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.microsoft.com> wrote in message
news:eg3ONfrdGHA.3484@.TK2MSFTNGP04.phx.gbl...
> The database is in Single User mode and I have restart the SQL Server 2000
> to ensure no one is connect to the database.
> However, it still shows that error message.
> Ivan
> "amish" <shahamishm@.gmail.com>
> '?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
>|||Hi
If you make the database the current database and then use
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
you should not need to reboot the server.
If you reboot the server and someone connects to the database before you
then you will have a problem.
John
"Ivan" wrote:

> The database is in Single User mode and I have restart the SQL Server 2000
> to ensure no one is connect to the database.
> However, it still shows that error message.
> Ivan
> "amish" <shahamishm@.gmail.com>
> '?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
>
>sql

Rename database

I have set the database to single user mode and want to rename the database.
However, it shows the following message:
To change the NAME, the database must be in state in which a checkpoint can
be executed.
I have restart the sql server service but it still shows the above message.
What should I do?
Best Regards,
IvanHi Ivan
I get a different error if I am not connected to the single user database in
SQL 2005, but I can rename a database in single user mode if I am connected
to it or if no-one else is connected to it.
I assume you are using SQL 2000? Is anyone else connected to the database?
John
"Ivan" wrote:
> I have set the database to single user mode and want to rename the database.
> However, it shows the following message:
> To change the NAME, the database must be in state in which a checkpoint can
> be executed.
> I have restart the sql server service but it still shows the above message.
> What should I do?
> Best Regards,
> Ivan
>
>|||In SQL2k with single user I can rename database easily.
Please check other settings also.
Regards
Amish Shah|||The database is in Single User mode and I have restart the SQL Server 2000
to ensure no one is connect to the database.
However, it still shows that error message.
Ivan
"amish" <shahamishm@.gmail.com>
'?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
> In SQL2k with single user I can rename database easily.
> Please check other settings also.
> Regards
> Amish Shah
>|||Is the database READ_ONLY? It must be writable before it can be renamed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.microsoft.com> wrote in message
news:eg3ONfrdGHA.3484@.TK2MSFTNGP04.phx.gbl...
> The database is in Single User mode and I have restart the SQL Server 2000
> to ensure no one is connect to the database.
> However, it still shows that error message.
> Ivan
> "amish" <shahamishm@.gmail.com>
> '?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
>> In SQL2k with single user I can rename database easily.
>> Please check other settings also.
>> Regards
>> Amish Shah
>|||Hi
If you make the database the current database and then use
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
you should not need to reboot the server.
If you reboot the server and someone connects to the database before you
then you will have a problem.
John
"Ivan" wrote:
> The database is in Single User mode and I have restart the SQL Server 2000
> to ensure no one is connect to the database.
> However, it still shows that error message.
> Ivan
> "amish" <shahamishm@.gmail.com>
> '?:1147525165.494414.185770@.j33g2000cwa.googlegroups.com...
> > In SQL2k with single user I can rename database easily.
> > Please check other settings also.
> >
> > Regards
> > Amish Shah
> >
>
>sql

Rename a cluster.. is it possible

We have a single instance cluster on 2 nodes say SQL1. We want to do a
side-by-side upgrade of the underlying hardware and we have another cluster
with 2 nodes already ready and its called SQL2. At the time of cutover, we
want to just rename SQL1 to SQLOld and SQL2 back to SQL1 so that our
applications can connect to SQL1 without having to go through any connection
name changes.
Can it be done ? We've done many of these on standalone SQL instances by
renaming servers.. Wasnt too sure how it works on a cluster. Especially
since we are not dependent on the node names and the applications just
connect to the Virtual SQL instance, I was not too sure how to go about the
same.
Thanks
We are using SQL 2000...
Also in SQL 2005, is it the same or is it better to do this easily ?
Why not just create a DNS alias and use that?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hassan" <hassan@.test.com> wrote in message
news:OI5fRApRIHA.1212@.TK2MSFTNGP05.phx.gbl...
We have a single instance cluster on 2 nodes say SQL1. We want to do a
side-by-side upgrade of the underlying hardware and we have another cluster
with 2 nodes already ready and its called SQL2. At the time of cutover, we
want to just rename SQL1 to SQLOld and SQL2 back to SQL1 so that our
applications can connect to SQL1 without having to go through any connection
name changes.
Can it be done ? We've done many of these on standalone SQL instances by
renaming servers.. Wasnt too sure how it works on a cluster. Especially
since we are not dependent on the node names and the applications just
connect to the Virtual SQL instance, I was not too sure how to go about the
same.
Thanks
We are using SQL 2000...
Also in SQL 2005, is it the same or is it better to do this easily ?
|||I guess we could, but also curious if a rename is possible too ?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OvufTE1RIHA.5136@.TK2MSFTNGP04.phx.gbl...
> Why not just create a DNS alias and use that?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OI5fRApRIHA.1212@.TK2MSFTNGP05.phx.gbl...
> We have a single instance cluster on 2 nodes say SQL1. We want to do a
> side-by-side upgrade of the underlying hardware and we have another
> cluster
> with 2 nodes already ready and its called SQL2. At the time of cutover, we
> want to just rename SQL1 to SQLOld and SQL2 back to SQL1 so that our
> applications can connect to SQL1 without having to go through any
> connection
> name changes.
> Can it be done ? We've done many of these on standalone SQL instances by
> renaming servers.. Wasnt too sure how it works on a cluster. Especially
> since we are not dependent on the node names and the applications just
> connect to the Virtual SQL instance, I was not too sure how to go about
> the
> same.
> Thanks
> We are using SQL 2000...
> Also in SQL 2005, is it the same or is it better to do this easily ?
>
|||Check out:
http://msdn2.microsoft.com/en-us/library/ms178083.aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hassan" <hassan@.test.com> wrote in message
news:u2ulJiASIHA.5128@.TK2MSFTNGP05.phx.gbl...
I guess we could, but also curious if a rename is possible too ?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OvufTE1RIHA.5136@.TK2MSFTNGP04.phx.gbl...
> Why not just create a DNS alias and use that?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OI5fRApRIHA.1212@.TK2MSFTNGP05.phx.gbl...
> We have a single instance cluster on 2 nodes say SQL1. We want to do a
> side-by-side upgrade of the underlying hardware and we have another
> cluster
> with 2 nodes already ready and its called SQL2. At the time of cutover, we
> want to just rename SQL1 to SQLOld and SQL2 back to SQL1 so that our
> applications can connect to SQL1 without having to go through any
> connection
> name changes.
> Can it be done ? We've done many of these on standalone SQL instances by
> renaming servers.. Wasnt too sure how it works on a cluster. Especially
> since we are not dependent on the node names and the applications just
> connect to the Virtual SQL instance, I was not too sure how to go about
> the
> same.
> Thanks
> We are using SQL 2000...
> Also in SQL 2005, is it the same or is it better to do this easily ?
>

Removing NULL in CASE ELSE

How to compress the output into single line
if got the below output using case..else
9155 michael NULL NULL NULL
9155 NULL NULL narain NULL
9155 NULL NULL NULL karthik
9155 NULL shumaker NULL NULL
Thanks
krishHi Krish,
Could you please be so kind give us more specific details that are you tryin
g?
Thanks in advance and regards,
"krish" wrote:

> How to compress the output into single line
> if got the below output using case..else
> 9155 michael NULL NULL NULL
> 9155 NULL NULL narain NULL
> 9155 NULL NULL NULL karthik
> 9155 NULL shumaker NULL NULL
> Thanks
> krish|||Try grouping by the first column and using min or max aggregate function.
Example:
select col1, min(case when ... end), min(case when ... end)
from table1
group by col1
AMB
"krish" wrote:

> How to compress the output into single line
> if got the below output using case..else
> 9155 michael NULL NULL NULL
> 9155 NULL NULL narain NULL
> 9155 NULL NULL NULL karthik
> 9155 NULL shumaker NULL NULL
> Thanks
> krish|||SELECT a, MAX(b), MAX(c), MAX(d), MAX(e)
FROM
(
SELECT 9155 as a, 'michael' as b, NULL AS c, NULL AS d, NULL AS e
UNION
SELECT 9155, NULL, NULL, 'narain', NULL
UNION
SELECT 9155, NULL, NULL, NULL, 'karthik'
UNION
SELECT 9155, NULL, 'shumaker', NULL, NULL
) AS sub
GROUP BY a
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"krish" <krish@.discussions.microsoft.com> wrote in message
news:A859613F-79B8-4BF2-840E-CA33BB9F42B2@.microsoft.com...
> How to compress the output into single line
> if got the below output using case..else
> 9155 michael NULL NULL NULL
> 9155 NULL NULL narain NULL
> 9155 NULL NULL NULL karthik
> 9155 NULL shumaker NULL NULL
> Thanks
> krish