cant shrink data file

sql2k sp3
Before I start, let me assure you that the 5 gigs of free
space in my db is in the data file, not the log file. I
already shrunk the 7 gig log file with no problem. Now
when I run dbcc shrinkfile(db_data_file) it runs for a
long time and then barely shrinks anything. Am I missing
something here? Any ideas?
Thanks in advance.Chris,
You may need to check for fragmentation.Refer DBCC SHOWCONTIG, DBCC
INDEXDEFRAG in BooksOnLine.
Also, may be..
PRB: DBCC SHRINKFILE and SHRINKDATABASE Commands May Not Work Because of
Sparsely Populated Text, Ntext, or Image Columns
http://support.microsoft.com/default.aspx?scid=kb;en-us;324432
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"chris" <chrisr@.fingps.com> wrote in message
news:3bbe01c37626$d832d270$a301280a@.phx.gbl...
> sql2k sp3
> Before I start, let me assure you that the 5 gigs of free
> space in my db is in the data file, not the log file. I
> already shrunk the 7 gig log file with no problem. Now
> when I run dbcc shrinkfile(db_data_file) it runs for a
> long time and then barely shrinks anything. Am I missing
> something here? Any ideas?
> Thanks in advance.
>|||If you want to see where your data is allocated through the data file go to
www.sqlfe.com. You can see what is at the end of the data file.
--
Barry McAuslin
support@.sqlfe.com
http://www.sqlfe.com
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23PZlOfidDHA.828@.TK2MSFTNGP11.phx.gbl...
> Chris,
> You may need to check for fragmentation.Refer DBCC SHOWCONTIG, DBCC
> INDEXDEFRAG in BooksOnLine.
> Also, may be..
> PRB: DBCC SHRINKFILE and SHRINKDATABASE Commands May Not Work Because of
> Sparsely Populated Text, Ntext, or Image Columns
> http://support.microsoft.com/default.aspx?scid=kb;en-us;324432
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "chris" <chrisr@.fingps.com> wrote in message
> news:3bbe01c37626$d832d270$a301280a@.phx.gbl...
> > sql2k sp3
> >
> > Before I start, let me assure you that the 5 gigs of free
> > space in my db is in the data file, not the log file. I
> > already shrunk the 7 gig log file with no problem. Now
> > when I run dbcc shrinkfile(db_data_file) it runs for a
> > long time and then barely shrinks anything. Am I missing
> > something here? Any ideas?
> > Thanks in advance.
> >
> >
>

Can't shrink a database

Hi there,
I am running SQL2005 SP1 Standard.
I have a database that was originally a SQL2K db that was rolled up to 2K5.
Everything was fine with it until I truncated a few tables and dropped a
large column & index on a few tables. Since then the following symptoms are
present:
1) The size of the datafile and log file are about 2.5x the size of the
actual data
2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
3) The shrink dialog with the file type set to "Log" shows avaialble free
space to be 98% but shrinking it does not release any of the free space.
I have tried backing up the database, deleting it and restoring it but the
inflated log file still exists. I've also tried creating a new blank DB
(versus letting the restore create it) and restoring into that but the
problem persists.
Does anyone have any ideas on how to shrink this database down?
Thanks,
--
-Steven1) Is it the data or the log file which is "too big"?
2) Read about DBCC UPDATEUSAGE
3) See http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steven Berkovitz" <mbcdev@.community.nospam> wrote in message
news:F4B6BA40-9143-4D52-BC8F-3FB9CFBEB133@.microsoft.com...
> Hi there,
> I am running SQL2005 SP1 Standard.
> I have a database that was originally a SQL2K db that was rolled up to 2K5.
> Everything was fine with it until I truncated a few tables and dropped a
> large column & index on a few tables. Since then the following symptoms are
> present:
> 1) The size of the datafile and log file are about 2.5x the size of the
> actual data
> 2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
> 3) The shrink dialog with the file type set to "Log" shows avaialble free
> space to be 98% but shrinking it does not release any of the free space.
> I have tried backing up the database, deleting it and restoring it but the
> inflated log file still exists. I've also tried creating a new blank DB
> (versus letting the restore create it) and restoring into that but the
> problem persists.
> Does anyone have any ideas on how to shrink this database down?
> Thanks,
> --
> -Steven|||Hi Steven,
I would like to know the issue status and whether or not you need further
research.
You can try setting the recovery mode to simple, backing up your database
completely, truncating the log with BACKUP LOG ... WITH TRUNCATE ONLY, and
then use DBCC SHRINKFILE to shrink the log file.
Have a great day!
Charles Wang
Microsoft Online Community Support

Can't shrink a database

