Repeating "starting up database DBtest" message in sql log

In my sql log I see the 'starting up database DBtest' repeating every few
seconds. The database seems fine and I can retreive data from tables of the
database. I've included a short clip of my log. I think something is wrong
here. Anybody have any clues. My first take is to terminate the spid65 and
see if all is well but this is a production system so I wanted to see if
anyone else has seen this before and had any suggestions.
Thanks, Edie
7/20/20051:13:27spid63Starting up database 'DBTest'.
7/20/20051:14:27backupDatabase backed up: Database: DBTest, creation
7/21/20051:13:27spid63Starting up database 'DBTest'.
7/21/20051:14:22backupDatabase backed up: Database: DBTest, creation
7/22/20051:14:19backupDatabase backed up: Database: DBTest, creation
7/23/20051:14:21backupDatabase backed up: Database: DBTest, creation
7/24/20051:14:20backupDatabase backed up: Database: DBTest, creation
7/24/200521:00:07backupDatabase backed up: Database: master, creation
7/24/200521:00:10backupDatabase backed up: Database: model, creation
7/24/200521:00:12backupDatabase backed up: Database: msdb, creation
7/25/20051:13:27spid63Starting up database 'DBTest'.
7/25/20051:14:20backupDatabase backed up: Database: DBTest, creation
7/25/200511:06:18spid65Starting up database 'DBTest'.
7/25/200511:06:18spid65Starting up database 'DBTest'.
7/25/200511:06:18spid65Starting up database 'DBTest'.
7/25/200511:06:19spid65Starting up database 'DBTest'.
7/25/200511:06:19spid65Starting up database 'DBTest'.
7/25/200511:06:19spid65Starting up database 'DBTest'.
Hi,
It seems you have enabled the database option "AUTOCLOSE". THis will close
the MDF and LDF as soon as the last user logs of the database. Again the MDF
and LDF will be opened once a user logins to the database.
How to check this option is checked:-
1. Enterprise manager -- Databases -- Select the database
2. Right click and select properties -- Choose options
3. Chek whether AUTOCLOSE option is "checked". If yes then remove it
or from Query analyzer execute the below command to turn off:-
sp_dboption <dbname>, 'autoclose', False
This will ensure that database will never closed as soon as last user logs
off.
Thanks
Hari
SQL Server MVP
"Edie Richardson" <Edie Richardson @.discussions.microsoft.com> wrote in
message news:D91B95AE-C74C-483A-9A7F-243B879A7636@.microsoft.com...
> In my sql log I see the 'starting up database DBtest' repeating every few
> seconds. The database seems fine and I can retreive data from tables of
> the
> database. I've included a short clip of my log. I think something is
> wrong
> here. Anybody have any clues. My first take is to terminate the spid65
> and
> see if all is well but this is a production system so I wanted to see if
> anyone else has seen this before and had any suggestions.
> Thanks, Edie
> 7/20/2005 1:13:27 spid63 Starting up database 'DBTest'.
> 7/20/2005 1:14:27 backup Database backed up: Database: DBTest, creation
> 7/21/2005 1:13:27 spid63 Starting up database 'DBTest'.
> 7/21/2005 1:14:22 backup Database backed up: Database: DBTest, creation
> 7/22/2005 1:14:19 backup Database backed up: Database: DBTest, creation
> 7/23/2005 1:14:21 backup Database backed up: Database: DBTest, creation
> 7/24/2005 1:14:20 backup Database backed up: Database: DBTest, creation
> 7/24/2005 21:00:07 backup Database backed up: Database: master, creation
> 7/24/2005 21:00:10 backup Database backed up: Database: model, creation
> 7/24/2005 21:00:12 backup Database backed up: Database: msdb, creation
> 7/25/2005 1:13:27 spid63 Starting up database 'DBTest'.
> 7/25/2005 1:14:20 backup Database backed up: Database: DBTest, creation
> 7/25/2005 11:06:18 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:18 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:18 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:19 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:19 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:19 spid65 Starting up database 'DBTest'.
>
|||Switch off "Auto Close" on the DB (Database Property)
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/
"Edie Richardson" <Edie Richardson @.discussions.microsoft.com> wrote in
message news:D91B95AE-C74C-483A-9A7F-243B879A7636@.microsoft.com...
> In my sql log I see the 'starting up database DBtest' repeating every few
> seconds. The database seems fine and I can retreive data from tables of
> the
> database. I've included a short clip of my log. I think something is
> wrong
> here. Anybody have any clues. My first take is to terminate the spid65
> and
> see if all is well but this is a production system so I wanted to see if
> anyone else has seen this before and had any suggestions.
> Thanks, Edie
> 7/20/2005 1:13:27 spid63 Starting up database 'DBTest'.
> 7/20/2005 1:14:27 backup Database backed up: Database: DBTest, creation
> 7/21/2005 1:13:27 spid63 Starting up database 'DBTest'.
> 7/21/2005 1:14:22 backup Database backed up: Database: DBTest, creation
> 7/22/2005 1:14:19 backup Database backed up: Database: DBTest, creation
> 7/23/2005 1:14:21 backup Database backed up: Database: DBTest, creation
> 7/24/2005 1:14:20 backup Database backed up: Database: DBTest, creation
> 7/24/2005 21:00:07 backup Database backed up: Database: master, creation
> 7/24/2005 21:00:10 backup Database backed up: Database: model, creation
> 7/24/2005 21:00:12 backup Database backed up: Database: msdb, creation
> 7/25/2005 1:13:27 spid63 Starting up database 'DBTest'.
> 7/25/2005 1:14:20 backup Database backed up: Database: DBTest, creation
> 7/25/2005 11:06:18 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:18 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:18 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:19 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:19 spid65 Starting up database 'DBTest'.
> 7/25/2005 11:06:19 spid65 Starting up database 'DBTest'.
>
|||thanks! The autoclose option was checked.
"Hari Prasad" wrote:

