How to free up SQL 2000 transaction log space?

I have tried all of the Shrink database options provided by Enterprise
Manager to try and shrink my transaction log from its current size of 2Gig,
but no matter what I execute it stays 2Gig.
I selected the option to reduce to its minimum allowable size (which it
showed as about 300Meg), but even after that the file still shows as 2Gig.
Is there a different mechanism I need to do to free up this huge amount of
space? A stored procedure or other system proc?
Many thanks!
Marksee
http://www.nigelrivett.net/SQLAdmin...ileGrows_1.html
"Mark Findlay" wrote:

> I have tried all of the Shrink database options provided by Enterprise
> Manager to try and shrink my transaction log from its current size of 2Gig
,
> but no matter what I execute it stays 2Gig.
> I selected the option to reduce to its minimum allowable size (which it
> showed as about 300Meg), but even after that the file still shows as 2Gig.
> Is there a different mechanism I need to do to free up this huge amount of
> space? A stored procedure or other system proc?
> Many thanks!
> Mark
>|||Nigel explains on his site about LSN's. For a large database to have a 2 GI
G
log is not unheard of. For example, if a backup hasn't been performed for
several days and there is a rollback or a deadlock and the database has to
track forward from that time, the database log file can swell quickly. You
may want to look at the cause of the log size moreso than just trying to
shrink the log. If there is an unresolved transaction from a particular day
and the LSN's for a part of the log have overlapping values (ie LSN start of
Wednesday is less than LSN start for Tuesday and matches LSN end of Monday),
you may be able to simply take the database offline and run a restore
operation to clean up the log. Nigel's instruction page is very clear. I
hope it helps you with your problem.
--
Regards,
Jamie
"Mark Findlay" wrote:

> I have tried all of the Shrink database options provided by Enterprise
> Manager to try and shrink my transaction log from its current size of 2Gig
,
> but no matter what I execute it stays 2Gig.
> I selected the option to reduce to its minimum allowable size (which it
> showed as about 300Meg), but even after that the file still shows as 2Gig.
> Is there a different mechanism I need to do to free up this huge amount of
> space? A stored procedure or other system proc?
> Many thanks!
> Mark
>|||Thanks for the great help Jamie, that answered my question and more!
Thanks,
Mark
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:30313C61-ECCC-4294-8AC9-8EB6AD22FF78@.microsoft.com...
> Nigel explains on his site about LSN's. For a large database to have a 2
> GIG
> log is not unheard of. For example, if a backup hasn't been performed for
> several days and there is a rollback or a deadlock and the database has to
> track forward from that time, the database log file can swell quickly.
> You
> may want to look at the cause of the log size moreso than just trying to
> shrink the log. If there is an unresolved transaction from a particular
> day
> and the LSN's for a part of the log have overlapping values (ie LSN start
> of
> Wednesday is less than LSN start for Tuesday and matches LSN end of
> Monday),
> you may be able to simply take the database offline and run a restore
> operation to clean up the log. Nigel's instruction page is very clear. I
> hope it helps you with your problem.
> --
> Regards,
> Jamie
>
> "Mark Findlay" wrote:
>

How to force DB shrink?

