Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

I refreshed the SQL server name after rnning a backup log with no
truncate ....and still found out that space available size is still 0!
I also shrunk the Trans Log and still the same rusults.
Would very much appreciate your help
Thank you
*** Sent via Developersdex http://www.codecomments.com ***Hi,
What is the Recovery model of your database?
If it is FULL OR BULK_LOGGED then
If you need the transaction log backup execute below to clear the
Transaction log:-
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncat_eonly)
After doing these execute the below command to get the log size
dbcc sqlperf(logspace)
Thanks
Hari
SQL Server MVP
"mike" <-nospam@.yahoo.com> wrote in message
news:%23F7ieaVkFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I refreshed the SQL server name after rnning a backup log with no
> truncate ....and still found out that space available size is still 0!
> I also shrunk the Trans Log and still the same rusults.
> Would very much appreciate your help
> Thank you
>
>
> *** Sent via Developersdex http://www.codecomments.com ***|||did you hear mike <-nospam@.yahoo.com> say in news:#F7ieaVkFHA.2852
@.TK2MSFTNGP14.phx.gbl:

> AFter Running Backup Log With No Truncate
sorry, but wouldn't this be the problem? if you wish to remove the
committed transactions from the log you should remove the no_truncate
option.
http://msdn.microsoft.com/library/d...rl=/library/en-
us/tsqlref/ts_ba-bz_35ww.asp
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||thank you. It worked, perfectly-
Mike
*** Sent via Developersdex http://www.codecomments.com ***

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

I refreshed the SQL server name after rnning a backup log with no
truncate ....and still found out that space available size is still 0!
I also shrunk the Trans Log and still the same rusults.
Would very much appreciate your help
Thank you
*** Sent via Developersdex http://www.codecomments.com ***
Hi,
What is the Recovery model of your database?
If it is FULL OR BULK_LOGGED then
If you need the transaction log backup execute below to clear the
Transaction log:-
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncatXeonly)
After doing these execute the below command to get the log size
dbcc sqlperf(logspace)
Thanks
Hari
SQL Server MVP
"mike" <-nospam@.yahoo.com> wrote in message
news:%23F7ieaVkFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I refreshed the SQL server name after rnning a backup log with no
> truncate ....and still found out that space available size is still 0!
> I also shrunk the Trans Log and still the same rusults.
> Would very much appreciate your help
> Thank you
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||did you hear mike <-nospam@.yahoo.com> say in news:#F7ieaVkFHA.2852
@.TK2MSFTNGP14.phx.gbl:

> AFter Running Backup Log With No Truncate
sorry, but wouldn't this be the problem? if you wish to remove the
committed transactions from the log you should remove the no_truncate
option.
http://msdn.microsoft.com/library/de...l=/library/en-
us/tsqlref/ts_ba-bz_35ww.asp
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||thank you. It worked, perfectly-
Mike
*** Sent via Developersdex http://www.codecomments.com ***

Database Space Available Is Zero Even AFter Running Backup Log With No Truncate

I refreshed the SQL server name after rnning a backup log with no
truncate ....and still found out that space available size is still 0!
I also shrunk the Trans Log and still the same rusults.
Would very much appreciate your help
Thank you
*** Sent via Developersdex http://www.developersdex.com ***Hi,
What is the Recovery model of your database?
If it is FULL OR BULK_LOGGED then
If you need the transaction log backup execute below to clear the
Transaction log:-
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncat­eonly)
After doing these execute the below command to get the log size
dbcc sqlperf(logspace)
Thanks
Hari
SQL Server MVP
"mike" <-nospam@.yahoo.com> wrote in message
news:%23F7ieaVkFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I refreshed the SQL server name after rnning a backup log with no
> truncate ....and still found out that space available size is still 0!
> I also shrunk the Trans Log and still the same rusults.
> Would very much appreciate your help
> Thank you
>
>
> *** Sent via Developersdex http://www.developersdex.com ***|||did you hear mike <-nospam@.yahoo.com> say in news:#F7ieaVkFHA.2852
@.TK2MSFTNGP14.phx.gbl:
> AFter Running Backup Log With No Truncate
sorry, but wouldn't this be the problem? if you wish to remove the
committed transactions from the log you should remove the no_truncate
option.
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/tsqlref/ts_ba-bz_35ww.asp
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogssql

