Database level lock: failed

Showing posts with label failed. Show all posts

Wednesday, March 21, 2012

Database Engine Tuning Wizard: "Failed to initialize MSDB database for tuning (exit code:

Error trying to tune a database:
Database Engine Tuning Wizard: "Failed to initialize MSDB database for
tuning (exit code: -1). (DTAClient)
Steps to Reproduce the Problem
1. Launch Database Engine Tuning Advisor
2. Enter connection info
Server Name: <The Server dns name>
Authentication: SQL Server Authentication
Login: sa
Password: <the sa password>
3. Click "Connect"
Notes:
a. The target server has an MSDB database
b. The server is Windows 2000
c. The server is SQL Server 2000
d. The sa login can connect and see the msdb database
e. My desktop machine has no application compatibility flags set for
explorer.exe
Full error log:
===================================
Failed to open a new connection.
===================================
Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)
--
Program Location:
at
Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at
Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at
Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo
connectionInfo, SqlConnection connection)
at
Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo
connectionInfo, SqlConnection connection)
at
Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object
sender, EventArgs e)
===================================
Failed to establish a connection to the server.With regard to the "Database Engine Tuning Wizard: "Failed to initializ
MSDB database for tuning (exit code: -1). (DTAClient)" error, I just ra
into this myself with a fresh install of MS SQL Server 2005 + SP1.
I ran SQL Profiler on another server, that was working, to see what wa
happening when firing up the Database Tuning Advisor, and found that i
was looking for the following in the MSDB database:
Tables:
'DTA_input',
'DTA_progress',
'DTA_output',
'DTA_tuninglog',
'DTA_reports_database',
'DTA_reports_partitionfunction',
'DTA_reports_partitionscheme',
'DTA_reports_table',
'DTA_reports_tableview',
'DTA_reports_query',
'DTA_reports_querytable',
'DTA_reports_querydatabase',
'DTA_reports_index',
'DTA_reports_queryindex',
'DTA_reports_column',
'DTA_reports_indexcolumn',
'DTA_reports_querycolumn'
Stored Procedures:
'sp_DTA_add_session',
'sp_DTA_delete_session',
'sp_DTA_help_session',
'sp_DTA_update_session',
'sp_DTA_get_tuninglog',
'sp_DTA_update_session',
'sp_DTA_get_session_report',
'sp_DTA_get_tuninglog',
'sp_DTA_get_session_tuning_results',
'sp_DTA_set_interactivestatus'
These objects were missing from the server that was failing, so
scripted them from the server that was working. Once I created th
objects in the MSDB database, Database Tuning Advisor now works.
I've attached a zip file with the SQL scripts
+---
|Filename: DTA_Objects.zip
|Download: http://unixadmintalk.com/attachment.php?attachmentid=2
+---
--
bkp_4
----
bkp_42's Profile: http://unixadmintalk.com/77
View this thread: http://unixadmintalk.com/showthread.php?t=18525sql

Tuesday, February 14, 2012

Database Corruption?

