Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Richard101
Starting Member
30 Posts |
 Posted - 2008-11-24 : 10:57:05
|
| Hi Y'all, I'm looking after a (sql2k) customer for a week whilst the regular DBA is away (on a SQL2008 course I believe).Mostly I've seen space issues and bad maint-plans. However I've now come to a scary low-space call were a single datafile is over 200 GB in size and doesn't seem to have been backed up for over a year.My first thought (after shrinking) was to create a second datafile in another drive (the current drive has only 400MB of 250 GB free).However, I'm real, real reluctant to play with an unbacked-up database (oh and btw there's no room to back it up locally).Question (if I'm going to be a wus and just keep it alive for a week) - will maintaining the indexes or statistics free-up disk-space?(btw there's no log issues - its in simple recovery mode. Also, may-not be relevent, but the application seems to be a TIF document store). |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-24 : 11:07:04
|
What does DBCC SHOWCONTIG says about indexes? And tables?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Richard101
Starting Member
30 Posts |
Posted - 2008-11-24 : 11:26:49
|
| DBCC SHOWCONTIG scanning 'sysobjects' table...Table: 'sysobjects' (1); index ID: 1, database ID: 14TABLE level scan performed.- Pages Scanned................................: 1- Extents Scanned..............................: 1- Extent Switches..............................: 0- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 0.00%- Avg. Bytes Free per Page.....................: 3568.0- Avg. Page Density (full).....................: 55.92%DBCC SHOWCONTIG scanning 'sysindexes' table...Table: 'sysindexes' (2); index ID: 1, database ID: 14TABLE level scan performed.- Pages Scanned................................: 2- Extents Scanned..............................: 2- Extent Switches..............................: 1- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 50.00%- Avg. Bytes Free per Page.....................: 4170.0- Avg. Page Density (full).....................: 48.48%DBCC SHOWCONTIG scanning 'syscolumns' table...Table: 'syscolumns' (3); index ID: 1, database ID: 14TABLE level scan performed.- Pages Scanned................................: 5- Extents Scanned..............................: 5- Extent Switches..............................: 4- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 20.00% [1:5]- Logical Scan Fragmentation ..................: 20.00%- Extent Scan Fragmentation ...................: 60.00%- Avg. Bytes Free per Page.....................: 3284.0- Avg. Page Density (full).....................: 59.43%DBCC SHOWCONTIG scanning 'systypes' table...Table: 'systypes' (4); index ID: 1, database ID: 14TABLE level scan performed.- Pages Scanned................................: 1- Extents Scanned..............................: 1- Extent Switches..............................: 0- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 0.00%- Avg. Bytes Free per Page.....................: 6712.0- Avg. Page Density (full).....................: 17.07%DBCC SHOWCONTIG scanning 'syscomments' table...Table: 'syscomments' (6); index ID: 1, database ID: 14TABLE level scan performed.- Pages Scanned................................: 7- Extents Scanned..............................: 3- Extent Switches..............................: 2- Avg. Pages per Extent........................: 2.3- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]- Logical Scan Fragmentation ..................: 28.57%- Extent Scan Fragmentation ...................: 33.33%- Avg. Bytes Free per Page.....................: 3818.6- Avg. Page Density (full).....................: 52.82%DBCC SHOWCONTIG scanning 'sysfiles1' table...Table: 'sysfiles1' (8); index ID: 0, database ID: 14TABLE level scan performed.- Pages Scanned................................: 1- Extents Scanned..............................: 1- Extent Switches..............................: 0- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]- Extent Scan Fragmentation ...................: 0.00%- Avg. Bytes Free per Page.....................: 6508.0- Avg. Page Density (full).....................: 19.59%DBCC SHOWCONTIG scanning 'syspermissions' table...Table: 'syspermissions' (9); index ID: 1, database ID: 14TABLE level scan performed.- Pages Scanned................................: 1- Extents Scanned..............................: 1- Extent Switches..............................: 0- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 0.00%- Avg. Bytes Free per Page.....................: 7084.0- Avg. Page Density (full).....................: 12.48%DBCC SHOWCONTIG scanning 'sysusers' table...Table: 'sysusers' (10); index ID: 1, database ID: 14TABLE level scan performed.- Pages Scanned................................: 1- Extents Scanned..............................: 1- Extent Switches..............................: 0- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 0.00%- Avg. Bytes Free per Page.....................: 7006.0- Avg. Page Density (full).....................: 13.44%DBCC SHOWCONTIG scanning 'sysdepends' table...Table: 'sysdepends' (12); index ID: 1, database ID: 14TABLE level scan performed.- Pages Scanned................................: 1- Extents Scanned..............................: 1- Extent Switches..............................: 0- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 0.00%- Avg. Bytes Free per Page.....................: 166.0- Avg. Page Density (full).....................: 97.95%DBCC SHOWCONTIG scanning 'sysfulltextcatalogs' table...Table: 'sysfulltextcatalogs' (19); index ID: 1, database ID: 14TABLE level scan performed.- Pages Scanned................................: 1- Extents Scanned..............................: 1- Extent Switches..............................: 0- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 0.00%- Avg. Bytes Free per Page.....................: 8058.0- Avg. Page Density (full).....................: 0.44%DBCC SHOWCONTIG scanning 'sysfulltextnotify' table...Table: 'sysfulltextnotify' (24); index ID: 1, database ID: 14TABLE level scan performed.- Pages Scanned................................: 1- Extents Scanned..............................: 1- Extent Switches..............................: 0- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 0.00%- Avg. Bytes Free per Page.....................: 8094.0- Avg. Page Density (full).....................: 0.00%DBCC SHOWCONTIG scanning 'sysfilegroups' table...Table: 'sysfilegroups' (96); index ID: 1, database ID: 14TABLE level scan performed.- Pages Scanned................................: 1- Extents Scanned..............................: 1- Extent Switches..............................: 0- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 0.00%- Avg. Bytes Free per Page.....................: 8058.0- Avg. Page Density (full).....................: 0.44%DBCC SHOWCONTIG scanning 'Wis' table...Table: 'Wis' (2009058193); index ID: 0, database ID: 14TABLE level scan performed.- Pages Scanned................................: 1- Extents Scanned..............................: 1- Extent Switches..............................: 0- Avg. Pages per Extent........................: 1.0- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]- Extent Scan Fragmentation ...................: 0.00%- Avg. Bytes Free per Page.....................: 8044.0- Avg. Page Density (full).....................: 0.62%DBCC SHOWCONTIG scanning 'docs' table...Table: 'docs' (2025058250); index ID: 1, database ID: 14TABLE level scan performed.- Pages Scanned................................: 26431- Extents Scanned..............................: 3323- Extent Switches..............................: 13016- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 25.38% [3304:13017]- Logical Scan Fragmentation ..................: 20.86%- Extent Scan Fragmentation ...................: 21.91%- Avg. Bytes Free per Page.....................: 1870.8- Avg. Page Density (full).....................: 76.89%DBCC execution completed. If DBCC printed error messages, contact your system administrator. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-24 : 13:42:59
|
| Run Sp_spaceused. See free space. |
|
|
|
|
|
|
|
|
freeing space