> Hi,
> It seems you have enabled the database option "AUTOCLOSE". THis will close
> the MDF and LDF as soon as the last user logs of the database. Again the MDF
> and LDF will be opened once a user logins to the database.
> How to check this option is checked:-
>
> 1. Enterprise manager -- Databases -- Select the database
> 2. Right click and select properties -- Choose options
> 3. Chek whether AUTOCLOSE option is "checked". If yes then remove it
> or from Query analyzer execute the below command to turn off:-
> sp_dboption <dbname>, 'autoclose', False
> This will ensure that database will never closed as soon as last user logs
> off.
> Thanks
> Hari
> SQL Server MVP
>
> "Edie Richardson" <Edie Richardson @.discussions.microsoft.com> wrote in
> message news:D91B95AE-C74C-483A-9A7F-243B879A7636@.microsoft.com...
>
>

Repeated Login failed for user SA in Event Log.

At regular intervals there are failed login message for User SA, User User,
User Root in the event log. This seems to have started after Security Patche
s
were applied.
How do I stop these failed logins occuring?
Thanks
--
John Franks MIScT, MIAP, MCSA, MCSE, MCDBACheck the Enterprise manager settings for configuration of SQL Agent.
Try changing SQL Agent to use Windows Authentication instead of Standard
Security.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks Kevin, we are already using Window authentication, any other
suggestions '
"Kevin McDonnell [MSFT]" wrote:

> Check the Enterprise manager settings for configuration of SQL Agent.
> Try changing SQL Agent to use Windows Authentication instead of Standard
> Security.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Otherwise, you could use SQL Profiler to capture failed login events to try
to find out what application is causing them, or run a network trace .
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Repeated Errors in SQL Server log file

I have hundreds of these errors saying 'Login failed for user 'Reporting' The user is not associated with a trusted SQL Server connection [CLIENT: ip address]

The ip address is that of the server that sql server is installed on.

Looking in my log file, all looks good until I get to Service Broker manager has started, then I get Error: 18452, Severity: 14 State: 1 then these two lines repeat about every minute, for the last 3 days!

I think I must have just missed a tick box somewhere, but where?

I have been into one of the databases, and input and checked data, both via an application I wrote and SQL Server Management Studio.

I am also having trouble connecting using my application to connect to the database, I can only connect if I use a Windows administrator account (this SQL Server 2005 running on a Windows 2003 Server, with the app on PC running Windows 2000)

Hello Graham,

did you find any solutions for that problem? I'm discovering a similar problem. The state of my error message is 5. According to the following source http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx this means the user is not known. This is correct. Neither the SQL-Server nor the Windows system has such a userid.

The faulty user name is 'Reporting'. What is that user used for? I have set up other SQL Server 2005 servers but was never asked for such a name.

Greetings,

Frank

|||

Frank

No, but I haven't had much chance to chase this, too many other issues!

|||

I've run into the same problem. As far as I can tell the "Reporting" userid is coming from Symantec Reporting Server for Antivirus or Client Security. Everything seems to be functioning in the Reporting console in Symantec Client security just fine, yet I still get the error. I've tried most of what I can think of to resolve this, but will let you know if I find anything else out. Please do likewise.

Jim

Repeated Errors in SQL Server log file

I have hundreds of these errors saying 'Login failed for user 'Reporting' The user is not associated with a trusted SQL Server connection [CLIENT: ip address]

The ip address is that of the server that sql server is installed on.

Looking in my log file, all looks good until I get to Service Broker manager has started, then I get Error: 18452, Severity: 14 State: 1 then these two lines repeat about every minute, for the last 3 days!

I think I must have just missed a tick box somewhere, but where?

I have been into one of the databases, and input and checked data, both via an application I wrote and SQL Server Management Studio.

I am also having trouble connecting using my application to connect to the database, I can only connect if I use a Windows administrator account (this SQL Server 2005 running on a Windows 2003 Server, with the app on PC running Windows 2000)

Hello Graham,

did you find any solutions for that problem? I'm discovering a similar problem. The state of my error message is 5. According to the following source http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx this means the user is not known. This is correct. Neither the SQL-Server nor the Windows system has such a userid.

