DBCC CHECKDB: idea

Showing posts with label idea. Show all posts

Thursday, March 22, 2012

DB size is morethan 2 GB how to optimize

Dears,
Is there any other settings for managing DB size is more than 2 GB, it seems
it takes long time to retives the data. please give me idea.
Thanks,
Dinesh BhandareAre you using MSDE by any chance? What makes you feel it takes long time ...
I've databases that are more than 8-10 GB in size and they yet perform good
under load ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Dinesh Bhandare" <DineshBhandare@.discussions.microsoft.com> wrote in
message news:B42CBC39-EA49-40FD-B6F2-97DAF84CD4FB@.microsoft.com...
> Dears,
> Is there any other settings for managing DB size is more than 2 GB, it
seems
> it takes long time to retives the data. please give me idea.
> Thanks,
> Dinesh Bhandare|||Dinesh
2gb is not a very large database. You should be able to get very good
performance from it. So many things it could be.
A few things to try. Run profiler to capture your longest running queries.
Put those queries into query anayser and check the execution plans. If there
are problems with the code or lack of correct indexes, it should be easy to
spot. Here are some good tips on using profiler.
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
Run dbcc showcontig on all your tables to check your tables for
fragmentation.
You do keep your database files, transaction logs and backups on seperate
disks, don't you? If you don't try to move them so you do.
Use perfmon to check your memory and disk I/O are not having problems. Here
is a link to a good article on using perfmon.
http://www.sql-server-performance.com/performance_monitor_tips.asp
There are lots of good articles on performance on
http://www.sql-server-performance.com/ have a good look through them and see
if you can identufy which areas may be affecting you.
If you are using a LAN or WAN, might be worth asking your network guys to do
a check, might not be a SQL Server problem at all.
These tips should at least get you started. There are so many things it can
be.
Hope this helps.
John
"Dinesh Bhandare" wrote:
> Dears,
> Is there any other settings for managing DB size is more than 2 GB, it seems
> it takes long time to retives the data. please give me idea.
> Thanks,
> Dinesh Bhandare|||To add to the other responses, appropriate indexing is an important
performance consideration. Large tables are less forgiving when you don't
have indexes in place to efficiently process queries. Review your query
plans. Columns referenced in joins and predicates may be good candidates
for indexing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dinesh Bhandare" <DineshBhandare@.discussions.microsoft.com> wrote in
message news:B42CBC39-EA49-40FD-B6F2-97DAF84CD4FB@.microsoft.com...
> Dears,
> Is there any other settings for managing DB size is more than 2 GB, it
> seems
> it takes long time to retives the data. please give me idea.
> Thanks,
> Dinesh Bhandare

Friday, February 24, 2012

DB Maintenance

Does anyone have an idea why I am getting the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
Thanks.A little more information would be helpful. What specifically are you
trying to do when you get the error?
My initial guess is that you are attempting to do index maintenance on a
table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is off,
or both...
"NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
> Does anyone have an idea why I am getting the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> Thanks.
>
>|||You'll also see this error when trying to run DBCC CHECKDB against tables
with indexed computed columns unless these SET options are both on.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Don Peterson" <no1@.nunya.com> wrote in message
news:OiZeXP7KEHA.3944@.tk2msftngp13.phx.gbl...
> A little more information would be helpful. What specifically are you
> trying to do when you get the error?
> My initial guess is that you are attempting to do index maintenance on a
> table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is
off,
> or both...
> "NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
> news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
> > Does anyone have an idea why I am getting the following error:
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> > SQL Server Driver][SQL Server]DBCC failed because the following SET
> > options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> >
> > Thanks.
> >
> >
> >
>

DB Maintenance

Does anyone have an idea why I am getting the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
Thanks.
A little more information would be helpful. What specifically are you
trying to do when you get the error?
My initial guess is that you are attempting to do index maintenance on a
table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is off,
or both...
"NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
> Does anyone have an idea why I am getting the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> Thanks.
>
>
|||You'll also see this error when trying to run DBCC CHECKDB against tables
with indexed computed columns unless these SET options are both on.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Don Peterson" <no1@.nunya.com> wrote in message
news:OiZeXP7KEHA.3944@.tk2msftngp13.phx.gbl...
> A little more information would be helpful. What specifically are you
> trying to do when you get the error?
> My initial guess is that you are attempting to do index maintenance on a
> table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is
off,
> or both...
> "NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
> news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
>