Hi,
I have a 36GB database that has failed it's integrity checks as part of a
scheduled maintenance plan over the weekend. The plan history and job step
history did not provide any useful information. I've been trying to run a
DBCC CHECKDB on the database (no options) for the last 2 hours and it is
still running. In the past I believe the DBCC CHECKDB statement required
less than 30 minutes to complete. What are my options at this point outside
of restoring from a backup?
Thanks
JerryOk...over 3 hours for the DBCC CHECKDB and it is still running. Any
recommendations?
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23j6KB$UvFHA.2504@.tk2msftngp13.phx.gbl...
> Hi,
> I have a 36GB database that has failed it's integrity checks as part of a
> scheduled maintenance plan over the weekend. The plan history and job
> step history did not provide any useful information. I've been trying to
> run a DBCC CHECKDB on the database (no options) for the last 2 hours and
> it is still running. In the past I believe the DBCC CHECKDB statement
> required less than 30 minutes to complete. What are my options at this
> point outside of restoring from a backup?
> Thanks
> Jerry
>|||Hi
Rather wait the DBCC out and see what it shows. You rather find out now than
in another 24 hours that the DB is corrupt.
In the mean time, fine the last good backup and restore it to another server
to see if it is also corrupt and if you can actually restore it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23UswseVvFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Ok...over 3 hours for the DBCC CHECKDB and it is still running. Any
> recommendations?
> Thanks
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23j6KB$UvFHA.2504@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a 36GB database that has failed it's integrity checks as part of a
>> scheduled maintenance plan over the weekend. The plan history and job
>> step history did not provide any useful information. I've been trying to
>> run a DBCC CHECKDB on the database (no options) for the last 2 hours and
>> it is still running. In the past I believe the DBCC CHECKDB statement
>> required less than 30 minutes to complete. What are my options at this
>> point outside of restoring from a backup?
>> Thanks
>> Jerry
>|||Thanks Mike. I was just starting that process as well as examining the
event logs to see if this could be a HW issue.
Will keep you posted.
Jerry
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uj25DxVvFHA.3792@.TK2MSFTNGP10.phx.gbl...
> Hi
> Rather wait the DBCC out and see what it shows. You rather find out now
> than in another 24 hours that the DB is corrupt.
> In the mean time, fine the last good backup and restore it to another
> server to see if it is also corrupt and if you can actually restore it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23UswseVvFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Ok...over 3 hours for the DBCC CHECKDB and it is still running. Any
>> recommendations?
>> Thanks
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23j6KB$UvFHA.2504@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a 36GB database that has failed it's integrity checks as part of
>> a scheduled maintenance plan over the weekend. The plan history and job
>> step history did not provide any useful information. I've been trying
>> to run a DBCC CHECKDB on the database (no options) for the last 2 hours
>> and it is still running. In the past I believe the DBCC CHECKDB
>> statement required less than 30 minutes to complete. What are my
>> options at this point outside of restoring from a backup?
>> Thanks
>> Jerry
>>
>|||Ok...event log shows a SQL Server Assertion error and a Error 3624 from a
couple of days ago. Also, an Error 8648 "Index entry for row ID was not
found in index ID 2 of table 1403152044. However, a SELECT OBJECT_NAME with
this number reveals a stored procedure. Any ideas? The DBCC is still
running and the last backup should be on the test box in about 30 minutes.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u1PqWzVvFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Thanks Mike. I was just starting that process as well as examining the
> event logs to see if this could be a HW issue.
> Will keep you posted.
> Jerry
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uj25DxVvFHA.3792@.TK2MSFTNGP10.phx.gbl...
>> Hi
>> Rather wait the DBCC out and see what it shows. You rather find out now
>> than in another 24 hours that the DB is corrupt.
>> In the mean time, fine the last good backup and restore it to another
>> server to see if it is also corrupt and if you can actually restore it.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23UswseVvFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Ok...over 3 hours for the DBCC CHECKDB and it is still running. Any
>> recommendations?
>> Thanks
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23j6KB$UvFHA.2504@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a 36GB database that has failed it's integrity checks as part of
>> a scheduled maintenance plan over the weekend. The plan history and
>> job step history did not provide any useful information. I've been
>> trying to run a DBCC CHECKDB on the database (no options) for the last
>> 2 hours and it is still running. In the past I believe the DBCC
>> CHECKDB statement required less than 30 minutes to complete. What are
>> my options at this point outside of restoring from a backup?
>> Thanks
>> Jerry
>>
>>
>|||Problem solved. Corrupt NC index on table with apx 150 million rows.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eaOzlIWvFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Ok...event log shows a SQL Server Assertion error and a Error 3624 from a
> couple of days ago. Also, an Error 8648 "Index entry for row ID was not
> found in index ID 2 of table 1403152044. However, a SELECT OBJECT_NAME
> with this number reveals a stored procedure. Any ideas? The DBCC is
> still running and the last backup should be on the test box in about 30
> minutes.
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:u1PqWzVvFHA.3500@.TK2MSFTNGP09.phx.gbl...
>> Thanks Mike. I was just starting that process as well as examining the
>> event logs to see if this could be a HW issue.
>> Will keep you posted.
>> Jerry
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:uj25DxVvFHA.3792@.TK2MSFTNGP10.phx.gbl...
>> Hi
>> Rather wait the DBCC out and see what it shows. You rather find out now
>> than in another 24 hours that the DB is corrupt.
>> In the mean time, fine the last good backup and restore it to another
>> server to see if it is also corrupt and if you can actually restore it.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23UswseVvFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Ok...over 3 hours for the DBCC CHECKDB and it is still running. Any
>> recommendations?
>> Thanks
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23j6KB$UvFHA.2504@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a 36GB database that has failed it's integrity checks as part
>> of a scheduled maintenance plan over the weekend. The plan history
>> and job step history did not provide any useful information. I've
>> been trying to run a DBCC CHECKDB on the database (no options) for the
>> last 2 hours and it is still running. In the past I believe the DBCC
>> CHECKDB statement required less than 30 minutes to complete. What are
>> my options at this point outside of restoring from a backup?
>> Thanks
>> Jerry
>>
>>
>>
>|||FWIW, the extra time you're seeing comes from DBCC doing extensive
non-clustered index to base table cross checks to determine which NC index
rows have no matching data rows, or which data rows have no matching NC
index rows. This only happens when we find corruptions in the database,
and, as you have found, is quite time-consuming.
Thanks,
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u0kgwaXvFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Problem solved. Corrupt NC index on table with apx 150 million rows.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eaOzlIWvFHA.3720@.TK2MSFTNGP14.phx.gbl...
>> Ok...event log shows a SQL Server Assertion error and a Error 3624 from a
>> couple of days ago. Also, an Error 8648 "Index entry for row ID was not
>> found in index ID 2 of table 1403152044. However, a SELECT OBJECT_NAME
>> with this number reveals a stored procedure. Any ideas? The DBCC is
>> still running and the last backup should be on the test box in about 30
>> minutes.
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:u1PqWzVvFHA.3500@.TK2MSFTNGP09.phx.gbl...
>> Thanks Mike. I was just starting that process as well as examining the
>> event logs to see if this could be a HW issue.
>> Will keep you posted.
>> Jerry
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:uj25DxVvFHA.3792@.TK2MSFTNGP10.phx.gbl...
>> Hi
>> Rather wait the DBCC out and see what it shows. You rather find out now
>> than in another 24 hours that the DB is corrupt.
>> In the mean time, fine the last good backup and restore it to another
>> server to see if it is also corrupt and if you can actually restore it.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23UswseVvFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Ok...over 3 hours for the DBCC CHECKDB and it is still running. Any
>> recommendations?
>> Thanks
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23j6KB$UvFHA.2504@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have a 36GB database that has failed it's integrity checks as part
>> of a scheduled maintenance plan over the weekend. The plan history
>> and job step history did not provide any useful information. I've
>> been trying to run a DBCC CHECKDB on the database (no options) for
>> the last 2 hours and it is still running. In the past I believe the
>> DBCC CHECKDB statement required less than 30 minutes to complete.
>> What are my options at this point outside of restoring from a backup?
>> Thanks
>> Jerry
>>
>>
>>
>>
>