Hi there,
I am running SQL2005 SP1 Standard.
I have a database that was originally a SQL2K db that was rolled up to 2K5.
Everything was fine with it until I truncated a few tables and dropped a
large column & index on a few tables. Since then the following symptoms are
present:
1) The size of the datafile and log file are about 2.5x the size of the
actual data
2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
3) The shrink dialog with the file type set to "Log" shows avaialble free
space to be 98% but shrinking it does not release any of the free space.
I have tried backing up the database, deleting it and restoring it but the
inflated log file still exists. I've also tried creating a new blank DB
(versus letting the restore create it) and restoring into that but the
problem persists.
Does anyone have any ideas on how to shrink this database down?
Thanks,
--
-Steven1) Is it the data or the log file which is "too big"?
2) Read about DBCC UPDATEUSAGE
3) See http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steven Berkovitz" <mbcdev@.community.nospam> wrote in message
news:F4B6BA40-9143-4D52-BC8F-3FB9CFBEB133@.microsoft.com...
> Hi there,
> I am running SQL2005 SP1 Standard.
> I have a database that was originally a SQL2K db that was rolled up to 2K5
.
> Everything was fine with it until I truncated a few tables and dropped a
> large column & index on a few tables. Since then the following symptoms a
re
> present:
> 1) The size of the datafile and log file are about 2.5x the size of the
> actual data
> 2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
> 3) The shrink dialog with the file type set to "Log" shows avaialble free
> space to be 98% but shrinking it does not release any of the free space.
> I have tried backing up the database, deleting it and restoring it but the
> inflated log file still exists. I've also tried creating a new blank DB
> (versus letting the restore create it) and restoring into that but the
> problem persists.
> Does anyone have any ideas on how to shrink this database down?
> Thanks,
> --
> -Steven|||Hi Steven,
I would like to know the issue status and whether or not you need further
research.
You can try setting the recovery mode to simple, backing up your database
completely, truncating the log with BACKUP LOG ... WITH TRUNCATE ONLY, and
then use DBCC SHRINKFILE to shrink the log file.
Have a great day!
Charles Wang
Microsoft Online Community Supportsql

Cant make alert job work.

sql2k
sp4
Howdy all. I set up an Alert for deadlocks (# 1205) and have the checkbox
checked to fire off a job when it occurs. I then generate a deadlock, but the
job never gets fired off. Some important fun facts:
1; I ran dbcc traceon with 1204 and 1205 to log deadlocks.
2; I've tried running different jobs manually. They both work.
On the advice I got last week, I have verified that the Event Log service
and SQL Server Agent are both running. Its probably worth noting though that
while I do see the deadlock in the SQL Error Log, it doesnnt show up in the
Event Viewer.
Any ideas?
TIA, ChrisRYou need to configure 1205 so it is written to the eventlog. In EM, right-click the server, Manage
Messages. Here you search for 1205 and check to write to eventlog. In 2005, you cannot customize
system messages this way, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:EA439440-08D7-4BBA-8EA5-E69C4DEC6C44@.microsoft.com...
> sql2k
> sp4
> Howdy all. I set up an Alert for deadlocks (# 1205) and have the checkbox
> checked to fire off a job when it occurs. I then generate a deadlock, but the
> job never gets fired off. Some important fun facts:
> 1; I ran dbcc traceon with 1204 and 1205 to log deadlocks.
> 2; I've tried running different jobs manually. They both work.
> On the advice I got last week, I have verified that the Event Log service
> and SQL Server Agent are both running. Its probably worth noting though that
> while I do see the deadlock in the SQL Error Log, it doesnnt show up in the
> Event Viewer.
> Any ideas?
> TIA, ChrisR
>|||I keep trying to add this but it doesnt save it. I add it, hit OK, reopen and
its gone. Im logged in as local admin so I know thats not it. Any ideas?
"Tibor Karaszi" wrote:
> You need to configure 1205 so it is written to the eventlog. In EM, right-click the server, Manage
> Messages. Here you search for 1205 and check to write to eventlog. In 2005, you cannot customize
> system messages this way, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:EA439440-08D7-4BBA-8EA5-E69C4DEC6C44@.microsoft.com...
> > sql2k
> > sp4
> >
> > Howdy all. I set up an Alert for deadlocks (# 1205) and have the checkbox
> > checked to fire off a job when it occurs. I then generate a deadlock, but the
> > job never gets fired off. Some important fun facts:
> >
> > 1; I ran dbcc traceon with 1204 and 1205 to log deadlocks.
> > 2; I've tried running different jobs manually. They both work.
> >
> > On the advice I got last week, I have verified that the Event Log service
> > and SQL Server Agent are both running. Its probably worth noting though that
> > while I do see the deadlock in the SQL Error Log, it doesnnt show up in the
> > Event Viewer.
> >
> > Any ideas?
> >
> > TIA, ChrisR
> >
>|||Tibor I just realized this is more of a "not write to error log" issue, so I
reposted accordingly, thanks.
"Tibor Karaszi" wrote:
> You need to configure 1205 so it is written to the eventlog. In EM, right-click the server, Manage
> Messages. Here you search for 1205 and check to write to eventlog. In 2005, you cannot customize
> system messages this way, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:EA439440-08D7-4BBA-8EA5-E69C4DEC6C44@.microsoft.com...
> > sql2k
> > sp4
> >
> > Howdy all. I set up an Alert for deadlocks (# 1205) and have the checkbox
> > checked to fire off a job when it occurs. I then generate a deadlock, but the
> > job never gets fired off. Some important fun facts:
> >
> > 1; I ran dbcc traceon with 1204 and 1205 to log deadlocks.
> > 2; I've tried running different jobs manually. They both work.
> >
> > On the advice I got last week, I have verified that the Event Log service
> > and SQL Server Agent are both running. Its probably worth noting though that
> > while I do see the deadlock in the SQL Error Log, it doesnnt show up in the
> > Event Viewer.
> >
> > Any ideas?
> >
> > TIA, ChrisR
> >
>