The faulty user name is 'Reporting'. What is that user used for? I have set up other SQL Server 2005 servers but was never asked for such a name.

Greetings,

Frank

|||

Frank

No, but I haven't had much chance to chase this, too many other issues!

|||

I've run into the same problem. As far as I can tell the "Reporting" userid is coming from Symantec Reporting Server for Antivirus or Client Security. Everything seems to be functioning in the Reporting console in Symantec Client security just fine, yet I still get the error. I've tried most of what I can think of to resolve this, but will let you know if I find anything else out. Please do likewise.

Jim

Repeated "starting up database" in SQL Server log

I am seeing this in my SQL server logs.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
You likely have the database set to auto-close.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Prabhash Shrestha" <Prabhash Shrestha@.discussions.microsoft.com> wrote in
message news:F267E7FA-DFF9-4BCD-BC7D-7D4B9647827A@.microsoft.com...
> I am seeing this in my SQL server logs.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
>
>
>
|||Your database must be set to autoclose. It is one of the options that you
can turn on and off.
Most people will recommend that autoclose be disabled
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_aa-az_4e5h.htm
ALTER DATABASE foo SET AUTO_CLOSE OFF
Keith
"Prabhash Shrestha" <Prabhash Shrestha@.discussions.microsoft.com> wrote in
message news:F267E7FA-DFF9-4BCD-BC7D-7D4B9647827A@.microsoft.com...
> I am seeing this in my SQL server logs.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
>
>
>
|||Hi
Uncheck Auto Close option of the database
"Prabhash Shrestha" <Prabhash Shrestha@.discussions.microsoft.com> wrote in
message news:F267E7FA-DFF9-4BCD-BC7D-7D4B9647827A@.microsoft.com...
> I am seeing this in my SQL server logs.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
>
>
>

Repeated "starting up database" in SQL Server log

I am seeing this in my SQL server logs.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.You likely have the database set to auto-close.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Prabhash Shrestha" <Prabhash Shrestha@.discussions.microsoft.com> wrote in
message news:F267E7FA-DFF9-4BCD-BC7D-7D4B9647827A@.microsoft.com...
> I am seeing this in my SQL server logs.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
>
>
>|||Your database must be set to autoclose. It is one of the options that you
can turn on and off.
Most people will recommend that autoclose be disabled
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_aa-az_4e5h.htm
ALTER DATABASE foo SET AUTO_CLOSE OFF
Keith
"Prabhash Shrestha" <Prabhash Shrestha@.discussions.microsoft.com> wrote in
message news:F267E7FA-DFF9-4BCD-BC7D-7D4B9647827A@.microsoft.com...
> I am seeing this in my SQL server logs.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
>
>
>|||Hi
Uncheck Auto Close option of the database
"Prabhash Shrestha" <Prabhash Shrestha@.discussions.microsoft.com> wrote in
message news:F267E7FA-DFF9-4BCD-BC7D-7D4B9647827A@.microsoft.com...
> I am seeing this in my SQL server logs.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
>
>
>

Repeated "starting up database" in SQL Server log

I am seeing this in my SQL server logs.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.You likely have the database set to auto-close.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Prabhash Shrestha" <Prabhash Shrestha@.discussions.microsoft.com> wrote in
message news:F267E7FA-DFF9-4BCD-BC7D-7D4B9647827A@.microsoft.com...
> I am seeing this in my SQL server logs.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
>
>
>|||Your database must be set to autoclose. It is one of the options that you
can turn on and off.
Most people will recommend that autoclose be disabled
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_aa-az_4e5h.htm
ALTER DATABASE foo SET AUTO_CLOSE OFF
--
Keith
"Prabhash Shrestha" <Prabhash Shrestha@.discussions.microsoft.com> wrote in
message news:F267E7FA-DFF9-4BCD-BC7D-7D4B9647827A@.microsoft.com...
> I am seeing this in my SQL server logs.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
>
>
>|||Hi
Uncheck Auto Close option of the database
"Prabhash Shrestha" <Prabhash Shrestha@.discussions.microsoft.com> wrote in
message news:F267E7FA-DFF9-4BCD-BC7D-7D4B9647827A@.microsoft.com...
> I am seeing this in my SQL server logs.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
>
>
>

Repeated "Login failed for user 'sa'" entries in SQL Server log