Database Space available is 0MB

The space available for a database is 0MB, what are the solutions to solve
this?
Thanks!
Enable autogrowth size up the database to a bigger with the statement
ALTER DATABASE which syntax can be found in the bOL.
HTH, Jens Suessmeyer.
|||Susanna wrote:
> The space available for a database is 0MB, what are the solutions to
> solve this?
What space are you talking about? Space inside the DB? Diskspace?
robert
|||yes space inside the DB, not diskspace
"Robert Klemme" wrote:

> Susanna wrote:
> What space are you talking about? Space inside the DB? Diskspace?
> robert
>
|||Try running DBCC UPDATEUSAGE.
Andrew J. Kelly SQL MVP
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:86D9A357-F5FE-4218-AE04-184B024ABA18@.microsoft.com...
> The space available for a database is 0MB, what are the solutions to solve
> this?
> Thanks!

Database space available error

i am getting incorrect value while trying to retrieve space available ina database using sql dmo..

i am using sql server express 2005

Hard to help you without knowing the code you are using. Please post it here in order to help you. What are you getting back, what are you expecting ? Why are you expecting this value and not the actual presented by DMO ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

databaseobject->get_spaceavailable(&space);

when i check this value with original value .ie. value known by opening database management is diferent

Database size vs total table and index size

I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that
-
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of you
r
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.

Database size vs total table and index size

I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply
"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...
|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.

Database size vs total table and index size

I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.

Database Size

I am attempting to create a new database with a size of 7gig. I have 16 gig
available but Enterprise Manager errors with not enough disk space with any
attempt greater than 4gig. any suggestions are very much welcomedAll I can think of is that the file system is FAT rather thjan NTFS.=20
It is always recommended to use NTFS for SQL Server
Mike John
"needing help" <anonymous@.discussions.microsoft.com> wrote in message =
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...

quote:

---

> I am attempting to create a new database with a size of 7gig. I have =

---

16 gig available but Enterprise Manager errors with not enough disk =
space with any attempt greater than 4gig. any suggestions are very much =
welcomed|||Hi,
From Query analyzer , execute the below Extended procedure and identify the
hard disk availability,
xp_fixeddrives
Thanks
Hari
MCDBA
"needing help" <anonymous@.discussions.microsoft.com> wrote in message
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...

quote:

---

> I am attempting to create a new database with a size of 7gig. I have 16

---

gig available but Enterprise Manager errors with not enough disk space with
any attempt greater than 4gig. any suggestions are very much welcomed|||I execute the produre and it confirms the drive has 16515MB free. The drive
is formatted to FAT32 much to my suprise

Database Size

I am attempting to create a new database with a size of 7gig. I have 16 gig available but Enterprise Manager errors with not enough disk space with any attempt greater than 4gig. any suggestions are very much welcomedAll I can think of is that the file system is FAT rather thjan NTFS.
It is always recommended to use NTFS for SQL Server
Mike John
"needing help" <anonymous@.discussions.microsoft.com> wrote in message =news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...
> I am attempting to create a new database with a size of 7gig. I have =16 gig available but Enterprise Manager errors with not enough disk =space with any attempt greater than 4gig. any suggestions are very much =welcomed|||Hi,
From Query analyzer , execute the below Extended procedure and identify the
hard disk availability,
xp_fixeddrives
Thanks
Hari
MCDBA
"needing help" <anonymous@.discussions.microsoft.com> wrote in message
news:737D769F-71B3-40F0-A2FD-DB8C609273F3@.microsoft.com...
> I am attempting to create a new database with a size of 7gig. I have 16
gig available but Enterprise Manager errors with not enough disk space with
any attempt greater than 4gig. any suggestions are very much welcomed|||I execute the produre and it confirms the drive has 16515MB free. The drive is formatted to FAT32 much to my suprisesql

