Database coming up suspect.

Let me tell you the scenario. We are doing the following to ensure we have
the fastest recovery time possible if Windows updates applied to the box
crash it.
When we get ready to apply updates to a box we want to break the driver
mirror by pulling the second drive. That way if the updates we are about to
apply break something we can just put the other drive in. But when we do this
on our test boxes the SQL databases come up suspect. Can anyone help me?
These are the steps we take.
Shut SQL down > Turn off box > Pull drive > turn PC on > log onto windows >
â'Database is now Suspectâ'."Rhild" <Rhild@.discussions.microsoft.com> wrote in message
news:EFF4313C-521D-42F1-895C-5DA19E006470@.microsoft.com...
> Let me tell you the scenario. We are doing the following to ensure we have
> the fastest recovery time possible if Windows updates applied to the box
> crash it.
> When we get ready to apply updates to a box we want to break the driver
> mirror by pulling the second drive. That way if the updates we are about
> to
> apply break something we can just put the other drive in. But when we do
> this
> on our test boxes the SQL databases come up suspect. Can anyone help me?
> These are the steps we take.
> Shut SQL down > Turn off box > Pull drive > turn PC on > log onto windows
> >
> "Database is now Suspect".
>
In theory, if this is all you're doing, it should work. I've done similiar
things.
However, it sounds like you're either pulling the wrong drive, or Windows is
somehow changing the drive letter(s) when you do this.
Check the errorlog for more details.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Database coming up suspect.

Let me tell you the scenario. We are doing the following to ensure we have
the fastest recovery time possible if Windows updates applied to the box
crash it.
When we get ready to apply updates to a box we want to break the driver
mirror by pulling the second drive. That way if the updates we are about to
apply break something we can just put the other drive in. But when we do this
on our test boxes the SQL databases come up suspect. Can anyone help me?
These are the steps we take.
Shut SQL down > Turn off box > Pull drive > turn PC on > log onto windows >
“Database is now Suspect”.
"Rhild" <Rhild@.discussions.microsoft.com> wrote in message
news:EFF4313C-521D-42F1-895C-5DA19E006470@.microsoft.com...
> Let me tell you the scenario. We are doing the following to ensure we have
> the fastest recovery time possible if Windows updates applied to the box
> crash it.
> When we get ready to apply updates to a box we want to break the driver
> mirror by pulling the second drive. That way if the updates we are about
> to
> apply break something we can just put the other drive in. But when we do
> this
> on our test boxes the SQL databases come up suspect. Can anyone help me?
> These are the steps we take.
> Shut SQL down > Turn off box > Pull drive > turn PC on > log onto windows
> "Database is now Suspect".
>
In theory, if this is all you're doing, it should work. I've done similiar
things.
However, it sounds like you're either pulling the wrong drive, or Windows is
somehow changing the drive letter(s) when you do this.
Check the errorlog for more details.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Database coming up suspect.

Let me tell you the scenario. We are doing the following to ensure we have
the fastest recovery time possible if Windows updates applied to the box
crash it.
When we get ready to apply updates to a box we want to break the driver
mirror by pulling the second drive. That way if the updates we are about to
apply break something we can just put the other drive in. But when we do thi
s
on our test boxes the SQL databases come up suspect. Can anyone help me?
These are the steps we take.
Shut SQL down > Turn off box > Pull drive > turn PC on > log onto windows >
“Database is now Suspect”."Rhild" <Rhild@.discussions.microsoft.com> wrote in message
news:EFF4313C-521D-42F1-895C-5DA19E006470@.microsoft.com...
> Let me tell you the scenario. We are doing the following to ensure we have
> the fastest recovery time possible if Windows updates applied to the box
> crash it.
> When we get ready to apply updates to a box we want to break the driver
> mirror by pulling the second drive. That way if the updates we are about
> to
> apply break something we can just put the other drive in. But when we do
> this
> on our test boxes the SQL databases come up suspect. Can anyone help me?
> These are the steps we take.
> Shut SQL down > Turn off box > Pull drive > turn PC on > log onto windows
> "Database is now Suspect".
>
In theory, if this is all you're doing, it should work. I've done similiar
things.
However, it sounds like you're either pulling the wrong drive, or Windows is
somehow changing the drive letter(s) when you do this.
Check the errorlog for more details.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Database Backups and Transaction Logs

Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks
You must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks
|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>
|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>
|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
was
>
|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set up
> that functionality as part of a job. I will add a nightly backup. We have to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> was
>

Database Backups and Transaction Logs

Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
ThanksYou must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
was[vbcol=seagreen]
>|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set u
p
> that functionality as part of a job. I will add a nightly backup. We have
to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> was
>

Database Backups and Transaction Logs

Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
ThanksYou must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> > Problem: Transaction log grew too big.
> >
> > I took my database off line and then back online to ensure that no one
was
> > connected to the database and then backed up the database.
> >
> > I figured at this point I would be able to shrink the transaction log
> > because all of the 'pending' transactions would be clear - however I
> > cannot.
> >
> > Do I have to backup the transaction log? And in so doing will this solve
> > my
> > problem?
> >
> > Thanks
> >
> >
> >
>|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set up
> that functionality as part of a job. I will add a nightly backup. We have to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
>> Stephen,
>> Production database? If so, backup the transaction log then shrink the
>> transaction log file using DBCC SHRINKFILE statment.
>> HTH
>> Jerry
>> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
>> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
>> > Problem: Transaction log grew too big.
>> >
>> > I took my database off line and then back online to ensure that no one
> was
>> > connected to the database and then backed up the database.
>> >
>> > I figured at this point I would be able to shrink the transaction log
>> > because all of the 'pending' transactions would be clear - however I
>> > cannot.
>> >
>> > Do I have to backup the transaction log? And in so doing will this solve
>> > my
>> > problem?
>> >
>> > Thanks
>> >
>> >
>> >
>>
>

Data Warehousing:Best way to write SP?

I'm writing a set of queries designed to test the data quality of our data warehouse at the fact level. The intent is to ensure that there are no keys at the fact level that do not exist at the dimension level.

I'm dealing with 8 fact tables, 8 dimension tables and 9 keys. I'm currently doing:

SELECT COUNT(DISTINCT key)
FROM t_fact
WHERE key NOT IN
(
SELECT DISTINCT key FROM t_dimension
)

If I do one of those for each key-fact table combo, there are about 50 queries in total. Not every key exists in every fact table.

I'm a Stored Procedure novice. What is the best way to check all of the fact tables, aside from running 50 counts with subqueries? If I run the queries one fact table at a time, it will take about 30 minutes. I've tried to run one query per fact table, by counting all keys, and doing a subselect to each dimension table, but got misleading results.

Any tips will be greatly appreciated. Abandoning data warehousing isn't a current option!

MikeIn Oracle I would find missing keys like this:

SELECT key FROM t_fact
MINUS
SELECT key FROM t_dimension

BTW, why not use a foreign key constraint to ensure all fact keys are based on dimension keys?