I'm getting repeating "Login failed for user 'sa'" messages in my SQL Server
log - every 20 to 30 seconds. These are also being recorded to the Event
Viewer / Application log. This is a SQL Server 2000 SP4 instance inside the
firewall.
I'm running SQL Profiler and capturing as follows:
Events:
Security Audig - Audit Login Failed
Sessions - ExistingConnection
Stored Procedures - RPC:Completed
Data Columns: All columns
SQL Profiler is returning:
Application Name: OSQL-32
ClientProcessID: <differs>
DatabaseID: 1
Error: 18456
Hostname: <server name>
LoginName: sa
LoginSid: 0x01
StartTime: <differs>
Success: 0
TextData: Login failed for user 'sa'.
I don't see much useful information here that can help me track down where
this is coming from; all the other data columns are empty. Am I missing
something? Is there some other tool I could use to track this down?
I'm guessing it's something on the server, based upon the ApplicationName
and HostName values being returned. Could it be a monitoring agent, i.e. MO
M?
Thanks,
MikeIn most cases this is usually a drone (BotNet) PC hijacked and trying to gai
n
access to your SQL server using the sa account. Why Microsoft haven't
produced any useful tools to:
1. Track to the source
2. Automatically filter out traffic once the repeatative pattern has been
established and notify
But there again, I don't expect much from a company like Microsoft who's
moto is "just enough effort to get revenue and leverage the profit".
netstat should help you identify the IP (in your case it sounds like someone
behind your firewall has infested a PC with a BotNet -- since it happens
every 20-30 seconds it should be pretty clear which IP is the source.
You can also use ActivePorts (freeware) to identify the source connections.
Anyway, why these tools aren't built into SQL 2005 is beyond me -- but I
guess that just goes to show you Microsoft's true "commitment" to security
and why their OS/services are such a easy target when compared to *nix based
platforms.
"mikron2" wrote:

> I'm getting repeating "Login failed for user 'sa'" messages in my SQL Serv
er
> log - every 20 to 30 seconds. These are also being recorded to the Event
> Viewer / Application log. This is a SQL Server 2000 SP4 instance inside t
he
> firewall.
>
> I'm running SQL Profiler and capturing as follows:
> Events:
> Security Audig - Audit Login Failed
> Sessions - ExistingConnection
> Stored Procedures - RPC:Completed
> Data Columns: All columns
> SQL Profiler is returning:
> Application Name: OSQL-32
> ClientProcessID: <differs>
> DatabaseID: 1
> Error: 18456
> Hostname: <server name>
> LoginName: sa
> LoginSid: 0x01
> StartTime: <differs>
> Success: 0
> TextData: Login failed for user 'sa'.
> I don't see much useful information here that can help me track down where
> this is coming from; all the other data columns are empty. Am I missing
> something? Is there some other tool I could use to track this down?
> I'm guessing it's something on the server, based upon the ApplicationName
> and HostName values being returned. Could it be a monitoring agent, i.e.
MOM?
> Thanks,
> Mike
>

Repairing a Database

When Trying to Attatch a Database, i get the following message.
Error 9003: The LSN(2768:609:1) passed to log scan in database 'master DB' is invalid.
Is there a repair tool, or something you can do in enterprise manager to repair the database.
Hi,
You may get this error while attaching the Database if the log file of the
database is corrupted.
If you have a clean backup, please restore the Database from the backup.
This is preferred method of recovering the database, because at this point
in time we will not be able to determine the impact of corrupted Log file.
If no clean backup is available, try this workaround, this is not
recommended, as you may have corrupted data.
Move the log file (.ldf) to a different location.
execute the following SP
sp_attach_single_file_db
Attaches a database having only one data file to the current server.
Syntax
sp_attach_single_file_db [ @.dbname = ] 'dbname'
, [ @.physname = ] 'physical_name'
Arguments
[@.dbname =] 'dbname'
Is the name of the database to be attached to the server. dbname is
sysname, with a default of NULL.
[@.physname =] 'phsyical_name'
Is the physical name, including path, of the database file. physical_name
is nvarchar(260), with a default of NULL.
This should be able to attach your database to the Server.
After attaching the database, execute DBCC CHECKDB without a repair clause
to
determine the extent of the corruption. DBCC CHECKDB may recommend a repair
clause
to use. Then, execute DBCC CHECKDB with the appropriate repair clause to
repair the
corruption.
CAUTION: If you are unsure what effect DBCC CHECKDB with a repair clause
has on your data, contact your primary support provider before executing
this
statement.
If running DBCC CHECKDB with one of the repair clauses does not correct the
problem, contact your primary support provider.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

repair_rebuild and the transaction log

We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talking
several hours or days?
Is DBCC reindex a better option in my case?
Thanks
Part of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks
|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:

> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks
|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...[vbcol=seagreen]
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
weekend[vbcol=seagreen]
it[vbcol=seagreen]
I[vbcol=seagreen]
for[vbcol=seagreen]
around[vbcol=seagreen]
160GB[vbcol=seagreen]
does[vbcol=seagreen]
talking[vbcol=seagreen]

repair_rebuild and the transaction log

We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talkin
g
several hours or days?
Is DBCC reindex a better option in my case?
ThanksPart of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink i
t
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room f
or
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160
GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - do
es
> anyone have an idea how long it will take on a 160GB database? Are we talk
ing
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:

> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink i
t
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room f
or
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160
GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - do
es
> anyone have an idea how long it will take on a 160GB database? Are we talk
ing
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...[vbcol=seagreen]
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
>
weekend[vbcol=seagreen]
it[vbcol=seagreen]
I[vbcol=seagreen]
for[vbcol=seagreen]
around[vbcol=seagreen]
160GB[vbcol=seagreen]
does[vbcol=seagreen]
talking[vbcol=seagreen]

repair_rebuild and the transaction log

We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talking
several hours or days?
Is DBCC reindex a better option in my case?
ThanksPart of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
> > We have a large-ish database (160GB) which grew to over 300GB last
weekend
> > when the maintenance job failed. Half of it is unused but I can't shrink
it
> > due to index errors and when I run checkdb it also reports index errors.
I
> > would like to run checkdb with repair_rebuild but there is not much room
for
> > the log file to grow now the DB has gotten so big - it will max out
around
> > 8-10GB.
> >
> > My questions are:
> >
> > Will repair_rebuild need the log file to grow any larger than 8GB on a
160GB
> > database? If so can I just move the log file to a drive with more room?
> >
> > I can't copy the DB to another server and test out the repair_rebuild -
does
> > anyone have an idea how long it will take on a 160GB database? Are we
talking
> > several hours or days?
> >
> > Is DBCC reindex a better option in my case?
> >
> > Thanks

reorganization percent -- ?

Hi All ,
i am using a 3rd s/w(Idera SQL DM to be exact) to perform diagonistics on
my SQL 2000 Server. from the alert log , i got some of the following messages
:
able managed_object (1445580188) in Database Altiris_Incidents (6) has a
reorganization percent of 27%
Table wu_custom_instance (2146106686) in Database Altiris_Incidents (6) has
a reorganization percent of 33%
Table wuci_history (254623950) in Database Altiris_Incidents (6) has a
reorganization percent of 25%
Table tb_Stock (1063674837) in Database SidataSQL_DC (7) has a
reorganization percent of 31%
Table tb_StockInfo (110623437) in Database SidataSQL_DC (7) has a
reorganization percent of 38%
Table tb_Supplier (142623551) in Database SidataSQL_DC (7) has a
reorganization percent of 25%
Table tb_StockCons (1735677231) in Database SidataSQL_DC (7) has a
reorganization percent of 24%
Table tb_Transaction (174623665) in Database SidataSQL_DC (7) has a
reorganization percent of 39%
Table tb_Purchase (292196091) in Database SidataSQL_DC (7) has a
reorganization percent of 48%
what does the organization percent really means ? the same as the fill factor
(i am using the default which is 100%) and what does it mean for my SQL
Server ?
appreciate any advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200512/1
Sounds like a question for Idera not SQL Server. Those are their own
custom messages and I assume they mean fragmentation % but who knows. I
think they need to come up with a better alert message than that<g>.
Andrew J. Kelly SQL MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:593d6713d72ac@.uwe...
> Hi All ,
> i am using a 3rd s/w(Idera SQL DM to be exact) to perform diagonistics
> on
> my SQL 2000 Server. from the alert log , i got some of the following
> messages
> :
> able managed_object (1445580188) in Database Altiris_Incidents (6) has a
> reorganization percent of 27%
> Table wu_custom_instance (2146106686) in Database Altiris_Incidents (6)
> has
> a reorganization percent of 33%
> Table wuci_history (254623950) in Database Altiris_Incidents (6) has a
> reorganization percent of 25%
> Table tb_Stock (1063674837) in Database SidataSQL_DC (7) has a
> reorganization percent of 31%
> Table tb_StockInfo (110623437) in Database SidataSQL_DC (7) has a
> reorganization percent of 38%
> Table tb_Supplier (142623551) in Database SidataSQL_DC (7) has a
> reorganization percent of 25%
> Table tb_StockCons (1735677231) in Database SidataSQL_DC (7) has a
> reorganization percent of 24%
> Table tb_Transaction (174623665) in Database SidataSQL_DC (7) has a
> reorganization percent of 39%
> Table tb_Purchase (292196091) in Database SidataSQL_DC (7) has a
> reorganization percent of 48%
>
> what does the organization percent really means ? the same as the fill
> factor
> (i am using the default which is 100%) and what does it mean for my SQL
> Server ?
> appreciate any advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200512/1
|||tks Andrew i'l check wif Idera then but on further checks it as per wat you
have mentioned(i.e fragmentation %) as the system is allowing me to whether
to do a defrag or not .
but in SQL Server , which DBCC or other commands can allow me to do a defrag
on the tables
tks & rdgs
Andrew J. Kelly wrote:[vbcol=seagreen]
>Sounds like a question for Idera not SQL Server. Those are their own
>custom messages and I assume they mean fragmentation % but who knows. I
>think they need to come up with a better alert message than that<g>.
>[quoted text clipped - 39 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200512/1
|||In SQL 2000:
* DBCC SHOWCONTIG will show you the fragmentation stats on indexes.
* DBCC DBREINDEX & DBCC INDEXDEFRAG will rebuild & defragment your
indexes respectively.
In SQL 2005:
* Querying the dynamic management view
sys.dm_db_index_physical_stats will show you the index fragmentation
* use the ALTER INDEX statement to rebuild or defragment your indexes
*mike hodgson*
blog: http://sqlnerd.blogspot.com
maxzsim via droptable.com wrote:

>tks Andrew i'l check wif Idera then but on further checks it as per wat you
>have mentioned(i.e fragmentation %) as the system is allowing me to whether
>to do a defrag or not .
>but in SQL Server , which DBCC or other commands can allow me to do a defrag
>on the tables
>tks & rdgs
>Andrew J. Kelly wrote:
>
>
>
|||tks Mike
Mike Hodgson wrote:[vbcol=seagreen]
>In SQL 2000:
> * DBCC SHOWCONTIG will show you the fragmentation stats on indexes.
> * DBCC DBREINDEX & DBCC INDEXDEFRAG will rebuild & defragment your
> indexes respectively.
>In SQL 2005:
> * Querying the dynamic management view
> sys.dm_db_index_physical_stats will show you the index fragmentation
> * use the ALTER INDEX statement to rebuild or defragment your indexes
>--
>*mike hodgson*
>blog: http://sqlnerd.blogspot.com
>[quoted text clipped - 24 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200512/1

reorganization percent -- ?

Hi All ,
i am using a 3rd s/w(Idera SQL DM to be exact) to perform diagonistics on
my SQL 2000 Server. from the alert log , i got some of the following message
s
:
able managed_object (1445580188) in Database Altiris_Incidents (6) has a
reorganization percent of 27%
Table wu_custom_instance (2146106686) in Database Altiris_Incidents (6) has
a reorganization percent of 33%
Table wuci_history (254623950) in Database Altiris_Incidents (6) has a
reorganization percent of 25%
Table tb_Stock (1063674837) in Database SidataSQL_DC (7) has a
reorganization percent of 31%
Table tb_StockInfo (110623437) in Database SidataSQL_DC (7) has a
reorganization percent of 38%
Table tb_Supplier (142623551) in Database SidataSQL_DC (7) has a
reorganization percent of 25%
Table tb_StockCons (1735677231) in Database SidataSQL_DC (7) has a
reorganization percent of 24%
Table tb_Transaction (174623665) in Database SidataSQL_DC (7) has a
reorganization percent of 39%
Table tb_Purchase (292196091) in Database SidataSQL_DC (7) has a
reorganization percent of 48%
what does the organization percent really means ? the same as the fill facto
r
(i am using the default which is 100%) and what does it mean for my SQL
Server ?
appreciate any advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1Sounds like a question for Idera not SQL Server. Those are their own
custom messages and I assume they mean fragmentation % but who knows. I
think they need to come up with a better alert message than that<g>.
Andrew J. Kelly SQL MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:593d6713d72ac@.uwe...
> Hi All ,
> i am using a 3rd s/w(Idera SQL DM to be exact) to perform diagonistics
> on
> my SQL 2000 Server. from the alert log , i got some of the following
> messages
> :
> able managed_object (1445580188) in Database Altiris_Incidents (6) has a
> reorganization percent of 27%
> Table wu_custom_instance (2146106686) in Database Altiris_Incidents (6)
> has
> a reorganization percent of 33%
> Table wuci_history (254623950) in Database Altiris_Incidents (6) has a
> reorganization percent of 25%
> Table tb_Stock (1063674837) in Database SidataSQL_DC (7) has a
> reorganization percent of 31%
> Table tb_StockInfo (110623437) in Database SidataSQL_DC (7) has a
> reorganization percent of 38%
> Table tb_Supplier (142623551) in Database SidataSQL_DC (7) has a
> reorganization percent of 25%
> Table tb_StockCons (1735677231) in Database SidataSQL_DC (7) has a
> reorganization percent of 24%
> Table tb_Transaction (174623665) in Database SidataSQL_DC (7) has a
> reorganization percent of 39%
> Table tb_Purchase (292196091) in Database SidataSQL_DC (7) has a
> reorganization percent of 48%
>
> what does the organization percent really means ? the same as the fill
> factor
> (i am using the default which is 100%) and what does it mean for my SQL
> Server ?
> appreciate any advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200512/1|||tks Andrew i'l check wif Idera then but on further checks it as per wat you
have mentioned(i.e fragmentation %) as the system is allowing me to whether
to do a defrag or not .
but in SQL Server , which DBCC or other commands can allow me to do a defrag
on the tables
tks & rdgs
Andrew J. Kelly wrote:[vbcol=seagreen]
>Sounds like a question for Idera not SQL Server. Those are their own
>custom messages and I assume they mean fragmentation % but who knows. I
>think they need to come up with a better alert message than that<g>.
>
>[quoted text clipped - 39 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1|||In SQL 2000:
* DBCC SHOWCONTIG will show you the fragmentation stats on indexes.
* DBCC DBREINDEX & DBCC INDEXDEFRAG will rebuild & defragment your
indexes respectively.
In SQL 2005:
* Querying the dynamic management view
sys.dm_db_index_physical_stats will show you the index fragmentation
* use the ALTER INDEX statement to rebuild or defragment your indexes
*mike hodgson*
blog: http://sqlnerd.blogspot.com
maxzsim via droptable.com wrote:

>tks Andrew i'l check wif Idera then but on further checks it as per wat you
>have mentioned(i.e fragmentation %) as the system is allowing me to whether
>to do a defrag or not .
>but in SQL Server , which DBCC or other commands can allow me to do a defra
g
>on the tables
>tks & rdgs
>Andrew J. Kelly wrote:
>
>
>|||tks Mike
Mike Hodgson wrote:[vbcol=seagreen]
>In SQL 2000:
> * DBCC SHOWCONTIG will show you the fragmentation stats on indexes.
> * DBCC DBREINDEX & DBCC INDEXDEFRAG will rebuild & defragment your
> indexes respectively.
>In SQL 2005:
> * Querying the dynamic management view
> sys.dm_db_index_physical_stats will show you the index fragmentation
> * use the ALTER INDEX statement to rebuild or defragment your indexes
>--
>*mike hodgson*
>blog: http://sqlnerd.blogspot.com
>
>[quoted text clipped - 24 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1

reorganization percent -- ?

Hi All ,
i am using a 3rd s/w(Idera SQL DM to be exact) to perform diagonistics on
my SQL 2000 Server. from the alert log , i got some of the following messages
:
able managed_object (1445580188) in Database Altiris_Incidents (6) has a
reorganization percent of 27%
Table wu_custom_instance (2146106686) in Database Altiris_Incidents (6) has
a reorganization percent of 33%
Table wuci_history (254623950) in Database Altiris_Incidents (6) has a
reorganization percent of 25%
Table tb_Stock (1063674837) in Database SidataSQL_DC (7) has a
reorganization percent of 31%
Table tb_StockInfo (110623437) in Database SidataSQL_DC (7) has a
reorganization percent of 38%
Table tb_Supplier (142623551) in Database SidataSQL_DC (7) has a
reorganization percent of 25%
Table tb_StockCons (1735677231) in Database SidataSQL_DC (7) has a
reorganization percent of 24%
Table tb_Transaction (174623665) in Database SidataSQL_DC (7) has a
reorganization percent of 39%
Table tb_Purchase (292196091) in Database SidataSQL_DC (7) has a
reorganization percent of 48%
what does the organization percent really means ? the same as the fill factor
(i am using the default which is 100%) and what does it mean for my SQL
Server ?
appreciate any advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1Sounds like a question for Idera not SQL Server. Those are their own
custom messages and I assume they mean fragmentation % but who knows. I
think they need to come up with a better alert message than that<g>.
--
Andrew J. Kelly SQL MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:593d6713d72ac@.uwe...
> Hi All ,
> i am using a 3rd s/w(Idera SQL DM to be exact) to perform diagonistics
> on
> my SQL 2000 Server. from the alert log , i got some of the following
> messages
> :
> able managed_object (1445580188) in Database Altiris_Incidents (6) has a
> reorganization percent of 27%
> Table wu_custom_instance (2146106686) in Database Altiris_Incidents (6)
> has
> a reorganization percent of 33%
> Table wuci_history (254623950) in Database Altiris_Incidents (6) has a
> reorganization percent of 25%
> Table tb_Stock (1063674837) in Database SidataSQL_DC (7) has a
> reorganization percent of 31%
> Table tb_StockInfo (110623437) in Database SidataSQL_DC (7) has a
> reorganization percent of 38%
> Table tb_Supplier (142623551) in Database SidataSQL_DC (7) has a
> reorganization percent of 25%
> Table tb_StockCons (1735677231) in Database SidataSQL_DC (7) has a
> reorganization percent of 24%
> Table tb_Transaction (174623665) in Database SidataSQL_DC (7) has a
> reorganization percent of 39%
> Table tb_Purchase (292196091) in Database SidataSQL_DC (7) has a
> reorganization percent of 48%
>
> what does the organization percent really means ? the same as the fill
> factor
> (i am using the default which is 100%) and what does it mean for my SQL
> Server ?
> appreciate any advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||tks Andrew i'l check wif Idera then but on further checks it as per wat you
have mentioned(i.e fragmentation %) as the system is allowing me to whether
to do a defrag or not .
but in SQL Server , which DBCC or other commands can allow me to do a defrag
on the tables
tks & rdgs
Andrew J. Kelly wrote:
>Sounds like a question for Idera not SQL Server. Those are their own
>custom messages and I assume they mean fragmentation % but who knows. I
>think they need to come up with a better alert message than that<g>.
>> Hi All ,
>[quoted text clipped - 39 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||This is a multi-part message in MIME format.
--050700080400090100010001
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
In SQL 2000:
* DBCC SHOWCONTIG will show you the fragmentation stats on indexes.
* DBCC DBREINDEX & DBCC INDEXDEFRAG will rebuild & defragment your
indexes respectively.
In SQL 2005:
* Querying the dynamic management view
sys.dm_db_index_physical_stats will show you the index fragmentation
* use the ALTER INDEX statement to rebuild or defragment your indexes
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
maxzsim via SQLMonster.com wrote:
>tks Andrew i'l check wif Idera then but on further checks it as per wat you
>have mentioned(i.e fragmentation %) as the system is allowing me to whether
>to do a defrag or not .
>but in SQL Server , which DBCC or other commands can allow me to do a defrag
>on the tables
>tks & rdgs
>Andrew J. Kelly wrote:
>
>>Sounds like a question for Idera not SQL Server. Those are their own
>>custom messages and I assume they mean fragmentation % but who knows. I
>>think they need to come up with a better alert message than that<g>.
>>
>>Hi All ,
>>
>>[quoted text clipped - 39 lines]
>>
>>tks & rdgs
>>
>
>
--050700080400090100010001
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>In SQL 2000:<br>
</tt>
<ul>
<li><tt>DBCC SHOWCONTIG will show you the fragmentation stats on
indexes. <br>
</tt></li>
<li><tt>DBCC DBREINDEX & DBCC INDEXDEFRAG will rebuild &
defragment your indexes respectively.</tt></li>
</ul>
<tt>In SQL 2005:<br>
</tt>
<ul>
<li><tt>Querying the dynamic management view
sys.dm_db_index_physical_stats will show you the index fragmentation</tt></li>
<li><tt>use the ALTER INDEX statement to rebuild or defragment your
indexes<br>
</tt></li>
</ul>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
maxzsim via SQLMonster.com wrote:
<blockquote cite="mid593db7aed34e0@.uwe" type="cite">
<pre wrap="">tks Andrew i'l check wif Idera then but on further checks it as per wat you
have mentioned(i.e fragmentation %) as the system is allowing me to whether
to do a defrag or not .
but in SQL Server , which DBCC or other commands can allow me to do a defrag
on the tables
tks & rdgs
Andrew J. Kelly wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Sounds like a question for Idera not SQL Server. Those are their own
custom messages and I assume they mean fragmentation % but who knows. I
think they need to come up with a better alert message than that<g>.
</pre>
<blockquote type="cite">
<pre wrap="">Hi All ,
</pre>
</blockquote>
<pre wrap="">[quoted text clipped - 39 lines]
</pre>
<blockquote type="cite">
<pre wrap="">tks & rdgs
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--050700080400090100010001--|||tks Mike
Mike Hodgson wrote:
>In SQL 2000:
> * DBCC SHOWCONTIG will show you the fragmentation stats on indexes.
> * DBCC DBREINDEX & DBCC INDEXDEFRAG will rebuild & defragment your
> indexes respectively.
>In SQL 2005:
> * Querying the dynamic management view
> sys.dm_db_index_physical_stats will show you the index fragmentation
> * use the ALTER INDEX statement to rebuild or defragment your indexes
>--
>*mike hodgson*
>blog: http://sqlnerd.blogspot.com
>>tks Andrew i'l check wif Idera then but on further checks it as per wat you
>>have mentioned(i.e fragmentation %) as the system is allowing me to whether
>[quoted text clipped - 24 lines]
>>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1

Reorganization and logs

I have a db maintenance plan set up that reorganizes my data once a week. I
noticed that the SQL Server Log does not indicate that this has run.
However, if I use the option to 'Write report to a text file in directory:',
a .txt file is created that indicates the reorganization has run. Why does
this not show up in the SQL Server Log as my reindexing and backups do? I'm
running SQL 2000.
The backups really shouldn't be in there either in my opinion. The log is
mainly for error info and a successful backup can be found in the job
history. If you want to know if and when it ran you can check the Job
History and the MP history.
Andrew J. Kelly SQL MVP
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:B604E4EE-8C77-4B5D-8B05-EBFC42BE0023@.microsoft.com...
> I have a db maintenance plan set up that reorganizes my data once a week.
I
> noticed that the SQL Server Log does not indicate that this has run.
> However, if I use the option to 'Write report to a text file in
directory:',
> a .txt file is created that indicates the reorganization has run. Why
does
> this not show up in the SQL Server Log as my reindexing and backups do?
I'm
> running SQL 2000.

Reorg indexes w/o log growing - how?

Hello:
is there a way to reorg indexes without the log growing to the size of the
whole db and more?
regards1. backup the log frequently
2. Change DB to "Simple Recovery" Mode
Greg Jackson
PDX, Oregon|||You could try DBCC INDEXDEFRAG instead of DBREINDEX. Sometimes, it will
produce less log records. But the mileage does vary. Also, you can consider
bulk logged recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Vadim Rapp" <vr@.myrealbox.nospam.com> wrote in message
news:O%23k6hXGEEHA.2576@.TK2MSFTNGP11.phx.gbl...
> Hello:
> is there a way to reorg indexes without the log growing to the size of the
> whole db and more?
> regards
>

Rendering Reports with IE7

Ever since I have installed IE7 I keep getting prompted to log in several times. It seems to work fine with IE6. Any help would be great

Thanks

Will

Do you see a status band on the top of the page prompting to enable Intranet settings?