DB Maintenance

Does anyone have an idea why I am getting the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
Thanks.A little more information would be helpful. What specifically are you
trying to do when you get the error?
My initial guess is that you are attempting to do index maintenance on a
table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is off,
or both...
"NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
> Does anyone have an idea why I am getting the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> Thanks.
>
>|||You'll also see this error when trying to run DBCC CHECKDB against tables
with indexed computed columns unless these SET options are both on.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Don Peterson" <no1@.nunya.com> wrote in message
news:OiZeXP7KEHA.3944@.tk2msftngp13.phx.gbl...
> A little more information would be helpful. What specifically are you
> trying to do when you get the error?
> My initial guess is that you are attempting to do index maintenance on a
> table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is
off,
> or both...
> "NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
> news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
>

Friday, February 17, 2012

DB is boated when restored

I have a *.bak file that is 1.7GB in size. When I do a restore the log file
is 3.0Gb and the data file is 16GB. Does anyone have any idea on what could
be causing this?
This is related to the other newsgroup post of "SQL 2005 db won't shrink".
Also, this db is a production Dynamics AX 4.0.
Any help/direction/info would be greatly appreciated.A database backup file only includes the data used in the database files and also the log records
produced while performing the backup. That backup was performed on a database which had free space
in both database file and the log file. When you restore the backup, SQL Server has to create the
database with the same file size as all the database files had when the backup was performed. So,
what you see is perfectly normal.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
news:7429D298-69ED-444B-87E1-0659CF2F74B9@.microsoft.com...
>I have a *.bak file that is 1.7GB in size. When I do a restore the log file
> is 3.0Gb and the data file is 16GB. Does anyone have any idea on what could
> be causing this?
> This is related to the other newsgroup post of "SQL 2005 db won't shrink".
> Also, this db is a production Dynamics AX 4.0.
> Any help/direction/info would be greatly appreciated.|||Patrick R. wrote:
> I have a *.bak file that is 1.7GB in size. When I do a restore the log file
> is 3.0Gb and the data file is 16GB. Does anyone have any idea on what could
> be causing this?
> This is related to the other newsgroup post of "SQL 2005 db won't shrink".
> Also, this db is a production Dynamics AX 4.0.
> Any help/direction/info would be greatly appreciated.
>
I didn't happen to read your other reference topic.
Have you looked at the dbcc shrinkdatabase with the truncate only option
TRUNCATEONLY
Releases all free space at the end of the file to the operating
system but does not perform any page movement inside the file. The
data file is shrunk only to the last allocated extent.
/target_percent/ is ignored if specified with TRUNCATEONLY.|||A misunderstanding, so let me clarify...I need to remove all the "space
available" from this db, no matter how it gets there. And I've tried all the
shrinkdatabase, shrinkfiles, put in simple recover mode, backed up, restored,
etc. And the space is still there. So what can remove the unwanted space?
Thanks
"Tibor Karaszi" wrote:
> A database backup file only includes the data used in the database files and also the log records
> produced while performing the backup. That backup was performed on a database which had free space
> in both database file and the log file. When you restore the backup, SQL Server has to create the
> database with the same file size as all the database files had when the backup was performed. So,
> what you see is perfectly normal.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
> news:7429D298-69ED-444B-87E1-0659CF2F74B9@.microsoft.com...
> >I have a *.bak file that is 1.7GB in size. When I do a restore the log file
> > is 3.0Gb and the data file is 16GB. Does anyone have any idea on what could
> > be causing this?
> > This is related to the other newsgroup post of "SQL 2005 db won't shrink".
> > Also, this db is a production Dynamics AX 4.0.
> > Any help/direction/info would be greatly appreciated.
>|||>A misunderstanding, so let me clarify...I need to remove all the "space
> available" from this db, no matter how it gets there.
Why, that comes with a huge performance penalty. But, assuming you have your reasons:
For the log to shrink, you need to work DBCC LOGINFO, BACKUP LOG and DBCC SHRINKFILE. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details. Pay special attention to the DBCC
LOGINFO part.
For the data part, well, it should shrink using DBCC SHRINKFILE. IT can take a while to shrink 16
GB, but it should happen (no fancy options, though).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
news:69E7CBDE-6BA0-4234-9987-7D1AE5529275@.microsoft.com...
>A misunderstanding, so let me clarify...I need to remove all the "space
> available" from this db, no matter how it gets there. And I've tried all the
> shrinkdatabase, shrinkfiles, put in simple recover mode, backed up, restored,
> etc. And the space is still there. So what can remove the unwanted space?
> Thanks
> "Tibor Karaszi" wrote:
>> A database backup file only includes the data used in the database files and also the log records
>> produced while performing the backup. That backup was performed on a database which had free
>> space
>> in both database file and the log file. When you restore the backup, SQL Server has to create the
>> database with the same file size as all the database files had when the backup was performed. So,
>> what you see is perfectly normal.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
>> news:7429D298-69ED-444B-87E1-0659CF2F74B9@.microsoft.com...
>> >I have a *.bak file that is 1.7GB in size. When I do a restore the log file
>> > is 3.0Gb and the data file is 16GB. Does anyone have any idea on what could
>> > be causing this?
>> > This is related to the other newsgroup post of "SQL 2005 db won't shrink".
>> > Also, this db is a production Dynamics AX 4.0.
>> > Any help/direction/info would be greatly appreciated.
>>|||It is not the log file that has the issue, it's the db file. Also, I've
logged a case w/ MS and we've done all the things I've read and done earlier,
to no availl. I am currently uploading my bak to MS for their evaluation. I
will keep this thread updated on the results, and thank everyone for their
time and effort, thanks, Patrick
"Tibor Karaszi" wrote:
> >A misunderstanding, so let me clarify...I need to remove all the "space
> > available" from this db, no matter how it gets there.
> Why, that comes with a huge performance penalty. But, assuming you have your reasons:
> For the log to shrink, you need to work DBCC LOGINFO, BACKUP LOG and DBCC SHRINKFILE. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details. Pay special attention to the DBCC
> LOGINFO part.
> For the data part, well, it should shrink using DBCC SHRINKFILE. IT can take a while to shrink 16
> GB, but it should happen (no fancy options, though).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
> news:69E7CBDE-6BA0-4234-9987-7D1AE5529275@.microsoft.com...
> >A misunderstanding, so let me clarify...I need to remove all the "space
> > available" from this db, no matter how it gets there. And I've tried all the
> > shrinkdatabase, shrinkfiles, put in simple recover mode, backed up, restored,
> > etc. And the space is still there. So what can remove the unwanted space?
> > Thanks
> >
> > "Tibor Karaszi" wrote:
> >
> >> A database backup file only includes the data used in the database files and also the log records
> >> produced while performing the backup. That backup was performed on a database which had free
> >> space
> >> in both database file and the log file. When you restore the backup, SQL Server has to create the
> >> database with the same file size as all the database files had when the backup was performed. So,
> >> what you see is perfectly normal.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
> >> news:7429D298-69ED-444B-87E1-0659CF2F74B9@.microsoft.com...
> >> >I have a *.bak file that is 1.7GB in size. When I do a restore the log file
> >> > is 3.0Gb and the data file is 16GB. Does anyone have any idea on what could
> >> > be causing this?
> >> > This is related to the other newsgroup post of "SQL 2005 db won't shrink".
> >> > Also, this db is a production Dynamics AX 4.0.
> >> > Any help/direction/info would be greatly appreciated.
> >>
> >>
>

Thursday, March 22, 2012

DB size is morethan 2 GB how to optimize

Friday, February 24, 2012

DB Maintenance

DB Maintenance

DB Maintenance

Friday, February 17, 2012

DB is boated when restored

DB is boated when restored

DB is boated when restored

DBCC CHECKDB

Blog Archive

About Me