Database Recovery Issue

We had a hard drive crash on one of our servers. There are no backups of the
database available but we were able to recover the MDF and LDf for the only
database that was on the server. The server had to be rebuilt and SQL
reinstalled. The question I have, is there a way to recover this database
using the MDF and LDF files. I tried using the sp_attach_db command but
without much success (don't know if I used the wrong parameters or what). Any
suggestions would be greatly appreciated.There are several possibilities depending on the state of the database and
files involved. Your best bet is to contact PSS
(http://support.microsoft.com) who will be able to help you get up and
running again (and fix your backup process, of course :-)
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:987BCD70-4AD9-42A6-AF66-CBB37EECB647@.microsoft.com...
> We had a hard drive crash on one of our servers. There are no backups of
the
> database available but we were able to recover the MDF and LDf for the
only
> database that was on the server. The server had to be rebuilt and SQL
> reinstalled. The question I have, is there a way to recover this database
> using the MDF and LDF files. I tried using the sp_attach_db command but
> without much success (don't know if I used the wrong parameters or what).
Any
> suggestions would be greatly appreciated.

Database Recovery Issue

We had a hard drive crash on one of our servers. There are no backups of the
database available but we were able to recover the MDF and LDf for the only
database that was on the server. The server had to be rebuilt and SQL
reinstalled. The question I have, is there a way to recover this database
using the MDF and LDF files. I tried using the sp_attach_db command but
without much success (don't know if I used the wrong parameters or what). Any
suggestions would be greatly appreciated.
There are several possibilities depending on the state of the database and
files involved. Your best bet is to contact PSS
(http://support.microsoft.com) who will be able to help you get up and
running again (and fix your backup process, of course :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:987BCD70-4AD9-42A6-AF66-CBB37EECB647@.microsoft.com...
> We had a hard drive crash on one of our servers. There are no backups of
the
> database available but we were able to recover the MDF and LDf for the
only
> database that was on the server. The server had to be rebuilt and SQL
> reinstalled. The question I have, is there a way to recover this database
> using the MDF and LDF files. I tried using the sp_attach_db command but
> without much success (don't know if I used the wrong parameters or what).
Any
> suggestions would be greatly appreciated.

Database Recovery Issue

We had a hard drive crash on one of our servers. There are no backups of the
database available but we were able to recover the MDF and LDf for the only
database that was on the server. The server had to be rebuilt and SQL
reinstalled. The question I have, is there a way to recover this database
using the MDF and LDF files. I tried using the sp_attach_db command but
without much success (don't know if I used the wrong parameters or what). An
y
suggestions would be greatly appreciated.There are several possibilities depending on the state of the database and
files involved. Your best bet is to contact PSS
(http://support.microsoft.com) who will be able to help you get up and
running again (and fix your backup process, of course :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:987BCD70-4AD9-42A6-AF66-CBB37EECB647@.microsoft.com...
> We had a hard drive crash on one of our servers. There are no backups of
the
> database available but we were able to recover the MDF and LDf for the
only
> database that was on the server. The server had to be rebuilt and SQL
> reinstalled. The question I have, is there a way to recover this database
> using the MDF and LDF files. I tried using the sp_attach_db command but
> without much success (don't know if I used the wrong parameters or what).
Any
> suggestions would be greatly appreciated.

database recovery if no backup available

Hi,

If we delete a Database accidentally (no backup exists), Is it possible to recover. How?

Thanks

ven--

If you have dropped the database and with no backup it is not possible to recover, thats why BACKUP is an important arm on the basis availability.|||

Hi Venp,

As Satya said it very crucial to take backup daily basis other wise you will lose your important data, but some third party tolls like Stellar can recovery the files , how ever i only tried with demo version but not for the SQL Database Files, this is FYI only.

Hemantgiri S. Goswami

|||

You can try some third-party file recovery tools to attempt to un-delete the files, and then attach them to the instance.

You might get lucky. Otherwise, there's not much to be done. We can't fix a file that isn't there without a backup.