I have DB 13GB in size. After we deleted some old tables
from it it's still using 13GB on hard drive. In properties
it was always set up "Auto shrink", but after a week it's
still 13GB... :(
How to force shrinking manual?Try running DBCC OPENTRANS to see if you have any long running open
transactions preventing the SHRINK.
Also have a look at DBCC SHRINKFILE in BOL
HTH
Ryan Waight, MCDBA, MCSE
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>|||Is it data or log file which is big? If log, check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>|||If you are trying to shrink the data file then you can use DBCC SHRINKFILE.
You will be unable to shrink the data file if there are allocated extents at
the end. You can move indexes and tables with clustered indexes by doing a
DBCC DBREINDEX. If the extents are from a table with non clustered index or
text/image data, then you have more of an issue. Unfortunately these do not
move with the DBCC DBREINDEX command. You will have to create a new table
and copy the data back in to physically move the extents. A better option
is to create a new file group for your non clustered tables and text/image
data and move it there.
To find the offending tables and indexes download the free tool below.
Select from the tool bar Table/Index View. This will show you a list of
tables and indexes and where they are allocated in the data file. Scroll to
the end of the data file click on an allocated extent (green). This will
highlight the index that is allocated to the extent. You can then run DBCC
DBREINDEX on that index.
Hope this helps
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>|||"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>
Autoshrink shrinks a database if it has 25% space in it. If the table took
up less space than that , then you have to manually shrink it
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003|||This is the main question! How manually shrink DB?!
>--Original Message--
>"Alexandre Rybalov" <alex@.covers.com> wrote in message
>news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
>> I have DB 13GB in size. After we deleted some old tables
>> from it it's still using 13GB on hard drive. In
properties
>> it was always set up "Auto shrink", but after a week
it's
>> still 13GB... :(
>> How to force shrinking manual?
>Autoshrink shrinks a database if it has 25% space in it.
If the table took
>up less space than that , then you have to manually
shrink it
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.518 / Virus Database: 316 - Release Date:
11/09/2003
>
>.
>|||Alexandre,
You can use DBCC SHRINKFILE for data files also.If it still doesnt shrink,
check for data fragmentation using DBCC SHOWCONTIG.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:52fe01c37d30$3ab07190$a501280a@.phx.gbl...
> This is the main question! How manually shrink DB?!
> >--Original Message--
> >"Alexandre Rybalov" <alex@.covers.com> wrote in message
> >news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> >> I have DB 13GB in size. After we deleted some old tables
> >> from it it's still using 13GB on hard drive. In
> properties
> >> it was always set up "Auto shrink", but after a week
> it's
> >> still 13GB... :(
> >>
> >> How to force shrinking manual?
> >>
> >Autoshrink shrinks a database if it has 25% space in it.
> If the table took
> >up less space than that , then you have to manually
> shrink it
> >
> >
> >--
> >Outgoing mail is certified Virus Free.
> >Checked by AVG anti-virus system (http://www.grisoft.com).
> >Version: 6.0.518 / Virus Database: 316 - Release Date:
> 11/09/2003
> >
> >
> >.
> >

how to fixed the size of the report and landscape view state?

Good day.

How to make or fixed the size of the templates? and how to make the report in landscape mode? i have make a layout in the landscape mode but the priblem in print preview it is not in the landscape mode.

thanks your help is greatly appreciated

Check the width and height property of the "Report" and "Page". Both should be proper, for your report to print (preview) in landscape.|||

thanks rajeeb

but there's a way to make it fixed landscape view. Coz when previewing in the Web form its ok coz the layout of the report is in the landscape but when you export it example in the PDF file it will not set in landscape mode.

thanks

|||

If your widht and height property have proper values and you can view the report in landscape mode, then the export to pdf will also not be a problem.

I have used it, no issues, though I export to PDF using Render method of the ReportViewer control, which is same as what you are trying to do.

How to find the size of the database dump.

hi,

There is facility to do backup of the database. I can use that
facility to backup the database. But before i do a backup i want to
check if the disk space available enough to backup that database. I
have a 22gb database mdf file, when i took a backup of that its only
3gb. SO i cannot use the size of mdf file to determine the database
dump file. Is there any facility available to find out the backup
database dump size before doing the backup?

Thanks
Nabhonil."Nabhonil Sinha" <nasinha@.cisco.com> wrote in message
news:a0a8344a.0409280610.230b4dae@.posting.google.c om...
> hi,
> There is facility to do backup of the database. I can use that
> facility to backup the database. But before i do a backup i want to
> check if the disk space available enough to backup that database. I
> have a 22gb database mdf file, when i took a backup of that its only
> 3gb. SO i cannot use the size of mdf file to determine the database
> dump file. Is there any facility available to find out the backup
> database dump size before doing the backup?
> Thanks
> Nabhonil.

I'm not sure if this is possible, at least in a reliable way. For a full
backup, the "reserved" column in the sp_spaceused output might be a very
rough starting point (it wouldn't include transaction log information), but
differential and log backups would make things more complicated. It could be
easier simply to run a few backups, get a feel for how big they should be,
then monitor the backup file size over time.

In any case, disk space is quite cheap, so buying an extra disk or two is
probably also something to consider.

Simon

How to find the size of all databases at once

Hi everyone.

I'm very new to SQL and need to move databases from an old server to a new one that is attached to a SAN. I'm trying to plan LUN sizes.

Is there a way to show the sizes of all databases on the server at once?

Thank you.

Try running the following: "exec sp_helpdb". This will give you the size in megabytes of each database. If you pass the database name to sp_helpdb you will get file level information which may assist in your planning. Thanks.

|||

When I execute this command I get this error:

Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53

Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.

The statement has been terminated.

Do I need to add something else?

|||

It seems that one of the databases does not have an owner. Check the following link:

http://www.fogcreek.com/FogBugz/KB/errors/CannotinsertthevalueNULLi.html

I hope this solves your problem.

Best regards,

Sami Samir

How to find the MDF file size.

Hi,
I want to find the size of the MDF file for a select database.
How can I do this?
Thanks in advance.
Nadee.Try this:
SELECT size*8192 as FileSize
FROM master..sysaltfiles
WHERE fileid=1 AND dbid=DB_ID('YourDatabase')
Razvan|||Gon
Also ,take a look at sp_helpdb 'database' system stored procedure
If you have more than one file
SELECT RTRIM(filename) ,SIZE
FROM sysfiles
WHERE fileid = (SELECT MIN(fileid) FROM sysfiles)
UNION ALL
SELECT RTRIM(filename) ,SIZE
FROM sysfiles
WHERE fileid > (SELECT MIN(fileid) FROM sysfiles) AND
fileid < (SELECT MAX(fileid) FROM sysfiles)
UNION ALL
SELECT RTRIM(filename),SIZE
FROM sysfiles
WHERE fileid = (SELECT MAX(fileid) FROM sysfiles)
"Gon Nadiya" <WazHot@.mymail.com> wrote in message
news:u$f5%23mllFHA.420@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to find the size of the MDF file for a select database.
> How can I do this?
> Thanks in advance.
> Nadee.
>|||Used the extended stored procedure xp_getfiledetails to give you size of fil
e
but the name of the file will be on NT format example 'BL4BB2~1.BKP'
EXEC master.dbo.xp_getfiledetails
'\\Blptcsqlmon01\backup\BLDALSTGSQL04\TR
AN_Backup\blcms_TRAN_BU_20050801_00.
bkp'
"Gon Nadiya" wrote:

> Hi,
> I want to find the size of the MDF file for a select database.
> How can I do this?
> Thanks in advance.
> Nadee.
>
>|||Hi URI,
Thanks for the support.
It worked with no issues.
Nadee.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OL0qrQmlFHA.1048@.tk2msftngp13.phx.gbl...
> Gon
> Also ,take a look at sp_helpdb 'database' system stored procedure
> If you have more than one file
>
> SELECT RTRIM(filename) ,SIZE
> FROM sysfiles
> WHERE fileid = (SELECT MIN(fileid) FROM sysfiles)
> UNION ALL
> SELECT RTRIM(filename) ,SIZE
> FROM sysfiles
> WHERE fileid > (SELECT MIN(fileid) FROM sysfiles) AND
> fileid < (SELECT MAX(fileid) FROM sysfiles)
> UNION ALL
> SELECT RTRIM(filename),SIZE
> FROM sysfiles
> WHERE fileid = (SELECT MAX(fileid) FROM sysfiles)
> "Gon Nadiya" <WazHot@.mymail.com> wrote in message
> news:u$f5%23mllFHA.420@.TK2MSFTNGP09.phx.gbl...
>|||Does anyone know how to get file size in Dos - need way to verify file size
is not equal to 0 bytes.
JC
"JosephPruiett" wrote:
> Used the extended stored procedure xp_getfiledetails to give you size of f
ile
> but the name of the file will be on NT format example 'BL4BB2~1.BKP'
> EXEC master.dbo.xp_getfiledetails
> '\\Blptcsqlmon01\backup\BLDALSTGSQL04\TR
AN_Backup\blcms_TRAN_BU_20050801_0
0.bkp'
>
>
> "Gon Nadiya" wrote:
>|||Not sure I understand you, bit how about DIR?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JC" <JC@.discussions.microsoft.com> wrote in message
news:44944802-D81D-481A-A475-0D82E0EC04BA@.microsoft.com...
> Does anyone know how to get file size in Dos - need way to verify file si
ze
> is not equal to 0 bytes.
> JC
> "JosephPruiett" wrote:
>|||sp_helpfile
"JC" <JC@.discussions.microsoft.com> wrote in message
news:44944802-D81D-481A-A475-0D82E0EC04BA@.microsoft.com...
> Does anyone know how to get file size in Dos - need way to verify file
> size
> is not equal to 0 bytes.
> JC
> "JosephPruiett" wrote:
>sql

how to find size of the index

sp_spaceused <tablename>
select dpages,reserved,used from sysindexes where id=357576312
These values seem to be different. Can somebody explan?
sp_spaceused <tablename>
namerowsreserveddataindex_sizeunused
<tablename>1998 136 KB72 KB8 KB56 KB
select id from sysobjects where name = <table_name>
357576312
select dpages,reserved,used from sysindexes where id=357576312
dpagesreservedused
91710
select (9*8)+(17*8)+(10*8) 288
select (136+72+8+56) 272
"mani" wrote:

> sp_spaceused <tablename>
> select dpages,reserved,used from sysindexes where id=357576312
>
> These values seem to be different. Can somebody explan?
|||You need a little math help:
Reserved = Data + Index + Unused = Used + Unused
17 Reserved Pages * 8 KB/page = 136 KB =
9 Data Pages * 8 KB/page = 72 KB +
1 Index Page * 8 KB/page = 10 Used Pages - 9 Data Pages = 8 KB
(9 Data Pages + 1 Index Page) * 8 KB/page = 10 Used Pages * 8 KB/page = 80 KB
Now, (17 Reserved Pages - 10 Used Pages) * 8 KB/page =
7 Unused Pages * 8 KB/page = 56 KB.
You just have to figure out which pieces are which but it is all consistent.
Your problem was that you added Reserved to what was used: reserved is
already the grand total you were aiming for.
Hope this helps. What also can help is to read what the authors wrote: take
a look at the sp_spaceused stored procedure,
USE master
GO
EXEC dbo.sp_helptext 'dbo.sp_spaceused'
GO
You can find a lot of interesting information by examining the code for the
system procedures.
Sincerely,
Anthony Thomas
"mani" wrote:
[vbcol=seagreen]
> sp_spaceused <tablename>
> namerowsreserveddataindex_sizeunused
> <tablename>1998 136 KB72 KB8 KB56 KB
>
> select id from sysobjects where name = <table_name>
> 357576312
> select dpages,reserved,used from sysindexes where id=357576312
> dpagesreservedused
> 91710
>
> select (9*8)+(17*8)+(10*8) 288
> select (136+72+8+56) 272
> "mani" wrote:

how to find size of the index

sp_spaceused <tablename>
select dpages,reserved,used from sysindexes where id=357576312
These values seem to be different. Can somebody explan?sp_spaceused <tablename>
name rows reserved data index_size unus
ed
<tablename> 1998 136 KB 72 KB 8 KB 56 KB
select id from sysobjects where name = <table_name>
357576312
select dpages,reserved,used from sysindexes where id=357576312
dpages reserved used
9 17 10
select (9*8)+(17*8)+(10*8) 288
select (136+72+8+56) 272
"mani" wrote:

> sp_spaceused <tablename>
> select dpages,reserved,used from sysindexes where id=357576312
>
> These values seem to be different. Can somebody explan?|||You need a little math help:
Reserved = Data + Index + Unused = Used + Unused
17 Reserved Pages * 8 KB/page = 136 KB =
9 Data Pages * 8 KB/page = 72 KB +
1 Index Page * 8 KB/page = 10 Used Pages - 9 Data Pages = 8 KB
(9 Data Pages + 1 Index Page) * 8 KB/page = 10 Used Pages * 8 KB/page = 80 K
B
Now, (17 Reserved Pages - 10 Used Pages) * 8 KB/page =
7 Unused Pages * 8 KB/page = 56 KB.
You just have to figure out which pieces are which but it is all consistent.
Your problem was that you added Reserved to what was used: reserved is
already the grand total you were aiming for.
Hope this helps. What also can help is to read what the authors wrote: take
a look at the sp_spaceused stored procedure,
USE master
GO
EXEC dbo.sp_helptext 'dbo.sp_spaceused'
GO
You can find a lot of interesting information by examining the code for the
system procedures.
Sincerely,
Anthony Thomas
"mani" wrote:
[vbcol=seagreen]
> sp_spaceused <tablename>
> name rows reserved data index_size unus
ed
> <tablename> 1998 136 KB 72 KB 8 KB 56 KB
>
> select id from sysobjects where name = <table_name>
> 357576312
> select dpages,reserved,used from sysindexes where id=357576312
> dpages reserved used
> 9 17 10
>
> select (9*8)+(17*8)+(10*8) 288
> select (136+72+8+56) 272
> "mani" wrote:
>

how to find size of the index

sp_spaceused <tablename>
select dpages,reserved,used from sysindexes where id=357576312
These values seem to be different. Can somebody explan?sp_spaceused <tablename>
name rows reserved data index_size unused
<tablename> 1998 136 KB 72 KB 8 KB 56 KB
select id from sysobjects where name = <table_name>
357576312
select dpages,reserved,used from sysindexes where id=357576312
dpages reserved used
9 17 10
select (9*8)+(17*8)+(10*8) 288
select (136+72+8+56) 272
"mani" wrote:
> sp_spaceused <tablename>
> select dpages,reserved,used from sysindexes where id=357576312
>
> These values seem to be different. Can somebody explan?|||You need a little math help:
Reserved = Data + Index + Unused = Used + Unused
17 Reserved Pages * 8 KB/page = 136 KB =9 Data Pages * 8 KB/page = 72 KB +
1 Index Page * 8 KB/page = 10 Used Pages - 9 Data Pages = 8 KB
(9 Data Pages + 1 Index Page) * 8 KB/page = 10 Used Pages * 8 KB/page = 80 KB
Now, (17 Reserved Pages - 10 Used Pages) * 8 KB/page =7 Unused Pages * 8 KB/page = 56 KB.
You just have to figure out which pieces are which but it is all consistent.
Your problem was that you added Reserved to what was used: reserved is
already the grand total you were aiming for.
Hope this helps. What also can help is to read what the authors wrote: take
a look at the sp_spaceused stored procedure,
USE master
GO
EXEC dbo.sp_helptext 'dbo.sp_spaceused'
GO
You can find a lot of interesting information by examining the code for the
system procedures.
Sincerely,
Anthony Thomas
"mani" wrote:
> sp_spaceused <tablename>
> name rows reserved data index_size unused
> <tablename> 1998 136 KB 72 KB 8 KB 56 KB
>
> select id from sysobjects where name = <table_name>
> 357576312
> select dpages,reserved,used from sysindexes where id=357576312
> dpages reserved used
> 9 17 10
>
> select (9*8)+(17*8)+(10*8) 288
> select (136+72+8+56) 272
> "mani" wrote:
> > sp_spaceused <tablename>
> >
> > select dpages,reserved,used from sysindexes where id=357576312
> >
> >
> > These values seem to be different. Can somebody explan?

how to find out minimum size

hello

i have one doubt.can anybody clarigy it.

every time i am doing my database shrinking manually.

what i am asking i find out every time when i shrink my database that sql is showing minimum size of database to shrink.how can its getting minimum size

but i need to find out how to get minimum size of particular database programatically.

thanx

excuse my poor english

"every time i am doing my database shrinking manually."

Bad planning. Disk is cheap. You probably don′t backup your database acording to the recovery model of it.

Shrinking database files drives to disk fragmentation on it, witch goes to low performance. Never think about "shrink" as one of the "best pratices".

Marcelo

|||Kiran - see the answer I posted in the getting started forum ...|||

where you posted rod colledge

can u send me that url?

How to Find Index Size / Usage (mb)

I'm trying to establish the mb usage of a series of nonclustered indexes, I'm used to using the manage indexes GUI in 6.5, and showcontig doesn't quite give me what I want, any suggestions?Have you tried sp_spaceused <table_name> yet?|||That's a great help thanks, is it possible to drill down into the 3 indexes to see how the usage is split?

How to find determine estimated time for restoring a terabyte database

Hi,

Is there any way i can determine the total time a database of size 1 tera byte will take while restoring it?

Or if anyone has hands on restoring a terabyte database and can share the time taken it would be of great help.

Thanks,

This depends on your disk subsystem.

What is the maximum throughput per second of your disk subsystem?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||SEe my http://sqlserver-qa.net/blogs/perftune/archive/2007/06/12/get-backup-and-restore-performance-stats-with-a-dmv.aspx fyi.