database corrupt

What can I do if I failed to attach a database by EM?
If the database is corrupted, any method to recover the
database?
hi Tony,
"Tony" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:06b901c4a073$d7dd8a40$a401280a@.phx.gbl
> What can I do if I failed to attach a database by EM?
> If the database is corrupted, any method to recover the
> database?
if you have a clean backup, this usually is the best solution to go but, if
the database can be saved, perhaps the advices by Jasper as per
http://tinyurl.com/3pxhv can be helpfull
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi,
What is the error you are getting while attaching?
Best option after a corrruption will be restoring from a good backup. If you
do not have the backup try below:-
1. Execute DBCC CHECKDB('db_name','REPAIR_REBUILD') , this will corrupt
minor issues.
If you do not have any options try with Jaspers suggestion
http://www.google.it/groups?q=+%22sp...ftngp09&rnum=1
Thanks
Hari
MCDBA
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2re0b2F18k52cU1@.uni-berlin.de...
> hi Tony,
> "Tony" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
> news:06b901c4a073$d7dd8a40$a401280a@.phx.gbl
> if you have a clean backup, this usually is the best solution to go but,
> if
> the database can be saved, perhaps the advices by Jasper as per
> http://tinyurl.com/3pxhv can be helpfull
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Wednesday, March 21, 2012

Database Engine Tuning Wizard: "Failed to initialize MSDB database for tuning (exit code:

Tuesday, February 14, 2012

Database Corruption?

Database Corruption?

Database Corruption?

database corrupt

Database level lock

Blog Archive

About Me