Database fragmentation

Hello,
How can I mesure the database fragmentation ? Cause DBCC SHOWCONTIG shows obects fragmentation only. I would like to see the whole database fragmentation.
Thanks for helpYour database is 60% fragmented. What does it say? What do you do?|||I just found my answer:

USE pubs
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES|||Fragmentation may be on the file system level, which may be corrected by detaching the database, defragging the disk, reattaching the db, and doing the db level defrag which you just discovered.|||The file fragmentation on the disk is invisible to SQL Server... It sees a file as a contiguous stream of 8 Kb pages, but it is quite ignorant of how those pages are actually laid out on the disk(s).

rdjabarov brings up a good point though, in that even a table or database that is contiguous from SQL Server's perspective might be on a Windoze file that has grown many times, so the head still has to hop all over creation to retrieve data. It is a good idea to periodically detach the database and defrag the Windoze disk after the database file(s) grow.

-PatP|||So what defragmentation tool is available that is able to defrag a database file considering it's internal structure?

I've seen several implementations of defragtools; one would only move about the occupied sectors, making it easier for the OS the find free space. Others would do a file approach, selecting the sectors involved and move those around. I've had a defrag tool that did a file approach and moved the first sectors of a file to the beginning, working to the end. It took ages, _and_ the tool somehow mixed up sectors, rendering some files useless.

With database files it's tricky: indexes, tables, procedures, views.. where to store them? Would a tool consider? Does it speed up performance or does it go down the drain?

I have to admit it's been a long time since I last looked at defragmentation tools; so I don't know how advanced they are these days and how well they work. SQLServer already claims a contiguos block of data when a db is created, I think adding new parts works the same. If a growth of a db is the only change to the disk, and sqlserver claims the new block right after the db-block, what are the odds a defrag tool doesn't leave it alone and make it worse?

I'm not an expert on defragmentation, I'm also not an expert on sqlserver, I know from experience a defrag helps when a lot of small files are scattered around the disk, but I wonder the effect on db-files.|||There are two kinds of fragmentation that apply here.

For internal fragementation, the only fix that I know of is to rebuild the index or indicies affected. DBCC REINDEX works well for this.

For file fragementation, you can either detach the database or stop the SQL Server and use the build in defragmentation tool, or you can buy Diskeeper.

-PatP|||But does the command

DBCC SHRINKDATABASE (pubs,NOTRUNCATE)

move pages in the beginning of the database file ? Can this contribute to decrease internal fragmentation ?|||No, DBCC SHRINKDATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp) doesn't move any pages. Even if it did move them, it would change the fragmentation, but rarely decrease it.

-PatP|||So, I don't understand the aim of this command (with NOTRUNCATE option).|||NOTRUNCATE will not release freed space back to OS.|||I'm sorry, I didn't catch Kuzco's reply earlier. I was thinking in terms of the TRUNCATEONLY option when I wrote that the DBCC SHRINKDATABASE didn't move pages. The NOTRUNCATE option can move pages, but it doesn't always do what you'd expect it to... See BOL on DBCC SHRINKDATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp) or better yet Inside SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=Xz6NhUAA2Z&isbn=0735609985&itm=1) for more detailed descriptions.

-PatP|||Ok, thanks for your informations

database failure to restore from NT backup

One of our customers had a problem with SQL Server 7. We have instigated a
work around, but they want to know what the root cause was.
Problem description
It seems that they had disk problems, then wanted to restore the database fr
om a backup. They were not doing backups to disk, just to shutting down the
services & doing a cold NT backup to tape.
Error Details
The server lost a disc (one of five from RAID5 array). After it was fixed,
the data was restored from a backup tape and the following appeared in the S
QL Server activity log. The database was in suspect mode.
2004-04-29 19:36:45.31 spid9 Starting up database 'dataname'.
2004-04-29 19:36:45.31 spid9 Opening file d:\SQL\data\dataname_Data.MDF.
2004-04-29 19:36:45.31 spid9 Opening file e:\SQL\data\dataname2_Data.NDF.
2004-04-29 19:36:45.31 spid9 The header for file 'e:\SQL\data\dataname2_D
ata.NDF' is not a valid database file header. The PageAudit property is inco
rrect.
2004-04-29 19:36:45.32 spid9 Device activation error. The physical file n
ame 'e:\SQL\data\dataname2_Data.NDF' may be incorrect.
2004-04-29 19:36:45.32 spid9 Opening file d:\SQL\data\dataname_Log.LDF.
2004-04-29 19:36:45.32 spid9 Opening file e:\SQL\data\dataname2_Log.LDF.
System Details
SQL Server 7
NT 4
Bull Express5800 180Ra-7 Rack P-III Xeon 700MHz/1MB Model 0
4 * PIII 700 Mhz
2 GB RAM
40/80GB SCSI DLT Internal Tape Drive
9.1GB Ultra 160/m SCSI HDD 7.2Krpm (1")
32 bit PCI 10/100Mbps Ethernet Adapter (Intel Ethernet Pro100+)
Regards
DavidDavid,
This scenario is a typical case why you should do backup using SQL Server BA
CKUP command instead of just
picking up the physical files. In general, it should work assuming that you
indeed get a consistent snapshot
of all files that constitutes a database. IMO, your best bet is to open a ca
se with MS Support for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Lascelles" <anonymous@.discussions.microsoft.com> wrote in message
news:88D14DC8-AF33-4016-B5A3-1E391707D7F6@.microsoft.com...
> One of our customers had a problem with SQL Server 7. We have instigated a work a
round, but they want to
know what the root cause was.
> Problem description
> It seems that they had disk problems, then wanted to restore the database from a b
ackup. They were not
doing backups to disk, just to shutting down the services & doing a cold NT backup to tape.[
vbcol=seagreen]
> Error Details
> The server lost a disc (one of five from RAID5 array). After it was fixed, the da
ta was restored from a[/vbcol]
backup tape and the following appeared in the SQL Server activity log. The database was in s
uspect mode.
> 2004-04-29 19:36:45.31 spid9 Starting up database 'dataname'.
> 2004-04-29 19:36:45.31 spid9 Opening file d:\SQL\data\dataname_Data.MDF
.
> 2004-04-29 19:36:45.31 spid9 Opening file e:\SQL\data\dataname2_Data.ND
F.
> 2004-04-29 19:36:45.31 spid9 The header for file 'e:\SQL\data\dataname2_Data.ND
F' is not a valid database
file header. The PageAudit property is incorrect.
> 2004-04-29 19:36:45.32 spid9 Device activation error. The physical file name[/v
bcol]
'e:\SQL\data\dataname2_Data.NDF' may be incorrect.[vbcol=seagreen]
> 2004-04-29 19:36:45.32 spid9 Opening file d:\SQL\data\dataname_Log.LDF.
> 2004-04-29 19:36:45.32 spid9 Opening file e:\SQL\data\dataname2_Log.LDF
.
> System Details
> SQL Server 7
> NT 4
> Bull Express5800 180Ra-7 Rack P-III Xeon 700MHz/1MB Model 0
> 4 * PIII 700 Mhz
> 2 GB RAM
> 40/80GB SCSI DLT Internal Tape Drive
> 9.1GB Ultra 160/m SCSI HDD 7.2Krpm (1")
> 32 bit PCI 10/100Mbps Ethernet Adapter (Intel Ethernet Pro100+)
> Regards
> David

database failure to restore from NT backup

One of our customers had a problem with SQL Server 7. We have instigated a work around, but they want to know what the root cause was
Problem descriptio
It seems that they had disk problems, then wanted to restore the database from a backup. They were not doing backups to disk, just to shutting down the services & doing a cold NT backup to tape
Error Detail
The server lost a disc (one of five from RAID5 array). After it was fixed, the data was restored from a backup tape and the following appeared in the SQL Server activity log. The database was in suspect mode.
2004-04-29 19:36:45.31 spid9 Starting up database 'dataname'
2004-04-29 19:36:45.31 spid9 Opening file d:\SQL\data\dataname_Data.MDF.
2004-04-29 19:36:45.31 spid9 Opening file e:\SQL\data\dataname2_Data.NDF.
2004-04-29 19:36:45.31 spid9 The header for file 'e:\SQL\data\dataname2_Data.NDF' is not a valid database file header. The PageAudit property is incorrect
2004-04-29 19:36:45.32 spid9 Device activation error. The physical file name 'e:\SQL\data\dataname2_Data.NDF' may be incorrect
2004-04-29 19:36:45.32 spid9 Opening file d:\SQL\data\dataname_Log.LDF.
2004-04-29 19:36:45.32 spid9 Opening file e:\SQL\data\dataname2_Log.LDF
System Detail
SQL Server
NT
Bull Express5800 180Ra-7 Rack P-III Xeon 700MHz/1MB Model
4 * PIII 700 Mh
2 GB RA
40/80GB SCSI DLT Internal Tape Driv
9.1GB Ultra 160/m SCSI HDD 7.2Krpm (1"
32 bit PCI 10/100Mbps Ethernet Adapter (Intel Ethernet Pro100+
Regard
DavidHello,
Have you seen this from Microsoft ?
http://support.microsoft.com/default.aspx?scid=kb;en-
us;268481
J
>--Original Message--
>One of our customers had a problem with SQL Server 7. We
have instigated a work around, but they want to know what
the root cause was.
>Problem description
>It seems that they had disk problems, then wanted to
restore the database from a backup. They were not doing
backups to disk, just to shutting down the services &
doing a cold NT backup to tape.
>Error Details
>The server lost a disc (one of five from RAID5 array).
After it was fixed, the data was restored from a backup
tape and the following appeared in the SQL Server activity
log. The database was in suspect mode.
>2004-04-29 19:36:45.31 spid9 Starting up
database 'dataname'.
>2004-04-29 19:36:45.31 spid9 Opening file
d:\SQL\data\dataname_Data.MDF.
>2004-04-29 19:36:45.31 spid9 Opening file
e:\SQL\data\dataname2_Data.NDF.
>2004-04-29 19:36:45.31 spid9 The header for
file 'e:\SQL\data\dataname2_Data.NDF' is not a valid
database file header. The PageAudit property is incorrect.
>2004-04-29 19:36:45.32 spid9 Device activation error.
The physical file name 'e:\SQL\data\dataname2_Data.NDF'
may be incorrect.
>2004-04-29 19:36:45.32 spid9 Opening file
d:\SQL\data\dataname_Log.LDF.
>2004-04-29 19:36:45.32 spid9 Opening file
e:\SQL\data\dataname2_Log.LDF.
>System Details
>SQL Server 7
>NT 4
>Bull Express5800 180Ra-7 Rack P-III Xeon 700MHz/1MB Model
0
>4 * PIII 700 Mhz
>2 GB RAM
>40/80GB SCSI DLT Internal Tape Drive
>9.1GB Ultra 160/m SCSI HDD 7.2Krpm (1")
>32 bit PCI 10/100Mbps Ethernet Adapter (Intel Ethernet
Pro100+)
>Regards
>David
>.
>|||David,
This scenario is a typical case why you should do backup using SQL Server BACKUP command instead of just
picking up the physical files. In general, it should work assuming that you indeed get a consistent snapshot
of all files that constitutes a database. IMO, your best bet is to open a case with MS Support for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Lascelles" <anonymous@.discussions.microsoft.com> wrote in message
news:88D14DC8-AF33-4016-B5A3-1E391707D7F6@.microsoft.com...
> One of our customers had a problem with SQL Server 7. We have instigated a work around, but they want to
know what the root cause was.
> Problem description
> It seems that they had disk problems, then wanted to restore the database from a backup. They were not
doing backups to disk, just to shutting down the services & doing a cold NT backup to tape.
> Error Details
> The server lost a disc (one of five from RAID5 array). After it was fixed, the data was restored from a
backup tape and the following appeared in the SQL Server activity log. The database was in suspect mode.
> 2004-04-29 19:36:45.31 spid9 Starting up database 'dataname'.
> 2004-04-29 19:36:45.31 spid9 Opening file d:\SQL\data\dataname_Data.MDF.
> 2004-04-29 19:36:45.31 spid9 Opening file e:\SQL\data\dataname2_Data.NDF.
> 2004-04-29 19:36:45.31 spid9 The header for file 'e:\SQL\data\dataname2_Data.NDF' is not a valid database
file header. The PageAudit property is incorrect.
> 2004-04-29 19:36:45.32 spid9 Device activation error. The physical file name
'e:\SQL\data\dataname2_Data.NDF' may be incorrect.
> 2004-04-29 19:36:45.32 spid9 Opening file d:\SQL\data\dataname_Log.LDF.
> 2004-04-29 19:36:45.32 spid9 Opening file e:\SQL\data\dataname2_Log.LDF.
> System Details
> SQL Server 7
> NT 4
> Bull Express5800 180Ra-7 Rack P-III Xeon 700MHz/1MB Model 0
> 4 * PIII 700 Mhz
> 2 GB RAM
> 40/80GB SCSI DLT Internal Tape Drive
> 9.1GB Ultra 160/m SCSI HDD 7.2Krpm (1")
> 32 bit PCI 10/100Mbps Ethernet Adapter (Intel Ethernet Pro100+)
> Regards
> David|||Juli
Thanks for the link, but the customer says that the Bull RAID controller is a Milex, not Adaptec,|||Ok, but does it use the Abaptec drivers such as graphics
tend to use the same ones ?
J
>--Original Message--
>Julie
>Thanks for the link, but the customer says that the Bull
RAID controller is a Milex, not Adaptec,
>.
>sql

database failure to restore from NT backup

One of our customers had a problem with SQL Server 7. We have instigated a work around, but they want to know what the root cause was.
Problem description
It seems that they had disk problems, then wanted to restore the database from a backup. They were not doing backups to disk, just to shutting down the services & doing a cold NT backup to tape.
Error Details
The server lost a disc (one of five from RAID5 array). After it was fixed, the data was restored from a backup tape and the following appeared in the SQL Server activity log. The database was in suspect mode.
2004-04-29 19:36:45.31 spid9 Starting up database 'dataname'.
2004-04-29 19:36:45.31 spid9 Opening file d:\SQL\data\dataname_Data.MDF.
2004-04-29 19:36:45.31 spid9 Opening file e:\SQL\data\dataname2_Data.NDF.
2004-04-29 19:36:45.31 spid9 The header for file 'e:\SQL\data\dataname2_Data.NDF' is not a valid database file header. The PageAudit property is incorrect.
2004-04-29 19:36:45.32 spid9 Device activation error. The physical file name 'e:\SQL\data\dataname2_Data.NDF' may be incorrect.
2004-04-29 19:36:45.32 spid9 Opening file d:\SQL\data\dataname_Log.LDF.
2004-04-29 19:36:45.32 spid9 Opening file e:\SQL\data\dataname2_Log.LDF.
System Details
SQL Server 7
NT 4
Bull Express5800 180Ra-7 Rack P-III Xeon 700MHz/1MB Model 0
4 * PIII 700 Mhz
2 GB RAM
40/80GB SCSI DLT Internal Tape Drive
9.1GB Ultra 160/m SCSI HDD 7.2Krpm (1")
32 bit PCI 10/100Mbps Ethernet Adapter (Intel Ethernet Pro100+)
Regards
David
David,
This scenario is a typical case why you should do backup using SQL Server BACKUP command instead of just
picking up the physical files. In general, it should work assuming that you indeed get a consistent snapshot
of all files that constitutes a database. IMO, your best bet is to open a case with MS Support for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Lascelles" <anonymous@.discussions.microsoft.com> wrote in message
news:88D14DC8-AF33-4016-B5A3-1E391707D7F6@.microsoft.com...
> One of our customers had a problem with SQL Server 7. We have instigated a work around, but they want to
know what the root cause was.
> Problem description
> It seems that they had disk problems, then wanted to restore the database from a backup. They were not
doing backups to disk, just to shutting down the services & doing a cold NT backup to tape.
> Error Details
> The server lost a disc (one of five from RAID5 array). After it was fixed, the data was restored from a
backup tape and the following appeared in the SQL Server activity log. The database was in suspect mode.
> 2004-04-29 19:36:45.31 spid9 Starting up database 'dataname'.
> 2004-04-29 19:36:45.31 spid9 Opening file d:\SQL\data\dataname_Data.MDF.
> 2004-04-29 19:36:45.31 spid9 Opening file e:\SQL\data\dataname2_Data.NDF.
> 2004-04-29 19:36:45.31 spid9 The header for file 'e:\SQL\data\dataname2_Data.NDF' is not a valid database
file header. The PageAudit property is incorrect.
> 2004-04-29 19:36:45.32 spid9 Device activation error. The physical file name
'e:\SQL\data\dataname2_Data.NDF' may be incorrect.
> 2004-04-29 19:36:45.32 spid9 Opening file d:\SQL\data\dataname_Log.LDF.
> 2004-04-29 19:36:45.32 spid9 Opening file e:\SQL\data\dataname2_Log.LDF.
> System Details
> SQL Server 7
> NT 4
> Bull Express5800 180Ra-7 Rack P-III Xeon 700MHz/1MB Model 0
> 4 * PIII 700 Mhz
> 2 GB RAM
> 40/80GB SCSI DLT Internal Tape Drive
> 9.1GB Ultra 160/m SCSI HDD 7.2Krpm (1")
> 32 bit PCI 10/100Mbps Ethernet Adapter (Intel Ethernet Pro100+)
> Regards
> David